Details
-
Type:
Bug
-
Status: Closed
-
Priority:
Major
-
Resolution: Fixed
-
Affects Version/s: None
-
Fix Version/s: None
-
Component/s: None
-
Labels:
Description
Requires derived_merge=on .Not repeatable before WL#106 .
The following query
SELECT *
FROM (SELECT * FROM t2 ) AS alias1
WHERE EXISTS (
SELECT t3.f2
FROM t3 , t1
WHERE t1.f3 = t3.f3
AND alias1.f2 != 0
) ;
returns no rows when executed with derived merge and 1 row when executed with no derived table or with derived_merge=off.
Explain:
| 1 | PRIMARY | t2 | ALL | NULL | NULL | NULL | NULL | 2 | Using where |
| 3 | DEPENDENT SUBQUERY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE noticed after reading const tables |
minimal optimizer switch: derived_merge=off
entire optimizer_switch:
index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=off,loosescan=off,materialization=off,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=off,mrr=on,mrr_cost_based=off,mrr_sort_keys=on,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=on,table_elimination=on
test case:
CREATE TABLE t1 ( f3 int) ;
INSERT IGNORE INTO t1 VALUES (8),(0);
CREATE TABLE t2 ( f2 int) ;
INSERT IGNORE INTO t2 VALUES (4),(NULL);
CREATE TABLE t3 ( f2 int, f3 int) ;
INSERT IGNORE INTO t3 VALUES (7,8);
SELECT *
FROM (SELECT * FROM t2 ) AS alias1
WHERE EXISTS (
SELECT t3.f2
FROM t3 , t1
WHERE t1.f3 = t3.f3
AND alias1.f2 != 0
) ;
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
Re: Wrong result with derived table, impossible where , derived_merge=on and EXISTS in maria-5.3
This bug can be reproduced with a view instead if the derived table:
MariaDB [test]> CREATE VIEW v2 AS SELECT * FROM t2;
Query OK, 0 rows affected (0.00 sec)
MariaDB [test]> EXPLAIN SELECT * FROM v2 AS alias1 WHERE EXISTS ( SELECT t3.f2 FROM t3 , t1 WHERE t1.f3 = t3.f3 AND alias1.f2 != 0 );
------------------------------------------------------------------------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
------------------------------------------------------------------------------------------------------------------+
| 1 | PRIMARY | t2 | ALL | NULL | NULL | NULL | NULL | 2 | Using where |
| 2 | DEPENDENT SUBQUERY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE noticed after reading const tables |
------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
Re: Wrong result with derived table, impossible where , derived_merge=on and EXISTS in maria-5.3
The bug existed before the merge with MWL#106.
The following demonstrates this for rev #3048:
MariaDB [test]> CREATE TABLE t1 (a int) ;
Query OK, 0 rows affected (0.02 sec)
MariaDB [test]> INSERT INTO t1 VALUES (4), (NULL);
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
MariaDB [test]> CREATE TABLE t2 (a int) ;
Query OK, 0 rows affected (0.01 sec)
MariaDB [test]> INSERT INTO t2 VALUES (8), (0);
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
MariaDB [test]> CREATE TABLE t3 (a int, b int) ;
Query OK, 0 rows affected (0.01 sec)
MariaDB [test]> INSERT INTO t3 VALUES (7,8);
Query OK, 1 row affected (0.00 sec)
MariaDB [test]> CREATE VIEW v1 AS SELECT * FROM t1;
Query OK, 0 rows affected (0.01 sec)
MariaDB [test]> SELECT * FROM v1 t
-> WHERE EXISTS (SELECT t3.a FROM t3, t2
-> WHERE t2.a = t3.b AND t.a != 0);
Empty set (0.01 sec)
MariaDB [test]> EXPLAIN
-> SELECT * FROM v1 t
-> WHERE EXISTS (SELECT t3.a FROM t3, t2
-> WHERE t2.a = t3.b AND t.a != 0);
------------------------------------------------------------------------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
------------------------------------------------------------------------------------------------------------------+
| 1 | PRIMARY | t1 | ALL | NULL | NULL | NULL | NULL | 2 | Using where |
| 2 | DEPENDENT SUBQUERY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE noticed after reading const tables |
------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
Launchpad bug id: 806510
Re: Wrong result with derived table, impossible where , derived_merge=on and EXISTS in maria-5.3
bzr version-info
revision-id: psergey@askmonty.org-20110706063051-1x1x67sbg5q57sai
date: 2011-07-06 10:30:51 +0400
build-date: 2011-07-06 17:33:36 +0300
revno: 3085
branch-nick: maria-5.3