Debian Codegraph @

mysql-test/suite/compat/oracle/t/minus.test

raw
1 CREATE TABLE tx1 (c1 int, c2 varchar(30));
2 CREATE TABLE tx2 (c1 int, c2 varchar(30));
3 CREATE TABLE tx3 (c1 int, c2 varchar(30));
4 INSERT INTO tx1 VALUES (1, 'jim');
5 INSERT INTO tx1 VALUES (2, 'menny');
6 INSERT INTO tx1 VALUES (3, 'linda');
7
8 INSERT INTO tx2 VALUES (1, 'jim');
9 INSERT INTO tx2 VALUES (2, 'kris');
10 INSERT INTO tx2 VALUES (3, 'shory');
11
12 INSERT INTO tx3 VALUES (1, 'jim');
13 INSERT INTO tx3 VALUES (2, 'kris');
14 INSERT INTO tx3 VALUES (3, 'linda');
15
16 --echo #
17 --echo # test when sql_mode is not oracle
18 --echo #
19
20 SELECT c2 FROM tx1 EXCEPT SELECT c2 from tx2;
21 --error 1064
22 SELECT c2 FROM tx1 MINUS SELECT c2 from tx2;
23
24 # MINUS should not be a reserved word
25 create table MINUS (a int);
26 drop table MINUS;
27
28 --echo #
29 --echo # test when sql_mode is oracle
30 --echo #
31
32 SET sql_mode=ORACLE;
33 SELECT c2 FROM tx1 MINUS SELECT c2 from tx2;
34 SELECT c2 FROM tx1 MINUS SELECT c2 from tx2 MINUS SELECT c2 from tx3;
35 SELECT c2 FROM tx1 MINUS SELECT c2 from tx2 EXCEPT SELECT c2 from tx3;
36 SELECT c2 FROM tx1 MINUS SELECT c2 from tx2 UNION SELECT c2 from tx3;
37
38 # MINUS should be a reserved word
39 --error ER_PARSE_ERROR
40 create table MINUS (a int);
41
42 DROP TABLE tx1;
43 DROP TABLE tx2;
44 DROP TABLE tx3;