[MDEV-2346] LP:806510 - Wrong result with derived table, impossible where , derived_merge=on and EXISTS in maria-5.3 Created: 2011-07-06  Updated: 2015-02-02  Resolved: 2012-10-04

Status: Closed
Project: MariaDB Server
Component/s: None
Affects Version/s: None
Fix Version/s: None

Type: Bug Priority: Major
Reporter: Philip Stoev (Inactive) Assignee: Igor Babaev
Resolution: Fixed Votes: 0
Labels: Launchpad

Attachments: XML File LPexportBug806510.xml    

 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
) ;

 Comments   
Comment by Philip Stoev (Inactive) [ 2011-07-06 ]

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

Comment by Igor Babaev [ 2011-07-08 ]

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)

Comment by Igor Babaev [ 2011-07-09 ]

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)

Comment by Rasmus Johansson (Inactive) [ 2011-12-13 ]

Launchpad bug id: 806510

Generated at Thu Feb 08 06:41:13 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.