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

Inconsistency in the results when using the COALESCE function

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Duplicate
    • 10.6.0
    • N/A
    • Data types, Server
    • None

    Description

      There is an inconsistency in the results when using the COALESCE function.

      Set up table t0:

      CREATE TABLE t0 (c0 BIT, c1 BIT);
      		 
      INSERT INTO t0 VALUES (0, 1);

      Use COALESCE in a SELECT query:

      (SELECT (COALESCE(t0.c0, t0.c1)) AS c0 FROM t0);
      		 
      --Result: 48

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

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

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

      Attachments

        Issue Links

          duplicates

          Bug - A problem which impairs or prevents the functions of the product. MDEV-34792 Inconsistent Results When Using NULLIF

          • Major - Major loss of function.
          • Confirmed

          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 COALESCE function.

            Set up table t0:

            {code:sql}
            CREATE TABLE t0 (c0 BIT, c1 BIT);
            INSERT INTO t0 VALUES (0, 1);
            {code}

            Use COALESCE in a SELECT query:
            {code:sql}
            (SELECT (COALESCE(t0.c0, t0.c1)) AS c0 FROM t0);
            --Result: 48
            {code}

            Store the result of COALESCE in another table t1 and SELECT:
            {code:sql}
            CREATE TABLE t1 AS (SELECT (COALESCE(t0.c0, t0.c1)) AS c0 FROM t0);
            SELECT (COALESCE(t0.c0, t0.c1)) AS c0 FROM t0;
            --Result: 1
            {code}

            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 COALESCE function.

            Set up table t0:

            {code:sql}
            CREATE TABLE t0 (c0 BIT, c1 BIT);
            INSERT INTO t0 VALUES (0, 1);
            {code}

            Use COALESCE in a SELECT query:
            {code:sql}
            (SELECT (COALESCE(t0.c0, t0.c1)) AS c0 FROM t0);
            --Result: 48
            {code}

            Store the result of COALESCE in another table t1 and SELECT:
            {code:sql}
            CREATE TABLE t1 AS (SELECT (COALESCE(t0.c0, t0.c1)) AS c0 FROM t0);
            SELECT * FROM t1;
            --Result: 0
            {code}

            The result should be consistent regardless of whether the result is directly selected or stored in another table.
            dwenking chaos made changes -
            Affects Version/s 10.6.0 [ 24431 ]
            Affects Version/s 11.4.3 [ 29837 ]
            dwenking chaos made changes -
            Summary Inconsistent Results When Using COALESCE Nu
            dwenking chaos made changes -
            Summary Nu Inconsistency in the results when using the COALESCE function
            serg Sergei Golubchik made changes -
            serg Sergei Golubchik made changes -
            Component/s Data types [ 13906 ]
            Component/s Server [ 13907 ]
            Fix Version/s N/A [ 14700 ]
            Resolution Duplicate [ 3 ]
            Status Open [ 1 ] Closed [ 6 ]

            People

              Unassigned Unassigned
              dwenking chaos
              Votes:
              0 Vote for this issue
              Watchers:
              2 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.