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
-