Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-17342

Mariadb is giving warning for this query and table

    Details

    • Type: Bug
    • Status: Open (View Workflow)
    • Priority: Major
    • Resolution: Unresolved
    • Affects Version/s: 10.2.18, 10.3.9
    • Fix Version/s: 10.2, 10.3, 10.4
    • Component/s: Optimizer
    • Labels:
      None
    • Environment:
      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

            Activity

              People

              • Assignee:
                sanja Oleksandr Byelkin
                Reporter:
                amitsingh Amit Singh
              • Votes:
                0 Vote for this issue
                Watchers:
                5 Start watching this issue

                Dates

                • Created:
                  Updated: