| 1 |
SET sql_mode=ORACLE; |
| 2 |
# |
| 3 |
# MDEV-15620 Crash when using "SET @@NEW.a=expr" inside a trigger |
| 4 |
# |
| 5 |
CREATE TABLE t1 (a INT); |
| 6 |
CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW SET @@NEW.a=0; |
| 7 |
ERROR HY000: Unknown structured system variable or ROW routine variable 'NEW' |
| 8 |
DROP TABLE t1; |
| 9 |
# |
| 10 |
# MDEV-15615 Unexpected syntax error instead of "Unknown system variable" inside an SP |
| 11 |
# |
| 12 |
DECLARE |
| 13 |
a INT; |
| 14 |
BEGIN |
| 15 |
SET GLOBAL a=10; |
| 16 |
END; |
| 17 |
$$ |
| 18 |
ERROR HY000: Unknown system variable 'a' |
| 19 |
# |
| 20 |
# MDEV-16202 Latest changes made erroneously some keywords reserved in sql_mode=ORACLE |
| 21 |
# |
| 22 |
CREATE PROCEDURE p1(name VARCHAR(64), pattern TEXT) AS |
| 23 |
query TEXT DEFAULT REPLACE(pattern, 'name', name); |
| 24 |
BEGIN |
| 25 |
SELECT query AS ''; |
| 26 |
EXECUTE IMMEDIATE query; |
| 27 |
EXCEPTION |
| 28 |
WHEN OTHERS THEN |
| 29 |
BEGIN |
| 30 |
SHOW ERRORS; |
| 31 |
END; |
| 32 |
END; |
| 33 |
$$ |
| 34 |
CREATE PROCEDURE p2(name VARCHAR(64)) AS |
| 35 |
BEGIN |
| 36 |
CALL p1(name, 'DECLARE name INT; BEGIN name:=10; SELECT name; END'); |
| 37 |
EXECUTE IMMEDIATE REPLACE('CREATE TABLE t1 (name INT)', 'name', name); |
| 38 |
CALL p1(name, 'SELECT name FROM t1'); |
| 39 |
CALL p1(name, 'SELECT name ''alias'' FROM t1'); |
| 40 |
CALL p1(name, 'SELECT name()'); |
| 41 |
CALL p1(name, 'SELECT name.name()'); |
| 42 |
CALL p1(name, 'SELECT name DATE FROM t1'); |
| 43 |
CALL p1(name, 'SELECT name HISTORY FROM t1'); |
| 44 |
CALL p1(name, 'SELECT name NEXT FROM t1'); |
| 45 |
CALL p1(name, 'SELECT name PERIOD FROM t1'); |
| 46 |
CALL p1(name, 'SELECT name PREVIOUS FROM t1'); |
| 47 |
CALL p1(name, 'SELECT name SYSTEM FROM t1'); |
| 48 |
CALL p1(name, 'SELECT name SYSTEM_TIME FROM t1'); |
| 49 |
CALL p1(name, 'SELECT name TIME FROM t1'); |
| 50 |
CALL p1(name, 'SELECT name TIMESTAMP FROM t1'); |
| 51 |
CALL p1(name, 'SELECT name TRANSACTION FROM t1'); |
| 52 |
CALL p1(name, 'SELECT name VALUE FROM t1'); |
| 53 |
CALL p1(name, 'SELECT name VERSIONING FROM t1'); |
| 54 |
CALL p1(name, 'SELECT name WITHOUT FROM t1'); |
| 55 |
DROP TABLE t1; |
| 56 |
END; |
| 57 |
$$ |
| 58 |
CALL p2('date'); |
| 59 |
DECLARE date INT; BEGIN date:=10; SELECT date; END |
| 60 |
Error 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'INT; BEGIN date:=10; SELECT date; END' at line 1 |
| 61 |
SELECT date FROM t1 |
| 62 |
SELECT date 'alias' FROM t1 |
| 63 |
Error 1525 Incorrect DATE value: 'alias' |
| 64 |
SELECT date() |
| 65 |
Error 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ')' at line 1 |
| 66 |
SELECT date.date() |
| 67 |
Error 1630 FUNCTION date.date does not exist. Check the 'Function Name Parsing and Resolution' section in the Reference Manual |
| 68 |
SELECT date DATE FROM t1 |
| 69 |
SELECT date HISTORY FROM t1 |
| 70 |
SELECT date NEXT FROM t1 |
| 71 |
SELECT date PERIOD FROM t1 |
| 72 |
SELECT date PREVIOUS FROM t1 |
| 73 |
SELECT date SYSTEM FROM t1 |
| 74 |
SELECT date SYSTEM_TIME FROM t1 |
| 75 |
SELECT date TIME FROM t1 |
| 76 |
SELECT date TIMESTAMP FROM t1 |
| 77 |
SELECT date TRANSACTION FROM t1 |
| 78 |
SELECT date VALUE FROM t1 |
| 79 |
SELECT date VERSIONING FROM t1 |
| 80 |
SELECT date WITHOUT FROM t1 |
| 81 |
CALL p2('history'); |
| 82 |
DECLARE history INT; BEGIN history:=10; SELECT history; END |
| 83 |
10 |
| 84 |
SELECT history FROM t1 |
| 85 |
SELECT history 'alias' FROM t1 |
| 86 |
SELECT history() |
| 87 |
Error 1630 FUNCTION test.history does not exist. Check the 'Function Name Parsing and Resolution' section in the Reference Manual |
| 88 |
SELECT history.history() |
| 89 |
Error 1630 FUNCTION history.history does not exist. Check the 'Function Name Parsing and Resolution' section in the Reference Manual |
| 90 |
SELECT history DATE FROM t1 |
| 91 |
SELECT history HISTORY FROM t1 |
| 92 |
SELECT history NEXT FROM t1 |
| 93 |
SELECT history PERIOD FROM t1 |
| 94 |
SELECT history PREVIOUS FROM t1 |
| 95 |
SELECT history SYSTEM FROM t1 |
| 96 |
SELECT history SYSTEM_TIME FROM t1 |
| 97 |
SELECT history TIME FROM t1 |
| 98 |
SELECT history TIMESTAMP FROM t1 |
| 99 |
SELECT history TRANSACTION FROM t1 |
| 100 |
SELECT history VALUE FROM t1 |
| 101 |
SELECT history VERSIONING FROM t1 |
| 102 |
SELECT history WITHOUT FROM t1 |
| 103 |
CALL p2('next'); |
| 104 |
DECLARE next INT; BEGIN next:=10; SELECT next; END |
| 105 |
10 |
| 106 |
SELECT next FROM t1 |
| 107 |
SELECT next 'alias' FROM t1 |
| 108 |
SELECT next() |
| 109 |
Error 1630 FUNCTION test.next does not exist. Check the 'Function Name Parsing and Resolution' section in the Reference Manual |
| 110 |
SELECT next.next() |
| 111 |
Error 1630 FUNCTION next.next does not exist. Check the 'Function Name Parsing and Resolution' section in the Reference Manual |
| 112 |
SELECT next DATE FROM t1 |
| 113 |
SELECT next HISTORY FROM t1 |
| 114 |
SELECT next NEXT FROM t1 |
| 115 |
SELECT next PERIOD FROM t1 |
| 116 |
SELECT next PREVIOUS FROM t1 |
| 117 |
SELECT next SYSTEM FROM t1 |
| 118 |
SELECT next SYSTEM_TIME FROM t1 |
| 119 |
SELECT next TIME FROM t1 |
| 120 |
SELECT next TIMESTAMP FROM t1 |
| 121 |
SELECT next TRANSACTION FROM t1 |
| 122 |
SELECT next VALUE FROM t1 |
| 123 |
Error 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'FROM t1' at line 1 |
| 124 |
SELECT next VERSIONING FROM t1 |
| 125 |
SELECT next WITHOUT FROM t1 |
| 126 |
CALL p2('period'); |
| 127 |
DECLARE period INT; BEGIN period:=10; SELECT period; END |
| 128 |
10 |
| 129 |
SELECT period FROM t1 |
| 130 |
SELECT period 'alias' FROM t1 |
| 131 |
SELECT period() |
| 132 |
Error 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '()' at line 1 |
| 133 |
SELECT period.period() |
| 134 |
Error 1630 FUNCTION period.period does not exist. Check the 'Function Name Parsing and Resolution' section in the Reference Manual |
| 135 |
SELECT period DATE FROM t1 |
| 136 |
SELECT period HISTORY FROM t1 |
| 137 |
SELECT period NEXT FROM t1 |
| 138 |
SELECT period PERIOD FROM t1 |
| 139 |
SELECT period PREVIOUS FROM t1 |
| 140 |
SELECT period SYSTEM FROM t1 |
| 141 |
SELECT period SYSTEM_TIME FROM t1 |
| 142 |
SELECT period TIME FROM t1 |
| 143 |
SELECT period TIMESTAMP FROM t1 |
| 144 |
SELECT period TRANSACTION FROM t1 |
| 145 |
SELECT period VALUE FROM t1 |
| 146 |
SELECT period VERSIONING FROM t1 |
| 147 |
SELECT period WITHOUT FROM t1 |
| 148 |
CALL p2('previous'); |
| 149 |
DECLARE previous INT; BEGIN previous:=10; SELECT previous; END |
| 150 |
10 |
| 151 |
SELECT previous FROM t1 |
| 152 |
SELECT previous 'alias' FROM t1 |
| 153 |
SELECT previous() |
| 154 |
Error 1630 FUNCTION test.previous does not exist. Check the 'Function Name Parsing and Resolution' section in the Reference Manual |
| 155 |
SELECT previous.previous() |
| 156 |
Error 1630 FUNCTION previous.previous does not exist. Check the 'Function Name Parsing and Resolution' section in the Reference Manual |
| 157 |
SELECT previous DATE FROM t1 |
| 158 |
SELECT previous HISTORY FROM t1 |
| 159 |
SELECT previous NEXT FROM t1 |
| 160 |
SELECT previous PERIOD FROM t1 |
| 161 |
SELECT previous PREVIOUS FROM t1 |
| 162 |
SELECT previous SYSTEM FROM t1 |
| 163 |
SELECT previous SYSTEM_TIME FROM t1 |
| 164 |
SELECT previous TIME FROM t1 |
| 165 |
SELECT previous TIMESTAMP FROM t1 |
| 166 |
SELECT previous TRANSACTION FROM t1 |
| 167 |
SELECT previous VALUE FROM t1 |
| 168 |
Error 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'FROM t1' at line 1 |
| 169 |
SELECT previous VERSIONING FROM t1 |
| 170 |
SELECT previous WITHOUT FROM t1 |
| 171 |
CALL p2('system'); |
| 172 |
DECLARE system INT; BEGIN system:=10; SELECT system; END |
| 173 |
10 |
| 174 |
SELECT system FROM t1 |
| 175 |
SELECT system 'alias' FROM t1 |
| 176 |
SELECT system() |
| 177 |
Error 1630 FUNCTION test.system does not exist. Check the 'Function Name Parsing and Resolution' section in the Reference Manual |
| 178 |
SELECT system.system() |
| 179 |
Error 1630 FUNCTION system.system does not exist. Check the 'Function Name Parsing and Resolution' section in the Reference Manual |
| 180 |
SELECT system DATE FROM t1 |
| 181 |
SELECT system HISTORY FROM t1 |
| 182 |
SELECT system NEXT FROM t1 |
| 183 |
SELECT system PERIOD FROM t1 |
| 184 |
SELECT system PREVIOUS FROM t1 |
| 185 |
SELECT system SYSTEM FROM t1 |
| 186 |
SELECT system SYSTEM_TIME FROM t1 |
| 187 |
SELECT system TIME FROM t1 |
| 188 |
SELECT system TIMESTAMP FROM t1 |
| 189 |
SELECT system TRANSACTION FROM t1 |
| 190 |
SELECT system VALUE FROM t1 |
| 191 |
SELECT system VERSIONING FROM t1 |
| 192 |
SELECT system WITHOUT FROM t1 |
| 193 |
CALL p2('system_time'); |
| 194 |
DECLARE system_time INT; BEGIN system_time:=10; SELECT system_time; END |
| 195 |
10 |
| 196 |
SELECT system_time FROM t1 |
| 197 |
SELECT system_time 'alias' FROM t1 |
| 198 |
SELECT system_time() |
| 199 |
Error 1630 FUNCTION test.system_time does not exist. Check the 'Function Name Parsing and Resolution' section in the Reference Manual |
| 200 |
SELECT system_time.system_time() |
| 201 |
Error 1630 FUNCTION system_time.system_time does not exist. Check the 'Function Name Parsing and Resolution' section in the Reference Manual |
| 202 |
SELECT system_time DATE FROM t1 |
| 203 |
SELECT system_time HISTORY FROM t1 |
| 204 |
SELECT system_time NEXT FROM t1 |
| 205 |
SELECT system_time PERIOD FROM t1 |
| 206 |
SELECT system_time PREVIOUS FROM t1 |
| 207 |
SELECT system_time SYSTEM FROM t1 |
| 208 |
SELECT system_time SYSTEM_TIME FROM t1 |
| 209 |
SELECT system_time TIME FROM t1 |
| 210 |
SELECT system_time TIMESTAMP FROM t1 |
| 211 |
SELECT system_time TRANSACTION FROM t1 |
| 212 |
SELECT system_time VALUE FROM t1 |
| 213 |
SELECT system_time VERSIONING FROM t1 |
| 214 |
SELECT system_time WITHOUT FROM t1 |
| 215 |
CALL p2('time'); |
| 216 |
DECLARE time INT; BEGIN time:=10; SELECT time; END |
| 217 |
Error 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'INT; BEGIN time:=10; SELECT time; END' at line 1 |
| 218 |
SELECT time FROM t1 |
| 219 |
SELECT time 'alias' FROM t1 |
| 220 |
Error 1525 Incorrect TIME value: 'alias' |
| 221 |
SELECT time() |
| 222 |
Error 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ')' at line 1 |
| 223 |
SELECT time.time() |
| 224 |
Error 1630 FUNCTION time.time does not exist. Check the 'Function Name Parsing and Resolution' section in the Reference Manual |
| 225 |
SELECT time DATE FROM t1 |
| 226 |
SELECT time HISTORY FROM t1 |
| 227 |
SELECT time NEXT FROM t1 |
| 228 |
SELECT time PERIOD FROM t1 |
| 229 |
SELECT time PREVIOUS FROM t1 |
| 230 |
SELECT time SYSTEM FROM t1 |
| 231 |
SELECT time SYSTEM_TIME FROM t1 |
| 232 |
SELECT time TIME FROM t1 |
| 233 |
SELECT time TIMESTAMP FROM t1 |
| 234 |
SELECT time TRANSACTION FROM t1 |
| 235 |
SELECT time VALUE FROM t1 |
| 236 |
SELECT time VERSIONING FROM t1 |
| 237 |
SELECT time WITHOUT FROM t1 |
| 238 |
CALL p2('timestamp'); |
| 239 |
DECLARE timestamp INT; BEGIN timestamp:=10; SELECT timestamp; END |
| 240 |
Error 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'INT; BEGIN timestamp:=10; SELECT timestamp; END' at line 1 |
| 241 |
SELECT timestamp FROM t1 |
| 242 |
SELECT timestamp 'alias' FROM t1 |
| 243 |
Error 1525 Incorrect DATETIME value: 'alias' |
| 244 |
SELECT timestamp() |
| 245 |
Error 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ')' at line 1 |
| 246 |
SELECT timestamp.timestamp() |
| 247 |
Error 1630 FUNCTION timestamp.timestamp does not exist. Check the 'Function Name Parsing and Resolution' section in the Reference Manual |
| 248 |
SELECT timestamp DATE FROM t1 |
| 249 |
SELECT timestamp HISTORY FROM t1 |
| 250 |
SELECT timestamp NEXT FROM t1 |
| 251 |
SELECT timestamp PERIOD FROM t1 |
| 252 |
SELECT timestamp PREVIOUS FROM t1 |
| 253 |
SELECT timestamp SYSTEM FROM t1 |
| 254 |
SELECT timestamp SYSTEM_TIME FROM t1 |
| 255 |
SELECT timestamp TIME FROM t1 |
| 256 |
SELECT timestamp TIMESTAMP FROM t1 |
| 257 |
SELECT timestamp TRANSACTION FROM t1 |
| 258 |
SELECT timestamp VALUE FROM t1 |
| 259 |
SELECT timestamp VERSIONING FROM t1 |
| 260 |
SELECT timestamp WITHOUT FROM t1 |
| 261 |
CALL p2('transaction'); |
| 262 |
DECLARE transaction INT; BEGIN transaction:=10; SELECT transaction; END |
| 263 |
10 |
| 264 |
SELECT transaction FROM t1 |
| 265 |
SELECT transaction 'alias' FROM t1 |
| 266 |
SELECT transaction() |
| 267 |
Error 1630 FUNCTION test.transaction does not exist. Check the 'Function Name Parsing and Resolution' section in the Reference Manual |
| 268 |
SELECT transaction.transaction() |
| 269 |
Error 1630 FUNCTION transaction.transaction does not exist. Check the 'Function Name Parsing and Resolution' section in the Reference Manual |
| 270 |
SELECT transaction DATE FROM t1 |
| 271 |
SELECT transaction HISTORY FROM t1 |
| 272 |
SELECT transaction NEXT FROM t1 |
| 273 |
SELECT transaction PERIOD FROM t1 |
| 274 |
SELECT transaction PREVIOUS FROM t1 |
| 275 |
SELECT transaction SYSTEM FROM t1 |
| 276 |
SELECT transaction SYSTEM_TIME FROM t1 |
| 277 |
SELECT transaction TIME FROM t1 |
| 278 |
SELECT transaction TIMESTAMP FROM t1 |
| 279 |
SELECT transaction TRANSACTION FROM t1 |
| 280 |
SELECT transaction VALUE FROM t1 |
| 281 |
SELECT transaction VERSIONING FROM t1 |
| 282 |
SELECT transaction WITHOUT FROM t1 |
| 283 |
CALL p2('value'); |
| 284 |
DECLARE value INT; BEGIN value:=10; SELECT value; END |
| 285 |
10 |
| 286 |
SELECT value FROM t1 |
| 287 |
SELECT value 'alias' FROM t1 |
| 288 |
SELECT value() |
| 289 |
Error 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ')' at line 1 |
| 290 |
SELECT value.value() |
| 291 |
Error 1630 FUNCTION value.value does not exist. Check the 'Function Name Parsing and Resolution' section in the Reference Manual |
| 292 |
SELECT value DATE FROM t1 |
| 293 |
SELECT value HISTORY FROM t1 |
| 294 |
SELECT value NEXT FROM t1 |
| 295 |
SELECT value PERIOD FROM t1 |
| 296 |
SELECT value PREVIOUS FROM t1 |
| 297 |
SELECT value SYSTEM FROM t1 |
| 298 |
SELECT value SYSTEM_TIME FROM t1 |
| 299 |
SELECT value TIME FROM t1 |
| 300 |
SELECT value TIMESTAMP FROM t1 |
| 301 |
SELECT value TRANSACTION FROM t1 |
| 302 |
SELECT value VALUE FROM t1 |
| 303 |
SELECT value VERSIONING FROM t1 |
| 304 |
SELECT value WITHOUT FROM t1 |
| 305 |
CALL p2('versioning'); |
| 306 |
DECLARE versioning INT; BEGIN versioning:=10; SELECT versioning; END |
| 307 |
10 |
| 308 |
SELECT versioning FROM t1 |
| 309 |
SELECT versioning 'alias' FROM t1 |
| 310 |
SELECT versioning() |
| 311 |
Error 1630 FUNCTION test.versioning does not exist. Check the 'Function Name Parsing and Resolution' section in the Reference Manual |
| 312 |
SELECT versioning.versioning() |
| 313 |
Error 1630 FUNCTION versioning.versioning does not exist. Check the 'Function Name Parsing and Resolution' section in the Reference Manual |
| 314 |
SELECT versioning DATE FROM t1 |
| 315 |
SELECT versioning HISTORY FROM t1 |
| 316 |
SELECT versioning NEXT FROM t1 |
| 317 |
SELECT versioning PERIOD FROM t1 |
| 318 |
SELECT versioning PREVIOUS FROM t1 |
| 319 |
SELECT versioning SYSTEM FROM t1 |
| 320 |
SELECT versioning SYSTEM_TIME FROM t1 |
| 321 |
SELECT versioning TIME FROM t1 |
| 322 |
SELECT versioning TIMESTAMP FROM t1 |
| 323 |
SELECT versioning TRANSACTION FROM t1 |
| 324 |
SELECT versioning VALUE FROM t1 |
| 325 |
SELECT versioning VERSIONING FROM t1 |
| 326 |
SELECT versioning WITHOUT FROM t1 |
| 327 |
CALL p2('without'); |
| 328 |
DECLARE without INT; BEGIN without:=10; SELECT without; END |
| 329 |
10 |
| 330 |
SELECT without FROM t1 |
| 331 |
SELECT without 'alias' FROM t1 |
| 332 |
SELECT without() |
| 333 |
Error 1630 FUNCTION test.without does not exist. Check the 'Function Name Parsing and Resolution' section in the Reference Manual |
| 334 |
SELECT without.without() |
| 335 |
Error 1630 FUNCTION without.without does not exist. Check the 'Function Name Parsing and Resolution' section in the Reference Manual |
| 336 |
SELECT without DATE FROM t1 |
| 337 |
SELECT without HISTORY FROM t1 |
| 338 |
SELECT without NEXT FROM t1 |
| 339 |
SELECT without PERIOD FROM t1 |
| 340 |
SELECT without PREVIOUS FROM t1 |
| 341 |
SELECT without SYSTEM FROM t1 |
| 342 |
SELECT without SYSTEM_TIME FROM t1 |
| 343 |
SELECT without TIME FROM t1 |
| 344 |
SELECT without TIMESTAMP FROM t1 |
| 345 |
SELECT without TRANSACTION FROM t1 |
| 346 |
SELECT without VALUE FROM t1 |
| 347 |
SELECT without VERSIONING FROM t1 |
| 348 |
SELECT without WITHOUT FROM t1 |
| 349 |
DROP PROCEDURE p2; |
| 350 |
DROP PROCEDURE p1; |
| 351 |
# |
| 352 |
# MDEV-16244 sql_mode=ORACLE: Some keywords do not work in variable declarations |
| 353 |
# |
| 354 |
SET sql_mode=ORACLE; |
| 355 |
DECLARE |
| 356 |
do INT; |
| 357 |
BEGIN |
| 358 |
SELECT do INTO do FROM DUAL; |
| 359 |
END; |
| 360 |
/ |
| 361 |
DECLARE |
| 362 |
handler INT; |
| 363 |
BEGIN |
| 364 |
SELECT handler INTO handler FROM DUAL; |
| 365 |
END; |
| 366 |
/ |
| 367 |
DECLARE |
| 368 |
repair INT; |
| 369 |
BEGIN |
| 370 |
SELECT repair INTO repair FROM DUAL; |
| 371 |
END; |
| 372 |
/ |
| 373 |
DECLARE |
| 374 |
shutdown INT; |
| 375 |
BEGIN |
| 376 |
SELECT shutdown INTO shutdown FROM DUAL; |
| 377 |
END; |
| 378 |
/ |
| 379 |
DECLARE |
| 380 |
truncate INT; |
| 381 |
BEGIN |
| 382 |
SELECT truncate INTO truncate FROM DUAL; |
| 383 |
END; |
| 384 |
/ |
| 385 |
DECLARE |
| 386 |
close INT; |
| 387 |
BEGIN |
| 388 |
SELECT close INTO close FROM DUAL; |
| 389 |
END; |
| 390 |
/ |
| 391 |
DECLARE |
| 392 |
commit INT; |
| 393 |
BEGIN |
| 394 |
SELECT commit INTO commit FROM DUAL; |
| 395 |
END; |
| 396 |
/ |
| 397 |
DECLARE |
| 398 |
open INT; |
| 399 |
BEGIN |
| 400 |
SELECT open INTO open FROM DUAL; |
| 401 |
END; |
| 402 |
/ |
| 403 |
DECLARE |
| 404 |
rollback INT; |
| 405 |
BEGIN |
| 406 |
SELECT rollback INTO rollback FROM DUAL; |
| 407 |
END; |
| 408 |
/ |
| 409 |
DECLARE |
| 410 |
savepoint INT; |
| 411 |
BEGIN |
| 412 |
SELECT savepoint INTO savepoint FROM DUAL; |
| 413 |
END; |
| 414 |
/ |
| 415 |
DECLARE |
| 416 |
contains INT; |
| 417 |
BEGIN |
| 418 |
SELECT contains INTO contains FROM DUAL; |
| 419 |
END; |
| 420 |
/ |
| 421 |
DECLARE |
| 422 |
language INT; |
| 423 |
BEGIN |
| 424 |
SELECT language INTO language FROM DUAL; |
| 425 |
END; |
| 426 |
/ |
| 427 |
DECLARE |
| 428 |
no INT; |
| 429 |
BEGIN |
| 430 |
SELECT no INTO no FROM DUAL; |
| 431 |
END; |
| 432 |
/ |
| 433 |
DECLARE |
| 434 |
charset INT; |
| 435 |
BEGIN |
| 436 |
SELECT charset INTO charset FROM DUAL; |
| 437 |
END; |
| 438 |
/ |
| 439 |
DECLARE |
| 440 |
follows INT; |
| 441 |
BEGIN |
| 442 |
SELECT follows INTO follows FROM DUAL; |
| 443 |
END; |
| 444 |
/ |
| 445 |
DECLARE |
| 446 |
precedes INT; |
| 447 |
BEGIN |
| 448 |
SELECT precedes INTO precedes FROM DUAL; |
| 449 |
END; |
| 450 |
/ |
| 451 |
# |
| 452 |
# MDEV-16464 Oracle Comp.: Sql-Error on "SELECT name, comment FROM mysql.proc" |
| 453 |
# |
| 454 |
SET sql_mode=ORACLE; |
| 455 |
SELECT name, comment FROM mysql.proc WHERE db='test'; |
| 456 |
name comment |
| 457 |
CREATE TABLE comment (comment INT); |
| 458 |
SELECT comment FROM comment; |
| 459 |
comment |
| 460 |
SELECT comment comment FROM comment comment; |
| 461 |
comment |
| 462 |
SELECT comment AS comment FROM comment AS comment; |
| 463 |
comment |
| 464 |
DROP TABLE comment; |
| 465 |
DECLARE |
| 466 |
comment INT; |
| 467 |
BEGIN |
| 468 |
SELECT comment INTO comment FROM DUAL; |
| 469 |
END; |
| 470 |
/ |
| 471 |
CREATE PROCEDURE comment COMMENT 'test' AS |
| 472 |
BEGIN |
| 473 |
SELECT 1; |
| 474 |
END; |
| 475 |
/ |
| 476 |
BEGIN |
| 477 |
comment; |
| 478 |
END; |
| 479 |
/ |
| 480 |
1 |
| 481 |
1 |
| 482 |
CALL comment(); |
| 483 |
1 |
| 484 |
1 |
| 485 |
CALL comment; |
| 486 |
1 |
| 487 |
1 |
| 488 |
DROP PROCEDURE comment; |
| 489 |
CREATE FUNCTION comment RETURN INT COMMENT 'test' AS |
| 490 |
BEGIN |
| 491 |
RETURN 1; |
| 492 |
END; |
| 493 |
/ |
| 494 |
Warnings: |
| 495 |
Note 1585 This function 'comment' has the same name as a native function |
| 496 |
SELECT test.comment() FROM DUAL; |
| 497 |
test.comment() |
| 498 |
1 |
| 499 |
Warnings: |
| 500 |
Note 1585 This function 'comment' has the same name as a native function |
| 501 |
DROP FUNCTION comment; |
| 502 |
# |
| 503 |
# MDEV-17660 sql_mode=ORACLE: Some keywords do not work as label names: history, system, versioning, without |
| 504 |
# |
| 505 |
BEGIN |
| 506 |
<<date_format>> |
| 507 |
NULL; |
| 508 |
END; |
| 509 |
/ |
| 510 |
BEGIN |
| 511 |
<<decode>> |
| 512 |
NULL; |
| 513 |
END; |
| 514 |
/ |
| 515 |
BEGIN |
| 516 |
<<history>> |
| 517 |
NULL; |
| 518 |
END; |
| 519 |
/ |
| 520 |
BEGIN |
| 521 |
<<system>> |
| 522 |
NULL; |
| 523 |
END; |
| 524 |
/ |
| 525 |
BEGIN |
| 526 |
<<versioning>> |
| 527 |
NULL; |
| 528 |
END; |
| 529 |
/ |
| 530 |
BEGIN |
| 531 |
<<without>> |
| 532 |
NULL; |
| 533 |
END; |
| 534 |
/ |
| 535 |
# |
| 536 |
# MDEV-17666 sql_mode=ORACLE: Keyword ELSEIF should not be reserved |
| 537 |
# |
| 538 |
DECLARE |
| 539 |
ELSEIF INT; |
| 540 |
BEGIN |
| 541 |
ELSEIF:=1; |
| 542 |
END; |
| 543 |
/ |
| 544 |
BEGIN |
| 545 |
<<ELSEIF>> |
| 546 |
NULL; |
| 547 |
END; |
| 548 |
/ |
| 549 |
# |
| 550 |
# MDEV-17693 Shift/reduce conflicts for NAMES,ROLE,PASSWORD in the option_value_no_option_type grammar |
| 551 |
# |
| 552 |
CREATE TABLE names (names INT); |
| 553 |
SELECT names FROM names AS names; |
| 554 |
names |
| 555 |
DROP TABLE names; |
| 556 |
CREATE TABLE password (password INT); |
| 557 |
SELECT password FROM password AS password; |
| 558 |
password |
| 559 |
DROP TABLE password; |
| 560 |
CREATE TABLE role (role INT); |
| 561 |
SELECT role FROM role AS role; |
| 562 |
role |
| 563 |
DROP TABLE role; |
| 564 |
DECLARE |
| 565 |
names VARCHAR(32) DEFAULT '[names]'; |
| 566 |
password VARCHAR(32) DEFAULT '[password]'; |
| 567 |
role VARCHAR(32) DEFAULT '[role]'; |
| 568 |
BEGIN |
| 569 |
<<names>> |
| 570 |
SELECT names; |
| 571 |
<<password>> |
| 572 |
SELECT password; |
| 573 |
<<role>> |
| 574 |
SELECT role; |
| 575 |
END; |
| 576 |
$$ |
| 577 |
names |
| 578 |
[names] |
| 579 |
password |
| 580 |
[password] |
| 581 |
role |
| 582 |
[role] |
| 583 |
DECLARE |
| 584 |
names VARCHAR(32); |
| 585 |
BEGIN |
| 586 |
SET names='[names]'; |
| 587 |
END; |
| 588 |
$$ |
| 589 |
ERROR 42000: Variable 'names' must be quoted with `...`, or renamed |
| 590 |
DECLARE |
| 591 |
password VARCHAR(32); |
| 592 |
BEGIN |
| 593 |
SET password='[password]'; |
| 594 |
END; |
| 595 |
$$ |
| 596 |
ERROR 42000: Variable 'password' must be quoted with `...`, or renamed |
| 597 |
DECLARE |
| 598 |
role VARCHAR(32); |
| 599 |
BEGIN |
| 600 |
SET role='[role]'; |
| 601 |
END; |
| 602 |
$$ |
| 603 |
SELECT @@GLOBAL.names; |
| 604 |
ERROR HY000: Unknown system variable 'names' |
| 605 |
SELECT @@GLOBAL.password; |
| 606 |
ERROR HY000: Unknown system variable 'password' |
| 607 |
SELECT @@GLOBAL.role; |
| 608 |
ERROR HY000: Unknown system variable 'role' |
| 609 |
# |
| 610 |
# MDEV-22822 sql_mode="oracle" cannot declare without variable errors |
| 611 |
# |
| 612 |
# It's OK to have no declarations between DECLARE and BEGIN. |
| 613 |
# |
| 614 |
BEGIN |
| 615 |
DECLARE |
| 616 |
BEGIN |
| 617 |
NULL; |
| 618 |
END; |
| 619 |
EXCEPTION |
| 620 |
WHEN OTHERS THEN |
| 621 |
NULL; |
| 622 |
END; |
| 623 |
// |
| 624 |
DECLARE |
| 625 |
BEGIN |
| 626 |
NULL; |
| 627 |
EXCEPTION |
| 628 |
WHEN OTHERS THEN |
| 629 |
NULL; |
| 630 |
END; |
| 631 |
// |
| 632 |
BEGIN |
| 633 |
<<lab>> |
| 634 |
DECLARE |
| 635 |
BEGIN |
| 636 |
NULL; |
| 637 |
END; |
| 638 |
EXCEPTION |
| 639 |
WHEN OTHERS THEN |
| 640 |
NULL; |
| 641 |
END; |
| 642 |
// |
| 643 |
# |
| 644 |
# End of 10.3 tests |
| 645 |
# |
| 646 |
# |
| 647 |
# MDEV-21998: Server crashes in st_select_lex::add_table_to_list |
| 648 |
# upon mix of KILL and sequences |
| 649 |
# |
| 650 |
KILL ( SELECT 1 ) + LASTVAL(s); |
| 651 |
ERROR 42000: KILL does not support subqueries or stored functions |
| 652 |
KILL LASTVAL(s); |
| 653 |
ERROR 42000: KILL does not support subqueries or stored functions |
| 654 |
# |
| 655 |
# MDEV-23094: Multiple calls to a Stored Procedure from another |
| 656 |
# Stored Procedure crashes server |
| 657 |
# |
| 658 |
create table t1 (id1 int primary key, data1 int); |
| 659 |
create table t2 (id2 int primary key, data2 int); |
| 660 |
create procedure p1(id int,dt int) as |
| 661 |
begin |
| 662 |
if (exists(select * from t1 where id1 = id and data1 = dt) or |
| 663 |
not exists (select * from t2 where id2 = id and data2 = dt)) |
| 664 |
then |
| 665 |
select 1; |
| 666 |
end if; |
| 667 |
end // |
| 668 |
call p1(1,2); |
| 669 |
1 |
| 670 |
1 |
| 671 |
call p1(1,2); |
| 672 |
1 |
| 673 |
1 |
| 674 |
drop procedure p1; |
| 675 |
create procedure p1(id int, dt int) as |
| 676 |
begin |
| 677 |
case (exists(select * from t1 where id1 = id and data1 = dt) or |
| 678 |
not exists (select * from t2 where id2 = id and data2 = dt)) |
| 679 |
when 1 then |
| 680 |
select 1; |
| 681 |
else |
| 682 |
select 0; |
| 683 |
end case; |
| 684 |
end // |
| 685 |
call p1(1,2); |
| 686 |
1 |
| 687 |
1 |
| 688 |
call p1(1,2); |
| 689 |
1 |
| 690 |
1 |
| 691 |
drop procedure p1; |
| 692 |
create procedure p1(id int, dt int) as |
| 693 |
begin |
| 694 |
declare wcont int default 1; |
| 695 |
begin |
| 696 |
while (exists(select * from t1 where id1 = id and data1 = dt) or |
| 697 |
not exists (select * from t2 where id2 = id and data2 = dt)) and wcont |
| 698 |
loop |
| 699 |
select 1; |
| 700 |
set wcont=0; |
| 701 |
end loop; |
| 702 |
end; |
| 703 |
end // |
| 704 |
call p1(1,2); |
| 705 |
1 |
| 706 |
1 |
| 707 |
call p1(1,2); |
| 708 |
1 |
| 709 |
1 |
| 710 |
drop procedure p1; |
| 711 |
create procedure p1(id int, dt int) as |
| 712 |
begin |
| 713 |
declare count int default 1; |
| 714 |
begin |
| 715 |
repeat |
| 716 |
select 1; |
| 717 |
set count=count+1; |
| 718 |
until (exists(select * from t1 where id1 = id and data1 = dt) or |
| 719 |
not exists (select * from t2 where id2 = id and data2 = dt)) and |
| 720 |
count < 3 |
| 721 |
end repeat; |
| 722 |
end; |
| 723 |
end // |
| 724 |
call p1(1,2); |
| 725 |
1 |
| 726 |
1 |
| 727 |
call p1(1,2); |
| 728 |
1 |
| 729 |
1 |
| 730 |
drop procedure p1; |
| 731 |
create procedure p1(id int, dt int) as |
| 732 |
begin |
| 733 |
for i in 1..(exists(select * from t1 where id1 = id and data1 = dt) or |
| 734 |
not exists (select * from t2 where id2 = id and data2 = dt)) |
| 735 |
loop |
| 736 |
select 1; |
| 737 |
end loop; |
| 738 |
end // |
| 739 |
call p1(1,2); |
| 740 |
1 |
| 741 |
1 |
| 742 |
call p1(1,2); |
| 743 |
1 |
| 744 |
1 |
| 745 |
drop procedure p1; |
| 746 |
set sql_mode=ORACLE; |
| 747 |
create or replace procedure p1(id int, dt int) as |
| 748 |
begin |
| 749 |
while (1) |
| 750 |
loop |
| 751 |
exit when (exists(select * from t1 where id1 = id and data1 = dt) or |
| 752 |
not exists (select * from t2 where id2 = id and data2 = dt)); |
| 753 |
end loop; |
| 754 |
end; |
| 755 |
// |
| 756 |
call p1(1,2); |
| 757 |
call p1(1,2); |
| 758 |
drop procedure p1; |
| 759 |
drop table t1,t2; |
| 760 |
# End of 10.4 tests |
| 761 |
# |
| 762 |
# Start of 10.5 tests |
| 763 |
# |
| 764 |
# |
| 765 |
# MDEV-20734 Allow reserved keywords as user defined type names |
| 766 |
# |
| 767 |
CREATE TABLE t1 (a DUAL); |
| 768 |
ERROR HY000: Unknown data type: 'DUAL' |
| 769 |
SELECT CAST(1 AS DUAL); |
| 770 |
ERROR HY000: Unknown data type: 'DUAL' |
| 771 |
# |
| 772 |
# MDEV-20735 Allow non-reserved keywords as user defined type names |
| 773 |
# |
| 774 |
CREATE TABLE t1 (a ASCII); |
| 775 |
ERROR HY000: Unknown data type: 'ASCII' |
| 776 |
SELECT CAST(1 AS ASCII); |
| 777 |
ERROR HY000: Unknown data type: 'ASCII' |
| 778 |
CREATE TABLE t1 (a LANGUAGE); |
| 779 |
ERROR HY000: Unknown data type: 'LANGUAGE' |
| 780 |
SELECT CAST(1 AS LANGUAGE); |
| 781 |
ERROR HY000: Unknown data type: 'LANGUAGE' |
| 782 |
CREATE TABLE t1 (a CLOSE); |
| 783 |
ERROR HY000: Unknown data type: 'CLOSE' |
| 784 |
SELECT CAST(1 AS CLOSE); |
| 785 |
ERROR HY000: Unknown data type: 'CLOSE' |
| 786 |
CREATE TABLE t1 (a NAMES); |
| 787 |
ERROR HY000: Unknown data type: 'NAMES' |
| 788 |
SELECT CAST(1 AS NAMES); |
| 789 |
ERROR HY000: Unknown data type: 'NAMES' |
| 790 |
CREATE TABLE t1 (a END); |
| 791 |
ERROR HY000: Unknown data type: 'END' |
| 792 |
SELECT CAST(1 AS END); |
| 793 |
ERROR HY000: Unknown data type: 'END' |
| 794 |
CREATE TABLE t1 (a GLOBAL); |
| 795 |
ERROR HY000: Unknown data type: 'GLOBAL' |
| 796 |
SELECT CAST(1 AS GLOBAL); |
| 797 |
ERROR HY000: Unknown data type: 'GLOBAL' |
| 798 |
CREATE TABLE t1 (a ACTION); |
| 799 |
ERROR HY000: Unknown data type: 'ACTION' |
| 800 |
SELECT CAST(1 AS ACTION); |
| 801 |
ERROR HY000: Unknown data type: 'ACTION' |
| 802 |
CREATE TABLE t1 (a BEGIN); |
| 803 |
ERROR HY000: Unknown data type: 'BEGIN' |
| 804 |
SELECT CAST(1 AS BEGIN); |
| 805 |
ERROR HY000: Unknown data type: 'BEGIN' |
| 806 |
# |
| 807 |
# End of 10.5 tests |
| 808 |
# |
| 809 |
# |
| 810 |
# Start of 10.6 tests |
| 811 |
# |
| 812 |
# |
| 813 |
# MDEV-19682 sql_mode="oracle" does not support sysdate |
| 814 |
# |
| 815 |
SELECT sysdate LIKE '____-__-__ __:__:__'; |
| 816 |
sysdate LIKE '____-__-__ __:__:__' |
| 817 |
1 |
| 818 |
SELECT sysdate = sysdate(); |
| 819 |
sysdate = sysdate() |
| 820 |
1 |
| 821 |
SELECT sysdate = sysdate(0); |
| 822 |
sysdate = sysdate(0) |
| 823 |
1 |
| 824 |
CREATE DATABASE sysdate; |
| 825 |
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'sysdate' at line 1 |
| 826 |
CREATE TABLE sysdate (a INT); |
| 827 |
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'sysdate (a INT)' at line 1 |
| 828 |
CREATE TABLE t1 (sysdate INT); |
| 829 |
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'sysdate INT)' at line 1 |
| 830 |
CREATE TABLE t1 (a sysdate); |
| 831 |
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'sysdate)' at line 1 |
| 832 |
CREATE FUNCTION sysdate RETURN INT AS |
| 833 |
BEGIN |
| 834 |
RETURN 1; |
| 835 |
END; |
| 836 |
$$ |
| 837 |
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'sysdate RETURN INT AS |
| 838 |
BEGIN |
| 839 |
RETURN 1; |
| 840 |
END' at line 1 |
| 841 |
CREATE FUNCTION sysdate() RETURN INT AS |
| 842 |
BEGIN |
| 843 |
RETURN 1; |
| 844 |
END; |
| 845 |
$$ |
| 846 |
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'sysdate() RETURN INT AS |
| 847 |
BEGIN |
| 848 |
RETURN 1; |
| 849 |
END' at line 1 |
| 850 |
DECLARE |
| 851 |
sysdate INT := 10; |
| 852 |
BEGIN |
| 853 |
NULL; |
| 854 |
END; |
| 855 |
$$ |
| 856 |
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'sysdate INT := 10; |
| 857 |
BEGIN |
| 858 |
NULL; |
| 859 |
END' at line 2 |
| 860 |
BEGIN |
| 861 |
<<sysdate>> |
| 862 |
NULL; |
| 863 |
END; |
| 864 |
$$ |
| 865 |
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'sysdate>> |
| 866 |
NULL; |
| 867 |
END' at line 2 |
| 868 |
# |
| 869 |
# End of 10.6 tests |
| 870 |
# |