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

CREATE VIEW materialization changes byte-valued rows in non-set-operation query

    XMLWordPrintable

Details

    • Not for Release Notes

    Description

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

      How to repeat:

       
      		 
      CREATE TABLE t2 (c12 BIT(8));
      		 
      INSERT INTO t2 VALUES (b'10101101');
      		 
       
      		 
      -- Source Original SQL
      		 
      SELECT GROUP_CONCAT(col1 ORDER BY col1 SEPARATOR ',') AS col_1
      		 
      FROM (SELECT c12 AS col1 FROM t2 GROUP BY c12) x;
      		 
       
      		 
      -- View SQL
      		 
      CREATE VIEW v AS SELECT c12 AS col1 FROM t2 GROUP BY c12;
      		 
       
      		 
      -- Mutated SQL
      		 
      SELECT GROUP_CONCAT(col1 ORDER BY col1 SEPARATOR ',') AS col_1
      		 
      FROM (SELECT col1 FROM v) x;

      Observed result - Query A (original):

      col_1
      173

      Observed result - Query B (mutated):

      col_1
      \xad

      Attachments

        Issue Links

          is duplicated by

          Bug - A problem which impairs or prevents the functions of the product. MDEV-39579 CREATE VIEW materialization changes byte-valued rows in UNION query

          • Major - Major loss of function.
          • Closed
          relates to

          Bug - A problem which impairs or prevents the functions of the product. MDEV-9331 Inconsistency between ALTER from BIT to VARCHAR and from BIT to TEXT

          • Minor - Minor loss of function, or other problem where easy workaround is present.
          • Confirmed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-39397 Wrong result after view extraction for GROUP_CONCAT(BIT(8)) in a window function query

          • Major - Major loss of function.
          • In Review
          has action item

          MSQA-96 Loading...

          Activity

            People

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