[#MDEV-5050] Different result set with index_merge=on and index_merge=off on comparing a date column to an incorrect value

The following test case returns different results for the same query when it's executed with and without index_merge:

CREATE TABLE t1 (i INT, d DATE, c1 CHAR(16), INDEX(i), INDEX(d), INDEX(c1)) ENGINE=MyISAM;

INSERT INTO t1 VALUES

(4,'2002-12-21','John'),(2,'2002-03-18','Leon'),(1,'0000-00-00','Daniel'),

(2,'2006-09-12','Tom'),(194,'2003-06-05','Sam'),(2,'2000-07-19','Ivan'),

(3,'1900-01-01','Julia');

CREATE TABLE t2 (c2 CHAR(16)) ENGINE=MyISAM;

INSERT INTO t2 VALUES ('John'),('Veronica');

SET optimizer_switch='index_merge=on';

SELECT * FROM t1 LEFT JOIN t2 ON ( c2 = c1 )

WHERE c1 = 'Alan' OR i > 254 OR d = 'wrong value' ;

SET optimizer_switch='index_merge=off';

SELECT * FROM t1 LEFT JOIN t2 ON ( c2 = c1 )

WHERE c1 = 'Alan' OR i > 254 OR d = 'wrong value' ;

Result with index_merge=on (empty set):

i	d	c1	c2

Result with index_merge=off:

i	d	c1	c2

1	0000-00-00	Daniel	NULL

I cannot say for sure which one is correct, since there is a comparison to an incorrect date value. I suppose it should return the empty result since '0000-00-00' does not look equal to 'wrong value'; but more importantly, I expect the query return the same result set, regardless of the optimizer_switch value.

Reproducible on 5.3 (down to 5.3.5), 5.5, 10.0.

See also MDEV-4837 (Comparing a TIME value with an illegal time representation returns TRUE), it might be related.

EXPLAIN with index_merge=on:

id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra

1	SIMPLE	t1	index_merge	i,d,c1	c1,i	17,5	NULL	2	100.00	Using sort_union(c1,i); Using where

1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where; Using join buffer (flat, BNL join)

Warnings:

Note	1003	select `test`.`t1`.`i` AS `i`,`test`.`t1`.`d` AS `d`,`test`.`t1`.`c1` AS `c1`,`test`.`t2`.`c2` AS `c2` from `test`.`t1` left join `test`.`t2` on((`test`.`t2`.`c2` = `test`.`t1`.`c1`)) where ((`test`.`t1`.`c1` = 'Alan') or (`test`.`t1`.`i` > 254) or (`test`.`t1`.`d` = 'wrong value'))

EXPLAIN with index_merge=off:

id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra

1	SIMPLE	t1	ALL	i,d,c1	NULL	NULL	NULL	7	100.00	Using where

1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where; Using join buffer (flat, BNL join)

Warnings:

Note	1003	select `test`.`t1`.`i` AS `i`,`test`.`t1`.`d` AS `d`,`test`.`t1`.`c1` AS `c1`,`test`.`t2`.`c2` AS `c2` from `test`.`t1` left join `test`.`t2` on((`test`.`t2`.`c2` = `test`.`t1`.`c1`)) where ((`test`.`t1`.`c1` = 'Alan') or (`test`.`t1`.`i` > 254) or (`test`.`t1`.`d` = 'wrong value'))

[MDEV-5050] Different result set with index_merge=on and index_merge=off on comparing a date column to an incorrect value Created: 2013-09-22 Updated: 2022-09-08
Status:	Open
Project:	MariaDB Server
Component/s:	None
Affects Version/s:	10.0.4, 5.5.33, 5.3.12
Fix Version/s:	5.5

[MDEV-5050] Different result set with index_merge=on and index_merge=off on comparing a date column to an incorrect value Created: 2013-09-22 Updated: 2022-09-08