0

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.

4
  • 1
    The explain will tell you what's "wrong".
    – mustaccio
    Oct 1, 2018 at 16:17
  • 1
    check out this syntax, where date(meta_value)<CURDATE() I think the issue is here. What happens if you run the query without the WHERE clause?
    – htm11h
    Oct 1, 2018 at 17:10
  • when I run the query without where clause it seems to give a proper result, but what i want to understand what's wrong with the give where clause.
    – Amit Singh
    Oct 1, 2018 at 17:28
  • I am not able to emulate this unless if i remove the condition meta_key='lead_date' and i don't need to use a subquery to get it working fine. and without the warnings. So maybe your table contain more than this. Oct 1, 2018 at 17:45

2 Answers 2

1

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.

2
  • I am not able to emulate this unless if i remove the condition meta_key='lead_date' and i don't need to use a subquery to get it working fine. and without the warnings. So maybe the user table contain more than this. Oct 1, 2018 at 17:46
  • just updated the question to provide more details.
    – Amit Singh
    Oct 2, 2018 at 7:17
-1

Date format is '2018-09-15', not '20180915'.

Don't use subqueries when you don't need to.

select  meta_value
    from test3
    where  meta_key = 'lead_date'
    where  meta_value < '2020-01-01';

Have

PRIMARY KEY(meta_key)

In the long run, you will find EAV schema to be a pain.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.