1drop table if exists t1; 2set sql_mode="MySQL40"; 3select @@sql_mode; 4@@sql_mode 5MYSQL40,HIGH_NOT_PRECEDENCE 6set @@sql_mode="ANSI"; 7select @@sql_mode; 8@@sql_mode 9REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ANSI 10SELECT 'A' || 'B'; 11'A' || 'B' 12AB 13CREATE TABLE t1 (id INT, id2 int); 14SELECT id,NULL,1,1.1,'a' FROM t1 GROUP BY id; 15id NULL 1 1.1 a 16SELECT id FROM t1 GROUP BY id2; 17id 18drop table t1; 19SET @@SQL_MODE=""; 20CREATE TABLE t1 (i int auto_increment NOT NULL, PRIMARY KEY (i)); 21SHOW CREATE TABLE t1; 22Table Create Table 23t1 CREATE TABLE `t1` ( 24 `i` int(11) NOT NULL AUTO_INCREMENT, 25 PRIMARY KEY (`i`) 26) ENGINE=MyISAM DEFAULT CHARSET=latin1 27SET @@SQL_MODE="MYSQL323"; 28SHOW CREATE TABLE t1; 29Table Create Table 30t1 CREATE TABLE `t1` ( 31 `i` int(11) NOT NULL AUTO_INCREMENT, 32 PRIMARY KEY (`i`) 33) TYPE=MyISAM 34SET @@SQL_MODE="MYSQL40"; 35SHOW CREATE TABLE t1; 36Table Create Table 37t1 CREATE TABLE `t1` ( 38 `i` int(11) NOT NULL AUTO_INCREMENT, 39 PRIMARY KEY (`i`) 40) TYPE=MyISAM 41SET @@SQL_MODE="NO_FIELD_OPTIONS"; 42SHOW CREATE TABLE t1; 43Table Create Table 44t1 CREATE TABLE `t1` ( 45 `i` int(11) NOT NULL, 46 PRIMARY KEY (`i`) 47) ENGINE=MyISAM DEFAULT CHARSET=latin1 48DROP TABLE t1; 49# 50# MDEV-16186 Concatenation operator || returns wrong results in sql_mode=ORACLE 51# 52SET sql_mode=ANSI; 53SELECT -1<<1||1 AS a FROM DUAL; 54a 5518446744073709549568 56SELECT -1||0<<1 AS a FROM DUAL; 57a 5818446744073709551596 59EXPLAIN EXTENDED SELECT -1<<1||1 AS a FROM DUAL; 60id select_type table type possible_keys key key_len ref rows filtered Extra 611 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used 62Warnings: 63Note 1003 select -1 << concat(1,1) AS "a" 64EXPLAIN EXTENDED SELECT -1||0<<1 AS a FROM DUAL; 65id select_type table type possible_keys key key_len ref rows filtered Extra 661 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used 67Warnings: 68Note 1003 select concat(-1,0) << 1 AS "a" 69SELECT -1+1||1 AS a FROM DUAL; 70a 7110 72SELECT -1||0+1 AS a FROM DUAL; 73a 74-9 75EXPLAIN EXTENDED SELECT -1+1||1 AS a FROM DUAL; 76id select_type table type possible_keys key key_len ref rows filtered Extra 771 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used 78Warnings: 79Note 1003 select -1 + concat(1,1) AS "a" 80EXPLAIN EXTENDED SELECT -1||0+1 AS a FROM DUAL; 81id select_type table type possible_keys key key_len ref rows filtered Extra 821 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used 83Warnings: 84Note 1003 select concat(-1,0) + 1 AS "a" 85SELECT 1*1||-1 AS a FROM DUAL; 86a 871 88Warnings: 89Warning 1292 Truncated incorrect DOUBLE value: '1-1' 90SELECT 1||1*-1 AS a FROM DUAL; 91a 92-11 93EXPLAIN EXTENDED SELECT 1*1||-1 AS a FROM DUAL; 94id select_type table type possible_keys key key_len ref rows filtered Extra 951 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used 96Warnings: 97Note 1003 select 1 * concat(1,-1) AS "a" 98EXPLAIN EXTENDED SELECT 1||1*-1 AS a FROM DUAL; 99id select_type table type possible_keys key key_len ref rows filtered Extra 1001 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used 101Warnings: 102Note 1003 select concat(1,1) * -1 AS "a" 103SELECT -1^1||1 AS a FROM DUAL; 104a 10518446744073709551604 106SELECT -1||0^1 AS a FROM DUAL; 107a 10818446744073709551607 109EXPLAIN EXTENDED SELECT -1^1||1 AS a FROM DUAL; 110id select_type table type possible_keys key key_len ref rows filtered Extra 1111 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used 112Warnings: 113Note 1003 select -1 ^ concat(1,1) AS "a" 114EXPLAIN EXTENDED SELECT -1||0^1 AS a FROM DUAL; 115id select_type table type possible_keys key key_len ref rows filtered Extra 1161 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used 117Warnings: 118Note 1003 select concat(-1,0) ^ 1 AS "a" 119