Wrong result with DISTINCT , ANY , subquery_cache=off , NOT NULL
Affects | Status | Importance | Assigned to | Milestone | |
---|---|---|---|---|---|
MariaDB |
Fix Released
|
High
|
Timour Katchaounov |
Bug Description
The following query:
SELECT *
FROM t2 , t1
WHERE t1.pk != ANY (
SELECT DISTINCT col_int_key
FROM t3
);
incorrectly returns
8 28
9 28
8 29
when executed with semijoin=off and
8 28
9 28
8 29
9 29
otherwise. Repeatable in maria-5.3. Not repeatable in maria-5.2, mysql-5.5
EXPLAIN:
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t2 ALL NULL NULL NULL NULL 2 100.00
1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (flat, BNL join)
2 DEPENDENT SUBQUERY t3 range col_int_key col_int_key 5 NULL 6 100.00 Using where; Using index for group-by
select `test`.
minimal switch: subquery_cache=off
full switch: index_merge=
revision-id: <email address hidden>
date: 2011-11-07 16:39:02 +0400
build-date: 2011-11-10 11:21:11 +0200
revno: 3273
branch-nick: maria-5.3
test case:
CREATE TABLE t1 ( a int NOT NULL) ;
INSERT INTO t1 VALUES (28),(29);
CREATE TABLE t2 ( a int) ;
INSERT INTO t2 VALUES (8),(9);
CREATE TABLE t3 ( a int, KEY (a)) ;
INSERT INTO t3 VALUES (1),(1)
SELECT *
FROM t2 , t1
WHERE t1.a != ANY (
SELECT DISTINCT t3.a
FROM t3
);
Related branches
Changed in maria: | |
milestone: | none → 5.3 |
assignee: | nobody → Oleksandr "Sanja" Byelkin (sanja-byelkin) |
description: | updated |
Changed in maria: | |
importance: | Undecided → High |
Changed in maria: | |
status: | New → In Progress |
Changed in maria: | |
status: | Confirmed → In Progress |
the problem was in resetting QUICK_GROUP_ MIN_MAX_ SELECT, fix is:
=== modified file 'sql/opt_range.cc' MIN_MAX_ SELECT: :reset( vo ENTER(" QUICK_GROUP_ MIN_MAX_ SELECT: :reset" );
--- sql/opt_range.cc 2011-11-18 17:35:51 +0000
+++ sql/opt_range.cc 2011-11-29 07:19:01 +0000
@@ -12915,6 +12915,7 @@ int QUICK_GROUP_
int result;
DBUG_
+ seen_first_key= FALSE; key_read= 1;
if (!head->key_read)
{
doing_
But there is 2 strange things:
1) why the bug is visible only on 5.3?
2) are there something else which should be reset?