# --disable_abort_on_error

# test case derived from NIST cdr017 TEST:0375 ref. integrity with computation!

--disable_warnings
DROP TABLE IF EXISTS t1, t2;
--enable_warnings

SET SESSION STORAGE_ENGINE=INNODB;

# harmless non crashing variant FOREIGN KEY relation with BIGINT
# Please set $run to 1 to check the non crashing test case
let $run= 0;
while ($run)
{
  CREATE TABLE t1 (F1 BIGINT NOT NULL, UNIQUE (F1));
  CREATE TABLE t2 (F1 BIGINT, FOREIGN KEY (F1) REFERENCES t1(F1));

  DELETE FROM t2 WHERE F1 = 5;

  DROP TABLE t2;
  DROP TABLE t1;
  let $run= 0;
}

# harmless non crashing variant no FOREIGN KEY relation
# Please set $run to 1 to check the non crashing test case
let $run= 0;
while ($run)
{
  CREATE TABLE t1 (F1 DECIMAL(4) NOT NULL, UNIQUE (F1));
  CREATE TABLE t2 (F1 DECIMAL(4));
  INSERT INTO t1 VALUES(2),(5);
  INSERT INTO t2 VALUES(5);

  DELETE FROM t1 WHERE F1 = (SELECT F1 FROM t2 WHERE F1 = 5);

  DROP TABLE t1, t2;
  let $run= 0;
}

# crashing variant FOREIGN KEY relation with DECIMAL
# Please set $run to 1 to check the simplified crashing test case
let $run= 1;
while ($run)
{
  CREATE TABLE t1 (F1 DECIMAL(4) NOT NULL, UNIQUE (F1));

  CREATE TABLE t2 (F1 DECIMAL(4), FOREIGN KEY (F1) REFERENCES t1(F1));

  COMMIT WORK;

  # Here comes the server crash
  DELETE FROM t2 WHERE F1 = 5;

  DROP TABLE t2;
  DROP TABLE t1;
  let $run= 0;
}

# crashing variant FOREIGN KEY relation with DECIMAL, slightly modified
# NIST test case
# Please set $run to 1 to check the NIST test case
let $run= 0;
while ($run)
{
  --disable_warnings
  DROP TABLE IF EXISTS t3, t4, t5, t6, t7, t8, t9, t10;
  --enable_warnings

  CREATE TABLE t1 (F1   CHAR(3) NOT NULL, F2   CHAR(8), UNIQUE (F1));
  CREATE TABLE t2 (F1   CHAR(3) NOT NULL, F2   CHAR(8), UNIQUE (F1));
  CREATE TABLE t3 (F1   DECIMAL(4) NOT NULL, F2   CHAR(8), UNIQUE (F1));
  CREATE TABLE t4 (F1   CHAR(3) NOT NULL, F2   CHAR(8), UNIQUE (F1));
  CREATE TABLE t5 (F1   DECIMAL(4) NOT NULL, F2   CHAR(8), UNIQUE (F1));
  CREATE TABLE t6 (F1   CHAR(3) NOT NULL, F2   CHAR(8), UNIQUE (F1));
  CREATE TABLE t7 (F1   CHAR(3) NOT NULL, F2   CHAR(8), UNIQUE (F1));
  CREATE TABLE t8 (F1   DECIMAL(4) NOT NULL, F2   CHAR(8), UNIQUE (F1));
  CREATE TABLE t9 (F1   DECIMAL(4) NOT NULL, F2   CHAR(8), UNIQUE (F1));
  CREATE TABLE t10 (F1   DECIMAL(4) NOT NULL, F2   CHAR(8), UNIQUE (F1));

  CREATE TABLE t11
     (P1   CHAR(3) NOT NULL,
      P2   CHAR(3),
      P3   DECIMAL(4),
      P4   CHAR(3),
      P5   DECIMAL(4),
      P6   CHAR(3),
      P7   CHAR(3),
      P8   DECIMAL(4),
      P9   DECIMAL(4),
      P10   DECIMAL(4),
      P11   CHAR(4),
      FOREIGN KEY (P1) REFERENCES t1(F1),
      FOREIGN KEY (P2) REFERENCES t2(F1),
      FOREIGN KEY (P3) REFERENCES t3(F1),
      FOREIGN KEY (P4) REFERENCES t4(F1),
      FOREIGN KEY (P5) REFERENCES t5(F1),
      FOREIGN KEY (P6) REFERENCES t6(F1),
      FOREIGN KEY (P7) REFERENCES t7(F1),
      FOREIGN KEY (P8) REFERENCES t8(F1),
      FOREIGN KEY (P9) REFERENCES t9(F1),
      FOREIGN KEY (P10) REFERENCES t10(F1));

  COMMIT WORK;

  # Here comes the server crash
  DELETE FROM t11 WHERE P5 = 5;

  DROP TABLE t11;
  DROP TABLE t1, t2, t3, t4, t5, t6, t7, t8, t9, t10;


  let $run= 0;
}