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

Mariadb is giving warning for this query and table

    XMLWordPrintable

Details

    • Bug
    • Status: Open (View Workflow)
    • Major
    • Resolution: Unresolved
    • 10.2.18, 10.3.9
    • 10.4
    • Optimizer
    • 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

          Activity

            People

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

              Dates

                Created:
                Updated:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.