[MDEV-14347] CREATE PROCEDURE returns no error when using an unknown variable - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Major
Resolution: Fixed
Affects Version/s: 5.5(EOL), 10.0(EOL), 10.1(EOL), 10.2(EOL), 10.3(EOL), 10.4(EOL)
Fix Version/s: 10.5.4
Component/s: Stored routines
Labels:
None

Description

DELIMITER $$

CREATE OR REPLACE PROCEDURE p1(a INT)

BEGIN

  DECLARE res INT DEFAULT 0;

  IF (a < 0) THEN

    SET res=x;

  END IF;

END;

$$

DELIMITER ;

Now if I call the procedure as follows:

CALL p1(0);

it still returns no errors.

It only returns an error if the condition in IF evaluates to true:

call p1(-1);

ERROR 1054 (42S22): Unknown column 'x' in 'field list'

This behavior makes it very difficult to design stored procedures.
The error should happen during CREATE PROCEDURE time.

In this context, x can only be a variable. It cannot be a column because no tables are involved.

Attachments

Issue Links

relates to

MDEV-11953 support of brackets (parentheses) in UNION/EXCEPT/INTERSECT operations

Closed

MDEV-22774 Invalid use of aggregate function in SP is not detected at CREATE time

Open

Activity

Ascending order - Click to sort in descending order

Alexander Barkov created issue - 2017-11-10 11:36

Alexander Barkov made changes - 2017-11-10 11:36

Field	Original Value	New Value
Rank		Ranked higher

Alexander Barkov made changes - 2017-11-10 11:37

Description

{code:sql}
DELIMITER $$
CREATE OR REPLACE PROCEDURE p1(a INT)
BEGIN
  DECLARE res INT DEFAULT 0;
  IF (a < 0) THEN
    SET res=x;
  END IF;
END;
$$
DELIMITER ;
{code}
Now if I call the procedure as follows:
{code:sql}
CALL p1(0);
{code}
it still returns no errors.

