Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.2.18, 10.3.9
-
None
-
ubuntu 18
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.
Attachments
Issue Links
- relates to
-
-
- Closed
-
Activity
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.
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'