1# 2# Start of 10.1 tests 3# 4# 5# MDEV-8865 Wrong field type or metadata for COALESCE(signed_int_column, unsigned_int_column) 6# 7# 8CREATE TABLE t1 (a INT, b INT UNSIGNED); 9INSERT INTO t1 VALUES (1,1); 10INSERT INTO t1 VALUES (-1,1); 11INSERT INTO t1 VALUES (-2147483648,4294967295); 12SELECT 13a AS ___________a, 14CASE WHEN a IS NOT NULL THEN a END AS case_______a, 15CASE WHEN a IS NOT NULL THEN a ELSE a END AS case_____a_a, 16COALESCE(a) AS coalesce___a, 17COALESCE(a, a) AS coalesce_a_a, 18IF(a IS NULL, a, a) AS if_______a_a, 19IFNULL(a, a) AS ifnull___a_a, 20LEAST(a, a) AS least____a_a, 21GREATEST(a, a) AS greatest_a_a, 22b AS ___________b, 23CASE WHEN a IS NOT NULL THEN b END AS case_______b, 24CASE WHEN a IS NOT NULL THEN b ELSE b END AS case_____b_b, 25COALESCE(b) AS coalesce___b, 26COALESCE(b, b) AS coalesce_b_b, 27IF(a IS NULL, b, b) AS if_______b_b, 28IFNULL(b, b) AS ifnull___b_b, 29LEAST(b, b) AS least____b_b, 30GREATEST(b, b) AS greatest_b_b 31FROM t1; 32Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr 33def test t1 t1 a ___________a 3 11 11 Y 32768 0 63 34def case_______a 3 11 11 Y 32896 0 63 35def case_____a_a 3 11 11 Y 32896 0 63 36def coalesce___a 3 11 11 Y 32896 0 63 37def coalesce_a_a 3 11 11 Y 32896 0 63 38def if_______a_a 3 11 11 Y 32896 0 63 39def ifnull___a_a 3 11 11 Y 32896 0 63 40def least____a_a 3 11 11 Y 32896 0 63 41def greatest_a_a 3 11 11 Y 32896 0 63 42def test t1 t1 b ___________b 3 10 10 Y 32800 0 63 43def case_______b 3 10 10 Y 32928 0 63 44def case_____b_b 3 10 10 Y 32928 0 63 45def coalesce___b 3 10 10 Y 32928 0 63 46def coalesce_b_b 3 10 10 Y 32928 0 63 47def if_______b_b 3 10 10 Y 32928 0 63 48def ifnull___b_b 3 10 10 Y 32928 0 63 49def least____b_b 3 10 10 Y 32928 0 63 50def greatest_b_b 3 10 10 Y 32928 0 63 51___________a 1 52case_______a 1 53case_____a_a 1 54coalesce___a 1 55coalesce_a_a 1 56if_______a_a 1 57ifnull___a_a 1 58least____a_a 1 59greatest_a_a 1 60___________b 1 61case_______b 1 62case_____b_b 1 63coalesce___b 1 64coalesce_b_b 1 65if_______b_b 1 66ifnull___b_b 1 67least____b_b 1 68greatest_b_b 1 69___________a -1 70case_______a -1 71case_____a_a -1 72coalesce___a -1 73coalesce_a_a -1 74if_______a_a -1 75ifnull___a_a -1 76least____a_a -1 77greatest_a_a -1 78___________b 1 79case_______b 1 80case_____b_b 1 81coalesce___b 1 82coalesce_b_b 1 83if_______b_b 1 84ifnull___b_b 1 85least____b_b 1 86greatest_b_b 1 87___________a -2147483648 88case_______a -2147483648 89case_____a_a -2147483648 90coalesce___a -2147483648 91coalesce_a_a -2147483648 92if_______a_a -2147483648 93ifnull___a_a -2147483648 94least____a_a -2147483648 95greatest_a_a -2147483648 96___________b 4294967295 97case_______b 4294967295 98case_____b_b 4294967295 99coalesce___b 4294967295 100coalesce_b_b 4294967295 101if_______b_b 4294967295 102ifnull___b_b 4294967295 103least____b_b 4294967295 104greatest_b_b 4294967295 105SELECT 106CASE WHEN a IS NOT NULL THEN a ELSE b END AS case_____a_b, 107CASE WHEN a IS NOT NULL THEN b ELSE a END AS case_____b_a, 108COALESCE(a, b) AS coalesce_a_b, 109COALESCE(b, a) AS coalesce_b_a, 110IF(a IS NULL, a, b) AS if_______a_b, 111IF(a IS NULL, b, a) AS if_______b_a, 112IFNULL(a, b) AS ifnull___a_b, 113IFNULL(b, a) AS ifnull___b_a, 114LEAST(a, b) AS least____a_b, 115LEAST(b, a) AS least____b_a, 116GREATEST(a, b) AS greatest_a_b, 117GREATEST(b, a) AS greatest_b_a 118FROM t1; 119Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr 120def case_____a_b 246 11 11 Y 32896 0 63 121def case_____b_a 246 11 10 Y 32896 0 63 122def coalesce_a_b 246 11 11 Y 32896 0 63 123def coalesce_b_a 246 11 10 Y 32896 0 63 124def if_______a_b 246 11 10 Y 32896 0 63 125def if_______b_a 246 11 11 Y 32896 0 63 126def ifnull___a_b 246 11 11 Y 32896 0 63 127def ifnull___b_a 246 11 10 Y 32896 0 63 128def least____a_b 246 11 11 Y 32896 0 63 129def least____b_a 246 11 11 Y 32896 0 63 130def greatest_a_b 246 11 10 Y 32896 0 63 131def greatest_b_a 246 11 10 Y 32896 0 63 132case_____a_b 1 133case_____b_a 1 134coalesce_a_b 1 135coalesce_b_a 1 136if_______a_b 1 137if_______b_a 1 138ifnull___a_b 1 139ifnull___b_a 1 140least____a_b 1 141least____b_a 1 142greatest_a_b 1 143greatest_b_a 1 144case_____a_b -1 145case_____b_a 1 146coalesce_a_b -1 147coalesce_b_a 1 148if_______a_b 1 149if_______b_a -1 150ifnull___a_b -1 151ifnull___b_a 1 152least____a_b -1 153least____b_a -1 154greatest_a_b 1 155greatest_b_a 1 156case_____a_b -2147483648 157case_____b_a 4294967295 158coalesce_a_b -2147483648 159coalesce_b_a 4294967295 160if_______a_b 4294967295 161if_______b_a -2147483648 162ifnull___a_b -2147483648 163ifnull___b_a 4294967295 164least____a_b -2147483648 165least____b_a -2147483648 166greatest_a_b 4294967295 167greatest_b_a 4294967295 168CREATE TABLE t2 AS 169SELECT 170a AS ___________a, 171CASE WHEN a IS NOT NULL THEN a END AS case_______a, 172CASE WHEN a IS NOT NULL THEN a ELSE a END AS case_____a_a, 173COALESCE(a) AS coalesce___a, 174COALESCE(a, a) AS coalesce_a_a, 175IF(a IS NULL, a, a) AS if_______a_a, 176IFNULL(a, a) AS ifnull___a_a, 177LEAST(a, a) AS least____a_a, 178GREATEST(a, a) AS greatest_a_a, 179b AS ___________b, 180CASE WHEN a IS NOT NULL THEN b END AS case_______b, 181CASE WHEN a IS NOT NULL THEN b ELSE b END AS case_____b_b, 182COALESCE(b) AS coalesce___b, 183COALESCE(b, b) AS coalesce_b_b, 184IF(a IS NULL, b, b) AS if_______b_b, 185IFNULL(b, b) AS ifnull___b_b, 186LEAST(b, b) AS least____b_b, 187GREATEST(b, b) AS greatest_b_b 188FROM t1; 189SHOW CREATE TABLE t2; 190Table Create Table 191t2 CREATE TABLE `t2` ( 192 `___________a` int(11) DEFAULT NULL, 193 `case_______a` int(11) DEFAULT NULL, 194 `case_____a_a` int(11) DEFAULT NULL, 195 `coalesce___a` int(11) DEFAULT NULL, 196 `coalesce_a_a` int(11) DEFAULT NULL, 197 `if_______a_a` int(11) DEFAULT NULL, 198 `ifnull___a_a` int(11) DEFAULT NULL, 199 `least____a_a` int(11) DEFAULT NULL, 200 `greatest_a_a` int(11) DEFAULT NULL, 201 `___________b` int(10) unsigned DEFAULT NULL, 202 `case_______b` int(10) unsigned DEFAULT NULL, 203 `case_____b_b` int(10) unsigned DEFAULT NULL, 204 `coalesce___b` int(10) unsigned DEFAULT NULL, 205 `coalesce_b_b` int(10) unsigned DEFAULT NULL, 206 `if_______b_b` int(10) unsigned DEFAULT NULL, 207 `ifnull___b_b` int(10) unsigned DEFAULT NULL, 208 `least____b_b` int(10) unsigned DEFAULT NULL, 209 `greatest_b_b` int(10) unsigned DEFAULT NULL 210) ENGINE=MyISAM DEFAULT CHARSET=latin1 211DROP TABLE t2; 212SET STATEMENT sql_mode = 'NO_ENGINE_SUBSTITUTION' FOR 213CREATE TABLE t2 AS 214SELECT 215CASE WHEN a IS NOT NULL THEN a ELSE b END AS case_____a_b, 216CASE WHEN a IS NOT NULL THEN b ELSE a END AS case_____b_a, 217COALESCE(a, b) AS coalesce_a_b, 218COALESCE(b, a) AS coalesce_b_a, 219IF(a IS NULL, a, b) AS if_______a_b, 220IF(a IS NULL, b, a) AS if_______b_a, 221IFNULL(a, b) AS ifnull___a_b, 222IFNULL(b, a) AS ifnull___b_a, 223LEAST(a, b) AS least____a_b, 224LEAST(b, a) AS least____b_a, 225GREATEST(a, b) AS greatest_a_b, 226GREATEST(b, a) AS greatest_b_a 227FROM t1; 228SHOW CREATE TABLE t2; 229Table Create Table 230t2 CREATE TABLE `t2` ( 231 `case_____a_b` decimal(10,0) DEFAULT NULL, 232 `case_____b_a` decimal(10,0) DEFAULT NULL, 233 `coalesce_a_b` decimal(10,0) DEFAULT NULL, 234 `coalesce_b_a` decimal(10,0) DEFAULT NULL, 235 `if_______a_b` decimal(10,0) DEFAULT NULL, 236 `if_______b_a` decimal(10,0) DEFAULT NULL, 237 `ifnull___a_b` decimal(10,0) DEFAULT NULL, 238 `ifnull___b_a` decimal(10,0) DEFAULT NULL, 239 `least____a_b` decimal(10,0) DEFAULT NULL, 240 `least____b_a` decimal(10,0) DEFAULT NULL, 241 `greatest_a_b` decimal(10,0) DEFAULT NULL, 242 `greatest_b_a` decimal(10,0) DEFAULT NULL 243) ENGINE=MyISAM DEFAULT CHARSET=latin1 244DROP TABLE t2; 245DROP TABLE t1; 246# 247CREATE TABLE t1 (a INT, b INT); 248INSERT INTO t1 VALUES (1,1); 249INSERT INTO t1 VALUES (-2147483648,2147483647); 250SELECT 251a AS ___________a, 252CASE WHEN a IS NOT NULL THEN a END AS case_______a, 253CASE WHEN a IS NOT NULL THEN a ELSE a END AS case_____a_a, 254COALESCE(a) AS coalesce___a, 255COALESCE(a, a) AS coalesce_a_a, 256IF(a IS NULL, a, a) AS if_______a_a, 257IFNULL(a, a) AS ifnull___a_a, 258LEAST(a, a) AS least____a_a, 259GREATEST(a, a) AS greatest_a_a, 260b AS ___________b, 261CASE WHEN a IS NOT NULL THEN b END AS case_______b, 262CASE WHEN a IS NOT NULL THEN b ELSE b END AS case_____b_b, 263COALESCE(b) AS coalesce___b, 264COALESCE(b, b) AS coalesce_b_b, 265IF(a IS NULL, b, b) AS if_______b_b, 266IFNULL(b, b) AS ifnull___b_b, 267LEAST(b, b) AS least____b_b, 268GREATEST(b, b) AS greatest_b_b 269FROM t1; 270Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr 271def test t1 t1 a ___________a 3 11 11 Y 32768 0 63 272def case_______a 3 11 11 Y 32896 0 63 273def case_____a_a 3 11 11 Y 32896 0 63 274def coalesce___a 3 11 11 Y 32896 0 63 275def coalesce_a_a 3 11 11 Y 32896 0 63 276def if_______a_a 3 11 11 Y 32896 0 63 277def ifnull___a_a 3 11 11 Y 32896 0 63 278def least____a_a 3 11 11 Y 32896 0 63 279def greatest_a_a 3 11 11 Y 32896 0 63 280def test t1 t1 b ___________b 3 11 10 Y 32768 0 63 281def case_______b 3 11 10 Y 32896 0 63 282def case_____b_b 3 11 10 Y 32896 0 63 283def coalesce___b 3 11 10 Y 32896 0 63 284def coalesce_b_b 3 11 10 Y 32896 0 63 285def if_______b_b 3 11 10 Y 32896 0 63 286def ifnull___b_b 3 11 10 Y 32896 0 63 287def least____b_b 3 11 10 Y 32896 0 63 288def greatest_b_b 3 11 10 Y 32896 0 63 289___________a 1 290case_______a 1 291case_____a_a 1 292coalesce___a 1 293coalesce_a_a 1 294if_______a_a 1 295ifnull___a_a 1 296least____a_a 1 297greatest_a_a 1 298___________b 1 299case_______b 1 300case_____b_b 1 301coalesce___b 1 302coalesce_b_b 1 303if_______b_b 1 304ifnull___b_b 1 305least____b_b 1 306greatest_b_b 1 307___________a -2147483648 308case_______a -2147483648 309case_____a_a -2147483648 310coalesce___a -2147483648 311coalesce_a_a -2147483648 312if_______a_a -2147483648 313ifnull___a_a -2147483648 314least____a_a -2147483648 315greatest_a_a -2147483648 316___________b 2147483647 317case_______b 2147483647 318case_____b_b 2147483647 319coalesce___b 2147483647 320coalesce_b_b 2147483647 321if_______b_b 2147483647 322ifnull___b_b 2147483647 323least____b_b 2147483647 324greatest_b_b 2147483647 325SELECT 326CASE WHEN a IS NOT NULL THEN a ELSE b END AS case_____a_b, 327CASE WHEN a IS NOT NULL THEN b ELSE a END AS case_____b_a, 328COALESCE(a, b) AS coalesce_a_b, 329COALESCE(b, a) AS coalesce_b_a, 330IF(a IS NULL, a, b) AS if_______a_b, 331IF(a IS NULL, b, a) AS if_______b_a, 332IFNULL(a, b) AS ifnull___a_b, 333IFNULL(b, a) AS ifnull___b_a, 334LEAST(a, b) AS least____a_b, 335LEAST(b, a) AS least____b_a, 336GREATEST(a, b) AS greatest_a_b, 337GREATEST(b, a) AS greatest_b_a 338FROM t1; 339Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr 340def case_____a_b 3 11 11 Y 32896 0 63 341def case_____b_a 3 11 10 Y 32896 0 63 342def coalesce_a_b 3 11 11 Y 32896 0 63 343def coalesce_b_a 3 11 10 Y 32896 0 63 344def if_______a_b 3 11 10 Y 32896 0 63 345def if_______b_a 3 11 11 Y 32896 0 63 346def ifnull___a_b 3 11 11 Y 32896 0 63 347def ifnull___b_a 3 11 10 Y 32896 0 63 348def least____a_b 3 11 11 Y 32896 0 63 349def least____b_a 3 11 11 Y 32896 0 63 350def greatest_a_b 3 11 10 Y 32896 0 63 351def greatest_b_a 3 11 10 Y 32896 0 63 352case_____a_b 1 353case_____b_a 1 354coalesce_a_b 1 355coalesce_b_a 1 356if_______a_b 1 357if_______b_a 1 358ifnull___a_b 1 359ifnull___b_a 1 360least____a_b 1 361least____b_a 1 362greatest_a_b 1 363greatest_b_a 1 364case_____a_b -2147483648 365case_____b_a 2147483647 366coalesce_a_b -2147483648 367coalesce_b_a 2147483647 368if_______a_b 2147483647 369if_______b_a -2147483648 370ifnull___a_b -2147483648 371ifnull___b_a 2147483647 372least____a_b -2147483648 373least____b_a -2147483648 374greatest_a_b 2147483647 375greatest_b_a 2147483647 376CREATE TABLE t2 AS 377SELECT 378a AS ___________a, 379CASE WHEN a IS NOT NULL THEN a END AS case_______a, 380CASE WHEN a IS NOT NULL THEN a ELSE a END AS case_____a_a, 381COALESCE(a) AS coalesce___a, 382COALESCE(a, a) AS coalesce_a_a, 383IF(a IS NULL, a, a) AS if_______a_a, 384IFNULL(a, a) AS ifnull___a_a, 385LEAST(a, a) AS least____a_a, 386GREATEST(a, a) AS greatest_a_a, 387b AS ___________b, 388CASE WHEN a IS NOT NULL THEN b END AS case_______b, 389CASE WHEN a IS NOT NULL THEN b ELSE b END AS case_____b_b, 390COALESCE(b) AS coalesce___b, 391COALESCE(b, b) AS coalesce_b_b, 392IF(a IS NULL, b, b) AS if_______b_b, 393IFNULL(b, b) AS ifnull___b_b, 394LEAST(b, b) AS least____b_b, 395GREATEST(b, b) AS greatest_b_b 396FROM t1; 397SHOW CREATE TABLE t2; 398Table Create Table 399t2 CREATE TABLE `t2` ( 400 `___________a` int(11) DEFAULT NULL, 401 `case_______a` int(11) DEFAULT NULL, 402 `case_____a_a` int(11) DEFAULT NULL, 403 `coalesce___a` int(11) DEFAULT NULL, 404 `coalesce_a_a` int(11) DEFAULT NULL, 405 `if_______a_a` int(11) DEFAULT NULL, 406 `ifnull___a_a` int(11) DEFAULT NULL, 407 `least____a_a` int(11) DEFAULT NULL, 408 `greatest_a_a` int(11) DEFAULT NULL, 409 `___________b` int(11) DEFAULT NULL, 410 `case_______b` int(11) DEFAULT NULL, 411 `case_____b_b` int(11) DEFAULT NULL, 412 `coalesce___b` int(11) DEFAULT NULL, 413 `coalesce_b_b` int(11) DEFAULT NULL, 414 `if_______b_b` int(11) DEFAULT NULL, 415 `ifnull___b_b` int(11) DEFAULT NULL, 416 `least____b_b` int(11) DEFAULT NULL, 417 `greatest_b_b` int(11) DEFAULT NULL 418) ENGINE=MyISAM DEFAULT CHARSET=latin1 419DROP TABLE t2; 420SET STATEMENT sql_mode = 'NO_ENGINE_SUBSTITUTION' FOR 421CREATE TABLE t2 AS 422SELECT 423CASE WHEN a IS NOT NULL THEN a ELSE b END AS case_____a_b, 424CASE WHEN a IS NOT NULL THEN b ELSE a END AS case_____b_a, 425COALESCE(a, b) AS coalesce_a_b, 426COALESCE(b, a) AS coalesce_b_a, 427IF(a IS NULL, a, b) AS if_______a_b, 428IF(a IS NULL, b, a) AS if_______b_a, 429IFNULL(a, b) AS ifnull___a_b, 430IFNULL(b, a) AS ifnull___b_a, 431LEAST(a, b) AS least____a_b, 432LEAST(b, a) AS least____b_a, 433GREATEST(a, b) AS greatest_a_b, 434GREATEST(b, a) AS greatest_b_a 435FROM t1; 436SHOW CREATE TABLE t2; 437Table Create Table 438t2 CREATE TABLE `t2` ( 439 `case_____a_b` int(11) DEFAULT NULL, 440 `case_____b_a` int(11) DEFAULT NULL, 441 `coalesce_a_b` int(11) DEFAULT NULL, 442 `coalesce_b_a` int(11) DEFAULT NULL, 443 `if_______a_b` int(11) DEFAULT NULL, 444 `if_______b_a` int(11) DEFAULT NULL, 445 `ifnull___a_b` int(11) DEFAULT NULL, 446 `ifnull___b_a` int(11) DEFAULT NULL, 447 `least____a_b` int(11) DEFAULT NULL, 448 `least____b_a` int(11) DEFAULT NULL, 449 `greatest_a_b` int(11) DEFAULT NULL, 450 `greatest_b_a` int(11) DEFAULT NULL 451) ENGINE=MyISAM DEFAULT CHARSET=latin1 452DROP TABLE t2; 453DROP TABLE t1; 454# 455CREATE TABLE t1 (a BIGINT, b BIGINT UNSIGNED); 456INSERT INTO t1 VALUES (1,1); 457INSERT INTO t1 VALUES (-9223372036854775808,0xFFFFFFFFFFFFFFFF); 458SELECT 459a AS ___________a, 460CASE WHEN a IS NOT NULL THEN a END AS case_______a, 461CASE WHEN a IS NOT NULL THEN a ELSE a END AS case_____a_a, 462COALESCE(a) AS coalesce___a, 463COALESCE(a, a) AS coalesce_a_a, 464IF(a IS NULL, a, a) AS if_______a_a, 465IFNULL(a, a) AS ifnull___a_a, 466LEAST(a, a) AS least____a_a, 467GREATEST(a, a) AS greatest_a_a, 468b AS ___________b, 469CASE WHEN a IS NOT NULL THEN b END AS case_______b, 470CASE WHEN a IS NOT NULL THEN b ELSE b END AS case_____b_b, 471COALESCE(b) AS coalesce___b, 472COALESCE(b, b) AS coalesce_b_b, 473IF(a IS NULL, b, b) AS if_______b_b, 474IFNULL(b, b) AS ifnull___b_b, 475LEAST(b, b) AS least____b_b, 476GREATEST(b, b) AS greatest_b_b 477FROM t1; 478Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr 479def test t1 t1 a ___________a 8 20 20 Y 32768 0 63 480def case_______a 8 20 20 Y 32896 0 63 481def case_____a_a 8 20 20 Y 32896 0 63 482def coalesce___a 8 20 20 Y 32896 0 63 483def coalesce_a_a 8 20 20 Y 32896 0 63 484def if_______a_a 8 20 20 Y 32896 0 63 485def ifnull___a_a 8 20 20 Y 32896 0 63 486def least____a_a 8 20 20 Y 32896 0 63 487def greatest_a_a 8 20 20 Y 32896 0 63 488def test t1 t1 b ___________b 8 20 20 Y 32800 0 63 489def case_______b 8 20 20 Y 32928 0 63 490def case_____b_b 8 20 20 Y 32928 0 63 491def coalesce___b 8 20 20 Y 32928 0 63 492def coalesce_b_b 8 20 20 Y 32928 0 63 493def if_______b_b 8 20 20 Y 32928 0 63 494def ifnull___b_b 8 20 20 Y 32928 0 63 495def least____b_b 8 20 20 Y 32928 0 63 496def greatest_b_b 8 20 20 Y 32928 0 63 497___________a 1 498case_______a 1 499case_____a_a 1 500coalesce___a 1 501coalesce_a_a 1 502if_______a_a 1 503ifnull___a_a 1 504least____a_a 1 505greatest_a_a 1 506___________b 1 507case_______b 1 508case_____b_b 1 509coalesce___b 1 510coalesce_b_b 1 511if_______b_b 1 512ifnull___b_b 1 513least____b_b 1 514greatest_b_b 1 515___________a -9223372036854775808 516case_______a -9223372036854775808 517case_____a_a -9223372036854775808 518coalesce___a -9223372036854775808 519coalesce_a_a -9223372036854775808 520if_______a_a -9223372036854775808 521ifnull___a_a -9223372036854775808 522least____a_a -9223372036854775808 523greatest_a_a -9223372036854775808 524___________b 18446744073709551615 525case_______b 18446744073709551615 526case_____b_b 18446744073709551615 527coalesce___b 18446744073709551615 528coalesce_b_b 18446744073709551615 529if_______b_b 18446744073709551615 530ifnull___b_b 18446744073709551615 531least____b_b 18446744073709551615 532greatest_b_b 18446744073709551615 533SELECT 534CASE WHEN a IS NOT NULL THEN a ELSE b END AS case_____a_b, 535CASE WHEN a IS NOT NULL THEN b ELSE a END AS case_____b_a, 536COALESCE(a, b) AS coalesce_a_b, 537COALESCE(b, a) AS coalesce_b_a, 538IF(a IS NULL, a, b) AS if_______a_b, 539IF(a IS NULL, b, a) AS if_______b_a, 540IFNULL(a, b) AS ifnull___a_b, 541IFNULL(b, a) AS ifnull___b_a, 542LEAST(a, b) AS least____a_b, 543LEAST(b, a) AS least____b_a, 544GREATEST(a, b) AS greatest_a_b, 545GREATEST(b, a) AS greatest_b_a 546FROM t1; 547Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr 548def case_____a_b 246 21 20 Y 32896 0 63 549def case_____b_a 246 21 20 Y 32896 0 63 550def coalesce_a_b 246 21 20 Y 32896 0 63 551def coalesce_b_a 246 21 20 Y 32896 0 63 552def if_______a_b 246 21 20 Y 32896 0 63 553def if_______b_a 246 21 20 Y 32896 0 63 554def ifnull___a_b 246 21 20 Y 32896 0 63 555def ifnull___b_a 246 21 20 Y 32896 0 63 556def least____a_b 246 21 20 Y 32896 0 63 557def least____b_a 246 21 20 Y 32896 0 63 558def greatest_a_b 246 21 20 Y 32896 0 63 559def greatest_b_a 246 21 20 Y 32896 0 63 560case_____a_b 1 561case_____b_a 1 562coalesce_a_b 1 563coalesce_b_a 1 564if_______a_b 1 565if_______b_a 1 566ifnull___a_b 1 567ifnull___b_a 1 568least____a_b 1 569least____b_a 1 570greatest_a_b 1 571greatest_b_a 1 572case_____a_b -9223372036854775808 573case_____b_a 18446744073709551615 574coalesce_a_b -9223372036854775808 575coalesce_b_a 18446744073709551615 576if_______a_b 18446744073709551615 577if_______b_a -9223372036854775808 578ifnull___a_b -9223372036854775808 579ifnull___b_a 18446744073709551615 580least____a_b -9223372036854775808 581least____b_a -9223372036854775808 582greatest_a_b 18446744073709551615 583greatest_b_a 18446744073709551615 584CREATE TABLE t2 AS 585SELECT 586a AS ___________a, 587CASE WHEN a IS NOT NULL THEN a END AS case_______a, 588CASE WHEN a IS NOT NULL THEN a ELSE a END AS case_____a_a, 589COALESCE(a) AS coalesce___a, 590COALESCE(a, a) AS coalesce_a_a, 591IF(a IS NULL, a, a) AS if_______a_a, 592IFNULL(a, a) AS ifnull___a_a, 593LEAST(a, a) AS least____a_a, 594GREATEST(a, a) AS greatest_a_a, 595b AS ___________b, 596CASE WHEN a IS NOT NULL THEN b END AS case_______b, 597CASE WHEN a IS NOT NULL THEN b ELSE b END AS case_____b_b, 598COALESCE(b) AS coalesce___b, 599COALESCE(b, b) AS coalesce_b_b, 600IF(a IS NULL, b, b) AS if_______b_b, 601IFNULL(b, b) AS ifnull___b_b, 602LEAST(b, b) AS least____b_b, 603GREATEST(b, b) AS greatest_b_b 604FROM t1; 605SHOW CREATE TABLE t2; 606Table Create Table 607t2 CREATE TABLE `t2` ( 608 `___________a` bigint(20) DEFAULT NULL, 609 `case_______a` bigint(20) DEFAULT NULL, 610 `case_____a_a` bigint(20) DEFAULT NULL, 611 `coalesce___a` bigint(20) DEFAULT NULL, 612 `coalesce_a_a` bigint(20) DEFAULT NULL, 613 `if_______a_a` bigint(20) DEFAULT NULL, 614 `ifnull___a_a` bigint(20) DEFAULT NULL, 615 `least____a_a` bigint(20) DEFAULT NULL, 616 `greatest_a_a` bigint(20) DEFAULT NULL, 617 `___________b` bigint(20) unsigned DEFAULT NULL, 618 `case_______b` bigint(20) unsigned DEFAULT NULL, 619 `case_____b_b` bigint(20) unsigned DEFAULT NULL, 620 `coalesce___b` bigint(20) unsigned DEFAULT NULL, 621 `coalesce_b_b` bigint(20) unsigned DEFAULT NULL, 622 `if_______b_b` bigint(20) unsigned DEFAULT NULL, 623 `ifnull___b_b` bigint(20) unsigned DEFAULT NULL, 624 `least____b_b` bigint(20) unsigned DEFAULT NULL, 625 `greatest_b_b` bigint(20) unsigned DEFAULT NULL 626) ENGINE=MyISAM DEFAULT CHARSET=latin1 627DROP TABLE t2; 628SET STATEMENT sql_mode = 'NO_ENGINE_SUBSTITUTION' FOR 629CREATE TABLE t2 AS 630SELECT 631CASE WHEN a IS NOT NULL THEN a ELSE b END AS case_____a_b, 632CASE WHEN a IS NOT NULL THEN b ELSE a END AS case_____b_a, 633COALESCE(a, b) AS coalesce_a_b, 634COALESCE(b, a) AS coalesce_b_a, 635IF(a IS NULL, a, b) AS if_______a_b, 636IF(a IS NULL, b, a) AS if_______b_a, 637IFNULL(a, b) AS ifnull___a_b, 638IFNULL(b, a) AS ifnull___b_a, 639LEAST(a, b) AS least____a_b, 640LEAST(b, a) AS least____b_a, 641GREATEST(a, b) AS greatest_a_b, 642GREATEST(b, a) AS greatest_b_a 643FROM t1; 644SHOW CREATE TABLE t2; 645Table Create Table 646t2 CREATE TABLE `t2` ( 647 `case_____a_b` decimal(20,0) DEFAULT NULL, 648 `case_____b_a` decimal(20,0) DEFAULT NULL, 649 `coalesce_a_b` decimal(20,0) DEFAULT NULL, 650 `coalesce_b_a` decimal(20,0) DEFAULT NULL, 651 `if_______a_b` decimal(20,0) DEFAULT NULL, 652 `if_______b_a` decimal(20,0) DEFAULT NULL, 653 `ifnull___a_b` decimal(20,0) DEFAULT NULL, 654 `ifnull___b_a` decimal(20,0) DEFAULT NULL, 655 `least____a_b` decimal(20,0) DEFAULT NULL, 656 `least____b_a` decimal(20,0) DEFAULT NULL, 657 `greatest_a_b` decimal(20,0) DEFAULT NULL, 658 `greatest_b_a` decimal(20,0) DEFAULT NULL 659) ENGINE=MyISAM DEFAULT CHARSET=latin1 660DROP TABLE t2; 661DROP TABLE t1; 662# 663CREATE TABLE t1 (a BIGINT, b BIGINT); 664INSERT INTO t1 VALUES (1,1); 665INSERT INTO t1 VALUES (-9223372036854775808,9223372036854775807); 666SELECT 667a AS ___________a, 668CASE WHEN a IS NOT NULL THEN a END AS case_______a, 669CASE WHEN a IS NOT NULL THEN a ELSE a END AS case_____a_a, 670COALESCE(a) AS coalesce___a, 671COALESCE(a, a) AS coalesce_a_a, 672IF(a IS NULL, a, a) AS if_______a_a, 673IFNULL(a, a) AS ifnull___a_a, 674LEAST(a, a) AS least____a_a, 675GREATEST(a, a) AS greatest_a_a, 676b AS ___________b, 677CASE WHEN a IS NOT NULL THEN b END AS case_______b, 678CASE WHEN a IS NOT NULL THEN b ELSE b END AS case_____b_b, 679COALESCE(b) AS coalesce___b, 680COALESCE(b, b) AS coalesce_b_b, 681IF(a IS NULL, b, b) AS if_______b_b, 682IFNULL(b, b) AS ifnull___b_b, 683LEAST(b, b) AS least____b_b, 684GREATEST(b, b) AS greatest_b_b 685FROM t1; 686Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr 687def test t1 t1 a ___________a 8 20 20 Y 32768 0 63 688def case_______a 8 20 20 Y 32896 0 63 689def case_____a_a 8 20 20 Y 32896 0 63 690def coalesce___a 8 20 20 Y 32896 0 63 691def coalesce_a_a 8 20 20 Y 32896 0 63 692def if_______a_a 8 20 20 Y 32896 0 63 693def ifnull___a_a 8 20 20 Y 32896 0 63 694def least____a_a 8 20 20 Y 32896 0 63 695def greatest_a_a 8 20 20 Y 32896 0 63 696def test t1 t1 b ___________b 8 20 19 Y 32768 0 63 697def case_______b 8 20 19 Y 32896 0 63 698def case_____b_b 8 20 19 Y 32896 0 63 699def coalesce___b 8 20 19 Y 32896 0 63 700def coalesce_b_b 8 20 19 Y 32896 0 63 701def if_______b_b 8 20 19 Y 32896 0 63 702def ifnull___b_b 8 20 19 Y 32896 0 63 703def least____b_b 8 20 19 Y 32896 0 63 704def greatest_b_b 8 20 19 Y 32896 0 63 705___________a 1 706case_______a 1 707case_____a_a 1 708coalesce___a 1 709coalesce_a_a 1 710if_______a_a 1 711ifnull___a_a 1 712least____a_a 1 713greatest_a_a 1 714___________b 1 715case_______b 1 716case_____b_b 1 717coalesce___b 1 718coalesce_b_b 1 719if_______b_b 1 720ifnull___b_b 1 721least____b_b 1 722greatest_b_b 1 723___________a -9223372036854775808 724case_______a -9223372036854775808 725case_____a_a -9223372036854775808 726coalesce___a -9223372036854775808 727coalesce_a_a -9223372036854775808 728if_______a_a -9223372036854775808 729ifnull___a_a -9223372036854775808 730least____a_a -9223372036854775808 731greatest_a_a -9223372036854775808 732___________b 9223372036854775807 733case_______b 9223372036854775807 734case_____b_b 9223372036854775807 735coalesce___b 9223372036854775807 736coalesce_b_b 9223372036854775807 737if_______b_b 9223372036854775807 738ifnull___b_b 9223372036854775807 739least____b_b 9223372036854775807 740greatest_b_b 9223372036854775807 741SELECT 742CASE WHEN a IS NOT NULL THEN a ELSE b END AS case_____a_b, 743CASE WHEN a IS NOT NULL THEN b ELSE a END AS case_____b_a, 744COALESCE(a, b) AS coalesce_a_b, 745COALESCE(b, a) AS coalesce_b_a, 746IF(a IS NULL, a, b) AS if_______a_b, 747IF(a IS NULL, b, a) AS if_______b_a, 748IFNULL(a, b) AS ifnull___a_b, 749IFNULL(b, a) AS ifnull___b_a, 750LEAST(a, b) AS least____a_b, 751LEAST(b, a) AS least____b_a, 752GREATEST(a, b) AS greatest_a_b, 753GREATEST(b, a) AS greatest_b_a 754FROM t1; 755Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr 756def case_____a_b 8 20 20 Y 32896 0 63 757def case_____b_a 8 20 19 Y 32896 0 63 758def coalesce_a_b 8 20 20 Y 32896 0 63 759def coalesce_b_a 8 20 19 Y 32896 0 63 760def if_______a_b 8 20 19 Y 32896 0 63 761def if_______b_a 8 20 20 Y 32896 0 63 762def ifnull___a_b 8 20 20 Y 32896 0 63 763def ifnull___b_a 8 20 19 Y 32896 0 63 764def least____a_b 8 20 20 Y 32896 0 63 765def least____b_a 8 20 20 Y 32896 0 63 766def greatest_a_b 8 20 19 Y 32896 0 63 767def greatest_b_a 8 20 19 Y 32896 0 63 768case_____a_b 1 769case_____b_a 1 770coalesce_a_b 1 771coalesce_b_a 1 772if_______a_b 1 773if_______b_a 1 774ifnull___a_b 1 775ifnull___b_a 1 776least____a_b 1 777least____b_a 1 778greatest_a_b 1 779greatest_b_a 1 780case_____a_b -9223372036854775808 781case_____b_a 9223372036854775807 782coalesce_a_b -9223372036854775808 783coalesce_b_a 9223372036854775807 784if_______a_b 9223372036854775807 785if_______b_a -9223372036854775808 786ifnull___a_b -9223372036854775808 787ifnull___b_a 9223372036854775807 788least____a_b -9223372036854775808 789least____b_a -9223372036854775808 790greatest_a_b 9223372036854775807 791greatest_b_a 9223372036854775807 792CREATE TABLE t2 AS 793SELECT 794a AS ___________a, 795CASE WHEN a IS NOT NULL THEN a END AS case_______a, 796CASE WHEN a IS NOT NULL THEN a ELSE a END AS case_____a_a, 797COALESCE(a) AS coalesce___a, 798COALESCE(a, a) AS coalesce_a_a, 799IF(a IS NULL, a, a) AS if_______a_a, 800IFNULL(a, a) AS ifnull___a_a, 801LEAST(a, a) AS least____a_a, 802GREATEST(a, a) AS greatest_a_a, 803b AS ___________b, 804CASE WHEN a IS NOT NULL THEN b END AS case_______b, 805CASE WHEN a IS NOT NULL THEN b ELSE b END AS case_____b_b, 806COALESCE(b) AS coalesce___b, 807COALESCE(b, b) AS coalesce_b_b, 808IF(a IS NULL, b, b) AS if_______b_b, 809IFNULL(b, b) AS ifnull___b_b, 810LEAST(b, b) AS least____b_b, 811GREATEST(b, b) AS greatest_b_b 812FROM t1; 813SHOW CREATE TABLE t2; 814Table Create Table 815t2 CREATE TABLE `t2` ( 816 `___________a` bigint(20) DEFAULT NULL, 817 `case_______a` bigint(20) DEFAULT NULL, 818 `case_____a_a` bigint(20) DEFAULT NULL, 819 `coalesce___a` bigint(20) DEFAULT NULL, 820 `coalesce_a_a` bigint(20) DEFAULT NULL, 821 `if_______a_a` bigint(20) DEFAULT NULL, 822 `ifnull___a_a` bigint(20) DEFAULT NULL, 823 `least____a_a` bigint(20) DEFAULT NULL, 824 `greatest_a_a` bigint(20) DEFAULT NULL, 825 `___________b` bigint(20) DEFAULT NULL, 826 `case_______b` bigint(20) DEFAULT NULL, 827 `case_____b_b` bigint(20) DEFAULT NULL, 828 `coalesce___b` bigint(20) DEFAULT NULL, 829 `coalesce_b_b` bigint(20) DEFAULT NULL, 830 `if_______b_b` bigint(20) DEFAULT NULL, 831 `ifnull___b_b` bigint(20) DEFAULT NULL, 832 `least____b_b` bigint(20) DEFAULT NULL, 833 `greatest_b_b` bigint(20) DEFAULT NULL 834) ENGINE=MyISAM DEFAULT CHARSET=latin1 835DROP TABLE t2; 836SET STATEMENT sql_mode = 'NO_ENGINE_SUBSTITUTION' FOR 837CREATE TABLE t2 AS 838SELECT 839CASE WHEN a IS NOT NULL THEN a ELSE b END AS case_____a_b, 840CASE WHEN a IS NOT NULL THEN b ELSE a END AS case_____b_a, 841COALESCE(a, b) AS coalesce_a_b, 842COALESCE(b, a) AS coalesce_b_a, 843IF(a IS NULL, a, b) AS if_______a_b, 844IF(a IS NULL, b, a) AS if_______b_a, 845IFNULL(a, b) AS ifnull___a_b, 846IFNULL(b, a) AS ifnull___b_a, 847LEAST(a, b) AS least____a_b, 848LEAST(b, a) AS least____b_a, 849GREATEST(a, b) AS greatest_a_b, 850GREATEST(b, a) AS greatest_b_a 851FROM t1; 852SHOW CREATE TABLE t2; 853Table Create Table 854t2 CREATE TABLE `t2` ( 855 `case_____a_b` bigint(20) DEFAULT NULL, 856 `case_____b_a` bigint(20) DEFAULT NULL, 857 `coalesce_a_b` bigint(20) DEFAULT NULL, 858 `coalesce_b_a` bigint(20) DEFAULT NULL, 859 `if_______a_b` bigint(20) DEFAULT NULL, 860 `if_______b_a` bigint(20) DEFAULT NULL, 861 `ifnull___a_b` bigint(20) DEFAULT NULL, 862 `ifnull___b_a` bigint(20) DEFAULT NULL, 863 `least____a_b` bigint(20) DEFAULT NULL, 864 `least____b_a` bigint(20) DEFAULT NULL, 865 `greatest_a_b` bigint(20) DEFAULT NULL, 866 `greatest_b_a` bigint(20) DEFAULT NULL 867) ENGINE=MyISAM DEFAULT CHARSET=latin1 868DROP TABLE t2; 869DROP TABLE t1; 870# 871CREATE TABLE t1 (a INT, b BIT(8)); 872INSERT INTO t1 VALUES (-2147483648,0x32); 873SELECT 874a AS ___________a, 875CASE WHEN a IS NOT NULL THEN a END AS case_______a, 876CASE WHEN a IS NOT NULL THEN a ELSE a END AS case_____a_a, 877COALESCE(a) AS coalesce___a, 878COALESCE(a, a) AS coalesce_a_a, 879IF(a IS NULL, a, a) AS if_______a_a, 880IFNULL(a, a) AS ifnull___a_a, 881LEAST(a, a) AS least____a_a, 882GREATEST(a, a) AS greatest_a_a, 883b AS ___________b, 884CASE WHEN a IS NOT NULL THEN b END AS case_______b, 885CASE WHEN a IS NOT NULL THEN b ELSE b END AS case_____b_b, 886COALESCE(b) AS coalesce___b, 887COALESCE(b, b) AS coalesce_b_b, 888IF(a IS NULL, b, b) AS if_______b_b, 889IFNULL(b, b) AS ifnull___b_b, 890LEAST(b, b) AS least____b_b, 891GREATEST(b, b) AS greatest_b_b 892FROM t1; 893Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr 894def test t1 t1 a ___________a 3 11 11 Y 32768 0 63 895def case_______a 3 11 11 Y 32896 0 63 896def case_____a_a 3 11 11 Y 32896 0 63 897def coalesce___a 3 11 11 Y 32896 0 63 898def coalesce_a_a 3 11 11 Y 32896 0 63 899def if_______a_a 3 11 11 Y 32896 0 63 900def ifnull___a_a 3 11 11 Y 32896 0 63 901def least____a_a 3 11 11 Y 32896 0 63 902def greatest_a_a 3 11 11 Y 32896 0 63 903def test t1 t1 b ___________b 16 8 1 Y 32 0 63 904def case_______b 16 8 2 Y 160 0 63 905def case_____b_b 16 8 2 Y 160 0 63 906def coalesce___b 16 8 2 Y 160 0 63 907def coalesce_b_b 16 8 2 Y 160 0 63 908def if_______b_b 16 8 2 Y 160 0 63 909def ifnull___b_b 16 8 2 Y 160 0 63 910def least____b_b 16 8 2 Y 160 0 63 911def greatest_b_b 16 8 2 Y 160 0 63 912___________a -2147483648 913case_______a -2147483648 914case_____a_a -2147483648 915coalesce___a -2147483648 916coalesce_a_a -2147483648 917if_______a_a -2147483648 918ifnull___a_a -2147483648 919least____a_a -2147483648 920greatest_a_a -2147483648 921___________b 2 922case_______b 50 923case_____b_b 50 924coalesce___b 50 925coalesce_b_b 50 926if_______b_b 50 927ifnull___b_b 50 928least____b_b 50 929greatest_b_b 50 930SELECT 931CASE WHEN a IS NOT NULL THEN a ELSE b END AS case_____a_b, 932CASE WHEN a IS NOT NULL THEN b ELSE a END AS case_____b_a, 933COALESCE(a, b) AS coalesce_a_b, 934COALESCE(b, a) AS coalesce_b_a, 935IF(a IS NULL, a, b) AS if_______a_b, 936IF(a IS NULL, b, a) AS if_______b_a, 937IFNULL(a, b) AS ifnull___a_b, 938IFNULL(b, a) AS ifnull___b_a, 939LEAST(a, b) AS least____a_b, 940LEAST(b, a) AS least____b_a, 941GREATEST(a, b) AS greatest_a_b, 942GREATEST(b, a) AS greatest_b_a 943FROM t1; 944Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr 945def case_____a_b 246 11 11 Y 32896 0 63 946def case_____b_a 246 11 2 Y 32896 0 63 947def coalesce_a_b 246 11 11 Y 32896 0 63 948def coalesce_b_a 246 11 2 Y 32896 0 63 949def if_______a_b 246 11 2 Y 32896 0 63 950def if_______b_a 246 11 11 Y 32896 0 63 951def ifnull___a_b 246 11 11 Y 32896 0 63 952def ifnull___b_a 246 11 2 Y 32896 0 63 953def least____a_b 246 11 11 Y 32896 0 63 954def least____b_a 246 11 11 Y 32896 0 63 955def greatest_a_b 246 11 2 Y 32896 0 63 956def greatest_b_a 246 11 2 Y 32896 0 63 957case_____a_b -2147483648 958case_____b_a 50 959coalesce_a_b -2147483648 960coalesce_b_a 50 961if_______a_b 50 962if_______b_a -2147483648 963ifnull___a_b -2147483648 964ifnull___b_a 50 965least____a_b -2147483648 966least____b_a -2147483648 967greatest_a_b 50 968greatest_b_a 50 969CREATE TABLE t2 AS 970SELECT 971a AS ___________a, 972CASE WHEN a IS NOT NULL THEN a END AS case_______a, 973CASE WHEN a IS NOT NULL THEN a ELSE a END AS case_____a_a, 974COALESCE(a) AS coalesce___a, 975COALESCE(a, a) AS coalesce_a_a, 976IF(a IS NULL, a, a) AS if_______a_a, 977IFNULL(a, a) AS ifnull___a_a, 978LEAST(a, a) AS least____a_a, 979GREATEST(a, a) AS greatest_a_a, 980b AS ___________b, 981CASE WHEN a IS NOT NULL THEN b END AS case_______b, 982CASE WHEN a IS NOT NULL THEN b ELSE b END AS case_____b_b, 983COALESCE(b) AS coalesce___b, 984COALESCE(b, b) AS coalesce_b_b, 985IF(a IS NULL, b, b) AS if_______b_b, 986IFNULL(b, b) AS ifnull___b_b, 987LEAST(b, b) AS least____b_b, 988GREATEST(b, b) AS greatest_b_b 989FROM t1; 990SHOW CREATE TABLE t2; 991Table Create Table 992t2 CREATE TABLE `t2` ( 993 `___________a` int(11) DEFAULT NULL, 994 `case_______a` int(11) DEFAULT NULL, 995 `case_____a_a` int(11) DEFAULT NULL, 996 `coalesce___a` int(11) DEFAULT NULL, 997 `coalesce_a_a` int(11) DEFAULT NULL, 998 `if_______a_a` int(11) DEFAULT NULL, 999 `ifnull___a_a` int(11) DEFAULT NULL, 1000 `least____a_a` int(11) DEFAULT NULL, 1001 `greatest_a_a` int(11) DEFAULT NULL, 1002 `___________b` bit(8) DEFAULT NULL, 1003 `case_______b` bit(8) DEFAULT NULL, 1004 `case_____b_b` bit(8) DEFAULT NULL, 1005 `coalesce___b` bit(8) DEFAULT NULL, 1006 `coalesce_b_b` bit(8) DEFAULT NULL, 1007 `if_______b_b` bit(8) DEFAULT NULL, 1008 `ifnull___b_b` bit(8) DEFAULT NULL, 1009 `least____b_b` bit(8) DEFAULT NULL, 1010 `greatest_b_b` bit(8) DEFAULT NULL 1011) ENGINE=MyISAM DEFAULT CHARSET=latin1 1012DROP TABLE t2; 1013SET STATEMENT sql_mode = 'NO_ENGINE_SUBSTITUTION' FOR 1014CREATE TABLE t2 AS 1015SELECT 1016CASE WHEN a IS NOT NULL THEN a ELSE b END AS case_____a_b, 1017CASE WHEN a IS NOT NULL THEN b ELSE a END AS case_____b_a, 1018COALESCE(a, b) AS coalesce_a_b, 1019COALESCE(b, a) AS coalesce_b_a, 1020IF(a IS NULL, a, b) AS if_______a_b, 1021IF(a IS NULL, b, a) AS if_______b_a, 1022IFNULL(a, b) AS ifnull___a_b, 1023IFNULL(b, a) AS ifnull___b_a, 1024LEAST(a, b) AS least____a_b, 1025LEAST(b, a) AS least____b_a, 1026GREATEST(a, b) AS greatest_a_b, 1027GREATEST(b, a) AS greatest_b_a 1028FROM t1; 1029SHOW CREATE TABLE t2; 1030Table Create Table 1031t2 CREATE TABLE `t2` ( 1032 `case_____a_b` decimal(10,0) DEFAULT NULL, 1033 `case_____b_a` decimal(10,0) DEFAULT NULL, 1034 `coalesce_a_b` decimal(10,0) DEFAULT NULL, 1035 `coalesce_b_a` decimal(10,0) DEFAULT NULL, 1036 `if_______a_b` decimal(10,0) DEFAULT NULL, 1037 `if_______b_a` decimal(10,0) DEFAULT NULL, 1038 `ifnull___a_b` decimal(10,0) DEFAULT NULL, 1039 `ifnull___b_a` decimal(10,0) DEFAULT NULL, 1040 `least____a_b` decimal(10,0) DEFAULT NULL, 1041 `least____b_a` decimal(10,0) DEFAULT NULL, 1042 `greatest_a_b` decimal(10,0) DEFAULT NULL, 1043 `greatest_b_a` decimal(10,0) DEFAULT NULL 1044) ENGINE=MyISAM DEFAULT CHARSET=latin1 1045DROP TABLE t2; 1046DROP TABLE t1; 1047# 1048CREATE TABLE t1 (a INT UNSIGNED, b BIT(8)); 1049INSERT INTO t1 VALUES (4294967295,0x32); 1050SELECT 1051a AS ___________a, 1052CASE WHEN a IS NOT NULL THEN a END AS case_______a, 1053CASE WHEN a IS NOT NULL THEN a ELSE a END AS case_____a_a, 1054COALESCE(a) AS coalesce___a, 1055COALESCE(a, a) AS coalesce_a_a, 1056IF(a IS NULL, a, a) AS if_______a_a, 1057IFNULL(a, a) AS ifnull___a_a, 1058LEAST(a, a) AS least____a_a, 1059GREATEST(a, a) AS greatest_a_a, 1060b AS ___________b, 1061CASE WHEN a IS NOT NULL THEN b END AS case_______b, 1062CASE WHEN a IS NOT NULL THEN b ELSE b END AS case_____b_b, 1063COALESCE(b) AS coalesce___b, 1064COALESCE(b, b) AS coalesce_b_b, 1065IF(a IS NULL, b, b) AS if_______b_b, 1066IFNULL(b, b) AS ifnull___b_b, 1067LEAST(b, b) AS least____b_b, 1068GREATEST(b, b) AS greatest_b_b 1069FROM t1; 1070Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr 1071def test t1 t1 a ___________a 3 10 10 Y 32800 0 63 1072def case_______a 3 10 10 Y 32928 0 63 1073def case_____a_a 3 10 10 Y 32928 0 63 1074def coalesce___a 3 10 10 Y 32928 0 63 1075def coalesce_a_a 3 10 10 Y 32928 0 63 1076def if_______a_a 3 10 10 Y 32928 0 63 1077def ifnull___a_a 3 10 10 Y 32928 0 63 1078def least____a_a 3 10 10 Y 32928 0 63 1079def greatest_a_a 3 10 10 Y 32928 0 63 1080def test t1 t1 b ___________b 16 8 1 Y 32 0 63 1081def case_______b 16 8 2 Y 160 0 63 1082def case_____b_b 16 8 2 Y 160 0 63 1083def coalesce___b 16 8 2 Y 160 0 63 1084def coalesce_b_b 16 8 2 Y 160 0 63 1085def if_______b_b 16 8 2 Y 160 0 63 1086def ifnull___b_b 16 8 2 Y 160 0 63 1087def least____b_b 16 8 2 Y 160 0 63 1088def greatest_b_b 16 8 2 Y 160 0 63 1089___________a 4294967295 1090case_______a 4294967295 1091case_____a_a 4294967295 1092coalesce___a 4294967295 1093coalesce_a_a 4294967295 1094if_______a_a 4294967295 1095ifnull___a_a 4294967295 1096least____a_a 4294967295 1097greatest_a_a 4294967295 1098___________b 2 1099case_______b 50 1100case_____b_b 50 1101coalesce___b 50 1102coalesce_b_b 50 1103if_______b_b 50 1104ifnull___b_b 50 1105least____b_b 50 1106greatest_b_b 50 1107SELECT 1108CASE WHEN a IS NOT NULL THEN a ELSE b END AS case_____a_b, 1109CASE WHEN a IS NOT NULL THEN b ELSE a END AS case_____b_a, 1110COALESCE(a, b) AS coalesce_a_b, 1111COALESCE(b, a) AS coalesce_b_a, 1112IF(a IS NULL, a, b) AS if_______a_b, 1113IF(a IS NULL, b, a) AS if_______b_a, 1114IFNULL(a, b) AS ifnull___a_b, 1115IFNULL(b, a) AS ifnull___b_a, 1116LEAST(a, b) AS least____a_b, 1117LEAST(b, a) AS least____b_a, 1118GREATEST(a, b) AS greatest_a_b, 1119GREATEST(b, a) AS greatest_b_a 1120FROM t1; 1121Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr 1122def case_____a_b 3 10 10 Y 32928 0 63 1123def case_____b_a 3 10 2 Y 32928 0 63 1124def coalesce_a_b 3 10 10 Y 32928 0 63 1125def coalesce_b_a 3 10 2 Y 32928 0 63 1126def if_______a_b 3 10 2 Y 32928 0 63 1127def if_______b_a 3 10 10 Y 32928 0 63 1128def ifnull___a_b 3 10 10 Y 32928 0 63 1129def ifnull___b_a 3 10 2 Y 32928 0 63 1130def least____a_b 3 10 2 Y 32928 0 63 1131def least____b_a 3 10 2 Y 32928 0 63 1132def greatest_a_b 3 10 10 Y 32928 0 63 1133def greatest_b_a 3 10 10 Y 32928 0 63 1134case_____a_b 4294967295 1135case_____b_a 50 1136coalesce_a_b 4294967295 1137coalesce_b_a 50 1138if_______a_b 50 1139if_______b_a 4294967295 1140ifnull___a_b 4294967295 1141ifnull___b_a 50 1142least____a_b 50 1143least____b_a 50 1144greatest_a_b 4294967295 1145greatest_b_a 4294967295 1146CREATE TABLE t2 AS 1147SELECT 1148a AS ___________a, 1149CASE WHEN a IS NOT NULL THEN a END AS case_______a, 1150CASE WHEN a IS NOT NULL THEN a ELSE a END AS case_____a_a, 1151COALESCE(a) AS coalesce___a, 1152COALESCE(a, a) AS coalesce_a_a, 1153IF(a IS NULL, a, a) AS if_______a_a, 1154IFNULL(a, a) AS ifnull___a_a, 1155LEAST(a, a) AS least____a_a, 1156GREATEST(a, a) AS greatest_a_a, 1157b AS ___________b, 1158CASE WHEN a IS NOT NULL THEN b END AS case_______b, 1159CASE WHEN a IS NOT NULL THEN b ELSE b END AS case_____b_b, 1160COALESCE(b) AS coalesce___b, 1161COALESCE(b, b) AS coalesce_b_b, 1162IF(a IS NULL, b, b) AS if_______b_b, 1163IFNULL(b, b) AS ifnull___b_b, 1164LEAST(b, b) AS least____b_b, 1165GREATEST(b, b) AS greatest_b_b 1166FROM t1; 1167SHOW CREATE TABLE t2; 1168Table Create Table 1169t2 CREATE TABLE `t2` ( 1170 `___________a` int(10) unsigned DEFAULT NULL, 1171 `case_______a` int(10) unsigned DEFAULT NULL, 1172 `case_____a_a` int(10) unsigned DEFAULT NULL, 1173 `coalesce___a` int(10) unsigned DEFAULT NULL, 1174 `coalesce_a_a` int(10) unsigned DEFAULT NULL, 1175 `if_______a_a` int(10) unsigned DEFAULT NULL, 1176 `ifnull___a_a` int(10) unsigned DEFAULT NULL, 1177 `least____a_a` int(10) unsigned DEFAULT NULL, 1178 `greatest_a_a` int(10) unsigned DEFAULT NULL, 1179 `___________b` bit(8) DEFAULT NULL, 1180 `case_______b` bit(8) DEFAULT NULL, 1181 `case_____b_b` bit(8) DEFAULT NULL, 1182 `coalesce___b` bit(8) DEFAULT NULL, 1183 `coalesce_b_b` bit(8) DEFAULT NULL, 1184 `if_______b_b` bit(8) DEFAULT NULL, 1185 `ifnull___b_b` bit(8) DEFAULT NULL, 1186 `least____b_b` bit(8) DEFAULT NULL, 1187 `greatest_b_b` bit(8) DEFAULT NULL 1188) ENGINE=MyISAM DEFAULT CHARSET=latin1 1189DROP TABLE t2; 1190SET STATEMENT sql_mode = 'NO_ENGINE_SUBSTITUTION' FOR 1191CREATE TABLE t2 AS 1192SELECT 1193CASE WHEN a IS NOT NULL THEN a ELSE b END AS case_____a_b, 1194CASE WHEN a IS NOT NULL THEN b ELSE a END AS case_____b_a, 1195COALESCE(a, b) AS coalesce_a_b, 1196COALESCE(b, a) AS coalesce_b_a, 1197IF(a IS NULL, a, b) AS if_______a_b, 1198IF(a IS NULL, b, a) AS if_______b_a, 1199IFNULL(a, b) AS ifnull___a_b, 1200IFNULL(b, a) AS ifnull___b_a, 1201LEAST(a, b) AS least____a_b, 1202LEAST(b, a) AS least____b_a, 1203GREATEST(a, b) AS greatest_a_b, 1204GREATEST(b, a) AS greatest_b_a 1205FROM t1; 1206SHOW CREATE TABLE t2; 1207Table Create Table 1208t2 CREATE TABLE `t2` ( 1209 `case_____a_b` int(10) unsigned DEFAULT NULL, 1210 `case_____b_a` int(10) unsigned DEFAULT NULL, 1211 `coalesce_a_b` int(10) unsigned DEFAULT NULL, 1212 `coalesce_b_a` int(10) unsigned DEFAULT NULL, 1213 `if_______a_b` int(10) unsigned DEFAULT NULL, 1214 `if_______b_a` int(10) unsigned DEFAULT NULL, 1215 `ifnull___a_b` int(10) unsigned DEFAULT NULL, 1216 `ifnull___b_a` int(10) unsigned DEFAULT NULL, 1217 `least____a_b` int(10) unsigned DEFAULT NULL, 1218 `least____b_a` int(10) unsigned DEFAULT NULL, 1219 `greatest_a_b` int(10) unsigned DEFAULT NULL, 1220 `greatest_b_a` int(10) unsigned DEFAULT NULL 1221) ENGINE=MyISAM DEFAULT CHARSET=latin1 1222DROP TABLE t2; 1223DROP TABLE t1; 1224# 1225CREATE TABLE t1 (a BIT(7), b BIT(8)); 1226INSERT INTO t1 VALUES (0x32,0x32); 1227SELECT 1228a AS ___________a, 1229CASE WHEN a IS NOT NULL THEN a END AS case_______a, 1230CASE WHEN a IS NOT NULL THEN a ELSE a END AS case_____a_a, 1231COALESCE(a) AS coalesce___a, 1232COALESCE(a, a) AS coalesce_a_a, 1233IF(a IS NULL, a, a) AS if_______a_a, 1234IFNULL(a, a) AS ifnull___a_a, 1235LEAST(a, a) AS least____a_a, 1236GREATEST(a, a) AS greatest_a_a, 1237b AS ___________b, 1238CASE WHEN a IS NOT NULL THEN b END AS case_______b, 1239CASE WHEN a IS NOT NULL THEN b ELSE b END AS case_____b_b, 1240COALESCE(b) AS coalesce___b, 1241COALESCE(b, b) AS coalesce_b_b, 1242IF(a IS NULL, b, b) AS if_______b_b, 1243IFNULL(b, b) AS ifnull___b_b, 1244LEAST(b, b) AS least____b_b, 1245GREATEST(b, b) AS greatest_b_b 1246FROM t1; 1247Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr 1248def test t1 t1 a ___________a 16 7 1 Y 32 0 63 1249def case_______a 16 7 2 Y 160 0 63 1250def case_____a_a 16 7 2 Y 160 0 63 1251def coalesce___a 16 7 2 Y 160 0 63 1252def coalesce_a_a 16 7 2 Y 160 0 63 1253def if_______a_a 16 7 2 Y 160 0 63 1254def ifnull___a_a 16 7 2 Y 160 0 63 1255def least____a_a 16 7 2 Y 160 0 63 1256def greatest_a_a 16 7 2 Y 160 0 63 1257def test t1 t1 b ___________b 16 8 1 Y 32 0 63 1258def case_______b 16 8 2 Y 160 0 63 1259def case_____b_b 16 8 2 Y 160 0 63 1260def coalesce___b 16 8 2 Y 160 0 63 1261def coalesce_b_b 16 8 2 Y 160 0 63 1262def if_______b_b 16 8 2 Y 160 0 63 1263def ifnull___b_b 16 8 2 Y 160 0 63 1264def least____b_b 16 8 2 Y 160 0 63 1265def greatest_b_b 16 8 2 Y 160 0 63 1266___________a 2 1267case_______a 50 1268case_____a_a 50 1269coalesce___a 50 1270coalesce_a_a 50 1271if_______a_a 50 1272ifnull___a_a 50 1273least____a_a 50 1274greatest_a_a 50 1275___________b 2 1276case_______b 50 1277case_____b_b 50 1278coalesce___b 50 1279coalesce_b_b 50 1280if_______b_b 50 1281ifnull___b_b 50 1282least____b_b 50 1283greatest_b_b 50 1284SELECT 1285CASE WHEN a IS NOT NULL THEN a ELSE b END AS case_____a_b, 1286CASE WHEN a IS NOT NULL THEN b ELSE a END AS case_____b_a, 1287COALESCE(a, b) AS coalesce_a_b, 1288COALESCE(b, a) AS coalesce_b_a, 1289IF(a IS NULL, a, b) AS if_______a_b, 1290IF(a IS NULL, b, a) AS if_______b_a, 1291IFNULL(a, b) AS ifnull___a_b, 1292IFNULL(b, a) AS ifnull___b_a, 1293LEAST(a, b) AS least____a_b, 1294LEAST(b, a) AS least____b_a, 1295GREATEST(a, b) AS greatest_a_b, 1296GREATEST(b, a) AS greatest_b_a 1297FROM t1; 1298Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr 1299def case_____a_b 16 8 2 Y 160 0 63 1300def case_____b_a 16 8 2 Y 160 0 63 1301def coalesce_a_b 16 8 2 Y 160 0 63 1302def coalesce_b_a 16 8 2 Y 160 0 63 1303def if_______a_b 16 8 2 Y 160 0 63 1304def if_______b_a 16 8 2 Y 160 0 63 1305def ifnull___a_b 16 8 2 Y 160 0 63 1306def ifnull___b_a 16 8 2 Y 160 0 63 1307def least____a_b 16 8 2 Y 160 0 63 1308def least____b_a 16 8 2 Y 160 0 63 1309def greatest_a_b 16 8 2 Y 160 0 63 1310def greatest_b_a 16 8 2 Y 160 0 63 1311case_____a_b 50 1312case_____b_a 50 1313coalesce_a_b 50 1314coalesce_b_a 50 1315if_______a_b 50 1316if_______b_a 50 1317ifnull___a_b 50 1318ifnull___b_a 50 1319least____a_b 50 1320least____b_a 50 1321greatest_a_b 50 1322greatest_b_a 50 1323CREATE TABLE t2 AS 1324SELECT 1325a AS ___________a, 1326CASE WHEN a IS NOT NULL THEN a END AS case_______a, 1327CASE WHEN a IS NOT NULL THEN a ELSE a END AS case_____a_a, 1328COALESCE(a) AS coalesce___a, 1329COALESCE(a, a) AS coalesce_a_a, 1330IF(a IS NULL, a, a) AS if_______a_a, 1331IFNULL(a, a) AS ifnull___a_a, 1332LEAST(a, a) AS least____a_a, 1333GREATEST(a, a) AS greatest_a_a, 1334b AS ___________b, 1335CASE WHEN a IS NOT NULL THEN b END AS case_______b, 1336CASE WHEN a IS NOT NULL THEN b ELSE b END AS case_____b_b, 1337COALESCE(b) AS coalesce___b, 1338COALESCE(b, b) AS coalesce_b_b, 1339IF(a IS NULL, b, b) AS if_______b_b, 1340IFNULL(b, b) AS ifnull___b_b, 1341LEAST(b, b) AS least____b_b, 1342GREATEST(b, b) AS greatest_b_b 1343FROM t1; 1344SHOW CREATE TABLE t2; 1345Table Create Table 1346t2 CREATE TABLE `t2` ( 1347 `___________a` bit(7) DEFAULT NULL, 1348 `case_______a` bit(7) DEFAULT NULL, 1349 `case_____a_a` bit(7) DEFAULT NULL, 1350 `coalesce___a` bit(7) DEFAULT NULL, 1351 `coalesce_a_a` bit(7) DEFAULT NULL, 1352 `if_______a_a` bit(7) DEFAULT NULL, 1353 `ifnull___a_a` bit(7) DEFAULT NULL, 1354 `least____a_a` bit(7) DEFAULT NULL, 1355 `greatest_a_a` bit(7) DEFAULT NULL, 1356 `___________b` bit(8) DEFAULT NULL, 1357 `case_______b` bit(8) DEFAULT NULL, 1358 `case_____b_b` bit(8) DEFAULT NULL, 1359 `coalesce___b` bit(8) DEFAULT NULL, 1360 `coalesce_b_b` bit(8) DEFAULT NULL, 1361 `if_______b_b` bit(8) DEFAULT NULL, 1362 `ifnull___b_b` bit(8) DEFAULT NULL, 1363 `least____b_b` bit(8) DEFAULT NULL, 1364 `greatest_b_b` bit(8) DEFAULT NULL 1365) ENGINE=MyISAM DEFAULT CHARSET=latin1 1366DROP TABLE t2; 1367SET STATEMENT sql_mode = 'NO_ENGINE_SUBSTITUTION' FOR 1368CREATE TABLE t2 AS 1369SELECT 1370CASE WHEN a IS NOT NULL THEN a ELSE b END AS case_____a_b, 1371CASE WHEN a IS NOT NULL THEN b ELSE a END AS case_____b_a, 1372COALESCE(a, b) AS coalesce_a_b, 1373COALESCE(b, a) AS coalesce_b_a, 1374IF(a IS NULL, a, b) AS if_______a_b, 1375IF(a IS NULL, b, a) AS if_______b_a, 1376IFNULL(a, b) AS ifnull___a_b, 1377IFNULL(b, a) AS ifnull___b_a, 1378LEAST(a, b) AS least____a_b, 1379LEAST(b, a) AS least____b_a, 1380GREATEST(a, b) AS greatest_a_b, 1381GREATEST(b, a) AS greatest_b_a 1382FROM t1; 1383SHOW CREATE TABLE t2; 1384Table Create Table 1385t2 CREATE TABLE `t2` ( 1386 `case_____a_b` bit(8) DEFAULT NULL, 1387 `case_____b_a` bit(8) DEFAULT NULL, 1388 `coalesce_a_b` bit(8) DEFAULT NULL, 1389 `coalesce_b_a` bit(8) DEFAULT NULL, 1390 `if_______a_b` bit(8) DEFAULT NULL, 1391 `if_______b_a` bit(8) DEFAULT NULL, 1392 `ifnull___a_b` bit(8) DEFAULT NULL, 1393 `ifnull___b_a` bit(8) DEFAULT NULL, 1394 `least____a_b` bit(8) DEFAULT NULL, 1395 `least____b_a` bit(8) DEFAULT NULL, 1396 `greatest_a_b` bit(8) DEFAULT NULL, 1397 `greatest_b_a` bit(8) DEFAULT NULL 1398) ENGINE=MyISAM DEFAULT CHARSET=latin1 1399DROP TABLE t2; 1400DROP TABLE t1; 1401# 1402CREATE TABLE t1 (a FLOAT, b SMALLINT); 1403INSERT INTO t1 VALUES (1,-32678); 1404SELECT 1405a AS ___________a, 1406CASE WHEN a IS NOT NULL THEN a END AS case_______a, 1407CASE WHEN a IS NOT NULL THEN a ELSE a END AS case_____a_a, 1408COALESCE(a) AS coalesce___a, 1409COALESCE(a, a) AS coalesce_a_a, 1410IF(a IS NULL, a, a) AS if_______a_a, 1411IFNULL(a, a) AS ifnull___a_a, 1412LEAST(a, a) AS least____a_a, 1413GREATEST(a, a) AS greatest_a_a, 1414b AS ___________b, 1415CASE WHEN a IS NOT NULL THEN b END AS case_______b, 1416CASE WHEN a IS NOT NULL THEN b ELSE b END AS case_____b_b, 1417COALESCE(b) AS coalesce___b, 1418COALESCE(b, b) AS coalesce_b_b, 1419IF(a IS NULL, b, b) AS if_______b_b, 1420IFNULL(b, b) AS ifnull___b_b, 1421LEAST(b, b) AS least____b_b, 1422GREATEST(b, b) AS greatest_b_b 1423FROM t1; 1424Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr 1425def test t1 t1 a ___________a 4 12 1 Y 32768 31 63 1426def case_______a 4 12 1 Y 32896 31 63 1427def case_____a_a 4 12 1 Y 32896 31 63 1428def coalesce___a 4 12 1 Y 32896 31 63 1429def coalesce_a_a 4 12 1 Y 32896 31 63 1430def if_______a_a 4 12 1 Y 32896 31 63 1431def ifnull___a_a 4 12 1 Y 32896 31 63 1432def least____a_a 4 23 1 Y 32896 31 63 1433def greatest_a_a 4 23 1 Y 32896 31 63 1434def test t1 t1 b ___________b 2 6 6 Y 32768 0 63 1435def case_______b 2 6 6 Y 32896 0 63 1436def case_____b_b 2 6 6 Y 32896 0 63 1437def coalesce___b 2 6 6 Y 32896 0 63 1438def coalesce_b_b 2 6 6 Y 32896 0 63 1439def if_______b_b 2 6 6 Y 32896 0 63 1440def ifnull___b_b 2 6 6 Y 32896 0 63 1441def least____b_b 2 6 6 Y 32896 0 63 1442def greatest_b_b 2 6 6 Y 32896 0 63 1443___________a 1 1444case_______a 1 1445case_____a_a 1 1446coalesce___a 1 1447coalesce_a_a 1 1448if_______a_a 1 1449ifnull___a_a 1 1450least____a_a 1 1451greatest_a_a 1 1452___________b -32678 1453case_______b -32678 1454case_____b_b -32678 1455coalesce___b -32678 1456coalesce_b_b -32678 1457if_______b_b -32678 1458ifnull___b_b -32678 1459least____b_b -32678 1460greatest_b_b -32678 1461SELECT 1462CASE WHEN a IS NOT NULL THEN a ELSE b END AS case_____a_b, 1463CASE WHEN a IS NOT NULL THEN b ELSE a END AS case_____b_a, 1464COALESCE(a, b) AS coalesce_a_b, 1465COALESCE(b, a) AS coalesce_b_a, 1466IF(a IS NULL, a, b) AS if_______a_b, 1467IF(a IS NULL, b, a) AS if_______b_a, 1468IFNULL(a, b) AS ifnull___a_b, 1469IFNULL(b, a) AS ifnull___b_a, 1470LEAST(a, b) AS least____a_b, 1471LEAST(b, a) AS least____b_a, 1472GREATEST(a, b) AS greatest_a_b, 1473GREATEST(b, a) AS greatest_b_a 1474FROM t1; 1475Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr 1476def case_____a_b 4 12 1 Y 32896 31 63 1477def case_____b_a 4 12 6 Y 32896 31 63 1478def coalesce_a_b 4 12 1 Y 32896 31 63 1479def coalesce_b_a 4 12 6 Y 32896 31 63 1480def if_______a_b 4 12 6 Y 32896 31 63 1481def if_______b_a 4 12 1 Y 32896 31 63 1482def ifnull___a_b 4 12 1 Y 32896 31 63 1483def ifnull___b_a 4 12 6 Y 32896 31 63 1484def least____a_b 5 23 6 Y 32896 31 63 1485def least____b_a 5 23 6 Y 32896 31 63 1486def greatest_a_b 5 23 1 Y 32896 31 63 1487def greatest_b_a 5 23 1 Y 32896 31 63 1488case_____a_b 1 1489case_____b_a -32678 1490coalesce_a_b 1 1491coalesce_b_a -32678 1492if_______a_b -32678 1493if_______b_a 1 1494ifnull___a_b 1 1495ifnull___b_a -32678 1496least____a_b -32678 1497least____b_a -32678 1498greatest_a_b 1 1499greatest_b_a 1 1500CREATE TABLE t2 AS 1501SELECT 1502a AS ___________a, 1503CASE WHEN a IS NOT NULL THEN a END AS case_______a, 1504CASE WHEN a IS NOT NULL THEN a ELSE a END AS case_____a_a, 1505COALESCE(a) AS coalesce___a, 1506COALESCE(a, a) AS coalesce_a_a, 1507IF(a IS NULL, a, a) AS if_______a_a, 1508IFNULL(a, a) AS ifnull___a_a, 1509LEAST(a, a) AS least____a_a, 1510GREATEST(a, a) AS greatest_a_a, 1511b AS ___________b, 1512CASE WHEN a IS NOT NULL THEN b END AS case_______b, 1513CASE WHEN a IS NOT NULL THEN b ELSE b END AS case_____b_b, 1514COALESCE(b) AS coalesce___b, 1515COALESCE(b, b) AS coalesce_b_b, 1516IF(a IS NULL, b, b) AS if_______b_b, 1517IFNULL(b, b) AS ifnull___b_b, 1518LEAST(b, b) AS least____b_b, 1519GREATEST(b, b) AS greatest_b_b 1520FROM t1; 1521SHOW CREATE TABLE t2; 1522Table Create Table 1523t2 CREATE TABLE `t2` ( 1524 `___________a` float DEFAULT NULL, 1525 `case_______a` float DEFAULT NULL, 1526 `case_____a_a` float DEFAULT NULL, 1527 `coalesce___a` float DEFAULT NULL, 1528 `coalesce_a_a` float DEFAULT NULL, 1529 `if_______a_a` float DEFAULT NULL, 1530 `ifnull___a_a` float DEFAULT NULL, 1531 `least____a_a` float DEFAULT NULL, 1532 `greatest_a_a` float DEFAULT NULL, 1533 `___________b` smallint(6) DEFAULT NULL, 1534 `case_______b` smallint(6) DEFAULT NULL, 1535 `case_____b_b` smallint(6) DEFAULT NULL, 1536 `coalesce___b` smallint(6) DEFAULT NULL, 1537 `coalesce_b_b` smallint(6) DEFAULT NULL, 1538 `if_______b_b` smallint(6) DEFAULT NULL, 1539 `ifnull___b_b` smallint(6) DEFAULT NULL, 1540 `least____b_b` smallint(6) DEFAULT NULL, 1541 `greatest_b_b` smallint(6) DEFAULT NULL 1542) ENGINE=MyISAM DEFAULT CHARSET=latin1 1543DROP TABLE t2; 1544SET STATEMENT sql_mode = 'NO_ENGINE_SUBSTITUTION' FOR 1545CREATE TABLE t2 AS 1546SELECT 1547CASE WHEN a IS NOT NULL THEN a ELSE b END AS case_____a_b, 1548CASE WHEN a IS NOT NULL THEN b ELSE a END AS case_____b_a, 1549COALESCE(a, b) AS coalesce_a_b, 1550COALESCE(b, a) AS coalesce_b_a, 1551IF(a IS NULL, a, b) AS if_______a_b, 1552IF(a IS NULL, b, a) AS if_______b_a, 1553IFNULL(a, b) AS ifnull___a_b, 1554IFNULL(b, a) AS ifnull___b_a, 1555LEAST(a, b) AS least____a_b, 1556LEAST(b, a) AS least____b_a, 1557GREATEST(a, b) AS greatest_a_b, 1558GREATEST(b, a) AS greatest_b_a 1559FROM t1; 1560SHOW CREATE TABLE t2; 1561Table Create Table 1562t2 CREATE TABLE `t2` ( 1563 `case_____a_b` float DEFAULT NULL, 1564 `case_____b_a` float DEFAULT NULL, 1565 `coalesce_a_b` float DEFAULT NULL, 1566 `coalesce_b_a` float DEFAULT NULL, 1567 `if_______a_b` float DEFAULT NULL, 1568 `if_______b_a` float DEFAULT NULL, 1569 `ifnull___a_b` float DEFAULT NULL, 1570 `ifnull___b_a` float DEFAULT NULL, 1571 `least____a_b` double DEFAULT NULL, 1572 `least____b_a` double DEFAULT NULL, 1573 `greatest_a_b` double DEFAULT NULL, 1574 `greatest_b_a` double DEFAULT NULL 1575) ENGINE=MyISAM DEFAULT CHARSET=latin1 1576DROP TABLE t2; 1577DROP TABLE t1; 1578# 1579CREATE TABLE t1 (a VARCHAR(10), b ENUM('b')); 1580INSERT INTO t1 VALUES ('a','b'); 1581SELECT 1582a AS ___________a, 1583CASE WHEN a IS NOT NULL THEN a END AS case_______a, 1584CASE WHEN a IS NOT NULL THEN a ELSE a END AS case_____a_a, 1585COALESCE(a) AS coalesce___a, 1586COALESCE(a, a) AS coalesce_a_a, 1587IF(a IS NULL, a, a) AS if_______a_a, 1588IFNULL(a, a) AS ifnull___a_a, 1589LEAST(a, a) AS least____a_a, 1590GREATEST(a, a) AS greatest_a_a, 1591b AS ___________b, 1592CASE WHEN a IS NOT NULL THEN b END AS case_______b, 1593CASE WHEN a IS NOT NULL THEN b ELSE b END AS case_____b_b, 1594COALESCE(b) AS coalesce___b, 1595COALESCE(b, b) AS coalesce_b_b, 1596IF(a IS NULL, b, b) AS if_______b_b, 1597IFNULL(b, b) AS ifnull___b_b, 1598LEAST(b, b) AS least____b_b, 1599GREATEST(b, b) AS greatest_b_b 1600FROM t1; 1601Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr 1602def test t1 t1 a ___________a 253 10 1 Y 0 0 8 1603def case_______a 253 10 1 Y 0 39 8 1604def case_____a_a 253 10 1 Y 0 39 8 1605def coalesce___a 253 10 1 Y 0 39 8 1606def coalesce_a_a 253 10 1 Y 0 39 8 1607def if_______a_a 253 10 1 Y 0 39 8 1608def ifnull___a_a 253 10 1 Y 0 39 8 1609def least____a_a 253 10 1 Y 0 39 8 1610def greatest_a_a 253 10 1 Y 0 39 8 1611def test t1 t1 b ___________b 254 1 1 Y 256 0 8 1612def case_______b 254 1 1 Y 0 39 8 1613def case_____b_b 254 1 1 Y 0 39 8 1614def coalesce___b 254 1 1 Y 0 39 8 1615def coalesce_b_b 254 1 1 Y 0 39 8 1616def if_______b_b 254 1 1 Y 0 39 8 1617def ifnull___b_b 254 1 1 Y 0 39 8 1618def least____b_b 254 1 1 Y 0 39 8 1619def greatest_b_b 254 1 1 Y 0 39 8 1620___________a a 1621case_______a a 1622case_____a_a a 1623coalesce___a a 1624coalesce_a_a a 1625if_______a_a a 1626ifnull___a_a a 1627least____a_a a 1628greatest_a_a a 1629___________b b 1630case_______b b 1631case_____b_b b 1632coalesce___b b 1633coalesce_b_b b 1634if_______b_b b 1635ifnull___b_b b 1636least____b_b b 1637greatest_b_b b 1638SELECT 1639CASE WHEN a IS NOT NULL THEN a ELSE b END AS case_____a_b, 1640CASE WHEN a IS NOT NULL THEN b ELSE a END AS case_____b_a, 1641COALESCE(a, b) AS coalesce_a_b, 1642COALESCE(b, a) AS coalesce_b_a, 1643IF(a IS NULL, a, b) AS if_______a_b, 1644IF(a IS NULL, b, a) AS if_______b_a, 1645IFNULL(a, b) AS ifnull___a_b, 1646IFNULL(b, a) AS ifnull___b_a, 1647LEAST(a, b) AS least____a_b, 1648LEAST(b, a) AS least____b_a, 1649GREATEST(a, b) AS greatest_a_b, 1650GREATEST(b, a) AS greatest_b_a 1651FROM t1; 1652Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr 1653def case_____a_b 253 10 1 Y 0 39 8 1654def case_____b_a 253 10 1 Y 0 39 8 1655def coalesce_a_b 253 10 1 Y 0 39 8 1656def coalesce_b_a 253 10 1 Y 0 39 8 1657def if_______a_b 253 10 1 Y 0 39 8 1658def if_______b_a 253 10 1 Y 0 39 8 1659def ifnull___a_b 253 10 1 Y 0 39 8 1660def ifnull___b_a 253 10 1 Y 0 39 8 1661def least____a_b 253 10 1 Y 0 39 8 1662def least____b_a 253 10 1 Y 0 39 8 1663def greatest_a_b 253 10 1 Y 0 39 8 1664def greatest_b_a 253 10 1 Y 0 39 8 1665case_____a_b a 1666case_____b_a b 1667coalesce_a_b a 1668coalesce_b_a b 1669if_______a_b b 1670if_______b_a a 1671ifnull___a_b a 1672ifnull___b_a b 1673least____a_b a 1674least____b_a a 1675greatest_a_b b 1676greatest_b_a b 1677CREATE TABLE t2 AS 1678SELECT 1679a AS ___________a, 1680CASE WHEN a IS NOT NULL THEN a END AS case_______a, 1681CASE WHEN a IS NOT NULL THEN a ELSE a END AS case_____a_a, 1682COALESCE(a) AS coalesce___a, 1683COALESCE(a, a) AS coalesce_a_a, 1684IF(a IS NULL, a, a) AS if_______a_a, 1685IFNULL(a, a) AS ifnull___a_a, 1686LEAST(a, a) AS least____a_a, 1687GREATEST(a, a) AS greatest_a_a, 1688b AS ___________b, 1689CASE WHEN a IS NOT NULL THEN b END AS case_______b, 1690CASE WHEN a IS NOT NULL THEN b ELSE b END AS case_____b_b, 1691COALESCE(b) AS coalesce___b, 1692COALESCE(b, b) AS coalesce_b_b, 1693IF(a IS NULL, b, b) AS if_______b_b, 1694IFNULL(b, b) AS ifnull___b_b, 1695LEAST(b, b) AS least____b_b, 1696GREATEST(b, b) AS greatest_b_b 1697FROM t1; 1698SHOW CREATE TABLE t2; 1699Table Create Table 1700t2 CREATE TABLE `t2` ( 1701 `___________a` varchar(10) DEFAULT NULL, 1702 `case_______a` varchar(10) DEFAULT NULL, 1703 `case_____a_a` varchar(10) DEFAULT NULL, 1704 `coalesce___a` varchar(10) DEFAULT NULL, 1705 `coalesce_a_a` varchar(10) DEFAULT NULL, 1706 `if_______a_a` varchar(10) DEFAULT NULL, 1707 `ifnull___a_a` varchar(10) DEFAULT NULL, 1708 `least____a_a` varchar(10) DEFAULT NULL, 1709 `greatest_a_a` varchar(10) DEFAULT NULL, 1710 `___________b` enum('b') DEFAULT NULL, 1711 `case_______b` varchar(1) DEFAULT NULL, 1712 `case_____b_b` varchar(1) DEFAULT NULL, 1713 `coalesce___b` varchar(1) DEFAULT NULL, 1714 `coalesce_b_b` varchar(1) DEFAULT NULL, 1715 `if_______b_b` varchar(1) DEFAULT NULL, 1716 `ifnull___b_b` varchar(1) DEFAULT NULL, 1717 `least____b_b` varchar(1) DEFAULT NULL, 1718 `greatest_b_b` varchar(1) DEFAULT NULL 1719) ENGINE=MyISAM DEFAULT CHARSET=latin1 1720DROP TABLE t2; 1721SET STATEMENT sql_mode = 'NO_ENGINE_SUBSTITUTION' FOR 1722CREATE TABLE t2 AS 1723SELECT 1724CASE WHEN a IS NOT NULL THEN a ELSE b END AS case_____a_b, 1725CASE WHEN a IS NOT NULL THEN b ELSE a END AS case_____b_a, 1726COALESCE(a, b) AS coalesce_a_b, 1727COALESCE(b, a) AS coalesce_b_a, 1728IF(a IS NULL, a, b) AS if_______a_b, 1729IF(a IS NULL, b, a) AS if_______b_a, 1730IFNULL(a, b) AS ifnull___a_b, 1731IFNULL(b, a) AS ifnull___b_a, 1732LEAST(a, b) AS least____a_b, 1733LEAST(b, a) AS least____b_a, 1734GREATEST(a, b) AS greatest_a_b, 1735GREATEST(b, a) AS greatest_b_a 1736FROM t1; 1737SHOW CREATE TABLE t2; 1738Table Create Table 1739t2 CREATE TABLE `t2` ( 1740 `case_____a_b` varchar(10) DEFAULT NULL, 1741 `case_____b_a` varchar(10) DEFAULT NULL, 1742 `coalesce_a_b` varchar(10) DEFAULT NULL, 1743 `coalesce_b_a` varchar(10) DEFAULT NULL, 1744 `if_______a_b` varchar(10) DEFAULT NULL, 1745 `if_______b_a` varchar(10) DEFAULT NULL, 1746 `ifnull___a_b` varchar(10) DEFAULT NULL, 1747 `ifnull___b_a` varchar(10) DEFAULT NULL, 1748 `least____a_b` varchar(10) DEFAULT NULL, 1749 `least____b_a` varchar(10) DEFAULT NULL, 1750 `greatest_a_b` varchar(10) DEFAULT NULL, 1751 `greatest_b_a` varchar(10) DEFAULT NULL 1752) ENGINE=MyISAM DEFAULT CHARSET=latin1 1753DROP TABLE t2; 1754DROP TABLE t1; 1755# 1756CREATE TABLE t1 (a INT, b YEAR); 1757INSERT INTO t1 VALUES (-2147483648,2015); 1758SELECT 1759a AS ___________a, 1760CASE WHEN a IS NOT NULL THEN a END AS case_______a, 1761CASE WHEN a IS NOT NULL THEN a ELSE a END AS case_____a_a, 1762COALESCE(a) AS coalesce___a, 1763COALESCE(a, a) AS coalesce_a_a, 1764IF(a IS NULL, a, a) AS if_______a_a, 1765IFNULL(a, a) AS ifnull___a_a, 1766LEAST(a, a) AS least____a_a, 1767GREATEST(a, a) AS greatest_a_a, 1768b AS ___________b, 1769CASE WHEN a IS NOT NULL THEN b END AS case_______b, 1770CASE WHEN a IS NOT NULL THEN b ELSE b END AS case_____b_b, 1771COALESCE(b) AS coalesce___b, 1772COALESCE(b, b) AS coalesce_b_b, 1773IF(a IS NULL, b, b) AS if_______b_b, 1774IFNULL(b, b) AS ifnull___b_b, 1775LEAST(b, b) AS least____b_b, 1776GREATEST(b, b) AS greatest_b_b 1777FROM t1; 1778Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr 1779def test t1 t1 a ___________a 3 11 11 Y 32768 0 63 1780def case_______a 3 11 11 Y 32896 0 63 1781def case_____a_a 3 11 11 Y 32896 0 63 1782def coalesce___a 3 11 11 Y 32896 0 63 1783def coalesce_a_a 3 11 11 Y 32896 0 63 1784def if_______a_a 3 11 11 Y 32896 0 63 1785def ifnull___a_a 3 11 11 Y 32896 0 63 1786def least____a_a 3 11 11 Y 32896 0 63 1787def greatest_a_a 3 11 11 Y 32896 0 63 1788def test t1 t1 b ___________b 13 4 4 Y 32864 0 63 1789def case_______b 13 4 4 Y 32928 0 63 1790def case_____b_b 13 4 4 Y 32928 0 63 1791def coalesce___b 13 4 4 Y 32928 0 63 1792def coalesce_b_b 13 4 4 Y 32928 0 63 1793def if_______b_b 13 4 4 Y 32928 0 63 1794def ifnull___b_b 13 4 4 Y 32928 0 63 1795def least____b_b 13 4 4 Y 32928 0 63 1796def greatest_b_b 13 4 4 Y 32928 0 63 1797___________a -2147483648 1798case_______a -2147483648 1799case_____a_a -2147483648 1800coalesce___a -2147483648 1801coalesce_a_a -2147483648 1802if_______a_a -2147483648 1803ifnull___a_a -2147483648 1804least____a_a -2147483648 1805greatest_a_a -2147483648 1806___________b 2015 1807case_______b 2015 1808case_____b_b 2015 1809coalesce___b 2015 1810coalesce_b_b 2015 1811if_______b_b 2015 1812ifnull___b_b 2015 1813least____b_b 2015 1814greatest_b_b 2015 1815SELECT 1816CASE WHEN a IS NOT NULL THEN a ELSE b END AS case_____a_b, 1817CASE WHEN a IS NOT NULL THEN b ELSE a END AS case_____b_a, 1818COALESCE(a, b) AS coalesce_a_b, 1819COALESCE(b, a) AS coalesce_b_a, 1820IF(a IS NULL, a, b) AS if_______a_b, 1821IF(a IS NULL, b, a) AS if_______b_a, 1822IFNULL(a, b) AS ifnull___a_b, 1823IFNULL(b, a) AS ifnull___b_a, 1824LEAST(a, b) AS least____a_b, 1825LEAST(b, a) AS least____b_a, 1826GREATEST(a, b) AS greatest_a_b, 1827GREATEST(b, a) AS greatest_b_a 1828FROM t1; 1829Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr 1830def case_____a_b 246 11 11 Y 32896 0 63 1831def case_____b_a 246 11 4 Y 32896 0 63 1832def coalesce_a_b 246 11 11 Y 32896 0 63 1833def coalesce_b_a 246 11 4 Y 32896 0 63 1834def if_______a_b 246 11 4 Y 32896 0 63 1835def if_______b_a 246 11 11 Y 32896 0 63 1836def ifnull___a_b 246 11 11 Y 32896 0 63 1837def ifnull___b_a 246 11 4 Y 32896 0 63 1838def least____a_b 246 11 11 Y 32896 0 63 1839def least____b_a 246 11 11 Y 32896 0 63 1840def greatest_a_b 246 11 4 Y 32896 0 63 1841def greatest_b_a 246 11 4 Y 32896 0 63 1842case_____a_b -2147483648 1843case_____b_a 2015 1844coalesce_a_b -2147483648 1845coalesce_b_a 2015 1846if_______a_b 2015 1847if_______b_a -2147483648 1848ifnull___a_b -2147483648 1849ifnull___b_a 2015 1850least____a_b -2147483648 1851least____b_a -2147483648 1852greatest_a_b 2015 1853greatest_b_a 2015 1854CREATE TABLE t2 AS 1855SELECT 1856a AS ___________a, 1857CASE WHEN a IS NOT NULL THEN a END AS case_______a, 1858CASE WHEN a IS NOT NULL THEN a ELSE a END AS case_____a_a, 1859COALESCE(a) AS coalesce___a, 1860COALESCE(a, a) AS coalesce_a_a, 1861IF(a IS NULL, a, a) AS if_______a_a, 1862IFNULL(a, a) AS ifnull___a_a, 1863LEAST(a, a) AS least____a_a, 1864GREATEST(a, a) AS greatest_a_a, 1865b AS ___________b, 1866CASE WHEN a IS NOT NULL THEN b END AS case_______b, 1867CASE WHEN a IS NOT NULL THEN b ELSE b END AS case_____b_b, 1868COALESCE(b) AS coalesce___b, 1869COALESCE(b, b) AS coalesce_b_b, 1870IF(a IS NULL, b, b) AS if_______b_b, 1871IFNULL(b, b) AS ifnull___b_b, 1872LEAST(b, b) AS least____b_b, 1873GREATEST(b, b) AS greatest_b_b 1874FROM t1; 1875SHOW CREATE TABLE t2; 1876Table Create Table 1877t2 CREATE TABLE `t2` ( 1878 `___________a` int(11) DEFAULT NULL, 1879 `case_______a` int(11) DEFAULT NULL, 1880 `case_____a_a` int(11) DEFAULT NULL, 1881 `coalesce___a` int(11) DEFAULT NULL, 1882 `coalesce_a_a` int(11) DEFAULT NULL, 1883 `if_______a_a` int(11) DEFAULT NULL, 1884 `ifnull___a_a` int(11) DEFAULT NULL, 1885 `least____a_a` int(11) DEFAULT NULL, 1886 `greatest_a_a` int(11) DEFAULT NULL, 1887 `___________b` year(4) DEFAULT NULL, 1888 `case_______b` year(4) DEFAULT NULL, 1889 `case_____b_b` year(4) DEFAULT NULL, 1890 `coalesce___b` year(4) DEFAULT NULL, 1891 `coalesce_b_b` year(4) DEFAULT NULL, 1892 `if_______b_b` year(4) DEFAULT NULL, 1893 `ifnull___b_b` year(4) DEFAULT NULL, 1894 `least____b_b` year(4) DEFAULT NULL, 1895 `greatest_b_b` year(4) DEFAULT NULL 1896) ENGINE=MyISAM DEFAULT CHARSET=latin1 1897DROP TABLE t2; 1898SET STATEMENT sql_mode = 'NO_ENGINE_SUBSTITUTION' FOR 1899CREATE TABLE t2 AS 1900SELECT 1901CASE WHEN a IS NOT NULL THEN a ELSE b END AS case_____a_b, 1902CASE WHEN a IS NOT NULL THEN b ELSE a END AS case_____b_a, 1903COALESCE(a, b) AS coalesce_a_b, 1904COALESCE(b, a) AS coalesce_b_a, 1905IF(a IS NULL, a, b) AS if_______a_b, 1906IF(a IS NULL, b, a) AS if_______b_a, 1907IFNULL(a, b) AS ifnull___a_b, 1908IFNULL(b, a) AS ifnull___b_a, 1909LEAST(a, b) AS least____a_b, 1910LEAST(b, a) AS least____b_a, 1911GREATEST(a, b) AS greatest_a_b, 1912GREATEST(b, a) AS greatest_b_a 1913FROM t1; 1914SHOW CREATE TABLE t2; 1915Table Create Table 1916t2 CREATE TABLE `t2` ( 1917 `case_____a_b` decimal(10,0) DEFAULT NULL, 1918 `case_____b_a` decimal(10,0) DEFAULT NULL, 1919 `coalesce_a_b` decimal(10,0) DEFAULT NULL, 1920 `coalesce_b_a` decimal(10,0) DEFAULT NULL, 1921 `if_______a_b` decimal(10,0) DEFAULT NULL, 1922 `if_______b_a` decimal(10,0) DEFAULT NULL, 1923 `ifnull___a_b` decimal(10,0) DEFAULT NULL, 1924 `ifnull___b_a` decimal(10,0) DEFAULT NULL, 1925 `least____a_b` decimal(10,0) DEFAULT NULL, 1926 `least____b_a` decimal(10,0) DEFAULT NULL, 1927 `greatest_a_b` decimal(10,0) DEFAULT NULL, 1928 `greatest_b_a` decimal(10,0) DEFAULT NULL 1929) ENGINE=MyISAM DEFAULT CHARSET=latin1 1930DROP TABLE t2; 1931DROP TABLE t1; 1932# 1933CREATE TABLE t1 (a INT UNSIGNED, b YEAR); 1934INSERT INTO t1 VALUES (4294967295,2015); 1935SELECT 1936a AS ___________a, 1937CASE WHEN a IS NOT NULL THEN a END AS case_______a, 1938CASE WHEN a IS NOT NULL THEN a ELSE a END AS case_____a_a, 1939COALESCE(a) AS coalesce___a, 1940COALESCE(a, a) AS coalesce_a_a, 1941IF(a IS NULL, a, a) AS if_______a_a, 1942IFNULL(a, a) AS ifnull___a_a, 1943LEAST(a, a) AS least____a_a, 1944GREATEST(a, a) AS greatest_a_a, 1945b AS ___________b, 1946CASE WHEN a IS NOT NULL THEN b END AS case_______b, 1947CASE WHEN a IS NOT NULL THEN b ELSE b END AS case_____b_b, 1948COALESCE(b) AS coalesce___b, 1949COALESCE(b, b) AS coalesce_b_b, 1950IF(a IS NULL, b, b) AS if_______b_b, 1951IFNULL(b, b) AS ifnull___b_b, 1952LEAST(b, b) AS least____b_b, 1953GREATEST(b, b) AS greatest_b_b 1954FROM t1; 1955Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr 1956def test t1 t1 a ___________a 3 10 10 Y 32800 0 63 1957def case_______a 3 10 10 Y 32928 0 63 1958def case_____a_a 3 10 10 Y 32928 0 63 1959def coalesce___a 3 10 10 Y 32928 0 63 1960def coalesce_a_a 3 10 10 Y 32928 0 63 1961def if_______a_a 3 10 10 Y 32928 0 63 1962def ifnull___a_a 3 10 10 Y 32928 0 63 1963def least____a_a 3 10 10 Y 32928 0 63 1964def greatest_a_a 3 10 10 Y 32928 0 63 1965def test t1 t1 b ___________b 13 4 4 Y 32864 0 63 1966def case_______b 13 4 4 Y 32928 0 63 1967def case_____b_b 13 4 4 Y 32928 0 63 1968def coalesce___b 13 4 4 Y 32928 0 63 1969def coalesce_b_b 13 4 4 Y 32928 0 63 1970def if_______b_b 13 4 4 Y 32928 0 63 1971def ifnull___b_b 13 4 4 Y 32928 0 63 1972def least____b_b 13 4 4 Y 32928 0 63 1973def greatest_b_b 13 4 4 Y 32928 0 63 1974___________a 4294967295 1975case_______a 4294967295 1976case_____a_a 4294967295 1977coalesce___a 4294967295 1978coalesce_a_a 4294967295 1979if_______a_a 4294967295 1980ifnull___a_a 4294967295 1981least____a_a 4294967295 1982greatest_a_a 4294967295 1983___________b 2015 1984case_______b 2015 1985case_____b_b 2015 1986coalesce___b 2015 1987coalesce_b_b 2015 1988if_______b_b 2015 1989ifnull___b_b 2015 1990least____b_b 2015 1991greatest_b_b 2015 1992SELECT 1993CASE WHEN a IS NOT NULL THEN a ELSE b END AS case_____a_b, 1994CASE WHEN a IS NOT NULL THEN b ELSE a END AS case_____b_a, 1995COALESCE(a, b) AS coalesce_a_b, 1996COALESCE(b, a) AS coalesce_b_a, 1997IF(a IS NULL, a, b) AS if_______a_b, 1998IF(a IS NULL, b, a) AS if_______b_a, 1999IFNULL(a, b) AS ifnull___a_b, 2000IFNULL(b, a) AS ifnull___b_a, 2001LEAST(a, b) AS least____a_b, 2002LEAST(b, a) AS least____b_a, 2003GREATEST(a, b) AS greatest_a_b, 2004GREATEST(b, a) AS greatest_b_a 2005FROM t1; 2006Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr 2007def case_____a_b 3 10 10 Y 32928 0 63 2008def case_____b_a 3 10 4 Y 32928 0 63 2009def coalesce_a_b 3 10 10 Y 32928 0 63 2010def coalesce_b_a 3 10 4 Y 32928 0 63 2011def if_______a_b 3 10 4 Y 32928 0 63 2012def if_______b_a 3 10 10 Y 32928 0 63 2013def ifnull___a_b 3 10 10 Y 32928 0 63 2014def ifnull___b_a 3 10 4 Y 32928 0 63 2015def least____a_b 3 10 4 Y 32928 0 63 2016def least____b_a 3 10 4 Y 32928 0 63 2017def greatest_a_b 3 10 10 Y 32928 0 63 2018def greatest_b_a 3 10 10 Y 32928 0 63 2019case_____a_b 4294967295 2020case_____b_a 2015 2021coalesce_a_b 4294967295 2022coalesce_b_a 2015 2023if_______a_b 2015 2024if_______b_a 4294967295 2025ifnull___a_b 4294967295 2026ifnull___b_a 2015 2027least____a_b 2015 2028least____b_a 2015 2029greatest_a_b 4294967295 2030greatest_b_a 4294967295 2031CREATE TABLE t2 AS 2032SELECT 2033a AS ___________a, 2034CASE WHEN a IS NOT NULL THEN a END AS case_______a, 2035CASE WHEN a IS NOT NULL THEN a ELSE a END AS case_____a_a, 2036COALESCE(a) AS coalesce___a, 2037COALESCE(a, a) AS coalesce_a_a, 2038IF(a IS NULL, a, a) AS if_______a_a, 2039IFNULL(a, a) AS ifnull___a_a, 2040LEAST(a, a) AS least____a_a, 2041GREATEST(a, a) AS greatest_a_a, 2042b AS ___________b, 2043CASE WHEN a IS NOT NULL THEN b END AS case_______b, 2044CASE WHEN a IS NOT NULL THEN b ELSE b END AS case_____b_b, 2045COALESCE(b) AS coalesce___b, 2046COALESCE(b, b) AS coalesce_b_b, 2047IF(a IS NULL, b, b) AS if_______b_b, 2048IFNULL(b, b) AS ifnull___b_b, 2049LEAST(b, b) AS least____b_b, 2050GREATEST(b, b) AS greatest_b_b 2051FROM t1; 2052SHOW CREATE TABLE t2; 2053Table Create Table 2054t2 CREATE TABLE `t2` ( 2055 `___________a` int(10) unsigned DEFAULT NULL, 2056 `case_______a` int(10) unsigned DEFAULT NULL, 2057 `case_____a_a` int(10) unsigned DEFAULT NULL, 2058 `coalesce___a` int(10) unsigned DEFAULT NULL, 2059 `coalesce_a_a` int(10) unsigned DEFAULT NULL, 2060 `if_______a_a` int(10) unsigned DEFAULT NULL, 2061 `ifnull___a_a` int(10) unsigned DEFAULT NULL, 2062 `least____a_a` int(10) unsigned DEFAULT NULL, 2063 `greatest_a_a` int(10) unsigned DEFAULT NULL, 2064 `___________b` year(4) DEFAULT NULL, 2065 `case_______b` year(4) DEFAULT NULL, 2066 `case_____b_b` year(4) DEFAULT NULL, 2067 `coalesce___b` year(4) DEFAULT NULL, 2068 `coalesce_b_b` year(4) DEFAULT NULL, 2069 `if_______b_b` year(4) DEFAULT NULL, 2070 `ifnull___b_b` year(4) DEFAULT NULL, 2071 `least____b_b` year(4) DEFAULT NULL, 2072 `greatest_b_b` year(4) DEFAULT NULL 2073) ENGINE=MyISAM DEFAULT CHARSET=latin1 2074DROP TABLE t2; 2075SET STATEMENT sql_mode = 'NO_ENGINE_SUBSTITUTION' FOR 2076CREATE TABLE t2 AS 2077SELECT 2078CASE WHEN a IS NOT NULL THEN a ELSE b END AS case_____a_b, 2079CASE WHEN a IS NOT NULL THEN b ELSE a END AS case_____b_a, 2080COALESCE(a, b) AS coalesce_a_b, 2081COALESCE(b, a) AS coalesce_b_a, 2082IF(a IS NULL, a, b) AS if_______a_b, 2083IF(a IS NULL, b, a) AS if_______b_a, 2084IFNULL(a, b) AS ifnull___a_b, 2085IFNULL(b, a) AS ifnull___b_a, 2086LEAST(a, b) AS least____a_b, 2087LEAST(b, a) AS least____b_a, 2088GREATEST(a, b) AS greatest_a_b, 2089GREATEST(b, a) AS greatest_b_a 2090FROM t1; 2091SHOW CREATE TABLE t2; 2092Table Create Table 2093t2 CREATE TABLE `t2` ( 2094 `case_____a_b` int(10) unsigned DEFAULT NULL, 2095 `case_____b_a` int(10) unsigned DEFAULT NULL, 2096 `coalesce_a_b` int(10) unsigned DEFAULT NULL, 2097 `coalesce_b_a` int(10) unsigned DEFAULT NULL, 2098 `if_______a_b` int(10) unsigned DEFAULT NULL, 2099 `if_______b_a` int(10) unsigned DEFAULT NULL, 2100 `ifnull___a_b` int(10) unsigned DEFAULT NULL, 2101 `ifnull___b_a` int(10) unsigned DEFAULT NULL, 2102 `least____a_b` int(10) unsigned DEFAULT NULL, 2103 `least____b_a` int(10) unsigned DEFAULT NULL, 2104 `greatest_a_b` int(10) unsigned DEFAULT NULL, 2105 `greatest_b_a` int(10) unsigned DEFAULT NULL 2106) ENGINE=MyISAM DEFAULT CHARSET=latin1 2107DROP TABLE t2; 2108DROP TABLE t1; 2109# 2110SET timestamp=UNIX_TIMESTAMP('2001-01-01 01:02:03'); 2111CREATE TABLE t1 (a DATE, b TIME); 2112INSERT INTO t1 VALUES ('2010-01-01','10:20:30'); 2113SELECT 2114a AS ___________a, 2115CASE WHEN a IS NOT NULL THEN a END AS case_______a, 2116CASE WHEN a IS NOT NULL THEN a ELSE a END AS case_____a_a, 2117COALESCE(a) AS coalesce___a, 2118COALESCE(a, a) AS coalesce_a_a, 2119IF(a IS NULL, a, a) AS if_______a_a, 2120IFNULL(a, a) AS ifnull___a_a, 2121LEAST(a, a) AS least____a_a, 2122GREATEST(a, a) AS greatest_a_a, 2123b AS ___________b, 2124CASE WHEN a IS NOT NULL THEN b END AS case_______b, 2125CASE WHEN a IS NOT NULL THEN b ELSE b END AS case_____b_b, 2126COALESCE(b) AS coalesce___b, 2127COALESCE(b, b) AS coalesce_b_b, 2128IF(a IS NULL, b, b) AS if_______b_b, 2129IFNULL(b, b) AS ifnull___b_b, 2130LEAST(b, b) AS least____b_b, 2131GREATEST(b, b) AS greatest_b_b 2132FROM t1; 2133Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr 2134def test t1 t1 a ___________a 10 10 10 Y 128 0 63 2135def case_______a 10 10 10 Y 128 0 63 2136def case_____a_a 10 10 10 Y 128 0 63 2137def coalesce___a 10 10 10 Y 128 0 63 2138def coalesce_a_a 10 10 10 Y 128 0 63 2139def if_______a_a 10 10 10 Y 128 0 63 2140def ifnull___a_a 10 10 10 Y 128 0 63 2141def least____a_a 10 10 10 Y 128 0 63 2142def greatest_a_a 10 10 10 Y 128 0 63 2143def test t1 t1 b ___________b 11 10 8 Y 128 0 63 2144def case_______b 11 10 8 Y 128 0 63 2145def case_____b_b 11 10 8 Y 128 0 63 2146def coalesce___b 11 10 8 Y 128 0 63 2147def coalesce_b_b 11 10 8 Y 128 0 63 2148def if_______b_b 11 10 8 Y 128 0 63 2149def ifnull___b_b 11 10 8 Y 128 0 63 2150def least____b_b 11 10 8 Y 128 0 63 2151def greatest_b_b 11 10 8 Y 128 0 63 2152___________a 2010-01-01 2153case_______a 2010-01-01 2154case_____a_a 2010-01-01 2155coalesce___a 2010-01-01 2156coalesce_a_a 2010-01-01 2157if_______a_a 2010-01-01 2158ifnull___a_a 2010-01-01 2159least____a_a 2010-01-01 2160greatest_a_a 2010-01-01 2161___________b 10:20:30 2162case_______b 10:20:30 2163case_____b_b 10:20:30 2164coalesce___b 10:20:30 2165coalesce_b_b 10:20:30 2166if_______b_b 10:20:30 2167ifnull___b_b 10:20:30 2168least____b_b 10:20:30 2169greatest_b_b 10:20:30 2170SELECT 2171CASE WHEN a IS NOT NULL THEN a ELSE b END AS case_____a_b, 2172CASE WHEN a IS NOT NULL THEN b ELSE a END AS case_____b_a, 2173COALESCE(a, b) AS coalesce_a_b, 2174COALESCE(b, a) AS coalesce_b_a, 2175IF(a IS NULL, a, b) AS if_______a_b, 2176IF(a IS NULL, b, a) AS if_______b_a, 2177IFNULL(a, b) AS ifnull___a_b, 2178IFNULL(b, a) AS ifnull___b_a, 2179LEAST(a, b) AS least____a_b, 2180LEAST(b, a) AS least____b_a, 2181GREATEST(a, b) AS greatest_a_b, 2182GREATEST(b, a) AS greatest_b_a 2183FROM t1; 2184Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr 2185def case_____a_b 12 19 19 Y 128 0 63 2186def case_____b_a 12 19 19 Y 128 0 63 2187def coalesce_a_b 12 19 19 Y 128 0 63 2188def coalesce_b_a 12 19 19 Y 128 0 63 2189def if_______a_b 12 19 19 Y 128 0 63 2190def if_______b_a 12 19 19 Y 128 0 63 2191def ifnull___a_b 12 19 19 Y 128 0 63 2192def ifnull___b_a 12 19 19 Y 128 0 63 2193def least____a_b 12 19 19 Y 128 0 63 2194def least____b_a 12 19 19 Y 128 0 63 2195def greatest_a_b 12 19 19 Y 128 0 63 2196def greatest_b_a 12 19 19 Y 128 0 63 2197case_____a_b 2010-01-01 00:00:00 2198case_____b_a 2001-01-01 10:20:30 2199coalesce_a_b 2010-01-01 00:00:00 2200coalesce_b_a 2001-01-01 10:20:30 2201if_______a_b 2001-01-01 10:20:30 2202if_______b_a 2010-01-01 00:00:00 2203ifnull___a_b 2010-01-01 00:00:00 2204ifnull___b_a 2001-01-01 10:20:30 2205least____a_b 2001-01-01 10:20:30 2206least____b_a 2001-01-01 10:20:30 2207greatest_a_b 2010-01-01 00:00:00 2208greatest_b_a 2010-01-01 00:00:00 2209CREATE TABLE t2 AS 2210SELECT 2211a AS ___________a, 2212CASE WHEN a IS NOT NULL THEN a END AS case_______a, 2213CASE WHEN a IS NOT NULL THEN a ELSE a END AS case_____a_a, 2214COALESCE(a) AS coalesce___a, 2215COALESCE(a, a) AS coalesce_a_a, 2216IF(a IS NULL, a, a) AS if_______a_a, 2217IFNULL(a, a) AS ifnull___a_a, 2218LEAST(a, a) AS least____a_a, 2219GREATEST(a, a) AS greatest_a_a, 2220b AS ___________b, 2221CASE WHEN a IS NOT NULL THEN b END AS case_______b, 2222CASE WHEN a IS NOT NULL THEN b ELSE b END AS case_____b_b, 2223COALESCE(b) AS coalesce___b, 2224COALESCE(b, b) AS coalesce_b_b, 2225IF(a IS NULL, b, b) AS if_______b_b, 2226IFNULL(b, b) AS ifnull___b_b, 2227LEAST(b, b) AS least____b_b, 2228GREATEST(b, b) AS greatest_b_b 2229FROM t1; 2230SHOW CREATE TABLE t2; 2231Table Create Table 2232t2 CREATE TABLE `t2` ( 2233 `___________a` date DEFAULT NULL, 2234 `case_______a` date DEFAULT NULL, 2235 `case_____a_a` date DEFAULT NULL, 2236 `coalesce___a` date DEFAULT NULL, 2237 `coalesce_a_a` date DEFAULT NULL, 2238 `if_______a_a` date DEFAULT NULL, 2239 `ifnull___a_a` date DEFAULT NULL, 2240 `least____a_a` date DEFAULT NULL, 2241 `greatest_a_a` date DEFAULT NULL, 2242 `___________b` time DEFAULT NULL, 2243 `case_______b` time DEFAULT NULL, 2244 `case_____b_b` time DEFAULT NULL, 2245 `coalesce___b` time DEFAULT NULL, 2246 `coalesce_b_b` time DEFAULT NULL, 2247 `if_______b_b` time DEFAULT NULL, 2248 `ifnull___b_b` time DEFAULT NULL, 2249 `least____b_b` time DEFAULT NULL, 2250 `greatest_b_b` time DEFAULT NULL 2251) ENGINE=MyISAM DEFAULT CHARSET=latin1 2252DROP TABLE t2; 2253SET STATEMENT sql_mode = 'NO_ENGINE_SUBSTITUTION' FOR 2254CREATE TABLE t2 AS 2255SELECT 2256CASE WHEN a IS NOT NULL THEN a ELSE b END AS case_____a_b, 2257CASE WHEN a IS NOT NULL THEN b ELSE a END AS case_____b_a, 2258COALESCE(a, b) AS coalesce_a_b, 2259COALESCE(b, a) AS coalesce_b_a, 2260IF(a IS NULL, a, b) AS if_______a_b, 2261IF(a IS NULL, b, a) AS if_______b_a, 2262IFNULL(a, b) AS ifnull___a_b, 2263IFNULL(b, a) AS ifnull___b_a, 2264LEAST(a, b) AS least____a_b, 2265LEAST(b, a) AS least____b_a, 2266GREATEST(a, b) AS greatest_a_b, 2267GREATEST(b, a) AS greatest_b_a 2268FROM t1; 2269SHOW CREATE TABLE t2; 2270Table Create Table 2271t2 CREATE TABLE `t2` ( 2272 `case_____a_b` datetime DEFAULT NULL, 2273 `case_____b_a` datetime DEFAULT NULL, 2274 `coalesce_a_b` datetime DEFAULT NULL, 2275 `coalesce_b_a` datetime DEFAULT NULL, 2276 `if_______a_b` datetime DEFAULT NULL, 2277 `if_______b_a` datetime DEFAULT NULL, 2278 `ifnull___a_b` datetime DEFAULT NULL, 2279 `ifnull___b_a` datetime DEFAULT NULL, 2280 `least____a_b` datetime DEFAULT NULL, 2281 `least____b_a` datetime DEFAULT NULL, 2282 `greatest_a_b` datetime DEFAULT NULL, 2283 `greatest_b_a` datetime DEFAULT NULL 2284) ENGINE=MyISAM DEFAULT CHARSET=latin1 2285DROP TABLE t2; 2286DROP TABLE t1; 2287SET timestamp=DEFAULT; 2288# 2289SET timestamp=UNIX_TIMESTAMP('2001-01-01 01:02:03'); 2290CREATE TABLE t1 (a TIMESTAMP, b TIME); 2291INSERT INTO t1 VALUES ('2010-01-01 00:00:00','10:20:30'); 2292SELECT 2293a AS ___________a, 2294CASE WHEN a IS NOT NULL THEN a END AS case_______a, 2295CASE WHEN a IS NOT NULL THEN a ELSE a END AS case_____a_a, 2296COALESCE(a) AS coalesce___a, 2297COALESCE(a, a) AS coalesce_a_a, 2298IF(a IS NULL, a, a) AS if_______a_a, 2299IFNULL(a, a) AS ifnull___a_a, 2300LEAST(a, a) AS least____a_a, 2301GREATEST(a, a) AS greatest_a_a, 2302b AS ___________b, 2303CASE WHEN a IS NOT NULL THEN b END AS case_______b, 2304CASE WHEN a IS NOT NULL THEN b ELSE b END AS case_____b_b, 2305COALESCE(b) AS coalesce___b, 2306COALESCE(b, b) AS coalesce_b_b, 2307IF(a IS NULL, b, b) AS if_______b_b, 2308IFNULL(b, b) AS ifnull___b_b, 2309LEAST(b, b) AS least____b_b, 2310GREATEST(b, b) AS greatest_b_b 2311FROM t1; 2312Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr 2313def test t1 t1 a ___________a 7 19 19 N 9377 0 63 2314def case_______a 7 19 19 Y 128 0 63 2315def case_____a_a 7 19 19 N 129 0 63 2316def coalesce___a 7 19 19 N 129 0 63 2317def coalesce_a_a 7 19 19 N 129 0 63 2318def if_______a_a 7 19 19 N 129 0 63 2319def ifnull___a_a 7 19 19 N 129 0 63 2320def least____a_a 7 19 19 N 129 0 63 2321def greatest_a_a 7 19 19 N 129 0 63 2322def test t1 t1 b ___________b 11 10 8 Y 128 0 63 2323def case_______b 11 10 8 Y 128 0 63 2324def case_____b_b 11 10 8 Y 128 0 63 2325def coalesce___b 11 10 8 Y 128 0 63 2326def coalesce_b_b 11 10 8 Y 128 0 63 2327def if_______b_b 11 10 8 Y 128 0 63 2328def ifnull___b_b 11 10 8 Y 128 0 63 2329def least____b_b 11 10 8 Y 128 0 63 2330def greatest_b_b 11 10 8 Y 128 0 63 2331___________a 2010-01-01 00:00:00 2332case_______a 2010-01-01 00:00:00 2333case_____a_a 2010-01-01 00:00:00 2334coalesce___a 2010-01-01 00:00:00 2335coalesce_a_a 2010-01-01 00:00:00 2336if_______a_a 2010-01-01 00:00:00 2337ifnull___a_a 2010-01-01 00:00:00 2338least____a_a 2010-01-01 00:00:00 2339greatest_a_a 2010-01-01 00:00:00 2340___________b 10:20:30 2341case_______b 10:20:30 2342case_____b_b 10:20:30 2343coalesce___b 10:20:30 2344coalesce_b_b 10:20:30 2345if_______b_b 10:20:30 2346ifnull___b_b 10:20:30 2347least____b_b 10:20:30 2348greatest_b_b 10:20:30 2349SELECT 2350CASE WHEN a IS NOT NULL THEN a ELSE b END AS case_____a_b, 2351CASE WHEN a IS NOT NULL THEN b ELSE a END AS case_____b_a, 2352COALESCE(a, b) AS coalesce_a_b, 2353COALESCE(b, a) AS coalesce_b_a, 2354IF(a IS NULL, a, b) AS if_______a_b, 2355IF(a IS NULL, b, a) AS if_______b_a, 2356IFNULL(a, b) AS ifnull___a_b, 2357IFNULL(b, a) AS ifnull___b_a, 2358LEAST(a, b) AS least____a_b, 2359LEAST(b, a) AS least____b_a, 2360GREATEST(a, b) AS greatest_a_b, 2361GREATEST(b, a) AS greatest_b_a 2362FROM t1; 2363Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr 2364def case_____a_b 12 19 19 Y 128 0 63 2365def case_____b_a 12 19 19 Y 128 0 63 2366def coalesce_a_b 12 19 19 Y 128 0 63 2367def coalesce_b_a 12 19 19 Y 128 0 63 2368def if_______a_b 12 19 19 Y 128 0 63 2369def if_______b_a 12 19 19 Y 128 0 63 2370def ifnull___a_b 12 19 19 Y 128 0 63 2371def ifnull___b_a 12 19 19 N 129 0 63 2372def least____a_b 12 19 19 Y 128 0 63 2373def least____b_a 12 19 19 Y 128 0 63 2374def greatest_a_b 12 19 19 Y 128 0 63 2375def greatest_b_a 12 19 19 Y 128 0 63 2376case_____a_b 2010-01-01 00:00:00 2377case_____b_a 2001-01-01 10:20:30 2378coalesce_a_b 2010-01-01 00:00:00 2379coalesce_b_a 2001-01-01 10:20:30 2380if_______a_b 2001-01-01 10:20:30 2381if_______b_a 2010-01-01 00:00:00 2382ifnull___a_b 2010-01-01 00:00:00 2383ifnull___b_a 2001-01-01 10:20:30 2384least____a_b 2001-01-01 10:20:30 2385least____b_a 2001-01-01 10:20:30 2386greatest_a_b 2010-01-01 00:00:00 2387greatest_b_a 2010-01-01 00:00:00 2388CREATE TABLE t2 AS 2389SELECT 2390a AS ___________a, 2391CASE WHEN a IS NOT NULL THEN a END AS case_______a, 2392CASE WHEN a IS NOT NULL THEN a ELSE a END AS case_____a_a, 2393COALESCE(a) AS coalesce___a, 2394COALESCE(a, a) AS coalesce_a_a, 2395IF(a IS NULL, a, a) AS if_______a_a, 2396IFNULL(a, a) AS ifnull___a_a, 2397LEAST(a, a) AS least____a_a, 2398GREATEST(a, a) AS greatest_a_a, 2399b AS ___________b, 2400CASE WHEN a IS NOT NULL THEN b END AS case_______b, 2401CASE WHEN a IS NOT NULL THEN b ELSE b END AS case_____b_b, 2402COALESCE(b) AS coalesce___b, 2403COALESCE(b, b) AS coalesce_b_b, 2404IF(a IS NULL, b, b) AS if_______b_b, 2405IFNULL(b, b) AS ifnull___b_b, 2406LEAST(b, b) AS least____b_b, 2407GREATEST(b, b) AS greatest_b_b 2408FROM t1; 2409SHOW CREATE TABLE t2; 2410Table Create Table 2411t2 CREATE TABLE `t2` ( 2412 `___________a` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(), 2413 `case_______a` timestamp NULL DEFAULT NULL, 2414 `case_____a_a` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', 2415 `coalesce___a` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', 2416 `coalesce_a_a` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', 2417 `if_______a_a` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', 2418 `ifnull___a_a` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', 2419 `least____a_a` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', 2420 `greatest_a_a` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', 2421 `___________b` time DEFAULT NULL, 2422 `case_______b` time DEFAULT NULL, 2423 `case_____b_b` time DEFAULT NULL, 2424 `coalesce___b` time DEFAULT NULL, 2425 `coalesce_b_b` time DEFAULT NULL, 2426 `if_______b_b` time DEFAULT NULL, 2427 `ifnull___b_b` time DEFAULT NULL, 2428 `least____b_b` time DEFAULT NULL, 2429 `greatest_b_b` time DEFAULT NULL 2430) ENGINE=MyISAM DEFAULT CHARSET=latin1 2431DROP TABLE t2; 2432SET STATEMENT sql_mode = 'NO_ENGINE_SUBSTITUTION' FOR 2433CREATE TABLE t2 AS 2434SELECT 2435CASE WHEN a IS NOT NULL THEN a ELSE b END AS case_____a_b, 2436CASE WHEN a IS NOT NULL THEN b ELSE a END AS case_____b_a, 2437COALESCE(a, b) AS coalesce_a_b, 2438COALESCE(b, a) AS coalesce_b_a, 2439IF(a IS NULL, a, b) AS if_______a_b, 2440IF(a IS NULL, b, a) AS if_______b_a, 2441IFNULL(a, b) AS ifnull___a_b, 2442IFNULL(b, a) AS ifnull___b_a, 2443LEAST(a, b) AS least____a_b, 2444LEAST(b, a) AS least____b_a, 2445GREATEST(a, b) AS greatest_a_b, 2446GREATEST(b, a) AS greatest_b_a 2447FROM t1; 2448SHOW CREATE TABLE t2; 2449Table Create Table 2450t2 CREATE TABLE `t2` ( 2451 `case_____a_b` datetime DEFAULT NULL, 2452 `case_____b_a` datetime DEFAULT NULL, 2453 `coalesce_a_b` datetime DEFAULT NULL, 2454 `coalesce_b_a` datetime DEFAULT NULL, 2455 `if_______a_b` datetime DEFAULT NULL, 2456 `if_______b_a` datetime DEFAULT NULL, 2457 `ifnull___a_b` datetime DEFAULT NULL, 2458 `ifnull___b_a` datetime NOT NULL, 2459 `least____a_b` datetime DEFAULT NULL, 2460 `least____b_a` datetime DEFAULT NULL, 2461 `greatest_a_b` datetime DEFAULT NULL, 2462 `greatest_b_a` datetime DEFAULT NULL 2463) ENGINE=MyISAM DEFAULT CHARSET=latin1 2464DROP TABLE t2; 2465DROP TABLE t1; 2466SET timestamp=DEFAULT; 2467# 2468SET timestamp=UNIX_TIMESTAMP('2001-01-01 01:02:03'); 2469CREATE TABLE t1 (a DATETIME, b TIME); 2470INSERT INTO t1 VALUES ('2010-01-01 00:00:00','10:20:30'); 2471SELECT 2472a AS ___________a, 2473CASE WHEN a IS NOT NULL THEN a END AS case_______a, 2474CASE WHEN a IS NOT NULL THEN a ELSE a END AS case_____a_a, 2475COALESCE(a) AS coalesce___a, 2476COALESCE(a, a) AS coalesce_a_a, 2477IF(a IS NULL, a, a) AS if_______a_a, 2478IFNULL(a, a) AS ifnull___a_a, 2479LEAST(a, a) AS least____a_a, 2480GREATEST(a, a) AS greatest_a_a, 2481b AS ___________b, 2482CASE WHEN a IS NOT NULL THEN b END AS case_______b, 2483CASE WHEN a IS NOT NULL THEN b ELSE b END AS case_____b_b, 2484COALESCE(b) AS coalesce___b, 2485COALESCE(b, b) AS coalesce_b_b, 2486IF(a IS NULL, b, b) AS if_______b_b, 2487IFNULL(b, b) AS ifnull___b_b, 2488LEAST(b, b) AS least____b_b, 2489GREATEST(b, b) AS greatest_b_b 2490FROM t1; 2491Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr 2492def test t1 t1 a ___________a 12 19 19 Y 128 0 63 2493def case_______a 12 19 19 Y 128 0 63 2494def case_____a_a 12 19 19 Y 128 0 63 2495def coalesce___a 12 19 19 Y 128 0 63 2496def coalesce_a_a 12 19 19 Y 128 0 63 2497def if_______a_a 12 19 19 Y 128 0 63 2498def ifnull___a_a 12 19 19 Y 128 0 63 2499def least____a_a 12 19 19 Y 128 0 63 2500def greatest_a_a 12 19 19 Y 128 0 63 2501def test t1 t1 b ___________b 11 10 8 Y 128 0 63 2502def case_______b 11 10 8 Y 128 0 63 2503def case_____b_b 11 10 8 Y 128 0 63 2504def coalesce___b 11 10 8 Y 128 0 63 2505def coalesce_b_b 11 10 8 Y 128 0 63 2506def if_______b_b 11 10 8 Y 128 0 63 2507def ifnull___b_b 11 10 8 Y 128 0 63 2508def least____b_b 11 10 8 Y 128 0 63 2509def greatest_b_b 11 10 8 Y 128 0 63 2510___________a 2010-01-01 00:00:00 2511case_______a 2010-01-01 00:00:00 2512case_____a_a 2010-01-01 00:00:00 2513coalesce___a 2010-01-01 00:00:00 2514coalesce_a_a 2010-01-01 00:00:00 2515if_______a_a 2010-01-01 00:00:00 2516ifnull___a_a 2010-01-01 00:00:00 2517least____a_a 2010-01-01 00:00:00 2518greatest_a_a 2010-01-01 00:00:00 2519___________b 10:20:30 2520case_______b 10:20:30 2521case_____b_b 10:20:30 2522coalesce___b 10:20:30 2523coalesce_b_b 10:20:30 2524if_______b_b 10:20:30 2525ifnull___b_b 10:20:30 2526least____b_b 10:20:30 2527greatest_b_b 10:20:30 2528SELECT 2529CASE WHEN a IS NOT NULL THEN a ELSE b END AS case_____a_b, 2530CASE WHEN a IS NOT NULL THEN b ELSE a END AS case_____b_a, 2531COALESCE(a, b) AS coalesce_a_b, 2532COALESCE(b, a) AS coalesce_b_a, 2533IF(a IS NULL, a, b) AS if_______a_b, 2534IF(a IS NULL, b, a) AS if_______b_a, 2535IFNULL(a, b) AS ifnull___a_b, 2536IFNULL(b, a) AS ifnull___b_a, 2537LEAST(a, b) AS least____a_b, 2538LEAST(b, a) AS least____b_a, 2539GREATEST(a, b) AS greatest_a_b, 2540GREATEST(b, a) AS greatest_b_a 2541FROM t1; 2542Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr 2543def case_____a_b 12 19 19 Y 128 0 63 2544def case_____b_a 12 19 19 Y 128 0 63 2545def coalesce_a_b 12 19 19 Y 128 0 63 2546def coalesce_b_a 12 19 19 Y 128 0 63 2547def if_______a_b 12 19 19 Y 128 0 63 2548def if_______b_a 12 19 19 Y 128 0 63 2549def ifnull___a_b 12 19 19 Y 128 0 63 2550def ifnull___b_a 12 19 19 Y 128 0 63 2551def least____a_b 12 19 19 Y 128 0 63 2552def least____b_a 12 19 19 Y 128 0 63 2553def greatest_a_b 12 19 19 Y 128 0 63 2554def greatest_b_a 12 19 19 Y 128 0 63 2555case_____a_b 2010-01-01 00:00:00 2556case_____b_a 2001-01-01 10:20:30 2557coalesce_a_b 2010-01-01 00:00:00 2558coalesce_b_a 2001-01-01 10:20:30 2559if_______a_b 2001-01-01 10:20:30 2560if_______b_a 2010-01-01 00:00:00 2561ifnull___a_b 2010-01-01 00:00:00 2562ifnull___b_a 2001-01-01 10:20:30 2563least____a_b 2001-01-01 10:20:30 2564least____b_a 2001-01-01 10:20:30 2565greatest_a_b 2010-01-01 00:00:00 2566greatest_b_a 2010-01-01 00:00:00 2567CREATE TABLE t2 AS 2568SELECT 2569a AS ___________a, 2570CASE WHEN a IS NOT NULL THEN a END AS case_______a, 2571CASE WHEN a IS NOT NULL THEN a ELSE a END AS case_____a_a, 2572COALESCE(a) AS coalesce___a, 2573COALESCE(a, a) AS coalesce_a_a, 2574IF(a IS NULL, a, a) AS if_______a_a, 2575IFNULL(a, a) AS ifnull___a_a, 2576LEAST(a, a) AS least____a_a, 2577GREATEST(a, a) AS greatest_a_a, 2578b AS ___________b, 2579CASE WHEN a IS NOT NULL THEN b END AS case_______b, 2580CASE WHEN a IS NOT NULL THEN b ELSE b END AS case_____b_b, 2581COALESCE(b) AS coalesce___b, 2582COALESCE(b, b) AS coalesce_b_b, 2583IF(a IS NULL, b, b) AS if_______b_b, 2584IFNULL(b, b) AS ifnull___b_b, 2585LEAST(b, b) AS least____b_b, 2586GREATEST(b, b) AS greatest_b_b 2587FROM t1; 2588SHOW CREATE TABLE t2; 2589Table Create Table 2590t2 CREATE TABLE `t2` ( 2591 `___________a` datetime DEFAULT NULL, 2592 `case_______a` datetime DEFAULT NULL, 2593 `case_____a_a` datetime DEFAULT NULL, 2594 `coalesce___a` datetime DEFAULT NULL, 2595 `coalesce_a_a` datetime DEFAULT NULL, 2596 `if_______a_a` datetime DEFAULT NULL, 2597 `ifnull___a_a` datetime DEFAULT NULL, 2598 `least____a_a` datetime DEFAULT NULL, 2599 `greatest_a_a` datetime DEFAULT NULL, 2600 `___________b` time DEFAULT NULL, 2601 `case_______b` time DEFAULT NULL, 2602 `case_____b_b` time DEFAULT NULL, 2603 `coalesce___b` time DEFAULT NULL, 2604 `coalesce_b_b` time DEFAULT NULL, 2605 `if_______b_b` time DEFAULT NULL, 2606 `ifnull___b_b` time DEFAULT NULL, 2607 `least____b_b` time DEFAULT NULL, 2608 `greatest_b_b` time DEFAULT NULL 2609) ENGINE=MyISAM DEFAULT CHARSET=latin1 2610DROP TABLE t2; 2611SET STATEMENT sql_mode = 'NO_ENGINE_SUBSTITUTION' FOR 2612CREATE TABLE t2 AS 2613SELECT 2614CASE WHEN a IS NOT NULL THEN a ELSE b END AS case_____a_b, 2615CASE WHEN a IS NOT NULL THEN b ELSE a END AS case_____b_a, 2616COALESCE(a, b) AS coalesce_a_b, 2617COALESCE(b, a) AS coalesce_b_a, 2618IF(a IS NULL, a, b) AS if_______a_b, 2619IF(a IS NULL, b, a) AS if_______b_a, 2620IFNULL(a, b) AS ifnull___a_b, 2621IFNULL(b, a) AS ifnull___b_a, 2622LEAST(a, b) AS least____a_b, 2623LEAST(b, a) AS least____b_a, 2624GREATEST(a, b) AS greatest_a_b, 2625GREATEST(b, a) AS greatest_b_a 2626FROM t1; 2627SHOW CREATE TABLE t2; 2628Table Create Table 2629t2 CREATE TABLE `t2` ( 2630 `case_____a_b` datetime DEFAULT NULL, 2631 `case_____b_a` datetime DEFAULT NULL, 2632 `coalesce_a_b` datetime DEFAULT NULL, 2633 `coalesce_b_a` datetime DEFAULT NULL, 2634 `if_______a_b` datetime DEFAULT NULL, 2635 `if_______b_a` datetime DEFAULT NULL, 2636 `ifnull___a_b` datetime DEFAULT NULL, 2637 `ifnull___b_a` datetime DEFAULT NULL, 2638 `least____a_b` datetime DEFAULT NULL, 2639 `least____b_a` datetime DEFAULT NULL, 2640 `greatest_a_b` datetime DEFAULT NULL, 2641 `greatest_b_a` datetime DEFAULT NULL 2642) ENGINE=MyISAM DEFAULT CHARSET=latin1 2643DROP TABLE t2; 2644DROP TABLE t1; 2645SET timestamp=DEFAULT; 2646# 2647SET timestamp=UNIX_TIMESTAMP('2001-01-01 01:02:03'); 2648CREATE TABLE t1 (a DATETIME, b DATE); 2649INSERT INTO t1 VALUES ('2010-01-01 10:20:30','2001-01-02'); 2650SELECT 2651a AS ___________a, 2652CASE WHEN a IS NOT NULL THEN a END AS case_______a, 2653CASE WHEN a IS NOT NULL THEN a ELSE a END AS case_____a_a, 2654COALESCE(a) AS coalesce___a, 2655COALESCE(a, a) AS coalesce_a_a, 2656IF(a IS NULL, a, a) AS if_______a_a, 2657IFNULL(a, a) AS ifnull___a_a, 2658LEAST(a, a) AS least____a_a, 2659GREATEST(a, a) AS greatest_a_a, 2660b AS ___________b, 2661CASE WHEN a IS NOT NULL THEN b END AS case_______b, 2662CASE WHEN a IS NOT NULL THEN b ELSE b END AS case_____b_b, 2663COALESCE(b) AS coalesce___b, 2664COALESCE(b, b) AS coalesce_b_b, 2665IF(a IS NULL, b, b) AS if_______b_b, 2666IFNULL(b, b) AS ifnull___b_b, 2667LEAST(b, b) AS least____b_b, 2668GREATEST(b, b) AS greatest_b_b 2669FROM t1; 2670Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr 2671def test t1 t1 a ___________a 12 19 19 Y 128 0 63 2672def case_______a 12 19 19 Y 128 0 63 2673def case_____a_a 12 19 19 Y 128 0 63 2674def coalesce___a 12 19 19 Y 128 0 63 2675def coalesce_a_a 12 19 19 Y 128 0 63 2676def if_______a_a 12 19 19 Y 128 0 63 2677def ifnull___a_a 12 19 19 Y 128 0 63 2678def least____a_a 12 19 19 Y 128 0 63 2679def greatest_a_a 12 19 19 Y 128 0 63 2680def test t1 t1 b ___________b 10 10 10 Y 128 0 63 2681def case_______b 10 10 10 Y 128 0 63 2682def case_____b_b 10 10 10 Y 128 0 63 2683def coalesce___b 10 10 10 Y 128 0 63 2684def coalesce_b_b 10 10 10 Y 128 0 63 2685def if_______b_b 10 10 10 Y 128 0 63 2686def ifnull___b_b 10 10 10 Y 128 0 63 2687def least____b_b 10 10 10 Y 128 0 63 2688def greatest_b_b 10 10 10 Y 128 0 63 2689___________a 2010-01-01 10:20:30 2690case_______a 2010-01-01 10:20:30 2691case_____a_a 2010-01-01 10:20:30 2692coalesce___a 2010-01-01 10:20:30 2693coalesce_a_a 2010-01-01 10:20:30 2694if_______a_a 2010-01-01 10:20:30 2695ifnull___a_a 2010-01-01 10:20:30 2696least____a_a 2010-01-01 10:20:30 2697greatest_a_a 2010-01-01 10:20:30 2698___________b 2001-01-02 2699case_______b 2001-01-02 2700case_____b_b 2001-01-02 2701coalesce___b 2001-01-02 2702coalesce_b_b 2001-01-02 2703if_______b_b 2001-01-02 2704ifnull___b_b 2001-01-02 2705least____b_b 2001-01-02 2706greatest_b_b 2001-01-02 2707SELECT 2708CASE WHEN a IS NOT NULL THEN a ELSE b END AS case_____a_b, 2709CASE WHEN a IS NOT NULL THEN b ELSE a END AS case_____b_a, 2710COALESCE(a, b) AS coalesce_a_b, 2711COALESCE(b, a) AS coalesce_b_a, 2712IF(a IS NULL, a, b) AS if_______a_b, 2713IF(a IS NULL, b, a) AS if_______b_a, 2714IFNULL(a, b) AS ifnull___a_b, 2715IFNULL(b, a) AS ifnull___b_a, 2716LEAST(a, b) AS least____a_b, 2717LEAST(b, a) AS least____b_a, 2718GREATEST(a, b) AS greatest_a_b, 2719GREATEST(b, a) AS greatest_b_a 2720FROM t1; 2721Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr 2722def case_____a_b 12 19 19 Y 128 0 63 2723def case_____b_a 12 19 19 Y 128 0 63 2724def coalesce_a_b 12 19 19 Y 128 0 63 2725def coalesce_b_a 12 19 19 Y 128 0 63 2726def if_______a_b 12 19 19 Y 128 0 63 2727def if_______b_a 12 19 19 Y 128 0 63 2728def ifnull___a_b 12 19 19 Y 128 0 63 2729def ifnull___b_a 12 19 19 Y 128 0 63 2730def least____a_b 12 19 19 Y 128 0 63 2731def least____b_a 12 19 19 Y 128 0 63 2732def greatest_a_b 12 19 19 Y 128 0 63 2733def greatest_b_a 12 19 19 Y 128 0 63 2734case_____a_b 2010-01-01 10:20:30 2735case_____b_a 2001-01-02 00:00:00 2736coalesce_a_b 2010-01-01 10:20:30 2737coalesce_b_a 2001-01-02 00:00:00 2738if_______a_b 2001-01-02 00:00:00 2739if_______b_a 2010-01-01 10:20:30 2740ifnull___a_b 2010-01-01 10:20:30 2741ifnull___b_a 2001-01-02 00:00:00 2742least____a_b 2001-01-02 00:00:00 2743least____b_a 2001-01-02 00:00:00 2744greatest_a_b 2010-01-01 10:20:30 2745greatest_b_a 2010-01-01 10:20:30 2746CREATE TABLE t2 AS 2747SELECT 2748a AS ___________a, 2749CASE WHEN a IS NOT NULL THEN a END AS case_______a, 2750CASE WHEN a IS NOT NULL THEN a ELSE a END AS case_____a_a, 2751COALESCE(a) AS coalesce___a, 2752COALESCE(a, a) AS coalesce_a_a, 2753IF(a IS NULL, a, a) AS if_______a_a, 2754IFNULL(a, a) AS ifnull___a_a, 2755LEAST(a, a) AS least____a_a, 2756GREATEST(a, a) AS greatest_a_a, 2757b AS ___________b, 2758CASE WHEN a IS NOT NULL THEN b END AS case_______b, 2759CASE WHEN a IS NOT NULL THEN b ELSE b END AS case_____b_b, 2760COALESCE(b) AS coalesce___b, 2761COALESCE(b, b) AS coalesce_b_b, 2762IF(a IS NULL, b, b) AS if_______b_b, 2763IFNULL(b, b) AS ifnull___b_b, 2764LEAST(b, b) AS least____b_b, 2765GREATEST(b, b) AS greatest_b_b 2766FROM t1; 2767SHOW CREATE TABLE t2; 2768Table Create Table 2769t2 CREATE TABLE `t2` ( 2770 `___________a` datetime DEFAULT NULL, 2771 `case_______a` datetime DEFAULT NULL, 2772 `case_____a_a` datetime DEFAULT NULL, 2773 `coalesce___a` datetime DEFAULT NULL, 2774 `coalesce_a_a` datetime DEFAULT NULL, 2775 `if_______a_a` datetime DEFAULT NULL, 2776 `ifnull___a_a` datetime DEFAULT NULL, 2777 `least____a_a` datetime DEFAULT NULL, 2778 `greatest_a_a` datetime DEFAULT NULL, 2779 `___________b` date DEFAULT NULL, 2780 `case_______b` date DEFAULT NULL, 2781 `case_____b_b` date DEFAULT NULL, 2782 `coalesce___b` date DEFAULT NULL, 2783 `coalesce_b_b` date DEFAULT NULL, 2784 `if_______b_b` date DEFAULT NULL, 2785 `ifnull___b_b` date DEFAULT NULL, 2786 `least____b_b` date DEFAULT NULL, 2787 `greatest_b_b` date DEFAULT NULL 2788) ENGINE=MyISAM DEFAULT CHARSET=latin1 2789DROP TABLE t2; 2790SET STATEMENT sql_mode = 'NO_ENGINE_SUBSTITUTION' FOR 2791CREATE TABLE t2 AS 2792SELECT 2793CASE WHEN a IS NOT NULL THEN a ELSE b END AS case_____a_b, 2794CASE WHEN a IS NOT NULL THEN b ELSE a END AS case_____b_a, 2795COALESCE(a, b) AS coalesce_a_b, 2796COALESCE(b, a) AS coalesce_b_a, 2797IF(a IS NULL, a, b) AS if_______a_b, 2798IF(a IS NULL, b, a) AS if_______b_a, 2799IFNULL(a, b) AS ifnull___a_b, 2800IFNULL(b, a) AS ifnull___b_a, 2801LEAST(a, b) AS least____a_b, 2802LEAST(b, a) AS least____b_a, 2803GREATEST(a, b) AS greatest_a_b, 2804GREATEST(b, a) AS greatest_b_a 2805FROM t1; 2806SHOW CREATE TABLE t2; 2807Table Create Table 2808t2 CREATE TABLE `t2` ( 2809 `case_____a_b` datetime DEFAULT NULL, 2810 `case_____b_a` datetime DEFAULT NULL, 2811 `coalesce_a_b` datetime DEFAULT NULL, 2812 `coalesce_b_a` datetime DEFAULT NULL, 2813 `if_______a_b` datetime DEFAULT NULL, 2814 `if_______b_a` datetime DEFAULT NULL, 2815 `ifnull___a_b` datetime DEFAULT NULL, 2816 `ifnull___b_a` datetime DEFAULT NULL, 2817 `least____a_b` datetime DEFAULT NULL, 2818 `least____b_a` datetime DEFAULT NULL, 2819 `greatest_a_b` datetime DEFAULT NULL, 2820 `greatest_b_a` datetime DEFAULT NULL 2821) ENGINE=MyISAM DEFAULT CHARSET=latin1 2822DROP TABLE t2; 2823DROP TABLE t1; 2824SET timestamp=DEFAULT; 2825# 2826# MDEV-8873 Wrong field type or metadata for LEAST(int_column,string_column) 2827# 2828CREATE TABLE t1 (a INT, b VARCHAR(10)); 2829INSERT INTO t1 VALUES (-2147483648,'100x'); 2830SELECT 2831a AS ___________a, 2832CASE WHEN a IS NOT NULL THEN a END AS case_______a, 2833CASE WHEN a IS NOT NULL THEN a ELSE a END AS case_____a_a, 2834COALESCE(a) AS coalesce___a, 2835COALESCE(a, a) AS coalesce_a_a, 2836IF(a IS NULL, a, a) AS if_______a_a, 2837IFNULL(a, a) AS ifnull___a_a, 2838LEAST(a, a) AS least____a_a, 2839GREATEST(a, a) AS greatest_a_a, 2840b AS ___________b, 2841CASE WHEN a IS NOT NULL THEN b END AS case_______b, 2842CASE WHEN a IS NOT NULL THEN b ELSE b END AS case_____b_b, 2843COALESCE(b) AS coalesce___b, 2844COALESCE(b, b) AS coalesce_b_b, 2845IF(a IS NULL, b, b) AS if_______b_b, 2846IFNULL(b, b) AS ifnull___b_b, 2847LEAST(b, b) AS least____b_b, 2848GREATEST(b, b) AS greatest_b_b 2849FROM t1; 2850Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr 2851def test t1 t1 a ___________a 3 11 11 Y 32768 0 63 2852def case_______a 3 11 11 Y 32896 0 63 2853def case_____a_a 3 11 11 Y 32896 0 63 2854def coalesce___a 3 11 11 Y 32896 0 63 2855def coalesce_a_a 3 11 11 Y 32896 0 63 2856def if_______a_a 3 11 11 Y 32896 0 63 2857def ifnull___a_a 3 11 11 Y 32896 0 63 2858def least____a_a 3 11 11 Y 32896 0 63 2859def greatest_a_a 3 11 11 Y 32896 0 63 2860def test t1 t1 b ___________b 253 10 4 Y 0 0 8 2861def case_______b 253 10 4 Y 0 39 8 2862def case_____b_b 253 10 4 Y 0 39 8 2863def coalesce___b 253 10 4 Y 0 39 8 2864def coalesce_b_b 253 10 4 Y 0 39 8 2865def if_______b_b 253 10 4 Y 0 39 8 2866def ifnull___b_b 253 10 4 Y 0 39 8 2867def least____b_b 253 10 4 Y 0 39 8 2868def greatest_b_b 253 10 4 Y 0 39 8 2869___________a -2147483648 2870case_______a -2147483648 2871case_____a_a -2147483648 2872coalesce___a -2147483648 2873coalesce_a_a -2147483648 2874if_______a_a -2147483648 2875ifnull___a_a -2147483648 2876least____a_a -2147483648 2877greatest_a_a -2147483648 2878___________b 100x 2879case_______b 100x 2880case_____b_b 100x 2881coalesce___b 100x 2882coalesce_b_b 100x 2883if_______b_b 100x 2884ifnull___b_b 100x 2885least____b_b 100x 2886greatest_b_b 100x 2887SELECT 2888CASE WHEN a IS NOT NULL THEN a ELSE b END AS case_____a_b, 2889CASE WHEN a IS NOT NULL THEN b ELSE a END AS case_____b_a, 2890COALESCE(a, b) AS coalesce_a_b, 2891COALESCE(b, a) AS coalesce_b_a, 2892IF(a IS NULL, a, b) AS if_______a_b, 2893IF(a IS NULL, b, a) AS if_______b_a, 2894IFNULL(a, b) AS ifnull___a_b, 2895IFNULL(b, a) AS ifnull___b_a, 2896LEAST(a, b) AS least____a_b, 2897LEAST(b, a) AS least____b_a, 2898GREATEST(a, b) AS greatest_a_b, 2899GREATEST(b, a) AS greatest_b_a 2900FROM t1; 2901Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr 2902def case_____a_b 253 11 11 Y 0 39 8 2903def case_____b_a 253 11 4 Y 0 39 8 2904def coalesce_a_b 253 11 11 Y 0 39 8 2905def coalesce_b_a 253 11 4 Y 0 39 8 2906def if_______a_b 253 11 4 Y 0 39 8 2907def if_______b_a 253 11 11 Y 0 39 8 2908def ifnull___a_b 253 11 11 Y 0 39 8 2909def ifnull___b_a 253 11 4 Y 0 39 8 2910def least____a_b 5 23 11 Y 32896 31 63 2911def least____b_a 5 23 11 Y 32896 31 63 2912def greatest_a_b 5 23 3 Y 32896 31 63 2913def greatest_b_a 5 23 3 Y 32896 31 63 2914case_____a_b -2147483648 2915case_____b_a 100x 2916coalesce_a_b -2147483648 2917coalesce_b_a 100x 2918if_______a_b 100x 2919if_______b_a -2147483648 2920ifnull___a_b -2147483648 2921ifnull___b_a 100x 2922least____a_b -2147483648 2923least____b_a -2147483648 2924greatest_a_b 100 2925greatest_b_a 100 2926Warnings: 2927Level Warning 2928Code 1292 2929Message Truncated incorrect DOUBLE value: '100x' 2930Level Warning 2931Code 1292 2932Message Truncated incorrect DOUBLE value: '100x' 2933Level Warning 2934Code 1292 2935Message Truncated incorrect DOUBLE value: '100x' 2936Level Warning 2937Code 1292 2938Message Truncated incorrect DOUBLE value: '100x' 2939CREATE TABLE t2 AS 2940SELECT 2941a AS ___________a, 2942CASE WHEN a IS NOT NULL THEN a END AS case_______a, 2943CASE WHEN a IS NOT NULL THEN a ELSE a END AS case_____a_a, 2944COALESCE(a) AS coalesce___a, 2945COALESCE(a, a) AS coalesce_a_a, 2946IF(a IS NULL, a, a) AS if_______a_a, 2947IFNULL(a, a) AS ifnull___a_a, 2948LEAST(a, a) AS least____a_a, 2949GREATEST(a, a) AS greatest_a_a, 2950b AS ___________b, 2951CASE WHEN a IS NOT NULL THEN b END AS case_______b, 2952CASE WHEN a IS NOT NULL THEN b ELSE b END AS case_____b_b, 2953COALESCE(b) AS coalesce___b, 2954COALESCE(b, b) AS coalesce_b_b, 2955IF(a IS NULL, b, b) AS if_______b_b, 2956IFNULL(b, b) AS ifnull___b_b, 2957LEAST(b, b) AS least____b_b, 2958GREATEST(b, b) AS greatest_b_b 2959FROM t1; 2960SHOW CREATE TABLE t2; 2961Table Create Table 2962t2 CREATE TABLE `t2` ( 2963 `___________a` int(11) DEFAULT NULL, 2964 `case_______a` int(11) DEFAULT NULL, 2965 `case_____a_a` int(11) DEFAULT NULL, 2966 `coalesce___a` int(11) DEFAULT NULL, 2967 `coalesce_a_a` int(11) DEFAULT NULL, 2968 `if_______a_a` int(11) DEFAULT NULL, 2969 `ifnull___a_a` int(11) DEFAULT NULL, 2970 `least____a_a` int(11) DEFAULT NULL, 2971 `greatest_a_a` int(11) DEFAULT NULL, 2972 `___________b` varchar(10) DEFAULT NULL, 2973 `case_______b` varchar(10) DEFAULT NULL, 2974 `case_____b_b` varchar(10) DEFAULT NULL, 2975 `coalesce___b` varchar(10) DEFAULT NULL, 2976 `coalesce_b_b` varchar(10) DEFAULT NULL, 2977 `if_______b_b` varchar(10) DEFAULT NULL, 2978 `ifnull___b_b` varchar(10) DEFAULT NULL, 2979 `least____b_b` varchar(10) DEFAULT NULL, 2980 `greatest_b_b` varchar(10) DEFAULT NULL 2981) ENGINE=MyISAM DEFAULT CHARSET=latin1 2982DROP TABLE t2; 2983SET STATEMENT sql_mode = 'NO_ENGINE_SUBSTITUTION' FOR 2984CREATE TABLE t2 AS 2985SELECT 2986CASE WHEN a IS NOT NULL THEN a ELSE b END AS case_____a_b, 2987CASE WHEN a IS NOT NULL THEN b ELSE a END AS case_____b_a, 2988COALESCE(a, b) AS coalesce_a_b, 2989COALESCE(b, a) AS coalesce_b_a, 2990IF(a IS NULL, a, b) AS if_______a_b, 2991IF(a IS NULL, b, a) AS if_______b_a, 2992IFNULL(a, b) AS ifnull___a_b, 2993IFNULL(b, a) AS ifnull___b_a, 2994LEAST(a, b) AS least____a_b, 2995LEAST(b, a) AS least____b_a, 2996GREATEST(a, b) AS greatest_a_b, 2997GREATEST(b, a) AS greatest_b_a 2998FROM t1; 2999Warnings: 3000Warning 1292 Truncated incorrect DOUBLE value: '100x' 3001Warning 1292 Truncated incorrect DOUBLE value: '100x' 3002Warning 1292 Truncated incorrect DOUBLE value: '100x' 3003Warning 1292 Truncated incorrect DOUBLE value: '100x' 3004SHOW CREATE TABLE t2; 3005Table Create Table 3006t2 CREATE TABLE `t2` ( 3007 `case_____a_b` varchar(11) DEFAULT NULL, 3008 `case_____b_a` varchar(11) DEFAULT NULL, 3009 `coalesce_a_b` varchar(11) DEFAULT NULL, 3010 `coalesce_b_a` varchar(11) DEFAULT NULL, 3011 `if_______a_b` varchar(11) DEFAULT NULL, 3012 `if_______b_a` varchar(11) DEFAULT NULL, 3013 `ifnull___a_b` varchar(11) DEFAULT NULL, 3014 `ifnull___b_a` varchar(11) DEFAULT NULL, 3015 `least____a_b` double DEFAULT NULL, 3016 `least____b_a` double DEFAULT NULL, 3017 `greatest_a_b` double DEFAULT NULL, 3018 `greatest_b_a` double DEFAULT NULL 3019) ENGINE=MyISAM DEFAULT CHARSET=latin1 3020DROP TABLE t2; 3021DROP TABLE t1; 3022# 3023# MDEV-4848 Wrong metadata or column type for LEAST(1.0,'10') 3024# 3025SELECT LEAST(1.0,'10'); 3026Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr 3027def LEAST(1.0,'10') 5 23 1 N 32897 31 63 3028LEAST(1.0,'10') 30291 3030CREATE TABLE t1 AS SELECT LEAST(1.0,'10'); 3031SHOW CREATE TABLE t1; 3032Table Create Table 3033t1 CREATE TABLE `t1` ( 3034 `LEAST(1.0,'10')` double NOT NULL 3035) ENGINE=MyISAM DEFAULT CHARSET=latin1 3036DROP TABLE t1; 3037# 3038# MDEV-657 LP:873142 - GREATEST() does not always return same signness of argument types 3039# 3040CREATE TABLE t1 (a BIGINT(20) UNSIGNED NOT NULL PRIMARY KEY); 3041INSERT INTO t1 (a) VALUES (13836376518955650385) ON DUPLICATE KEY UPDATE a=GREATEST(a,VALUES(a)); 3042INSERT INTO t1 (a) VALUES (13836376518955650385) ON DUPLICATE KEY UPDATE a=GREATEST(a,VALUES(a)); 3043SELECT * FROM t1; 3044a 304513836376518955650385 3046DROP TABLE t1; 3047# 3048# MDEV-5694 GREATEST(date, time) returns a wrong data type 3049# 3050SET timestamp=UNIX_TIMESTAMP('2010-01-01 01:02:03'); 3051SELECT GREATEST(CURRENT_TIME, CURRENT_DATE), COALESCE(CURRENT_TIME, CURRENT_DATE); 3052Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr 3053def GREATEST(CURRENT_TIME, CURRENT_DATE) 12 19 19 N 129 0 63 3054def COALESCE(CURRENT_TIME, CURRENT_DATE) 12 19 19 N 129 0 63 3055GREATEST(CURRENT_TIME, CURRENT_DATE) COALESCE(CURRENT_TIME, CURRENT_DATE) 30562010-01-01 01:02:03 2010-01-01 01:02:03 3057CREATE TABLE t1 (a TIMESTAMP); 3058INSERT INTO t1 VALUES ('2010-01-01 10:20:30'); 3059SELECT GREATEST(a,a) FROM t1; 3060Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr 3061def GREATEST(a,a) 7 19 19 N 129 0 63 3062GREATEST(a,a) 30632010-01-01 10:20:30 3064SELECT COALESCE(a,a) FROM t1; 3065Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr 3066def COALESCE(a,a) 7 19 19 N 129 0 63 3067COALESCE(a,a) 30682010-01-01 10:20:30 3069DROP TABLE t1; 3070CREATE TABLE t1 (a TIMESTAMP, b DATETIME); 3071CREATE TABLE t2 AS SELECT LEAST(a,a),LEAST(b,b),LEAST(a,b) FROM t1; 3072SHOW CREATE TABLE t2; 3073Table Create Table 3074t2 CREATE TABLE `t2` ( 3075 `LEAST(a,a)` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', 3076 `LEAST(b,b)` datetime DEFAULT NULL, 3077 `LEAST(a,b)` datetime DEFAULT NULL 3078) ENGINE=MyISAM DEFAULT CHARSET=latin1 3079DROP TABLE t2; 3080DROP TABLE t1; 3081SET timestamp=DEFAULT; 3082# 3083# MDEV-8910 Wrong metadata or field type for MAX(COALESCE(string_field)) 3084# 3085CREATE TABLE t1 (c1 TINYBLOB, c2 MEDIUMBLOB, c3 BLOB, c4 LONGBLOB); 3086CREATE TABLE t2 AS 3087SELECT 3088MAX(COALESCE(c1)) AS c1, 3089MAX(COALESCE(c2)) AS c2, 3090MAX(COALESCE(c3)) AS c3, 3091MAX(COALESCE(c4)) AS c4 3092FROM t1; 3093SHOW CREATE TABLE t2; 3094Table Create Table 3095t2 CREATE TABLE `t2` ( 3096 `c1` varbinary(255) DEFAULT NULL, 3097 `c2` mediumblob DEFAULT NULL, 3098 `c3` blob DEFAULT NULL, 3099 `c4` longblob DEFAULT NULL 3100) ENGINE=MyISAM DEFAULT CHARSET=latin1 3101SELECT 3102MAX(COALESCE(c1)) AS c1, 3103MAX(COALESCE(c2)) AS c2, 3104MAX(COALESCE(c3)) AS c3, 3105MAX(COALESCE(c4)) AS c4 3106FROM t1; 3107Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr 3108def c1 253 255 0 Y 128 39 63 3109def c2 250 16777215 0 Y 128 39 63 3110def c3 252 65535 0 Y 128 39 63 3111def c4 251 4294967295 0 Y 128 39 63 3112c1 c2 c3 c4 3113NULL NULL NULL NULL 3114DROP TABLE t2; 3115DROP TABLE t1; 3116CREATE TABLE t1 (c1 CHAR(1), c2 CHAR(255)) CHARACTER SET latin1; 3117CREATE TABLE t2 AS 3118SELECT 3119MAX(COALESCE(c1)) AS c1, 3120MAX(COALESCE(c2)) AS c2 3121FROM t1; 3122SHOW CREATE TABLE t2; 3123Table Create Table 3124t2 CREATE TABLE `t2` ( 3125 `c1` varchar(1) DEFAULT NULL, 3126 `c2` varchar(255) DEFAULT NULL 3127) ENGINE=MyISAM DEFAULT CHARSET=latin1 3128SELECT 3129MAX(COALESCE(c1)) AS c1, 3130MAX(COALESCE(c2)) AS c2 3131FROM t1; 3132Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr 3133def c1 253 1 0 Y 0 39 8 3134def c2 253 255 0 Y 0 39 8 3135c1 c2 3136NULL NULL 3137DROP TABLE t2; 3138DROP TABLE t1; 3139CREATE TABLE t1 (c1 CHAR(1), c2 CHAR(255)) CHARACTER SET utf8; 3140CREATE TABLE t2 AS 3141SELECT 3142MAX(COALESCE(c1)) AS c1, 3143MAX(COALESCE(c2)) AS c2 3144FROM t1; 3145SHOW CREATE TABLE t2; 3146Table Create Table 3147t2 CREATE TABLE `t2` ( 3148 `c1` varchar(1) CHARACTER SET utf8 DEFAULT NULL, 3149 `c2` varchar(255) CHARACTER SET utf8 DEFAULT NULL 3150) ENGINE=MyISAM DEFAULT CHARSET=latin1 3151SELECT 3152MAX(COALESCE(c1)) AS c1, 3153MAX(COALESCE(c2)) AS c2 3154FROM t1; 3155Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr 3156def c1 253 1 0 Y 0 39 8 3157def c2 253 255 0 Y 0 39 8 3158c1 c2 3159NULL NULL 3160DROP TABLE t2; 3161DROP TABLE t1; 3162CREATE TABLE t1 (c1 VARCHAR(1), c2 VARCHAR(255), c3 VARCHAR(20000)) CHARACTER SET latin1; 3163CREATE TABLE t2 AS 3164SELECT 3165MAX(COALESCE(c1)) AS c1, 3166MAX(COALESCE(c2)) AS c2, 3167MAX(COALESCE(c3)) AS c3 3168FROM t1; 3169SHOW CREATE TABLE t2; 3170Table Create Table 3171t2 CREATE TABLE `t2` ( 3172 `c1` varchar(1) DEFAULT NULL, 3173 `c2` varchar(255) DEFAULT NULL, 3174 `c3` text DEFAULT NULL 3175) ENGINE=MyISAM DEFAULT CHARSET=latin1 3176SELECT 3177MAX(COALESCE(c1)) AS c1, 3178MAX(COALESCE(c2)) AS c2, 3179MAX(COALESCE(c3)) AS c3 3180FROM t1; 3181Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr 3182def c1 253 1 0 Y 0 39 8 3183def c2 253 255 0 Y 0 39 8 3184def c3 252 20000 0 Y 0 39 8 3185c1 c2 c3 3186NULL NULL NULL 3187DROP TABLE t2; 3188DROP TABLE t1; 3189CREATE TABLE t1 (c1 VARCHAR(1), c2 VARCHAR(255), c3 VARCHAR(20000)) CHARACTER SET utf8; 3190CREATE TABLE t2 AS 3191SELECT 3192MAX(COALESCE(c1)) AS c1, 3193MAX(COALESCE(c2)) AS c2, 3194MAX(COALESCE(c3)) AS c3 3195FROM t1; 3196SHOW CREATE TABLE t2; 3197Table Create Table 3198t2 CREATE TABLE `t2` ( 3199 `c1` varchar(1) CHARACTER SET utf8 DEFAULT NULL, 3200 `c2` varchar(255) CHARACTER SET utf8 DEFAULT NULL, 3201 `c3` text CHARACTER SET utf8 DEFAULT NULL 3202) ENGINE=MyISAM DEFAULT CHARSET=latin1 3203SELECT 3204MAX(COALESCE(c1)) AS c1, 3205MAX(COALESCE(c2)) AS c2, 3206MAX(COALESCE(c3)) AS c3 3207FROM t1; 3208Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr 3209def c1 253 1 0 Y 0 39 8 3210def c2 253 255 0 Y 0 39 8 3211def c3 252 60000 0 Y 0 39 8 3212c1 c2 c3 3213NULL NULL NULL 3214DROP TABLE t2; 3215DROP TABLE t1; 3216CREATE TABLE t1 (c1 ENUM('a')) CHARACTER SET latin1; 3217CREATE TABLE t2 AS 3218SELECT 3219MAX(COALESCE(c1)) AS c1 3220FROM t1; 3221SHOW CREATE TABLE t2; 3222Table Create Table 3223t2 CREATE TABLE `t2` ( 3224 `c1` varchar(1) DEFAULT NULL 3225) ENGINE=MyISAM DEFAULT CHARSET=latin1 3226SELECT 3227MAX(COALESCE(c1)) AS c1 3228FROM t1; 3229Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr 3230def c1 253 1 0 Y 0 39 8 3231c1 3232NULL 3233DROP TABLE t2; 3234DROP TABLE t1; 3235CREATE TABLE t1 (c1 ENUM('a')) CHARACTER SET utf8; 3236CREATE TABLE t2 AS 3237SELECT 3238MAX(COALESCE(c1)) AS c1 3239FROM t1; 3240SHOW CREATE TABLE t2; 3241Table Create Table 3242t2 CREATE TABLE `t2` ( 3243 `c1` varchar(1) CHARACTER SET utf8 DEFAULT NULL 3244) ENGINE=MyISAM DEFAULT CHARSET=latin1 3245SELECT 3246MAX(COALESCE(c1)) AS c1 3247FROM t1; 3248Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr 3249def c1 253 1 0 Y 0 39 8 3250c1 3251NULL 3252DROP TABLE t2; 3253DROP TABLE t1; 3254# 3255# MDEV-8912 Wrong metadata or type for @c:=string_or_blob_field 3256# 3257CREATE TABLE t1 (c1 TINYBLOB, c2 BLOB, c3 MEDIUMBLOB, c4 LONGBLOB); 3258CREATE TABLE t2 AS 3259SELECT 3260@c1:=c1 AS c1, 3261@c2:=c2 AS c2, 3262@c3:=c3 AS c3, 3263@c4:=c4 AS c4 3264FROM t1; 3265SHOW CREATE TABLE t2; 3266Table Create Table 3267t2 CREATE TABLE `t2` ( 3268 `c1` varbinary(255) DEFAULT NULL, 3269 `c2` blob DEFAULT NULL, 3270 `c3` mediumblob DEFAULT NULL, 3271 `c4` longblob DEFAULT NULL 3272) ENGINE=MyISAM DEFAULT CHARSET=latin1 3273SELECT 3274@c1:=c1 AS c1, 3275@c2:=c2 AS c2, 3276@c3:=c3 AS c3, 3277@c4:=c4 AS c4 3278FROM t1; 3279Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr 3280def c1 253 255 0 Y 128 39 63 3281def c2 252 65535 0 Y 128 39 63 3282def c3 250 16777215 0 Y 128 39 63 3283def c4 251 4294967295 0 Y 128 39 63 3284c1 c2 c3 c4 3285DROP TABLE t2; 3286DROP TABLE t1; 3287CREATE TABLE t1 (c1 CHAR(1), c2 CHAR(255)) CHARACTER SET latin1; 3288CREATE TABLE t2 AS 3289SELECT 3290@c1:=c1 AS c1, 3291@c2:=c2 AS c2 3292FROM t1; 3293SHOW CREATE TABLE t2; 3294Table Create Table 3295t2 CREATE TABLE `t2` ( 3296 `c1` varchar(1) DEFAULT NULL, 3297 `c2` varchar(255) DEFAULT NULL 3298) ENGINE=MyISAM DEFAULT CHARSET=latin1 3299SELECT 3300@c1:=c1 AS c1, 3301@c2:=c2 AS c2 3302FROM t1; 3303Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr 3304def c1 253 1 0 Y 0 39 8 3305def c2 253 255 0 Y 0 39 8 3306c1 c2 3307DROP TABLE t2; 3308DROP TABLE t1; 3309CREATE TABLE t1 (c1 CHAR(1), c2 CHAR(255)) CHARACTER SET utf8; 3310CREATE TABLE t2 AS 3311SELECT 3312@c1:=c1 AS c1, 3313@c2:=c2 AS c2 3314FROM t1; 3315SHOW CREATE TABLE t2; 3316Table Create Table 3317t2 CREATE TABLE `t2` ( 3318 `c1` varchar(1) CHARACTER SET utf8 DEFAULT NULL, 3319 `c2` varchar(255) CHARACTER SET utf8 DEFAULT NULL 3320) ENGINE=MyISAM DEFAULT CHARSET=latin1 3321SELECT 3322@c1:=c1 AS c1, 3323@c2:=c2 AS c2 3324FROM t1; 3325Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr 3326def c1 253 1 0 Y 0 39 8 3327def c2 253 255 0 Y 0 39 8 3328c1 c2 3329DROP TABLE t2; 3330DROP TABLE t1; 3331CREATE TABLE t1 (c1 VARCHAR(1), c2 VARCHAR(255), c3 VARCHAR(20000)) CHARACTER SET latin1; 3332CREATE TABLE t2 AS 3333SELECT 3334@c:=c1 AS c1, 3335@c:=c2 AS c2, 3336@c:=c3 AS c3 3337FROM t1; 3338SHOW CREATE TABLE t2; 3339Table Create Table 3340t2 CREATE TABLE `t2` ( 3341 `c1` varchar(1) DEFAULT NULL, 3342 `c2` varchar(255) DEFAULT NULL, 3343 `c3` text DEFAULT NULL 3344) ENGINE=MyISAM DEFAULT CHARSET=latin1 3345SELECT 3346@c:=c1 AS c1, 3347@c:=c2 AS c2, 3348@c:=c3 AS c3 3349FROM t1; 3350Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr 3351def c1 253 1 0 Y 0 39 8 3352def c2 253 255 0 Y 0 39 8 3353def c3 252 20000 0 Y 0 39 8 3354c1 c2 c3 3355DROP TABLE t2; 3356DROP TABLE t1; 3357CREATE TABLE t1 (c1 VARCHAR(1), c2 VARCHAR(255), c3 VARCHAR(20000)) CHARACTER SET utf8; 3358CREATE TABLE t2 AS 3359SELECT 3360@c:=c1 AS c1, 3361@c:=c2 AS c2, 3362@c:=c3 AS c3 3363FROM t1; 3364SHOW CREATE TABLE t2; 3365Table Create Table 3366t2 CREATE TABLE `t2` ( 3367 `c1` varchar(1) CHARACTER SET utf8 DEFAULT NULL, 3368 `c2` varchar(255) CHARACTER SET utf8 DEFAULT NULL, 3369 `c3` text CHARACTER SET utf8 DEFAULT NULL 3370) ENGINE=MyISAM DEFAULT CHARSET=latin1 3371SELECT 3372@c:=c1 AS c1, 3373@c:=c2 AS c2, 3374@c:=c3 AS c3 3375FROM t1; 3376Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr 3377def c1 253 1 0 Y 0 39 8 3378def c2 253 255 0 Y 0 39 8 3379def c3 252 60000 0 Y 0 39 8 3380c1 c2 c3 3381DROP TABLE t2; 3382DROP TABLE t1; 3383CREATE TABLE t1 (c1 ENUM('a')) CHARACTER SET latin1; 3384CREATE TABLE t2 AS 3385SELECT 3386@c:=c1 AS c1 3387FROM t1; 3388SHOW CREATE TABLE t2; 3389Table Create Table 3390t2 CREATE TABLE `t2` ( 3391 `c1` varchar(1) DEFAULT NULL 3392) ENGINE=MyISAM DEFAULT CHARSET=latin1 3393SELECT 3394@c:=c1 AS c1 3395FROM t1; 3396Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr 3397def c1 253 1 0 Y 0 0 8 3398c1 3399DROP TABLE t2; 3400DROP TABLE t1; 3401CREATE TABLE t1 (c1 ENUM('a')) CHARACTER SET utf8; 3402CREATE TABLE t2 AS 3403SELECT 3404@c:=c1 AS c1 3405FROM t1; 3406SHOW CREATE TABLE t2; 3407Table Create Table 3408t2 CREATE TABLE `t2` ( 3409 `c1` varchar(1) CHARACTER SET utf8 DEFAULT NULL 3410) ENGINE=MyISAM DEFAULT CHARSET=latin1 3411SELECT 3412@c:=c1 AS c1 3413FROM t1; 3414Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr 3415def c1 253 1 0 Y 0 0 8 3416c1 3417DROP TABLE t2; 3418DROP TABLE t1; 3419# 3420# MDEV-9653 Assertion `length || !scale' failed in uint my_decimal_length_to_precision(uint, uint, bool) 3421# 3422SELECT CASE 0 WHEN 1 THEN (CASE 2 WHEN 3 THEN NULL END) WHEN 4 THEN 5 END; 3423CASE 0 WHEN 1 THEN (CASE 2 WHEN 3 THEN NULL END) WHEN 4 THEN 5 END 3424NULL 3425SELECT CASE 0 WHEN 1 THEN (COALESCE(NULL)) WHEN 4 THEN 5 END; 3426CASE 0 WHEN 1 THEN (COALESCE(NULL)) WHEN 4 THEN 5 END 3427NULL 3428SELECT CASE WHEN TRUE THEN COALESCE(NULL) ELSE 4 END; 3429CASE WHEN TRUE THEN COALESCE(NULL) ELSE 4 END 3430NULL 3431SELECT COALESCE(COALESCE(NULL), 1.1) AS c0, IF(0, COALESCE(NULL), 1.1) AS c1; 3432Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr 3433def c0 246 4 3 Y 32896 1 63 3434def c1 246 4 3 Y 32896 1 63 3435c0 c1 34361.1 1.1 3437# 3438# MDEV-9752 Wrong data type for COALEASCE(?,1) in prepared statements 3439# 3440PREPARE stmt FROM "CREATE TABLE t1 AS SELECT CONCAT(COALESCE(?,1)) AS a, CONCAT(CASE WHEN TRUE THEN ? ELSE 1 END) AS b"; 3441SET @a=1; 3442EXECUTE stmt USING @a,@a; 3443SHOW CREATE TABLE t1; 3444Table Create Table 3445t1 CREATE TABLE `t1` ( 3446 `a` varchar(20) DEFAULT NULL, 3447 `b` varchar(20) DEFAULT NULL 3448) ENGINE=MyISAM DEFAULT CHARSET=latin1 3449DROP TABLE t1; 3450# 3451# MDEV-11015 Assertion failed: precision > 0 in decimal_bin_size upon SELECT with DISTINCT, CAST and other functions 3452# 3453CREATE TABLE t1 (b LONGBLOB); 3454INSERT IGNORE INTO t1 VALUES ('foo'),('bar'); 3455SELECT DISTINCT - GREATEST( b, CAST( NULL AS DATETIME ) ) AS f FROM t1; 3456f 3457NULL 3458Warnings: 3459Warning 1292 Truncated incorrect datetime value: 'foo' 3460Warning 1292 Truncated incorrect datetime value: 'bar' 3461DROP TABLE t1; 3462CREATE TABLE t1 (b LONGBLOB); 3463INSERT IGNORE INTO t1 VALUES ('foo'),('bar'); 3464SELECT DISTINCT - GREATEST( b, CAST( NULL AS TIME) ) AS f FROM t1; 3465f 3466NULL 3467Warnings: 3468Warning 1292 Incorrect time value: 'foo' 3469Warning 1292 Incorrect time value: 'bar' 3470DROP TABLE t1; 3471CREATE TABLE t1 (b LONGBLOB); 3472INSERT IGNORE INTO t1 VALUES ('foo'),('bar'); 3473SELECT DISTINCT - GREATEST( b, CAST( NULL AS DATE) ) AS f FROM t1; 3474f 3475NULL 3476Warnings: 3477Warning 1292 Truncated incorrect datetime value: 'foo' 3478Warning 1292 Truncated incorrect datetime value: 'bar' 3479DROP TABLE t1; 3480# 3481# End of 10.1 tests 3482# 3483# 3484# Start of 10.3 tests 3485# 3486# 3487# MDEV-12497 Wrong data type for LEAST(latin1_expr, utf8_expr) 3488# 3489CREATE TABLE t1 AS SELECT 3490LEAST(_latin1'aaa',_utf8 0xC39F) AS c1, 3491COALESCE(_latin1'aaa',_utf8 0xC39F) AS c2; 3492SHOW CREATE TABLE t1; 3493Table Create Table 3494t1 CREATE TABLE `t1` ( 3495 `c1` varchar(3) CHARACTER SET utf8 NOT NULL, 3496 `c2` varchar(3) CHARACTER SET utf8 NOT NULL 3497) ENGINE=MyISAM DEFAULT CHARSET=latin1 3498SELECT * FROM t1; 3499c1 c2 3500aaa aaa 3501DROP TABLE t1; 3502# 3503# MDEV-12504 Wrong data type for LEAST(date_expr,time_expr) 3504# 3505CREATE TABLE t1 AS SELECT 3506LEAST(DATE'2001-01-01', TIME'10:20:30') AS c1, 3507CONCAT(LEAST(DATE'2001-01-01', TIME'10:20:30')) AS c2; 3508SELECT * FROM t1; 3509c1 c2 35102001-01-01 00:00:00 2001-01-01 00:00:00 3511DROP TABLE t1; 3512# 3513# MDEV-12505 Wrong data type for GREATEST(bit_column, int_column) 3514# 3515CREATE TABLE t1 (a BIT(64),b INT); 3516INSERT INTO t1 VALUES (0xFFFFFFFFFFFFFFFF,-1); 3517SELECT a>b, COALESCE(a,b), GREATEST(a,b) FROM t1; 3518a>b COALESCE(a,b) GREATEST(a,b) 35191 18446744073709551615 18446744073709551615 3520CREATE TABLE t2 AS SELECT COALESCE(a,b),GREATEST(a,b) FROM t1; 3521SELECT * FROM t2; 3522COALESCE(a,b) GREATEST(a,b) 352318446744073709551615 18446744073709551615 3524SHOW CREATE TABLE t2; 3525Table Create Table 3526t2 CREATE TABLE `t2` ( 3527 `COALESCE(a,b)` decimal(64,0) DEFAULT NULL, 3528 `GREATEST(a,b)` decimal(64,0) DEFAULT NULL 3529) ENGINE=MyISAM DEFAULT CHARSET=latin1 3530DROP TABLE t2; 3531DROP TABLE t1; 3532# 3533# MDEV-12601 Hybrid functions create a column of an impossible type DOUBLE(256,4) 3534# 3535CREATE TABLE t1 (a DOUBLE(255,4),b DOUBLE(255,3)); 3536CREATE TABLE t2 AS SELECT COALESCE(a,b) FROM t1; 3537SHOW CREATE TABLE t2; 3538Table Create Table 3539t2 CREATE TABLE `t2` ( 3540 `COALESCE(a,b)` double(255,4) DEFAULT NULL 3541) ENGINE=MyISAM DEFAULT CHARSET=latin1 3542DROP TABLE t2; 3543DROP TABLE t1; 3544# 3545# MDEV-12617 CASE and CASE-alike hybrid functions do not preserve exact data types 3546# 3547CREATE TABLE t1 (a FLOAT(10,2)); 3548CREATE TABLE t2 AS SELECT COALESCE(a) FROM t1; 3549SHOW CREATE TABLE t2; 3550Table Create Table 3551t2 CREATE TABLE `t2` ( 3552 `COALESCE(a)` float(10,2) DEFAULT NULL 3553) ENGINE=MyISAM DEFAULT CHARSET=latin1 3554DROP TABLE t2, t1; 3555CREATE TABLE t1 (a FLOAT(10,2)); 3556CREATE TABLE t2 AS SELECT LEAST(a,a) FROM t1; 3557SHOW CREATE TABLE t2; 3558Table Create Table 3559t2 CREATE TABLE `t2` ( 3560 `LEAST(a,a)` float(19,2) DEFAULT NULL 3561) ENGINE=MyISAM DEFAULT CHARSET=latin1 3562DROP TABLE t2, t1; 3563CREATE TABLE t1 (a TINYINT(1)); 3564CREATE TABLE t2 AS SELECT COALESCE(a) FROM t1; 3565SHOW CREATE TABLE t2; 3566Table Create Table 3567t2 CREATE TABLE `t2` ( 3568 `COALESCE(a)` tinyint(4) DEFAULT NULL 3569) ENGINE=MyISAM DEFAULT CHARSET=latin1 3570DROP TABLE t2, t1; 3571CREATE TABLE t1 (a TINYINT(1)); 3572CREATE TABLE t2 AS SELECT LEAST(a,a) FROM t1; 3573SHOW CREATE TABLE t2; 3574Table Create Table 3575t2 CREATE TABLE `t2` ( 3576 `LEAST(a,a)` tinyint(4) DEFAULT NULL 3577) ENGINE=MyISAM DEFAULT CHARSET=latin1 3578DROP TABLE t2, t1; 3579# 3580# MDEV-12875 Wrong VIEW column data type for COALESCE(int_column) 3581# 3582CREATE TABLE t1 (a INT); 3583CREATE OR REPLACE VIEW v1 AS SELECT COALESCE(a) FROM t1; 3584DESCRIBE v1; 3585Field Type Null Key Default Extra 3586COALESCE(a) int(11) YES NULL 3587DROP VIEW v1; 3588DROP TABLE t1; 3589# 3590# MDEV-10309 COALESCE(12345678900) makes a column of a wrong type and truncates the data 3591# 3592CREATE TABLE t1 AS SELECT 12345678900 AS c1, COALESCE(12345678900) AS c2; 3593SELECT * FROM t1; 3594c1 c2 359512345678900 12345678900 3596SHOW CREATE TABLE t1; 3597Table Create Table 3598t1 CREATE TABLE `t1` ( 3599 `c1` bigint(11) NOT NULL, 3600 `c2` bigint(11) NOT NULL 3601) ENGINE=MyISAM DEFAULT CHARSET=latin1 3602DROP TABLE t1; 3603CREATE TABLE t1 AS SELECT 36049 AS i1, COALESCE(9) AS c1, 360599 AS i2, COALESCE(99) AS c2, 3606999 AS i3, COALESCE(999) AS c3, 36079999 AS i4, COALESCE(9999) AS c4, 360899999 AS i5, COALESCE(99999) AS c5, 3609999999 AS i6, COALESCE(999999) AS c6, 36109999999 AS i7, COALESCE(9999999) AS c7, 361199999999 AS i8, COALESCE(99999999) AS c8, 3612999999999 AS i9, COALESCE(999999999) AS c9, 36132147483647, COALESCE(2147483647), 36142147483648, COALESCE(2147483648), 36159999999999 AS i10, COALESCE(9999999999) AS c10, 361699999999999 AS i11, COALESCE(99999999999) AS c11, 3617999999999999 AS i12, COALESCE(999999999999) AS c12, 36189999999999999 AS i13, COALESCE(9999999999999) AS c13, 361999999999999999 AS i14, COALESCE(99999999999999) AS c14, 3620999999999999999 AS i15, COALESCE(999999999999999) AS c15, 36219999999999999999 AS i16, COALESCE(9999999999999999) AS c16, 362299999999999999999 AS i17, COALESCE(99999999999999999) AS c17, 3623999999999999999999 AS i18, COALESCE(999999999999999999) AS c18, 36249223372036854775807, COALESCE(9223372036854775807), 36259223372036854775808, COALESCE(9223372036854775808), 36269999999999999999999 AS i19, COALESCE(9999999999999999999) AS c19, 362718446744073709551615, COALESCE(18446744073709551615), 362818446744073709551616, COALESCE(18446744073709551616), 362999999999999999999999 AS i20, COALESCE(99999999999999999999) AS c20, 3630999999999999999999999 AS i21, COALESCE(999999999999999999999) AS c21, 36319999999999999999999999 AS i22, COALESCE(9999999999999999999999) AS c22; 3632SHOW CREATE TABLE t1; 3633Table Create Table 3634t1 CREATE TABLE `t1` ( 3635 `i1` int(1) NOT NULL, 3636 `c1` int(1) NOT NULL, 3637 `i2` int(2) NOT NULL, 3638 `c2` int(2) NOT NULL, 3639 `i3` int(3) NOT NULL, 3640 `c3` int(3) NOT NULL, 3641 `i4` int(4) NOT NULL, 3642 `c4` int(4) NOT NULL, 3643 `i5` int(5) NOT NULL, 3644 `c5` int(5) NOT NULL, 3645 `i6` int(6) NOT NULL, 3646 `c6` int(6) NOT NULL, 3647 `i7` int(7) NOT NULL, 3648 `c7` int(7) NOT NULL, 3649 `i8` int(8) NOT NULL, 3650 `c8` int(8) NOT NULL, 3651 `i9` int(9) NOT NULL, 3652 `c9` int(9) NOT NULL, 3653 `2147483647` bigint(10) NOT NULL, 3654 `COALESCE(2147483647)` bigint(10) NOT NULL, 3655 `2147483648` bigint(10) NOT NULL, 3656 `COALESCE(2147483648)` bigint(10) NOT NULL, 3657 `i10` bigint(10) NOT NULL, 3658 `c10` bigint(10) NOT NULL, 3659 `i11` bigint(11) NOT NULL, 3660 `c11` bigint(11) NOT NULL, 3661 `i12` bigint(12) NOT NULL, 3662 `c12` bigint(12) NOT NULL, 3663 `i13` bigint(13) NOT NULL, 3664 `c13` bigint(13) NOT NULL, 3665 `i14` bigint(14) NOT NULL, 3666 `c14` bigint(14) NOT NULL, 3667 `i15` bigint(15) NOT NULL, 3668 `c15` bigint(15) NOT NULL, 3669 `i16` bigint(16) NOT NULL, 3670 `c16` bigint(16) NOT NULL, 3671 `i17` bigint(17) NOT NULL, 3672 `c17` bigint(17) NOT NULL, 3673 `i18` bigint(18) NOT NULL, 3674 `c18` bigint(18) NOT NULL, 3675 `9223372036854775807` bigint(19) NOT NULL, 3676 `COALESCE(9223372036854775807)` bigint(19) NOT NULL, 3677 `9223372036854775808` bigint(19) unsigned NOT NULL, 3678 `COALESCE(9223372036854775808)` bigint(19) unsigned NOT NULL, 3679 `i19` bigint(19) unsigned NOT NULL, 3680 `c19` bigint(19) unsigned NOT NULL, 3681 `18446744073709551615` bigint(20) unsigned NOT NULL, 3682 `COALESCE(18446744073709551615)` bigint(20) unsigned NOT NULL, 3683 `18446744073709551616` decimal(20,0) NOT NULL, 3684 `COALESCE(18446744073709551616)` decimal(20,0) NOT NULL, 3685 `i20` decimal(20,0) NOT NULL, 3686 `c20` decimal(20,0) NOT NULL, 3687 `i21` decimal(21,0) NOT NULL, 3688 `c21` decimal(21,0) NOT NULL, 3689 `i22` decimal(22,0) NOT NULL, 3690 `c22` decimal(22,0) NOT NULL 3691) ENGINE=MyISAM DEFAULT CHARSET=latin1 3692SELECT * FROM t1; 3693i1 9 3694c1 9 3695i2 99 3696c2 99 3697i3 999 3698c3 999 3699i4 9999 3700c4 9999 3701i5 99999 3702c5 99999 3703i6 999999 3704c6 999999 3705i7 9999999 3706c7 9999999 3707i8 99999999 3708c8 99999999 3709i9 999999999 3710c9 999999999 37112147483647 2147483647 3712COALESCE(2147483647) 2147483647 37132147483648 2147483648 3714COALESCE(2147483648) 2147483648 3715i10 9999999999 3716c10 9999999999 3717i11 99999999999 3718c11 99999999999 3719i12 999999999999 3720c12 999999999999 3721i13 9999999999999 3722c13 9999999999999 3723i14 99999999999999 3724c14 99999999999999 3725i15 999999999999999 3726c15 999999999999999 3727i16 9999999999999999 3728c16 9999999999999999 3729i17 99999999999999999 3730c17 99999999999999999 3731i18 999999999999999999 3732c18 999999999999999999 37339223372036854775807 9223372036854775807 3734COALESCE(9223372036854775807) 9223372036854775807 37359223372036854775808 9223372036854775808 3736COALESCE(9223372036854775808) 9223372036854775808 3737i19 9999999999999999999 3738c19 9999999999999999999 373918446744073709551615 18446744073709551615 3740COALESCE(18446744073709551615) 18446744073709551615 374118446744073709551616 18446744073709551616 3742COALESCE(18446744073709551616) 18446744073709551616 3743i20 99999999999999999999 3744c20 99999999999999999999 3745i21 999999999999999999999 3746c21 999999999999999999999 3747i22 9999999999999999999999 3748c22 9999999999999999999999 3749DROP TABLE t1; 3750# 3751# MDEV-9406 CREATE TABLE..SELECT creates different columns for IFNULL() and equivalent COALESCE,CASE,IF 3752# 3753CREATE TABLE t1 (a SMALLINT); 3754INSERT INTO t1 VALUES (1),(2); 3755CREATE TABLE t2 AS SELECT 3756IFNULL(a,a) AS c1, 3757COALESCE(a,a) AS c2, 3758CASE WHEN a IS NOT NULL THEN a ELSE a END AS c3, 3759IF(a IS NULL,a,a) AS c4 FROM t1; 3760SHOW CREATE TABLE t2; 3761Table Create Table 3762t2 CREATE TABLE `t2` ( 3763 `c1` smallint(6) DEFAULT NULL, 3764 `c2` smallint(6) DEFAULT NULL, 3765 `c3` smallint(6) DEFAULT NULL, 3766 `c4` smallint(6) DEFAULT NULL 3767) ENGINE=MyISAM DEFAULT CHARSET=latin1 3768DROP TABLE t2,t1; 3769CREATE TABLE t1 AS SELECT 3770connection_id() AS c0, 3771IFNULL(connection_id(),connection_id()) AS c1, 3772COALESCE(connection_id(), connection_id()) AS c2, 3773CASE WHEN 0 THEN connection_id() ELSE connection_id() END AS c3, 3774IF(0,connection_id(),connection_id()) AS c4; 3775SHOW CREATE TABLE t1; 3776Table Create Table 3777t1 CREATE TABLE `t1` ( 3778 `c0` int(10) unsigned NOT NULL, 3779 `c1` int(10) unsigned NOT NULL, 3780 `c2` int(10) unsigned NOT NULL, 3781 `c3` int(10) unsigned NOT NULL, 3782 `c4` int(10) unsigned NOT NULL 3783) ENGINE=MyISAM DEFAULT CHARSET=latin1 3784DROP TABLE t1; 3785# 3786# MDEV-17759 Assertion `precision > 0' failed in decimal_bin_size upon CREATE TABLE .. SELECT 3787# 3788SET sql_mode=''; 3789CREATE TABLE t1 (d DECIMAL(43,0) UNSIGNED); 3790INSERT INTO t1 VALUES (1); 3791CREATE TABLE t2 AS SELECT 3792IFNULL(SLEEP(0.01), NULL DIV d) AS f0, 3793IFNULL(SLEEP(0.01), '' DIV d) AS f1 3794FROM t1; 3795SHOW CREATE TABLE t2; 3796Table Create Table 3797t2 CREATE TABLE `t2` ( 3798 `f0` decimal(1,0) DEFAULT NULL, 3799 `f1` decimal(1,0) DEFAULT NULL 3800) ENGINE=MyISAM DEFAULT CHARSET=latin1 3801DROP TABLE t1, t2; 3802SET sql_mode=DEFAULT; 3803# 3804# MDEV-17325 NULL-ability problems with LEAST() in combination with NO_ZERO_DATE and NO_ZERO_IN_DATE 3805# 3806SET sql_mode='NO_ZERO_DATE,NO_ZERO_IN_DATE'; 3807SELECT 3808LEAST('0000-00-00',DATE'2001-01-01') AS s1, 3809LEAST('0001-00-01',DATE'2001-01-01') AS s2, 3810LEAST('0000-00-00',TIMESTAMP'2001-01-01 00:00:00') AS s3, 3811LEAST('0001-00-01',TIMESTAMP'2001-01-01 00:00:00') AS s4, 3812LEAST(0,DATE'2001-01-01') AS i1, 3813LEAST(20010001,DATE'2001-01-01') AS i2, 3814LEAST(0,TIMESTAMP'2001-01-01 00:00:00') AS i3, 3815LEAST(20010001,TIMESTAMP'2001-01-01 00:00:00') AS i4; 3816Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr 3817def s1 10 10 0 Y 128 0 63 3818def s2 10 10 0 Y 128 0 63 3819def s3 12 26 0 Y 128 0 63 3820def s4 12 26 0 Y 128 0 63 3821def i1 10 10 0 Y 128 0 63 3822def i2 10 10 0 Y 128 0 63 3823def i3 12 19 0 Y 128 0 63 3824def i4 12 19 0 Y 128 0 63 3825s1 s2 s3 s4 i1 i2 i3 i4 3826NULL NULL NULL NULL NULL NULL NULL NULL 3827Warnings: 3828Warning 1292 Incorrect datetime value: '0000-00-00' 3829Warning 1292 Incorrect datetime value: '0001-00-01' 3830Warning 1292 Incorrect datetime value: '0000-00-00 00:00:00' 3831Warning 1292 Incorrect datetime value: '0001-00-01 00:00:00' 3832Warning 1292 Incorrect datetime value: '0000-00-00' 3833Warning 1292 Incorrect datetime value: '2001-00-01' 3834Warning 1292 Incorrect datetime value: '0000-00-00 00:00:00' 3835Warning 1292 Incorrect datetime value: '2001-00-01 00:00:00' 3836SET sql_mode='NO_ZERO_DATE,NO_ZERO_IN_DATE'; 3837CREATE TABLE t1 AS SELECT 3838LEAST('0000-00-00',DATE'2001-01-01') AS s1, 3839LEAST('0001-00-01',DATE'2001-01-01') AS s2, 3840LEAST('0000-00-00',TIMESTAMP'2001-01-01 00:00:00') AS s3, 3841LEAST('0001-00-01',TIMESTAMP'2001-01-01 00:00:00') AS s4, 3842LEAST(0,DATE'2001-01-01') AS i1, 3843LEAST(20010001,DATE'2001-01-01') AS i2, 3844LEAST(0,TIMESTAMP'2001-01-01 00:00:00') AS i3, 3845LEAST(20010001,TIMESTAMP'2001-01-01 00:00:00') AS i4; 3846Warnings: 3847Warning 1292 Incorrect datetime value: '0000-00-00' 3848Warning 1292 Incorrect datetime value: '0001-00-01' 3849Warning 1292 Incorrect datetime value: '0000-00-00 00:00:00' 3850Warning 1292 Incorrect datetime value: '0001-00-01 00:00:00' 3851Warning 1292 Incorrect datetime value: '0000-00-00' 3852Warning 1292 Incorrect datetime value: '2001-00-01' 3853Warning 1292 Incorrect datetime value: '0000-00-00 00:00:00' 3854Warning 1292 Incorrect datetime value: '2001-00-01 00:00:00' 3855SELECT * FROM t1; 3856s1 s2 s3 s4 i1 i2 i3 i4 3857NULL NULL NULL NULL NULL NULL NULL NULL 3858SHOW CREATE TABLE t1; 3859Table Create Table 3860t1 CREATE TABLE `t1` ( 3861 `s1` date DEFAULT NULL, 3862 `s2` date DEFAULT NULL, 3863 `s3` datetime DEFAULT NULL, 3864 `s4` datetime DEFAULT NULL, 3865 `i1` date DEFAULT NULL, 3866 `i2` date DEFAULT NULL, 3867 `i3` datetime DEFAULT NULL, 3868 `i4` datetime DEFAULT NULL 3869) ENGINE=MyISAM DEFAULT CHARSET=latin1 3870DROP TABLE t1; 3871SET timestamp=UNIX_TIMESTAMP('2001-01-01 10:20:30'); 3872CREATE TABLE t1 AS SELECT LEAST(CURRENT_DATE,CURRENT_TIME) AS c1; 3873SELECT * FROM t1; 3874c1 38752001-01-01 00:00:00 3876SHOW CREATE TABLE t1; 3877Table Create Table 3878t1 CREATE TABLE `t1` ( 3879 `c1` datetime NOT NULL 3880) ENGINE=MyISAM DEFAULT CHARSET=latin1 3881DROP TABLE t1; 3882SET old_mode=ZERO_DATE_TIME_CAST; 3883CREATE TABLE t1 AS SELECT LEAST(CURRENT_DATE,CURRENT_TIME) AS c1; 3884Warnings: 3885Warning 1292 Incorrect datetime value: '0000-00-00 10:20:30' 3886SELECT * FROM t1; 3887c1 3888NULL 3889SHOW CREATE TABLE t1; 3890Table Create Table 3891t1 CREATE TABLE `t1` ( 3892 `c1` datetime DEFAULT NULL 3893) ENGINE=MyISAM DEFAULT CHARSET=latin1 3894DROP TABLE t1; 3895SET old_mode=DEFAULT; 3896SET timestamp=DEFAULT; 3897SET sql_mode=DEFAULT; 3898SET sql_mode=''; 3899SELECT LEAST(999,TIME'10:20:30') AS c1; 3900c1 3901NULL 3902Warnings: 3903Warning 1292 Incorrect time value: '999' 3904CREATE TABLE t1 AS SELECT LEAST(999,TIME'10:20:30') AS c1; 3905Warnings: 3906Warning 1292 Incorrect time value: '999' 3907SELECT * FROM t1; 3908c1 3909NULL 3910SHOW CREATE TABLE t1; 3911Table Create Table 3912t1 CREATE TABLE `t1` ( 3913 `c1` time DEFAULT NULL 3914) ENGINE=MyISAM DEFAULT CHARSET=latin1 3915DROP TABLE t1; 3916SET sql_mode=DEFAULT; 3917# 3918# MDEV-18456 Assertion `item->maybe_null' failed in Type_handler_temporal_result::make_sort_key 3919# 3920CREATE TABLE t1 (t TIME NOT NULL); 3921INSERT INTO t1 VALUES ('00:20:11'),('14:52:05'); 3922SELECT GREATEST('9999', t) FROM t1 ORDER BY 1; 3923GREATEST('9999', t) 3924NULL 3925NULL 3926Warnings: 3927Warning 1292 Incorrect time value: '9999' 3928Warning 1292 Incorrect time value: '9999' 3929Warning 1292 Incorrect time value: '9999' 3930Warning 1292 Incorrect time value: '9999' 3931DROP TABLE t1; 3932# 3933# End of 10.3 tests 3934# 3935# 3936# MDEV-17325 NULL-ability problems with LEAST() in combination with NO_ZERO_DATE and NO_ZERO_IN_DATE 3937# 3938SET sql_mode='NO_ZERO_DATE,NO_ZERO_IN_DATE'; 3939SELECT 3940LEAST('0000-00-00',DATE'2001-01-01') AS s1, 3941LEAST('0001-00-01',DATE'2001-01-01') AS s2, 3942LEAST('0000-00-00',TIMESTAMP'2001-01-01 00:00:00') AS s3, 3943LEAST('0001-00-01',TIMESTAMP'2001-01-01 00:00:00') AS s4, 3944LEAST(0,DATE'2001-01-01') AS i1, 3945LEAST(20010001,DATE'2001-01-01') AS i2, 3946LEAST(0,TIMESTAMP'2001-01-01 00:00:00') AS i3, 3947LEAST(20010001,TIMESTAMP'2001-01-01 00:00:00') AS i4; 3948Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr 3949def s1 10 10 0 Y 128 0 63 3950def s2 10 10 0 Y 128 0 63 3951def s3 12 26 0 Y 128 0 63 3952def s4 12 26 0 Y 128 0 63 3953def i1 10 10 0 Y 128 0 63 3954def i2 10 10 0 Y 128 0 63 3955def i3 12 19 0 Y 128 0 63 3956def i4 12 19 0 Y 128 0 63 3957s1 s2 s3 s4 i1 i2 i3 i4 3958NULL NULL NULL NULL NULL NULL NULL NULL 3959Warnings: 3960Warning 1292 Incorrect datetime value: '0000-00-00' 3961Warning 1292 Incorrect datetime value: '0001-00-01' 3962Warning 1292 Incorrect datetime value: '0000-00-00 00:00:00' 3963Warning 1292 Incorrect datetime value: '0001-00-01 00:00:00' 3964Warning 1292 Incorrect datetime value: '0000-00-00' 3965Warning 1292 Incorrect datetime value: '2001-00-01' 3966Warning 1292 Incorrect datetime value: '0000-00-00 00:00:00' 3967Warning 1292 Incorrect datetime value: '2001-00-01 00:00:00' 3968SET sql_mode='NO_ZERO_DATE,NO_ZERO_IN_DATE'; 3969CREATE TABLE t1 AS SELECT 3970LEAST('0000-00-00',DATE'2001-01-01') AS s1, 3971LEAST('0001-00-01',DATE'2001-01-01') AS s2, 3972LEAST('0000-00-00',TIMESTAMP'2001-01-01 00:00:00') AS s3, 3973LEAST('0001-00-01',TIMESTAMP'2001-01-01 00:00:00') AS s4, 3974LEAST(0,DATE'2001-01-01') AS i1, 3975LEAST(20010001,DATE'2001-01-01') AS i2, 3976LEAST(0,TIMESTAMP'2001-01-01 00:00:00') AS i3, 3977LEAST(20010001,TIMESTAMP'2001-01-01 00:00:00') AS i4; 3978Warnings: 3979Warning 1292 Incorrect datetime value: '0000-00-00' 3980Warning 1292 Incorrect datetime value: '0001-00-01' 3981Warning 1292 Incorrect datetime value: '0000-00-00 00:00:00' 3982Warning 1292 Incorrect datetime value: '0001-00-01 00:00:00' 3983Warning 1292 Incorrect datetime value: '0000-00-00' 3984Warning 1292 Incorrect datetime value: '2001-00-01' 3985Warning 1292 Incorrect datetime value: '0000-00-00 00:00:00' 3986Warning 1292 Incorrect datetime value: '2001-00-01 00:00:00' 3987SELECT * FROM t1; 3988s1 s2 s3 s4 i1 i2 i3 i4 3989NULL NULL NULL NULL NULL NULL NULL NULL 3990SHOW CREATE TABLE t1; 3991Table Create Table 3992t1 CREATE TABLE `t1` ( 3993 `s1` date DEFAULT NULL, 3994 `s2` date DEFAULT NULL, 3995 `s3` datetime DEFAULT NULL, 3996 `s4` datetime DEFAULT NULL, 3997 `i1` date DEFAULT NULL, 3998 `i2` date DEFAULT NULL, 3999 `i3` datetime DEFAULT NULL, 4000 `i4` datetime DEFAULT NULL 4001) ENGINE=MyISAM DEFAULT CHARSET=latin1 4002DROP TABLE t1; 4003SET timestamp=UNIX_TIMESTAMP('2001-01-01 10:20:30'); 4004CREATE TABLE t1 AS SELECT LEAST(CURRENT_DATE,CURRENT_TIME) AS c1; 4005SELECT * FROM t1; 4006c1 40072001-01-01 00:00:00 4008SHOW CREATE TABLE t1; 4009Table Create Table 4010t1 CREATE TABLE `t1` ( 4011 `c1` datetime NOT NULL 4012) ENGINE=MyISAM DEFAULT CHARSET=latin1 4013DROP TABLE t1; 4014SET old_mode=ZERO_DATE_TIME_CAST; 4015CREATE TABLE t1 AS SELECT LEAST(CURRENT_DATE,CURRENT_TIME) AS c1; 4016Warnings: 4017Warning 1292 Incorrect datetime value: '0000-00-00 10:20:30' 4018SELECT * FROM t1; 4019c1 4020NULL 4021SHOW CREATE TABLE t1; 4022Table Create Table 4023t1 CREATE TABLE `t1` ( 4024 `c1` datetime DEFAULT NULL 4025) ENGINE=MyISAM DEFAULT CHARSET=latin1 4026DROP TABLE t1; 4027SET old_mode=DEFAULT; 4028SET timestamp=DEFAULT; 4029SET sql_mode=DEFAULT; 4030SET sql_mode=''; 4031SELECT LEAST(999,TIME'10:20:30') AS c1; 4032c1 4033NULL 4034Warnings: 4035Warning 1292 Incorrect time value: '999' 4036CREATE TABLE t1 AS SELECT LEAST(999,TIME'10:20:30') AS c1; 4037Warnings: 4038Warning 1292 Incorrect time value: '999' 4039SELECT * FROM t1; 4040c1 4041NULL 4042SHOW CREATE TABLE t1; 4043Table Create Table 4044t1 CREATE TABLE `t1` ( 4045 `c1` time DEFAULT NULL 4046) ENGINE=MyISAM DEFAULT CHARSET=latin1 4047DROP TABLE t1; 4048SET sql_mode=DEFAULT; 4049# 4050# MDEV-17318 CAST(LEAST(zero_date,non_zero_date) AS numeric_data_type) returns a wrong result 4051# 4052SET sql_mode='NO_ZERO_DATE,NO_ZERO_IN_DATE'; 4053SELECT 4054LEAST('0000-00-00',DATE'2001-01-01') AS c0, 4055CAST(LEAST('0000-00-00',DATE'2001-01-01') AS CHAR) AS string, 4056CAST(LEAST('0000-00-00',DATE'2001-01-01') AS DATE) AS date, 4057CAST(LEAST('0000-00-00',DATE'2001-01-01') AS DATETIME) AS datetime, 4058CAST(LEAST('0000-00-00',DATE'2001-01-01') AS TIME) AS time, 4059CAST(LEAST('0000-00-00',DATE'2001-01-01') AS DECIMAL) AS dc, 4060CAST(LEAST('0000-00-00',DATE'2001-01-01') AS DOUBLE) AS dbl, 4061CAST(LEAST('0000-00-00',DATE'2001-01-01') AS SIGNED) AS sint, 4062CAST(LEAST('0000-00-00',DATE'2001-01-01') AS UNSIGNED) AS uint; 4063c0 string date datetime time dc dbl sint uint 4064NULL NULL NULL NULL NULL NULL NULL NULL NULL 4065Warnings: 4066Warning 1292 Incorrect datetime value: '0000-00-00' 4067Warning 1292 Incorrect datetime value: '0000-00-00' 4068Warning 1292 Incorrect datetime value: '0000-00-00' 4069Warning 1292 Incorrect datetime value: '0000-00-00' 4070Warning 1292 Incorrect datetime value: '0000-00-00' 4071Warning 1292 Incorrect datetime value: '0000-00-00' 4072Warning 1292 Incorrect datetime value: '0000-00-00' 4073Warning 1292 Incorrect datetime value: '0000-00-00' 4074Warning 1292 Incorrect datetime value: '0000-00-00' 4075CREATE TABLE t1 AS SELECT 4076LEAST('0000-00-00',DATE'2001-01-01') AS c0, 4077CAST(LEAST('0000-00-00',DATE'2001-01-01') AS CHAR) AS string, 4078CAST(LEAST('0000-00-00',DATE'2001-01-01') AS DATE) AS date, 4079CAST(LEAST('0000-00-00',DATE'2001-01-01') AS DATETIME) AS datetime, 4080CAST(LEAST('0000-00-00',DATE'2001-01-01') AS TIME) AS time, 4081CAST(LEAST('0000-00-00',DATE'2001-01-01') AS DECIMAL) AS dc, 4082CAST(LEAST('0000-00-00',DATE'2001-01-01') AS DOUBLE) AS dbl, 4083CAST(LEAST('0000-00-00',DATE'2001-01-01') AS SIGNED) AS sint, 4084CAST(LEAST('0000-00-00',DATE'2001-01-01') AS UNSIGNED) AS uint; 4085Warnings: 4086Warning 1292 Incorrect datetime value: '0000-00-00' 4087Warning 1292 Incorrect datetime value: '0000-00-00' 4088Warning 1292 Incorrect datetime value: '0000-00-00' 4089Warning 1292 Incorrect datetime value: '0000-00-00' 4090Warning 1292 Incorrect datetime value: '0000-00-00' 4091Warning 1292 Incorrect datetime value: '0000-00-00' 4092Warning 1292 Incorrect datetime value: '0000-00-00' 4093Warning 1292 Incorrect datetime value: '0000-00-00' 4094Warning 1292 Incorrect datetime value: '0000-00-00' 4095SELECT * FROM t1; 4096c0 string date datetime time dc dbl sint uint 4097NULL NULL NULL NULL NULL NULL NULL NULL NULL 4098SHOW CREATE TABLE t1; 4099Table Create Table 4100t1 CREATE TABLE `t1` ( 4101 `c0` date DEFAULT NULL, 4102 `string` varchar(10) DEFAULT NULL, 4103 `date` date DEFAULT NULL, 4104 `datetime` datetime DEFAULT NULL, 4105 `time` time DEFAULT NULL, 4106 `dc` decimal(10,0) DEFAULT NULL, 4107 `dbl` double DEFAULT NULL, 4108 `sint` bigint(10) DEFAULT NULL, 4109 `uint` bigint(20) unsigned DEFAULT NULL 4110) ENGINE=MyISAM DEFAULT CHARSET=latin1 4111DROP TABLE t1; 4112SET sql_mode=DEFAULT; 4113# 4114# MDEV-17330 Wrong result for 0 + LEAST(TIME'-10:00:00',TIME'10:00:00') 4115# 4116SELECT 0 + LEAST(TIME'-10:00:00',TIME'10:00:00') AS c; 4117c 4118-100000 4119# 4120# End of 10.4 tests 4121# 4122# 4123# Start of 10.5 tests 4124# 4125# 4126# MDEV-20332 Wrong UNSIGNED metadata flag returned for COALESCE(unsigned_field,timestamp_field) 4127# 4128CREATE TABLE t1 (a INT UNSIGNED, b TIMESTAMP); 4129SELECT COALESCE(a,b) FROM t1; 4130Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr 4131def COALESCE(a,b) 253 19 0 Y 0 39 8 4132COALESCE(a,b) 4133DROP TABLE t1; 4134# 4135# MDEV-20353 Add separate type handlers for unsigned integer data types 4136# 4137# Constant 4138SELECT 1=ROW(1,1); 4139ERROR HY000: Illegal parameter data types int and row for operation '=' 4140SELECT -1=ROW(1,1); 4141ERROR HY000: Illegal parameter data types int and row for operation '=' 4142SELECT 9223372036854775807=ROW(1,1); 4143ERROR HY000: Illegal parameter data types bigint and row for operation '=' 4144SELECT 9223372036854775808=ROW(1,1); 4145ERROR HY000: Illegal parameter data types bigint unsigned and row for operation '=' 4146SELECT 18446744073709551615=ROW(1,1); 4147ERROR HY000: Illegal parameter data types bigint unsigned and row for operation '=' 4148# COALESCE 4149CREATE TABLE t1 (a TINYINT UNSIGNED, b TINYINT); 4150SELECT COALESCE(a,a)=ROW(1,1) FROM t1; 4151ERROR HY000: Illegal parameter data types tiny unsigned and row for operation '=' 4152SELECT COALESCE(b,b)=ROW(1,1) FROM t1; 4153ERROR HY000: Illegal parameter data types tinyint and row for operation '=' 4154DROP TABLE t1; 4155CREATE TABLE t1 (a SMALLINT UNSIGNED, b SMALLINT); 4156SELECT COALESCE(a,a)=ROW(1,1) FROM t1; 4157ERROR HY000: Illegal parameter data types smallint unsigned and row for operation '=' 4158SELECT COALESCE(b,b)=ROW(1,1) FROM t1; 4159ERROR HY000: Illegal parameter data types smallint and row for operation '=' 4160DROP TABLE t1; 4161CREATE TABLE t1 (a MEDIUMINT UNSIGNED, b MEDIUMINT); 4162SELECT COALESCE(a,a)=ROW(1,1) FROM t1; 4163ERROR HY000: Illegal parameter data types mediumint unsigned and row for operation '=' 4164SELECT COALESCE(b,b)=ROW(1,1) FROM t1; 4165ERROR HY000: Illegal parameter data types mediumint and row for operation '=' 4166DROP TABLE t1; 4167CREATE TABLE t1 (a INT UNSIGNED, b INT); 4168SELECT COALESCE(a,a)=ROW(1,1) FROM t1; 4169ERROR HY000: Illegal parameter data types int unsigned and row for operation '=' 4170SELECT COALESCE(b,b)=ROW(1,1) FROM t1; 4171ERROR HY000: Illegal parameter data types int and row for operation '=' 4172DROP TABLE t1; 4173CREATE TABLE t1 (a BIGINT UNSIGNED, b BIGINT); 4174SELECT COALESCE(a,a)=ROW(1,1) FROM t1; 4175ERROR HY000: Illegal parameter data types bigint unsigned and row for operation '=' 4176SELECT COALESCE(b,b)=ROW(1,1) FROM t1; 4177ERROR HY000: Illegal parameter data types bigint and row for operation '=' 4178DROP TABLE t1; 4179# COALESCE for different types integer types, with the UNSIGNED flag 4180CREATE TABLE t1 (a1 TINYINT UNSIGNED, a2 SMALLINT UNSIGNED); 4181SELECT COALESCE(a1,a2)=ROW(1,1) FROM t1; 4182ERROR HY000: Illegal parameter data types smallint unsigned and row for operation '=' 4183DROP TABLE t1; 4184CREATE TABLE t1 (a1 SMALLINT UNSIGNED, a2 MEDIUMINT UNSIGNED); 4185SELECT COALESCE(a1,a2)=ROW(1,1) FROM t1; 4186ERROR HY000: Illegal parameter data types mediumint unsigned and row for operation '=' 4187DROP TABLE t1; 4188CREATE TABLE t1 (a1 MEDIUMINT UNSIGNED, a2 INT UNSIGNED); 4189SELECT COALESCE(a1,a2)=ROW(1,1) FROM t1; 4190ERROR HY000: Illegal parameter data types int unsigned and row for operation '=' 4191DROP TABLE t1; 4192CREATE TABLE t1 (a1 INT UNSIGNED, a2 BIGINT UNSIGNED); 4193SELECT COALESCE(a1,a2)=ROW(1,1) FROM t1; 4194ERROR HY000: Illegal parameter data types bigint unsigned and row for operation '=' 4195DROP TABLE t1; 4196# COALESCE for different types integer types, without the UNSIGNED flag 4197CREATE TABLE t1 (a1 TINYINT, a2 SMALLINT); 4198SELECT COALESCE(a1,a2)=ROW(1,1) FROM t1; 4199ERROR HY000: Illegal parameter data types smallint and row for operation '=' 4200DROP TABLE t1; 4201CREATE TABLE t1 (a1 SMALLINT, a2 MEDIUMINT); 4202SELECT COALESCE(a1,a2)=ROW(1,1) FROM t1; 4203ERROR HY000: Illegal parameter data types mediumint and row for operation '=' 4204DROP TABLE t1; 4205CREATE TABLE t1 (a1 MEDIUMINT, a2 INT); 4206SELECT COALESCE(a1,a2)=ROW(1,1) FROM t1; 4207ERROR HY000: Illegal parameter data types int and row for operation '=' 4208DROP TABLE t1; 4209CREATE TABLE t1 (a1 INT, a2 BIGINT); 4210SELECT COALESCE(a1,a2)=ROW(1,1) FROM t1; 4211ERROR HY000: Illegal parameter data types bigint and row for operation '=' 4212DROP TABLE t1; 4213# Operator + 4214CREATE TABLE t1 (a TINYINT UNSIGNED, b TINYINT); 4215SELECT (a+a)=ROW(1,1) FROM t1; 4216ERROR HY000: Illegal parameter data types int unsigned and row for operation '=' 4217SELECT (b+b)=ROW(1,1) FROM t1; 4218ERROR HY000: Illegal parameter data types int and row for operation '=' 4219DROP TABLE t1; 4220CREATE TABLE t1 (a SMALLINT UNSIGNED, b SMALLINT); 4221SELECT (a+a)=ROW(1,1) FROM t1; 4222ERROR HY000: Illegal parameter data types int unsigned and row for operation '=' 4223SELECT (b+b)=ROW(1,1) FROM t1; 4224ERROR HY000: Illegal parameter data types int and row for operation '=' 4225DROP TABLE t1; 4226CREATE TABLE t1 (a MEDIUMINT UNSIGNED, b MEDIUMINT); 4227SELECT (a+a)=ROW(1,1) FROM t1; 4228ERROR HY000: Illegal parameter data types int unsigned and row for operation '=' 4229SELECT (b+b)=ROW(1,1) FROM t1; 4230ERROR HY000: Illegal parameter data types bigint and row for operation '=' 4231DROP TABLE t1; 4232CREATE TABLE t1 (a INT UNSIGNED, b INT); 4233SELECT (a+a)=ROW(1,1) FROM t1; 4234ERROR HY000: Illegal parameter data types bigint unsigned and row for operation '=' 4235SELECT (b+b)=ROW(1,1) FROM t1; 4236ERROR HY000: Illegal parameter data types bigint and row for operation '=' 4237DROP TABLE t1; 4238CREATE TABLE t1 (a BIGINT UNSIGNED, b BIGINT); 4239SELECT (a+a)=ROW(1,1) FROM t1; 4240ERROR HY000: Illegal parameter data types bigint unsigned and row for operation '=' 4241SELECT (b+b)=ROW(1,1) FROM t1; 4242ERROR HY000: Illegal parameter data types bigint and row for operation '=' 4243DROP TABLE t1; 4244# Opetator + for different types integer types, with the UNSIGNED flag 4245CREATE TABLE t1 (a1 TINYINT UNSIGNED, a2 SMALLINT UNSIGNED); 4246SELECT (a1+a2)=ROW(1,1) FROM t1; 4247ERROR HY000: Illegal parameter data types int unsigned and row for operation '=' 4248DROP TABLE t1; 4249CREATE TABLE t1 (a1 SMALLINT UNSIGNED, a2 MEDIUMINT UNSIGNED); 4250SELECT (a1+a2)=ROW(1,1) FROM t1; 4251ERROR HY000: Illegal parameter data types int unsigned and row for operation '=' 4252DROP TABLE t1; 4253CREATE TABLE t1 (a1 MEDIUMINT UNSIGNED, a2 INT UNSIGNED); 4254SELECT (a1+a2)=ROW(1,1) FROM t1; 4255ERROR HY000: Illegal parameter data types bigint unsigned and row for operation '=' 4256DROP TABLE t1; 4257CREATE TABLE t1 (a1 INT UNSIGNED, a2 BIGINT UNSIGNED); 4258SELECT (a1+a2)=ROW(1,1) FROM t1; 4259ERROR HY000: Illegal parameter data types bigint unsigned and row for operation '=' 4260DROP TABLE t1; 4261# Operator + for different types integer types, without the UNSIGNED flag 4262CREATE TABLE t1 (a1 TINYINT, a2 SMALLINT); 4263SELECT (a1+a2)=ROW(1,1) FROM t1; 4264ERROR HY000: Illegal parameter data types int and row for operation '=' 4265DROP TABLE t1; 4266CREATE TABLE t1 (a1 SMALLINT, a2 MEDIUMINT); 4267SELECT (a1+a2)=ROW(1,1) FROM t1; 4268ERROR HY000: Illegal parameter data types bigint and row for operation '=' 4269DROP TABLE t1; 4270CREATE TABLE t1 (a1 MEDIUMINT, a2 INT); 4271SELECT (a1+a2)=ROW(1,1) FROM t1; 4272ERROR HY000: Illegal parameter data types bigint and row for operation '=' 4273DROP TABLE t1; 4274CREATE TABLE t1 (a1 INT, a2 BIGINT); 4275SELECT (a1+a2)=ROW(1,1) FROM t1; 4276ERROR HY000: Illegal parameter data types bigint and row for operation '=' 4277DROP TABLE t1; 4278# SUM 4279CREATE TABLE t1 (a TINYINT UNSIGNED, b TINYINT); 4280SELECT MAX(a)=ROW(1,1) FROM t1; 4281ERROR HY000: Illegal parameter data types tiny unsigned and row for operation '=' 4282SELECT MAX(b)=ROW(1,1) FROM t1; 4283ERROR HY000: Illegal parameter data types tinyint and row for operation '=' 4284DROP TABLE t1; 4285CREATE TABLE t1 (a SMALLINT UNSIGNED, b SMALLINT); 4286SELECT MAX(a)=ROW(1,1) FROM t1; 4287ERROR HY000: Illegal parameter data types smallint unsigned and row for operation '=' 4288SELECT MAX(b)=ROW(1,1) FROM t1; 4289ERROR HY000: Illegal parameter data types smallint and row for operation '=' 4290DROP TABLE t1; 4291CREATE TABLE t1 (a MEDIUMINT UNSIGNED, b MEDIUMINT); 4292SELECT MAX(a)=ROW(1,1) FROM t1; 4293ERROR HY000: Illegal parameter data types mediumint unsigned and row for operation '=' 4294SELECT MAX(b)=ROW(1,1) FROM t1; 4295ERROR HY000: Illegal parameter data types mediumint and row for operation '=' 4296DROP TABLE t1; 4297CREATE TABLE t1 (a INT UNSIGNED, b INT); 4298SELECT MAX(a)=ROW(1,1) FROM t1; 4299ERROR HY000: Illegal parameter data types int unsigned and row for operation '=' 4300SELECT MAX(b)=ROW(1,1) FROM t1; 4301ERROR HY000: Illegal parameter data types int and row for operation '=' 4302DROP TABLE t1; 4303CREATE TABLE t1 (a BIGINT UNSIGNED, b BIGINT); 4304SELECT MAX(a)=ROW(1,1) FROM t1; 4305ERROR HY000: Illegal parameter data types bigint unsigned and row for operation '=' 4306SELECT MAX(b)=ROW(1,1) FROM t1; 4307ERROR HY000: Illegal parameter data types bigint and row for operation '=' 4308DROP TABLE t1; 4309# HEX hybrid 4310SELECT 0x20+ROW(1,1); 4311ERROR HY000: Illegal parameter data types bigint unsigned and row for operation '+' 4312# System variables 4313SELECT @@max_allowed_packet=ROW(1,1); 4314ERROR HY000: Illegal parameter data types bigint unsigned and row for operation '=' 4315# 4316# End of 10.5 tests 4317# 4318