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

CREATE VIEW materialization changes byte-valued rows in UNION query

    XMLWordPrintable

Details

    • Unexpected results

    Description

      Under semantic-equivalent rewriting, extracting the subquery into a view causes a stable byte-value drift in the UNION result.
      The source and mutated queries are logically equivalent, but produce different results.

      How to repeat:

      CREATE TABLE t1 (c4 INT);
      CREATE TABLE t2 (c12 BIT(8));
      INSERT INTO t1 VALUES (1);
      INSERT INTO t2 (c12) VALUES (b'01111110');
       
      -- Source Original SQL
      SELECT (SELECT c12 FROM t2) AS col_1 FROM t1 GROUP BY 1 UNION SELECT 24;
       
      -- View SQL
      CREATE VIEW v AS
      SELECT (SELECT c12 FROM t2) AS col_1 AS col_2
      FROM t1
      GROUP BY 1;
       
      -- Mutated SQL
      SELECT col_1 FROM v UNION SELECT 24;
      }
      

      Observed result - Query A (original):

      col_1
      126
      24

      Observed result - Query B (mutated):

      col_1
      255
      24

      Attachments

        Issue Links

          Activity

            People

              psergei Sergei Petrunia
              pyuan Peiyuan Liu
              Votes:
              0 Vote for this issue
              Watchers:
              3 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.