maria-developers team mailing list archive

[Sergey Petrunia <sergey@xxxxxxxxxxx>]

Hi Varun,

Please find some cosmetic input below. Ok to push after addresed. 

> diff --git a/mysql-test/main/subselect4.test b/mysql-test/main/subselect4.test
> index 6f5eb1f2985f..e9da028618f6 100644
> --- a/mysql-test/main/subselect4.test
> +++ b/mysql-test/main/subselect4.test
> @@ -2238,3 +2238,38 @@ SET join_cache_level= @save_join_cache_level;
>  DROP TABLE t1,t2,t3,t4;
>  
>  --echo # End of 10.2 tests
> +
> +--echo #
> +--echo # MDEV-21265: IN predicate conversion to IN subquery should be allowed for a broader set of datatype comparison
> +--echo #
> +
> +CREATE TABLE t1(a VARCHAR(50) collate utf8_general_ci, b INT);
> +INSERT INTO t1 VALUES ('abc',1), ('def', 2), ('ghi', 3), ('jkl', 4), ('mno', 5);
> +
> +CREATE TABLE t2(a VARCHAR(50) collate utf8mb4_general_ci, b INT);
> +INSERT INTO t2 VALUES ('abc',1), ('def', 2), ('ghi', 3), ('jkl', 4), ('mno', 5);
> +
> +set in_predicate_conversion_threshold=2;
> +
...

> +EXPLAIN
> +SELECT * FROM t2 WHERE (t2.a,t2.b) IN (('abx',1),('def',2), ('abc', 3));
> +
> +set names default;
> +DROP TABLE t1,t2;
> +
> +--echo # End of 10.3 tests

Please restore the value of in_predicate_conversion_threshold.

> diff --git a/sql/item_subselect.h b/sql/item_subselect.h
> index 1cea7291c9ef..d1b2f86088d9 100644
> --- a/sql/item_subselect.h
> +++ b/sql/item_subselect.h
> @@ -573,6 +573,13 @@ class Item_in_subselect :public Item_exists_subselect
>      TRUE<=>registered in the list of semijoins in outer select
>    */
>    bool is_registered_semijoin;
> +
> +  Item_func_not_all *upper_item; // point on NOT/NOP before ALL/SOME subquery

Is this necessary to touch the above member?  I think it's better leave it as
is.

> +
> +  /*
> +    SET to TRUE if IN subququery is converted from an IN rredicate
> +  */
> +  bool converted_from_in_predicate;
...

> diff --git a/sql/sql_type.h b/sql/sql_type.h
> index 907225b7c505..a1403165fe38 100644
> --- a/sql/sql_type.h
> +++ b/sql/sql_type.h
> @@ -1291,9 +1291,21 @@ class Type_handler
>                                 Item *target_expr, Item *target_value,
>                                 Item_bool_func2 *source,
>                                 Item *source_expr, Item *source_const) const= 0;
> +
> +  /*
> +    @brief
> +      Check if an IN subquery allows materialization or not
> +    @param
> +      inner              expression on the inner side of the IN subquery
> +      outer              expression on the inner side of the IN subquery

Typo. Should it should be 'outer' ?

> +      is_in_predicate    SET to true if IN subquery was converted from an
> +                         IN predicate or we are checking if materialization
> +                         strategy can be used for an IN predicate
> +  */
>    virtual bool
>    subquery_type_allows_materialization(const Item *inner,
> -                                       const Item *outer) const= 0;
> +                                       const Item *outer,
> +                                       bool is_in_predicate) const= 0;
>    /**
>      Make a simple constant replacement item for a constant "src",
>      so the new item can futher be used for comparison with "cmp", e.g.:

BR
 Sergei
-- 
Sergei Petrunia, Software Developer
MariaDB Corporation | Skype: sergefp | Blog: http://s.petrunia.net/blog