summaryrefslogtreecommitdiff
path: root/mysql-test/r/sp-group.result
blob: 2e30b697103fb732e8b375f3b4544ad9b9817356 (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
drop table if exists t1;
Warnings:
Note	1051	Unknown table 'test.t1'
drop view if exists view_t1;
Warnings:
Note	1051	Unknown table 'test.view_t1'
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;
POOL_ID	LRU_POSITION	SPACE	PAGE_NUMBER	PAGE_TYPE	FLUSH_TYPE	FIX_COUNT	IS_HASHED	NEWEST_MODIFICATION	OLDEST_MODIFICATION	ACCESS_TIME	TABLE_NAME	INDEX_NAME	NUMBER_RECORDS	DATA_SIZE	COMPRESSED_SIZE	COMPRESSED	IO_FIX	IS_OLD	FREE_PAGE_CLOCK	pk	f0	f1	f2	f3	f4	f5	f6	f7	f8	f9
CALL s1;
POOL_ID	LRU_POSITION	SPACE	PAGE_NUMBER	PAGE_TYPE	FLUSH_TYPE	FIX_COUNT	IS_HASHED	NEWEST_MODIFICATION	OLDEST_MODIFICATION	ACCESS_TIME	TABLE_NAME	INDEX_NAME	NUMBER_RECORDS	DATA_SIZE	COMPRESSED_SIZE	COMPRESSED	IO_FIX	IS_OLD	FREE_PAGE_CLOCK	pk	f0	f1	f2	f3	f4	f5	f6	f7	f8	f9
drop table t1;
drop view view_t1;
drop procedure s1;
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 ;
ALTER TABLE `DD` DROP PRIMARY KEY;
ERROR 42000: Incorrect table definition; there can be only one auto column and it must be defined as a key
INSERT INTO `view_A` ( `pk` ) VALUES (NULL);
INSERT INTO `DD` ( `pk` ) VALUES (NULL);
INSERT INTO `A` ( `pk` ) VALUES (NULL);
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;
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;