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

            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() }

            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.