Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-5050

Different result set with index_merge=on and index_merge=off on comparing a date column to an incorrect value

        Details

        • Type: Bug
        • Status: Open
        • Priority: Minor
        • Resolution: Unresolved
        • Affects Version/s: 10.0.4, 5.5.33, 5.3.12
        • Fix Version/s: 5.3.13, 10.0, 5.5
        • Component/s: None
        • Labels:
          None

          Description

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

            Gliffy Diagrams

              Attachments

                Issue Links

                relates to

                Bug - A problem which impairs or prevents the functions of the product. MDEV-4837 Comparing a TIME value with an illegal time representation returns TRUE

                • Minor - Minor loss of function, or other problem where easy workaround is present.
                • Open

                  Activity

                  Hide
                  Permalink
                  igor Igor Babaev added a comment -

                  This exactly the consequence of the bug MDEV-4837:
                  MariaDB [test]> select * from t1 where d = 'wrong value';
                  Empty set (0.00 sec)
                  MariaDB [test]> select * from t1 ignore index (d) where d = 'wrong value';
                  ----------------------

                  i d c1

                  ----------------------

                  1 0000-00-00 Daniel

                  ----------------------
                  1 row in set, 1 warning (0.01 sec)

                  Show
                  igor Igor Babaev added a comment - This exactly the consequence of the bug MDEV-4837 : MariaDB [test] > select * from t1 where d = 'wrong value'; Empty set (0.00 sec) MariaDB [test] > select * from t1 ignore index (d) where d = 'wrong value'; ----- ---------- ------- i d c1 ----- ---------- ------- 1 0000-00-00 Daniel ----- ---------- ------- 1 row in set, 1 warning (0.01 sec)

                    People

                    • Assignee:
                      psergey Sergei Petrunia
                      Reporter:
                      elenst Elena Stepanova
                    • Votes:
                      0 Vote for this issue
                      Watchers:
                      2 Start watching this issue

                      Dates

                      • Created:
                        Updated: