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

Inconsistent Results When Using NULLIF

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

          is duplicated by

          Bug - A problem which impairs or prevents the functions of the product. MDEV-34797 Inconsistency in the results when using the COALESCE function

          • Major - Major loss of function.
          • Closed

          Activity

            Ascending order - Click to sort in descending order
            dwenking chaos created issue -
            dwenking chaos made changes -
            Field Original Value New Value
            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:

            {code:sql}
            CREATE TABLE t0 (c0 BIT, c1 FLOAT);
            INSERT INTO t0 VALUES (1, 6.26034297557673e+37);
            {code}

            Use NULLIF in a SELECT query:

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

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

            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.
            		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:

            {code:sql}
            CREATE TABLE t0 (c0 BIT, c1 FLOAT);
            INSERT INTO t0 VALUES (1, 6.26034297557673e+37);
            {code}

            Use NULLIF in a SELECT query:

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

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

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

            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.
            serg Sergei Golubchik made changes -
            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:

            {code:sql}
            CREATE TABLE t0 (c0 BIT, c1 FLOAT);
            INSERT INTO t0 VALUES (1, 6.26034297557673e+37);
            {code}

            Use NULLIF in a SELECT query:

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

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

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

            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.
            		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:

            {code:sql}
            CREATE TABLE t0 (c0 BIT, c1 FLOAT);
            INSERT INTO t0 VALUES (1, 6.26034297557673e+37);
            {code}

            Use NULLIF in a SELECT query:

            {code:sql}
            SELECT ASCII(NULLIF(t0.c0, t0.c1)) AS c0 FROM t0;
            --Result: 49
            {code}

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

            {code:sql}
            CREATE TABLE t1 AS (SELECT (NULLIF(t0.c0, t0.c1)) AS c0 FROM t0);
            SELECT ASCII(c0) FROM t1;
            --Result: 1
            {code}

            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.
            serg Sergei Golubchik made changes -
            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:

            {code:sql}
            CREATE TABLE t0 (c0 BIT, c1 FLOAT);
            INSERT INTO t0 VALUES (1, 6.26034297557673e+37);
            {code}

            Use NULLIF in a SELECT query:

            {code:sql}
            SELECT ASCII(NULLIF(t0.c0, t0.c1)) AS c0 FROM t0;
            --Result: 49
            {code}

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

            {code:sql}
            CREATE TABLE t1 AS (SELECT (NULLIF(t0.c0, t0.c1)) AS c0 FROM t0);
            SELECT ASCII(c0) FROM t1;
            --Result: 1
            {code}

            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.
            		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:

            {code:sql}
            CREATE TABLE t0 (c0 BIT, c1 FLOAT);
            INSERT INTO t0 VALUES (1, 6.26034297557673e+37);
            {code}

            Use NULLIF in a SELECT query:

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

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

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

            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.
            serg Sergei Golubchik added a comment -

            it's not 49 and 1, it's CHAR(49) and CHAR(1), for some reason dbfiddle shows ascii code of the result. Doesn't matter, the result is still different.

            This stems from different behavior of bit_field->val_str() (which is Field_bit::val_str() and bit_expr->val_str() which is

            • Field_bit::val_int()
            • ...
            • Item_func_hybrid_field_type::val_str_from_int_op()
            • Type_handler_int_result::Item_func_hybrid_field_type_val_str()
            • Item_func_hybrid_field_type::val_str()}
            serg Sergei Golubchik added a comment - it's not 49 and 1, it's CHAR(49) and CHAR(1), for some reason dbfiddle shows ascii code of the result. Doesn't matter, the result is still different. This stems from different behavior of bit_field->val_str() (which is Field_bit::val_str() and bit_expr->val_str() which is Field_bit::val_int() ... Item_func_hybrid_field_type::val_str_from_int_op() Type_handler_int_result::Item_func_hybrid_field_type_val_str() Item_func_hybrid_field_type::val_str() }
            serg Sergei Golubchik made changes -
            Affects Version/s 10.5 [ 23123 ]
            Affects Version/s 10.11 [ 27614 ]
            Affects Version/s 11.4 [ 29301 ]
            serg Sergei Golubchik made changes -
            Fix Version/s 10.5 [ 23123 ]
            Fix Version/s 10.6 [ 24028 ]
            Fix Version/s 10.11 [ 27614 ]
            Fix Version/s 11.4 [ 29301 ]
            serg Sergei Golubchik made changes -
            Status Open [ 1 ] Confirmed [ 10101 ]
            serg Sergei Golubchik made changes -
            Assignee Alexander Barkov [ bar ]
            serg Sergei Golubchik made changes -
            serg Sergei Golubchik made changes -
            Component/s Data types [ 13906 ]
            Component/s Server [ 13907 ]

            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.