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

Depending on indexes or execution plans, a warning on incorrect or out of range values in WHERE condition is sometimes produced and sometimes not

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Minor
    • Resolution: Fixed
    • 5.5.25, 5.3.7
    • 5.5.27
    • None
    • None

    Description

      In the example below, a warning on '2009-01-32' is expected, but not produced:

      CREATE TABLE t1(c2 DATE NULL, INDEX(c2)) ENGINE=MyISAM;
      INSERT INTO t1 VALUES ('2009-01-27'),('2009-01-31'),('2009-02-28'),('2010-01-13');
      EXPLAIN EXTENDED SELECT COUNT(*) FROM t1 WHERE c2='2009-01-32' OR c2='2009-01-30' OR c2='2009-04-31' OR c2='2009-09-31';
      id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
      1	SIMPLE	t1	range	c2	c2	4	NULL	3	100.00	Using where; Using index
      Warnings:
      Note	1003	select count(0) AS `COUNT(*)` from `test`.`t1` where ((`test`.`t1`.`c2` = '2009-01-32') or (`test`.`t1`.`c2` = '2009-01-30') or (`test`.`t1`.`c2` = '2009-04-31') or (`test`.`t1`.`c2` = '2009-09-31'))
      SELECT COUNT(*) FROM t1 WHERE c2='2009-01-32' OR c2='2009-01-30' OR c2='2009-04-31' OR c2='2009-09-31';
      COUNT(*)
      0

      However if I modify the test case just a little bit, e.g. remove one value from the table (or from the OR list), so that the type changes from range to index, the warning shows up:

       
      DROP TABLE IF EXISTS t1,t2;
      CREATE TABLE t1(c2 DATE NULL, INDEX(c2));
      INSERT INTO t1 VALUES ('2009-01-27'),('2009-01-31'),('2009-02-28'),('2010-01-13');
      EXPLAIN EXTENDED SELECT COUNT(*) FROM t1 WHERE c2='2009-01-32' OR c2='2009-01-30' OR c2='2009-09-31';
      id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
      1	SIMPLE	t1	index	c2	c2	4	NULL	4	50.00	Using where; Using index
      Warnings:
      Note	1003	select count(0) AS `COUNT(*)` from `test`.`t1` where ((`test`.`t1`.`c2` = '2009-01-32') or (`test`.`t1`.`c2` = '2009-01-30') or (`test`.`t1`.`c2` = '2009-09-31'))
      SELECT COUNT(*) FROM t1 WHERE c2='2009-01-32' OR c2='2009-01-30' OR c2='2009-09-31';
      COUNT(*)
      0
      Warnings:
      Warning	1292	Incorrect datetime value: '2009-01-32'

      Below is another example, now with signed int values. When the field with index is compared to the number, the warning is produced, while if a field of the same type, but without an index, is compared to the same constant, the warning is missing. Unlike in the first example where the date is obviously incorrect, here I am not even sure whether the warning is supposed to be produced or not, but apparently it should at least be consistent.

      CREATE TABLE t1(c1 INT SIGNED, c2 INT SIGNED, INDEX(c2));
      INSERT INTO t1 VALUES(106,106),(107,107);
      EXPLAIN EXTENDED
      SELECT * FROM t1 WHERE c2=2147483648;
      id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
      1	SIMPLE	t1	ref	c2	c2	5	const	1	100.00	Using index condition
      Warnings:
      Warning	1264	Out of range value for column 'c2' at row 1
      Note	1003	select `test`.`t1`.`c1` AS `c1`,`test`.`t1`.`c2` AS `c2` from `test`.`t1` where (`test`.`t1`.`c2` = 2147483648)
      SELECT * FROM t1 WHERE c2=2147483648;
      c1	c2
      Warnings:
      Warning	1264	Out of range value for column 'c2' at row 1
      EXPLAIN EXTENDED
      SELECT * FROM t1 WHERE c1=2147483648;
      id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
      1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
      Warnings:
      Note	1003	select `test`.`t1`.`c1` AS `c1`,`test`.`t1`.`c2` AS `c2` from `test`.`t1` where (`test`.`t1`.`c1` = 2147483648)
      SELECT * FROM t1 WHERE c1=2147483648;
      c1	c2

      Attachments

        Issue Links

          Activity

            People

              serg Sergei Golubchik
              elenst Elena Stepanova
              Votes:
              0 Vote for this issue
              Watchers:
              1 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.