Details
-
Bug
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Fixed
-
10.0, 10.1, 10.2, 10.3
-
None
-
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)
|