[MDEV-13273] Confusion between table alias and ROW type variable Created: 2017-07-07  Updated: 2017-07-07  Resolved: 2017-07-07

Status: Closed
Project: MariaDB Server
Component/s: Parser, Stored routines
Affects Version/s: 10.3
Fix Version/s: 10.3.1

Type: Bug Priority: Major
Reporter: Alexander Barkov Assignee: Alexander Barkov
Resolution: Fixed Votes: 0
Labels: regression


 Description   

This script:

SET sql_mode=DEFAULT;
 
CREATE OR REPLACE TABLE t1 (c1 INT, c2 INT);
 
INSERT INTO t1 VALUES (0,0);
 
DELIMITER $$
 
DROP PROCEDURE IF EXISTS p1;
 
CREATE PROCEDURE p1()
 
BEGIN
 
  DECLARE a INT;
 
  DECLARE b INT;
 
  SELECT a.c1 INTO b
 
    FROM t1 a
 
   WHERE a.c2 = 0;
 
END;
 
$$
 
DELIMITER ;
 
CALL p1;

works fine in 10.2. It treats a.c2 as a reference to the column a.c2, which is in fact t1.c2.

Starting from bb-10.2-ext and 10.3, the same script fails to create the procedure with this error:

ERROR 1105 (HY000): 'a' is not a row variable



 Comments   
Comment by Alexander Barkov [ 2017-07-07 ]

Oracle treats a.c2 as the table column reference if a non-RECORD variable a exists:

SET SERVEROUTPUT ON;
 
DROP TABLE t1;
 
CREATE TABLE t1 (c1 INT, c2 INT);
 
INSERT INTO t1 VALUES (0,0);
 
DROP PROCEDURE p1;
 
CREATE PROCEDURE p1
 
AS
 
  a INT := 100;
 
  b INT := 100;
 
BEGIN
 
  SELECT a.c1 INTO b
 
    FROM t1 a
 
   WHERE a.c2 = 0;
 
  DBMS_OUTPUT.PUT_LINE('b=' || b);
 
END;
 
/
 
CALL p1();


b=0

Oracle treats a.c2 as a RECORD variable field, if the RECORD variable a exists:

SET SERVEROUTPUT ON;
 
DROP TABLE t1;
 
CREATE TABLE t1 (c1 INT, c2 INT);
 
INSERT INTO t1 VALUES (0,0);
 
DROP PROCEDURE p1;
 
CREATE PROCEDURE p1
 
AS
 
  TYPE rec_t IS RECORD (c1 INT, c3 INT);
 
  a rec_t;
 
  b INT := 1000;
 
BEGIN
 
  a.c1:= 10;
 
  a.c2:= 20;
 
  SELECT a.c1 INTO b
 
    FROM t1 a
 
   WHERE a.c2 = 0;
 
  DBMS_OUTPUT.PUT_LINE('b=');
 
END;
 
/
 
SHOW ERRORS;


12/5	 PLS-00302: component 'C2' must be declared


SET SERVEROUTPUT ON;
 
DROP TABLE t1;
 
CREATE TABLE t1 (c1 INT, c3 INT);
 
INSERT INTO t1 VALUES (0,0);
 
DROP PROCEDURE p1;
 
CREATE PROCEDURE p1
 
AS
 
  a t1%ROWTYPE;
 
  b INT := 1000;
 
BEGIN
 
  a.c1:= 10;
 
  a.c2:= 20;
 
  SELECT a.c1 INTO b
 
    FROM t1 a
 
   WHERE a.c2 = 0;
 
  DBMS_OUTPUT.PUT_LINE('b=');
 
END;
 
/
 
SHOW ERRORS;


9/3	 PL/SQL: Statement ignored
 
9/5	 PLS-00302: component 'C2' must be declared
 
11/3	 PL/SQL: SQL Statement ignored
 
15/10	 PL/SQL: ORA-00904: "A"."C2": invalid identifier
 
15/12	 PLS-00302: component 'C2' must be declared

Comment by Alexander Barkov [ 2017-07-07 ]

PostgreSQL interprets a.c1 as a table column, if a is a scalar variable:

DROP TABLE IF EXISTS t1;
 
CREATE TABLE t1 (c1 INT, c2 INT);
 
DROP FUNCTION f1();
 
CREATE FUNCTION f1() RETURNS INT AS $$
 
DECLARE
 
  a INT;
 
  b INT;
 
BEGIN
 
  SELECT a.c1 INTO b
 
    FROM t1 a
 
   WHERE a.c2 = 0;
 
  RAISE NOTICE 'b=%',b;
 
  RETURN 0;
 
END; $$
 
LANGUAGE plpgsql;
 
SELECT f1();
 
DROP FUNCTION f1();
 
DROP TABLE t1;


NOTICE:  b=<NULL>

PostgreSQL returns an error, complaining that a.c1 is ambiguous, if a is a ROWTYPE variable:

DROP TABLE IF EXISTS t1;
 
CREATE TABLE t1 (c1 INT, c3 INT);
 
DROP FUNCTION f1();
 
CREATE FUNCTION f1() RETURNS INT AS $$
 
DECLARE
 
  a t1%ROWTYPE;
 
  b INT;
 
BEGIN
 
  SELECT a.c1 INTO b
 
    FROM t1 a
 
   WHERE a.c2 = 0;
 
  RETURN 0;
 
END; $$
 
LANGUAGE plpgsql;
 
SELECT f1();
 
DROP FUNCTION f1();
 
DROP TABLE t1;


ERROR:  column reference "a.c1" is ambiguous
 
LINE 1: SELECT a.c1             FROM t1 a
 
               ^
 
DETAIL:  It could refer to either a PL/pgSQL variable or a table column.
 
QUERY:  SELECT a.c1             FROM t1 a

Comment by Alexander Barkov [ 2017-07-07 ]

Pushed to bb-10.2-ext

Generated at Thu Feb 08 08:04:17 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.