[MDEV-13273] Confusion between table alias and ROW type variable - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Major
Resolution: Fixed
Affects Version/s: 10.3(EOL)
Fix Version/s: 10.3.1
Component/s: Parser, Stored routines
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

Attachments

Activity

Ascending order - Click to sort in descending order

Alexander Barkov added a comment - 2017-07-07 07:47

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

Alexander Barkov added a comment - 2017-07-07 07:47 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

Alexander Barkov added a comment - 2017-07-07 08:00 - edited

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

Alexander Barkov added a comment - 2017-07-07 08:00 - edited 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

Alexander Barkov added a comment - 2017-07-07 13:10

Pushed to bb-10.2-ext

Alexander Barkov added a comment - 2017-07-07 13:10 Pushed to bb-10.2-ext

People

Assignee:: Alexander Barkov

Reporter:: Alexander Barkov

Votes:: 0 Vote for this issue

Watchers:: 1 Start watching this issue

Dates

Created:: 2017-07-07 07:24

Updated:: 2017-07-07 13:10

Resolved:: 2017-07-07 13:10

Git Integration

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.

MariaDB Server