[MDEV-14347] CREATE PROCEDURE returns no error when using an unknown variable Created: 2017-11-10  Updated: 2020-06-10  Resolved: 2020-06-10

Status: Closed
Project: MariaDB Server
Component/s: Stored routines
Affects Version/s: 5.5, 10.0, 10.1, 10.2, 10.3, 10.4
Fix Version/s: 10.5.4

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

Issue Links:
Relates
relates to MDEV-11953 support of brackets (parentheses) in ... Closed
relates to MDEV-22774 Invalid use of aggregate function in ... Open

 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.

 Comments   
Comment by Alexander Barkov [ 2020-06-03 ]

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 ;

Comment by Alexander Barkov [ 2020-06-03 ]

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 ;

Comment by Alexander Barkov [ 2020-06-03 ]

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 ;

Comment by Alexander Barkov [ 2020-06-03 ]

sanja, can you please review a patch?

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

Thanks.

Comment by Oleksandr Byelkin [ 2020-06-08 ]

OK, to push

Comment by Oleksandr Byelkin [ 2020-06-08 ]

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

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