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

MERGE/identity VIEW queried with two window functions and `GROUP BY` on unprefixed columns raises 1052

    XMLWordPrintable

Details

    • Can result in unexpected behaviour

    Description

      CREATE TABLE b (id BIGINT, name VARCHAR(255));
      INSERT INTO b VALUES (NULL, NULL), (2, 'abc'), (2, 'o''brien');
      CREATE VIEW t AS SELECT * FROM b;
       
      SELECT MIN(name) OVER (PARTITION BY id), MIN(id) OVER (ORDER BY name)
      FROM t
      GROUP BY id, name;
      -- ERROR 1052 (23000): Column 'id' in GROUP BY is ambiguous
      

      However, querying the base table succeeds:

      SELECT MIN(name) OVER (PARTITION BY id), MIN(id) OVER (ORDER BY name)
      FROM b
      GROUP BY id, name;
      

      Attachments

        Activity

          People

            psergei Sergei Petrunia
            junwenan Junwen An
            Votes:
            0 Vote for this issue
            Watchers:
            2 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.