1 2 3--disable_warnings 4drop table if exists t1_30237_bool; 5--enable_warnings 6 7create table t1_30237_bool(A boolean, B boolean, C boolean); 8 9insert into t1_30237_bool values 10(FALSE, FALSE, FALSE), 11(FALSE, FALSE, NULL), 12(FALSE, FALSE, TRUE), 13(FALSE, NULL, FALSE), 14(FALSE, NULL, NULL), 15(FALSE, NULL, TRUE), 16(FALSE, TRUE, FALSE), 17(FALSE, TRUE, NULL), 18(FALSE, TRUE, TRUE), 19(NULL, FALSE, FALSE), 20(NULL, FALSE, NULL), 21(NULL, FALSE, TRUE), 22(NULL, NULL, FALSE), 23(NULL, NULL, NULL), 24(NULL, NULL, TRUE), 25(NULL, TRUE, FALSE), 26(NULL, TRUE, NULL), 27(NULL, TRUE, TRUE), 28(TRUE, FALSE, FALSE), 29(TRUE, FALSE, NULL), 30(TRUE, FALSE, TRUE), 31(TRUE, NULL, FALSE), 32(TRUE, NULL, NULL), 33(TRUE, NULL, TRUE), 34(TRUE, TRUE, FALSE), 35(TRUE, TRUE, NULL), 36(TRUE, TRUE, TRUE) ; 37 38--echo Testing OR, XOR, AND 39select A, B, A OR B, A XOR B, A AND B 40 from t1_30237_bool where C is null order by A, B; 41 42--echo Testing that OR is associative 43select A, B, C, (A OR B) OR C, A OR (B OR C), A OR B OR C 44 from t1_30237_bool order by A, B, C; 45 46select count(*) from t1_30237_bool 47 where ((A OR B) OR C) != (A OR (B OR C)); 48 49--echo Testing that XOR is associative 50select A, B, C, (A XOR B) XOR C, A XOR (B XOR C), A XOR B XOR C 51 from t1_30237_bool order by A, B, C; 52 53select count(*) from t1_30237_bool 54 where ((A XOR B) XOR C) != (A XOR (B XOR C)); 55 56--echo Testing that AND is associative 57select A, B, C, (A AND B) AND C, A AND (B AND C), A AND B AND C 58 from t1_30237_bool order by A, B, C; 59 60select count(*) from t1_30237_bool 61 where ((A AND B) AND C) != (A AND (B AND C)); 62 63--echo Testing that AND has precedence over OR 64select A, B, C, (A OR B) AND C, A OR (B AND C), A OR B AND C 65 from t1_30237_bool order by A, B, C; 66select count(*) from t1_30237_bool 67 where (A OR (B AND C)) != (A OR B AND C); 68select A, B, C, (A AND B) OR C, A AND (B OR C), A AND B OR C 69 from t1_30237_bool order by A, B, C; 70select count(*) from t1_30237_bool 71 where ((A AND B) OR C) != (A AND B OR C); 72 73--echo Testing that AND has precedence over XOR 74select A, B, C, (A XOR B) AND C, A XOR (B AND C), A XOR B AND C 75 from t1_30237_bool order by A, B, C; 76select count(*) from t1_30237_bool 77 where (A XOR (B AND C)) != (A XOR B AND C); 78select A, B, C, (A AND B) XOR C, A AND (B XOR C), A AND B XOR C 79 from t1_30237_bool order by A, B, C; 80select count(*) from t1_30237_bool 81 where ((A AND B) XOR C) != (A AND B XOR C); 82 83--echo Testing that XOR has precedence over OR 84select A, B, C, (A XOR B) OR C, A XOR (B OR C), A XOR B OR C 85 from t1_30237_bool order by A, B, C; 86select count(*) from t1_30237_bool 87 where ((A XOR B) OR C) != (A XOR B OR C); 88select A, B, C, (A OR B) XOR C, A OR (B XOR C), A OR B XOR C 89 from t1_30237_bool order by A, B, C; 90select count(*) from t1_30237_bool 91 where (A OR (B XOR C)) != (A OR B XOR C); 92 93drop table t1_30237_bool; 94 95--echo Testing that NOT has precedence over OR 96select (NOT FALSE) OR TRUE, NOT (FALSE OR TRUE), NOT FALSE OR TRUE; 97 98--echo Testing that NOT has precedence over XOR 99select (NOT FALSE) XOR FALSE, NOT (FALSE XOR FALSE), NOT FALSE XOR FALSE; 100 101--echo Testing that NOT has precedence over AND 102select (NOT FALSE) AND FALSE, NOT (FALSE AND FALSE), NOT FALSE AND FALSE; 103 104--echo Testing that NOT is associative 105select NOT NOT TRUE, NOT NOT NOT FALSE; 106 107--echo Testing that IS has precedence over NOT 108select (NOT NULL) IS TRUE, NOT (NULL IS TRUE), NOT NULL IS TRUE; 109select (NOT NULL) IS NOT TRUE, NOT (NULL IS NOT TRUE), NOT NULL IS NOT TRUE; 110select (NOT NULL) IS FALSE, NOT (NULL IS FALSE), NOT NULL IS FALSE; 111select (NOT NULL) IS NOT FALSE, NOT (NULL IS NOT FALSE), NOT NULL IS NOT FALSE; 112select (NOT TRUE) IS UNKNOWN, NOT (TRUE IS UNKNOWN), NOT TRUE IS UNKNOWN; 113select (NOT TRUE) IS NOT UNKNOWN, NOT (TRUE IS NOT UNKNOWN), NOT TRUE IS NOT UNKNOWN; 114select (NOT TRUE) IS NULL, NOT (TRUE IS NULL), NOT TRUE IS NULL; 115select (NOT TRUE) IS NOT NULL, NOT (TRUE IS NOT NULL), NOT TRUE IS NOT NULL; 116 117--echo Testing that IS [NOT] TRUE/FALSE/UNKNOWN predicates are not associative 118# Documenting existing behavior in 5.0.48 119-- error ER_PARSE_ERROR 120select TRUE IS TRUE IS TRUE IS TRUE; 121-- error ER_PARSE_ERROR 122select FALSE IS NOT TRUE IS NOT TRUE IS NOT TRUE; 123-- error ER_PARSE_ERROR 124select NULL IS FALSE IS FALSE IS FALSE; 125-- error ER_PARSE_ERROR 126select TRUE IS NOT FALSE IS NOT FALSE IS NOT FALSE; 127-- error ER_PARSE_ERROR 128select FALSE IS UNKNOWN IS UNKNOWN IS UNKNOWN; 129-- error ER_PARSE_ERROR 130select TRUE IS NOT UNKNOWN IS NOT UNKNOWN IS NOT UNKNOWN; 131 132--echo Testing that IS [NOT] NULL predicates are associative 133# Documenting existing behavior in 5.0.48 134select FALSE IS NULL IS NULL IS NULL; 135select TRUE IS NOT NULL IS NOT NULL IS NOT NULL; 136 137--echo Testing that comparison operators are left associative 138select 1 <=> 2 <=> 2, (1 <=> 2) <=> 2, 1 <=> (2 <=> 2); 139select 1 = 2 = 2, (1 = 2) = 2, 1 = (2 = 2); 140select 1 != 2 != 3, (1 != 2) != 3, 1 != (2 != 3); 141select 1 <> 2 <> 3, (1 <> 2) <> 3, 1 <> (2 <> 3); 142select 1 < 2 < 3, (1 < 2) < 3, 1 < (2 < 3); 143select 3 <= 2 <= 1, (3 <= 2) <= 1, 3 <= (2 <= 1); 144select 1 > 2 > 3, (1 > 2) > 3, 1 > (2 > 3); 145select 1 >= 2 >= 3, (1 >= 2) >= 3, 1 >= (2 >= 3); 146 147-- echo Testing that | is associative 148select 0xF0 | 0x0F | 0x55, (0xF0 | 0x0F) | 0x55, 0xF0 | (0x0F | 0x55); 149 150-- echo Testing that & is associative 151select 0xF5 & 0x5F & 0x55, (0xF5 & 0x5F) & 0x55, 0xF5 & (0x5F & 0x55); 152 153-- echo Testing that << is left associative 154select 4 << 3 << 2, (4 << 3) << 2, 4 << (3 << 2); 155 156-- echo Testing that >> is left associative 157select 256 >> 3 >> 2, (256 >> 3) >> 2, 256 >> (3 >> 2); 158 159--echo Testing that & has precedence over | 160select 0xF0 & 0x0F | 0x55, (0xF0 & 0x0F) | 0x55, 0xF0 & (0x0F | 0x55); 161select 0x55 | 0xF0 & 0x0F, (0x55 | 0xF0) & 0x0F, 0x55 | (0xF0 & 0x0F); 162 163--echo Testing that << has precedence over | 164select 0x0F << 4 | 0x0F, (0x0F << 4) | 0x0F, 0x0F << (4 | 0x0F); 165select 0x0F | 0x0F << 4, (0x0F | 0x0F) << 4, 0x0F | (0x0F << 4); 166 167--echo Testing that >> has precedence over | 168select 0xF0 >> 4 | 0xFF, (0xF0 >> 4) | 0xFF, 0xF0 >> (4 | 0xFF); 169select 0xFF | 0xF0 >> 4, (0xFF | 0xF0) >> 4, 0xFF | (0xF0 >> 4); 170 171--echo Testing that << has precedence over & 172select 0x0F << 4 & 0xF0, (0x0F << 4) & 0xF0, 0x0F << (4 & 0xF0); 173select 0xF0 & 0x0F << 4, (0xF0 & 0x0F) << 4, 0xF0 & (0x0F << 4); 174 175--echo Testing that >> has precedence over & 176select 0xF0 >> 4 & 0x55, (0xF0 >> 4) & 0x55, 0xF0 >> (4 & 0x55); 177select 0x0F & 0xF0 >> 4, (0x0F & 0xF0) >> 4, 0x0F & (0xF0 >> 4); 178 179--echo Testing that >> and << have the same precedence 180select 0xFF >> 4 << 2, (0xFF >> 4) << 2, 0xFF >> (4 << 2); 181select 0x0F << 4 >> 2, (0x0F << 4) >> 2, 0x0F << (4 >> 2); 182 183--echo Testing that binary + is associative 184select 1 + 2 + 3, (1 + 2) + 3, 1 + (2 + 3); 185 186--echo Testing that binary - is left associative 187select 1 - 2 - 3, (1 - 2) - 3, 1 - (2 - 3); 188 189--echo Testing that binary + and binary - have the same precedence 190# evaluated left to right 191select 1 + 2 - 3, (1 + 2) - 3, 1 + (2 - 3); 192select 1 - 2 + 3, (1 - 2) + 3, 1 - (2 + 3); 193 194--echo Testing that binary + has precedence over | 195select 0xF0 + 0x0F | 0x55, (0xF0 + 0x0F) | 0x55, 0xF0 + (0x0F | 0x55); 196select 0x55 | 0xF0 + 0x0F, (0x55 | 0xF0) + 0x0F, 0x55 | (0xF0 + 0x0F); 197 198--echo Testing that binary + has precedence over & 199select 0xF0 + 0x0F & 0x55, (0xF0 + 0x0F) & 0x55, 0xF0 + (0x0F & 0x55); 200select 0x55 & 0xF0 + 0x0F, (0x55 & 0xF0) + 0x0F, 0x55 & (0xF0 + 0x0F); 201 202--echo Testing that binary + has precedence over << 203select 2 + 3 << 4, (2 + 3) << 4, 2 + (3 << 4); 204select 3 << 4 + 2, (3 << 4) + 2, 3 << (4 + 2); 205 206--echo Testing that binary + has precedence over >> 207select 4 + 3 >> 2, (4 + 3) >> 2, 4 + (3 >> 2); 208select 3 >> 2 + 1, (3 >> 2) + 1, 3 >> (2 + 1); 209 210--echo Testing that binary - has precedence over | 211select 0xFF - 0x0F | 0x55, (0xFF - 0x0F) | 0x55, 0xFF - (0x0F | 0x55); 212select 0x55 | 0xFF - 0xF0, (0x55 | 0xFF) - 0xF0, 0x55 | (0xFF - 0xF0); 213 214--echo Testing that binary - has precedence over & 215select 0xFF - 0xF0 & 0x55, (0xFF - 0xF0) & 0x55, 0xFF - (0xF0 & 0x55); 216select 0x55 & 0xFF - 0x49, (0x55 & 0xFF) - 0x49, 0x55 & (0xFF - 0x49); 217 218--echo Testing that binary - has precedence over << 219select 16 - 3 << 2, (16 - 3) << 2, 16 - (3 << 2); 220select 4 << 3 - 2, (4 << 3) - 2, 4 << (3 - 2); 221 222--echo Testing that binary - has precedence over >> 223select 16 - 3 >> 2, (16 - 3) >> 2, 16 - (3 >> 2); 224select 16 >> 3 - 2, (16 >> 3) - 2, 16 >> (3 - 2); 225 226--echo Testing that * is associative 227select 2 * 3 * 4, (2 * 3) * 4, 2 * (3 * 4); 228 229--echo Testing that * has precedence over | 230select 2 * 0x40 | 0x0F, (2 * 0x40) | 0x0F, 2 * (0x40 | 0x0F); 231select 0x0F | 2 * 0x40, (0x0F | 2) * 0x40, 0x0F | (2 * 0x40); 232 233--echo Testing that * has precedence over & 234select 2 * 0x40 & 0x55, (2 * 0x40) & 0x55, 2 * (0x40 & 0x55); 235select 0xF0 & 2 * 0x40, (0xF0 & 2) * 0x40, 0xF0 & (2 * 0x40); 236 237--echo Testing that * has precedence over << 238# Actually, can't prove it for the first case, 239# since << is a multiplication by a power of 2, 240# and * is associative 241select 5 * 3 << 4, (5 * 3) << 4, 5 * (3 << 4); 242select 2 << 3 * 4, (2 << 3) * 4, 2 << (3 * 4); 243 244--echo Testing that * has precedence over >> 245# >> is a multiplication by a (negative) power of 2, 246# see above. 247select 3 * 4 >> 2, (3 * 4) >> 2, 3 * (4 >> 2); 248select 4 >> 2 * 3, (4 >> 2) * 3, 4 >> (2 * 3); 249 250--echo Testing that * has precedence over binary + 251select 2 * 3 + 4, (2 * 3) + 4, 2 * (3 + 4); 252select 2 + 3 * 4, (2 + 3) * 4, 2 + (3 * 4); 253 254--echo Testing that * has precedence over binary - 255select 4 * 3 - 2, (4 * 3) - 2, 4 * (3 - 2); 256select 4 - 3 * 2, (4 - 3) * 2, 4 - (3 * 2); 257 258--echo Testing that / is left associative 259select 15 / 5 / 3, (15 / 5) / 3, 15 / (5 / 3); 260 261--echo Testing that / has precedence over | 262select 105 / 5 | 2, (105 / 5) | 2, 105 / (5 | 2); 263select 105 | 2 / 5, (105 | 2) / 5, 105 | (2 / 5); 264 265--echo Testing that / has precedence over & 266select 105 / 5 & 0x0F, (105 / 5) & 0x0F, 105 / (5 & 0x0F); 267select 0x0F & 105 / 5, (0x0F & 105) / 5, 0x0F & (105 / 5); 268 269--echo Testing that / has precedence over << 270select 0x80 / 4 << 2, (0x80 / 4) << 2, 0x80 / (4 << 2); 271select 0x80 << 4 / 2, (0x80 << 4) / 2, 0x80 << (4 / 2); 272 273--echo Testing that / has precedence over >> 274select 0x80 / 4 >> 2, (0x80 / 4) >> 2, 0x80 / (4 >> 2); 275select 0x80 >> 4 / 2, (0x80 >> 4) / 2, 0x80 >> (4 / 2); 276 277--echo Testing that / has precedence over binary + 278select 0x80 / 2 + 2, (0x80 / 2) + 2, 0x80 / (2 + 2); 279select 0x80 + 2 / 2, (0x80 + 2) / 2, 0x80 + (2 / 2); 280 281--echo Testing that / has precedence over binary - 282select 0x80 / 4 - 2, (0x80 / 4) - 2, 0x80 / (4 - 2); 283select 0x80 - 4 / 2, (0x80 - 4) / 2, 0x80 - (4 / 2); 284 285# TODO: %, DIV, MOD 286 287--echo Testing that ^ is associative 288select 0xFF ^ 0xF0 ^ 0x0F, (0xFF ^ 0xF0) ^ 0x0F, 0xFF ^ (0xF0 ^ 0x0F); 289select 0xFF ^ 0xF0 ^ 0x55, (0xFF ^ 0xF0) ^ 0x55, 0xFF ^ (0xF0 ^ 0x55); 290 291--echo Testing that ^ has precedence over | 292select 0xFF ^ 0xF0 | 0x0F, (0xFF ^ 0xF0) | 0x0F, 0xFF ^ (0xF0 | 0x0F); 293select 0xF0 | 0xFF ^ 0xF0, (0xF0 | 0xFF) ^ 0xF0, 0xF0 | (0xFF ^ 0xF0); 294 295--echo Testing that ^ has precedence over & 296select 0xFF ^ 0xF0 & 0x0F, (0xFF ^ 0xF0) & 0x0F, 0xFF ^ (0xF0 & 0x0F); 297select 0x0F & 0xFF ^ 0xF0, (0x0F & 0xFF) ^ 0xF0, 0x0F & (0xFF ^ 0xF0); 298 299--echo Testing that ^ has precedence over << 300select 0xFF ^ 0xF0 << 2, (0xFF ^ 0xF0) << 2, 0xFF ^ (0xF0 << 2); 301select 0x0F << 2 ^ 0xFF, (0x0F << 2) ^ 0xFF, 0x0F << (2 ^ 0xFF); 302 303--echo Testing that ^ has precedence over >> 304select 0xFF ^ 0xF0 >> 2, (0xFF ^ 0xF0) >> 2, 0xFF ^ (0xF0 >> 2); 305select 0xFF >> 2 ^ 0xF0, (0xFF >> 2) ^ 0xF0, 0xFF >> (2 ^ 0xF0); 306 307--echo Testing that ^ has precedence over binary + 308select 0xFF ^ 0xF0 + 0x0F, (0xFF ^ 0xF0) + 0x0F, 0xFF ^ (0xF0 + 0x0F); 309select 0x0F + 0xFF ^ 0xF0, (0x0F + 0xFF) ^ 0xF0, 0x0F + (0xFF ^ 0xF0); 310 311--echo Testing that ^ has precedence over binary - 312select 0xFF ^ 0xF0 - 1, (0xFF ^ 0xF0) - 1, 0xFF ^ (0xF0 - 1); 313select 0x65 - 0x0F ^ 0x55, (0x65 - 0x0F) ^ 0x55, 0x65 - (0x0F ^ 0x55); 314 315--echo Testing that ^ has precedence over * 316select 0xFF ^ 0xF0 * 2, (0xFF ^ 0xF0) * 2, 0xFF ^ (0xF0 * 2); 317select 2 * 0xFF ^ 0xF0, (2 * 0xFF) ^ 0xF0, 2 * (0xFF ^ 0xF0); 318 319--echo Testing that ^ has precedence over / 320select 0xFF ^ 0xF0 / 2, (0xFF ^ 0xF0) / 2, 0xFF ^ (0xF0 / 2); 321select 0xF2 / 2 ^ 0xF0, (0xF2 / 2) ^ 0xF0, 0xF2 / (2 ^ 0xF0); 322 323--echo Testing that ^ has precedence over % 324select 0xFF ^ 0xF0 % 0x20, (0xFF ^ 0xF0) % 0x20, 0xFF ^ (0xF0 % 0x20); 325select 0xFF % 0x20 ^ 0xF0, (0xFF % 0x20) ^ 0xF0, 0xFF % (0x20 ^ 0xF0); 326 327--echo Testing that ^ has precedence over DIV 328select 0xFF ^ 0xF0 DIV 2, (0xFF ^ 0xF0) DIV 2, 0xFF ^ (0xF0 DIV 2); 329select 0xF2 DIV 2 ^ 0xF0, (0xF2 DIV 2) ^ 0xF0, 0xF2 DIV (2 ^ 0xF0); 330 331--echo Testing that ^ has precedence over MOD 332select 0xFF ^ 0xF0 MOD 0x20, (0xFF ^ 0xF0) MOD 0x20, 0xFF ^ (0xF0 MOD 0x20); 333select 0xFF MOD 0x20 ^ 0xF0, (0xFF MOD 0x20) ^ 0xF0, 0xFF MOD (0x20 ^ 0xF0); 334 335