Details
-
Bug
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Fixed
-
10.0(EOL), 10.1(EOL), 10.2(EOL), 10.3(EOL)
-
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)
|
Attachments
Activity
Name resolution created during "pull-out". It advance next name resolution but uses original table list...
Try to use table list of the place where it was break other tests.
The problem is that fix_after_pullout() used for 2 purposes:
1) converting from semijoin and it should kind of "keep" context, but remove one level in previous context
2) pulling out of subquery (should keep context completely or move everything up)
github tree: bb-10.0-MDEV-14164
revision-id: cfb314a13b4187e8bb2d3b2ba0f48270e07c61be (mariadb-10.0.33-10-gcfb314a13b4)
parent(s): 6a524fcfdde539c6448aa4126ccb5ed79055b9ce
author: Oleksandr Byelkin
committer: Oleksandr Byelkin
timestamp: 2017-11-08 15:47:49 +0100
message:
MDEV-14164: Unknown column error when adding aggregate to function in oracle style procedure FOR loop
Make differentiation between pullout for merge and pulout of outer field during exists2in transformation.
In last case the field was outer and so we can safely starrt from name resolution context of the SELECT where it was pulled.
Old behaviour lead to inconsistence between list of tables and outer name resolution context (which skipps one SELECT for merge purposes) which cretes problem of name resolution.
—
Name resolution context of a.id field refers to table b (which is OK, because it was in the inner query) but upper name resolution context set to NULL which prevent resolving.