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

Inconsistent name resolution with subquery in HAVING clause

Details

    • Bug
    • Status: Stalled (View Workflow)
    • Minor
    • Resolution: Unresolved
    • 10.2(EOL), 10.3(EOL), 10.4(EOL), 10.5, 10.6
    • 10.5, 10.6, 10.11, 11.4
    • Optimizer, Views
    • None

    Description

      Set to Minor because the queries are silly, probably both are incorrect and should be rejected.

      CREATE TABLE t (a TEXT);
      		 
      CREATE VIEW v AS SELECT * FROM t;
      		 
       
      		 
      INSERT INTO t VALUES ('{}'),('[]');
      		 
       
      		 
      SELECT COUNT(*) FROM v HAVING (SELECT o FROM JSON_TABLE(v.a, '$' COLUMNS(o FOR ORDINALITY)) AS jt);
      		 
      SELECT COUNT(*) FROM t HAVING (SELECT o FROM JSON_TABLE(t.a, '$' COLUMNS(o FOR ORDINALITY)) AS jt);
      		 
       
      		 
      # Cleanup
      		 
      DROP VIEW v;
      		 
      DROP TABLE t;

      The difference between two SELECTs is that the first one uses the view, while the second one uses the table.

      The first one works, the second one fails:

      bb-10.6-mdev17399-hf 160bd1691

      		 
      MariaDB [test]> SELECT COUNT(*) FROM v HAVING (SELECT o FROM JSON_TABLE(v.a, '$' COLUMNS(o FOR ORDINALITY)) AS jt);
      		 
      +----------+
      		 
      | COUNT(*) |
      		 
      +----------+
      		 
      |        2 |
      		 
      +----------+
      		 
      1 row in set (0.002 sec)
      		 
       
      		 
      MariaDB [test]> SELECT COUNT(*) FROM t HAVING (SELECT o FROM JSON_TABLE(t.a, '$' COLUMNS(o FOR ORDINALITY)) AS jt);
      		 
      ERROR 1054 (42S22): Unknown column 'test.t.a' in 'JSON_TABLE argument'

      On MySQL both fail, although with different errors:

      MySQL 8.0.23

      		 
      MySQL [test]> SELECT COUNT(*) FROM v HAVING (SELECT o FROM JSON_TABLE(v.a, '$' COLUMNS(o FOR ORDINALITY)) AS jt);
      		 
      ERROR 1210 (HY000): Incorrect arguments to JSON_TABLE
      		 
       
      		 
      MySQL [test]> SELECT COUNT(*) FROM t HAVING (SELECT o FROM JSON_TABLE(t.a, '$' COLUMNS(o FOR ORDINALITY)) AS jt);
      		 
      ERROR 1054 (42S22): Unknown column 'test.t.a' in 'a table function argument'

      Attachments

        Activity

          Ascending order - Click to sort in descending order
          psergei Sergei Petrunia added a comment -

          elenst, one can easily create a testcase that shows the same behavior and does not use JSON_TABLE:

          create table t10 (a int);
          		 
          create table t11 (b int);
          		 
          create view v10 as select * from t10;
          		 
          SELECT COUNT(*) FROM t10 HAVING (SELECT 1 from t11 where t10.a=t11.b);
          		 
          ERROR 42S22: Unknown column 'test.t10.a' in 'where clause'
          		 
          SELECT COUNT(*) FROM v10 HAVING (SELECT 1 from t11 where v10.a=t11.b);
          		 
          COUNT(*)

          psergei Sergei Petrunia added a comment - elenst , one can easily create a testcase that shows the same behavior and does not use JSON_TABLE: create table t10 (a int ); create table t11 (b int ); create view v10 as select * from t10; SELECT COUNT (*) FROM t10 HAVING ( SELECT 1 from t11 where t10.a=t11.b); ERROR 42S22: Unknown column 'test.t10.a' in 'where clause' SELECT COUNT (*) FROM v10 HAVING ( SELECT 1 from t11 where v10.a=t11.b); COUNT (*)
          psergei Sergei Petrunia added a comment - - edited

          Also, the second query doesn't honor the ONLY_FULL_GROUP_BY mode:

          set sql_mode='only_full_group_by';
          		 
          SELECT COUNT(*) FROM v10 HAVING (SELECT 1 from t11 where v10.a=t11.b);
          		 
          COUNT(*)

          psergei Sergei Petrunia added a comment - - edited Also, the second query doesn't honor the ONLY_FULL_GROUP_BY mode: set sql_mode= 'only_full_group_by' ; SELECT COUNT (*) FROM v10 HAVING ( SELECT 1 from t11 where v10.a=t11.b); COUNT (*)

          People

            sanja Oleksandr Byelkin
            elenst Elena Stepanova
            Votes:
            0 Vote for this issue
            Watchers:
            3 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.