Hi Timour, On Tue, Mar 08, 2011 at 04:35:24PM +0200, timour@askmonty.org wrote:

At file:///home/tsk/mprog/src/5.3/

------------------------------------------------------------ revno: 2934 revision-id: timour@askmonty.org-20110308143512-0m7uotvxp9rmegak parent: psergey@askmonty.org-20110304155430-l0ad4iag9gn5zeka committer: timour@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 commits@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