maria-developers team mailing list archive

[Sergey Petrunya <psergey@xxxxxxxxxxxx>]

Hi Timour,

On Tue, Mar 08, 2011 at 04:35:24PM +0200, timour@xxxxxxxxxxxx wrote:
> At file:///home/tsk/mprog/src/5.3/
> 
> ------------------------------------------------------------
> revno: 2934
> revision-id: timour@xxxxxxxxxxxx-20110308143512-0m7uotvxp9rmegak
> parent: psergey@xxxxxxxxxxxx-20110304155430-l0ad4iag9gn5zeka
> committer: timour@xxxxxxxxxxxx
> branch nick: 5.3
> timestamp: Tue 2011-03-08 16:35:12 +0200
> message:
>   Fix LP BUG#719198
>   
>   Analysis:
>   The assert failed because the execution code for
>   partial matching is designed with the assumption that
>   NULLs on the left side are detected as early as possible,
>   and a NULL result is returned before any lookups are
>   performed at all.
>   
>   However, in the case of an Item_cache object on the left
>   side, null was not detected properly, because detection
>   was done via Item::is_null(), which is not implemented at
>   all for Item_cache, and resolved to the default Item::is_null()
>   which always returns FALSE.
>   
>   Solution:
>   Use the property Item::null_value instead of is_null(), which
>   is properly updated for Item_cache objects as well.
>   
>   Alternatively one could implement Item_cache:is_null(), but
>   its not quite clear how that will interact with the cache
>   update methods, so the current fix seems simpler and cleaner.

Ok to push.

Since it is guaranteed that cache_value() have been called for the object,
it's ok to use null_value instead of is_null().

Btw, out of interest I've implemented Item_cache::is_null() and 
t/subselect*.test ran successfully, so it seems to be ok to have. 
I don't insist that it is added as part of this fix, though.


