1drop table if exists t1, t2; 2create table t1 (v varchar(30), c char(3), e enum('abc','def','ghi'), t text); 3truncate table vchar; 4show create table t1; 5Table Create Table 6t1 CREATE TABLE `t1` ( 7 `v` varchar(30) DEFAULT NULL, 8 `c` char(3) DEFAULT NULL, 9 `e` enum('abc','def','ghi') DEFAULT NULL, 10 `t` text DEFAULT NULL 11) ENGINE=MyISAM DEFAULT CHARSET=latin1 12show create table vchar; 13Table Create Table 14vchar CREATE TABLE `vchar` ( 15 `v` varchar(30)/*old*/ DEFAULT NULL, 16 `c` char(3) DEFAULT NULL, 17 `e` enum('abc','def','ghi') DEFAULT NULL, 18 `t` text DEFAULT NULL 19) ENGINE=MyISAM DEFAULT CHARSET=latin1 20insert into t1 values ('abc', 'de', 'ghi', 'jkl'); 21insert into t1 values ('abc ', 'de ', 'ghi', 'jkl '); 22insert into t1 values ('abc ', 'd ', 'ghi', 'jkl '); 23insert into vchar values ('abc', 'de', 'ghi', 'jkl'); 24insert into vchar values ('abc ', 'de ', 'ghi', 'jkl '); 25insert into vchar values ('abc ', 'd ', 'ghi', 'jkl '); 26select length(v),length(c),length(e),length(t) from t1; 27length(v) length(c) length(e) length(t) 283 2 3 3 294 2 3 4 307 1 3 7 31select length(v),length(c),length(e),length(t) from vchar; 32length(v) length(c) length(e) length(t) 333 2 3 3 343 2 3 4 353 1 3 7 36alter table vchar add i int; 37show create table vchar; 38Table Create Table 39vchar CREATE TABLE `vchar` ( 40 `v` varchar(30) DEFAULT NULL, 41 `c` char(3) DEFAULT NULL, 42 `e` enum('abc','def','ghi') DEFAULT NULL, 43 `t` text DEFAULT NULL, 44 `i` int(11) DEFAULT NULL 45) ENGINE=MyISAM DEFAULT CHARSET=latin1 46select length(v),length(c),length(e),length(t) from vchar; 47length(v) length(c) length(e) length(t) 483 2 3 3 493 2 3 4 503 1 3 7 51drop table t1, vchar; 52create table t1 (v varchar(20)); 53insert into t1 values('a '); 54select v='a' from t1; 55v='a' 561 57select binary v='a' from t1; 58binary v='a' 590 60select binary v='a ' from t1; 61binary v='a ' 621 63insert into t1 values('a'); 64alter table t1 add primary key (v); 65ERROR 23000: Duplicate entry 'a' for key 'PRIMARY' 66drop table t1; 67create table t1 (v varbinary(20)); 68insert into t1 values('a'); 69insert into t1 values('a '); 70alter table t1 add primary key (v); 71drop table t1; 72create table t1 (v varchar(254), index (v)); 73insert into t1 values ("This is a test "); 74insert into t1 values ("Some sample data"); 75insert into t1 values (" garbage "); 76insert into t1 values (" This is a test "); 77insert into t1 values ("This is a test"); 78insert into t1 values ("Hello world"); 79insert into t1 values ("Foo bar"); 80insert into t1 values ("This is a test"); 81insert into t1 values ("MySQL varchar test"); 82insert into t1 values ("test MySQL varchar"); 83insert into t1 values ("This is a long string to have some random length data included"); 84insert into t1 values ("Short string"); 85insert into t1 values ("VSS"); 86insert into t1 values ("Some samples"); 87insert into t1 values ("Bar foo"); 88insert into t1 values ("Bye"); 89select * from t1 where v like 'This is a test' order by v; 90v 91This is a test 92This is a test 93select * from t1 where v='This is a test' order by v; 94v 95This is a test 96This is a test 97This is a test 98select * from t1 where v like 'S%' order by v; 99v 100Short string 101Some sample data 102Some samples 103explain select * from t1 where v like 'This is a test' order by v; 104id select_type table type possible_keys key key_len ref rows Extra 1051 SIMPLE t1 range v v 257 NULL 3 Using where; Using index 106explain select * from t1 where v='This is a test' order by v; 107id select_type table type possible_keys key key_len ref rows Extra 1081 SIMPLE t1 ref v v 257 const 3 Using where; Using index 109explain select * from t1 where v like 'S%' order by v; 110id select_type table type possible_keys key key_len ref rows Extra 1111 SIMPLE t1 range v v 257 NULL 3 Using where; Using index 112alter table t1 change v v varchar(255); 113select * from t1 where v like 'This is a test' order by v; 114v 115This is a test 116This is a test 117select * from t1 where v='This is a test' order by v; 118v 119This is a test 120This is a test 121This is a test 122select * from t1 where v like 'S%' order by v; 123v 124Short string 125Some sample data 126Some samples 127explain select * from t1 where v like 'This is a test' order by v; 128id select_type table type possible_keys key key_len ref rows Extra 1291 SIMPLE t1 range v v 258 NULL 3 Using where; Using index 130explain select * from t1 where v='This is a test' order by v; 131id select_type table type possible_keys key key_len ref rows Extra 1321 SIMPLE t1 ref v v 258 const 3 Using where; Using index 133explain select * from t1 where v like 'S%' order by v; 134id select_type table type possible_keys key key_len ref rows Extra 1351 SIMPLE t1 range v v 258 NULL 3 Using where; Using index 136alter table t1 change v v varchar(256); 137select * from t1 where v like 'This is a test' order by v; 138v 139This is a test 140This is a test 141select * from t1 where v='This is a test' order by v; 142v 143This is a test 144This is a test 145This is a test 146select * from t1 where v like 'S%' order by v; 147v 148Short string 149Some sample data 150Some samples 151explain select * from t1 where v like 'This is a test' order by v; 152id select_type table type possible_keys key key_len ref rows Extra 1531 SIMPLE t1 range v v 259 NULL 3 Using where; Using index 154explain select * from t1 where v='This is a test' order by v; 155id select_type table type possible_keys key key_len ref rows Extra 1561 SIMPLE t1 ref v v 259 const 3 Using where; Using index 157explain select * from t1 where v like 'S%' order by v; 158id select_type table type possible_keys key key_len ref rows Extra 1591 SIMPLE t1 range v v 259 NULL 3 Using where; Using index 160alter table t1 change v v varchar(257); 161select * from t1 where v like 'This is a test' order by v; 162v 163This is a test 164This is a test 165select * from t1 where v='This is a test' order by v; 166v 167This is a test 168This is a test 169This is a test 170select * from t1 where v like 'S%' order by v; 171v 172Short string 173Some sample data 174Some samples 175explain select * from t1 where v like 'This is a test' order by v; 176id select_type table type possible_keys key key_len ref rows Extra 1771 SIMPLE t1 range v v 260 NULL 3 Using where; Using index 178explain select * from t1 where v='This is a test' order by v; 179id select_type table type possible_keys key key_len ref rows Extra 1801 SIMPLE t1 ref v v 260 const 3 Using where; Using index 181explain select * from t1 where v like 'S%' order by v; 182id select_type table type possible_keys key key_len ref rows Extra 1831 SIMPLE t1 range v v 260 NULL 3 Using where; Using index 184alter table t1 change v v varchar(258); 185select * from t1 where v like 'This is a test' order by v; 186v 187This is a test 188This is a test 189select * from t1 where v='This is a test' order by v; 190v 191This is a test 192This is a test 193This is a test 194select * from t1 where v like 'S%' order by v; 195v 196Short string 197Some sample data 198Some samples 199explain select * from t1 where v like 'This is a test' order by v; 200id select_type table type possible_keys key key_len ref rows Extra 2011 SIMPLE t1 range v v 261 NULL 3 Using where; Using index 202explain select * from t1 where v='This is a test' order by v; 203id select_type table type possible_keys key key_len ref rows Extra 2041 SIMPLE t1 ref v v 261 const 3 Using where; Using index 205explain select * from t1 where v like 'S%' order by v; 206id select_type table type possible_keys key key_len ref rows Extra 2071 SIMPLE t1 range v v 261 NULL 3 Using where; Using index 208alter table t1 change v v varchar(259); 209select * from t1 where v like 'This is a test' order by v; 210v 211This is a test 212This is a test 213select * from t1 where v='This is a test' order by v; 214v 215This is a test 216This is a test 217This is a test 218select * from t1 where v like 'S%' order by v; 219v 220Short string 221Some sample data 222Some samples 223explain select * from t1 where v like 'This is a test' order by v; 224id select_type table type possible_keys key key_len ref rows Extra 2251 SIMPLE t1 range v v 262 NULL 3 Using where; Using index 226explain select * from t1 where v='This is a test' order by v; 227id select_type table type possible_keys key key_len ref rows Extra 2281 SIMPLE t1 ref v v 262 const 3 Using where; Using index 229explain select * from t1 where v like 'S%' order by v; 230id select_type table type possible_keys key key_len ref rows Extra 2311 SIMPLE t1 range v v 262 NULL 3 Using where; Using index 232alter table t1 change v v varchar(258); 233select * from t1 where v like 'This is a test' order by v; 234v 235This is a test 236This is a test 237select * from t1 where v='This is a test' order by v; 238v 239This is a test 240This is a test 241This is a test 242select * from t1 where v like 'S%' order by v; 243v 244Short string 245Some sample data 246Some samples 247explain select * from t1 where v like 'This is a test' order by v; 248id select_type table type possible_keys key key_len ref rows Extra 2491 SIMPLE t1 range v v 261 NULL 3 Using where; Using index 250explain select * from t1 where v='This is a test' order by v; 251id select_type table type possible_keys key key_len ref rows Extra 2521 SIMPLE t1 ref v v 261 const 3 Using where; Using index 253explain select * from t1 where v like 'S%' order by v; 254id select_type table type possible_keys key key_len ref rows Extra 2551 SIMPLE t1 range v v 261 NULL 3 Using where; Using index 256alter table t1 change v v varchar(257); 257select * from t1 where v like 'This is a test' order by v; 258v 259This is a test 260This is a test 261select * from t1 where v='This is a test' order by v; 262v 263This is a test 264This is a test 265This is a test 266select * from t1 where v like 'S%' order by v; 267v 268Short string 269Some sample data 270Some samples 271explain select * from t1 where v like 'This is a test' order by v; 272id select_type table type possible_keys key key_len ref rows Extra 2731 SIMPLE t1 range v v 260 NULL 3 Using where; Using index 274explain select * from t1 where v='This is a test' order by v; 275id select_type table type possible_keys key key_len ref rows Extra 2761 SIMPLE t1 ref v v 260 const 3 Using where; Using index 277explain select * from t1 where v like 'S%' order by v; 278id select_type table type possible_keys key key_len ref rows Extra 2791 SIMPLE t1 range v v 260 NULL 3 Using where; Using index 280alter table t1 change v v varchar(256); 281select * from t1 where v like 'This is a test' order by v; 282v 283This is a test 284This is a test 285select * from t1 where v='This is a test' order by v; 286v 287This is a test 288This is a test 289This is a test 290select * from t1 where v like 'S%' order by v; 291v 292Short string 293Some sample data 294Some samples 295explain select * from t1 where v like 'This is a test' order by v; 296id select_type table type possible_keys key key_len ref rows Extra 2971 SIMPLE t1 range v v 259 NULL 3 Using where; Using index 298explain select * from t1 where v='This is a test' order by v; 299id select_type table type possible_keys key key_len ref rows Extra 3001 SIMPLE t1 ref v v 259 const 3 Using where; Using index 301explain select * from t1 where v like 'S%' order by v; 302id select_type table type possible_keys key key_len ref rows Extra 3031 SIMPLE t1 range v v 259 NULL 3 Using where; Using index 304alter table t1 change v v varchar(255); 305select * from t1 where v like 'This is a test' order by v; 306v 307This is a test 308This is a test 309select * from t1 where v='This is a test' order by v; 310v 311This is a test 312This is a test 313This is a test 314select * from t1 where v like 'S%' order by v; 315v 316Short string 317Some sample data 318Some samples 319explain select * from t1 where v like 'This is a test' order by v; 320id select_type table type possible_keys key key_len ref rows Extra 3211 SIMPLE t1 range v v 258 NULL 3 Using where; Using index 322explain select * from t1 where v='This is a test' order by v; 323id select_type table type possible_keys key key_len ref rows Extra 3241 SIMPLE t1 ref v v 258 const 3 Using where; Using index 325explain select * from t1 where v like 'S%' order by v; 326id select_type table type possible_keys key key_len ref rows Extra 3271 SIMPLE t1 range v v 258 NULL 3 Using where; Using index 328alter table t1 change v v varchar(254); 329select * from t1 where v like 'This is a test' order by v; 330v 331This is a test 332This is a test 333select * from t1 where v='This is a test' order by v; 334v 335This is a test 336This is a test 337This is a test 338select * from t1 where v like 'S%' order by v; 339v 340Short string 341Some sample data 342Some samples 343explain select * from t1 where v like 'This is a test' order by v; 344id select_type table type possible_keys key key_len ref rows Extra 3451 SIMPLE t1 range v v 257 NULL 3 Using where; Using index 346explain select * from t1 where v='This is a test' order by v; 347id select_type table type possible_keys key key_len ref rows Extra 3481 SIMPLE t1 ref v v 257 const 3 Using where; Using index 349explain select * from t1 where v like 'S%' order by v; 350id select_type table type possible_keys key key_len ref rows Extra 3511 SIMPLE t1 range v v 257 NULL 3 Using where; Using index 352alter table t1 change v v varchar(253); 353alter table t1 change v v varchar(254), drop key v; 354alter table t1 change v v varchar(300), add key (v(10)); 355select * from t1 where v like 'This is a test' order by v; 356v 357This is a test 358This is a test 359select * from t1 where v='This is a test' order by v; 360v 361This is a test 362This is a test 363This is a test 364select * from t1 where v like 'S%' order by v; 365v 366Short string 367Some sample data 368Some samples 369explain select * from t1 where v like 'This is a test' order by v; 370id select_type table type possible_keys key key_len ref rows Extra 3711 SIMPLE t1 range v v 13 NULL 4 Using where; Using filesort 372explain select * from t1 where v='This is a test' order by v; 373id select_type table type possible_keys key key_len ref rows Extra 3741 SIMPLE t1 ref v v 13 const 4 Using where 375explain select * from t1 where v like 'S%' order by v; 376id select_type table type possible_keys key key_len ref rows Extra 3771 SIMPLE t1 range v v 13 NULL 3 Using where; Using filesort 378drop table t1; 379create table t1 (pkcol varchar(16), othercol varchar(16), primary key (pkcol)); 380insert into t1 values ('test', 'something'); 381update t1 set othercol='somethingelse' where pkcol='test'; 382select * from t1; 383pkcol othercol 384test somethingelse 385drop table t1; 386create table t1 (a int, b varchar(12)); 387insert into t1 values (1, 'A'), (22, NULL); 388create table t2 (a int); 389insert into t2 values (22), (22); 390select t1.a, t1.b, min(t1.b) from t1 inner join t2 ON t2.a = t1.a 391group by t1.b, t1.a; 392a b min(t1.b) 39322 NULL NULL 394drop table t1, t2; 395create table t1 (f1 varchar(65500)); 396create index index1 on t1(f1(10)); 397show create table t1; 398Table Create Table 399t1 CREATE TABLE `t1` ( 400 `f1` varchar(65500) DEFAULT NULL, 401 KEY `index1` (`f1`(10)) 402) ENGINE=MyISAM DEFAULT CHARSET=latin1 403alter table t1 modify f1 varchar(255); 404show create table t1; 405Table Create Table 406t1 CREATE TABLE `t1` ( 407 `f1` varchar(255) DEFAULT NULL, 408 KEY `index1` (`f1`(10)) 409) ENGINE=MyISAM DEFAULT CHARSET=latin1 410alter table t1 modify f1 tinytext; 411show create table t1; 412Table Create Table 413t1 CREATE TABLE `t1` ( 414 `f1` tinytext DEFAULT NULL, 415 KEY `index1` (`f1`(10)) 416) ENGINE=MyISAM DEFAULT CHARSET=latin1 417drop table t1; 418DROP TABLE IF EXISTS t1; 419CREATE TABLE t1(f1 VARCHAR(100) DEFAULT 'test'); 420INSERT INTO t1 VALUES(SUBSTR(f1, 1, 3)); 421DROP TABLE IF EXISTS t1; 422CREATE TABLE t1(f1 CHAR(100) DEFAULT 'test'); 423INSERT INTO t1 VALUES(SUBSTR(f1, 1, 3)); 424DROP TABLE IF EXISTS t1; 425drop table if exists t1, t2, t3; 426create table t3 ( 427id int(11), 428en varchar(255) character set utf8, 429cz varchar(255) character set utf8 430); 431truncate table t3; 432insert into t3 (id, en, cz) values 433(1,'en string 1','cz string 1'), 434(2,'en string 2','cz string 2'), 435(3,'en string 3','cz string 3'); 436create table t1 ( 437id int(11), 438name_id int(11) 439); 440insert into t1 (id, name_id) values (1,1), (2,3), (3,3); 441create table t2 (id int(11)); 442insert into t2 (id) values (1), (2), (3); 443select t1.*, t2.id, t3.en, t3.cz from t1 left join t2 on t1.id=t2.id 444left join t3 on t1.id=t3.id order by t3.id; 445Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr 446def test t1 t1 id id 3 11 1 Y 32768 0 63 447def test t1 t1 name_id name_id 3 11 1 Y 32768 0 63 448def test t2 t2 id id 3 11 1 Y 32768 0 63 449def test t3 t3 en en 253 255 11 Y 0 0 8 450def test t3 t3 cz cz 253 255 11 Y 0 0 8 451id name_id id en cz 4521 1 1 en string 1 cz string 1 4532 3 2 en string 2 cz string 2 4543 3 3 en string 3 cz string 3 455drop table t1, t2, t3; 456CREATE TABLE t1 (a CHAR(2)); 457INSERT INTO t1 VALUES (10), (50), (30), ('1a'), (60), ('t'); 458SELECT a,(a + 0) FROM t1 ORDER BY a; 459a (a + 0) 46010 10 4611a 1 46230 30 46350 50 46460 60 465t 0 466Warnings: 467Warning 1292 Truncated incorrect DOUBLE value: '1a' 468Warning 1292 Truncated incorrect DOUBLE value: 't ' 469SELECT a,(a DIV 2) FROM t1 ORDER BY a; 470a (a DIV 2) 47110 5 4721a 0 47330 15 47450 25 47560 30 476t 0 477Warnings: 478Warning 1292 Truncated incorrect DECIMAL value: '1a' 479Warning 1918 Encountered illegal value '' when converting to DECIMAL 480Warning 1292 Truncated incorrect DECIMAL value: 't ' 481SELECT a,CAST(a AS SIGNED) FROM t1 ORDER BY a; 482a CAST(a AS SIGNED) 48310 10 4841a 1 48530 30 48650 50 48760 60 488t 0 489Warnings: 490Warning 1292 Truncated incorrect INTEGER value: '1a' 491Warning 1292 Truncated incorrect INTEGER value: 't' 492DROP TABLE t1; 493CREATE TABLE t1 (a VARCHAR(16)); 494INSERT INTO t1 VALUES ('5'), ('s'), (''); 495SELECT 5 = a FROM t1; 4965 = a 4971 4980 4990 500Warnings: 501Warning 1292 Truncated incorrect DOUBLE value: 's' 502Warning 1292 Truncated incorrect DOUBLE value: '' 503DROP TABLE t1; 504CREATE TABLE t1 (a CHAR(16)); 505INSERT INTO t1 VALUES ('5'), ('s'), (''); 506SELECT 5 = a FROM t1; 5075 = a 5081 5090 5100 511Warnings: 512Warning 1292 Truncated incorrect DOUBLE value: 's ' 513Warning 1292 Truncated incorrect DOUBLE value: ' ' 514DROP TABLE t1; 515# 516# MDEV-13530 VARBINARY doesn't convert to to BLOB for sizes 65533, 65534 and 65535 517# 518set sql_mode=''; 519CREATE TABLE t1 (c1 VARBINARY(65532)); 520DESCRIBE t1; 521Field Type Null Key Default Extra 522c1 varbinary(65532) YES NULL 523DROP TABLE t1; 524CREATE TABLE t1 (c1 VARBINARY(65533)); 525Warnings: 526Note 1246 Converting column 'c1' from VARBINARY to BLOB 527DESCRIBE t1; 528Field Type Null Key Default Extra 529c1 blob YES NULL 530DROP TABLE t1; 531CREATE TABLE t1 (c1 VARBINARY(65534)); 532Warnings: 533Note 1246 Converting column 'c1' from VARBINARY to BLOB 534DESCRIBE t1; 535Field Type Null Key Default Extra 536c1 blob YES NULL 537DROP TABLE t1; 538CREATE TABLE t1 (c1 VARBINARY(65535)); 539Warnings: 540Note 1246 Converting column 'c1' from VARBINARY to BLOB 541DESCRIBE t1; 542Field Type Null Key Default Extra 543c1 blob YES NULL 544DROP TABLE t1; 545CREATE TABLE t1 (c1 VARBINARY(65536)); 546Warnings: 547Note 1246 Converting column 'c1' from VARBINARY to BLOB 548DESCRIBE t1; 549Field Type Null Key Default Extra 550c1 mediumblob YES NULL 551DROP TABLE t1; 552CREATE TABLE t1 (c1 VARCHAR(65532)); 553DESCRIBE t1; 554Field Type Null Key Default Extra 555c1 varchar(65532) YES NULL 556DROP TABLE t1; 557CREATE TABLE t1 (c1 VARCHAR(65533)); 558Warnings: 559Note 1246 Converting column 'c1' from VARCHAR to TEXT 560DESCRIBE t1; 561Field Type Null Key Default Extra 562c1 text YES NULL 563DROP TABLE t1; 564CREATE TABLE t1 (c1 VARCHAR(65534)); 565Warnings: 566Note 1246 Converting column 'c1' from VARCHAR to TEXT 567DESCRIBE t1; 568Field Type Null Key Default Extra 569c1 text YES NULL 570DROP TABLE t1; 571CREATE TABLE t1 (c1 VARCHAR(65535)); 572Warnings: 573Note 1246 Converting column 'c1' from VARCHAR to TEXT 574DESCRIBE t1; 575Field Type Null Key Default Extra 576c1 text YES NULL 577DROP TABLE t1; 578CREATE TABLE t1 (c1 VARCHAR(65536)); 579Warnings: 580Note 1246 Converting column 'c1' from VARCHAR to TEXT 581DESCRIBE t1; 582Field Type Null Key Default Extra 583c1 mediumtext YES NULL 584DROP TABLE t1; 585set sql_mode=default; 586CREATE TABLE t1 (c1 VARCHAR(65536)); 587ERROR 42000: Column length too big for column 'c1' (max = 65532); use BLOB or TEXT instead 588# 589# End of 5.5 tests 590# 591# 592# MDEV-6950 Bad results with joins comparing DATE and INT/ENUM/VARCHAR columns 593# 594CREATE TABLE t1 (c1 DATE PRIMARY KEY); 595INSERT INTO t1 VALUES ('2001-01-01'); 596CREATE TABLE t2 (c1 VARCHAR(20)); 597INSERT INTO t2 VALUES ('2001-01-01'); 598INSERT INTO t2 VALUES ('2001/01/01'); 599SELECT t1.* FROM t1,t2 WHERE t1.c1=t2.c1; 600c1 6012001-01-01 6022001-01-01 603SELECT t1.* FROM t1 LEFT JOIN t2 ON t1.c1=t2.c1; 604c1 6052001-01-01 6062001-01-01 607ALTER TABLE t2 ADD PRIMARY KEY(c1); 608SELECT t1.* FROM t1,t2 WHERE t1.c1=t2.c1; 609c1 6102001-01-01 6112001-01-01 612EXPLAIN SELECT t1.* FROM t1,t2 WHERE t1.c1=t2.c1; 613id select_type table type possible_keys key key_len ref rows Extra 6141 SIMPLE t1 system PRIMARY NULL NULL NULL 1 6151 SIMPLE t2 index PRIMARY PRIMARY 22 NULL 2 Using where; Using index 616SELECT t1.* FROM t1 LEFT JOIN t2 ON t1.c1=t2.c1; 617c1 6182001-01-01 6192001-01-01 620# t2 should NOT be eliminated 621EXPLAIN SELECT t1.* FROM t1 LEFT JOIN t2 ON t1.c1=t2.c1; 622id select_type table type possible_keys key key_len ref rows Extra 6231 SIMPLE t1 system NULL NULL NULL NULL 1 6241 SIMPLE t2 index PRIMARY PRIMARY 22 NULL 2 Using where; Using index 625DROP TABLE IF EXISTS t1,t2; 626# 627# MDEV-6989 BINARY and COLLATE xxx_bin comparisions are not used for optimization in some cases 628# 629CREATE TABLE t1 (c1 VARCHAR(20) CHARACTER SET latin1, PRIMARY KEY(c1)); 630INSERT INTO t1 VALUES ('a'),('b'),('c'),('d'); 631SELECT * FROM t1 WHERE c1=BINARY 'a'; 632c1 633a 634EXPLAIN SELECT * FROM t1 WHERE c1=BINARY 'a'; 635id select_type table type possible_keys key key_len ref rows Extra 6361 SIMPLE t1 const PRIMARY PRIMARY 22 const 1 Using index 637SELECT * FROM t1 WHERE c1=_latin1'a' COLLATE latin1_bin; 638c1 639a 640EXPLAIN SELECT * FROM t1 WHERE c1=_latin1'a' COLLATE latin1_bin; 641id select_type table type possible_keys key key_len ref rows Extra 6421 SIMPLE t1 const PRIMARY PRIMARY 22 const 1 Using index 643DROP TABLE t1; 644CREATE TABLE t1 (c1 VARCHAR(10) CHARACTER SET latin1 COLLATE latin1_bin); 645INSERT INTO t1 VALUES ('a'); 646CREATE TABLE t2 (c1 VARCHAR(10) CHARACTER SET latin1, PRIMARY KEY(c1)); 647INSERT INTO t2 VALUES ('a'),('b'); 648SELECT * FROM t1, t2 WHERE t1.c1=t2.c1; 649c1 c1 650a a 651EXPLAIN SELECT * FROM t1, t2 WHERE t1.c1=t2.c1; 652id select_type table type possible_keys key key_len ref rows Extra 6531 SIMPLE t1 system NULL NULL NULL NULL 1 6541 SIMPLE t2 const PRIMARY PRIMARY 12 const 1 Using index 655ALTER TABLE t1 MODIFY c1 VARBINARY(10); 656SELECT * FROM t1, t2 WHERE t1.c1=t2.c1; 657c1 c1 658a a 659EXPLAIN SELECT * FROM t1, t2 WHERE t1.c1=t2.c1; 660id select_type table type possible_keys key key_len ref rows Extra 6611 SIMPLE t1 system NULL NULL NULL NULL 1 6621 SIMPLE t2 const PRIMARY PRIMARY 12 const 1 Using index 663DROP TABLE t1, t2; 664CREATE TABLE t1 (c1 VARCHAR(10) CHARACTER SET latin1 COLLATE latin1_bin); 665INSERT INTO t1 VALUES ('a'),('c'); 666CREATE TABLE t2 (c1 VARCHAR(10) CHARACTER SET latin1, PRIMARY KEY(c1)); 667INSERT INTO t2 VALUES ('a'),('b'); 668SELECT t1.* FROM t1 LEFT JOIN t2 USING (c1); 669c1 670a 671c 672# t2 should be eliminated 673EXPLAIN SELECT t1.* FROM t1 LEFT JOIN t2 USING (c1); 674id select_type table type possible_keys key key_len ref rows Extra 6751 SIMPLE t1 ALL NULL NULL NULL NULL 2 676ALTER TABLE t1 MODIFY c1 VARBINARY(10); 677SELECT t1.* FROM t1 LEFT JOIN t2 USING (c1); 678c1 679a 680c 681# t2 should be eliminated 682EXPLAIN SELECT t1.* FROM t1 LEFT JOIN t2 USING (c1); 683id select_type table type possible_keys key key_len ref rows Extra 6841 SIMPLE t1 ALL NULL NULL NULL NULL 2 685DROP TABLE t1,t2; 686# 687# End of 10.0 tests 688# 689# 690# MDEV-17551 691# Assertion `(&(&share->intern_lock)->m_mutex)->count > 0 && 692# pthread_equal(pthread_self(), (&(&share->intern_lock)->m_mutex)-> 693# thread)' failed in _ma_state_info_write or ER_CRASHED_ON_USAGE 694# upon SELECT with UNION 695# 696CREATE TABLE t1 (b BLOB, vb BLOB AS (b) VIRTUAL); 697INSERT INTO t1 (b) VALUES ('foobar'); 698SELECT 'foo' AS f1, CONVERT( 'bar' USING latin1 ) AS f2 FROM t1 699UNION 700SELECT b AS f1, CONVERT( vb USING latin1 ) AS f2 FROM t1; 701f1 f2 702foo bar 703foobar foobar 704DROP TABLE t1; 705# 706# End of 10.3 tests 707# 708SET sql_mode=''; 709CREATE TABLE t1 (c VARCHAR(1) DEFAULT 'foo'); 710ERROR 42000: Invalid default value for 'c' 711SHOW WARNINGS; 712Level Code Message 713Warning 1265 Data truncated for column 'c' at row 1 714Error 1067 Invalid default value for 'c' 715SET sql_mode='STRICT_ALL_TABLES'; 716CREATE TABLE t1 (c VARCHAR(1) DEFAULT 'foo'); 717ERROR 42000: Invalid default value for 'c' 718SHOW WARNINGS; 719Level Code Message 720Warning 1265 Data truncated for column 'c' at row 1 721Error 1067 Invalid default value for 'c' 722CREATE TABLE t1 (c VARCHAR(1)); 723SET sql_mode=''; 724ALTER TABLE t1 ALTER column c SET DEFAULT 'foo'; 725ERROR 42000: Invalid default value for 'c' 726SHOW WARNINGS; 727Level Code Message 728Warning 1265 Data truncated for column 'c' at row 1 729Error 1067 Invalid default value for 'c' 730SET sql_mode='STRICT_ALL_TABLES'; 731ALTER TABLE t1 ALTER column c SET DEFAULT 'foo'; 732ERROR 42000: Invalid default value for 'c' 733SHOW WARNINGS; 734Level Code Message 735Warning 1265 Data truncated for column 'c' at row 1 736Error 1067 Invalid default value for 'c' 737DROP TABLE t1; 738SET sql_mode=DEFAULT; 739# 740# End of 10.4 tests 741# 742# 743# Start of 10.5 tests 744# 745# 746# MDEV-15592 Column COMPRESSED should select a 'high order' datatype 747# 748TRUNCATE TABLE vchar; 749SHOW CREATE TABLE vchar; 750Table Create Table 751vchar CREATE TABLE `vchar` ( 752 `v` varchar(30)/*old*/ DEFAULT NULL, 753 `c` char(3) DEFAULT NULL, 754 `e` enum('abc','def','ghi') DEFAULT NULL, 755 `t` text DEFAULT NULL 756) ENGINE=MyISAM DEFAULT CHARSET=latin1 757ALTER TABLE vchar ADD FULLTEXT INDEX(v); 758SHOW CREATE TABLE vchar; 759Table Create Table 760vchar CREATE TABLE `vchar` ( 761 `v` varchar(30) DEFAULT NULL, 762 `c` char(3) DEFAULT NULL, 763 `e` enum('abc','def','ghi') DEFAULT NULL, 764 `t` text DEFAULT NULL, 765 FULLTEXT KEY `v` (`v`) 766) ENGINE=MyISAM DEFAULT CHARSET=latin1 767DROP TABLE vchar; 768# 769# End of 10.5 tests 770# 771