Details
-
Type:
Bug
-
Status: Closed
-
Priority:
Minor
-
Resolution: Fixed
-
Affects Version/s: None
-
Fix Version/s: None
-
Component/s: None
-
Labels:
Description
The following sequence of commands gives a wrong query result in MariaDB 5.3/5.5:
CREATE TABLE t1 (i1 INTEGER, i2 INTEGER, KEY k1 (i1));
INSERT INTO t1 VALUES (9,0), (7,1), (1,9), (7,101), (2,1), (9,102);
CREATE TABLE t2 (pk INTEGER, i2 INTEGER, PRIMARY KEY (pk));
INSERT INTO t2 VALUES (2,1), (3,2), (5,3), (6,4), (7,6), (9,4);
CREATE TABLE t3 (i1 INTEGER, i2 INTEGER);
INSERT INTO t3 VALUES (1,0), (1,1), (1,101), (1,102);
SET SESSION optimizer_switch="index_condition_pushdown=on";
SET SESSION optimizer_switch="materialization=off";
SELECT * FROM t3
WHERE (i1, i2) IN
( SELECT COUNT(DISTINCT t2.i2), t1.i2 FROM t1 JOIN t2 ON t1.i1 = t2.pk
WHERE t2.pk BETWEEN 7 AND 9 GROUP BY t1.i2 );
MariaDB [test]> SELECT * FROM t3 WHERE (i1, i2) IN ( SELECT COUNT(DISTINCT t2.i2), t1.i2 FROM t1 JOIN t2 ON t1.i1 = t2.pk WHERE t2.pk BETWEEN 7 AND 9 GROUP BY t1.i2 ); +------+------+ | i1 | i2 | +------+------+ | 1 | 0 | | 1 | 101 | | 1 | 102 | +------+------+
The correct answer for the query is returned with these settings:
SET SESSION optimizer_switch="index_condition_pushdown=off"; SET SESSION optimizer_switch="materialization=off"; MariaDB [test]> SELECT * FROM t3 WHERE (i1, i2) IN ( SELECT COUNT(DISTINCT t2.i2), t1.i2 FROM t1 JOIN t2 ON t1.i1 = t2.pk WHERE t2.pk BETWEEN 7 AND 9 GROUP BY t1.i2 ); +------+------+ | i1 | i2 | +------+------+ | 1 | 0 | | 1 | 1 | | 1 | 101 | | 1 | 102 | +------+------+
And with these settings also we have the correct answer
SET SESSION optimizer_switch="index_condition_pushdown=on"; SET SESSION optimizer_switch="materialization=on"; MariaDB [test]> SELECT * FROM t3 WHERE (i1, i2) IN ( SELECT COUNT(DISTINCT t2.i2), t1.i2 FROM t1 JOIN t2 ON t1.i1 = t2.pk WHERE t2.pk BETWEEN 7 AND 9 GROUP BY t1.i2 ); +------+------+ | i1 | i2 | +------+------+ | 1 | 0 | | 1 | 1 | | 1 | 101 | | 1 | 102 | +------+------+
(See also bug#12667154 in mysql-5.6.5)
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
Re: Wrong result with enabled index condition pushdown and disabled subquery materialization.
The fix survived some rounds of testing, but then we've figured it has introduced https://mariadb.atlassian.net/browse/MDEV-416
Launchpad bug id: 992942
Currently:
MDEV-325(which has a fix for this bug) is only present in 10.0- for this bug, I get correct query results in the current 5.5 tree. (revision-id: igor@askmonty.org-20130123231805-5vrl79e2nia8vqb3)
I guess, MySQL has fixed it from their side, and we've got a fix merged from them.
Perhaps, we could unify the fixes. But if this bug is not repeatable, it cannot be a stopper for 5.5.29.
The bug disappeared from 5.3 tree since the following revision:
revno: 3586
revision-id: psergey@askmonty.org-20121010052122-1uua5ogs8pyoanbf
parent: sanja@montyprogram.com-20121005092655-1xfcw36gsdyhawu5
committer: Sergey Petrunya <psergey@askmonty.org>
branch nick: 5.3
timestamp: Wed 2012-10-10 09:21:22 +0400
message:
Backport of: olav.sandstaa@oracle.com-20120516074923-vd0dhp183vqcp2ql
.. into MariaDB 5.3
Fix for Bug#12667154 SAME QUERY EXEC AS WHERE SUBQ GIVES DIFFERENT
RESULTS ON IN() & NOT IN() COMP #3
This bug causes a wrong result in mysql-trunk when ICP is used
and bad performance in mysql-5.5 and mysql-trunk.
...
Thus closing as fixed in 5.3.10, 5.5.28, 10.0.0.
Re: Wrong result with enabled index condition pushdown and disabled subquery materialization.
The problem is actually inherited from 5.2. There, one can also observe that
this is clearly not what was intended. However, the effect is only performance, the query produces correct result. MariaDB 5.3/ MySQL 5.6 add IndexConditionPushdown, which is not cleaned up correctly and causes wrong query result.