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

Inconsistent Results When Using NULLIF

    XMLWordPrintable

Details

    Description

      There is an inconsistency in the results when using the NULLIF function on a BIT and FLOAT column in the same query versus storing the result in another table.

      Set up table t0:

      CREATE TABLE t0 (c0 BIT, c1 FLOAT);
      INSERT INTO t0 VALUES (1, 6.26034297557673e+37);
      

      Use NULLIF in a SELECT query:

      SELECT (NULLIF(t0.c0, t0.c1)) AS c0 FROM t0;
      --Result: 49
      

      Store the result of NULLIF in another table t1 and SELECT:

      CREATE TABLE t1 AS (SELECT (NULLIF(t0.c0, t0.c1)) AS c0 FROM t0);
      SELECT * FROM t1;
      --Result: 1
      

      Can also see: https://dbfiddle.uk/4sKLsbL9

      The result should be consistent regardless of whether the result is directly selected or stored in another table.

      Attachments

        Issue Links

          Activity

            People

              bar Alexander Barkov
              dwenking chaos
              Votes:
              0 Vote for this issue
              Watchers:
              3 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.