[MDEV-17342] Mariadb is giving warning for this query and table Created: 2018-10-02  Updated: 2023-04-27

Status: Open
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: 10.2.18, 10.3.9
Fix Version/s: 10.4

Type: Bug Priority: Major
Reporter: Amit Singh Assignee: Oleksandr Byelkin
Resolution: Unresolved Votes: 0
Labels: None
Environment:

ubuntu 18

Issue Links:
Relates
relates to MDEV-17563 Different results using table or view... Closed

 Description   

Please create the following table,

CREATE TABLE test3 (
 
 
 
  meta_key varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
 
  meta_value varchar(500) COLLATE utf8mb4_unicode_ci DEFAULT NULL
 
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
 
 
 
INSERT INTO test3 (meta_key, meta_value) VALUES
 
('utm_source', 'facebook'),
 
('lead_date', '20180915'),
 
('utm_source', 'facebook'),
 
('lead_date', '20180905');
 
then run the following query
 
 
 
select meta_value 
from (select meta_value 
      from test3 
      where meta_key='lead_date') as q0 
where date(meta_value)<CURDATE()   ;

you will get following warning while running them (use adminer to see them)

Level Code Message
Warning 1292 Incorrect datetime value: 'facebook'
Warning 1292 Incorrect datetime value: 'facebook'

his query is actually part of a bigger set of queries. It has been reduced to this level to highlight the warnings. The issue is not with CURDATE. if you write date(meta_value) is NOT NULL it will give warnings. In fact, it will give warnings with all date related functions in where clause.

It does not seem that it could be an optimization issue. Try the following queries with a join. At this point, the optimizer cannot optimize since I am comparing with another table. It will still give a warning.

drop table if exists test3;
 
CREATE TABLE test3 (
 
 
 
  meta_key varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
 
  meta_value varchar(500) COLLATE utf8mb4_unicode_ci DEFAULT NULL
 
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
 
 
 
INSERT INTO test3 (meta_key, meta_value) VALUES
 
('utm_source', 'facebook'),
 
('lead_date', '20180915'),
 
('utm_source', 'facebook'),
 
('lead_date', '20180905');
 
 
 
select meta_value 
from (select meta_value 
      from test3 
      where meta_key='lead_date') as q0 , (select date('20200101') as 
compare) as d
 
where date(meta_value)<compare  ;

Now try another query:

select meta_value 
from (select meta_value 
      from test3 
      where meta_key='lead_date' limit 1000000) as q0 
where date(meta_value)<CURDATE()  ;

This will not give an error.

 Comments   
Comment by Alice Sherepa [ 2018-10-02 ]

Could you please explain why you consider this as a bug.
In the first case, optimizer transforms the query to the equivalent query and there 2 warnings, while casting string 'facebook'

MariaDB [test]> explain extended select meta_value from (select meta_value from test3 where meta_key='lead_date') as q0   where date(meta_value)<CURDATE();
 
+------+-------------+-------+------+---------------+------+---------+------+------+----------+-------------+
 
| id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
 
+------+-------------+-------+------+---------------+------+---------+------+------+----------+-------------+
 
|    1 | SIMPLE      | test3 | ALL  | NULL          | NULL | NULL    | NULL |    4 |   100.00 | Using where |
 
+------+-------------+-------+------+---------------+------+---------+------+------+----------+-------------+
 
1 row in set, 1 warning (0.001 sec)
 
 
 
Note (Code 1003): select `test`.`test3`.`meta_value` AS `meta_value` from `test`.`test3` where cast(`test`.`test3`.`meta_value` as date) < <cache>(curdate()) and `test`.`test3`.`meta_key` = 'lead_date'
 
 
 
MariaDB [test]> select meta_value from (select meta_value from test3 where meta_key='lead_date') as q0 where date(meta_value)<CURDATE();
 
+------------+
 
| meta_value |
 
+------------+
 
| 20180915   |
 
| 20180905   |
 
+------------+
 
2 rows in set, 2 warnings (0.001 sec)
 
Warning (Code 1292): Incorrect datetime value: 'facebook'
 
Warning (Code 1292): Incorrect datetime value: 'facebook'

The second query can not be transformed the same way because of the limit, so derived table is used (and after performing (where meta_key='lead_date') there are no incorrect datetime values left), so the query does not return warnings.

 
 
MariaDB [test]> explain extended select meta_value from (select meta_value from test3 where meta_key='lead_date' limit 1000000) as q0   where date(meta_value)<CURDATE();
 
+------+-------------+------------+------+---------------+------+---------+------+------+----------+-------------+
 
| id   | select_type | table      | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
 
+------+-------------+------------+------+---------------+------+---------+------+------+----------+-------------+
 
|    1 | PRIMARY     | <derived2> | ALL  | NULL          | NULL | NULL    | NULL |    4 |   100.00 | Using where |
 
|    2 | DERIVED     | test3      | ALL  | NULL          | NULL | NULL    | NULL |    4 |   100.00 | Using where |
 
+------+-------------+------------+------+---------------+------+---------+------+------+----------+-------------+
 
2 rows in set, 1 warning (0.001 sec)
 
 
 
Note (Code 1003): /* select#1 */ select `q0`.`meta_value` AS `meta_value` from (/* select#2 */ select `test`.`test3`.`meta_value` AS `meta_value` from `test`.`test3` where `test`.`test3`.`meta_key` = 'lead_date' limit 1000000) `q0` where cast(`q0`.`meta_value` as date) < <cache>(curdate())
 
 
 
MariaDB [test]> select meta_value from (select meta_value from test3 where meta_key='lead_date' limit 1000000) as q0   where date(meta_value)<CURDATE();
 
+------------+
 
| meta_value |
 
+------------+
 
| 20180915   |
 
| 20180905   |
 
+------------+
 
2 rows in set (0.001 sec)
 
 
 
MariaDB [test]> insert into test3 values ('lead_date','facebook');
 
Query OK, 1 row affected (0.001 sec)
 
 
 
MariaDB [test]> select meta_value from (select meta_value from test3 where meta_key='lead_date' limit 1000000) as q0   where date(meta_value)<CURDATE();
 
+------------+
 
| meta_value |
 
+------------+
 
| 20180915   |
 
| 20180905   |
 
+------------+
 
2 rows in set, 1 warning (0.000 sec)
 
 
 
Warning (Code 1292): Incorrect datetime value: 'facebook'

Comment by Alexander Barkov [ 2018-11-08 ]

If I try EXPLAIN EXTENDED for this query:

select meta_value 
from (select meta_value 
      from test3 
      where meta_key='lead_date') as q0 
where date(meta_value)<CURDATE()   ;

It gets rewritten to:

select meta_value
 
  from test3
 
where
 
  cast(meta_value as date) < <cache>(curdate())
 
 and
 
  meta_key = 'lead_date' ;

So the condition with meta_value is evaluated first, hence the warning.

This does not look correct. The query should probably be rewritten as:

select meta_value
 
  from test3
 
where
 
  meta_key = 'lead_date'
 
 and
 
  cast(meta_value as date) < <cache>(curdate());

to make the condition on meta_key evaluate first, as in the original query.

sanja, can you please have a look into this? Thanks.

Generated at Thu Feb 08 08:35:44 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.