path: root/mysql-test/main/get_diagnostics.test

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

SELECT CAST(-19999999999999999999 AS SIGNED),
       CAST(-19999999999999999999 AS SIGNED);
GET DIAGNOSTICS @var = NUMBER;
SELECT @var;

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-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;

--error ER_TRUNCATED_WRONG_VALUE
INSERT INTO t1 VALUES (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;

--error ER_TRUNCATED_WRONG_VALUE_FOR_FIELD
INSERT INTO t1 VALUES ('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 ((13,'m',1.0113));
GET DIAGNOSTICS CONDITION 1 @var11= ROW_NUMBER;
GET DIAGNOSTICS CONDITION 2 @var12= ROW_NUMBER;
SELECT @var11, @var12;

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 (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 ('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 ((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;

--error ER_TRUNCATED_WRONG_VALUE_FOR_FIELD
INSERT INTO t1 VALUES ('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 ((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_TRUNCATED_WRONG_VALUE_FOR_FIELD
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;

--error ER_TRUNCATED_WRONG_VALUE
REPLACE INTO t1 VALUES (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;

--error ER_TRUNCATED_WRONG_VALUE_FOR_FIELD
REPLACE INTO t1 VALUES ('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 ((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_TRUNCATED_WRONG_VALUE_FOR_FIELD
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;