mariadb/mysql-test/main/sp-group.test

--source include/have_innodb.inc

drop table if exists t1;
drop view if exists view_t1;

#
# Test case for MDEV 7601, MDEV-7594 and MDEV-7555
# Server crashes in functions related to stored procedures
# Server crashes in different ways while executing concurrent
# flow involving views and non-empty sql_mode with ONLY_FULL_GROUP_BY
#

SET sql_mode=ONLY_FULL_GROUP_BY;

CREATE TABLE t1 (
  pk INT, 
  f0 INT, f1 INT, f2 INT, f3 INT, f4 INT, 
  f5 INT, f6 INT, f7 INT, f8 INT, f9 INT, 
  PRIMARY KEY (pk)
);

CREATE VIEW view_t1 AS SELECT * FROM t1;
CREATE PROCEDURE s1() 
  SELECT * FROM (
  INFORMATION_SCHEMA.`INNODB_BUFFER_PAGE_LRU` AS table1
  LEFT JOIN test.view_t1 AS table2
  ON ( table2.`f6` = table1.FREE_PAGE_CLOCK) 
  ) 
  ORDER BY table1.NUMBER_RECORDS
  LIMIT 0
;
CALL s1;
CALL s1;

drop table t1;
drop view view_t1;
drop procedure s1;

#
# MDEV-7590
# Server crashes in st_select_lex_unit::cleanup on executing a trigger
#

CREATE TABLE A (
    pk INTEGER AUTO_INCREMENT,
    col_int_key INTEGER,
    col_varchar_key VARCHAR(1),
    PRIMARY KEY (pk)
) ENGINE=MyISAM;
CREATE VIEW view_A AS SELECT * FROM A;
CREATE TABLE C (
    pk INTEGER AUTO_INCREMENT,
    col_int_nokey INTEGER,
    col_int_key INTEGER,
    col_date_key DATE,
    col_date_nokey DATE,
    col_time_key TIME,
    col_time_nokey TIME,
    col_datetime_key DATETIME,
    col_datetime_nokey DATETIME,
    col_varchar_key VARCHAR(1),
    col_varchar_nokey VARCHAR(1),
    PRIMARY KEY (pk)
) ENGINE=MyISAM;
CREATE VIEW view_C AS SELECT * FROM C;
CREATE TABLE AA (
    pk INTEGER AUTO_INCREMENT,
    col_int_nokey INTEGER,
    col_int_key INTEGER,
    col_date_key DATE,
    col_date_nokey DATE,
    col_time_key TIME,
    col_time_nokey TIME,
    col_datetime_key DATETIME,
    col_datetime_nokey DATETIME,
    col_varchar_key VARCHAR(1),
    col_varchar_nokey VARCHAR(1),
    PRIMARY KEY (pk),
    KEY (col_varchar_key, col_int_key)
) ENGINE=MyISAM;
CREATE VIEW view_AA AS SELECT * FROM AA;
CREATE TABLE BB (
    pk INTEGER AUTO_INCREMENT,
    col_int_key INTEGER,
    col_varchar_key VARCHAR(1),
    col_varchar_nokey VARCHAR(1),
    PRIMARY KEY (pk),
    KEY (col_varchar_key, col_int_key)
) ENGINE=MyISAM;
CREATE VIEW view_BB AS SELECT * FROM BB;
CREATE TABLE DD (
    pk INTEGER AUTO_INCREMENT,
    col_int_key INTEGER,
    col_date_key DATE,
    col_time_key TIME,
    col_datetime_key DATETIME,
    col_varchar_key VARCHAR(1),
    PRIMARY KEY (pk),
    KEY (col_varchar_key, col_int_key)
) ENGINE=MyISAM;
CREATE VIEW view_DD AS SELECT * FROM DD;
CREATE TRIGGER k BEFORE INSERT ON `DD` FOR EACH ROW INSERT INTO `view_BB` SELECT * FROM `view_A` LIMIT 0 ;
CREATE TRIGGER r BEFORE INSERT ON `A` FOR EACH ROW INSERT INTO `view_AA` SELECT * FROM `view_C` LIMIT 0 ;
--error ER_WRONG_AUTO_KEY
ALTER TABLE `DD` DROP PRIMARY KEY;
INSERT INTO `view_A` ( `pk` ) VALUES (NULL);
--error 0,ER_WRONG_VALUE_COUNT_ON_ROW
INSERT INTO `DD` ( `pk` ) VALUES (NULL);
INSERT INTO `A` ( `pk` ) VALUES (NULL);
--error 0,ER_WRONG_VALUE_COUNT_ON_ROW
INSERT INTO `view_DD` ( `pk` ) VALUES (NULL);

drop trigger r;
drop trigger k;
drop view view_A,view_AA,view_C,view_BB,view_DD;
drop table A,C,AA,BB,DD;

#
# MDEV-7581
# Server crashes in st_select_lex_unit::cleanup after a sequence of statements
#

CREATE TABLE A (
 i INT,
 i1 INT,
 i2 INT,
 d1 DATE,
 d2 DATE,
 col_time_nokey1 TIME,
 col_time_nokey2 TIME,
 col_datetime_nokey1 DATETIME,
 col_datetime_nokey2 DATETIME,
 col_varchar_nokey1 VARCHAR(1),
 col_varchar_nokey2 VARCHAR(1)
) ENGINE=MyISAM;

CREATE VIEW view_A AS SELECT * FROM A;

CREATE TABLE B (
 col_varchar_nokey VARCHAR(1)
) ENGINE=MyISAM;

CREATE TABLE AA (
 i INT,
 i1 INT,
 i2 INT,
 d1 DATE,
 d2 DATE,
 col_time_nokey1 TIME,
 col_time_nokey2 TIME,
 col_datetime_nokey1 DATETIME,
 col_datetime_nokey2 DATETIME,
 col_varchar_nokey1 VARCHAR(1),
 col_varchar_nokey2 VARCHAR(1)
) ENGINE=MyISAM;

CREATE VIEW view_AA AS SELECT * FROM AA;

CREATE TABLE DD (
 i INT,
 i1 INT,
 i2 INT,
 d1 DATE,
 d2 DATE,
 col_time_nokey1 TIME,
 col_time_nokey2 TIME,
 col_datetime_nokey1 DATETIME,
 col_datetime_nokey2 DATETIME,
 col_varchar_nokey1 VARCHAR(1),
 col_varchar_nokey2 VARCHAR(1)
) ENGINE=MyISAM;

CREATE VIEW view_DD AS SELECT * FROM DD;

CREATE TRIGGER tr1 BEFORE INSERT ON `AA` FOR EACH ROW INSERT INTO `view_A` SELECT * FROM `view_AA` LIMIT 0 ; 
CREATE TRIGGER tr2 BEFORE INSERT ON `B` FOR EACH ROW INSERT INTO `D` SELECT * FROM `A` LIMIT 0 ; 

INSERT INTO `view_AA` ( `i` ) VALUES (1);
INSERT INTO `AA` ( `i` ) VALUES (2);
DELETE FROM `B`;
INSERT INTO `view_DD` ( `i` ) VALUES (1);
INSERT INTO `view_AA` ( `i` ) VALUES (3);

drop trigger tr1;
drop trigger tr2;
drop view view_A, view_AA,view_DD;
drop table A,B,AA,DD;