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

Replacing a base table with an equivalent passthrough view makes a valid grouped expression query fail under `ONLY_FULL_GROUP_BY`

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Duplicate
    • 10.11, 11.4, 11.8, 12.3, 11.4.10
    • N/A
    • Optimizer
    • Not for Release Notes

    Description

      Under semantic-equivalent rewriting, extracting one join input into a passthrough view causes MariaDB to reject the rewritten query with `ERROR 1055`.
      The source and mutated queries are logically equivalent, but MariaDB accepts the source and rejects the mutated form.

      SET SESSION sql_mode = 'ONLY_FULL_GROUP_BY';
       
      DROP VIEW IF EXISTS V_t2_case07;
      DROP TABLE IF EXISTS t2;
      DROP TABLE IF EXISTS t3;
       
      CREATE TABLE t2 (
        c1 INT,
        c7 INT
      );
       
      CREATE TABLE t3 (
        c3 INT,
        c12 INT
      );
       
      CREATE VIEW V_t2_case07 AS
      SELECT c1, c7
      FROM t2;
       
      -- Source Original SQL
      SELECT t2.c7 + t3.c12 AS x
      FROM t2
      JOIN t3 ON t3.c3 = t2.c1
      GROUP BY t2.c7 + t3.c12;
       
      -- Mutated SQL
      SELECT V_t2_case07.c7 + t3.c12 AS x
      FROM V_t2_case07
      JOIN t3 ON t3.c3 = V_t2_case07.c1
      GROUP BY V_t2_case07.c7 + t3.c12;
      

      Observed result - Query A (original):

      Empty set

      Observed result - Query B (mutated):

      ERROR 1055 (42000): 'test.t3.c12' isn't in GROUP BY

      Attachments

        Issue Links

          Activity

            People

              Unassigned Unassigned
              pyuan Peiyuan Liu
              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.