| Bug #52329 | Wrong result: subquery materialization, IN, non-null field followed by nullable | ||
|---|---|---|---|
| Submitted: | 24 Mar 2010 13:20 | Modified: | 22 Nov 2010 1:27 |
| Reporter: | Timour Katchaounov | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S2 (Serious) |
| Version: | 6.0 | OS: | Any |
| Assigned to: | Jørgen Løland | CPU Architecture: | Any |
| Tags: | materialization, optimizer_switch | ||
[24 Mar 2010 13:45]
MySQL Verification Team
Thank you for the bug report. Verified as described.
[24 Mar 2010 14:05]
Timour Katchaounov
Some further analysis shows that this bug can be caused when the non-aggregated field of the subquery is declared as "NOT NULL". Consider the test case from subselect.test, that is marked with "Bug#16603". It is sufficient to add 'NOT NULL' to field 'a', to reproduce the same bug. Thus the following modified test case from subselect.test causes the same bug: DROP TABLE if exists t1; CREATE TABLE t1 (a varchar(5) not null, b varchar(10)); INSERT INTO t1 VALUES ('AAA', 5), ('BBB', 4), ('BBB', 1), ('CCC', 2), ('CCC', 7), ('AAA', 2), ('AAA', 4), ('BBB', 3), ('AAA', 8); set @@optimizer_switch='materialization=off'; SELECT * FROM t1 WHERE (a,b) = ANY (SELECT a, max(b) FROM t1 GROUP BY a); set @@optimizer_switch='materialization=on'; SELECT * FROM t1 WHERE (a,b) = ANY (SELECT a, max(b) FROM t1 GROUP BY a); Notice that there are at least several more test cases in the whole regression suite, where a similar modification causes the same wrong result.
[24 Mar 2010 15:10]
Roy Lyseng
Just verified that this is a problem also as long back as version 6.0.6 (2008-08-11), hence it is probably not a recent regression. Developer: Please contact reporter when starting to work on this bug.
[25 Mar 2010 10:46]
Timour Katchaounov
The problem is independent of whether there is a MIN/MAX aggregate.
The general problem is when we create a temp table for a non-NULL-able
field followed by a NULL-able one.
Thus an even simpler test case is:
drop table if exists t1n, t2;
create table t1n (a1 char(8) not null, a2 char(8) not null);
create table t2 (b1 char(8) not null, b2 char(8));
insert into t1n values ('1 - 02', '2 - 02');
insert into t2 values ('1 - 01', '2 - 01');
insert into t2 values ('1 - 01', '2 - 01');
insert into t2 values ('1 - 02', '2 - 02');
insert into t2 values ('1 - 02', '2 - 02');
insert into t2 values ('1 - 03', '2 - 03');
-- Wrong result
set @@optimizer_switch='materialization=on';
select * from t1n
where (a1, a2) in (select b1, b2 from t2 where b1 > '0' group by b1, b2);
-- Correct result
set @@optimizer_switch='materialization=off';
select * from t1n
where (a1, a2) in (select b1, b2 from t2 where b1 > '0' group by b1, b2);
[8 Apr 2010 9:14]
Timour Katchaounov
I have a fix which I will submit shortly (after UC'2010).
[11 Oct 2010 9:34]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/120475 3260 Jorgen Loland 2010-10-11 Bug#52329: Wrong result: subquery materialization, IN, non-null field followed by nullable Consider a query SELECT * FROM t1 WHERE (a1, a2) IN ( SELECT b1, b2 FROM t2 ...) When solved using materialized subselect, we check if a record from t1 is part of the result set by constructing a key from the a1 and a2 values and perform a lookup based on that key. If a1 and a2 are CHAR(3) with values foo and bar, the KEY is "foobar". If a2 is NULLable, the null-bit should be the first bit for this field's part of the KEY ("foo<nullbit_a2>bar"). Before, the null-bit pointer for each key field wrongly pointed to the first bit of the KEY instead of the first bit of the key part. Thus, when setting the null bit for a2 above, the first bit of a1 was wrongly updated: "0oo bar" ('f' in foo replaced with null-bit of a2, and the null-bit of a2 not set) This patch sets the null-pointer for each key part to the first bit of that field's part of KEY, not the first bit of KEY. The bug was masked if the first field was NOT NULL. @ mysql-test/include/subquery_mat.inc Added test for BUG#52329 @ mysql-test/r/subquery_mat.result Added test for BUG#52329 @ mysql-test/r/subquery_mat_all.result Added test for BUG#52329 @ mysql-test/r/subquery_mat_none.result Added test for BUG#52329 @ sql/item_subselect.cc subselect_hash_sj_engine::init_permanent: Make the null-pointer for each key part point to the first bit of that field's part of KEY, not the first bit of KEY.
[13 Oct 2010 8:52]
Timour Katchaounov
Jorgen, my fault I forgot to send you the fix, however it would be fair to mention you that you copied my fix from MariaDB. It actually took me some time to debug this. By the way, your fix is incomplete. You missed another case when materialization is chosen as a sub-strategy of semijoin. The same error has been copied in the procedure setup_sj_materialization(). Timour
[13 Oct 2010 10:35]
Jørgen Løland
Timour, I have not looked at your work and didn't know you had a fix for it. I would of course never copy any of your work without your full agreement accompanied with an SCA. I figured this one out on my own. However, it is reassuring that we came to the same conclusion. I will look into the other case you mention as well. Thanks for the pointer.
[13 Oct 2010 11:39]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/120676 3262 Jorgen Loland 2010-10-13 Bug#52329: Wrong result: subquery materialization, IN, non-null field followed by nullable Consider a query SELECT * FROM t1 WHERE (a1, a2) IN ( SELECT b1, b2 FROM t2 ...) When solved using materialized subselect, we check if a record from t1 is part of the result set by constructing a key from the a1 and a2 values and perform a lookup based on that key. If a1 and a2 are CHAR(3) with values foo and bar, the KEY is "foobar". If a2 is NULLable, the null-bit should be the first bit for this field's part of the KEY ("foo<nullbit_a2>bar"). Before, the null-bit pointer for each key field wrongly pointed to the first bit of the KEY instead of the first bit of the key part. Thus, when setting the null bit for a2 above, the first bit of a1 was wrongly updated: "0oo bar" ('f' in foo replaced with null-bit of a2, and the null-bit of a2 not set) This patch sets the null-pointer for each key part to the first bit of that field's part of KEY, not the first bit of KEY. The bug was masked if the first field was NOT NULL. The bug also applied to semi-join materialization since the code has been copied (also fixed by this patch). @ mysql-test/include/subquery_sj.inc Add test for BUG52329 @ mysql-test/r/subquery_sj_all.result Add test for BUG52329 @ mysql-test/r/subquery_sj_all_jcl6.result Add test for BUG52329 @ mysql-test/r/subquery_sj_all_jcl7.result Add test for BUG52329 @ mysql-test/r/subquery_sj_dupsweed.result Add test for BUG52329 @ mysql-test/r/subquery_sj_dupsweed_jcl6.result Add test for BUG52329 @ mysql-test/r/subquery_sj_dupsweed_jcl7.result Add test for BUG52329 @ mysql-test/r/subquery_sj_firstmatch.result Add test for BUG52329 @ mysql-test/r/subquery_sj_firstmatch_jcl6.result Add test for BUG52329 @ mysql-test/r/subquery_sj_firstmatch_jcl7.result Add test for BUG52329 @ mysql-test/r/subquery_sj_loosescan.result Add test for BUG52329 @ mysql-test/r/subquery_sj_loosescan_jcl6.result Add test for BUG52329 @ mysql-test/r/subquery_sj_loosescan_jcl7.result Add test for BUG52329 @ mysql-test/r/subquery_sj_mat.result Add test for BUG52329 @ mysql-test/r/subquery_sj_mat_jcl6.result Add test for BUG52329 @ mysql-test/r/subquery_sj_mat_jcl7.result Add test for BUG52329 @ mysql-test/r/subquery_sj_mat_nosj.result Add test for BUG52329 @ mysql-test/r/subquery_sj_none.result Add test for BUG52329 @ mysql-test/r/subquery_sj_none_jcl6.result Add test for BUG52329 @ mysql-test/r/subquery_sj_none_jcl7.result Add test for BUG52329 @ sql/item_subselect.cc subselect_hash_sj_engine::init_permanent(): Make the null-pointer for each key part point to the first bit of that field's part of KEY, not the first bit of KEY. @ sql/sql_select.cc setup_sj_materialization(): Make the null-pointer for each key part point to the first bit of that field's part of KEY, not the first bit of KEY.
[13 Oct 2010 13:28]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/120686 3262 Jorgen Loland 2010-10-13 Bug#52329: Wrong result: subquery materialization, IN, non-null field followed by nullable Consider a query SELECT * FROM t1 WHERE (a1, a2) IN ( SELECT b1, b2 FROM t2 ...) When solved using materialized subselect, we check if a record from t1 is part of the result set by constructing a key from the a1 and a2 values and perform a lookup based on that key. If a1 and a2 are CHAR(3) with values foo and bar, the KEY is "foobar". If a2 is NULLable, the null-byte should be the first byte for this field's part of the KEY ("foo<nullbyte_a2>bar"). Before, the null-byte pointer for each key field wrongly pointed to the first byte of the KEY instead of the first byte of the key part. Thus, when setting the null byte for a2 above, the first byte of a1 was wrongly updated: "0oo bar" ('f' in foo replaced with null-byte of a2, and the null-byte of a2 not set) This patch sets the null-pointer for each key part to the first bit of that field's part of KEY, not the first bit of KEY. The bug was masked if the first field was NOT NULL. The bug also applied to semi-join materialization since the code has been copied (also fixed by this patch). @ mysql-test/include/subquery_sj.inc Add test for BUG52329 @ mysql-test/r/subquery_sj_all.result Add test for BUG52329 @ mysql-test/r/subquery_sj_all_jcl6.result Add test for BUG52329 @ mysql-test/r/subquery_sj_all_jcl7.result Add test for BUG52329 @ mysql-test/r/subquery_sj_dupsweed.result Add test for BUG52329 @ mysql-test/r/subquery_sj_dupsweed_jcl6.result Add test for BUG52329 @ mysql-test/r/subquery_sj_dupsweed_jcl7.result Add test for BUG52329 @ mysql-test/r/subquery_sj_firstmatch.result Add test for BUG52329 @ mysql-test/r/subquery_sj_firstmatch_jcl6.result Add test for BUG52329 @ mysql-test/r/subquery_sj_firstmatch_jcl7.result Add test for BUG52329 @ mysql-test/r/subquery_sj_loosescan.result Add test for BUG52329 @ mysql-test/r/subquery_sj_loosescan_jcl6.result Add test for BUG52329 @ mysql-test/r/subquery_sj_loosescan_jcl7.result Add test for BUG52329 @ mysql-test/r/subquery_sj_mat.result Add test for BUG52329 @ mysql-test/r/subquery_sj_mat_jcl6.result Add test for BUG52329 @ mysql-test/r/subquery_sj_mat_jcl7.result Add test for BUG52329 @ mysql-test/r/subquery_sj_mat_nosj.result Add test for BUG52329 @ mysql-test/r/subquery_sj_none.result Add test for BUG52329 @ mysql-test/r/subquery_sj_none_jcl6.result Add test for BUG52329 @ mysql-test/r/subquery_sj_none_jcl7.result Add test for BUG52329 @ sql/item_subselect.cc subselect_hash_sj_engine::init_permanent(): Make the null-pointer for each key part point to the first byte of that field's part of KEY, not the first byte of KEY. @ sql/sql_select.cc setup_sj_materialization(): Make the null-pointer for each key part point to the first byte of that field's part of KEY, not the first byte of KEY.
[14 Oct 2010 8:12]
Jørgen Løland
Pushed to mysql-next-mr-opt-backporting
[13 Nov 2010 16:19]
Bugs System
Pushed into mysql-trunk 5.6.99-m5 (revid:alexander.nozdrin@oracle.com-20101113155825-czmva9kg4n31anmu) (version source revid:vasil.dimov@oracle.com-20100629074804-359l9m9gniauxr94) (merge vers: 5.6.99-m4) (pib:21)
[13 Nov 2010 16:34]
Bugs System
Pushed into mysql-next-mr (revid:alexander.nozdrin@oracle.com-20101113160336-atmtmfb3mzm4pz4i) (version source revid:jimmy.yang@oracle.com-20100804103744-vbpeghipkz6pyc9z) (pib:21)
[22 Nov 2010 1:27]
Paul DuBois
Bug is not in any released 5.6.x version. No changelog entry needed.
Description: The following example produces incorrect query result when using subquery materialization for the IN predicate. Notice that if the aggregate function is replaced by just the field, everything works correctly. How to repeat: drop table if exists t1n, t2n; create table t1n (a1 char(8) not null, a2 char(8) not null); create table t2n (b1 char(8) not null, b2 char(8) not null); insert into t1n values ('1 - 02', '2 - 02'); insert into t2n values ('1 - 01', '2 - 01'); insert into t2n values ('1 - 01', '2 - 01'); insert into t2n values ('1 - 02', '2 - 02'); insert into t2n values ('1 - 02', '2 - 02'); insert into t2n values ('1 - 03', '2 - 03'); -- correct result set @@optimizer_switch='materialization=off'; select * from t1n where (a1, a2) in (select b1, min(b2) from t2n where b1 > '0' group by b1); -- wrong empty result set @@optimizer_switch='materialization=on'; select * from t1n where (a1, a2) in (select b1, min(b2) from t2n where b1 > '0' group by b1); -- this works fine select * from t1n where (a1, a2) in (select b1, b2 from t2n where b1 > '0' group by b1, b2);