summaryrefslogtreecommitdiff
path: root/mysql-test/t/sp-group.test
blob: 2083ac97595776cada80ae902e4d77d51d0c06fb (plain)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
--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;