It only returns an error if the condition in {{IF}} evaluates to {{true}}:
{code:sql
call p(-1);
{code}
{noformat}
ERROR 1054 (42S22): Unknown column 'x' in 'field list'
{noformat}

This behavior makes it very difficult to design stored procedures.
The error should happen during {{CREATE PROCEDURE}} time.

In this context, {{x}} can only be a variable. It cannot be a column because no tables are involved.

{code:sql}
DELIMITER $$
CREATE OR REPLACE PROCEDURE p1(a INT)
BEGIN
  DECLARE res INT DEFAULT 0;
  IF (a < 0) THEN
    SET res=x;
  END IF;
END;
$$
DELIMITER ;
{code}
Now if I call the procedure as follows:
{code:sql}
CALL p1(0);
{code}
it still returns no errors.

It only returns an error if the condition in {{IF}} evaluates to {{true}}:
{code:sql}
call p(-1);
{code}
{noformat}
ERROR 1054 (42S22): Unknown column 'x' in 'field list'
{noformat}

This behavior makes it very difficult to design stored procedures.
The error should happen during {{CREATE PROCEDURE}} time.

In this context, {{x}} can only be a variable. It cannot be a column because no tables are involved.

Alexander Barkov made changes - 2018-04-16 07:57

Link

This issue relates to ~~MDEV-11953~~ [ ~~MDEV-11953~~ ]

Alexander Barkov made changes - 2018-04-16 07:57

Status

Open [ 1 ]

In Progress [ 3 ]

Alexander Barkov made changes - 2018-06-19 15:15

Fix Version/s		10.4 [ 22408 ]
Fix Version/s	10.3 [ 22126 ]

Alexander Barkov made changes - 2018-12-27 06:03

Description

{code:sql}
DELIMITER $$
CREATE OR REPLACE PROCEDURE p1(a INT)
BEGIN
  DECLARE res INT DEFAULT 0;
  IF (a < 0) THEN
    SET res=x;
  END IF;
END;
$$
DELIMITER ;
{code}
Now if I call the procedure as follows:
{code:sql}
CALL p1(0);
{code}
it still returns no errors.

It only returns an error if the condition in {{IF}} evaluates to {{true}}:
{code:sql}
call p(-1);
{code}
{noformat}
ERROR 1054 (42S22): Unknown column 'x' in 'field list'
{noformat}

This behavior makes it very difficult to design stored procedures.
The error should happen during {{CREATE PROCEDURE}} time.

In this context, {{x}} can only be a variable. It cannot be a column because no tables are involved.

{code:sql}
DELIMITER $$
CREATE OR REPLACE PROCEDURE p1(a INT)
BEGIN
  DECLARE res INT DEFAULT 0;
  IF (a < 0) THEN
    SET res=x;
  END IF;
END;
$$
DELIMITER ;
{code}
Now if I call the procedure as follows:
{code:sql}
CALL p1(0);
{code}
it still returns no errors.

It only returns an error if the condition in {{IF}} evaluates to {{true}}:
{code:sql}
call p1(-1);
{code}
{noformat}
ERROR 1054 (42S22): Unknown column 'x' in 'field list'
{noformat}

This behavior makes it very difficult to design stored procedures.
The error should happen during {{CREATE PROCEDURE}} time.

In this context, {{x}} can only be a variable. It cannot be a column because no tables are involved.

Alexander Barkov made changes - 2019-07-05 02:27

Fix Version/s		10.5 [ 23123 ]
Fix Version/s	10.4 [ 22408 ]

Alexander Barkov made changes - 2019-07-05 02:27

Affects Version/s

10.4 [ 22408 ]

Alexander Barkov made changes - 2020-02-06 10:21

Status

In Progress [ 3 ]

Stalled [ 10000 ]

Alexander Barkov made changes - 2020-06-02 06:19

Link

This issue relates to MDEV-22774 [ MDEV-22774 ]

Alexander Barkov made changes - 2020-06-02 06:20

Comment

[ The same problem is repeatable with aggregate functions:
{code:sql}
CREATE OR REPLACE FUNCTION f1() RETURNS INT RETURN SUM(1);
SELECT f1();
{code}
{noformat}
ERROR 1111 (HY000): Invalid use of group function
{noformat}

And window functions:
{code:sql}
CREATE OR REPLACE FUNCTION f1() RETURNS INT RETURN ROW_NUMBER() OVER();
SELECT f1();
{code}
{noformat}
ERROR 4015 (HY000): Window function is allowed only in SELECT list and ORDER BY clause
{noformat}

It should report the error at creation rather than execution time.
]

Alexander Barkov made changes - 2020-06-02 06:55

Assignee

Alexander Barkov [ bar ]

Oleksandr Byelkin [ sanja ]

Alexander Barkov made changes - 2020-06-03 03:27

Assignee

Oleksandr Byelkin [ sanja ]

Alexander Barkov [ bar ]

Alexander Barkov added a comment - 2020-06-03 03:30 - edited

More examples when an error is not returned:

DELIMITER $$

CREATE OR REPLACE PROCEDURE p1(a INT)

BEGIN

  DECLARE res INT DEFAULT 0;

  SET res=(SELECT 1 WHERE unknown);

END;

$$

DELIMITER ;

DELIMITER $$

CREATE OR REPLACE PROCEDURE p1(a INT)

BEGIN

  DECLARE res INT DEFAULT 0;

  SET res=(SELECT 1 HAVING unknown);

END;

$$

DELIMITER ;

DELIMITER $$

CREATE OR REPLACE PROCEDURE p1(a INT)

BEGIN

  DECLARE res INT DEFAULT 0;

  SET res=(SELECT 1 GROUP BY unknown);

END;

$$

DELIMITER ;

DELIMITER $$

CREATE OR REPLACE PROCEDURE p1(a INT)

BEGIN

  DECLARE res INT DEFAULT 0;

  SET res=(SELECT 1 ORDER BY unknown);

END;

$$

DELIMITER ;

Alexander Barkov added a comment - 2020-06-03 03:30 - edited More examples when an error is not returned: DELIMITER $$ CREATE OR REPLACE PROCEDURE p1(a INT ) BEGIN DECLARE res INT DEFAULT 0; SET res=( SELECT 1 WHERE unknown); END ; $$ DELIMITER ; DELIMITER $$ CREATE OR REPLACE PROCEDURE p1(a INT ) BEGIN DECLARE res INT DEFAULT 0; SET res=( SELECT 1 HAVING unknown); END ; $$ DELIMITER ; DELIMITER $$ CREATE OR REPLACE PROCEDURE p1(a INT ) BEGIN DECLARE res INT DEFAULT 0; SET res=( SELECT 1 GROUP BY unknown); END ; $$ DELIMITER ; DELIMITER $$ CREATE OR REPLACE PROCEDURE p1(a INT ) BEGIN DECLARE res INT DEFAULT 0; SET res=( SELECT 1 ORDER BY unknown); END ; $$ DELIMITER ;

Alexander Barkov added a comment - 2020-06-03 03:34 - edited

More examples with TVC when no error is returned:

DELIMITER $$

CREATE OR REPLACE PROCEDURE p1(a INT)

BEGIN

  DECLARE res INT DEFAULT 0;

  SET res=(VALUES(xxx));

END;

$$

DELIMITER ;

DELIMITER $$

CREATE OR REPLACE PROCEDURE p1(a INT)

BEGIN

  DECLARE res INT DEFAULT 0;

  SET res=(VALUES(1) ORDER BY yyy);

  SELECT res;

END;

$$

DELIMITER ;

Alexander Barkov added a comment - 2020-06-03 03:34 - edited More examples with TVC when no error is returned: DELIMITER $$ CREATE OR REPLACE PROCEDURE p1(a INT ) BEGIN DECLARE res INT DEFAULT 0; SET res=( VALUES (xxx)); END ; $$ DELIMITER ; DELIMITER $$ CREATE OR REPLACE PROCEDURE p1(a INT ) BEGIN DECLARE res INT DEFAULT 0; SET res=( VALUES (1) ORDER BY yyy); SELECT res; END ; $$ DELIMITER ;

Alexander Barkov added a comment - 2020-06-03 04:29 - edited

More examples with DEFAULT and VALUE

DELIMITER $$

CREATE OR REPLACE PROCEDURE p1()

BEGIN

  DECLARE res INT DEFAULT 0;

  SET res=DEFAULT(unknown);

  SELECT res;

END;

$$

DELIMITER ;

DELIMITER $$

CREATE OR REPLACE PROCEDURE p1()

BEGIN

  DECLARE res INT DEFAULT 0;

  SET res=VALUE(unknown);

  SELECT res;

END;

$$

DELIMITER ;

Alexander Barkov added a comment - 2020-06-03 04:29 - edited More examples with DEFAULT and VALUE DELIMITER $$ CREATE OR REPLACE PROCEDURE p1() BEGIN DECLARE res INT DEFAULT 0; SET res= DEFAULT (unknown); SELECT res; END ; $$ DELIMITER ; DELIMITER $$ CREATE OR REPLACE PROCEDURE p1() BEGIN DECLARE res INT DEFAULT 0; SET res=VALUE(unknown); SELECT res; END ; $$ DELIMITER ;

Alexander Barkov added a comment - 2020-06-03 08:15

sanja, can you please review a patch?

https://github.com/MariaDB/server/commit/06942363ebc847c5cd16f4b8e610451d3c8eeb3f

Thanks.

Alexander Barkov added a comment - 2020-06-03 08:15 sanja , can you please review a patch? https://github.com/MariaDB/server/commit/06942363ebc847c5cd16f4b8e610451d3c8eeb3f Thanks.

Alexander Barkov made changes - 2020-06-03 08:15

Status

Stalled [ 10000 ]

In Progress [ 3 ]

Alexander Barkov made changes - 2020-06-03 08:16

Assignee	Alexander Barkov [ bar ]	Oleksandr Byelkin [ sanja ]
Status	In Progress [ 3 ]	In Review [ 10002 ]

Oleksandr Byelkin added a comment - 2020-06-08 08:24

OK, to push

Oleksandr Byelkin added a comment - 2020-06-08 08:24 OK, to push

Oleksandr Byelkin made changes - 2020-06-08 08:24

Status

In Review [ 10002 ]

Stalled [ 10000 ]

Oleksandr Byelkin added a comment - 2020-06-08 08:26

igor it looks like Item::walk do not check TVC now even if asked to go inside subqueries (see the patch)

Oleksandr Byelkin added a comment - 2020-06-08 08:26 igor it looks like Item::walk do not check TVC now even if asked to go inside subqueries (see the patch)

Oleksandr Byelkin made changes - 2020-06-08 08:27

Assignee

Oleksandr Byelkin [ sanja ]

Alexander Barkov [ bar ]

Alexander Barkov made changes - 2020-06-10 14:39

issue.field.resolutiondate

2020-06-10 14:39:30.0

2020-06-10 14:39:30.07

Alexander Barkov made changes - 2020-06-10 14:39

Fix Version/s		10.5.4 [ 24264 ]
Fix Version/s	10.5 [ 23123 ]
Resolution		Fixed [ 1 ]
Status	Stalled [ 10000 ]	Closed [ 6 ]

Sergei Golubchik made changes - 2021-12-06 21:46

Workflow

MariaDB v3 [ 83736 ]

MariaDB v4 [ 153158 ]

People

Assignee:: Alexander Barkov

Reporter:: Alexander Barkov

Votes:: 0 Vote for this issue

Watchers:: 3 Start watching this issue

Dates

Created:: 2017-11-10 11:36

Updated:: 2020-06-10 14:39

Resolved:: 2020-06-10 14:39

Git Integration

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

MariaDB Server

Details

Description

Attachments

Issue Links

Activity

People

Dates

Git Integration