Wrong result with implicit grouping and empty result set
Affects | Status | Importance | Assigned to | Milestone | |
---|---|---|---|---|---|
MariaDB |
Fix Released
|
Low
|
Timour Katchaounov |
Bug Description
When executing the following query with materialization, it returns a row, even though the WHERE clause must be false because the subquery in the NOT IN predicate returns ( NULL , NULL ) , which should make the NOT IN predicate FALSE. It seems this is only observed with queries that violate the ONLY_FULL_GROUP_BY SQL mode.
Test case:
SET SESSION optimizer_switch = 'in_to_
CREATE TABLE t1 ( f10 varchar(1)) ;
CREATE TABLE t2 ( f1 int(11), f3 int(11), PRIMARY KEY (f1)) ;
CREATE TABLE t3 ( f4 date, f11 varchar(1)) ;
INSERT IGNORE INTO t3 VALUES ('1900-01-01','f');
SELECT f4 FROM t3
WHERE ( 2 , 7 ) NOT IN (
SELECT f1 , MIN( f3 ) FROM t2 WHERE ( 'j' ) IN ( SELECT t1.f10 FROM t1 ) );
explain:
+----+-
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-
| 1 | PRIMARY | t3 | system | NULL | NULL | NULL | NULL | 1 | |
| 2 | SUBQUERY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE noticed after reading const tables |
| 3 | SUBQUERY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | no matching row in const table |
+----+-
Changed in maria: | |
milestone: | none → 5.3 |
assignee: | nobody → Timour Katchaounov (timour) |
Changed in maria: | |
status: | Confirmed → In Progress |
summary: |
- Wrong result with materialization=on in maria-5.3 maria-5.3-mwl89 + Wrong result with implicit grouping and empty result set |
Changed in maria: | |
status: | In Progress → Fix Committed |
Changed in maria: | |
status: | Fix Committed → Fix Released |
Simpler query:
SELECT f4 FROM t3 WHERE ( 2 , 7 ) NOT IN ( SELECT f1 , MIN( f3 ) FROM t2);
* In 5.3 the query always produces wrong result exists= off,materializa tion=on, semijoin= off';
* In 5.3-mwl89 the query produces wrong result only with materialization ON:
SET SESSION optimizer_switch = 'in_to_