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

Unknown column error when adding aggregate to function in oracle style procedure FOR loop

    Details

    • Sprint:
      10.2.11

      Description

      Using this simple setup ...

      SET SQL_MODE='ORACLE';
       
      CREATE TABLE t1(
        id int primary key,
        val int
      );
      

      ... the following query works fine when used standalone ...

      SELECT * FROM (
        SELECT id, SUM(val) as sum_val FROM t1) a
        WHERE NOT EXISTS (SELECT * FROM ( SELECT id FROM t1) b
                           WHERE a.id = b.id
                    ) 
      ;
      

      ... but not when used in a procedure / FOR loop context ...

      delimiter //
       
      CREATE PROCEDURE p1
      AS
      BEGIN
      FOR rec in (
      SELECT * FROM (
        SELECT id, SUM(val) as sum_val FROM t1) a
        WHERE NOT EXISTS (SELECT * FROM ( SELECT id FROM t1) b
                           WHERE a.id = b.id
                     ) 
      )
      LOOP
      SELECT 1;
      END LOOP;
      END;
      //
      DELIMITER ;
      

      ... resulting in ...

      mysql> CALL p1();
      ERROR 1054 (42S22): Unknown column 'a.id' in 'where clause'
      

      Removing just the SUM() aggregate part makes the query/procedure work:

      delimiter //
       
      CREATE PROCEDURE p2
      AS
      BEGIN
      FOR rec in (
      SELECT * FROM (
        SELECT id FROM t1) a 
        WHERE NOT EXISTS (SELECT * FROM ( SELECT id FROM t1) b
                           WHERE a.id = b.id
                     ) 
      )
      LOOP
      SELECT 2;
      END LOOP;
      END;
      //
       
      DELIMITER ;
      

      mysql> CALL p2();
      Query OK, 0 rows affected (0,00 sec)
      

        Attachments

          Activity

            People

            • Assignee:
              sanja Oleksandr Byelkin
              Reporter:
              hholzgra Hartmut Holzgraefe
            • Votes:
              0 Vote for this issue
              Watchers:
              5 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: