| Bug #36752 | subquery materialization produces wrong results when comparing different types | ||
|---|---|---|---|
| Submitted: | 16 May 2008 8:35 | Modified: | 22 Nov 2010 1:15 |
| Reporter: | Sergey Petrunya | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S3 (Non-critical) |
| Version: | 6.0-bk | OS: | Any |
| Assigned to: | Timour Katchaounov | CPU Architecture: | Any |
[16 May 2008 9:12]
Timour Katchaounov
The solution is to catch truncation errors, and return FALSE in this case. Investigate how does regular straight forward evaluation work. Consider this example: explain SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t2);
[12 Nov 2008 15:19]
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/58560 2700 Timour Katchaounov 2008-11-12 BUG#36752 subquery materialization produces wrong results when comparing different types The problem was in that temp table fields are created by looking at the inner fields of IN. In this case the type of these fields is "smaller" than the corresponding outer fields, so the lookup keys are trucated, and this found in the temp table index. As a result, when we perform index lookups, we get a match, even in cases when we shouldn't. The solution in this patch extends the one in subselect_uniquesubquery_engine::exec(). In this method, found rows are post-filtered by subselect_uniquesubquery_engine::cond, which consist of a conjunction of join->conds and the equi-join conditions added by the Item_in_subselect::*_in_to_exists_transformer methods. Since with materialization we don't call the "*_in_to_exists_transformer" methods, this patch adds equi-join conditions to subselect_uniquesubquery_engine::cond, and reuses the post-filtering in subselect_uniquesubquery_engine::exec. In addition, this fix corrects several wrong results in the current test file subselect_mat.test.
[18 Nov 2008 15:53]
Sergey Petrunya
Ok to push after the email review feedback is addressed.
[18 Nov 2008 16:54]
Sergey Petrunya
BUG#40667 depends on the fix for this bug being present. Please post a note in BUG#40667's entry when the fix for this bug is pushed. Thanks.
[19 Nov 2008 15:27]
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/59242 2700 Timour Katchaounov 2008-11-19 BUG#36752 subquery materialization produces wrong results when comparing different types The problem was in that temp table fields are created by looking at the inner fields of IN. In this case the type of these fields is "smaller" than the corresponding outer fields, so the lookup keys are trucated, and this found in the temp table index. As a result, when we perform index lookups, we get a match, even in cases when we shouldn't. The solution in this patch extends the one in subselect_uniquesubquery_engine::exec(). In this method, found rows are post-filtered by subselect_uniquesubquery_engine::cond, which consist of a conjunction of join->conds and the equi-join conditions added by the Item_in_subselect::*_in_to_exists_transformer methods. Since with materialization we don't call the "*_in_to_exists_transformer" methods, this patch adds equi-join conditions to subselect_uniquesubquery_engine::cond, and reuses the post-filtering in subselect_uniquesubquery_engine::exec. In addition, this fix corrects several wrong results in the current test file subselect_mat.test.
[19 Nov 2008 15:43]
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/59243 2700 Timour Katchaounov 2008-11-19 [merge] BUG#36752: subquery materialization produces wrong results when comparing different types Merge with mysql-6.0-opt tree.
[20 Nov 2008 15: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/59405 2701 Timour Katchaounov 2008-11-20 [merge] BUG#36752: subquery materialization produces wrong results when comparing different types Manual merge with mysql-6.0-opt tree that fixes missing "drop table" statements in subselect_mat.test.
[29 Dec 2008 15:39]
Bugs System
Pushed into 6.0.10-alpha (revid:sergefp@mysql.com-20081229085854-ui755edl9x4xomen) (version source revid:sergefp@mysql.com-20081126143611-sh4x9pv9vmbnm00v) (merge vers: 6.0.9-alpha) (pib:6)
[7 Jan 2009 20:00]
Paul DuBois
Noted in 6.0.10 changelog. Subquery materialization produced incorrect results when comparing different types.
[16 Aug 2010 6:35]
Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100816062819-bluwgdq8q4xysmlg) (version source revid:alik@sun.com-20100816062612-enatdwnv809iw3s9) (pib:20)
[13 Nov 2010 16:23]
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)
[22 Nov 2010 1:15]
Paul DuBois
Noted in 5.6.1 changelog.
[23 Nov 2010 2:28]
Paul DuBois
Correction: No 5.6.1 changelog entry. Bug does not appear in any released 5.6.x version.
Description: Subquery materialization will produce wrong query results when the IN-equality compares columns/expressions of different types. How to repeat: Run this: CREATE TABLE t1 (a CHAR(1), b VARCHAR(10)); INSERT INTO t1 VALUES ('a', 'aa'); INSERT INTO t1 VALUES ('a', 'aaa'); mysql> set @@optimizer_switch='no_semijoin'; mysql> SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1); +------+------+ | a | b | +------+------+ | a | aa | | a | aaa | +------+------+ 2 rows in set (0.00 sec) mysql> set @@optimizer_switch='no_semijoin,no_materialization'; mysql> SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1); Empty set (0.01 sec) And see that use of materialization strategy produces wrong query results. Suggested fix: The problem in the above case is column `a` is defined as CHAR(1), materialization table lookup uses ignores that values of `b` are truncated on lookup, and so always gets a match (`a` = `b` always). Perhaps we need look at both sides of the IN-equality and use the 'bigger' datatype for the temporary table?