> === modified file 'mysql-test/r/subselect_mat.result'
> --- a/mysql-test/r/subselect_mat.result	2011-01-14 10:51:30 +0000
> +++ b/mysql-test/r/subselect_mat.result	2011-03-08 14:35:12 +0000
> @@ -1371,3 +1371,41 @@ SELECT pk FROM t1 WHERE (b,c,d) IN (SELE
>  pk
>  2
>  DROP TABLE t1, t2;
> +#
> +# LPBUG#719198 Ordered_key::cmp_key_with_search_key(rownum_t): Assertion `!compare_pred[i]->null_value'
> +# failed with subquery on both sides of NOT IN and materialization
> +#
> +CREATE TABLE t1 (f1a int, f1b int) ;
> +INSERT IGNORE INTO t1 VALUES (1,1),(2,2);
> +CREATE TABLE t2 ( f2 int);
> +INSERT IGNORE INTO t2 VALUES (3),(4);
> +CREATE TABLE t3 (f3a int, f3b int);
> +set session optimizer_switch='materialization=on,partial_match_rowid_merge=on,partial_match_table_scan=off';
> +EXPLAIN
> +SELECT * FROM t2 WHERE (SELECT f3a FROM t3) NOT IN (SELECT f1a FROM t1);
> +id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
> +1       PRIMARY t2      ALL     NULL    NULL    NULL    NULL    2       Using where
> +3       SUBQUERY        t1      ALL     NULL    NULL    NULL    NULL    2       
> +2       SUBQUERY        t3      system  NULL    NULL    NULL    NULL    0       const row not found
> +SELECT * FROM t2 WHERE (SELECT f3a FROM t3) NOT IN (SELECT f1a FROM t1);
> +f2
> +EXPLAIN
> +SELECT * FROM t2 WHERE (SELECT f3a, f3b FROM t3) NOT IN (SELECT f1a, f1b FROM t1);
> +id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
> +1       PRIMARY t2      ALL     NULL    NULL    NULL    NULL    2       Using where
> +3       SUBQUERY        t1      ALL     NULL    NULL    NULL    NULL    2       
> +2       SUBQUERY        t3      system  NULL    NULL    NULL    NULL    0       const row not found
> +SELECT * FROM t2 WHERE (SELECT f3a, f3b FROM t3) NOT IN (SELECT f1a, f1b FROM t1);
> +f2
> +insert into t3 values (1,1),(2,2);
> +EXPLAIN
> +SELECT * FROM t2 WHERE (SELECT f3a FROM t3 where f3a > 3) NOT IN (SELECT f1a FROM t1);
> +id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
> +1       PRIMARY t2      ALL     NULL    NULL    NULL    NULL    2       Using where
> +3       SUBQUERY        t1      ALL     NULL    NULL    NULL    NULL    2       
> +2       SUBQUERY        t3      ALL     NULL    NULL    NULL    NULL    2       Using where
> +SELECT * FROM t2 WHERE (SELECT f3a FROM t3 where f3a > 3) NOT IN (SELECT f1a FROM t1);
> +f2
> +3
> +4
> +drop table t1, t2, t3;
> 
> === modified file 'mysql-test/t/subselect_mat.test'
> --- a/mysql-test/t/subselect_mat.test	2011-01-14 10:51:30 +0000
> +++ b/mysql-test/t/subselect_mat.test	2011-03-08 14:35:12 +0000
> @@ -1011,3 +1011,31 @@ SELECT pk FROM t1 WHERE (a) IN (SELECT a
>  SELECT pk FROM t1 WHERE (b,c,d) IN (SELECT b,c,d FROM t2 WHERE pk > 0);
>  DROP TABLE t1, t2;
>  
> +--echo #
> +--echo # LPBUG#719198 Ordered_key::cmp_key_with_search_key(rownum_t): Assertion `!compare_pred[i]->null_value'
> +--echo # failed with subquery on both sides of NOT IN and materialization
> +--echo #
> +
> +CREATE TABLE t1 (f1a int, f1b int) ;
> +INSERT IGNORE INTO t1 VALUES (1,1),(2,2);
> +CREATE TABLE t2 ( f2 int);
> +INSERT IGNORE INTO t2 VALUES (3),(4);
> +CREATE TABLE t3 (f3a int, f3b int);
> +
> +set session optimizer_switch='materialization=on,partial_match_rowid_merge=on,partial_match_table_scan=off';
> +
> +EXPLAIN
> +SELECT * FROM t2 WHERE (SELECT f3a FROM t3) NOT IN (SELECT f1a FROM t1);
> +SELECT * FROM t2 WHERE (SELECT f3a FROM t3) NOT IN (SELECT f1a FROM t1);
> +
> +EXPLAIN
> +SELECT * FROM t2 WHERE (SELECT f3a, f3b FROM t3) NOT IN (SELECT f1a, f1b FROM t1);
> +SELECT * FROM t2 WHERE (SELECT f3a, f3b FROM t3) NOT IN (SELECT f1a, f1b FROM t1);
> +
> +insert into t3 values (1,1),(2,2);
> +
> +EXPLAIN
> +SELECT * FROM t2 WHERE (SELECT f3a FROM t3 where f3a > 3) NOT IN (SELECT f1a FROM t1);
> +SELECT * FROM t2 WHERE (SELECT f3a FROM t3 where f3a > 3) NOT IN (SELECT f1a FROM t1);
> +
> +drop table t1, t2, t3;
> 
> === modified file 'sql/item_subselect.cc'
> --- a/sql/item_subselect.cc	2011-03-03 21:48:31 +0000
> +++ b/sql/item_subselect.cc	2011-03-08 14:35:12 +0000
> @@ -5069,7 +5069,7 @@ bool subselect_rowid_merge_engine::parti
>    for (uint i= test(non_null_key); i < keys_count; i++)
>    {
>      DBUG_ASSERT(merge_keys[i]->get_column_count() == 1);
> -    if (merge_keys[i]->get_search_key(0)->is_null())
> +    if (merge_keys[i]->get_search_key(0)->null_value)
>      {
>        ++count_nulls_in_search_key;
>        bitmap_set_bit(&matching_outer_cols, merge_keys[i]->get_keyid());
> 

> _______________________________________________
> commits mailing list
> commits@xxxxxxxxxxx
> https://lists.askmonty.org/cgi-bin/mailman/listinfo/commits

-- 
BR
 Sergey
-- 
Sergey Petrunia, Software Developer
Monty Program AB, http://askmonty.org
Blog: http://s.petrunia.net/blog