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

Inconsistent name resolution with subquery in HAVING clause

    XMLWordPrintable

    Details

      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

            People

            Assignee:
            sanja Oleksandr Byelkin
            Reporter:
            elenst Elena Stepanova
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

              Dates

              Created:
              Updated: