diff options
Diffstat (limited to 'mysql-test/main/get_diagnostics.test')
| -rw-r--r-- | mysql-test/main/get_diagnostics.test | 1734 |
1 files changed, 1734 insertions, 0 deletions
diff --git a/mysql-test/main/get_diagnostics.test b/mysql-test/main/get_diagnostics.test new file mode 100644 index 00000000..27c98069 --- /dev/null +++ b/mysql-test/main/get_diagnostics.test @@ -0,0 +1,1734 @@ +--source include/have_innodb.inc + +SET NAMES utf8; + +--echo # +--echo # WL#2111: GET DIAGNOSTICS tests +--echo # + +--echo # +--echo # In MariaDB GET is not reserved +--echo # + +CREATE TABLE t1 (get INT); +DROP TABLE t1; + +DELIMITER |; +CREATE PROCEDURE p1() +BEGIN + DECLARE get INT DEFAULT 1; +END| +DELIMITER ;| +DROP PROCEDURE p1; + +# but cannot be used as a label + +DELIMITER |; +--error ER_PARSE_ERROR +CREATE PROCEDURE p1() +get: +BEGIN + SELECT 1; +END get| +DELIMITER ;| + +--echo # +--echo # Test non-reserved keywords: CURRENT, DIAGNOSTICS, NUMBER, RETURNED_SQLSTATE +--echo # + +CREATE TABLE t1 (current INT, diagnostics INT, number INT, returned_sqlstate INT); +INSERT INTO t1 (current, diagnostics, number, returned_sqlstate) values (1,2,3,4); +SELECT current, diagnostics, number, returned_sqlstate FROM t1 WHERE number = 3; +SELECT `current`, `number` FROM t1 WHERE `current` = 1 AND `number` = 3; +DROP TABLE t1; + +DELIMITER |; +CREATE PROCEDURE p1() +BEGIN + DECLARE current INT DEFAULT 1; + DECLARE diagnostics INT DEFAULT 2; + DECLARE number INT DEFAULT 3; + DECLARE returned_sqlstate INT DEFAULT 4; + SELECT current, diagnostics, number, returned_sqlstate; +END| +DELIMITER ;| + +CALL p1(); + +DROP PROCEDURE p1; + +--echo # +--echo # Test GET DIAGNOSTICS syntax +--echo # + +--disable_warnings +DROP PROCEDURE IF EXISTS p1; +--enable_warnings + +--error ER_PARSE_ERROR +GET; +--error ER_PARSE_ERROR +GET CURRENT; +--error ER_PARSE_ERROR +GET DIAGNOSTICS; +--error ER_PARSE_ERROR +GET CURRENT DIAGNOSTICS; + +--echo +--echo # Statement information syntax +--echo + +--error ER_PARSE_ERROR +GET DIAGNOSTICS @var; + +--error ER_SP_UNDECLARED_VAR +GET DIAGNOSTICS var; + +DELIMITER |; +--error ER_SP_UNDECLARED_VAR +CREATE PROCEDURE p1() +BEGIN + GET DIAGNOSTICS var; +END| +DELIMITER ;| + +DELIMITER |; +--error ER_PARSE_ERROR +CREATE PROCEDURE p1() +BEGIN + DECLARE var INT; + GET DIAGNOSTICS var; +END| +DELIMITER ;| + +--error ER_PARSE_ERROR +GET DIAGNOSTICS @var =; +--error ER_PARSE_ERROR +GET DIAGNOSTICS @var = INVALID; +--error ER_PARSE_ERROR +GET DIAGNOSTICS @var = MORE; +--error ER_PARSE_ERROR +GET DIAGNOSTICS @var = CLASS_ORIGIN; +--error ER_PARSE_ERROR +GET DIAGNOSTICS @var = INVALID,; +--error ER_PARSE_ERROR +GET DIAGNOSTICS @var1 = NUMBER, @var2; +--error ER_PARSE_ERROR +GET DIAGNOSTICS @var1 = NUMBER, @var2 = INVALID; +--error ER_PARSE_ERROR +GET DIAGNOSTICS @@var1 = NUMBER; +--error ER_PARSE_ERROR +GET DIAGNOSTICS @var1 = NUMBER, @@var2 = NUMBER; + +DELIMITER |; +--error ER_PARSE_ERROR +CREATE PROCEDURE p1() +BEGIN + DECLARE var INT; + GET DIAGNOSTICS var = INVALID; +END| +DELIMITER ;| + +DELIMITER |; +--error ER_SP_UNDECLARED_VAR +CREATE PROCEDURE p1() +BEGIN + DECLARE var CONDITION FOR SQLSTATE '12345'; + GET DIAGNOSTICS var = NUMBER; +END| +DELIMITER ;| + +DELIMITER |; +--error ER_SP_UNDECLARED_VAR +CREATE PROCEDURE p1() +BEGIN + DECLARE var INT; + GET DIAGNOSTICS var = NUMBER, var1 = ROW_COUNT; +END| +DELIMITER ;| + +GET DIAGNOSTICS @var = NUMBER; +GET DIAGNOSTICS @var = ROW_COUNT; +GET DIAGNOSTICS @var1 = NUMBER, @var2 = ROW_COUNT; +GET DIAGNOSTICS @var1 = ROW_COUNT, @var2 = NUMBER; + +DELIMITER |; +CREATE PROCEDURE p1() +BEGIN + DECLARE var INT; + DECLARE var1 INT; + DECLARE var2 INT; + GET DIAGNOSTICS var = NUMBER; + GET DIAGNOSTICS var = ROW_COUNT; + GET DIAGNOSTICS var1 = NUMBER, var2 = ROW_COUNT; + GET DIAGNOSTICS var1 = ROW_COUNT, var2 = NUMBER; +END| +DELIMITER ;| + +DROP PROCEDURE p1; + +--echo +--echo # Condition information syntax +--echo + +--error ER_PARSE_ERROR +GET DIAGNOSTICS CONDITION; +--error ER_BAD_FIELD_ERROR +GET DIAGNOSTICS CONDITION a; +--error ER_PARSE_ERROR +GET DIAGNOSTICS CONDITION 1; + +--error ER_PARSE_ERROR +GET DIAGNOSTICS CONDITION 1 @var; + +--error ER_SP_UNDECLARED_VAR +GET DIAGNOSTICS CONDITION 1 var; + +DELIMITER |; +--error ER_SP_UNDECLARED_VAR +CREATE PROCEDURE p1() +BEGIN + GET DIAGNOSTICS CONDITION 1 var; +END| +DELIMITER ;| + +DELIMITER |; +--error ER_PARSE_ERROR +CREATE PROCEDURE p1() +BEGIN + DECLARE var INT; + GET DIAGNOSTICS CONDITION 1 var; +END| +DELIMITER ;| + +--error ER_PARSE_ERROR +GET DIAGNOSTICS CONDITION 1 @var =; +--error ER_PARSE_ERROR +GET DIAGNOSTICS CONDITION 1 @var = INVALID; +--error ER_PARSE_ERROR +GET DIAGNOSTICS CONDITION 1 @var = NUMBER; +--error ER_PARSE_ERROR +GET DIAGNOSTICS CONDITION 1 @var = INVALID,; +--error ER_PARSE_ERROR +GET DIAGNOSTICS CONDITION 1 @var1 = CLASS_ORIGIN, @var2; +--error ER_PARSE_ERROR +GET DIAGNOSTICS CONDITION 1 @var1 = CLASS_ORIGIN, @var2 = INVALID; +--error ER_PARSE_ERROR +GET DIAGNOSTICS CONDITION 1 @@var1 = CLASS_ORIGIN; +--error ER_PARSE_ERROR +GET DIAGNOSTICS CONDITION 1 @var1 = CLASS_ORIGIN, @@var2 = CLASS_ORIGIN; + +DELIMITER |; +--error ER_PARSE_ERROR +CREATE PROCEDURE p1() +BEGIN + DECLARE var INT; + GET DIAGNOSTICS CONDITION 1 var = INVALID; +END| +DELIMITER ;| + +DELIMITER |; +--error ER_SP_UNDECLARED_VAR +CREATE PROCEDURE p1() +BEGIN + DECLARE var CONDITION FOR SQLSTATE '12345'; + GET DIAGNOSTICS CONDITION 1 var = NUMBER; +END| +DELIMITER ;| + +DELIMITER |; +--error ER_SP_UNDECLARED_VAR +CREATE PROCEDURE p1() +BEGIN + DECLARE var INT; + GET DIAGNOSTICS CONDITION 1 var = CLASS_ORIGIN, var1 = SUBCLASS_ORIGIN; +END| +DELIMITER ;| + +GET DIAGNOSTICS CONDITION 1 @var = CLASS_ORIGIN; +GET DIAGNOSTICS CONDITION 1 @var = SUBCLASS_ORIGIN; +GET DIAGNOSTICS CONDITION 1 @var1 = CLASS_ORIGIN, @var2 = SUBCLASS_ORIGIN; +GET DIAGNOSTICS CONDITION 1 @var1 = SUBCLASS_ORIGIN, @var2 = CLASS_ORIGIN; + +DELIMITER |; +CREATE PROCEDURE p1() +BEGIN + DECLARE var INT; + DECLARE var1 INT; + DECLARE var2 INT; + GET DIAGNOSTICS CONDITION 1 var = CLASS_ORIGIN; + GET DIAGNOSTICS CONDITION 1 var = SUBCLASS_ORIGIN; + GET DIAGNOSTICS CONDITION 1 var1 = CLASS_ORIGIN, var2 = SUBCLASS_ORIGIN; + GET DIAGNOSTICS CONDITION 1 var1 = SUBCLASS_ORIGIN, var2 = CLASS_ORIGIN; +END| +DELIMITER ;| + +DROP PROCEDURE p1; + +--echo # Condition number expression + +--error ER_PARSE_ERROR +GET DIAGNOSTICS CONDITION -1 @var = CLASS_ORIGIN; +--error ER_PARSE_ERROR +GET DIAGNOSTICS CONDITION 1+1 @var = CLASS_ORIGIN; +--error ER_PARSE_ERROR +GET DIAGNOSTICS CONDITION ? @var = CLASS_ORIGIN; +--error ER_PARSE_ERROR +GET DIAGNOSTICS CONDITION (1) @var = CLASS_ORIGIN; +--error ER_BAD_FIELD_ERROR +GET DIAGNOSTICS CONDITION p1() @var = CLASS_ORIGIN; +--error ER_BAD_FIELD_ERROR +GET DIAGNOSTICS CONDITION ABS(2) @var = CLASS_ORIGIN; + +# Unfortunate side effects... +GET DIAGNOSTICS CONDITION 1.1 @var = CLASS_ORIGIN; +GET DIAGNOSTICS CONDITION "1" @var = CLASS_ORIGIN; + +# Reset warnings +SELECT COUNT(max_questions) INTO @var FROM mysql.user; + +GET DIAGNOSTICS CONDITION 9999 @var = CLASS_ORIGIN; +GET DIAGNOSTICS CONDITION NULL @var = CLASS_ORIGIN; +--error ER_BAD_FIELD_ERROR +GET DIAGNOSTICS CONDITION a @var = CLASS_ORIGIN; + +# Reset warnings +SELECT COUNT(max_questions) INTO @var FROM mysql.user; + +SET @cond = 1; +GET DIAGNOSTICS CONDITION @cond @var1 = CLASS_ORIGIN; + +SET @cond = "invalid"; +GET DIAGNOSTICS CONDITION @cond @var1 = CLASS_ORIGIN; + +# Reset warnings +SELECT COUNT(max_questions) INTO @var FROM mysql.user; + +DELIMITER |; +CREATE PROCEDURE p1() +BEGIN + DECLARE cond INT DEFAULT 1; + DECLARE var INT; + GET DIAGNOSTICS CONDITION cond var = CLASS_ORIGIN; +END| +DELIMITER ;| + +DROP PROCEDURE p1; + +DELIMITER |; +CREATE PROCEDURE p1() +BEGIN + DECLARE cond TEXT; + DECLARE var INT; + GET DIAGNOSTICS CONDITION cond var = CLASS_ORIGIN; +END| +DELIMITER ;| + +CALL p1(); + +DROP PROCEDURE p1; + +--echo # +--echo # Test GET DIAGNOSTICS runtime +--echo # + +--echo +--echo # GET DIAGNOSTICS can be the object of a PREPARE statement. +--echo + +PREPARE stmt FROM "GET DIAGNOSTICS CONDITION 1 @var = CLASS_ORIGIN"; +PREPARE stmt FROM "GET DIAGNOSTICS @var = NUMBER"; + +--echo +--echo # GET DIAGNOSTICS does not clear the diagnostics area. +--echo + +SELECT CAST(-19999999999999999999 AS SIGNED); +GET DIAGNOSTICS @var = NUMBER; +SHOW WARNINGS; + +--echo # +--echo # If GET DIAGNOSTICS itself causes an error, an error message is appended. +--echo # + +SELECT CAST(-19999999999999999999 AS SIGNED); +GET DIAGNOSTICS CONDITION 99999 @var = CLASS_ORIGIN; +SHOW WARNINGS; + +--echo +--echo # Statement information runtime +--echo + +#enable after fix MDEV-28535 +--disable_view_protocol +SELECT CAST(-19999999999999999999 AS SIGNED), + CAST(-19999999999999999999 AS SIGNED); +GET DIAGNOSTICS @var = NUMBER; +SELECT @var; +--enable_view_protocol + +SELECT COUNT(max_questions) INTO @var FROM mysql.user; +GET DIAGNOSTICS @var = NUMBER; +SELECT @var; + +SELECT 1; +GET DIAGNOSTICS @var = ROW_COUNT; +SELECT @var; + +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (1),(2),(3); +GET DIAGNOSTICS @var = ROW_COUNT; +SELECT @var; +DROP TABLE t1; + +DELIMITER |; +CREATE PROCEDURE p1() +BEGIN + DECLARE number INT; + DECLARE row_count INT; + + SELECT CAST(-19999999999999999999 AS SIGNED), + CAST(-19999999999999999999 AS SIGNED); + + GET DIAGNOSTICS number = NUMBER; + + CREATE TABLE t1 (a INT); + INSERT INTO t1 VALUES (1),(2),(3); + GET DIAGNOSTICS row_count = ROW_COUNT; + DROP TABLE t1; + + SELECT number, row_count; + +END| +DELIMITER ;| + +CALL p1(); + +DROP PROCEDURE p1; + +--echo +--echo # Condition information runtime +--echo + +SELECT CAST(-19999999999999999999 AS SIGNED); + +GET DIAGNOSTICS CONDITION 1 + @class_origin = CLASS_ORIGIN, + @subclass_origin = SUBCLASS_ORIGIN, + @constraint_catalog = CONSTRAINT_CATALOG, + @constraint_schema = CONSTRAINT_SCHEMA, + @constraint_name = CONSTRAINT_NAME, + @catalog_name = CATALOG_NAME, + @schema_name = SCHEMA_NAME, + @table_name = TABLE_NAME, + @column_name = COLUMN_NAME, + @cursor_name = CURSOR_NAME, + @message_text = MESSAGE_TEXT, + @mysql_errno = MYSQL_ERRNO, + @returned_sqlstate = RETURNED_SQLSTATE; + +--vertical_results +SELECT + @class_origin, + @subclass_origin, + @constraint_catalog, + @constraint_schema, + @constraint_name, + @catalog_name, + @schema_name, + @table_name, + @column_name, + @cursor_name, + @message_text, + @mysql_errno, + @returned_sqlstate; +--horizontal_results + +DELIMITER |; +CREATE PROCEDURE p1() +BEGIN + DECLARE class_origin TEXT DEFAULT "a"; + DECLARE subclass_origin TEXT DEFAULT "a"; + DECLARE constraint_catalog TEXT DEFAULT "a"; + DECLARE constraint_schema TEXT DEFAULT "a"; + DECLARE constraint_name TEXT DEFAULT "a"; + DECLARE catalog_name TEXT DEFAULT "a"; + DECLARE schema_name TEXT DEFAULT "a"; + DECLARE table_name TEXT DEFAULT "a"; + DECLARE column_name TEXT DEFAULT "a"; + DECLARE cursor_name TEXT DEFAULT "a"; + DECLARE message_text TEXT DEFAULT "a"; + DECLARE mysql_errno INT DEFAULT 1; + DECLARE returned_sqlstate TEXT DEFAULT "a"; + + SELECT CAST(-19999999999999999999 AS SIGNED); + + GET DIAGNOSTICS CONDITION 1 + class_origin = CLASS_ORIGIN, + subclass_origin = SUBCLASS_ORIGIN, + constraint_catalog = CONSTRAINT_CATALOG, + constraint_schema = CONSTRAINT_SCHEMA, + constraint_name = CONSTRAINT_NAME, + catalog_name = CATALOG_NAME, + schema_name = SCHEMA_NAME, + table_name = TABLE_NAME, + column_name = COLUMN_NAME, + cursor_name = CURSOR_NAME, + message_text = MESSAGE_TEXT, + mysql_errno = MYSQL_ERRNO, + returned_sqlstate = RETURNED_SQLSTATE; + + SELECT + class_origin, + subclass_origin, + constraint_catalog, + constraint_schema, + constraint_name, + catalog_name, + schema_name, + table_name, + column_name, + cursor_name, + message_text, + mysql_errno, + returned_sqlstate; +END| +DELIMITER ;| + +--vertical_results +CALL p1(); +--horizontal_results + +DROP PROCEDURE p1; + +DELIMITER |; +CREATE PROCEDURE p1() +BEGIN + DECLARE errno1 INT; + DECLARE errno2 INT; + DECLARE msg1 TEXT; + DECLARE msg2 TEXT; + + SELECT CAST(-19999999999999999999 AS SIGNED); + GET DIAGNOSTICS CONDITION 99999 msg1 = MESSAGE_TEXT; + + GET DIAGNOSTICS CONDITION 1 errno1 = MYSQL_ERRNO, msg1 = MESSAGE_TEXT; + GET DIAGNOSTICS CONDITION 2 errno2 = MYSQL_ERRNO, msg2 = MESSAGE_TEXT; + + SELECT errno1, msg1, errno2, msg2; +END| +DELIMITER ;| + +--vertical_results +CALL p1(); +--horizontal_results + +DROP PROCEDURE p1; + +--echo +--echo # Interaction with SIGNAL +--echo + +DELIMITER |; +CREATE PROCEDURE p1() +BEGIN + DECLARE errno INT DEFAULT 0; + DECLARE msg TEXT DEFAULT "foo"; + DECLARE cond CONDITION FOR SQLSTATE "01234"; + DECLARE CONTINUE HANDLER for 1012 + BEGIN + GET DIAGNOSTICS CONDITION 1 errno = MYSQL_ERRNO, msg = MESSAGE_TEXT; + END; + + SIGNAL cond SET MESSAGE_TEXT = "Signal message", MYSQL_ERRNO = 1012; + + SELECT errno, msg; +END| +DELIMITER ;| + +--vertical_results +CALL p1(); +--horizontal_results + +DROP PROCEDURE p1; + +DELIMITER |; +CREATE PROCEDURE p1() +BEGIN + SIGNAL SQLSTATE '77777' SET MYSQL_ERRNO = 1000, MESSAGE_TEXT='ÁÂÃÅÄ'; +END| +DELIMITER ;| + +--error 1000 +CALL p1(); + +GET DIAGNOSTICS CONDITION 1 + @mysql_errno = MYSQL_ERRNO, @message_text = MESSAGE_TEXT, + @returned_sqlstate = RETURNED_SQLSTATE, @class_origin = CLASS_ORIGIN; + +--vertical_results +SELECT @mysql_errno, @message_text, @returned_sqlstate, @class_origin; +--horizontal_results + +DROP PROCEDURE p1; + +DELIMITER |; +CREATE PROCEDURE p1() +BEGIN + DECLARE cond CONDITION FOR SQLSTATE '12345'; + SIGNAL cond SET + CLASS_ORIGIN = 'CLASS_ORIGIN text', + SUBCLASS_ORIGIN = 'SUBCLASS_ORIGIN text', + CONSTRAINT_CATALOG = 'CONSTRAINT_CATALOG text', + CONSTRAINT_SCHEMA = 'CONSTRAINT_SCHEMA text', + CONSTRAINT_NAME = 'CONSTRAINT_NAME text', + CATALOG_NAME = 'CATALOG_NAME text', + SCHEMA_NAME = 'SCHEMA_NAME text', + TABLE_NAME = 'TABLE_NAME text', + COLUMN_NAME = 'COLUMN_NAME text', + CURSOR_NAME = 'CURSOR_NAME text', + MESSAGE_TEXT = 'MESSAGE_TEXT text', + MYSQL_ERRNO = 54321; +END| +DELIMITER ;| + +--error 54321 +CALL p1(); + +GET DIAGNOSTICS CONDITION 1 + @class_origin = CLASS_ORIGIN, + @subclass_origin = SUBCLASS_ORIGIN, + @constraint_catalog = CONSTRAINT_CATALOG, + @constraint_schema = CONSTRAINT_SCHEMA, + @constraint_name = CONSTRAINT_NAME, + @catalog_name = CATALOG_NAME, + @schema_name = SCHEMA_NAME, + @table_name = TABLE_NAME, + @column_name = COLUMN_NAME, + @cursor_name = CURSOR_NAME, + @message_text = MESSAGE_TEXT, + @mysql_errno = MYSQL_ERRNO, + @returned_sqlstate = RETURNED_SQLSTATE; + +--vertical_results +SELECT + @class_origin, + @subclass_origin, + @constraint_catalog, + @constraint_schema, + @constraint_name, + @catalog_name, + @schema_name, + @table_name, + @column_name, + @cursor_name, + @message_text, + @mysql_errno, + @returned_sqlstate; +--horizontal_results + +DROP PROCEDURE p1; + +--echo # +--echo # Demonstration +--echo # + +--echo +--echo # The same statement information item can be used multiple times. +--echo + +SHOW WARNINGS; +DELIMITER |; +CREATE PROCEDURE p1() +BEGIN + DECLARE var INT; + GET DIAGNOSTICS var = NUMBER, @var = NUMBER; + SELECT var, @var; +END| +DELIMITER ;| + +CALL p1(); + +DROP PROCEDURE p1; + +--echo +--echo # Setting TABLE_NAME is currently not implemented. +--echo + +DELIMITER |; +CREATE PROCEDURE p1() +BEGIN + DECLARE v VARCHAR(64); + DECLARE CONTINUE HANDLER FOR SQLEXCEPTION + GET DIAGNOSTICS CONDITION 1 v = TABLE_NAME; + DROP TABLE no_such_table; + SELECT v; +END| +DELIMITER ;| + +CALL p1(); + +DROP PROCEDURE p1; + +--echo +--echo # Message is truncated to fit into target. No truncation warning. +--echo + +DELIMITER |; +CREATE PROCEDURE p1() +BEGIN + DECLARE v CHAR(1); + CREATE TABLE IF NOT EXISTS t1 (a INT); + GET DIAGNOSTICS CONDITION 1 v = MESSAGE_TEXT; + SELECT v; +END| +DELIMITER ;| + +CREATE TABLE t1 (a INT); +CALL p1(); +DROP TABLE t1; + +DROP PROCEDURE p1; + +--echo +--echo # Returns number of rows updated by the UPDATE statements. +--echo + +DELIMITER |; +CREATE PROCEDURE p1(IN param INT) +LANGUAGE SQL +BEGIN + DECLARE v INT DEFAULT 0; + DECLARE rcount_each INT; + DECLARE rcount_total INT DEFAULT 0; + WHILE v < 5 DO + UPDATE t1 SET a = a * 1.1 WHERE b = param; + GET DIAGNOSTICS rcount_each = ROW_COUNT; + SET rcount_total = rcount_total + rcount_each; + SET v = v + 1; + END WHILE; + SELECT rcount_total; +END| +DELIMITER ;| + +CREATE TABLE t1 (a REAL, b INT); +INSERT INTO t1 VALUES (1.1, 1); +CALL p1(1); +DROP TABLE t1; + +DROP PROCEDURE p1; + +--echo +--echo # GET DIAGNOSTICS doesn't clear the diagnostics area. +--echo + +DELIMITER |; +CREATE PROCEDURE p1() +BEGIN + DECLARE CONTINUE HANDLER FOR SQLWARNING + BEGIN + GET CURRENT DIAGNOSTICS CONDITION 1 @x = RETURNED_SQLSTATE; + SIGNAL SQLSTATE '01002'; + GET CURRENT DIAGNOSTICS CONDITION 1 @y = RETURNED_SQLSTATE; + END; + SIGNAL SQLSTATE '01001'; + SELECT @x, @y; +END| +DELIMITER ;| + +CALL p1(); + +DROP PROCEDURE p1; + +--echo +--echo # Using OUT and INOUT parameters as the target variables. +--echo + +DELIMITER |; +CREATE PROCEDURE p1(OUT number INT, INOUT message TEXT) +BEGIN + DECLARE warn CONDITION FOR SQLSTATE "01234"; + DECLARE CONTINUE HANDLER FOR SQLWARNING + BEGIN + GET DIAGNOSTICS number = NUMBER; + GET DIAGNOSTICS CONDITION 1 message = MESSAGE_TEXT; + END; + SELECT message; + SIGNAL warn SET MESSAGE_TEXT = "inout parameter"; +END| +DELIMITER ;| + +SET @var1 = 0; +SET @var2 = "message text"; +CALL p1(@var1, @var2); +SELECT @var1, @var2; + +DROP PROCEDURE p1; + +--echo +--echo # Using an IN parameter as the target variable. +--echo + +DELIMITER |; +CREATE PROCEDURE p1(IN number INT) +BEGIN + SELECT number; + GET DIAGNOSTICS number = NUMBER; + SELECT number; +END| +DELIMITER ;| + +SET @var1 = 9999; +CALL p1(@var1); +SELECT @var1; + +DROP PROCEDURE p1; + +--echo +--echo # Using GET DIAGNOSTICS in a stored function. +--echo + +DELIMITER |; +CREATE FUNCTION f1() RETURNS TEXT +BEGIN + DECLARE message TEXT; + DECLARE warn CONDITION FOR SQLSTATE "01234"; + DECLARE CONTINUE HANDLER FOR SQLWARNING + BEGIN + GET DIAGNOSTICS CONDITION 1 message = MESSAGE_TEXT; + END; + SIGNAL warn SET MESSAGE_TEXT = "message text"; + return message; +END| +DELIMITER ;| + +SELECT f1(); + +DROP FUNCTION f1; + +--echo +--echo # Using GET DIAGNOSTICS in a trigger. +--echo + +CREATE TABLE t1 (a INT); + +DELIMITER |; +CREATE TRIGGER trg1 BEFORE INSERT ON t1 FOR EACH ROW +BEGIN + DECLARE var INT DEFAULT row_count(); + GET DIAGNOSTICS @var1 = ROW_COUNT; + SET @var2 = var; +END| +DELIMITER ;| + +SET @var1 = 9999, @var2 = 9999; +INSERT INTO t1 VALUES (1), (2); +SELECT @var1, @var2; + +DROP TRIGGER trg1; +DROP TABLE t1; + +--echo +--echo # GET DIAGNOSTICS does not reset ROW_COUNT +--echo + +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (1); +GET DIAGNOSTICS @var1 = ROW_COUNT; +GET DIAGNOSTICS @var2 = ROW_COUNT; +SELECT @var1, @var2; +DROP TABLE t1; + +--echo +--echo # Items are UTF8 (utf8_general_ci default collation) +--echo + +SELECT CAST(-19999999999999999999 AS SIGNED); +GET DIAGNOSTICS CONDITION 1 @var1 = MESSAGE_TEXT, @var2 = CLASS_ORIGIN; +SELECT CHARSET(@var1), COLLATION(@var1), COERCIBILITY(@var1); +SELECT CHARSET(@var2), COLLATION(@var2), COERCIBILITY(@var2); + +--echo # +--echo # Command statistics +--echo # + +FLUSH STATUS; +SHOW STATUS LIKE 'Com%get_diagnostics'; +GET DIAGNOSTICS @var1 = NUMBER; +SHOW STATUS LIKE 'Com%get_diagnostics'; + +--echo # +--echo # MDEV-26695: Number of an invalid row is not calculated for table value constructor +--echo # + +CREATE TABLE t1 (a CHAR(1)) VALUES ('a'),('b'),('foo'); +CREATE TABLE t2 (a char(1)) VALUES ('a'),('b') UNION VALUES ('foo'); + +DROP TABLE t1, t2; + +--echo # +--echo # End of 10.6 tests +--echo # + +--echo # +--echo # MDEV-10075: Provide index of error causing error in array INSERT +--echo # + +--echo # +--echo # INSERT STATEMENT +--echo # + +CREATE TABLE t1(id1 INT PRIMARY KEY, val1 VARCHAR(10), d1 DOUBLE); +CREATE TABLE t2(id2 INT PRIMARY KEY, val2 VARCHAR(10), d2 DOUBLE, p2 POINT); + + +--echo # Simple INSERT statement + +INSERT INTO t1 VALUES(1,'a',1.00101), (2,'b',1.00102), (3,'c',1.00103); +--error ER_DUP_ENTRY +INSERT INTO t1 VALUES (4,'d',1.00104),(1,'a',1.00101),(2,'b',1.00102); +GET DIAGNOSTICS CONDITION 1 @var1= ROW_NUMBER; +GET DIAGNOSTICS CONDITION 2 @var2= ROW_NUMBER; +SELECT @var1, @var2; + +--error ER_ILLEGAL_VALUE_FOR_TYPE +INSERT INTO t1 VALUES (5,'e',1.00105),(6,'f',1.79769313486232e+308); +GET DIAGNOSTICS CONDITION 1 @var3= ROW_NUMBER; +GET DIAGNOSTICS CONDITION 2 @var4= ROW_NUMBER; +SELECT @var3, @var4; + +--error ER_WRONG_VALUE_COUNT_ON_ROW +INSERT INTO t1 VALUES (7,'g',1.00107),(8,'h',1.00108),(9,'i'),(10,'j'); +GET DIAGNOSTICS CONDITION 1 @var5= ROW_NUMBER; +GET DIAGNOSTICS CONDITION 2 @var6= ROW_NUMBER; +SELECT @var5, @var6; + +INSERT INTO t1 VALUES (8,8,8),(9,CAST(123 AS CHAR(1)),1.00109); +GET DIAGNOSTICS CONDITION 1 @var7= ROW_NUMBER; +GET DIAGNOSTICS CONDITION 2 @var8= ROW_NUMBER; +SELECT @var7, @var8; + +INSERT INTO t1 VALUES (10,10,10),('x','foo',1.0203); +GET DIAGNOSTICS CONDITION 1 @var9= ROW_NUMBER; +GET DIAGNOSTICS CONDITION 2 @var10= ROW_NUMBER; +SELECT @var9, @var10; + +--error ER_OPERAND_COLUMNS +INSERT INTO t1 VALUES (11,11,11),(12,12,(13,'m',1.0113)); +GET DIAGNOSTICS CONDITION 1 @var11= ROW_NUMBER; +GET DIAGNOSTICS CONDITION 2 @var12= ROW_NUMBER; +SELECT @var11, @var12; + +delete from t1 where id1=0; +INSERT INTO t1 VALUES (14,'n',1.0114), (15,'o',1.0115), (16/0,'p',1.0116); +GET DIAGNOSTICS CONDITION 1 @var13= ROW_NUMBER; +GET DIAGNOSTICS CONDITION 2 @var14= ROW_NUMBER; +GET DIAGNOSTICS CONDITION 3 @var15= ROW_NUMBER; +SELECT @var13, @var14, @var15; + +TRUNCATE TABLE t1; + +--echo # INSERT ... IGNORE + +INSERT IGNORE INTO t1 VALUES(1,'a',1.00101), (2,'b',1.00102), (3,'c',1.00103); +INSERT IGNORE INTO t1 VALUES (4,'d',1.00104),(1,'a',1.00101),(2,'b',1.00102); +GET DIAGNOSTICS CONDITION 1 @var16= ROW_NUMBER; +GET DIAGNOSTICS CONDITION 2 @var17= ROW_NUMBER; +GET DIAGNOSTICS CONDITION 3 @var18= ROW_NUMBER; +SELECT @var16, @var17, @var18; + +--error ER_ILLEGAL_VALUE_FOR_TYPE +INSERT IGNORE INTO t1 VALUES (5,'e',1.00105),(6,'f',1.79769313486232e+308); +GET DIAGNOSTICS CONDITION 1 @var19= ROW_NUMBER; +GET DIAGNOSTICS CONDITION 2 @var20= ROW_NUMBER; +SELECT @var19, @var20; + +--error ER_WRONG_VALUE_COUNT_ON_ROW +INSERT IGNORE INTO t1 VALUES (7,'g',1.00107),(8,'h',1.00108),(9,'i'),(10,'j'); +GET DIAGNOSTICS CONDITION 1 @var21= ROW_NUMBER; +GET DIAGNOSTICS CONDITION 2 @var22= ROW_NUMBER; +SELECT @var21, @var22; + +INSERT IGNORE INTO t1 VALUES (8,8,8),(9,CAST(123 AS CHAR(1)),1.00109); +GET DIAGNOSTICS CONDITION 1 @var23= ROW_NUMBER; +GET DIAGNOSTICS CONDITION 2 @var24= ROW_NUMBER; +SELECT @var23, @var24; + +INSERT IGNORE INTO t1 VALUES (10,10,10),('x','foo',1.0203); +GET DIAGNOSTICS CONDITION 1 @var25= ROW_NUMBER; +GET DIAGNOSTICS CONDITION 2 @var26= ROW_NUMBER; +SELECT @var25, @var26; + +--error ER_OPERAND_COLUMNS +INSERT IGNORE INTO t1 VALUES (11,11,11),(12,12,(13,'m',1.0113)); +GET DIAGNOSTICS CONDITION 1 @var27= ROW_NUMBER; +GET DIAGNOSTICS CONDITION 2 @var28= ROW_NUMBER; +SELECT @var27, @var28; + +INSERT IGNORE INTO t1 VALUES (14,'n',1.0114), (15,'o',1.0115), +(16/0,'p',1.0116); +GET DIAGNOSTICS CONDITION 1 @var29= ROW_NUMBER; +GET DIAGNOSTICS CONDITION 2 @var30= ROW_NUMBER; +GET DIAGNOSTICS CONDITION 3 @var31= ROW_NUMBER; +GET DIAGNOSTICS CONDITION 4 @var32= ROW_NUMBER; +SELECT @var29, @var30, @var31, @var32; + +TRUNCATE TABLE t1; + +--echo # INSERT ... SET + +INSERT INTO t1 SET id1=1, val1='a', d1=1.00101; +INSERT INTO t1 SET id1=2, val1='b', d1=1.00102; +INSERT INTO t1 SET id1=3, val1='c', d1=1.00103; + +--error ER_DUP_ENTRY +INSERT INTO t1 SET id1=1, val1='a', d1=1.00101; +GET DIAGNOSTICS CONDITION 1 @var33= ROW_NUMBER; +GET DIAGNOSTICS CONDITION 2 @var34= ROW_NUMBER; +SELECT @var33, @var34; + +--error ER_ILLEGAL_VALUE_FOR_TYPE +INSERT INTO t1 SET id1=6, val1='f', d1=1.79769313486232e+308); +GET DIAGNOSTICS CONDITION 1 @var35= ROW_NUMBER; +GET DIAGNOSTICS CONDITION 2 @var36= ROW_NUMBER; +SELECT @var35, @var36; + + +--error ER_TRUNCATED_WRONG_VALUE +INSERT INTO t1 SET id1=9, val1=CAST(123 AS CHAR(1)), d1=1.00109; +GET DIAGNOSTICS CONDITION 1 @var37= ROW_NUMBER; +GET DIAGNOSTICS CONDITION 2 @var38= ROW_NUMBER; +SELECT @var37, @var38; + +--error ER_TRUNCATED_WRONG_VALUE_FOR_FIELD +INSERT INTO t1 SET id1='x', val1='foo', d1=1.0203; +GET DIAGNOSTICS CONDITION 1 @var39= ROW_NUMBER; +GET DIAGNOSTICS CONDITION 2 @var40= ROW_NUMBER; +SELECT @var39, @var40; + +--error ER_DIVISION_BY_ZERO +INSERT INTO t1 SET id1=3/0, val1='p', d1=1.0116; +GET DIAGNOSTICS CONDITION 1 @var41= ROW_NUMBER; +GET DIAGNOSTICS CONDITION 2 @var42= ROW_NUMBER; +GET DIAGNOSTICS CONDITION 3 @var43= ROW_NUMBER; +GET DIAGNOSTICS CONDITION 4 @var44= ROW_NUMBER; +SELECT @var41, @var42, @var43, @var44; + +TRUNCATE TABLE t1; + +--echo # INSERT ... ON DUPLICATE KEY UPDATE + +INSERT INTO t1 VALUES(1,'a',1.00101), (2,'b',1.00102), (3,'c',1.00103); + +INSERT INTO t1 VALUES (4,'d',1.00104),(1,'a',1.00101) +ON DUPLICATE KEY UPDATE val1='a', d1=1.00101; +GET DIAGNOSTICS CONDITION 1 @var45= ROW_NUMBER; +GET DIAGNOSTICS CONDITION 2 @var46= ROW_NUMBER; +SELECT @var45, @var46; + +--error ER_ILLEGAL_VALUE_FOR_TYPE +INSERT INTO t1 VALUES (5,'e',1.00105),(3,'f',1.79769313486232e+308) +ON DUPLICATE KEY UPDATE val1='c'; +GET DIAGNOSTICS CONDITION 1 @var47= ROW_NUMBER; +GET DIAGNOSTICS CONDITION 2 @var48= ROW_NUMBER; +SELECT @var47, @var48; + +--error ER_WRONG_VALUE_COUNT_ON_ROW +INSERT INTO t1 VALUES (5,'e',1.00105), (3,'i') +ON DUPLICATE KEY UPDATE val1='c'; +GET DIAGNOSTICS CONDITION 1 @var49= ROW_NUMBER; +GET DIAGNOSTICS CONDITION 2 @var50= ROW_NUMBER; +SELECT @var49, @var50; + +--error ER_TRUNCATED_WRONG_VALUE +INSERT INTO t1 VALUES (3, CAST(123 AS CHAR(1)), 1.00103) +ON DUPLICATE KEY UPDATE val1='c'; +GET DIAGNOSTICS CONDITION 1 @var51= ROW_NUMBER; +GET DIAGNOSTICS CONDITION 2 @var52= ROW_NUMBER; +SELECT @var51, @var52; + +INSERT INTO t1 VALUES (10,10,10),('x','foo',1.0203) +ON DUPLICATE KEY UPDATE val1='c'; +GET DIAGNOSTICS CONDITION 1 @var53= ROW_NUMBER; +GET DIAGNOSTICS CONDITION 2 @var54= ROW_NUMBER; +SELECT @var53, @var54; + +--error ER_OPERAND_COLUMNS +INSERT INTO t1 VALUES (11,11,11),(12,12,(13,'m',1.0113)) ON DUPLICATE KEY UPDATE val1='c'; +GET DIAGNOSTICS CONDITION 1 @var55= ROW_NUMBER; +GET DIAGNOSTICS CONDITION 2 @var56= ROW_NUMBER; +SELECT @var55, @var56; + +INSERT INTO t1 VALUES (14,'n',1.0114), (15,'o',1.0115), (2/0,'p',1.00102) +ON DUPLICATE KEY UPDATE val1='b'; +GET DIAGNOSTICS CONDITION 1 @var57= ROW_NUMBER; +GET DIAGNOSTICS CONDITION 2 @var58= ROW_NUMBER; +GET DIAGNOSTICS CONDITION 3 @var59= ROW_NUMBER; +SELECT @var57, @var58, @var59; + +--error ER_DUP_ENTRY +INSERT INTO t1 VALUES (1,'e',1.0),(5,'e',1.0),(5,'f',1.7) +ON DUPLICATE KEY UPDATE id1='1'; +GET DIAGNOSTICS CONDITION 1 @var60= ROW_NUMBER; +GET DIAGNOSTICS CONDITION 2 @var61= ROW_NUMBER; +SELECT @var60, @var61; + +TRUNCATE TABLE t1; + +DROP TABLE t1; +DROP TABLE t2; + +--echo INSERT ... SELECT + +CREATE TABLE t1(id1 INT PRIMARY KEY, val1 VARCHAR(10), d1 DOUBLE); +CREATE TABLE t2(id2 INT PRIMARY KEY, val2 VARCHAR(10), d2 DOUBLE, p2 POINT); + +INSERT INTO t2 VALUES(1,'a',1.00101,PointFromText('POINT(20 10)')), + (2,'b',1.00102,PointFromText('POINT(20 10)')), + (3,'c',1.00103,PointFromText('POINT(20 10)')); +INSERT INTO t1 SELECT id2, val2, d2 FROM t2; + +--error ER_DUP_ENTRY +INSERT INTO t1 SELECT id2, val2, d2 FROM t2 WHERE id2=1; +GET DIAGNOSTICS CONDITION 1 @var62= ROW_NUMBER; +GET DIAGNOSTICS CONDITION 2 @var63= ROW_NUMBER; +SELECT @var62, @var63; + +--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION +INSERT INTO t1 SELECT id2, val2, p2 from t2; +GET DIAGNOSTICS CONDITION 1 @var64= ROW_NUMBER; +GET DIAGNOSTICS CONDITION 2 @var65= ROW_NUMBER; +SELECT @var64, @var65; + +--error ER_WRONG_VALUE_COUNT_ON_ROW +INSERT INTO t1 SELECT id2, val2 FROM t2; +GET DIAGNOSTICS CONDITION 1 @var66= ROW_NUMBER; +GET DIAGNOSTICS CONDITION 2 @var67= ROW_NUMBER; +SELECT @var66, @var67; + +INSERT INTO t2 VALUES(4,'a',1.00101,PointFromText('POINT(20 10)')); +--error ER_TRUNCATED_WRONG_VALUE +INSERT INTO t1 SELECT id2, CAST(123 AS CHAR(1)), d2 FROM t2 WHERE id2=4; +GET DIAGNOSTICS CONDITION 1 @var68= ROW_NUMBER; +GET DIAGNOSTICS CONDITION 2 @var69= ROW_NUMBER; +SELECT @var68, @var69; + +--error ER_ILLEGAL_VALUE_FOR_TYPE +INSERT INTO t1 SELECT id2, val2, 1.79769313486232e+308 FROM t2; +GET DIAGNOSTICS CONDITION 1 @var70= ROW_NUMBER; +GET DIAGNOSTICS CONDITION 2 @var71= ROW_NUMBER; +SELECT @var70, @var71; + + +--error ER_DIVISION_BY_ZERO +INSERT INTO t1 SELECT id2/0, val2, d2 FROM t2; +GET DIAGNOSTICS CONDITION 1 @var72= ROW_NUMBER; +GET DIAGNOSTICS CONDITION 2 @var73= ROW_NUMBER; +GET DIAGNOSTICS CONDITION 3 @var74= ROW_NUMBER; +SELECT @var72, @var73, @var74; + +DROP TABLE t1,t2; + +--echo # Checking ROW_NUMBER in STORED PROCEDURE for INSERT + +CREATE TABLE t1(id1 INT PRIMARY KEY); + +DELIMITER |; + +CREATE PROCEDURE proc1 () +BEGIN + + DECLARE var75 INT; + DECLARE var76 INT; + + INSERT INTO t1 VALUES (1),(2); + INSERT IGNORE INTO t1 VALUES(2); + + GET DIAGNOSTICS CONDITION 1 var75= ROW_NUMBER; + GET DIAGNOSTICS CONDITION 2 var76= ROW_NUMBER; + + SELECT var75, var76; + +END; +| + +DELIMITER ;| + +CALL proc1(); +TRUNCATE TABLE t1; + +--echo # Checking ROW_NUMBER in PREPARED STATEMENTS for INSERT + +INSERT INTO t1 VALUES (1), (2), (3); +INSERT IGNORE INTO t1 VALUES(2),(3),(4); + +PREPARE stmt1 FROM "GET DIAGNOSTICS CONDITION 2 @var77= ROW_NUMBER"; +EXECUTE stmt1; +SELECT @var77; + +DROP PROCEDURE proc1; +DROP PREPARE stmt1; +DROP TABLE t1; + +--echo # +--echo # REPLACE STATEMENT +--echo # + +CREATE TABLE t1(id1 INT PRIMARY KEY, val1 VARCHAR(10), d1 DOUBLE); +CREATE TABLE t2(id2 INT PRIMARY KEY, val2 VARCHAR(10), d2 DOUBLE); + + +--echo # Simple REPLACE statement + +REPLACE INTO t1 VALUES(1,'a',1.00101), (2,'b',1.00102), (3,'c',1.00103); +REPLACE INTO t1 VALUES (4,'d',1.00104),(1,'a',1.00101),(2,'b',1.00102); +GET DIAGNOSTICS CONDITION 1 @var78= ROW_NUMBER; +SELECT @var78; + +--error ER_ILLEGAL_VALUE_FOR_TYPE +REPLACE INTO t1 VALUES (5,'e',1.00105),(6,'f',1.79769313486232e+308); +GET DIAGNOSTICS CONDITION 1 @var79= ROW_NUMBER; +GET DIAGNOSTICS CONDITION 2 @var80= ROW_NUMBER; +SELECT @var79, @var80; + +--error ER_WRONG_VALUE_COUNT_ON_ROW +REPLACE INTO t1 VALUES (7,'g',1.00107),(8,'h',1.00108),(9,'i'),(10,'j'); +GET DIAGNOSTICS CONDITION 1 @var81= ROW_NUMBER; +GET DIAGNOSTICS CONDITION 2 @var82= ROW_NUMBER; +SELECT @var81, @var82; + +REPLACE INTO t1 VALUES (8,8,8),(9,CAST(123 AS CHAR(1)),1.00109); +GET DIAGNOSTICS CONDITION 1 @var83= ROW_NUMBER; +GET DIAGNOSTICS CONDITION 2 @var84= ROW_NUMBER; +SELECT @var83, @var84; + +REPLACE INTO t1 VALUES (10,10,10),('x','foo',1.0203); +GET DIAGNOSTICS CONDITION 1 @var85= ROW_NUMBER; +GET DIAGNOSTICS CONDITION 2 @var86= ROW_NUMBER; +SELECT @var85, @var86; + +--error ER_OPERAND_COLUMNS +REPLACE INTO t1 VALUES (11,11,11),(12,12,(13,'m',1.0113)); +GET DIAGNOSTICS CONDITION 1 @var87= ROW_NUMBER; +GET DIAGNOSTICS CONDITION 2 @var88= ROW_NUMBER; +SELECT @var87, @var88; + +REPLACE INTO t1 VALUES (14,'n',1.0114), (15,'o',1.0115), (16/0,'p',1.0116); +GET DIAGNOSTICS CONDITION 1 @var89= ROW_NUMBER; +GET DIAGNOSTICS CONDITION 2 @var90= ROW_NUMBER; +GET DIAGNOSTICS CONDITION 3 @var91= ROW_NUMBER; +SELECT @var89, @var90, @var91; + +TRUNCATE TABLE t1; + +--echo # REPLACE ... SET + +REPLACE INTO t1 SET id1=1, val1='a', d1=1.00101; +REPLACE INTO t1 SET id1=2, val1='b', d1=1.00102; +REPLACE INTO t1 SET id1=3, val1='c', d1=1.00103; + +REPLACE INTO t1 SET id1=1, val1='a', d1=1.00101; +GET DIAGNOSTICS CONDITION 1 @var92= ROW_NUMBER; +SELECT @var92; + +--error ER_ILLEGAL_VALUE_FOR_TYPE +REPLACE INTO t1 SET id1=6, val1='f', d1=1.79769313486232e+308); +GET DIAGNOSTICS CONDITION 1 @var93= ROW_NUMBER; +GET DIAGNOSTICS CONDITION 2 @var94= ROW_NUMBER; +SELECT @var93, @var94; + + +--error ER_TRUNCATED_WRONG_VALUE +REPLACE INTO t1 SET id1=9, val1=CAST(123 AS CHAR(1)), d1=1.00109; +GET DIAGNOSTICS CONDITION 1 @var95= ROW_NUMBER; +GET DIAGNOSTICS CONDITION 2 @var96= ROW_NUMBER; +SELECT @var95, @var96; + +--error ER_TRUNCATED_WRONG_VALUE_FOR_FIELD +REPLACE INTO t1 SET id1='x', val1='foo', d1=1.0203; +GET DIAGNOSTICS CONDITION 1 @var97= ROW_NUMBER; +GET DIAGNOSTICS CONDITION 2 @var98= ROW_NUMBER; +SELECT @var97, @var98; + +--error ER_DIVISION_BY_ZERO +REPLACE INTO t1 SET id1=3/0, val1='p', d1=1.0116; +GET DIAGNOSTICS CONDITION 1 @var99= ROW_NUMBER; +GET DIAGNOSTICS CONDITION 2 @var100= ROW_NUMBER; +GET DIAGNOSTICS CONDITION 3 @var101= ROW_NUMBER; +GET DIAGNOSTICS CONDITION 4 @var102= ROW_NUMBER; +SELECT @var99, @var100, @var101, @var102; + +TRUNCATE TABLE t1; + +DROP TABLE t1; +DROP TABLE t2; + +--echo # REPLACE ... SELECT + +CREATE TABLE t1(id1 INT PRIMARY KEY, val1 VARCHAR(10), d1 DOUBLE); +CREATE TABLE t2(id2 INT PRIMARY KEY, val2 VARCHAR(10), d2 DOUBLE, p2 POINT); + + +INSERT INTO t2 VALUES(1,'a',1.00101,PointFromText('POINT(20 10)')), + (2,'b',1.00102,PointFromText('POINT(20 10)')), + (3,'c',1.00103,PointFromText('POINT(20 10)')); +INSERT INTO t1 SELECT id2, val2, d2 FROM t2; + +--error ER_DUP_ENTRY +INSERT INTO t1 SELECT id2, val2, d2 FROM t2 WHERE id2=1; +GET DIAGNOSTICS CONDITION 1 @var103= ROW_NUMBER; +GET DIAGNOSTICS CONDITION 2 @var104= ROW_NUMBER; +SELECT @var103, @var104; + +--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION +INSERT INTO t1 SELECT id2, val2, p2 from t2; +GET DIAGNOSTICS CONDITION 1 @var105= ROW_NUMBER; +GET DIAGNOSTICS CONDITION 2 @var106= ROW_NUMBER; +SELECT @var105, @var106; + +--error ER_WRONG_VALUE_COUNT_ON_ROW +INSERT INTO t1 SELECT id2, val2 FROM t2; +GET DIAGNOSTICS CONDITION 1 @var107= ROW_NUMBER; +GET DIAGNOSTICS CONDITION 2 @var108= ROW_NUMBER; +SELECT @var107, @var108; + +INSERT INTO t2 VALUES(4,'a',1.00101,PointFromText('POINT(20 10)')); +--error ER_TRUNCATED_WRONG_VALUE +INSERT INTO t1 SELECT id2, CAST(123 AS CHAR(1)), d2 FROM t2 WHERE id2=4; +GET DIAGNOSTICS CONDITION 1 @var109= ROW_NUMBER; +GET DIAGNOSTICS CONDITION 2 @var110= ROW_NUMBER; +SELECT @var109, @var110; + +--error ER_ILLEGAL_VALUE_FOR_TYPE +INSERT INTO t1 SELECT id2, val2, 1.79769313486232e+308 FROM t2; +GET DIAGNOSTICS CONDITION 1 @var111= ROW_NUMBER; +GET DIAGNOSTICS CONDITION 2 @var112= ROW_NUMBER; +SELECT @var111, @var112; + + +--error ER_DIVISION_BY_ZERO +INSERT INTO t1 SELECT id2/0, val2, d2 FROM t2; +GET DIAGNOSTICS CONDITION 1 @var113= ROW_NUMBER; +GET DIAGNOSTICS CONDITION 2 @var114= ROW_NUMBER; +GET DIAGNOSTICS CONDITION 3 @var115= ROW_NUMBER; +SELECT @var113, @var114, @var115; + +DROP TABLE t1,t2; + +--echo # Checking ROW_NUMBER in STORED PROCEDURE + +CREATE TABLE t1(id1 INT PRIMARY KEY); + +DELIMITER |; + +CREATE PROCEDURE proc1 () +BEGIN + + DECLARE var116 INT; + + REPLACE INTO t1 VALUES (1),(2); + + GET DIAGNOSTICS CONDITION 1 var116= ROW_NUMBER; + + SELECT var116; + +END; +| + +DELIMITER ;| + +CALL proc1(); +TRUNCATE TABLE t1; + +--echo # Checking ROW_NUMBER in PREPARED STATEMENTS + +REPLACE INTO t1 VALUES (1), (2), (3); + +PREPARE stmt1 FROM "GET DIAGNOSTICS CONDITION 2 @var117= ROW_NUMBER"; +EXECUTE stmt1; +SELECT @var117; + +DROP PROCEDURE proc1; +DROP PREPARE stmt1; +DROP TABLE t1; + +--echo # +--echo # Checking that ROW_NUMBER is only for errors with rows/values +--echo # + +--echo # INSERT STATEMENT + +CREATE TABLE t1(id1 INT); +CREATE TABLE t2(id2 INT); +CREATE VIEW v AS SELECT t1.id1 AS A, t2.id2 AS b FROM t1,t2; + +--error ER_FIELD_SPECIFIED_TWICE +INSERT INTO t1(id1, id1) VALUES (1,1); +GET DIAGNOSTICS CONDITION 1 @var118= ROW_NUMBER; +SELECT @var118; + +delimiter |; +CREATE FUNCTION f1() RETURNS INT +BEGIN + INSERT INTO t1 VALUES (1); + RETURN 1; +END | +delimiter ;| + +--error ER_CANT_UPDATE_USED_TABLE_IN_SF_OR_TRG +INSERT INTO t1 VALUES (1), (f1()); +GET DIAGNOSTICS CONDITION 1 @var119= ROW_NUMBER; +SELECT @var119; + +--error ER_BAD_FIELD_ERROR +INSERT INTO t1 VALUES (1) RETURNING id2; +GET DIAGNOSTICS CONDITION 1 @var120= ROW_NUMBER; +SELECT @var120; + +--error ER_BAD_FIELD_ERROR +INSERT INTO t1(id2) VALUES(1); +GET DIAGNOSTICS CONDITION 1 @var121= ROW_NUMBER; +SELECT @var121; + +--error ER_VIEW_NO_INSERT_FIELD_LIST +INSERT INTO v VALUES(1,2); +GET DIAGNOSTICS CONDITION 1 @var122= ROW_NUMBER; +SELECT @var122; + +--error ER_VIEW_MULTIUPDATE +INSERT INTO v(a,b) VALUES (1,2); +GET DIAGNOSTICS CONDITION 1 @var123= ROW_NUMBER; +SELECT @var123; + +--echo # REPLACE STATEMENT + +--error ER_FIELD_SPECIFIED_TWICE +REPLACE INTO t1(id1, id1) VALUES (1,1); +GET DIAGNOSTICS CONDITION 1 @var124= ROW_NUMBER; +SELECT @var124; + +delimiter |; +CREATE FUNCTION f2() RETURNS INT +BEGIN + REPLACE INTO t1 VALUES (1); + RETURN 1; +END | +delimiter ;| + +--error ER_CANT_UPDATE_USED_TABLE_IN_SF_OR_TRG +REPLACE INTO t1 VALUES (1), (f2()); +GET DIAGNOSTICS CONDITION 1 @var125= ROW_NUMBER; +SELECT @var125; + +--error ER_BAD_FIELD_ERROR +REPLACE INTO t1 VALUES (1) RETURNING id2; +GET DIAGNOSTICS CONDITION 1 @var126= ROW_NUMBER; +SELECT @var126; + +--error ER_BAD_FIELD_ERROR +REPLACE INTO t1(id2) VALUES(1); +GET DIAGNOSTICS CONDITION 1 @var127= ROW_NUMBER; +SELECT @var127; + +--error ER_VIEW_NO_INSERT_FIELD_LIST +REPLACE INTO v VALUES(1,2); +GET DIAGNOSTICS CONDITION 1 @var128= ROW_NUMBER; +SELECT @var128; + +--error ER_VIEW_MULTIUPDATE +REPLACE INTO v(a,b) VALUES (1,2); +GET DIAGNOSTICS CONDITION 1 @var129= ROW_NUMBER; +SELECT @var129; + +DROP TABLE t1,t2; +DROP FUNCTION f1; +DROP FUNCTION f2; +DROP VIEW v; + +--echo # +--echo # MDEV-26606: ROW_NUMBER property value isn't passed from inside a +--echo # stored procedure +--echo # + +--echo # Test 1: Without RESIGNAL + +CREATE OR REPLACE TABLE t1 (pk INT PRIMARY KEY); +CREATE OR REPLACE PROCEDURE sp(a INT) INSERT INTO t1 VALUES (2),(a); +SET @num=null, @msg=null; + +INSERT INTO t1 VALUES(1); + +--error ER_DUP_ENTRY +CALL sp(1); +GET DIAGNOSTICS CONDITION 1 @num = ROW_NUMBER, @msg = MESSAGE_TEXT; +SELECT @num, @msg; + +DROP PROCEDURE sp; +DROP TABLE t1; + +--echo # Test 2: With RESIGNAL + +CREATE OR REPLACE TABLE t1 (pk INT PRIMARY KEY); + +DELIMITER |; +CREATE OR REPLACE PROCEDURE sp(a INT) +BEGIN + DECLARE n INT; + DECLARE m VARCHAR(255); + DECLARE EXIT HANDLER FOR 1062 + BEGIN + GET DIAGNOSTICS CONDITION 1 n = ROW_NUMBER, m = MESSAGE_TEXT; + SELECT n, m; + RESIGNAL; + END; + INSERT INTO t1 VALUES (2), (a); +END | +DELIMITER ;| + +SET @num=null, @msg=null; +INSERT INTO t1 VALUES (1); + +--error ER_DUP_ENTRY +CALL sp(1); +GET DIAGNOSTICS CONDITION 1 @num = ROW_NUMBER, @msg = MESSAGE_TEXT; +SELECT @num, @msg; + +DROP PROCEDURE sp; +DROP TABLE t1; + +--echo # Checking more errors + +CREATE TABLE t1 (val1 TINYINT); + +CREATE PROCEDURE sp(a INT) INSERT INTO t1 VALUES (2),(a); + +INSERT INTO t1 VALUES(1); + +CALL sp(100000); +GET DIAGNOSTICS CONDITION 1 @var1= ROW_NUMBER; +SELECT @var1; + +DROP TABLE t1; +DROP PROCEDURE sp; + +--echo # +--echo # MDEV-26684: Unexpected ROW_NUMBER in a condition raised by a diagnostics statement +--echo # + +CREATE TABLE t1 (a INT); + +INSERT INTO t1 VALUES (1),(2); + +GET DIAGNOSTICS CONDITION 5 @msg = MESSAGE_TEXT; +SHOW WARNINGS; + +GET DIAGNOSTICS CONDITION 1 @ind = ROW_NUMBER, @msg = MESSAGE_TEXT; +SELECT @ind, @msg; + +INSERT INTO t1 VALUES (3),(4); + +GET DIAGNOSTICS CONDITION 1 @ind = ROW_NUMBER, @msg = MESSAGE_TEXT; +SELECT @ind, @msg; + +GET DIAGNOSTICS CONDITION 5 @ind = ROW_NUMBER, @msg = MESSAGE_TEXT; +SELECT @ind, @msg; + +DROP TABLE t1; + +--echo # +--echo # MDEV-26681: ROW_NUMBER is not available within compound statement blocks +--echo # + +CREATE OR REPLACE TABLE t1 (pk INT PRIMARY KEY, a CHAR(3)); +INSERT IGNORE INTO t1 VALUES (1,'foo'),(1,'bar'),(2,'foobar'); + +DELIMITER |; + +BEGIN NOT ATOMIC + DECLARE i INT DEFAULT 0; + DECLARE rnum INT DEFAULT -1; + DECLARE msg VARCHAR(1024) DEFAULT ''; + DECLARE err INT DEFAULT -1; + WHILE i < @@warning_count + DO + SET i = i + 1; + GET DIAGNOSTICS CONDITION i rnum = ROW_NUMBER, msg = MESSAGE_TEXT, err = MYSQL_ERRNO; + SELECT i, rnum, msg, err; + END WHILE; +END | + +DELIMITER ;| + +GET DIAGNOSTICS CONDITION 1 @rnum = ROW_NUMBER, @msg = MESSAGE_TEXT, @err = MYSQL_ERRNO; +select @rnum, @msg, @err; + +GET DIAGNOSTICS CONDITION 2 @rnum = ROW_NUMBER, @msg = MESSAGE_TEXT, @err = MYSQL_ERRNO; +SELECT @rnum, @msg, @err; + +DROP TABLE t1; + +--echo # +--echo # ROW_NUMBER differs from the number in the error message upon +--echo # ER_WARN_DATA_OUT_OF_RANGE +--echo # +CREATE TABLE t (a INT); +INSERT INTO t VALUES (1),(2); +SELECT CAST(a AS DECIMAL(2,2)) AS f FROM t; + +GET DIAGNOSTICS CONDITION 2 @n= ROW_NUMBER, @m = MESSAGE_TEXT; +SELECT @n, @m; + +DROP TABLE t; + +--echo # +--echo # MDEV-26832: ROW_NUMBER in SIGNAL/RESIGNAL causes a syntax error +--echo # + +--echo # using signal + +DELIMITER |; +CREATE PROCEDURE signal_syntax() +BEGIN + DECLARE errno INT DEFAULT 0; + DECLARE msg TEXT DEFAULT "foo"; + DECLARE row_num INT DEFAULT 0; + DECLARE cond CONDITION FOR SQLSTATE "01234"; + DECLARE CONTINUE HANDLER for 1012 + BEGIN + GET DIAGNOSTICS CONDITION 1 errno = MYSQL_ERRNO, msg = MESSAGE_TEXT, row_num= ROW_NUMBER; + END; + + SIGNAL cond SET MESSAGE_TEXT = "Signal message", MYSQL_ERRNO = 1012, ROW_NUMBER= 5; + + SELECT errno, msg, row_num; +END| +DELIMITER ;| + +CALL signal_syntax(); + +DROP PROCEDURE signal_syntax; + +--echo # using resignal + +DELIMITER |; + +CREATE PROCEDURE resignal_syntax() +BEGIN + DECLARE CONTINUE HANDLER + FOR 1146 + BEGIN + RESIGNAL SET + MESSAGE_TEXT = '`temptab` does not exist', ROW_NUMBER= 105; + END; + SELECT `c` FROM `temptab`; +END| + +DELIMITER ;| + +--error ER_NO_SUCH_TABLE +CALL resignal_syntax(); + +GET DIAGNOSTICS CONDITION 1 @row_num= ROW_NUMBER; +SELECT @row_num; + +DROP PROCEDURE resignal_syntax; + +--echo # +--echo # MDEV-26842: ROW_NUMBER is not set and differs from the message upon +--echo # WARN_DATA_TRUNCATED produced by inplace ALTER +--echo # + +CREATE TABLE t1 (a VARCHAR(64)) ENGINE=InnoDB; +INSERT INTO t1 VALUES ('foo'),(null); + +--error WARN_DATA_TRUNCATED +ALTER TABLE t1 ADD PRIMARY KEY(a), ALGORITHM=INPLACE; + +GET DIAGNOSTICS CONDITION 1 @n= ROW_NUMBER, @m= MESSAGE_TEXT; +SELECT @n, @m; + +DROP TABLE t1; + +--echo # +--echo # MDEV-26841: ROW_NUMBER is not set and differs from the message upon +--echo # ER_WRONG_VALUE_COUNT_ON_ROW for the 1st row +--echo # + +CREATE TABLE t1 (a INT); + +--error ER_WRONG_VALUE_COUNT_ON_ROW +INSERT INTO t1 VALUES (1,2),(3); + +GET DIAGNOSTICS CONDITION 1 @n= ROW_NUMBER, @m= MESSAGE_TEXT; +SELECT @n, @m; + +--error ER_WRONG_VALUE_COUNT_ON_ROW +INSERT INTO t1(a) VALUES(1,2), (3); + +GET DIAGNOSTICS CONDITION 1 @n= ROW_NUMBER, @m= MESSAGE_TEXT; +SELECT @n, @m; + +DROP TABLE t1; + +--echo # +--echo # MDEV-26830: Wrong ROW_NUMBER in diagnostics upon INSERT IGNORE with +--echo # CHECK violation +--echo # + +CREATE TABLE t1 (a INT, CHECK(a>0)); +INSERT IGNORE INTO t1 VALUES (1),(0),(2),(0); +GET DIAGNOSTICS CONDITION 1 @n= ROW_NUMBER; +SELECT @n; +GET DIAGNOSTICS CONDITION 2 @n= ROW_NUMBER; +SELECT @n; + +DROP TABLE t1; + +--echo # +--echo # MDEV-26844: DELETE returns ROW_NUMBER=1 for every row upon +--echo # ER_TRUNCATED_WRONG_VALUE +--echo # + +--echo # without ORDER BY + +CREATE TABLE t (a VARCHAR(8)); + +INSERT INTO t VALUES ('val1'),('val2'),('100'),('val4'); +SELECT * FROM t; + +DELETE FROM t WHERE a = 100; +SHOW WARNINGS; + +GET DIAGNOSTICS CONDITION 3 @n = ROW_NUMBER; +SELECT @n; + +DROP TABLE t; + +--echo # +--echo # MDEV-26695: Number of an invalid row is not calculated for table value constructor +--echo # + +CREATE TABLE t1 (a CHAR(1)) VALUES ('a'),('b'),('foo'); +GET DIAGNOSTICS CONDITION 1 @n= ROW_NUMBER; +SELECT @n; +CREATE TABLE t2 (a CHAR(1)) VALUES ('a'),('b') UNION VALUES ('foo'); +GET DIAGNOSTICS CONDITION 1 @n= ROW_NUMBER; +SELECT @n; + +DROP TABLE t1, t2; + +--echo # Checking that CREATE ... SELECT works + +CREATE TABLE t1 (val1 CHAR(5)); +INSERT INTO t1 VALUES ('A'),('B'),('C'),('DEF'); +CREATE TABLE t2 (val2 CHAR(1)) SELECT val1 as val2 FROM t1; +GET DIAGNOSTICS CONDITION 1 @n= ROW_NUMBER; +SELECT @n; +SELECT * FROM t2; + +DROP TABLE t1,t2; + +--echo # +--echo # End of 10.7 tests +--echo # + |