Details
-
Bug
-
Status: Closed (View Workflow)
-
Minor
-
Resolution: Duplicate
-
12.3.3
-
None
-
Linux/amd64, MariaDB 12.3.3-MariaDB-ubu2404
-
Not for Release Notes
Description
I get error 1064 when a previously successful SYS_REFCURSOR query has a newline between OPEN c FOR and its SELECT, then the referenced table undergoes a metadata-only ALTER TABLE. The same reference cursor with a space after FOR, and a static cursor with the same newline placement, continue to work.
This occurs when a SYS_REFCURSOR query places a newline after OPEN ... FOR and is reconstructed after table metadata invalidation.
Reproducer
Run this script in the mariadb client on a clean instance:
CREATE DATABASE cursor_test; |
USE cursor_test; |
CREATE TABLE t1 (a INT); |
INSERT INTO t1 VALUES (7); |
DELIMITER //
|
CREATE PROCEDURE ref_space(IN phase VARCHAR(16)) |
BEGIN
|
DECLARE c SYS_REFCURSOR; |
DECLARE v INT; |
DECLARE e INT DEFAULT 0; |
DECLARE msg TEXT; |
DECLARE EXIT HANDLER FOR SQLEXCEPTION |
BEGIN |
GET DIAGNOSTICS CONDITION 1 e = MYSQL_ERRNO, msg = MESSAGE_TEXT;
|
SELECT 'ref_space' AS variant, phase, e AS errno, msg AS value; |
END; |
OPEN c FOR SELECT a FROM t1; |
FETCH c INTO v; |
CLOSE c; |
SELECT 'ref_space' AS variant, phase, e AS errno, v AS value; |
END// |
CREATE PROCEDURE ref_newline(IN phase VARCHAR(16)) |
BEGIN
|
DECLARE c SYS_REFCURSOR; |
DECLARE v INT; |
DECLARE e INT DEFAULT 0; |
DECLARE msg TEXT; |
DECLARE EXIT HANDLER FOR SQLEXCEPTION |
BEGIN |
GET DIAGNOSTICS CONDITION 1 e = MYSQL_ERRNO, msg = MESSAGE_TEXT;
|
SELECT 'ref_newline' AS variant, phase, e AS errno, msg AS value; |
END; |
OPEN c FOR |
SELECT a FROM t1; |
FETCH c INTO v; |
CLOSE c; |
SELECT 'ref_newline' AS variant, phase, e AS errno, v AS value; |
END// |
CREATE PROCEDURE static_newline(IN phase VARCHAR(16)) |
BEGIN
|
DECLARE v INT; |
DECLARE e INT DEFAULT 0; |
DECLARE msg TEXT; |
DECLARE c CURSOR FOR |
SELECT a FROM t1; |
DECLARE EXIT HANDLER FOR SQLEXCEPTION |
BEGIN |
GET DIAGNOSTICS CONDITION 1 e = MYSQL_ERRNO, msg = MESSAGE_TEXT;
|
SELECT 'static_newline' AS variant, phase, e AS errno, msg AS value; |
END; |
OPEN c; |
FETCH c INTO v; |
CLOSE c; |
SELECT 'static_newline' AS variant, phase, e AS errno, v AS value; |
END// |
DELIMITER ;
|
CALL ref_space('before'); |
CALL ref_newline('before'); |
CALL static_newline('before'); |
ALTER TABLE t1 COMMENT 'metadata refresh'; |
CALL ref_space('after'); |
CALL ref_newline('after'); |
CALL static_newline('after'); |
SELECT 'data_intact' AS check_name, COUNT(*)=1 AND MIN(a)=7 AS ok |
FROM t1; |
The recorded result was:
variant phase errno value
|
ref_space before 0 7
|
ref_newline before 0 7
|
static_newline before 0 7
|
ref_space after 0 7
|
ref_newline after 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'SELECT a FROM t1' at line 2
|
static_newline after 0 7
|
data_intact 1
|
All three cursor variants should return errno 0 and value 7 both before and after the metadata-only ALTER TABLE. The newline reference cursor is valid when first executed, and whitespace after FOR should remain equivalent when MariaDB reconstructs it after metadata invalidation. The successful space-form reference cursor and newline-form static cursor isolate the failure to reconstruction of the newline SYS_REFCURSOR form. The table still contains its original value.
Attachments
Issue Links
- duplicates
-
-
- Open
-