The same problem is repeatable on an attempt to open the same cursor two times, without a cursor FOR loop.

SET sql_mode=ORACLE;

DELIMITER //

CREATE OR REPLACE PROCEDURE p1

AS

  CURSOR cur1 IS 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);

BEGIN

  OPEN cur1;

  CLOSE cur1;

  OPEN cur1;

  CLOSE cur1;

END;

//

DELIMITER ;

CALL p1();

The first sp_instr_copen::exec_core() works fine, but the second one fails.

The same problem is repeatable with sql_mode=DEFAULT in 10.0, 10.1, 10.2, 10.3.

SET sql_mode=DEFAULT;

CREATE OR REPLACE TABLE t1(id INT, val INT);

DROP PROCEDURE IF EXISTS p1;

DELIMITER //

CREATE PROCEDURE p1()

BEGIN

  DECLARE cur1 CURSOR FOR 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);

  OPEN cur1;

  CLOSE cur1;

  OPEN cur1;

  CLOSE cur1;

END;

//

DELIMITER ;

CALL p1();

Also repeatable with COUNT(val) and COUNT( * ) instead of SUM(val).

Note, if I remove the second pair of OPEN cur1 and CLOSE cur1, it works fine.

Also repeatable if I remove SUM from the inner SELECT, but add DISTINCT to id instead:

SET sql_mode=DEFAULT;

CREATE OR REPLACE TABLE t1(id INT, val INT);

DROP PROCEDURE IF EXISTS p1;

DELIMITER //

CREATE PROCEDURE p1()

BEGIN

  DECLARE cur1 CURSOR FOR SELECT * FROM (

  SELECT DISTINCT id FROM t1) a

  WHERE NOT EXISTS (SELECT * FROM ( SELECT id FROM t1) b

                    WHERE a.id=b.id);

  OPEN cur1;

  CLOSE cur1;

  OPEN cur1;

  CLOSE cur1;

END;

//

DELIMITER ;

CALL p1();

The same problem is repeatable without a CURSOR, if I do a direct SELECT inside the SP and call the SP two times:

SET sql_mode=DEFAULT;

DROP TABLE IF EXISTS t1;

CREATE TABLE t1(id INT, val INT);

DROP PROCEDURE IF EXISTS p1;

DELIMITER //

CREATE PROCEDURE p1()

BEGIN

  SELECT * FROM (

  SELECT DISTINCT id FROM t1) a

  WHERE NOT EXISTS (SELECT * FROM ( SELECT id FROM t1) b

                    WHERE a.id=b.id);

END;

//

DELIMITER ;

CALL p1();

CALL p1();

Notice, the first CALL works fine, while the second CALL fails on the same error:

ERROR 1054 (42S22): Unknown column 'a.id' in 'where clause'

Note, if I change the equality a.id=b.id to some other comparison predicate, e.g. to:

• a.id<=>b.id
• a.id<>b.id
• a.id<b.id
• a.id>b.id

it starts to work fine.

If I issue this command:

SET optimizer_switch='exists_to_in=off';

all problems disappear.

Test suite in format of test suite put here to avoid fixing it each time

CREATE TABLE t1(id INT, val INT);

DELIMITER //;

CREATE PROCEDURE p1()

BEGIN

  DECLARE cur1 CURSOR FOR SELECT * FROM (

  SELECT DISTINCT id FROM t1) a

  WHERE NOT EXISTS (SELECT * FROM ( SELECT id FROM t1) b

                    WHERE a.id=b.id);

  OPEN cur1;

  CLOSE cur1;

  OPEN cur1;

  CLOSE cur1;

END;

//

DELIMITER ;//

CALL p1();

DROP PROCEDURE p1;

DROP TABLE t1;

cause of the problem is <exists outer expr> which is not supposed to be reprepared but it is...

I was not right, the problem is in the field the <exists outer expr> refer

The problem is that name resolution context (made during pullout) refer on not opened table (alias b)

It do not depend on derived tables:

CREATE TABLE t1(id INT, val INT);

DELIMITER //;

CREATE PROCEDURE p1()

BEGIN

  SELECT * FROM (

  SELECT DISTINCT id FROM t1) a

  WHERE NOT EXISTS (SELECT * FROM t1 b WHERE a.id=b.id);

END;

//

DELIMITER ;//

CALL p1();

CALL p1();

DROP PROCEDURE p1;

DROP TABLE t1;

oops, above I removed not derived which causes problem

Actually distinct just make it non mergible, so problem is that there is materialazed derived and it is not processed

Converted to IN manually it works:

  SELECT * FROM (SELECT DISTINCT id FROM t1) a

  WHERE NOT a.id IN (SELECT b.id FROM t1 b);

Temporary table looks like created, but somehow Item points in other place

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.

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.

—