Hi Timour,
On Tue, Mar 08, 2011 at 04:35:24PM +0200, tim...@askmonty.org wrote:
> At file:///home/tsk/mprog/src/5.3/
>
> ------------------------------------------------------------
> revno: 2934
> revision-id: tim...@askmonty.org-20110308143512-0m7uotvxp9rmegak
> parent: pser...@askmonty.org-20110304155430-l0ad4iag9gn5zeka
> committer: tim...@askmonty.org
> 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
> comm...@mariadb.org
> 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
_______________________________________________
Mailing list: https://launchpad.net/~maria-developers
Post to : maria-developers@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-developers
More help : https://help.launchpad.net/ListHelp
