Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
11.8
-
None
Description
Warning message info is not showing in the result set when SQL_MODE is set to ORACLE in a procedure
Test case
SET SQL_MODE=ORACLE; |
CREATE OR REPLACE TABLE t1( c VARCHAR(2)); |
|
|
DELIMITER $$
|
|
|
CREATE OR REPLACE PROCEDURE p1 AS |
TYPE RecT IS RECORD ( |
c_name t1.c%TYPE
|
);
|
rec RecT:= RecT('aaa'); |
|
|
|
|
BEGIN
|
INSERT INTO t1 SELECT rec.c_name; |
END; |
$$
|
DELIMITER ;
|
CALL p1;
|
INSERT INTO t1 SELECT 'xyz'; |
SHOW WARNINGS;
|
SELECT * FROM t1; |
Output
11.7.0-dbg>SET SQL_MODE=ORACLE;
|
Query OK, 0 rows affected (0.001 sec)
|
|
|
11.7.0-dbg>CREATE OR REPLACE TABLE t1( c VARCHAR(2));
|
Query OK, 0 rows affected (0.070 sec)
|
|
|
11.7.0-dbg>
|
11.7.0-dbg>DELIMITER $$
|
11.7.0-dbg>
|
11.7.0-dbg>CREATE OR REPLACE PROCEDURE p1 AS
|
-> TYPE RecT IS RECORD (
|
-> c_name t1.c%TYPE
|
-> );
|
-> rec RecT:= RecT('aaa');
|
->
|
->
|
-> BEGIN
|
-> INSERT INTO t1 SELECT rec.c_name;
|
-> END;
|
-> $$
|
Query OK, 0 rows affected (0.037 sec)
|
|
|
11.7.0-dbg>DELIMITER ;
|
11.7.0-dbg>CALL p1;
|
Query OK, 1 row affected (0.004 sec)
|
|
|
11.7.0-dbg>INSERT INTO t1 SELECT 'xyz';
|
Query OK, 1 row affected, 1 warning (0.003 sec)
|
Records: 1 Duplicates: 0 Warnings: 1
|
|
|
11.7.0-dbg>SHOW WARNINGS;
|
+---------+------+----------------------------------------+
|
| Level | Code | Message |
|
+---------+------+----------------------------------------+
|
| Warning | 1265 | Data truncated for column 'c' at row 1 |
|
+---------+------+----------------------------------------+
|
1 row in set (0.000 sec)
|
|
|
11.7.0-dbg>SELECT * FROM t1;
|
+------+
|
| c |
|
+------+
|
| aa |
|
| xy |
|
+------+
|
2 rows in set (0.000 sec)
|
|
|
11.7.0-dbg>
|
Attachments
Activity
With explicit VARCHAR(2) variable data type it also does not show warnings:
SET SQL_MODE=ORACLE; |
CREATE OR REPLACE TABLE t1( c VARCHAR(2)); |
DELIMITER $$
|
CREATE OR REPLACE PROCEDURE p1 AS |
c_name VARCHAR(2) := 'xyz'; |
BEGIN
|
INSERT INTO t1 SELECT c_name; |
END; |
$$
|
DELIMITER ;
|
CALL p1;
|
Interesting, with a similar procedure with sql_mode=DEFAULT it returns an error:
SET SQL_MODE=DEFAULT; |
CREATE OR REPLACE TABLE t1(c VARCHAR(2)); |
DELIMITER $$
|
CREATE OR REPLACE PROCEDURE p1() |
BEGIN
|
DECLARE c_name VARCHAR(2) DEFAULT 'xyz'; |
INSERT INTO t1 SELECT c_name; |
END; |
$$
|
DELIMITER ;
|
CALL p1;
|
ERROR 1406 (22001): Data too long for column 'c_name' at row 0
|
The error depends on the presense of STRICT_TRANS_TABLES in sql_mode at the CREATE PROCEDURE time.
This script does not show errors or warnings:
SET SQL_MODE=''; |
CREATE OR REPLACE TABLE t1(c VARCHAR(2)); |
DELIMITER $$
|
CREATE OR REPLACE PROCEDURE p1() |
BEGIN
|
DECLARE c_name VARCHAR(2) DEFAULT 'xyz'; |
INSERT INTO t1 SELECT c_name; |
END; |
$$
|
DELIMITER ;
|
CALL p1;
|
This script returns an error:
SET SQL_MODE='STRICT_TRANS_TABLES'; |
CREATE OR REPLACE TABLE t1(c VARCHAR(2)); |
DELIMITER $$
|
CREATE OR REPLACE PROCEDURE p1() |
BEGIN
|
DECLARE c_name VARCHAR(2) DEFAULT 'xyz'; |
INSERT INTO t1 SELECT c_name; |
END; |
$$
|
DELIMITER ;
|
CALL p1;
|
ERROR 1406 (22001): Data too long for column 'c_name' at row 0
|
It seems this problem is not caused by
MDEV-34317.This script without a record also does not show warnings.
DELIMITER $$BEGIN$$DELIMITER ;CALL p1;