Mariadb is giving warning for this query and table

mariadb

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'

Ideally, this should not have happened. Can someone explain what's wrong here?

Update
This 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.

Best Answer

You expect the subselect (select meta_value from test3 where meta_key='lead_date') to materialize only rows that contain values in meta_value that can be converted to the DATE data type, but this is not happening: the optimizer inlines the subselect and simultaneously applies two predicates: meta_key='lead_date' and date(meta_value)<CURDATE() to all rows in the table, which obviously fails for rows containing 'facebook' in meta_value.

Related Question