1# 2# Test keywords as fields 3# 4 5--disable_warnings 6drop table if exists t1; 7--enable_warnings 8 9create table t1 (time time, date date, timestamp timestamp, 10quarter int, week int, year int, timestampadd int, timestampdiff int); 11insert into t1 values ("12:22:22","97:02:03","1997-01-02",1,2,3,4,5); 12select * from t1; 13select t1.time+0,t1.date+0,t1.timestamp+0,concat(date," ",time), 14 t1.quarter+t1.week, t1.year+timestampadd, timestampdiff from t1; 15drop table t1; 16create table events(binlog int); 17insert into events values(1); 18select events.binlog from events; 19drop table events; 20 21# End of 4.1 tests 22 23# 24# Bug#19939 "AUTHORS is not a keyword" 25# 26delimiter |; 27create procedure p1() 28begin 29 declare n int default 2; 30 authors: while n > 0 do 31 set n = n -1; 32 end while authors; 33end| 34create procedure p2() 35begin 36 declare n int default 2; 37 contributors: while n > 0 do 38 set n = n -1; 39 end while contributors; 40end| 41delimiter ;| 42drop procedure p1; 43drop procedure p2; 44 45# End of 5.1 tests 46 47# 48# Bug#12204 - CONNECTION should not be a reserved word 49# 50 51create table t1 (connection int, b int); 52delimiter |; 53create procedure p1() 54begin 55 declare connection int; 56 select max(t1.connection) into connection from t1; 57 select concat("max=",connection) 'p1'; 58end| 59delimiter ;| 60insert into t1 (connection) values (1); 61call p1(); 62drop procedure p1; 63drop table t1; 64 65# End of 5.0 tests 66 67# 68# BUG#57899: Certain reserved words should not be reserved 69# 70 71# 72# We are looking for SYNTAX ERRORS here, so no need to 73# log the queries 74# 75 76CREATE TABLE slow (slow INT, general INT, master_heartbeat_period INT, ignore_server_ids INT); 77INSERT INTO slow(slow, general, master_heartbeat_period, ignore_server_ids) VALUES (1,2,3,4), (5,6,7,8); 78INSERT INTO slow(slow, general, master_heartbeat_period) VALUES (1,2,3), (5,6,7); 79INSERT INTO slow(slow, general) VALUES (1,2), (5,6); 80INSERT INTO slow(slow) VALUES (1), (5); 81SELECT slow, general, master_heartbeat_period, ignore_server_ids FROM slow ORDER BY slow; 82SELECT slow, general, master_heartbeat_period FROM slow ORDER BY slow; 83SELECT slow, master_heartbeat_period FROM slow ORDER BY slow; 84SELECT slow FROM slow ORDER BY slow; 85DROP TABLE slow; 86CREATE TABLE general (slow INT, general INT, master_heartbeat_period INT, ignore_server_ids INT); 87INSERT INTO general(slow, general, master_heartbeat_period, ignore_server_ids) VALUES (1,2,3,4), (5,6,7,8); 88INSERT INTO general(slow, general, master_heartbeat_period) VALUES (1,2,3), (5,6,7); 89INSERT INTO general(slow, general) VALUES (1,2), (5,6); 90INSERT INTO general(slow) VALUES (1), (5); 91SELECT slow, general, master_heartbeat_period, ignore_server_ids FROM general ORDER BY slow; 92SELECT slow, general, master_heartbeat_period FROM general ORDER BY slow; 93SELECT slow, master_heartbeat_period FROM general ORDER BY slow; 94SELECT slow FROM general ORDER BY slow; 95DROP TABLE general; 96CREATE TABLE master_heartbeat_period (slow INT, general INT, master_heartbeat_period INT, ignore_server_ids INT); 97INSERT INTO master_heartbeat_period(slow, general, master_heartbeat_period, ignore_server_ids) VALUES (1,2,3,4), (5,6,7,8); 98INSERT INTO master_heartbeat_period(slow, general, master_heartbeat_period) VALUES (1,2,3), (5,6,7); 99INSERT INTO master_heartbeat_period(slow, general) VALUES (1,2), (5,6); 100INSERT INTO master_heartbeat_period(slow) VALUES (1), (5); 101SELECT slow, general, master_heartbeat_period, ignore_server_ids FROM master_heartbeat_period ORDER BY slow; 102SELECT slow, general, master_heartbeat_period FROM master_heartbeat_period ORDER BY slow; 103SELECT slow, master_heartbeat_period FROM master_heartbeat_period ORDER BY slow; 104SELECT slow FROM master_heartbeat_period ORDER BY slow; 105DROP TABLE master_heartbeat_period; 106CREATE TABLE ignore_server_ids (slow INT, general INT, master_heartbeat_period INT, ignore_server_ids INT); 107INSERT INTO ignore_server_ids(slow, general, master_heartbeat_period, ignore_server_ids) VALUES (1,2,3,4), (5,6,7,8); 108INSERT INTO ignore_server_ids(slow, general, master_heartbeat_period) VALUES (1,2,3), (5,6,7); 109INSERT INTO ignore_server_ids(slow, general) VALUES (1,2), (5,6); 110INSERT INTO ignore_server_ids(slow) VALUES (1), (5); 111SELECT slow, general, master_heartbeat_period, ignore_server_ids FROM ignore_server_ids ORDER BY slow; 112SELECT slow, general, master_heartbeat_period FROM ignore_server_ids ORDER BY slow; 113SELECT slow, master_heartbeat_period FROM ignore_server_ids ORDER BY slow; 114SELECT slow FROM ignore_server_ids ORDER BY slow; 115DROP TABLE ignore_server_ids; 116 117CREATE TABLE t1 (slow INT, general INT, ignore_server_ids INT, master_heartbeat_period INT); 118INSERT INTO t1 VALUES (1,2,3,4); 119DELIMITER |; 120CREATE PROCEDURE p1() 121BEGIN 122 DECLARE slow INT; 123 DECLARE general INT; 124 DECLARE ignore_server_ids INT; 125 DECLARE master_heartbeat_period INT; 126 127 SELECT max(t1.slow) INTO slow FROM t1; 128 SELECT max(t1.general) INTO general FROM t1; 129 SELECT max(t1.ignore_server_ids) INTO ignore_server_ids FROM t1; 130 SELECT max(t1.master_heartbeat_period) INTO master_heartbeat_period FROM t1; 131 132 SELECT slow, general, ignore_server_ids, master_heartbeat_period; 133END| 134 135CREATE PROCEDURE p2() 136BEGIN 137 138 DECLARE n INT DEFAULT 2; 139 general: WHILE n > 0 DO 140 SET n = n -1; 141 END WHILE general; 142 143 SET n = 2; 144 slow: WHILE n > 0 DO 145 SET n = n -1; 146 END WHILE slow; 147 148 SET n = 2; 149 ignore_server_ids: WHILE n > 0 DO 150 SET n = n -1; 151 END WHILE ignore_server_ids; 152 153 SET n = 2; 154 master_heartbeat_period: WHILE n > 0 DO 155 SET n = n -1; 156 END WHILE master_heartbeat_period; 157 158END| 159DELIMITER ;| 160CALL p1(); 161call p2(); 162DROP PROCEDURE p1; 163DROP PROCEDURE p2; 164DROP TABLE t1; 165 166# 167# OPTION is not anymore a keyword 168# 169 170create table option (option int not null); 171drop table option; 172--error 1193 173set option=1; 174--error ER_PARSE_ERROR 175set option option=1; 176 177--echo # 178--echo # MDEV-9979 Keywords UNBOUNDED, PRECEDING, FOLLOWING, TIES, OTHERS should be non-reserved 179--echo # 180CREATE TABLE EXCLUDE (EXCLUDE INT); 181SELECT EXCLUDE FROM EXCLUDE; 182SELECT EXCLUDE EXCLUDE FROM EXCLUDE; 183SELECT EXCLUDE AS EXCLUDE FROM EXCLUDE; 184DROP TABLE EXCLUDE; 185 186CREATE TABLE UNBOUNDED (UNBOUNDED INT); 187SELECT UNBOUNDED FROM UNBOUNDED; 188SELECT UNBOUNDED UNBOUNDEX FROM UNBOUNDED; 189SELECT UNBOUNDED AS UNBOUNDEX FROM UNBOUNDED; 190DROP TABLE UNBOUNDED; 191 192CREATE TABLE PRECEDING (PRECEDING INT); 193SELECT PRECEDING FROM PRECEDING; 194SELECT PRECEDING PRECEDING FROM PRECEDING; 195SELECT PRECEDING AS PRECEDING FROM PRECEDING; 196DROP TABLE PRECEDING; 197 198CREATE TABLE FOLLOWING (FOLLOWING INT); 199SELECT FOLLOWING FROM FOLLOWING; 200SELECT FOLLOWING FOLLOWING FROM FOLLOWING; 201SELECT FOLLOWING AS FOLLOWING FROM FOLLOWING; 202DROP TABLE FOLLOWING; 203 204CREATE TABLE TIES (TIES INT); 205SELECT TIES FROM TIES; 206SELECT TIES TIES FROM TIES; 207SELECT TIES AS TIES FROM TIES; 208DROP TABLE TIES; 209 210CREATE TABLE OTHERS (OTHERS INT); 211SELECT OTHERS FROM OTHERS; 212SELECT OTHERS OTHERS FROM OTHERS; 213SELECT OTHERS AS OTHERS FROM OTHERS; 214DROP TABLE OTHERS; 215 216 217--echo # 218--echo # MDEV-10585 EXECUTE IMMEDIATE statement 219--echo # 220 221CREATE TABLE immediate (immediate int); 222DROP TABLE immediate; 223 224--echo # 225--echo # MDEV-10142 Pluggable parser 226--echo # Testing keywords that were added into lex.h for Oracle compatibility 227--echo # that are not reserved keywords in MariaDB 228--echo # 229 230CREATE TABLE clob (clob int); 231DROP TABLE clob; 232 233CREATE TABLE elsif (elsif INT); 234DROP TABLE elsif; 235 236CREATE TABLE exception (exception INT); 237DROP TABLE exception; 238 239CREATE TABLE raw (raw int); 240DROP TABLE raw; 241 242CREATE TABLE varchar2 (varchar2 int); 243DROP TABLE varchar2; 244 245CREATE TABLE decode (decode int); 246DROP TABLE decode; 247 248CREATE TABLE rowcount (rowcount int); 249DROP TABLE rowcount; 250 251CREATE TABLE isopen (isopen int); 252DROP TABLE isopen; 253 254CREATE TABLE notfound (notfound int); 255DROP TABLE notfound; 256 257CREATE TABLE raise (raise int); 258DROP TABLE raise; 259 260CREATE TABLE reuse (reuse int); 261DROP TABLE reuse; 262 263 264--echo # 265--echo # MDEV-17363 Compressed columns cannot be restored from dump 266--echo # COMPRESSED is not valid as an SP label any more 267--echo # but is still valid as an SP variable name. 268--echo # 269 270DELIMITER $$; 271--error ER_PARSE_ERROR 272BEGIN NOT ATOMIC 273compressed: 274 BEGIN 275 SELECT 1 AS a; 276 END; 277END 278$$ 279DELIMITER ;$$ 280 281DELIMITER $$; 282BEGIN NOT ATOMIC 283`compressed`: 284 BEGIN 285 SELECT 1 AS a; 286 END; 287END 288$$ 289DELIMITER ;$$ 290 291DELIMITER $$; 292BEGIN NOT ATOMIC 293 DECLARE compressed INT DEFAULT 1; 294 SELECT compressed; 295END 296$$ 297DELIMITER ;$$ 298