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

greatest and coalesce on null date does not produce null

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Minor
    • Resolution: Fixed
    • 5.5.40, 10.0.14
    • N/A
    • OTHER
    • None

    Description

      I create a table and insert one row like this:

      create table datetest (nr decimal(1), date1 date);
      insert into datetest values (1,null);

      The bug can be reproduced with this statement:

      least(coalesce(date1), coalesce(date1)) 

      this should return null, but it doesnt.
      E.g.

      select * from datetest where least(coalesce(date1), coalesce(date1)) is not null; 

      ==> Returns one row

      It is also possible to insert this "corrupt null" into a table:

      insert into datetest select 2, least(coalesce(date1), coalesce(date1))  from datetest;

      if you select from this table with a select * from datetest where date1 is not null, you will get the row with nr=2

      Additional info:
      I get the same error using "greatest" instead of "least".
      I get the same error if I use more than one parameter for coalesce.
      I do not get this error if I use another datataype, like varchar instead of date.
      I do not get this error on another database like oracle 11g.

      Attachments

        Activity

          People

            bar Alexander Barkov
            Sektat Marcel Schneider
            Votes:
            1 Vote for this issue
            Watchers:
            4 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Git Integration

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