[MDEV-11315] Wrong result (missing rows) and unexpected warnings with condition_pushdown_for_derived Created: 2016-11-18  Updated: 2016-11-23  Resolved: 2016-11-23

Status: Closed
Project: MariaDB Server
Component/s: Optimizer, Views
Affects Version/s: 10.2
Fix Version/s: 10.2.3

Type: Bug Priority: Major
Reporter: Elena Stepanova Assignee: Igor Babaev
Resolution: Fixed Votes: 0
Labels: regression-10.2


 Description   

Note: MDEV-11103 has been fixed, so it must be a different problem.

DROP VIEW if exists v2;
 
DROP TABLE IF EXISTS t1, t2;
 
 
 
CREATE TABLE t1 (pk1 INT PRIMARY KEY, a INT, b INT) ENGINE=MyISAM;
 
INSERT INTO t1 VALUES (10,7,1),(11,0,2);
 
 
 
CREATE TABLE t2 (pk2 INT PRIMARY KEY, c INT, d DATETIME) ENGINE=MyISAM;
 
CREATE ALGORITHM=TEMPTABLE VIEW v2 AS SELECT * FROM t2;
 
INSERT INTO t2 VALUES (1,4,'2008-09-27 00:34:58'),(2,5,'2007-05-28 00:00:00'),(3,6,'2009-07-25 09:21:20');
 
 
 
SELECT * FROM ( SELECT * FROM t1 ) AS sq 
WHERE b IN ( SELECT pk2 FROM v2 WHERE c > sq.b ) OR b = 100;

Actual result (10.2 b162068456)

 
MariaDB [test]> SELECT * FROM ( SELECT * FROM t1 ) AS sq  WHERE b IN ( SELECT pk2 FROM v2 WHERE c > sq.b ) OR b = 100;
 
Empty set, 3 warnings (0.01 sec)
 
 
 
MariaDB [test]> show warnings;
 
+---------+------+-------------------------------------------------------+
 
| Level   | Code | Message                                               |
 
+---------+------+-------------------------------------------------------+
 
| Warning | 1292 | Incorrect datetime value: '4' for column 'c' at row 1 |
 
| Warning | 1292 | Incorrect datetime value: '5' for column 'c' at row 2 |
 
| Warning | 1292 | Incorrect datetime value: '6' for column 'c' at row 3 |
 
+---------+------+-------------------------------------------------------+
 
3 rows in set (0.01 sec)

(Please note the strange warnings – the datetime column does not participate in the query at all).

Expected result

 
MariaDB [test]> SELECT * FROM ( SELECT * FROM t1 ) AS sq  WHERE b IN ( SELECT pk2 FROM v2 WHERE c > sq.b ) OR b = 100;
 
+-----+------+------+
 
| pk1 | a    | b    |
 
+-----+------+------+
 
|  10 |    7 |    1 |
 
|  11 |    0 |    2 |
 
+-----+------+------+
 
2 rows in set (0.00 sec)



 Comments   
Comment by Igor Babaev [ 2016-11-21 ]

The query 

SELECT FROM t1 AS sq  WHERE b IN ( SELECT pk2 FROM v2 WHERE c > sq.b ) OR b = 100;

works fine.

The difference is that we have t1.b->used_tables() == OUTER_REF_TABLE_BIT (and this is correct) while in the query from the test case after the merge of the derived table t1 we have
t1.b->used_tables()=1 (and this is incorrect).

Comment by Igor Babaev [ 2016-11-21 ]

The same problem we can see for mergeable views:

MariaDB [test]> CREATE VIEW v1 AS SELECT * FROM t1;
 
Query OK, 0 rows affected (0.01 sec)
 
MariaDB [test]> SELECT * FROM v1 AS sq  WHERE b IN ( SELECT pk2 FROM v2 WHERE c > sq.b ) OR b = 100;
 
Empty set, 3 warnings (8.35 sec)

Comment by Igor Babaev [ 2016-11-23 ]

The fix for this bug was pushed into the 10.2 tree.

Generated at Thu Feb 08 07:49:00 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.