1SET column_compression_zlib_wrap=true; 2FLUSH STATUS; 3CREATE TABLE t1(a BLOB COMPRESSED, KEY(a(10))); 4ERROR HY000: Compressed column 'a' can't be used in key specification 5CREATE TABLE t1(a BLOB COMPRESSED); 6ALTER TABLE t1 ADD KEY(a(10)); 7ERROR HY000: Compressed column 'a' can't be used in key specification 8SHOW CREATE TABLE t1; 9Table Create Table 10t1 CREATE TABLE `t1` ( 11 `a` blob /*!100301 COMPRESSED*/ DEFAULT NULL 12) ENGINE=MyISAM DEFAULT CHARSET=latin1 13# Make sure column was actually compressed 14INSERT INTO t1 VALUES(REPEAT('a', 1000)); 15SELECT LEFT(a, 10), LENGTH(a) FROM t1; 16LEFT(a, 10) LENGTH(a) 17aaaaaaaaaa 1000 18SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME IN('Column_compressions', 'Column_decompressions'); 19VARIABLE_NAME VARIABLE_VALUE 20COLUMN_COMPRESSIONS 1 21COLUMN_DECOMPRESSIONS 2 22SELECT DATA_LENGTH FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test' AND TABLE_NAME='t1'; 23DATA_LENGTH 2428 25# Make sure ALTER TABLE rebuilds table 26ALTER TABLE t1 MODIFY COLUMN a BLOB; 27SHOW CREATE TABLE t1; 28Table Create Table 29t1 CREATE TABLE `t1` ( 30 `a` blob DEFAULT NULL 31) ENGINE=MyISAM DEFAULT CHARSET=latin1 32SELECT LEFT(a, 10), LENGTH(a) FROM t1; 33LEFT(a, 10) LENGTH(a) 34aaaaaaaaaa 1000 35SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME IN('Column_compressions', 'Column_decompressions'); 36VARIABLE_NAME VARIABLE_VALUE 37COLUMN_COMPRESSIONS 1 38COLUMN_DECOMPRESSIONS 3 39SELECT DATA_LENGTH FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test' AND TABLE_NAME='t1'; 40DATA_LENGTH 411008 42# Rebuild back 43ALTER TABLE t1 MODIFY COLUMN a BLOB COMPRESSED; 44SHOW CREATE TABLE t1; 45Table Create Table 46t1 CREATE TABLE `t1` ( 47 `a` blob /*!100301 COMPRESSED*/ DEFAULT NULL 48) ENGINE=MyISAM DEFAULT CHARSET=latin1 49SELECT LEFT(a, 10), LENGTH(a) FROM t1; 50LEFT(a, 10) LENGTH(a) 51aaaaaaaaaa 1000 52SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME IN('Column_compressions', 'Column_decompressions'); 53VARIABLE_NAME VARIABLE_VALUE 54COLUMN_COMPRESSIONS 2 55COLUMN_DECOMPRESSIONS 5 56SELECT DATA_LENGTH FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test' AND TABLE_NAME='t1'; 57DATA_LENGTH 5828 59# Make sure CREATE TABLE ... LIKE inherits compression 60CREATE TABLE t2 LIKE t1; 61SHOW CREATE TABLE t2; 62Table Create Table 63t2 CREATE TABLE `t2` ( 64 `a` blob /*!100301 COMPRESSED*/ DEFAULT NULL 65) ENGINE=MyISAM DEFAULT CHARSET=latin1 66DROP TABLE t2; 67# Make sure implicit CREATE TABLE ... SELECT inherits compression 68CREATE TABLE t2 SELECT * FROM t1; 69SHOW CREATE TABLE t2; 70Table Create Table 71t2 CREATE TABLE `t2` ( 72 `a` blob /*!100301 COMPRESSED*/ DEFAULT NULL 73) ENGINE=MyISAM DEFAULT CHARSET=latin1 74SELECT LEFT(a, 10), LENGTH(a) FROM t2; 75LEFT(a, 10) LENGTH(a) 76aaaaaaaaaa 1000 77SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME IN('Column_compressions', 'Column_decompressions'); 78VARIABLE_NAME VARIABLE_VALUE 79COLUMN_COMPRESSIONS 2 80COLUMN_DECOMPRESSIONS 7 81SELECT DATA_LENGTH FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test' AND TABLE_NAME='t2'; 82DATA_LENGTH 8328 84DROP TABLE t2; 85# Make sure explicit CREATE TABLE ... SELECT doesn't inherit compression 86CREATE TABLE t2(a BLOB) SELECT * FROM t1; 87SHOW CREATE TABLE t2; 88Table Create Table 89t2 CREATE TABLE `t2` ( 90 `a` blob DEFAULT NULL 91) ENGINE=MyISAM DEFAULT CHARSET=latin1 92SELECT LEFT(a, 10), LENGTH(a) FROM t2; 93LEFT(a, 10) LENGTH(a) 94aaaaaaaaaa 1000 95SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME IN('Column_compressions', 'Column_decompressions'); 96VARIABLE_NAME VARIABLE_VALUE 97COLUMN_COMPRESSIONS 2 98COLUMN_DECOMPRESSIONS 8 99SELECT DATA_LENGTH FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test' AND TABLE_NAME='t2'; 100DATA_LENGTH 1011008 102DROP TABLE t2; 103# Make sure engine change works 104ALTER TABLE t1 ENGINE=InnoDB; 105SHOW CREATE TABLE t1; 106Table Create Table 107t1 CREATE TABLE `t1` ( 108 `a` blob /*!100301 COMPRESSED*/ DEFAULT NULL 109) ENGINE=InnoDB DEFAULT CHARSET=latin1 110SELECT LEFT(a, 10), LENGTH(a) FROM t1; 111LEFT(a, 10) LENGTH(a) 112aaaaaaaaaa 1000 113SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME IN('Column_compressions', 'Column_decompressions'); 114VARIABLE_NAME VARIABLE_VALUE 115COLUMN_COMPRESSIONS 2 116COLUMN_DECOMPRESSIONS 10 117# Make sure online add column works (requires InnoDB) 118ALTER TABLE t1 ADD COLUMN b BLOB COMPRESSED DEFAULT "must be visible"; 119SELECT LEFT(a, 10), LENGTH(a), b FROM t1; 120LEFT(a, 10) LENGTH(a) b 121aaaaaaaaaa 1000 must be visible 122SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME IN('Column_compressions', 'Column_decompressions'); 123VARIABLE_NAME VARIABLE_VALUE 124COLUMN_COMPRESSIONS 2 125COLUMN_DECOMPRESSIONS 12 126ALTER TABLE t1 DROP COLUMN b; 127ALTER TABLE t1 ENGINE=MyISAM; 128TRUNCATE TABLE t1; 129# Make sure column_compression_zlib_level works 130SET column_compression_zlib_level= 1; 131INSERT INTO t1 VALUES(REPEAT('ab', 1000)); 132SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME IN('Column_compressions', 'Column_decompressions'); 133VARIABLE_NAME VARIABLE_VALUE 134COLUMN_COMPRESSIONS 3 135COLUMN_DECOMPRESSIONS 12 136SELECT DATA_LENGTH FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test' AND TABLE_NAME='t1'; 137DATA_LENGTH 13840 139TRUNCATE TABLE t1; 140SET column_compression_zlib_level= 9; 141INSERT INTO t1 VALUES(REPEAT('ab', 1000)); 142SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME IN('Column_compressions', 'Column_decompressions'); 143VARIABLE_NAME VARIABLE_VALUE 144COLUMN_COMPRESSIONS 4 145COLUMN_DECOMPRESSIONS 12 146SELECT DATA_LENGTH FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test' AND TABLE_NAME='t1'; 147DATA_LENGTH 14836 149SET column_compression_zlib_level= DEFAULT; 150TRUNCATE TABLE t1; 151# No compression, original data shorter than compressed 152INSERT INTO t1 VALUES('a'); 153SELECT a, LENGTH(a) FROM t1; 154a LENGTH(a) 155a 1 156SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME IN('Column_compressions', 'Column_decompressions'); 157VARIABLE_NAME VARIABLE_VALUE 158COLUMN_COMPRESSIONS 4 159COLUMN_DECOMPRESSIONS 12 160SELECT DATA_LENGTH FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test' AND TABLE_NAME='t1'; 161DATA_LENGTH 16220 163# Coverage for store(double) and store(longlong) 164INSERT INTO t1 VALUES(3.14),(CAST(9.8 AS DOUBLE)),(1),(''),(NULL); 165# and for sort_string() 166SELECT * FROM t1 ORDER BY a; 167a 168NULL 169 1701 1713.14 1729.8 173a 174# Coverage for val_real() and val_int() 175SELECT a+1 FROM t1 ORDER BY 1; 176a+1 177NULL 1781 1791 1802 1814.140000000000001 18210.8 183Warnings: 184Warning 1292 Truncated incorrect DOUBLE value: 'a' 185Warning 1292 Truncated incorrect DOUBLE value: '' 186Warning 1292 Truncated incorrect DOUBLE value: 'a' 187Warning 1292 Truncated incorrect DOUBLE value: '' 188SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME IN('Column_compressions', 'Column_decompressions'); 189VARIABLE_NAME VARIABLE_VALUE 190COLUMN_COMPRESSIONS 4 191COLUMN_DECOMPRESSIONS 12 192DROP TABLE t1; 193# 194# MDEV-13540 - Server crashes in copy or Assertion `0' failed in virtual 195# Field* Field_varstring_compressed::new_key_field 196# 197CREATE TABLE t1 (c1 BLOB COMPRESSED) ENGINE=MyISAM; 198INSERT IGNORE INTO t1 VALUES ('foo'),('bar'); 199CREATE TABLE t2 (c2 BLOB COMPRESSED) ENGINE=MyISAM; 200INSERT IGNORE INTO t2 VALUES ('qux'),('abc'); 201SELECT * FROM t1 WHERE c1 NOT IN ( SELECT c2 FROM t2 WHERE c2 = c1 ); 202c1 203foo 204bar 205DROP TABLE t1, t2; 206# 207# MDEV-13541 - Server crashes in next_breadth_first_tab or Assertion `0' 208# failed in Field_varstring_compressed::new_key_field 209# 210CREATE TABLE t1 (c BLOB COMPRESSED) ENGINE=InnoDB; 211INSERT INTO t1 VALUES ('foo'),('bar'); 212SELECT DISTINCT c FROM t1; 213c 214foo 215bar 216DROP TABLE t1; 217FLUSH STATUS; 218CREATE TABLE t1(a TEXT COMPRESSED, KEY(a(10))); 219ERROR HY000: Compressed column 'a' can't be used in key specification 220CREATE TABLE t1(a TEXT COMPRESSED); 221ALTER TABLE t1 ADD KEY(a(10)); 222ERROR HY000: Compressed column 'a' can't be used in key specification 223SHOW CREATE TABLE t1; 224Table Create Table 225t1 CREATE TABLE `t1` ( 226 `a` text /*!100301 COMPRESSED*/ DEFAULT NULL 227) ENGINE=MyISAM DEFAULT CHARSET=latin1 228# Make sure column was actually compressed 229INSERT INTO t1 VALUES(REPEAT('a', 1000)); 230SELECT LEFT(a, 10), LENGTH(a) FROM t1; 231LEFT(a, 10) LENGTH(a) 232aaaaaaaaaa 1000 233SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME IN('Column_compressions', 'Column_decompressions'); 234VARIABLE_NAME VARIABLE_VALUE 235COLUMN_COMPRESSIONS 1 236COLUMN_DECOMPRESSIONS 2 237SELECT DATA_LENGTH FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test' AND TABLE_NAME='t1'; 238DATA_LENGTH 23928 240# Make sure ALTER TABLE rebuilds table 241ALTER TABLE t1 MODIFY COLUMN a TEXT; 242SHOW CREATE TABLE t1; 243Table Create Table 244t1 CREATE TABLE `t1` ( 245 `a` text DEFAULT NULL 246) ENGINE=MyISAM DEFAULT CHARSET=latin1 247SELECT LEFT(a, 10), LENGTH(a) FROM t1; 248LEFT(a, 10) LENGTH(a) 249aaaaaaaaaa 1000 250SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME IN('Column_compressions', 'Column_decompressions'); 251VARIABLE_NAME VARIABLE_VALUE 252COLUMN_COMPRESSIONS 1 253COLUMN_DECOMPRESSIONS 3 254SELECT DATA_LENGTH FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test' AND TABLE_NAME='t1'; 255DATA_LENGTH 2561008 257# Rebuild back 258ALTER TABLE t1 MODIFY COLUMN a TEXT COMPRESSED; 259SHOW CREATE TABLE t1; 260Table Create Table 261t1 CREATE TABLE `t1` ( 262 `a` text /*!100301 COMPRESSED*/ DEFAULT NULL 263) ENGINE=MyISAM DEFAULT CHARSET=latin1 264SELECT LEFT(a, 10), LENGTH(a) FROM t1; 265LEFT(a, 10) LENGTH(a) 266aaaaaaaaaa 1000 267SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME IN('Column_compressions', 'Column_decompressions'); 268VARIABLE_NAME VARIABLE_VALUE 269COLUMN_COMPRESSIONS 2 270COLUMN_DECOMPRESSIONS 5 271SELECT DATA_LENGTH FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test' AND TABLE_NAME='t1'; 272DATA_LENGTH 27328 274# Make sure CREATE TABLE ... LIKE inherits compression 275CREATE TABLE t2 LIKE t1; 276SHOW CREATE TABLE t2; 277Table Create Table 278t2 CREATE TABLE `t2` ( 279 `a` text /*!100301 COMPRESSED*/ DEFAULT NULL 280) ENGINE=MyISAM DEFAULT CHARSET=latin1 281DROP TABLE t2; 282# Make sure implicit CREATE TABLE ... SELECT inherits compression 283CREATE TABLE t2 SELECT * FROM t1; 284SHOW CREATE TABLE t2; 285Table Create Table 286t2 CREATE TABLE `t2` ( 287 `a` text /*!100301 COMPRESSED*/ DEFAULT NULL 288) ENGINE=MyISAM DEFAULT CHARSET=latin1 289SELECT LEFT(a, 10), LENGTH(a) FROM t2; 290LEFT(a, 10) LENGTH(a) 291aaaaaaaaaa 1000 292SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME IN('Column_compressions', 'Column_decompressions'); 293VARIABLE_NAME VARIABLE_VALUE 294COLUMN_COMPRESSIONS 2 295COLUMN_DECOMPRESSIONS 7 296SELECT DATA_LENGTH FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test' AND TABLE_NAME='t2'; 297DATA_LENGTH 29828 299DROP TABLE t2; 300# Make sure explicit CREATE TABLE ... SELECT doesn't inherit compression 301CREATE TABLE t2(a TEXT) SELECT * FROM t1; 302SHOW CREATE TABLE t2; 303Table Create Table 304t2 CREATE TABLE `t2` ( 305 `a` text DEFAULT NULL 306) ENGINE=MyISAM DEFAULT CHARSET=latin1 307SELECT LEFT(a, 10), LENGTH(a) FROM t2; 308LEFT(a, 10) LENGTH(a) 309aaaaaaaaaa 1000 310SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME IN('Column_compressions', 'Column_decompressions'); 311VARIABLE_NAME VARIABLE_VALUE 312COLUMN_COMPRESSIONS 2 313COLUMN_DECOMPRESSIONS 8 314SELECT DATA_LENGTH FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test' AND TABLE_NAME='t2'; 315DATA_LENGTH 3161008 317DROP TABLE t2; 318# Make sure engine change works 319ALTER TABLE t1 ENGINE=InnoDB; 320SHOW CREATE TABLE t1; 321Table Create Table 322t1 CREATE TABLE `t1` ( 323 `a` text /*!100301 COMPRESSED*/ DEFAULT NULL 324) ENGINE=InnoDB DEFAULT CHARSET=latin1 325SELECT LEFT(a, 10), LENGTH(a) FROM t1; 326LEFT(a, 10) LENGTH(a) 327aaaaaaaaaa 1000 328SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME IN('Column_compressions', 'Column_decompressions'); 329VARIABLE_NAME VARIABLE_VALUE 330COLUMN_COMPRESSIONS 2 331COLUMN_DECOMPRESSIONS 10 332# Make sure online add column works (requires InnoDB) 333ALTER TABLE t1 ADD COLUMN b TEXT COMPRESSED DEFAULT "must be visible"; 334SELECT LEFT(a, 10), LENGTH(a), b FROM t1; 335LEFT(a, 10) LENGTH(a) b 336aaaaaaaaaa 1000 must be visible 337SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME IN('Column_compressions', 'Column_decompressions'); 338VARIABLE_NAME VARIABLE_VALUE 339COLUMN_COMPRESSIONS 2 340COLUMN_DECOMPRESSIONS 12 341ALTER TABLE t1 DROP COLUMN b; 342ALTER TABLE t1 ENGINE=MyISAM; 343TRUNCATE TABLE t1; 344# Make sure column_compression_zlib_level works 345SET column_compression_zlib_level= 1; 346INSERT INTO t1 VALUES(REPEAT('ab', 1000)); 347SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME IN('Column_compressions', 'Column_decompressions'); 348VARIABLE_NAME VARIABLE_VALUE 349COLUMN_COMPRESSIONS 3 350COLUMN_DECOMPRESSIONS 12 351SELECT DATA_LENGTH FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test' AND TABLE_NAME='t1'; 352DATA_LENGTH 35340 354TRUNCATE TABLE t1; 355SET column_compression_zlib_level= 9; 356INSERT INTO t1 VALUES(REPEAT('ab', 1000)); 357SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME IN('Column_compressions', 'Column_decompressions'); 358VARIABLE_NAME VARIABLE_VALUE 359COLUMN_COMPRESSIONS 4 360COLUMN_DECOMPRESSIONS 12 361SELECT DATA_LENGTH FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test' AND TABLE_NAME='t1'; 362DATA_LENGTH 36336 364SET column_compression_zlib_level= DEFAULT; 365TRUNCATE TABLE t1; 366# No compression, original data shorter than compressed 367INSERT INTO t1 VALUES('a'); 368SELECT a, LENGTH(a) FROM t1; 369a LENGTH(a) 370a 1 371SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME IN('Column_compressions', 'Column_decompressions'); 372VARIABLE_NAME VARIABLE_VALUE 373COLUMN_COMPRESSIONS 4 374COLUMN_DECOMPRESSIONS 12 375SELECT DATA_LENGTH FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test' AND TABLE_NAME='t1'; 376DATA_LENGTH 37720 378# Coverage for store(double) and store(longlong) 379INSERT INTO t1 VALUES(3.14),(CAST(9.8 AS DOUBLE)),(1),(''),(NULL); 380# and for sort_string() 381SELECT * FROM t1 ORDER BY a; 382a 383NULL 384 3851 3863.14 3879.8 388a 389# Coverage for val_real() and val_int() 390SELECT a+1 FROM t1 ORDER BY 1; 391a+1 392NULL 3931 3941 3952 3964.140000000000001 39710.8 398Warnings: 399Warning 1292 Truncated incorrect DOUBLE value: 'a' 400Warning 1292 Truncated incorrect DOUBLE value: '' 401Warning 1292 Truncated incorrect DOUBLE value: 'a' 402Warning 1292 Truncated incorrect DOUBLE value: '' 403SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME IN('Column_compressions', 'Column_decompressions'); 404VARIABLE_NAME VARIABLE_VALUE 405COLUMN_COMPRESSIONS 4 406COLUMN_DECOMPRESSIONS 12 407DROP TABLE t1; 408# 409# MDEV-13540 - Server crashes in copy or Assertion `0' failed in virtual 410# Field* Field_varstring_compressed::new_key_field 411# 412CREATE TABLE t1 (c1 TEXT COMPRESSED) ENGINE=MyISAM; 413INSERT IGNORE INTO t1 VALUES ('foo'),('bar'); 414CREATE TABLE t2 (c2 TEXT COMPRESSED) ENGINE=MyISAM; 415INSERT IGNORE INTO t2 VALUES ('qux'),('abc'); 416SELECT * FROM t1 WHERE c1 NOT IN ( SELECT c2 FROM t2 WHERE c2 = c1 ); 417c1 418foo 419bar 420DROP TABLE t1, t2; 421# 422# MDEV-13541 - Server crashes in next_breadth_first_tab or Assertion `0' 423# failed in Field_varstring_compressed::new_key_field 424# 425CREATE TABLE t1 (c TEXT COMPRESSED) ENGINE=InnoDB; 426INSERT INTO t1 VALUES ('foo'),('bar'); 427SELECT DISTINCT c FROM t1; 428c 429foo 430bar 431DROP TABLE t1; 432FLUSH STATUS; 433CREATE TABLE t1(a VARBINARY(10000) COMPRESSED, KEY(a(10))); 434ERROR HY000: Compressed column 'a' can't be used in key specification 435CREATE TABLE t1(a VARBINARY(10000) COMPRESSED); 436ALTER TABLE t1 ADD KEY(a(10)); 437ERROR HY000: Compressed column 'a' can't be used in key specification 438SHOW CREATE TABLE t1; 439Table Create Table 440t1 CREATE TABLE `t1` ( 441 `a` varbinary(10000) /*!100301 COMPRESSED*/ DEFAULT NULL 442) ENGINE=MyISAM DEFAULT CHARSET=latin1 443# Make sure column was actually compressed 444INSERT INTO t1 VALUES(REPEAT('a', 1000)); 445SELECT LEFT(a, 10), LENGTH(a) FROM t1; 446LEFT(a, 10) LENGTH(a) 447aaaaaaaaaa 1000 448SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME IN('Column_compressions', 'Column_decompressions'); 449VARIABLE_NAME VARIABLE_VALUE 450COLUMN_COMPRESSIONS 1 451COLUMN_DECOMPRESSIONS 2 452SELECT DATA_LENGTH FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test' AND TABLE_NAME='t1'; 453DATA_LENGTH 45428 455# Make sure ALTER TABLE rebuilds table 456ALTER TABLE t1 MODIFY COLUMN a VARBINARY(10000); 457SHOW CREATE TABLE t1; 458Table Create Table 459t1 CREATE TABLE `t1` ( 460 `a` varbinary(10000) DEFAULT NULL 461) ENGINE=MyISAM DEFAULT CHARSET=latin1 462SELECT LEFT(a, 10), LENGTH(a) FROM t1; 463LEFT(a, 10) LENGTH(a) 464aaaaaaaaaa 1000 465SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME IN('Column_compressions', 'Column_decompressions'); 466VARIABLE_NAME VARIABLE_VALUE 467COLUMN_COMPRESSIONS 1 468COLUMN_DECOMPRESSIONS 3 469SELECT DATA_LENGTH FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test' AND TABLE_NAME='t1'; 470DATA_LENGTH 4711008 472# Rebuild back 473ALTER TABLE t1 MODIFY COLUMN a VARBINARY(10000) COMPRESSED; 474SHOW CREATE TABLE t1; 475Table Create Table 476t1 CREATE TABLE `t1` ( 477 `a` varbinary(10000) /*!100301 COMPRESSED*/ DEFAULT NULL 478) ENGINE=MyISAM DEFAULT CHARSET=latin1 479SELECT LEFT(a, 10), LENGTH(a) FROM t1; 480LEFT(a, 10) LENGTH(a) 481aaaaaaaaaa 1000 482SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME IN('Column_compressions', 'Column_decompressions'); 483VARIABLE_NAME VARIABLE_VALUE 484COLUMN_COMPRESSIONS 2 485COLUMN_DECOMPRESSIONS 5 486SELECT DATA_LENGTH FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test' AND TABLE_NAME='t1'; 487DATA_LENGTH 48828 489# Make sure CREATE TABLE ... LIKE inherits compression 490CREATE TABLE t2 LIKE t1; 491SHOW CREATE TABLE t2; 492Table Create Table 493t2 CREATE TABLE `t2` ( 494 `a` varbinary(10000) /*!100301 COMPRESSED*/ DEFAULT NULL 495) ENGINE=MyISAM DEFAULT CHARSET=latin1 496DROP TABLE t2; 497# Make sure implicit CREATE TABLE ... SELECT inherits compression 498CREATE TABLE t2 SELECT * FROM t1; 499SHOW CREATE TABLE t2; 500Table Create Table 501t2 CREATE TABLE `t2` ( 502 `a` varbinary(10000) /*!100301 COMPRESSED*/ DEFAULT NULL 503) ENGINE=MyISAM DEFAULT CHARSET=latin1 504SELECT LEFT(a, 10), LENGTH(a) FROM t2; 505LEFT(a, 10) LENGTH(a) 506aaaaaaaaaa 1000 507SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME IN('Column_compressions', 'Column_decompressions'); 508VARIABLE_NAME VARIABLE_VALUE 509COLUMN_COMPRESSIONS 2 510COLUMN_DECOMPRESSIONS 7 511SELECT DATA_LENGTH FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test' AND TABLE_NAME='t2'; 512DATA_LENGTH 51328 514DROP TABLE t2; 515# Make sure explicit CREATE TABLE ... SELECT doesn't inherit compression 516CREATE TABLE t2(a VARBINARY(10000)) SELECT * FROM t1; 517SHOW CREATE TABLE t2; 518Table Create Table 519t2 CREATE TABLE `t2` ( 520 `a` varbinary(10000) DEFAULT NULL 521) ENGINE=MyISAM DEFAULT CHARSET=latin1 522SELECT LEFT(a, 10), LENGTH(a) FROM t2; 523LEFT(a, 10) LENGTH(a) 524aaaaaaaaaa 1000 525SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME IN('Column_compressions', 'Column_decompressions'); 526VARIABLE_NAME VARIABLE_VALUE 527COLUMN_COMPRESSIONS 2 528COLUMN_DECOMPRESSIONS 8 529SELECT DATA_LENGTH FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test' AND TABLE_NAME='t2'; 530DATA_LENGTH 5311008 532DROP TABLE t2; 533# Make sure engine change works 534ALTER TABLE t1 ENGINE=InnoDB; 535SHOW CREATE TABLE t1; 536Table Create Table 537t1 CREATE TABLE `t1` ( 538 `a` varbinary(10000) /*!100301 COMPRESSED*/ DEFAULT NULL 539) ENGINE=InnoDB DEFAULT CHARSET=latin1 540SELECT LEFT(a, 10), LENGTH(a) FROM t1; 541LEFT(a, 10) LENGTH(a) 542aaaaaaaaaa 1000 543SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME IN('Column_compressions', 'Column_decompressions'); 544VARIABLE_NAME VARIABLE_VALUE 545COLUMN_COMPRESSIONS 2 546COLUMN_DECOMPRESSIONS 10 547# Make sure online add column works (requires InnoDB) 548ALTER TABLE t1 ADD COLUMN b VARBINARY(10000) COMPRESSED DEFAULT "must be visible"; 549SELECT LEFT(a, 10), LENGTH(a), b FROM t1; 550LEFT(a, 10) LENGTH(a) b 551aaaaaaaaaa 1000 must be visible 552SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME IN('Column_compressions', 'Column_decompressions'); 553VARIABLE_NAME VARIABLE_VALUE 554COLUMN_COMPRESSIONS 2 555COLUMN_DECOMPRESSIONS 12 556ALTER TABLE t1 DROP COLUMN b; 557ALTER TABLE t1 ENGINE=MyISAM; 558TRUNCATE TABLE t1; 559# Make sure column_compression_zlib_level works 560SET column_compression_zlib_level= 1; 561INSERT INTO t1 VALUES(REPEAT('ab', 1000)); 562SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME IN('Column_compressions', 'Column_decompressions'); 563VARIABLE_NAME VARIABLE_VALUE 564COLUMN_COMPRESSIONS 3 565COLUMN_DECOMPRESSIONS 12 566SELECT DATA_LENGTH FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test' AND TABLE_NAME='t1'; 567DATA_LENGTH 56840 569TRUNCATE TABLE t1; 570SET column_compression_zlib_level= 9; 571INSERT INTO t1 VALUES(REPEAT('ab', 1000)); 572SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME IN('Column_compressions', 'Column_decompressions'); 573VARIABLE_NAME VARIABLE_VALUE 574COLUMN_COMPRESSIONS 4 575COLUMN_DECOMPRESSIONS 12 576SELECT DATA_LENGTH FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test' AND TABLE_NAME='t1'; 577DATA_LENGTH 57832 579SET column_compression_zlib_level= DEFAULT; 580TRUNCATE TABLE t1; 581# No compression, original data shorter than compressed 582INSERT INTO t1 VALUES('a'); 583SELECT a, LENGTH(a) FROM t1; 584a LENGTH(a) 585a 1 586SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME IN('Column_compressions', 'Column_decompressions'); 587VARIABLE_NAME VARIABLE_VALUE 588COLUMN_COMPRESSIONS 4 589COLUMN_DECOMPRESSIONS 12 590SELECT DATA_LENGTH FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test' AND TABLE_NAME='t1'; 591DATA_LENGTH 59220 593# Coverage for store(double) and store(longlong) 594INSERT INTO t1 VALUES(3.14),(CAST(9.8 AS DOUBLE)),(1),(''),(NULL); 595# and for sort_string() 596SELECT * FROM t1 ORDER BY a; 597a 598NULL 599 6001 6013.14 6029.8 603a 604# Coverage for val_real() and val_int() 605SELECT a+1 FROM t1 ORDER BY 1; 606a+1 607NULL 6081 6091 6102 6114.140000000000001 61210.8 613Warnings: 614Warning 1292 Truncated incorrect DOUBLE value: 'a' 615Warning 1292 Truncated incorrect DOUBLE value: '' 616Warning 1292 Truncated incorrect DOUBLE value: 'a' 617Warning 1292 Truncated incorrect DOUBLE value: '' 618SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME IN('Column_compressions', 'Column_decompressions'); 619VARIABLE_NAME VARIABLE_VALUE 620COLUMN_COMPRESSIONS 4 621COLUMN_DECOMPRESSIONS 12 622DROP TABLE t1; 623# 624# MDEV-13540 - Server crashes in copy or Assertion `0' failed in virtual 625# Field* Field_varstring_compressed::new_key_field 626# 627CREATE TABLE t1 (c1 VARBINARY(10000) COMPRESSED) ENGINE=MyISAM; 628INSERT IGNORE INTO t1 VALUES ('foo'),('bar'); 629CREATE TABLE t2 (c2 VARBINARY(10000) COMPRESSED) ENGINE=MyISAM; 630INSERT IGNORE INTO t2 VALUES ('qux'),('abc'); 631SELECT * FROM t1 WHERE c1 NOT IN ( SELECT c2 FROM t2 WHERE c2 = c1 ); 632c1 633foo 634bar 635DROP TABLE t1, t2; 636# 637# MDEV-13541 - Server crashes in next_breadth_first_tab or Assertion `0' 638# failed in Field_varstring_compressed::new_key_field 639# 640CREATE TABLE t1 (c VARBINARY(10000) COMPRESSED) ENGINE=InnoDB; 641INSERT INTO t1 VALUES ('foo'),('bar'); 642SELECT DISTINCT c FROM t1; 643c 644foo 645bar 646DROP TABLE t1; 647FLUSH STATUS; 648CREATE TABLE t1(a VARCHAR(10000) COMPRESSED, KEY(a(10))); 649ERROR HY000: Compressed column 'a' can't be used in key specification 650CREATE TABLE t1(a VARCHAR(10000) COMPRESSED); 651ALTER TABLE t1 ADD KEY(a(10)); 652ERROR HY000: Compressed column 'a' can't be used in key specification 653SHOW CREATE TABLE t1; 654Table Create Table 655t1 CREATE TABLE `t1` ( 656 `a` varchar(10000) /*!100301 COMPRESSED*/ DEFAULT NULL 657) ENGINE=MyISAM DEFAULT CHARSET=latin1 658# Make sure column was actually compressed 659INSERT INTO t1 VALUES(REPEAT('a', 1000)); 660SELECT LEFT(a, 10), LENGTH(a) FROM t1; 661LEFT(a, 10) LENGTH(a) 662aaaaaaaaaa 1000 663SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME IN('Column_compressions', 'Column_decompressions'); 664VARIABLE_NAME VARIABLE_VALUE 665COLUMN_COMPRESSIONS 1 666COLUMN_DECOMPRESSIONS 2 667SELECT DATA_LENGTH FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test' AND TABLE_NAME='t1'; 668DATA_LENGTH 66928 670# Make sure ALTER TABLE rebuilds table 671ALTER TABLE t1 MODIFY COLUMN a VARCHAR(10000); 672SHOW CREATE TABLE t1; 673Table Create Table 674t1 CREATE TABLE `t1` ( 675 `a` varchar(10000) DEFAULT NULL 676) ENGINE=MyISAM DEFAULT CHARSET=latin1 677SELECT LEFT(a, 10), LENGTH(a) FROM t1; 678LEFT(a, 10) LENGTH(a) 679aaaaaaaaaa 1000 680SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME IN('Column_compressions', 'Column_decompressions'); 681VARIABLE_NAME VARIABLE_VALUE 682COLUMN_COMPRESSIONS 1 683COLUMN_DECOMPRESSIONS 3 684SELECT DATA_LENGTH FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test' AND TABLE_NAME='t1'; 685DATA_LENGTH 6861008 687# Rebuild back 688ALTER TABLE t1 MODIFY COLUMN a VARCHAR(10000) COMPRESSED; 689SHOW CREATE TABLE t1; 690Table Create Table 691t1 CREATE TABLE `t1` ( 692 `a` varchar(10000) /*!100301 COMPRESSED*/ DEFAULT NULL 693) ENGINE=MyISAM DEFAULT CHARSET=latin1 694SELECT LEFT(a, 10), LENGTH(a) FROM t1; 695LEFT(a, 10) LENGTH(a) 696aaaaaaaaaa 1000 697SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME IN('Column_compressions', 'Column_decompressions'); 698VARIABLE_NAME VARIABLE_VALUE 699COLUMN_COMPRESSIONS 2 700COLUMN_DECOMPRESSIONS 5 701SELECT DATA_LENGTH FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test' AND TABLE_NAME='t1'; 702DATA_LENGTH 70328 704# Make sure CREATE TABLE ... LIKE inherits compression 705CREATE TABLE t2 LIKE t1; 706SHOW CREATE TABLE t2; 707Table Create Table 708t2 CREATE TABLE `t2` ( 709 `a` varchar(10000) /*!100301 COMPRESSED*/ DEFAULT NULL 710) ENGINE=MyISAM DEFAULT CHARSET=latin1 711DROP TABLE t2; 712# Make sure implicit CREATE TABLE ... SELECT inherits compression 713CREATE TABLE t2 SELECT * FROM t1; 714SHOW CREATE TABLE t2; 715Table Create Table 716t2 CREATE TABLE `t2` ( 717 `a` varchar(10000) /*!100301 COMPRESSED*/ DEFAULT NULL 718) ENGINE=MyISAM DEFAULT CHARSET=latin1 719SELECT LEFT(a, 10), LENGTH(a) FROM t2; 720LEFT(a, 10) LENGTH(a) 721aaaaaaaaaa 1000 722SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME IN('Column_compressions', 'Column_decompressions'); 723VARIABLE_NAME VARIABLE_VALUE 724COLUMN_COMPRESSIONS 2 725COLUMN_DECOMPRESSIONS 7 726SELECT DATA_LENGTH FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test' AND TABLE_NAME='t2'; 727DATA_LENGTH 72828 729DROP TABLE t2; 730# Make sure explicit CREATE TABLE ... SELECT doesn't inherit compression 731CREATE TABLE t2(a VARCHAR(10000)) SELECT * FROM t1; 732SHOW CREATE TABLE t2; 733Table Create Table 734t2 CREATE TABLE `t2` ( 735 `a` varchar(10000) DEFAULT NULL 736) ENGINE=MyISAM DEFAULT CHARSET=latin1 737SELECT LEFT(a, 10), LENGTH(a) FROM t2; 738LEFT(a, 10) LENGTH(a) 739aaaaaaaaaa 1000 740SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME IN('Column_compressions', 'Column_decompressions'); 741VARIABLE_NAME VARIABLE_VALUE 742COLUMN_COMPRESSIONS 2 743COLUMN_DECOMPRESSIONS 8 744SELECT DATA_LENGTH FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test' AND TABLE_NAME='t2'; 745DATA_LENGTH 7461008 747DROP TABLE t2; 748# Make sure engine change works 749ALTER TABLE t1 ENGINE=InnoDB; 750SHOW CREATE TABLE t1; 751Table Create Table 752t1 CREATE TABLE `t1` ( 753 `a` varchar(10000) /*!100301 COMPRESSED*/ DEFAULT NULL 754) ENGINE=InnoDB DEFAULT CHARSET=latin1 755SELECT LEFT(a, 10), LENGTH(a) FROM t1; 756LEFT(a, 10) LENGTH(a) 757aaaaaaaaaa 1000 758SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME IN('Column_compressions', 'Column_decompressions'); 759VARIABLE_NAME VARIABLE_VALUE 760COLUMN_COMPRESSIONS 2 761COLUMN_DECOMPRESSIONS 10 762# Make sure online add column works (requires InnoDB) 763ALTER TABLE t1 ADD COLUMN b VARCHAR(10000) COMPRESSED DEFAULT "must be visible"; 764SELECT LEFT(a, 10), LENGTH(a), b FROM t1; 765LEFT(a, 10) LENGTH(a) b 766aaaaaaaaaa 1000 must be visible 767SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME IN('Column_compressions', 'Column_decompressions'); 768VARIABLE_NAME VARIABLE_VALUE 769COLUMN_COMPRESSIONS 2 770COLUMN_DECOMPRESSIONS 12 771ALTER TABLE t1 DROP COLUMN b; 772ALTER TABLE t1 ENGINE=MyISAM; 773TRUNCATE TABLE t1; 774# Make sure column_compression_zlib_level works 775SET column_compression_zlib_level= 1; 776INSERT INTO t1 VALUES(REPEAT('ab', 1000)); 777SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME IN('Column_compressions', 'Column_decompressions'); 778VARIABLE_NAME VARIABLE_VALUE 779COLUMN_COMPRESSIONS 3 780COLUMN_DECOMPRESSIONS 12 781SELECT DATA_LENGTH FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test' AND TABLE_NAME='t1'; 782DATA_LENGTH 78340 784TRUNCATE TABLE t1; 785SET column_compression_zlib_level= 9; 786INSERT INTO t1 VALUES(REPEAT('ab', 1000)); 787SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME IN('Column_compressions', 'Column_decompressions'); 788VARIABLE_NAME VARIABLE_VALUE 789COLUMN_COMPRESSIONS 4 790COLUMN_DECOMPRESSIONS 12 791SELECT DATA_LENGTH FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test' AND TABLE_NAME='t1'; 792DATA_LENGTH 79332 794SET column_compression_zlib_level= DEFAULT; 795TRUNCATE TABLE t1; 796# No compression, original data shorter than compressed 797INSERT INTO t1 VALUES('a'); 798SELECT a, LENGTH(a) FROM t1; 799a LENGTH(a) 800a 1 801SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME IN('Column_compressions', 'Column_decompressions'); 802VARIABLE_NAME VARIABLE_VALUE 803COLUMN_COMPRESSIONS 4 804COLUMN_DECOMPRESSIONS 12 805SELECT DATA_LENGTH FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test' AND TABLE_NAME='t1'; 806DATA_LENGTH 80720 808# Coverage for store(double) and store(longlong) 809INSERT INTO t1 VALUES(3.14),(CAST(9.8 AS DOUBLE)),(1),(''),(NULL); 810# and for sort_string() 811SELECT * FROM t1 ORDER BY a; 812a 813NULL 814 8151 8163.14 8179.8 818a 819# Coverage for val_real() and val_int() 820SELECT a+1 FROM t1 ORDER BY 1; 821a+1 822NULL 8231 8241 8252 8264.140000000000001 82710.8 828Warnings: 829Warning 1292 Truncated incorrect DOUBLE value: 'a' 830Warning 1292 Truncated incorrect DOUBLE value: '' 831Warning 1292 Truncated incorrect DOUBLE value: 'a' 832Warning 1292 Truncated incorrect DOUBLE value: '' 833SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME IN('Column_compressions', 'Column_decompressions'); 834VARIABLE_NAME VARIABLE_VALUE 835COLUMN_COMPRESSIONS 4 836COLUMN_DECOMPRESSIONS 12 837DROP TABLE t1; 838# 839# MDEV-13540 - Server crashes in copy or Assertion `0' failed in virtual 840# Field* Field_varstring_compressed::new_key_field 841# 842CREATE TABLE t1 (c1 VARCHAR(10000) COMPRESSED) ENGINE=MyISAM; 843INSERT IGNORE INTO t1 VALUES ('foo'),('bar'); 844CREATE TABLE t2 (c2 VARCHAR(10000) COMPRESSED) ENGINE=MyISAM; 845INSERT IGNORE INTO t2 VALUES ('qux'),('abc'); 846SELECT * FROM t1 WHERE c1 NOT IN ( SELECT c2 FROM t2 WHERE c2 = c1 ); 847c1 848foo 849bar 850DROP TABLE t1, t2; 851# 852# MDEV-13541 - Server crashes in next_breadth_first_tab or Assertion `0' 853# failed in Field_varstring_compressed::new_key_field 854# 855CREATE TABLE t1 (c VARCHAR(10000) COMPRESSED) ENGINE=InnoDB; 856INSERT INTO t1 VALUES ('foo'),('bar'); 857SELECT DISTINCT c FROM t1; 858c 859foo 860bar 861DROP TABLE t1; 862FLUSH STATUS; 863CREATE TABLE t1(a TEXT COMPRESSED CHARSET ucs2, KEY(a(10))); 864ERROR HY000: Compressed column 'a' can't be used in key specification 865CREATE TABLE t1(a TEXT COMPRESSED CHARSET ucs2); 866ALTER TABLE t1 ADD KEY(a(10)); 867ERROR HY000: Compressed column 'a' can't be used in key specification 868SHOW CREATE TABLE t1; 869Table Create Table 870t1 CREATE TABLE `t1` ( 871 `a` text /*!100301 COMPRESSED*/ CHARACTER SET ucs2 DEFAULT NULL 872) ENGINE=MyISAM DEFAULT CHARSET=latin1 873# Make sure column was actually compressed 874INSERT INTO t1 VALUES(REPEAT('a', 1000)); 875SELECT LEFT(a, 10), LENGTH(a) FROM t1; 876LEFT(a, 10) LENGTH(a) 877aaaaaaaaaa 2000 878SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME IN('Column_compressions', 'Column_decompressions'); 879VARIABLE_NAME VARIABLE_VALUE 880COLUMN_COMPRESSIONS 1 881COLUMN_DECOMPRESSIONS 2 882SELECT DATA_LENGTH FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test' AND TABLE_NAME='t1'; 883DATA_LENGTH 88436 885# Make sure ALTER TABLE rebuilds table 886ALTER TABLE t1 MODIFY COLUMN a TEXT; 887SHOW CREATE TABLE t1; 888Table Create Table 889t1 CREATE TABLE `t1` ( 890 `a` text DEFAULT NULL 891) ENGINE=MyISAM DEFAULT CHARSET=latin1 892SELECT LEFT(a, 10), LENGTH(a) FROM t1; 893LEFT(a, 10) LENGTH(a) 894aaaaaaaaaa 1000 895SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME IN('Column_compressions', 'Column_decompressions'); 896VARIABLE_NAME VARIABLE_VALUE 897COLUMN_COMPRESSIONS 1 898COLUMN_DECOMPRESSIONS 3 899SELECT DATA_LENGTH FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test' AND TABLE_NAME='t1'; 900DATA_LENGTH 9011008 902# Rebuild back 903ALTER TABLE t1 MODIFY COLUMN a TEXT COMPRESSED CHARSET ucs2; 904SHOW CREATE TABLE t1; 905Table Create Table 906t1 CREATE TABLE `t1` ( 907 `a` text /*!100301 COMPRESSED*/ CHARACTER SET ucs2 DEFAULT NULL 908) ENGINE=MyISAM DEFAULT CHARSET=latin1 909SELECT LEFT(a, 10), LENGTH(a) FROM t1; 910LEFT(a, 10) LENGTH(a) 911aaaaaaaaaa 2000 912SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME IN('Column_compressions', 'Column_decompressions'); 913VARIABLE_NAME VARIABLE_VALUE 914COLUMN_COMPRESSIONS 2 915COLUMN_DECOMPRESSIONS 5 916SELECT DATA_LENGTH FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test' AND TABLE_NAME='t1'; 917DATA_LENGTH 91836 919# Make sure CREATE TABLE ... LIKE inherits compression 920CREATE TABLE t2 LIKE t1; 921SHOW CREATE TABLE t2; 922Table Create Table 923t2 CREATE TABLE `t2` ( 924 `a` text /*!100301 COMPRESSED*/ CHARACTER SET ucs2 DEFAULT NULL 925) ENGINE=MyISAM DEFAULT CHARSET=latin1 926DROP TABLE t2; 927# Make sure implicit CREATE TABLE ... SELECT inherits compression 928CREATE TABLE t2 SELECT * FROM t1; 929SHOW CREATE TABLE t2; 930Table Create Table 931t2 CREATE TABLE `t2` ( 932 `a` text /*!100301 COMPRESSED*/ CHARACTER SET ucs2 DEFAULT NULL 933) ENGINE=MyISAM DEFAULT CHARSET=latin1 934SELECT LEFT(a, 10), LENGTH(a) FROM t2; 935LEFT(a, 10) LENGTH(a) 936aaaaaaaaaa 2000 937SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME IN('Column_compressions', 'Column_decompressions'); 938VARIABLE_NAME VARIABLE_VALUE 939COLUMN_COMPRESSIONS 2 940COLUMN_DECOMPRESSIONS 7 941SELECT DATA_LENGTH FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test' AND TABLE_NAME='t2'; 942DATA_LENGTH 94336 944DROP TABLE t2; 945# Make sure explicit CREATE TABLE ... SELECT doesn't inherit compression 946CREATE TABLE t2(a TEXT) SELECT * FROM t1; 947SHOW CREATE TABLE t2; 948Table Create Table 949t2 CREATE TABLE `t2` ( 950 `a` text DEFAULT NULL 951) ENGINE=MyISAM DEFAULT CHARSET=latin1 952SELECT LEFT(a, 10), LENGTH(a) FROM t2; 953LEFT(a, 10) LENGTH(a) 954aaaaaaaaaa 1000 955SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME IN('Column_compressions', 'Column_decompressions'); 956VARIABLE_NAME VARIABLE_VALUE 957COLUMN_COMPRESSIONS 2 958COLUMN_DECOMPRESSIONS 8 959SELECT DATA_LENGTH FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test' AND TABLE_NAME='t2'; 960DATA_LENGTH 9611008 962DROP TABLE t2; 963# Make sure engine change works 964ALTER TABLE t1 ENGINE=InnoDB; 965SHOW CREATE TABLE t1; 966Table Create Table 967t1 CREATE TABLE `t1` ( 968 `a` text /*!100301 COMPRESSED*/ CHARACTER SET ucs2 DEFAULT NULL 969) ENGINE=InnoDB DEFAULT CHARSET=latin1 970SELECT LEFT(a, 10), LENGTH(a) FROM t1; 971LEFT(a, 10) LENGTH(a) 972aaaaaaaaaa 2000 973SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME IN('Column_compressions', 'Column_decompressions'); 974VARIABLE_NAME VARIABLE_VALUE 975COLUMN_COMPRESSIONS 2 976COLUMN_DECOMPRESSIONS 10 977# Make sure online add column works (requires InnoDB) 978ALTER TABLE t1 ADD COLUMN b TEXT COMPRESSED CHARSET ucs2 DEFAULT "must be visible"; 979SELECT LEFT(a, 10), LENGTH(a), b FROM t1; 980LEFT(a, 10) LENGTH(a) b 981aaaaaaaaaa 2000 must be visible 982SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME IN('Column_compressions', 'Column_decompressions'); 983VARIABLE_NAME VARIABLE_VALUE 984COLUMN_COMPRESSIONS 2 985COLUMN_DECOMPRESSIONS 12 986ALTER TABLE t1 DROP COLUMN b; 987ALTER TABLE t1 ENGINE=MyISAM; 988TRUNCATE TABLE t1; 989# Make sure column_compression_zlib_level works 990SET column_compression_zlib_level= 1; 991INSERT INTO t1 VALUES(REPEAT('ab', 1000)); 992SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME IN('Column_compressions', 'Column_decompressions'); 993VARIABLE_NAME VARIABLE_VALUE 994COLUMN_COMPRESSIONS 3 995COLUMN_DECOMPRESSIONS 12 996SELECT DATA_LENGTH FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test' AND TABLE_NAME='t1'; 997DATA_LENGTH 99860 999TRUNCATE TABLE t1; 1000SET column_compression_zlib_level= 9; 1001INSERT INTO t1 VALUES(REPEAT('ab', 1000)); 1002SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME IN('Column_compressions', 'Column_decompressions'); 1003VARIABLE_NAME VARIABLE_VALUE 1004COLUMN_COMPRESSIONS 4 1005COLUMN_DECOMPRESSIONS 12 1006SELECT DATA_LENGTH FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test' AND TABLE_NAME='t1'; 1007DATA_LENGTH 100844 1009SET column_compression_zlib_level= DEFAULT; 1010TRUNCATE TABLE t1; 1011# No compression, original data shorter than compressed 1012INSERT INTO t1 VALUES('a'); 1013SELECT a, LENGTH(a) FROM t1; 1014a LENGTH(a) 1015a 2 1016SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME IN('Column_compressions', 'Column_decompressions'); 1017VARIABLE_NAME VARIABLE_VALUE 1018COLUMN_COMPRESSIONS 4 1019COLUMN_DECOMPRESSIONS 12 1020SELECT DATA_LENGTH FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test' AND TABLE_NAME='t1'; 1021DATA_LENGTH 102220 1023# Coverage for store(double) and store(longlong) 1024INSERT INTO t1 VALUES(3.14),(CAST(9.8 AS DOUBLE)),(1),(''),(NULL); 1025# and for sort_string() 1026SELECT * FROM t1 ORDER BY a; 1027a 1028NULL 1029 10301 10313.14 10329.8 1033a 1034# Coverage for val_real() and val_int() 1035SELECT a+1 FROM t1 ORDER BY 1; 1036a+1 1037NULL 10381 10391 10402 10414.140000000000001 104210.8 1043Warnings: 1044Warning 1292 Truncated incorrect DOUBLE value: 'a' 1045Warning 1292 Truncated incorrect DOUBLE value: '' 1046Warning 1292 Truncated incorrect DOUBLE value: 'a' 1047Warning 1292 Truncated incorrect DOUBLE value: '' 1048SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME IN('Column_compressions', 'Column_decompressions'); 1049VARIABLE_NAME VARIABLE_VALUE 1050COLUMN_COMPRESSIONS 4 1051COLUMN_DECOMPRESSIONS 12 1052DROP TABLE t1; 1053# 1054# MDEV-13540 - Server crashes in copy or Assertion `0' failed in virtual 1055# Field* Field_varstring_compressed::new_key_field 1056# 1057CREATE TABLE t1 (c1 TEXT COMPRESSED CHARSET ucs2) ENGINE=MyISAM; 1058INSERT IGNORE INTO t1 VALUES ('foo'),('bar'); 1059CREATE TABLE t2 (c2 TEXT COMPRESSED CHARSET ucs2) ENGINE=MyISAM; 1060INSERT IGNORE INTO t2 VALUES ('qux'),('abc'); 1061SELECT * FROM t1 WHERE c1 NOT IN ( SELECT c2 FROM t2 WHERE c2 = c1 ); 1062c1 1063foo 1064bar 1065DROP TABLE t1, t2; 1066# 1067# MDEV-13541 - Server crashes in next_breadth_first_tab or Assertion `0' 1068# failed in Field_varstring_compressed::new_key_field 1069# 1070CREATE TABLE t1 (c TEXT COMPRESSED CHARSET ucs2) ENGINE=InnoDB; 1071INSERT INTO t1 VALUES ('foo'),('bar'); 1072SELECT DISTINCT c FROM t1; 1073c 1074foo 1075bar 1076DROP TABLE t1; 1077SET column_compression_zlib_wrap=DEFAULT; 1078FLUSH STATUS; 1079CREATE TABLE t1(a BLOB COMPRESSED, KEY(a(10))); 1080ERROR HY000: Compressed column 'a' can't be used in key specification 1081CREATE TABLE t1(a BLOB COMPRESSED); 1082ALTER TABLE t1 ADD KEY(a(10)); 1083ERROR HY000: Compressed column 'a' can't be used in key specification 1084SHOW CREATE TABLE t1; 1085Table Create Table 1086t1 CREATE TABLE `t1` ( 1087 `a` blob /*!100301 COMPRESSED*/ DEFAULT NULL 1088) ENGINE=MyISAM DEFAULT CHARSET=latin1 1089# Make sure column was actually compressed 1090INSERT INTO t1 VALUES(REPEAT('a', 1000)); 1091SELECT LEFT(a, 10), LENGTH(a) FROM t1; 1092LEFT(a, 10) LENGTH(a) 1093aaaaaaaaaa 1000 1094SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME IN('Column_compressions', 'Column_decompressions'); 1095VARIABLE_NAME VARIABLE_VALUE 1096COLUMN_COMPRESSIONS 1 1097COLUMN_DECOMPRESSIONS 2 1098SELECT DATA_LENGTH FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test' AND TABLE_NAME='t1'; 1099DATA_LENGTH 110024 1101# Make sure ALTER TABLE rebuilds table 1102ALTER TABLE t1 MODIFY COLUMN a BLOB; 1103SHOW CREATE TABLE t1; 1104Table Create Table 1105t1 CREATE TABLE `t1` ( 1106 `a` blob DEFAULT NULL 1107) ENGINE=MyISAM DEFAULT CHARSET=latin1 1108SELECT LEFT(a, 10), LENGTH(a) FROM t1; 1109LEFT(a, 10) LENGTH(a) 1110aaaaaaaaaa 1000 1111SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME IN('Column_compressions', 'Column_decompressions'); 1112VARIABLE_NAME VARIABLE_VALUE 1113COLUMN_COMPRESSIONS 1 1114COLUMN_DECOMPRESSIONS 3 1115SELECT DATA_LENGTH FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test' AND TABLE_NAME='t1'; 1116DATA_LENGTH 11171008 1118# Rebuild back 1119ALTER TABLE t1 MODIFY COLUMN a BLOB COMPRESSED; 1120SHOW CREATE TABLE t1; 1121Table Create Table 1122t1 CREATE TABLE `t1` ( 1123 `a` blob /*!100301 COMPRESSED*/ DEFAULT NULL 1124) ENGINE=MyISAM DEFAULT CHARSET=latin1 1125SELECT LEFT(a, 10), LENGTH(a) FROM t1; 1126LEFT(a, 10) LENGTH(a) 1127aaaaaaaaaa 1000 1128SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME IN('Column_compressions', 'Column_decompressions'); 1129VARIABLE_NAME VARIABLE_VALUE 1130COLUMN_COMPRESSIONS 2 1131COLUMN_DECOMPRESSIONS 5 1132SELECT DATA_LENGTH FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test' AND TABLE_NAME='t1'; 1133DATA_LENGTH 113424 1135# Make sure CREATE TABLE ... LIKE inherits compression 1136CREATE TABLE t2 LIKE t1; 1137SHOW CREATE TABLE t2; 1138Table Create Table 1139t2 CREATE TABLE `t2` ( 1140 `a` blob /*!100301 COMPRESSED*/ DEFAULT NULL 1141) ENGINE=MyISAM DEFAULT CHARSET=latin1 1142DROP TABLE t2; 1143# Make sure implicit CREATE TABLE ... SELECT inherits compression 1144CREATE TABLE t2 SELECT * FROM t1; 1145SHOW CREATE TABLE t2; 1146Table Create Table 1147t2 CREATE TABLE `t2` ( 1148 `a` blob /*!100301 COMPRESSED*/ DEFAULT NULL 1149) ENGINE=MyISAM DEFAULT CHARSET=latin1 1150SELECT LEFT(a, 10), LENGTH(a) FROM t2; 1151LEFT(a, 10) LENGTH(a) 1152aaaaaaaaaa 1000 1153SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME IN('Column_compressions', 'Column_decompressions'); 1154VARIABLE_NAME VARIABLE_VALUE 1155COLUMN_COMPRESSIONS 2 1156COLUMN_DECOMPRESSIONS 7 1157SELECT DATA_LENGTH FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test' AND TABLE_NAME='t2'; 1158DATA_LENGTH 115924 1160DROP TABLE t2; 1161# Make sure explicit CREATE TABLE ... SELECT doesn't inherit compression 1162CREATE TABLE t2(a BLOB) SELECT * FROM t1; 1163SHOW CREATE TABLE t2; 1164Table Create Table 1165t2 CREATE TABLE `t2` ( 1166 `a` blob DEFAULT NULL 1167) ENGINE=MyISAM DEFAULT CHARSET=latin1 1168SELECT LEFT(a, 10), LENGTH(a) FROM t2; 1169LEFT(a, 10) LENGTH(a) 1170aaaaaaaaaa 1000 1171SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME IN('Column_compressions', 'Column_decompressions'); 1172VARIABLE_NAME VARIABLE_VALUE 1173COLUMN_COMPRESSIONS 2 1174COLUMN_DECOMPRESSIONS 8 1175SELECT DATA_LENGTH FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test' AND TABLE_NAME='t2'; 1176DATA_LENGTH 11771008 1178DROP TABLE t2; 1179# Make sure engine change works 1180ALTER TABLE t1 ENGINE=InnoDB; 1181SHOW CREATE TABLE t1; 1182Table Create Table 1183t1 CREATE TABLE `t1` ( 1184 `a` blob /*!100301 COMPRESSED*/ DEFAULT NULL 1185) ENGINE=InnoDB DEFAULT CHARSET=latin1 1186SELECT LEFT(a, 10), LENGTH(a) FROM t1; 1187LEFT(a, 10) LENGTH(a) 1188aaaaaaaaaa 1000 1189SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME IN('Column_compressions', 'Column_decompressions'); 1190VARIABLE_NAME VARIABLE_VALUE 1191COLUMN_COMPRESSIONS 2 1192COLUMN_DECOMPRESSIONS 10 1193# Make sure online add column works (requires InnoDB) 1194ALTER TABLE t1 ADD COLUMN b BLOB COMPRESSED DEFAULT "must be visible"; 1195SELECT LEFT(a, 10), LENGTH(a), b FROM t1; 1196LEFT(a, 10) LENGTH(a) b 1197aaaaaaaaaa 1000 must be visible 1198SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME IN('Column_compressions', 'Column_decompressions'); 1199VARIABLE_NAME VARIABLE_VALUE 1200COLUMN_COMPRESSIONS 2 1201COLUMN_DECOMPRESSIONS 12 1202ALTER TABLE t1 DROP COLUMN b; 1203ALTER TABLE t1 ENGINE=MyISAM; 1204TRUNCATE TABLE t1; 1205# Make sure column_compression_zlib_level works 1206SET column_compression_zlib_level= 1; 1207INSERT INTO t1 VALUES(REPEAT('ab', 1000)); 1208SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME IN('Column_compressions', 'Column_decompressions'); 1209VARIABLE_NAME VARIABLE_VALUE 1210COLUMN_COMPRESSIONS 3 1211COLUMN_DECOMPRESSIONS 12 1212SELECT DATA_LENGTH FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test' AND TABLE_NAME='t1'; 1213DATA_LENGTH 121436 1215TRUNCATE TABLE t1; 1216SET column_compression_zlib_level= 9; 1217INSERT INTO t1 VALUES(REPEAT('ab', 1000)); 1218SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME IN('Column_compressions', 'Column_decompressions'); 1219VARIABLE_NAME VARIABLE_VALUE 1220COLUMN_COMPRESSIONS 4 1221COLUMN_DECOMPRESSIONS 12 1222SELECT DATA_LENGTH FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test' AND TABLE_NAME='t1'; 1223DATA_LENGTH 122428 1225SET column_compression_zlib_level= DEFAULT; 1226TRUNCATE TABLE t1; 1227# No compression, original data shorter than compressed 1228INSERT INTO t1 VALUES('a'); 1229SELECT a, LENGTH(a) FROM t1; 1230a LENGTH(a) 1231a 1 1232SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME IN('Column_compressions', 'Column_decompressions'); 1233VARIABLE_NAME VARIABLE_VALUE 1234COLUMN_COMPRESSIONS 4 1235COLUMN_DECOMPRESSIONS 12 1236SELECT DATA_LENGTH FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test' AND TABLE_NAME='t1'; 1237DATA_LENGTH 123820 1239# Coverage for store(double) and store(longlong) 1240INSERT INTO t1 VALUES(3.14),(CAST(9.8 AS DOUBLE)),(1),(''),(NULL); 1241# and for sort_string() 1242SELECT * FROM t1 ORDER BY a; 1243a 1244NULL 1245 12461 12473.14 12489.8 1249a 1250# Coverage for val_real() and val_int() 1251SELECT a+1 FROM t1 ORDER BY 1; 1252a+1 1253NULL 12541 12551 12562 12574.140000000000001 125810.8 1259Warnings: 1260Warning 1292 Truncated incorrect DOUBLE value: 'a' 1261Warning 1292 Truncated incorrect DOUBLE value: '' 1262Warning 1292 Truncated incorrect DOUBLE value: 'a' 1263Warning 1292 Truncated incorrect DOUBLE value: '' 1264SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME IN('Column_compressions', 'Column_decompressions'); 1265VARIABLE_NAME VARIABLE_VALUE 1266COLUMN_COMPRESSIONS 4 1267COLUMN_DECOMPRESSIONS 12 1268DROP TABLE t1; 1269# 1270# MDEV-13540 - Server crashes in copy or Assertion `0' failed in virtual 1271# Field* Field_varstring_compressed::new_key_field 1272# 1273CREATE TABLE t1 (c1 BLOB COMPRESSED) ENGINE=MyISAM; 1274INSERT IGNORE INTO t1 VALUES ('foo'),('bar'); 1275CREATE TABLE t2 (c2 BLOB COMPRESSED) ENGINE=MyISAM; 1276INSERT IGNORE INTO t2 VALUES ('qux'),('abc'); 1277SELECT * FROM t1 WHERE c1 NOT IN ( SELECT c2 FROM t2 WHERE c2 = c1 ); 1278c1 1279foo 1280bar 1281DROP TABLE t1, t2; 1282# 1283# MDEV-13541 - Server crashes in next_breadth_first_tab or Assertion `0' 1284# failed in Field_varstring_compressed::new_key_field 1285# 1286CREATE TABLE t1 (c BLOB COMPRESSED) ENGINE=InnoDB; 1287INSERT INTO t1 VALUES ('foo'),('bar'); 1288SELECT DISTINCT c FROM t1; 1289c 1290foo 1291bar 1292DROP TABLE t1; 1293CREATE TABLE t1(a CHAR(100) COMPRESSED); 1294ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'COMPRESSED)' at line 1 1295CREATE TABLE t1(a CHAR(100) NOT NULL COMPRESSED); 1296ERROR 42000: Incorrect column specifier for column 'a' 1297CREATE TABLE t1(a INT COMPRESSED); 1298ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'COMPRESSED)' at line 1 1299CREATE TABLE t1(a BLOB COMPRESSED=unknown); 1300ERROR HY000: Unknown compression method: unknown 1301CREATE TABLE t1(a BLOB COMPRESSED COMPRESSED); 1302ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'COMPRESSED)' at line 1 1303CREATE TABLE t1(a INT); 1304ALTER TABLE t1 MODIFY a INT COMPRESSED; 1305ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'COMPRESSED' at line 1 1306ALTER TABLE t1 MODIFY a INT NOT NULL COMPRESSED; 1307ERROR 42000: Incorrect column specifier for column 'a' 1308DROP TABLE t1; 1309# Test CSV 1310CREATE TABLE t1(a BLOB COMPRESSED NOT NULL) ENGINE=CSV; 1311INSERT INTO t1 VALUES(REPEAT('a', 110)); 1312SELECT LENGTH(a) FROM t1; 1313LENGTH(a) 1314110 1315ALTER TABLE t1 ENGINE=MyISAM; 1316SELECT LENGTH(a) FROM t1; 1317LENGTH(a) 1318110 1319ALTER TABLE t1 ENGINE=CSV; 1320SELECT LENGTH(a) FROM t1; 1321LENGTH(a) 1322110 1323SHOW CREATE TABLE t1; 1324Table Create Table 1325t1 CREATE TABLE `t1` ( 1326 `a` blob /*!100301 COMPRESSED*/ NOT NULL DEFAULT '' 1327) ENGINE=CSV DEFAULT CHARSET=latin1 1328"aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa" 1329DROP TABLE t1; 1330# Test fields that don't fit data 1331CREATE TABLE t1(a VARCHAR(9) COMPRESSED); 1332INSERT INTO t1 VALUES(REPEAT('a', 10)); 1333ERROR 22001: Data too long for column 'a' at row 1 1334INSERT INTO t1 VALUES(REPEAT(' ', 10)); 1335Warnings: 1336Note 1265 Data truncated for column 'a' at row 1 1337SELECT a, LENGTH(a) FROM t1; 1338a LENGTH(a) 1339 9 1340DROP TABLE t1; 1341CREATE TABLE t1(a TINYTEXT COMPRESSED); 1342SET column_compression_threshold=300; 1343INSERT INTO t1 VALUES(REPEAT('a', 254)); 1344INSERT INTO t1 VALUES(REPEAT(' ', 254)); 1345INSERT INTO t1 VALUES(REPEAT('a', 255)); 1346ERROR 22001: Data too long for column 'a' at row 1 1347INSERT INTO t1 VALUES(REPEAT(' ', 255)); 1348Warnings: 1349Note 1265 Data truncated for column 'a' at row 1 1350INSERT INTO t1 VALUES(REPEAT('a', 256)); 1351ERROR 22001: Data too long for column 'a' at row 1 1352INSERT INTO t1 VALUES(REPEAT(' ', 256)); 1353Warnings: 1354Note 1265 Data truncated for column 'a' at row 1 1355Note 1265 Data truncated for column 'a' at row 1 1356INSERT INTO t1 VALUES(REPEAT('a', 257)); 1357ERROR 22001: Data too long for column 'a' at row 1 1358INSERT INTO t1 VALUES(REPEAT(' ', 257)); 1359Warnings: 1360Note 1265 Data truncated for column 'a' at row 1 1361Note 1265 Data truncated for column 'a' at row 1 1362SET column_compression_threshold=DEFAULT; 1363SELECT LEFT(a, 10), LENGTH(a) FROM t1 ORDER BY 1; 1364LEFT(a, 10) LENGTH(a) 1365 254 1366 254 1367 254 1368 254 1369aaaaaaaaaa 254 1370DROP TABLE t1; 1371# Corner case: VARCHAR(255) COMPRESSED must have 2 bytes pack length 1372CREATE TABLE t1(a VARCHAR(255) COMPRESSED); 1373SHOW CREATE TABLE t1; 1374Table Create Table 1375t1 CREATE TABLE `t1` ( 1376 `a` varchar(255) /*!100301 COMPRESSED*/ DEFAULT NULL 1377) ENGINE=MyISAM DEFAULT CHARSET=latin1 1378SET column_compression_threshold=300; 1379INSERT INTO t1 VALUES(REPEAT('a', 255)); 1380SET column_compression_threshold=DEFAULT; 1381SELECT a, LENGTH(a) FROM t1; 1382a LENGTH(a) 1383aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa 255 1384DROP TABLE t1; 1385CREATE TABLE t1(a VARCHAR(65531) COMPRESSED); 1386SET column_compression_threshold=65537; 1387INSERT INTO t1 VALUES(REPEAT('a', 65530)); 1388INSERT INTO t1 VALUES(REPEAT(' ', 65530)); 1389INSERT INTO t1 VALUES(REPEAT('a', 65531)); 1390INSERT INTO t1 VALUES(REPEAT(' ', 65531)); 1391INSERT INTO t1 VALUES(REPEAT('a', 65532)); 1392ERROR 22001: Data too long for column 'a' at row 1 1393INSERT INTO t1 VALUES(REPEAT(' ', 65532)); 1394Warnings: 1395Note 1265 Data truncated for column 'a' at row 1 1396INSERT INTO t1 VALUES(REPEAT('a', 65533)); 1397ERROR 22001: Data too long for column 'a' at row 1 1398INSERT INTO t1 VALUES(REPEAT(' ', 65533)); 1399Warnings: 1400Note 1265 Data truncated for column 'a' at row 1 1401SET column_compression_threshold=DEFAULT; 1402SELECT LEFT(a, 10), LENGTH(a) FROM t1 ORDER BY 1, 2; 1403LEFT(a, 10) LENGTH(a) 1404 65530 1405 65531 1406 65531 1407 65531 1408aaaaaaaaaa 65530 1409aaaaaaaaaa 65531 1410DROP TABLE t1; 1411# 1412# MDEV-14929 - AddressSanitizer: memcpy-param-overlap in 1413# Field_longstr::compress 1414# 1415CREATE TABLE t1(b BLOB COMPRESSED); 1416INSERT INTO t1 VALUES('foo'),('bar'); 1417SET SESSION optimizer_switch = 'derived_merge=off'; 1418SELECT * FROM ( SELECT * FROM t1 ) AS sq ORDER BY b; 1419b 1420bar 1421foo 1422SET SESSION optimizer_switch=DEFAULT; 1423DROP TABLE t1; 1424# 1425# MDEV-15762 - VARCHAR(0) COMPRESSED crashes the server 1426# 1427CREATE TABLE t1(a VARCHAR(0) COMPRESSED); 1428INSERT INTO t1 VALUES('a'); 1429ERROR 22001: Data too long for column 'a' at row 1 1430INSERT INTO t1 VALUES(' '); 1431Warnings: 1432Note 1265 Data truncated for column 'a' at row 1 1433SELECT LENGTH(a) FROM t1; 1434LENGTH(a) 14350 1436DROP TABLE t1; 1437# 1438# MDEV-15763 - VARCHAR(1) COMPRESSED crashes the server 1439# 1440CREATE TABLE t1(a VARCHAR(1) COMPRESSED); 1441SET column_compression_threshold=0; 1442INSERT INTO t1 VALUES('a'); 1443SET column_compression_threshold=DEFAULT; 1444DROP TABLE t1; 1445# 1446# MDEV-15938 - TINYTEXT CHARACTER SET utf8 COMPRESSED truncates data 1447# 1448CREATE TABLE t1(a TINYTEXT COMPRESSED, b TINYTEXT) CHARACTER SET utf8; 1449INSERT INTO t1 VALUES (REPEAT(_latin1'a', 254), REPEAT(_latin1'a', 254)); 1450SELECT CHAR_LENGTH(a), CHAR_LENGTH(b), LEFT(a, 10), LEFT(b, 10) FROM t1; 1451CHAR_LENGTH(a) CHAR_LENGTH(b) LEFT(a, 10) LEFT(b, 10) 1452254 254 aaaaaaaaaa aaaaaaaaaa 1453DROP TABLE t1; 1454# 1455# MDEV-16134 Wrong I_S.COLUMNS.CHARACTER_XXX_LENGTH value for compressed columns 1456# 1457CREATE TABLE t1 1458( 1459a VARCHAR(10) COMPRESSED CHARACTER SET latin1, 1460b VARCHAR(10) COMPRESSED CHARACTER SET utf8 1461); 1462SELECT COLUMN_NAME, CHARACTER_MAXIMUM_LENGTH, CHARACTER_OCTET_LENGTH 1463FROM INFORMATION_SCHEMA.COLUMNS 1464WHERE TABLE_SCHEMA='test' AND TABLE_NAME='t1' AND COLUMN_NAME IN ('a','b') 1465ORDER BY COLUMN_NAME; 1466COLUMN_NAME CHARACTER_MAXIMUM_LENGTH CHARACTER_OCTET_LENGTH 1467a 10 10 1468b 10 30 1469DROP TABLE t1; 1470# 1471# MDEV-15592 - Column COMPRESSED should select a 'high order' datatype 1472# 1473CREATE TABLE t1(a TINYTEXT COMPRESSED); 1474INSERT INTO t1 VALUES(REPEAT('a', 255)); 1475SELECT LEFT(a, 10), LENGTH(a) FROM t1; 1476LEFT(a, 10) LENGTH(a) 1477aaaaaaaaaa 255 1478DROP TABLE t1; 1479# 1480# MDEV-16729 VARCHAR COMPRESSED is created with a wrong length for multi-byte character sets 1481# 1482CREATE OR REPLACE TABLE t1 (a VARCHAR(1000) COMPRESSED CHARACTER SET utf8); 1483SHOW CREATE TABLE t1; 1484Table Create Table 1485t1 CREATE TABLE `t1` ( 1486 `a` varchar(1000) /*!100301 COMPRESSED*/ CHARACTER SET utf8 DEFAULT NULL 1487) ENGINE=MyISAM DEFAULT CHARSET=latin1 1488SELECT COLUMN_TYPE FROM INFORMATION_SCHEMA.COLUMNS 1489WHERE TABLE_SCHEMA='test' AND TABLE_NAME='t1'; 1490COLUMN_TYPE 1491varchar(1000) /*!100301 COMPRESSED*/ 1492DROP TABLE t1; 1493# 1494# MDEV-17363 - Compressed columns cannot be restored from dump 1495# 1496CREATE TABLE t1(a INT NOT NULL COMPRESSED); 1497ERROR 42000: Incorrect column specifier for column 'a' 1498SHOW WARNINGS; 1499Level Code Message 1500Error 1063 Incorrect column specifier for column 'a' 1501CREATE TABLE t1( 1502a JSON COMPRESSED, 1503b VARCHAR(1000) COMPRESSED BINARY, 1504c NVARCHAR(1000) COMPRESSED BINARY, 1505d TINYTEXT COMPRESSED BINARY 1506); 1507SHOW CREATE TABLE t1; 1508Table Create Table 1509t1 CREATE TABLE `t1` ( 1510 `a` longtext /*!100301 COMPRESSED*/ CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`a`)), 1511 `b` varchar(1000) /*!100301 COMPRESSED*/ CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, 1512 `c` varchar(1000) /*!100301 COMPRESSED*/ CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL, 1513 `d` tinytext /*!100301 COMPRESSED*/ CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL 1514) ENGINE=MyISAM DEFAULT CHARSET=latin1 1515DROP TABLE t1; 1516# 1517# VARCHAR and TEXT variants 1518# 1519# 1520# The following statements run without warnings. 1521# The `compressed opt_binary` grammar sequence is covered. 1522# 1523CREATE TABLE t1 (a VARCHAR(10) COMPRESSED); 1524SHOW CREATE TABLE t1; 1525Table Create Table 1526t1 CREATE TABLE `t1` ( 1527 `a` varchar(10) /*!100301 COMPRESSED*/ DEFAULT NULL 1528) ENGINE=MyISAM DEFAULT CHARSET=latin1 1529DROP TABLE t1; 1530CREATE TABLE t1 (a VARCHAR(10) COMPRESSED BINARY); 1531SHOW CREATE TABLE t1; 1532Table Create Table 1533t1 CREATE TABLE `t1` ( 1534 `a` varchar(10) /*!100301 COMPRESSED*/ CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL 1535) ENGINE=MyISAM DEFAULT CHARSET=latin1 1536DROP TABLE t1; 1537CREATE TABLE t1 (a VARCHAR(10) COMPRESSED BINARY ASCII); 1538SHOW CREATE TABLE t1; 1539Table Create Table 1540t1 CREATE TABLE `t1` ( 1541 `a` varchar(10) /*!100301 COMPRESSED*/ CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL 1542) ENGINE=MyISAM DEFAULT CHARSET=latin1 1543DROP TABLE t1; 1544CREATE TABLE t1 (a VARCHAR(10) COMPRESSED BYTE); 1545SHOW CREATE TABLE t1; 1546Table Create Table 1547t1 CREATE TABLE `t1` ( 1548 `a` varbinary(10) /*!100301 COMPRESSED*/ DEFAULT NULL 1549) ENGINE=MyISAM DEFAULT CHARSET=latin1 1550DROP TABLE t1; 1551CREATE TABLE t1 (a VARCHAR(10) COMPRESSED ASCII); 1552SHOW CREATE TABLE t1; 1553Table Create Table 1554t1 CREATE TABLE `t1` ( 1555 `a` varchar(10) /*!100301 COMPRESSED*/ DEFAULT NULL 1556) ENGINE=MyISAM DEFAULT CHARSET=latin1 1557DROP TABLE t1; 1558CREATE TABLE t1 (a VARCHAR(10) COMPRESSED UNICODE); 1559SHOW CREATE TABLE t1; 1560Table Create Table 1561t1 CREATE TABLE `t1` ( 1562 `a` varchar(10) /*!100301 COMPRESSED*/ CHARACTER SET ucs2 DEFAULT NULL 1563) ENGINE=MyISAM DEFAULT CHARSET=latin1 1564DROP TABLE t1; 1565CREATE TABLE t1 (a VARCHAR(10) COMPRESSED CHARACTER SET utf8); 1566SHOW CREATE TABLE t1; 1567Table Create Table 1568t1 CREATE TABLE `t1` ( 1569 `a` varchar(10) /*!100301 COMPRESSED*/ CHARACTER SET utf8 DEFAULT NULL 1570) ENGINE=MyISAM DEFAULT CHARSET=latin1 1571DROP TABLE t1; 1572# 1573# The following statements run without warnings. 1574# They have extra column attributes (or GENERATED) after COMPRESSED. 1575# 1576CREATE TABLE t1 (a VARCHAR(10) COMPRESSED BYTE DEFAULT ''); 1577SHOW CREATE TABLE t1; 1578Table Create Table 1579t1 CREATE TABLE `t1` ( 1580 `a` varbinary(10) /*!100301 COMPRESSED*/ DEFAULT '' 1581) ENGINE=MyISAM DEFAULT CHARSET=latin1 1582DROP TABLE t1; 1583CREATE TABLE t1 (a VARCHAR(10) COMPRESSED BINARY DEFAULT ''); 1584SHOW CREATE TABLE t1; 1585Table Create Table 1586t1 CREATE TABLE `t1` ( 1587 `a` varchar(10) /*!100301 COMPRESSED*/ CHARACTER SET latin1 COLLATE latin1_bin DEFAULT '' 1588) ENGINE=MyISAM DEFAULT CHARSET=latin1 1589DROP TABLE t1; 1590CREATE TABLE t1 (a VARCHAR(10) COMPRESSED ASCII DEFAULT ''); 1591SHOW CREATE TABLE t1; 1592Table Create Table 1593t1 CREATE TABLE `t1` ( 1594 `a` varchar(10) /*!100301 COMPRESSED*/ DEFAULT '' 1595) ENGINE=MyISAM DEFAULT CHARSET=latin1 1596DROP TABLE t1; 1597CREATE TABLE t1 (a VARCHAR(10) COMPRESSED CHARACTER SET utf8 DEFAULT ''); 1598SHOW CREATE TABLE t1; 1599Table Create Table 1600t1 CREATE TABLE `t1` ( 1601 `a` varchar(10) /*!100301 COMPRESSED*/ CHARACTER SET utf8 DEFAULT '' 1602) ENGINE=MyISAM DEFAULT CHARSET=latin1 1603DROP TABLE t1; 1604CREATE TABLE t1 (a VARCHAR(10) COMPRESSED CHARACTER SET utf8 GENERATED ALWAYS AS (REPEAT('a',100))); 1605SHOW CREATE TABLE t1; 1606Table Create Table 1607t1 CREATE TABLE `t1` ( 1608 `a` varchar(10) /*!100301 COMPRESSED*/ CHARACTER SET utf8 GENERATED ALWAYS AS (repeat('a',100)) VIRTUAL 1609) ENGINE=MyISAM DEFAULT CHARSET=latin1 1610DROP TABLE t1; 1611# 1612# The following statements return deprecated syntax warnings 1613# 1614CREATE TABLE t1 (a VARCHAR(10) BINARY COMPRESSED); 1615Warnings: 1616Warning 1287 '<data type> <character set clause> ... COMPRESSED...' is deprecated and will be removed in a future release. Please use '<data type> COMPRESSED... <character set clause> ...' instead 1617SHOW CREATE TABLE t1; 1618Table Create Table 1619t1 CREATE TABLE `t1` ( 1620 `a` varchar(10) /*!100301 COMPRESSED*/ CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL 1621) ENGINE=MyISAM DEFAULT CHARSET=latin1 1622DROP TABLE t1; 1623CREATE TABLE t1 (a VARCHAR(10) ASCII COMPRESSED); 1624Warnings: 1625Warning 1287 '<data type> <character set clause> ... COMPRESSED...' is deprecated and will be removed in a future release. Please use '<data type> COMPRESSED... <character set clause> ...' instead 1626SHOW CREATE TABLE t1; 1627Table Create Table 1628t1 CREATE TABLE `t1` ( 1629 `a` varchar(10) /*!100301 COMPRESSED*/ DEFAULT NULL 1630) ENGINE=MyISAM DEFAULT CHARSET=latin1 1631DROP TABLE t1; 1632CREATE TABLE t1 (a VARCHAR(10) BYTE COMPRESSED); 1633Warnings: 1634Warning 1287 '<data type> <character set clause> ... COMPRESSED...' is deprecated and will be removed in a future release. Please use '<data type> COMPRESSED... <character set clause> ...' instead 1635SHOW CREATE TABLE t1; 1636Table Create Table 1637t1 CREATE TABLE `t1` ( 1638 `a` varbinary(10) /*!100301 COMPRESSED*/ DEFAULT NULL 1639) ENGINE=MyISAM DEFAULT CHARSET=latin1 1640DROP TABLE t1; 1641# 1642# The following statements fail by the grammar, 1643# because COMPRESSED immediately follows 'field_type'. 1644# 1645CREATE TABLE t1 (a VARCHAR(10) COMPRESSED BYTE COMPRESSED); 1646ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'COMPRESSED)' at line 1 1647CREATE TABLE t1 (a VARCHAR(10) COMPRESSED BINARY COMPRESSED); 1648ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'COMPRESSED)' at line 1 1649CREATE TABLE t1 (a VARCHAR(10) COMPRESSED ASCII COMPRESSED); 1650ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'COMPRESSED)' at line 1 1651CREATE TABLE t1 (a VARCHAR(10) COMPRESSED CHARACTER SET utf8 COMPRESSED); 1652ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'COMPRESSED)' at line 1 1653# 1654# The following statements are not prohibited by the *.yy grammar, 1655# because the sequence `field_type attribute COMPRESSED` is allowed 1656# (notice there is at least one attribute after `field_type`). 1657# The first COMPRESSED is parsed inside `field_type`. 1658# The second COMPRESSED passes through the parser but then is caught 1659# inside Column_definition::set_compressed_deprecated_with_type_check() 1660# and a syntax error is raised. 1661# 1662CREATE TABLE t1 (a VARCHAR(10) COMPRESSED BYTE DEFAULT '' COMPRESSED); 1663ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'COMPRESSED)' at line 1 1664CREATE TABLE t1 (a VARCHAR(10) COMPRESSED BINARY DEFAULT '' COMPRESSED); 1665ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'COMPRESSED)' at line 1 1666CREATE TABLE t1 (a VARCHAR(10) COMPRESSED ASCII DEFAULT '' COMPRESSED); 1667ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'COMPRESSED)' at line 1 1668CREATE TABLE t1 (a VARCHAR(10) COMPRESSED CHARACTER SET utf8 DEFAULT '' COMPRESSED); 1669ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'COMPRESSED)' at line 1 1670# 1671# The following statements run without warnings. 1672# The `compressed opt_binary` grammar sequence is covered. 1673# 1674CREATE TABLE t1 (a TINYTEXT COMPRESSED); 1675SHOW CREATE TABLE t1; 1676Table Create Table 1677t1 CREATE TABLE `t1` ( 1678 `a` tinytext /*!100301 COMPRESSED*/ DEFAULT NULL 1679) ENGINE=MyISAM DEFAULT CHARSET=latin1 1680DROP TABLE t1; 1681CREATE TABLE t1 (a TINYTEXT COMPRESSED BINARY); 1682SHOW CREATE TABLE t1; 1683Table Create Table 1684t1 CREATE TABLE `t1` ( 1685 `a` tinytext /*!100301 COMPRESSED*/ CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL 1686) ENGINE=MyISAM DEFAULT CHARSET=latin1 1687DROP TABLE t1; 1688CREATE TABLE t1 (a TINYTEXT COMPRESSED BINARY ASCII); 1689SHOW CREATE TABLE t1; 1690Table Create Table 1691t1 CREATE TABLE `t1` ( 1692 `a` tinytext /*!100301 COMPRESSED*/ CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL 1693) ENGINE=MyISAM DEFAULT CHARSET=latin1 1694DROP TABLE t1; 1695CREATE TABLE t1 (a TINYTEXT COMPRESSED BYTE); 1696SHOW CREATE TABLE t1; 1697Table Create Table 1698t1 CREATE TABLE `t1` ( 1699 `a` tinyblob /*!100301 COMPRESSED*/ DEFAULT NULL 1700) ENGINE=MyISAM DEFAULT CHARSET=latin1 1701DROP TABLE t1; 1702CREATE TABLE t1 (a TINYTEXT COMPRESSED ASCII); 1703SHOW CREATE TABLE t1; 1704Table Create Table 1705t1 CREATE TABLE `t1` ( 1706 `a` tinytext /*!100301 COMPRESSED*/ DEFAULT NULL 1707) ENGINE=MyISAM DEFAULT CHARSET=latin1 1708DROP TABLE t1; 1709CREATE TABLE t1 (a TINYTEXT COMPRESSED UNICODE); 1710SHOW CREATE TABLE t1; 1711Table Create Table 1712t1 CREATE TABLE `t1` ( 1713 `a` tinytext /*!100301 COMPRESSED*/ CHARACTER SET ucs2 DEFAULT NULL 1714) ENGINE=MyISAM DEFAULT CHARSET=latin1 1715DROP TABLE t1; 1716CREATE TABLE t1 (a TINYTEXT COMPRESSED CHARACTER SET utf8); 1717SHOW CREATE TABLE t1; 1718Table Create Table 1719t1 CREATE TABLE `t1` ( 1720 `a` tinytext /*!100301 COMPRESSED*/ CHARACTER SET utf8 DEFAULT NULL 1721) ENGINE=MyISAM DEFAULT CHARSET=latin1 1722DROP TABLE t1; 1723# 1724# The following statements run without warnings. 1725# They have extra column attributes (or GENERATED) after COMPRESSED. 1726# 1727CREATE TABLE t1 (a TINYTEXT COMPRESSED BYTE DEFAULT ''); 1728SHOW CREATE TABLE t1; 1729Table Create Table 1730t1 CREATE TABLE `t1` ( 1731 `a` tinyblob /*!100301 COMPRESSED*/ DEFAULT '' 1732) ENGINE=MyISAM DEFAULT CHARSET=latin1 1733DROP TABLE t1; 1734CREATE TABLE t1 (a TINYTEXT COMPRESSED BINARY DEFAULT ''); 1735SHOW CREATE TABLE t1; 1736Table Create Table 1737t1 CREATE TABLE `t1` ( 1738 `a` tinytext /*!100301 COMPRESSED*/ CHARACTER SET latin1 COLLATE latin1_bin DEFAULT '' 1739) ENGINE=MyISAM DEFAULT CHARSET=latin1 1740DROP TABLE t1; 1741CREATE TABLE t1 (a TINYTEXT COMPRESSED ASCII DEFAULT ''); 1742SHOW CREATE TABLE t1; 1743Table Create Table 1744t1 CREATE TABLE `t1` ( 1745 `a` tinytext /*!100301 COMPRESSED*/ DEFAULT '' 1746) ENGINE=MyISAM DEFAULT CHARSET=latin1 1747DROP TABLE t1; 1748CREATE TABLE t1 (a TINYTEXT COMPRESSED CHARACTER SET utf8 DEFAULT ''); 1749SHOW CREATE TABLE t1; 1750Table Create Table 1751t1 CREATE TABLE `t1` ( 1752 `a` tinytext /*!100301 COMPRESSED*/ CHARACTER SET utf8 DEFAULT '' 1753) ENGINE=MyISAM DEFAULT CHARSET=latin1 1754DROP TABLE t1; 1755CREATE TABLE t1 (a TINYTEXT COMPRESSED CHARACTER SET utf8 GENERATED ALWAYS AS (REPEAT('a',100))); 1756SHOW CREATE TABLE t1; 1757Table Create Table 1758t1 CREATE TABLE `t1` ( 1759 `a` tinytext /*!100301 COMPRESSED*/ CHARACTER SET utf8 GENERATED ALWAYS AS (repeat('a',100)) VIRTUAL 1760) ENGINE=MyISAM DEFAULT CHARSET=latin1 1761DROP TABLE t1; 1762# 1763# The following statements return deprecated syntax warnings 1764# 1765CREATE TABLE t1 (a TINYTEXT BINARY COMPRESSED); 1766Warnings: 1767Warning 1287 '<data type> <character set clause> ... COMPRESSED...' is deprecated and will be removed in a future release. Please use '<data type> COMPRESSED... <character set clause> ...' instead 1768SHOW CREATE TABLE t1; 1769Table Create Table 1770t1 CREATE TABLE `t1` ( 1771 `a` tinytext /*!100301 COMPRESSED*/ CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL 1772) ENGINE=MyISAM DEFAULT CHARSET=latin1 1773DROP TABLE t1; 1774CREATE TABLE t1 (a TINYTEXT ASCII COMPRESSED); 1775Warnings: 1776Warning 1287 '<data type> <character set clause> ... COMPRESSED...' is deprecated and will be removed in a future release. Please use '<data type> COMPRESSED... <character set clause> ...' instead 1777SHOW CREATE TABLE t1; 1778Table Create Table 1779t1 CREATE TABLE `t1` ( 1780 `a` tinytext /*!100301 COMPRESSED*/ DEFAULT NULL 1781) ENGINE=MyISAM DEFAULT CHARSET=latin1 1782DROP TABLE t1; 1783CREATE TABLE t1 (a TINYTEXT BYTE COMPRESSED); 1784Warnings: 1785Warning 1287 '<data type> <character set clause> ... COMPRESSED...' is deprecated and will be removed in a future release. Please use '<data type> COMPRESSED... <character set clause> ...' instead 1786SHOW CREATE TABLE t1; 1787Table Create Table 1788t1 CREATE TABLE `t1` ( 1789 `a` tinyblob /*!100301 COMPRESSED*/ DEFAULT NULL 1790) ENGINE=MyISAM DEFAULT CHARSET=latin1 1791DROP TABLE t1; 1792# 1793# The following statements fail by the grammar, 1794# because COMPRESSED immediately follows 'field_type'. 1795# 1796CREATE TABLE t1 (a TINYTEXT COMPRESSED BYTE COMPRESSED); 1797ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'COMPRESSED)' at line 1 1798CREATE TABLE t1 (a TINYTEXT COMPRESSED BINARY COMPRESSED); 1799ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'COMPRESSED)' at line 1 1800CREATE TABLE t1 (a TINYTEXT COMPRESSED ASCII COMPRESSED); 1801ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'COMPRESSED)' at line 1 1802CREATE TABLE t1 (a TINYTEXT COMPRESSED CHARACTER SET utf8 COMPRESSED); 1803ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'COMPRESSED)' at line 1 1804# 1805# The following statements are not prohibited by the *.yy grammar, 1806# because the sequence `field_type attribute COMPRESSED` is allowed 1807# (notice there is at least one attribute after `field_type`). 1808# The first COMPRESSED is parsed inside `field_type`. 1809# The second COMPRESSED passes through the parser but then is caught 1810# inside Column_definition::set_compressed_deprecated_with_type_check() 1811# and a syntax error is raised. 1812# 1813CREATE TABLE t1 (a TINYTEXT COMPRESSED BYTE DEFAULT '' COMPRESSED); 1814ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'COMPRESSED)' at line 1 1815CREATE TABLE t1 (a TINYTEXT COMPRESSED BINARY DEFAULT '' COMPRESSED); 1816ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'COMPRESSED)' at line 1 1817CREATE TABLE t1 (a TINYTEXT COMPRESSED ASCII DEFAULT '' COMPRESSED); 1818ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'COMPRESSED)' at line 1 1819CREATE TABLE t1 (a TINYTEXT COMPRESSED CHARACTER SET utf8 DEFAULT '' COMPRESSED); 1820ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'COMPRESSED)' at line 1 1821# 1822# The following statements run without warnings. 1823# The `compressed opt_binary` grammar sequence is covered. 1824# 1825CREATE TABLE t1 (a TEXT COMPRESSED); 1826SHOW CREATE TABLE t1; 1827Table Create Table 1828t1 CREATE TABLE `t1` ( 1829 `a` text /*!100301 COMPRESSED*/ DEFAULT NULL 1830) ENGINE=MyISAM DEFAULT CHARSET=latin1 1831DROP TABLE t1; 1832CREATE TABLE t1 (a TEXT COMPRESSED BINARY); 1833SHOW CREATE TABLE t1; 1834Table Create Table 1835t1 CREATE TABLE `t1` ( 1836 `a` text /*!100301 COMPRESSED*/ CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL 1837) ENGINE=MyISAM DEFAULT CHARSET=latin1 1838DROP TABLE t1; 1839CREATE TABLE t1 (a TEXT COMPRESSED BINARY ASCII); 1840SHOW CREATE TABLE t1; 1841Table Create Table 1842t1 CREATE TABLE `t1` ( 1843 `a` text /*!100301 COMPRESSED*/ CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL 1844) ENGINE=MyISAM DEFAULT CHARSET=latin1 1845DROP TABLE t1; 1846CREATE TABLE t1 (a TEXT COMPRESSED BYTE); 1847SHOW CREATE TABLE t1; 1848Table Create Table 1849t1 CREATE TABLE `t1` ( 1850 `a` blob /*!100301 COMPRESSED*/ DEFAULT NULL 1851) ENGINE=MyISAM DEFAULT CHARSET=latin1 1852DROP TABLE t1; 1853CREATE TABLE t1 (a TEXT COMPRESSED ASCII); 1854SHOW CREATE TABLE t1; 1855Table Create Table 1856t1 CREATE TABLE `t1` ( 1857 `a` text /*!100301 COMPRESSED*/ DEFAULT NULL 1858) ENGINE=MyISAM DEFAULT CHARSET=latin1 1859DROP TABLE t1; 1860CREATE TABLE t1 (a TEXT COMPRESSED UNICODE); 1861SHOW CREATE TABLE t1; 1862Table Create Table 1863t1 CREATE TABLE `t1` ( 1864 `a` text /*!100301 COMPRESSED*/ CHARACTER SET ucs2 DEFAULT NULL 1865) ENGINE=MyISAM DEFAULT CHARSET=latin1 1866DROP TABLE t1; 1867CREATE TABLE t1 (a TEXT COMPRESSED CHARACTER SET utf8); 1868SHOW CREATE TABLE t1; 1869Table Create Table 1870t1 CREATE TABLE `t1` ( 1871 `a` text /*!100301 COMPRESSED*/ CHARACTER SET utf8 DEFAULT NULL 1872) ENGINE=MyISAM DEFAULT CHARSET=latin1 1873DROP TABLE t1; 1874# 1875# The following statements run without warnings. 1876# They have extra column attributes (or GENERATED) after COMPRESSED. 1877# 1878CREATE TABLE t1 (a TEXT COMPRESSED BYTE DEFAULT ''); 1879SHOW CREATE TABLE t1; 1880Table Create Table 1881t1 CREATE TABLE `t1` ( 1882 `a` blob /*!100301 COMPRESSED*/ DEFAULT '' 1883) ENGINE=MyISAM DEFAULT CHARSET=latin1 1884DROP TABLE t1; 1885CREATE TABLE t1 (a TEXT COMPRESSED BINARY DEFAULT ''); 1886SHOW CREATE TABLE t1; 1887Table Create Table 1888t1 CREATE TABLE `t1` ( 1889 `a` text /*!100301 COMPRESSED*/ CHARACTER SET latin1 COLLATE latin1_bin DEFAULT '' 1890) ENGINE=MyISAM DEFAULT CHARSET=latin1 1891DROP TABLE t1; 1892CREATE TABLE t1 (a TEXT COMPRESSED ASCII DEFAULT ''); 1893SHOW CREATE TABLE t1; 1894Table Create Table 1895t1 CREATE TABLE `t1` ( 1896 `a` text /*!100301 COMPRESSED*/ DEFAULT '' 1897) ENGINE=MyISAM DEFAULT CHARSET=latin1 1898DROP TABLE t1; 1899CREATE TABLE t1 (a TEXT COMPRESSED CHARACTER SET utf8 DEFAULT ''); 1900SHOW CREATE TABLE t1; 1901Table Create Table 1902t1 CREATE TABLE `t1` ( 1903 `a` text /*!100301 COMPRESSED*/ CHARACTER SET utf8 DEFAULT '' 1904) ENGINE=MyISAM DEFAULT CHARSET=latin1 1905DROP TABLE t1; 1906CREATE TABLE t1 (a TEXT COMPRESSED CHARACTER SET utf8 GENERATED ALWAYS AS (REPEAT('a',100))); 1907SHOW CREATE TABLE t1; 1908Table Create Table 1909t1 CREATE TABLE `t1` ( 1910 `a` text /*!100301 COMPRESSED*/ CHARACTER SET utf8 GENERATED ALWAYS AS (repeat('a',100)) VIRTUAL 1911) ENGINE=MyISAM DEFAULT CHARSET=latin1 1912DROP TABLE t1; 1913# 1914# The following statements return deprecated syntax warnings 1915# 1916CREATE TABLE t1 (a TEXT BINARY COMPRESSED); 1917Warnings: 1918Warning 1287 '<data type> <character set clause> ... COMPRESSED...' is deprecated and will be removed in a future release. Please use '<data type> COMPRESSED... <character set clause> ...' instead 1919SHOW CREATE TABLE t1; 1920Table Create Table 1921t1 CREATE TABLE `t1` ( 1922 `a` text /*!100301 COMPRESSED*/ CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL 1923) ENGINE=MyISAM DEFAULT CHARSET=latin1 1924DROP TABLE t1; 1925CREATE TABLE t1 (a TEXT ASCII COMPRESSED); 1926Warnings: 1927Warning 1287 '<data type> <character set clause> ... COMPRESSED...' is deprecated and will be removed in a future release. Please use '<data type> COMPRESSED... <character set clause> ...' instead 1928SHOW CREATE TABLE t1; 1929Table Create Table 1930t1 CREATE TABLE `t1` ( 1931 `a` text /*!100301 COMPRESSED*/ DEFAULT NULL 1932) ENGINE=MyISAM DEFAULT CHARSET=latin1 1933DROP TABLE t1; 1934CREATE TABLE t1 (a TEXT BYTE COMPRESSED); 1935Warnings: 1936Warning 1287 '<data type> <character set clause> ... COMPRESSED...' is deprecated and will be removed in a future release. Please use '<data type> COMPRESSED... <character set clause> ...' instead 1937SHOW CREATE TABLE t1; 1938Table Create Table 1939t1 CREATE TABLE `t1` ( 1940 `a` blob /*!100301 COMPRESSED*/ DEFAULT NULL 1941) ENGINE=MyISAM DEFAULT CHARSET=latin1 1942DROP TABLE t1; 1943# 1944# The following statements fail by the grammar, 1945# because COMPRESSED immediately follows 'field_type'. 1946# 1947CREATE TABLE t1 (a TEXT COMPRESSED BYTE COMPRESSED); 1948ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'COMPRESSED)' at line 1 1949CREATE TABLE t1 (a TEXT COMPRESSED BINARY COMPRESSED); 1950ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'COMPRESSED)' at line 1 1951CREATE TABLE t1 (a TEXT COMPRESSED ASCII COMPRESSED); 1952ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'COMPRESSED)' at line 1 1953CREATE TABLE t1 (a TEXT COMPRESSED CHARACTER SET utf8 COMPRESSED); 1954ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'COMPRESSED)' at line 1 1955# 1956# The following statements are not prohibited by the *.yy grammar, 1957# because the sequence `field_type attribute COMPRESSED` is allowed 1958# (notice there is at least one attribute after `field_type`). 1959# The first COMPRESSED is parsed inside `field_type`. 1960# The second COMPRESSED passes through the parser but then is caught 1961# inside Column_definition::set_compressed_deprecated_with_type_check() 1962# and a syntax error is raised. 1963# 1964CREATE TABLE t1 (a TEXT COMPRESSED BYTE DEFAULT '' COMPRESSED); 1965ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'COMPRESSED)' at line 1 1966CREATE TABLE t1 (a TEXT COMPRESSED BINARY DEFAULT '' COMPRESSED); 1967ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'COMPRESSED)' at line 1 1968CREATE TABLE t1 (a TEXT COMPRESSED ASCII DEFAULT '' COMPRESSED); 1969ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'COMPRESSED)' at line 1 1970CREATE TABLE t1 (a TEXT COMPRESSED CHARACTER SET utf8 DEFAULT '' COMPRESSED); 1971ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'COMPRESSED)' at line 1 1972# 1973# The following statements run without warnings. 1974# The `compressed opt_binary` grammar sequence is covered. 1975# 1976CREATE TABLE t1 (a MEDIUMTEXT COMPRESSED); 1977SHOW CREATE TABLE t1; 1978Table Create Table 1979t1 CREATE TABLE `t1` ( 1980 `a` mediumtext /*!100301 COMPRESSED*/ DEFAULT NULL 1981) ENGINE=MyISAM DEFAULT CHARSET=latin1 1982DROP TABLE t1; 1983CREATE TABLE t1 (a MEDIUMTEXT COMPRESSED BINARY); 1984SHOW CREATE TABLE t1; 1985Table Create Table 1986t1 CREATE TABLE `t1` ( 1987 `a` mediumtext /*!100301 COMPRESSED*/ CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL 1988) ENGINE=MyISAM DEFAULT CHARSET=latin1 1989DROP TABLE t1; 1990CREATE TABLE t1 (a MEDIUMTEXT COMPRESSED BINARY ASCII); 1991SHOW CREATE TABLE t1; 1992Table Create Table 1993t1 CREATE TABLE `t1` ( 1994 `a` mediumtext /*!100301 COMPRESSED*/ CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL 1995) ENGINE=MyISAM DEFAULT CHARSET=latin1 1996DROP TABLE t1; 1997CREATE TABLE t1 (a MEDIUMTEXT COMPRESSED BYTE); 1998SHOW CREATE TABLE t1; 1999Table Create Table 2000t1 CREATE TABLE `t1` ( 2001 `a` mediumblob /*!100301 COMPRESSED*/ DEFAULT NULL 2002) ENGINE=MyISAM DEFAULT CHARSET=latin1 2003DROP TABLE t1; 2004CREATE TABLE t1 (a MEDIUMTEXT COMPRESSED ASCII); 2005SHOW CREATE TABLE t1; 2006Table Create Table 2007t1 CREATE TABLE `t1` ( 2008 `a` mediumtext /*!100301 COMPRESSED*/ DEFAULT NULL 2009) ENGINE=MyISAM DEFAULT CHARSET=latin1 2010DROP TABLE t1; 2011CREATE TABLE t1 (a MEDIUMTEXT COMPRESSED UNICODE); 2012SHOW CREATE TABLE t1; 2013Table Create Table 2014t1 CREATE TABLE `t1` ( 2015 `a` mediumtext /*!100301 COMPRESSED*/ CHARACTER SET ucs2 DEFAULT NULL 2016) ENGINE=MyISAM DEFAULT CHARSET=latin1 2017DROP TABLE t1; 2018CREATE TABLE t1 (a MEDIUMTEXT COMPRESSED CHARACTER SET utf8); 2019SHOW CREATE TABLE t1; 2020Table Create Table 2021t1 CREATE TABLE `t1` ( 2022 `a` mediumtext /*!100301 COMPRESSED*/ CHARACTER SET utf8 DEFAULT NULL 2023) ENGINE=MyISAM DEFAULT CHARSET=latin1 2024DROP TABLE t1; 2025# 2026# The following statements run without warnings. 2027# They have extra column attributes (or GENERATED) after COMPRESSED. 2028# 2029CREATE TABLE t1 (a MEDIUMTEXT COMPRESSED BYTE DEFAULT ''); 2030SHOW CREATE TABLE t1; 2031Table Create Table 2032t1 CREATE TABLE `t1` ( 2033 `a` mediumblob /*!100301 COMPRESSED*/ DEFAULT '' 2034) ENGINE=MyISAM DEFAULT CHARSET=latin1 2035DROP TABLE t1; 2036CREATE TABLE t1 (a MEDIUMTEXT COMPRESSED BINARY DEFAULT ''); 2037SHOW CREATE TABLE t1; 2038Table Create Table 2039t1 CREATE TABLE `t1` ( 2040 `a` mediumtext /*!100301 COMPRESSED*/ CHARACTER SET latin1 COLLATE latin1_bin DEFAULT '' 2041) ENGINE=MyISAM DEFAULT CHARSET=latin1 2042DROP TABLE t1; 2043CREATE TABLE t1 (a MEDIUMTEXT COMPRESSED ASCII DEFAULT ''); 2044SHOW CREATE TABLE t1; 2045Table Create Table 2046t1 CREATE TABLE `t1` ( 2047 `a` mediumtext /*!100301 COMPRESSED*/ DEFAULT '' 2048) ENGINE=MyISAM DEFAULT CHARSET=latin1 2049DROP TABLE t1; 2050CREATE TABLE t1 (a MEDIUMTEXT COMPRESSED CHARACTER SET utf8 DEFAULT ''); 2051SHOW CREATE TABLE t1; 2052Table Create Table 2053t1 CREATE TABLE `t1` ( 2054 `a` mediumtext /*!100301 COMPRESSED*/ CHARACTER SET utf8 DEFAULT '' 2055) ENGINE=MyISAM DEFAULT CHARSET=latin1 2056DROP TABLE t1; 2057CREATE TABLE t1 (a MEDIUMTEXT COMPRESSED CHARACTER SET utf8 GENERATED ALWAYS AS (REPEAT('a',100))); 2058SHOW CREATE TABLE t1; 2059Table Create Table 2060t1 CREATE TABLE `t1` ( 2061 `a` mediumtext /*!100301 COMPRESSED*/ CHARACTER SET utf8 GENERATED ALWAYS AS (repeat('a',100)) VIRTUAL 2062) ENGINE=MyISAM DEFAULT CHARSET=latin1 2063DROP TABLE t1; 2064# 2065# The following statements return deprecated syntax warnings 2066# 2067CREATE TABLE t1 (a MEDIUMTEXT BINARY COMPRESSED); 2068Warnings: 2069Warning 1287 '<data type> <character set clause> ... COMPRESSED...' is deprecated and will be removed in a future release. Please use '<data type> COMPRESSED... <character set clause> ...' instead 2070SHOW CREATE TABLE t1; 2071Table Create Table 2072t1 CREATE TABLE `t1` ( 2073 `a` mediumtext /*!100301 COMPRESSED*/ CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL 2074) ENGINE=MyISAM DEFAULT CHARSET=latin1 2075DROP TABLE t1; 2076CREATE TABLE t1 (a MEDIUMTEXT ASCII COMPRESSED); 2077Warnings: 2078Warning 1287 '<data type> <character set clause> ... COMPRESSED...' is deprecated and will be removed in a future release. Please use '<data type> COMPRESSED... <character set clause> ...' instead 2079SHOW CREATE TABLE t1; 2080Table Create Table 2081t1 CREATE TABLE `t1` ( 2082 `a` mediumtext /*!100301 COMPRESSED*/ DEFAULT NULL 2083) ENGINE=MyISAM DEFAULT CHARSET=latin1 2084DROP TABLE t1; 2085CREATE TABLE t1 (a MEDIUMTEXT BYTE COMPRESSED); 2086Warnings: 2087Warning 1287 '<data type> <character set clause> ... COMPRESSED...' is deprecated and will be removed in a future release. Please use '<data type> COMPRESSED... <character set clause> ...' instead 2088SHOW CREATE TABLE t1; 2089Table Create Table 2090t1 CREATE TABLE `t1` ( 2091 `a` mediumblob /*!100301 COMPRESSED*/ DEFAULT NULL 2092) ENGINE=MyISAM DEFAULT CHARSET=latin1 2093DROP TABLE t1; 2094# 2095# The following statements fail by the grammar, 2096# because COMPRESSED immediately follows 'field_type'. 2097# 2098CREATE TABLE t1 (a MEDIUMTEXT COMPRESSED BYTE COMPRESSED); 2099ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'COMPRESSED)' at line 1 2100CREATE TABLE t1 (a MEDIUMTEXT COMPRESSED BINARY COMPRESSED); 2101ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'COMPRESSED)' at line 1 2102CREATE TABLE t1 (a MEDIUMTEXT COMPRESSED ASCII COMPRESSED); 2103ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'COMPRESSED)' at line 1 2104CREATE TABLE t1 (a MEDIUMTEXT COMPRESSED CHARACTER SET utf8 COMPRESSED); 2105ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'COMPRESSED)' at line 1 2106# 2107# The following statements are not prohibited by the *.yy grammar, 2108# because the sequence `field_type attribute COMPRESSED` is allowed 2109# (notice there is at least one attribute after `field_type`). 2110# The first COMPRESSED is parsed inside `field_type`. 2111# The second COMPRESSED passes through the parser but then is caught 2112# inside Column_definition::set_compressed_deprecated_with_type_check() 2113# and a syntax error is raised. 2114# 2115CREATE TABLE t1 (a MEDIUMTEXT COMPRESSED BYTE DEFAULT '' COMPRESSED); 2116ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'COMPRESSED)' at line 1 2117CREATE TABLE t1 (a MEDIUMTEXT COMPRESSED BINARY DEFAULT '' COMPRESSED); 2118ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'COMPRESSED)' at line 1 2119CREATE TABLE t1 (a MEDIUMTEXT COMPRESSED ASCII DEFAULT '' COMPRESSED); 2120ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'COMPRESSED)' at line 1 2121CREATE TABLE t1 (a MEDIUMTEXT COMPRESSED CHARACTER SET utf8 DEFAULT '' COMPRESSED); 2122ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'COMPRESSED)' at line 1 2123# 2124# The following statements run without warnings. 2125# The `compressed opt_binary` grammar sequence is covered. 2126# 2127CREATE TABLE t1 (a LONGTEXT COMPRESSED); 2128SHOW CREATE TABLE t1; 2129Table Create Table 2130t1 CREATE TABLE `t1` ( 2131 `a` longtext /*!100301 COMPRESSED*/ DEFAULT NULL 2132) ENGINE=MyISAM DEFAULT CHARSET=latin1 2133DROP TABLE t1; 2134CREATE TABLE t1 (a LONGTEXT COMPRESSED BINARY); 2135SHOW CREATE TABLE t1; 2136Table Create Table 2137t1 CREATE TABLE `t1` ( 2138 `a` longtext /*!100301 COMPRESSED*/ CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL 2139) ENGINE=MyISAM DEFAULT CHARSET=latin1 2140DROP TABLE t1; 2141CREATE TABLE t1 (a LONGTEXT COMPRESSED BINARY ASCII); 2142SHOW CREATE TABLE t1; 2143Table Create Table 2144t1 CREATE TABLE `t1` ( 2145 `a` longtext /*!100301 COMPRESSED*/ CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL 2146) ENGINE=MyISAM DEFAULT CHARSET=latin1 2147DROP TABLE t1; 2148CREATE TABLE t1 (a LONGTEXT COMPRESSED BYTE); 2149SHOW CREATE TABLE t1; 2150Table Create Table 2151t1 CREATE TABLE `t1` ( 2152 `a` longblob /*!100301 COMPRESSED*/ DEFAULT NULL 2153) ENGINE=MyISAM DEFAULT CHARSET=latin1 2154DROP TABLE t1; 2155CREATE TABLE t1 (a LONGTEXT COMPRESSED ASCII); 2156SHOW CREATE TABLE t1; 2157Table Create Table 2158t1 CREATE TABLE `t1` ( 2159 `a` longtext /*!100301 COMPRESSED*/ DEFAULT NULL 2160) ENGINE=MyISAM DEFAULT CHARSET=latin1 2161DROP TABLE t1; 2162CREATE TABLE t1 (a LONGTEXT COMPRESSED UNICODE); 2163SHOW CREATE TABLE t1; 2164Table Create Table 2165t1 CREATE TABLE `t1` ( 2166 `a` longtext /*!100301 COMPRESSED*/ CHARACTER SET ucs2 DEFAULT NULL 2167) ENGINE=MyISAM DEFAULT CHARSET=latin1 2168DROP TABLE t1; 2169CREATE TABLE t1 (a LONGTEXT COMPRESSED CHARACTER SET utf8); 2170SHOW CREATE TABLE t1; 2171Table Create Table 2172t1 CREATE TABLE `t1` ( 2173 `a` longtext /*!100301 COMPRESSED*/ CHARACTER SET utf8 DEFAULT NULL 2174) ENGINE=MyISAM DEFAULT CHARSET=latin1 2175DROP TABLE t1; 2176# 2177# The following statements run without warnings. 2178# They have extra column attributes (or GENERATED) after COMPRESSED. 2179# 2180CREATE TABLE t1 (a LONGTEXT COMPRESSED BYTE DEFAULT ''); 2181SHOW CREATE TABLE t1; 2182Table Create Table 2183t1 CREATE TABLE `t1` ( 2184 `a` longblob /*!100301 COMPRESSED*/ DEFAULT '' 2185) ENGINE=MyISAM DEFAULT CHARSET=latin1 2186DROP TABLE t1; 2187CREATE TABLE t1 (a LONGTEXT COMPRESSED BINARY DEFAULT ''); 2188SHOW CREATE TABLE t1; 2189Table Create Table 2190t1 CREATE TABLE `t1` ( 2191 `a` longtext /*!100301 COMPRESSED*/ CHARACTER SET latin1 COLLATE latin1_bin DEFAULT '' 2192) ENGINE=MyISAM DEFAULT CHARSET=latin1 2193DROP TABLE t1; 2194CREATE TABLE t1 (a LONGTEXT COMPRESSED ASCII DEFAULT ''); 2195SHOW CREATE TABLE t1; 2196Table Create Table 2197t1 CREATE TABLE `t1` ( 2198 `a` longtext /*!100301 COMPRESSED*/ DEFAULT '' 2199) ENGINE=MyISAM DEFAULT CHARSET=latin1 2200DROP TABLE t1; 2201CREATE TABLE t1 (a LONGTEXT COMPRESSED CHARACTER SET utf8 DEFAULT ''); 2202SHOW CREATE TABLE t1; 2203Table Create Table 2204t1 CREATE TABLE `t1` ( 2205 `a` longtext /*!100301 COMPRESSED*/ CHARACTER SET utf8 DEFAULT '' 2206) ENGINE=MyISAM DEFAULT CHARSET=latin1 2207DROP TABLE t1; 2208CREATE TABLE t1 (a LONGTEXT COMPRESSED CHARACTER SET utf8 GENERATED ALWAYS AS (REPEAT('a',100))); 2209SHOW CREATE TABLE t1; 2210Table Create Table 2211t1 CREATE TABLE `t1` ( 2212 `a` longtext /*!100301 COMPRESSED*/ CHARACTER SET utf8 GENERATED ALWAYS AS (repeat('a',100)) VIRTUAL 2213) ENGINE=MyISAM DEFAULT CHARSET=latin1 2214DROP TABLE t1; 2215# 2216# The following statements return deprecated syntax warnings 2217# 2218CREATE TABLE t1 (a LONGTEXT BINARY COMPRESSED); 2219Warnings: 2220Warning 1287 '<data type> <character set clause> ... COMPRESSED...' is deprecated and will be removed in a future release. Please use '<data type> COMPRESSED... <character set clause> ...' instead 2221SHOW CREATE TABLE t1; 2222Table Create Table 2223t1 CREATE TABLE `t1` ( 2224 `a` longtext /*!100301 COMPRESSED*/ CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL 2225) ENGINE=MyISAM DEFAULT CHARSET=latin1 2226DROP TABLE t1; 2227CREATE TABLE t1 (a LONGTEXT ASCII COMPRESSED); 2228Warnings: 2229Warning 1287 '<data type> <character set clause> ... COMPRESSED...' is deprecated and will be removed in a future release. Please use '<data type> COMPRESSED... <character set clause> ...' instead 2230SHOW CREATE TABLE t1; 2231Table Create Table 2232t1 CREATE TABLE `t1` ( 2233 `a` longtext /*!100301 COMPRESSED*/ DEFAULT NULL 2234) ENGINE=MyISAM DEFAULT CHARSET=latin1 2235DROP TABLE t1; 2236CREATE TABLE t1 (a LONGTEXT BYTE COMPRESSED); 2237Warnings: 2238Warning 1287 '<data type> <character set clause> ... COMPRESSED...' is deprecated and will be removed in a future release. Please use '<data type> COMPRESSED... <character set clause> ...' instead 2239SHOW CREATE TABLE t1; 2240Table Create Table 2241t1 CREATE TABLE `t1` ( 2242 `a` longblob /*!100301 COMPRESSED*/ DEFAULT NULL 2243) ENGINE=MyISAM DEFAULT CHARSET=latin1 2244DROP TABLE t1; 2245# 2246# The following statements fail by the grammar, 2247# because COMPRESSED immediately follows 'field_type'. 2248# 2249CREATE TABLE t1 (a LONGTEXT COMPRESSED BYTE COMPRESSED); 2250ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'COMPRESSED)' at line 1 2251CREATE TABLE t1 (a LONGTEXT COMPRESSED BINARY COMPRESSED); 2252ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'COMPRESSED)' at line 1 2253CREATE TABLE t1 (a LONGTEXT COMPRESSED ASCII COMPRESSED); 2254ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'COMPRESSED)' at line 1 2255CREATE TABLE t1 (a LONGTEXT COMPRESSED CHARACTER SET utf8 COMPRESSED); 2256ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'COMPRESSED)' at line 1 2257# 2258# The following statements are not prohibited by the *.yy grammar, 2259# because the sequence `field_type attribute COMPRESSED` is allowed 2260# (notice there is at least one attribute after `field_type`). 2261# The first COMPRESSED is parsed inside `field_type`. 2262# The second COMPRESSED passes through the parser but then is caught 2263# inside Column_definition::set_compressed_deprecated_with_type_check() 2264# and a syntax error is raised. 2265# 2266CREATE TABLE t1 (a LONGTEXT COMPRESSED BYTE DEFAULT '' COMPRESSED); 2267ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'COMPRESSED)' at line 1 2268CREATE TABLE t1 (a LONGTEXT COMPRESSED BINARY DEFAULT '' COMPRESSED); 2269ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'COMPRESSED)' at line 1 2270CREATE TABLE t1 (a LONGTEXT COMPRESSED ASCII DEFAULT '' COMPRESSED); 2271ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'COMPRESSED)' at line 1 2272CREATE TABLE t1 (a LONGTEXT COMPRESSED CHARACTER SET utf8 DEFAULT '' COMPRESSED); 2273ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'COMPRESSED)' at line 1 2274# 2275# VARBINARY and BLOB variables 2276# 2277# 2278# The following statements run without warnings. 2279# 2280CREATE TABLE t1 (a VARCHAR(10) COMPRESSED); 2281SHOW CREATE TABLE t1; 2282Table Create Table 2283t1 CREATE TABLE `t1` ( 2284 `a` varchar(10) /*!100301 COMPRESSED*/ DEFAULT NULL 2285) ENGINE=MyISAM DEFAULT CHARSET=latin1 2286DROP TABLE t1; 2287# 2288# The following statements run without warnings. 2289# They have extra column attributes (or GENERATED) after COMPRESSED. 2290# 2291CREATE TABLE t1 (a VARCHAR(10) COMPRESSED DEFAULT ''); 2292SHOW CREATE TABLE t1; 2293Table Create Table 2294t1 CREATE TABLE `t1` ( 2295 `a` varchar(10) /*!100301 COMPRESSED*/ DEFAULT '' 2296) ENGINE=MyISAM DEFAULT CHARSET=latin1 2297DROP TABLE t1; 2298CREATE TABLE t1 (a VARCHAR(10) COMPRESSED NULL); 2299SHOW CREATE TABLE t1; 2300Table Create Table 2301t1 CREATE TABLE `t1` ( 2302 `a` varchar(10) /*!100301 COMPRESSED*/ DEFAULT NULL 2303) ENGINE=MyISAM DEFAULT CHARSET=latin1 2304DROP TABLE t1; 2305CREATE TABLE t1 (a VARCHAR(10) COMPRESSED GENERATED ALWAYS AS (REPEAT('a',10))); 2306SHOW CREATE TABLE t1; 2307Table Create Table 2308t1 CREATE TABLE `t1` ( 2309 `a` varchar(10) /*!100301 COMPRESSED*/ GENERATED ALWAYS AS (repeat('a',10)) VIRTUAL 2310) ENGINE=MyISAM DEFAULT CHARSET=latin1 2311DROP TABLE t1; 2312# 2313# The following statements return deprecated syntax warnings 2314# 2315CREATE TABLE t1 (a VARCHAR(10) DEFAULT '' COMPRESSED); 2316Warnings: 2317Warning 1287 '<data type> <character set clause> ... COMPRESSED...' is deprecated and will be removed in a future release. Please use '<data type> COMPRESSED... <character set clause> ...' instead 2318SHOW CREATE TABLE t1; 2319Table Create Table 2320t1 CREATE TABLE `t1` ( 2321 `a` varchar(10) /*!100301 COMPRESSED*/ DEFAULT '' 2322) ENGINE=MyISAM DEFAULT CHARSET=latin1 2323DROP TABLE t1; 2324CREATE TABLE t1 (a VARCHAR(10) NULL COMPRESSED); 2325Warnings: 2326Warning 1287 '<data type> <character set clause> ... COMPRESSED...' is deprecated and will be removed in a future release. Please use '<data type> COMPRESSED... <character set clause> ...' instead 2327SHOW CREATE TABLE t1; 2328Table Create Table 2329t1 CREATE TABLE `t1` ( 2330 `a` varchar(10) /*!100301 COMPRESSED*/ DEFAULT NULL 2331) ENGINE=MyISAM DEFAULT CHARSET=latin1 2332DROP TABLE t1; 2333# 2334# The following statements fail by the grammar, 2335# because COMPRESSED immediately follows 'field_type'. 2336# 2337CREATE TABLE t1 (a VARCHAR(10) COMPRESSED COMPRESSED); 2338ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'COMPRESSED)' at line 1 2339# 2340# The following statements are not prohibited by the *.yy grammar, 2341# because the sequence `field_type attribute COMPRESSED` is allowed 2342# (notice there is at least one attribute after `field_type`). 2343# The first COMPRESSED is parsed inside `field_type`. 2344# The second COMPRESSED passes through the parser but then is caught 2345# inside Column_definition::set_compressed_deprecated_with_type_check() 2346# and a syntax error is raised. 2347# 2348CREATE TABLE t1 (a VARCHAR(10) COMPRESSED DEFAULT '' COMPRESSED); 2349ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'COMPRESSED)' at line 1 2350CREATE TABLE t1 (a VARCHAR(10) COMPRESSED NULL COMPRESSED); 2351ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'COMPRESSED)' at line 1 2352# 2353# The following statements run without warnings. 2354# 2355CREATE TABLE t1 (a TINYBLOB COMPRESSED); 2356SHOW CREATE TABLE t1; 2357Table Create Table 2358t1 CREATE TABLE `t1` ( 2359 `a` tinyblob /*!100301 COMPRESSED*/ DEFAULT NULL 2360) ENGINE=MyISAM DEFAULT CHARSET=latin1 2361DROP TABLE t1; 2362# 2363# The following statements run without warnings. 2364# They have extra column attributes (or GENERATED) after COMPRESSED. 2365# 2366CREATE TABLE t1 (a TINYBLOB COMPRESSED DEFAULT ''); 2367SHOW CREATE TABLE t1; 2368Table Create Table 2369t1 CREATE TABLE `t1` ( 2370 `a` tinyblob /*!100301 COMPRESSED*/ DEFAULT '' 2371) ENGINE=MyISAM DEFAULT CHARSET=latin1 2372DROP TABLE t1; 2373CREATE TABLE t1 (a TINYBLOB COMPRESSED NULL); 2374SHOW CREATE TABLE t1; 2375Table Create Table 2376t1 CREATE TABLE `t1` ( 2377 `a` tinyblob /*!100301 COMPRESSED*/ DEFAULT NULL 2378) ENGINE=MyISAM DEFAULT CHARSET=latin1 2379DROP TABLE t1; 2380CREATE TABLE t1 (a TINYBLOB COMPRESSED GENERATED ALWAYS AS (REPEAT('a',10))); 2381SHOW CREATE TABLE t1; 2382Table Create Table 2383t1 CREATE TABLE `t1` ( 2384 `a` tinyblob /*!100301 COMPRESSED*/ GENERATED ALWAYS AS (repeat('a',10)) VIRTUAL 2385) ENGINE=MyISAM DEFAULT CHARSET=latin1 2386DROP TABLE t1; 2387# 2388# The following statements return deprecated syntax warnings 2389# 2390CREATE TABLE t1 (a TINYBLOB DEFAULT '' COMPRESSED); 2391Warnings: 2392Warning 1287 '<data type> <character set clause> ... COMPRESSED...' is deprecated and will be removed in a future release. Please use '<data type> COMPRESSED... <character set clause> ...' instead 2393SHOW CREATE TABLE t1; 2394Table Create Table 2395t1 CREATE TABLE `t1` ( 2396 `a` tinyblob /*!100301 COMPRESSED*/ DEFAULT '' 2397) ENGINE=MyISAM DEFAULT CHARSET=latin1 2398DROP TABLE t1; 2399CREATE TABLE t1 (a TINYBLOB NULL COMPRESSED); 2400Warnings: 2401Warning 1287 '<data type> <character set clause> ... COMPRESSED...' is deprecated and will be removed in a future release. Please use '<data type> COMPRESSED... <character set clause> ...' instead 2402SHOW CREATE TABLE t1; 2403Table Create Table 2404t1 CREATE TABLE `t1` ( 2405 `a` tinyblob /*!100301 COMPRESSED*/ DEFAULT NULL 2406) ENGINE=MyISAM DEFAULT CHARSET=latin1 2407DROP TABLE t1; 2408# 2409# The following statements fail by the grammar, 2410# because COMPRESSED immediately follows 'field_type'. 2411# 2412CREATE TABLE t1 (a TINYBLOB COMPRESSED COMPRESSED); 2413ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'COMPRESSED)' at line 1 2414# 2415# The following statements are not prohibited by the *.yy grammar, 2416# because the sequence `field_type attribute COMPRESSED` is allowed 2417# (notice there is at least one attribute after `field_type`). 2418# The first COMPRESSED is parsed inside `field_type`. 2419# The second COMPRESSED passes through the parser but then is caught 2420# inside Column_definition::set_compressed_deprecated_with_type_check() 2421# and a syntax error is raised. 2422# 2423CREATE TABLE t1 (a TINYBLOB COMPRESSED DEFAULT '' COMPRESSED); 2424ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'COMPRESSED)' at line 1 2425CREATE TABLE t1 (a TINYBLOB COMPRESSED NULL COMPRESSED); 2426ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'COMPRESSED)' at line 1 2427# 2428# The following statements run without warnings. 2429# 2430CREATE TABLE t1 (a BLOB COMPRESSED); 2431SHOW CREATE TABLE t1; 2432Table Create Table 2433t1 CREATE TABLE `t1` ( 2434 `a` blob /*!100301 COMPRESSED*/ DEFAULT NULL 2435) ENGINE=MyISAM DEFAULT CHARSET=latin1 2436DROP TABLE t1; 2437# 2438# The following statements run without warnings. 2439# They have extra column attributes (or GENERATED) after COMPRESSED. 2440# 2441CREATE TABLE t1 (a BLOB COMPRESSED DEFAULT ''); 2442SHOW CREATE TABLE t1; 2443Table Create Table 2444t1 CREATE TABLE `t1` ( 2445 `a` blob /*!100301 COMPRESSED*/ DEFAULT '' 2446) ENGINE=MyISAM DEFAULT CHARSET=latin1 2447DROP TABLE t1; 2448CREATE TABLE t1 (a BLOB COMPRESSED NULL); 2449SHOW CREATE TABLE t1; 2450Table Create Table 2451t1 CREATE TABLE `t1` ( 2452 `a` blob /*!100301 COMPRESSED*/ DEFAULT NULL 2453) ENGINE=MyISAM DEFAULT CHARSET=latin1 2454DROP TABLE t1; 2455CREATE TABLE t1 (a BLOB COMPRESSED GENERATED ALWAYS AS (REPEAT('a',10))); 2456SHOW CREATE TABLE t1; 2457Table Create Table 2458t1 CREATE TABLE `t1` ( 2459 `a` blob /*!100301 COMPRESSED*/ GENERATED ALWAYS AS (repeat('a',10)) VIRTUAL 2460) ENGINE=MyISAM DEFAULT CHARSET=latin1 2461DROP TABLE t1; 2462# 2463# The following statements return deprecated syntax warnings 2464# 2465CREATE TABLE t1 (a BLOB DEFAULT '' COMPRESSED); 2466Warnings: 2467Warning 1287 '<data type> <character set clause> ... COMPRESSED...' is deprecated and will be removed in a future release. Please use '<data type> COMPRESSED... <character set clause> ...' instead 2468SHOW CREATE TABLE t1; 2469Table Create Table 2470t1 CREATE TABLE `t1` ( 2471 `a` blob /*!100301 COMPRESSED*/ DEFAULT '' 2472) ENGINE=MyISAM DEFAULT CHARSET=latin1 2473DROP TABLE t1; 2474CREATE TABLE t1 (a BLOB NULL COMPRESSED); 2475Warnings: 2476Warning 1287 '<data type> <character set clause> ... COMPRESSED...' is deprecated and will be removed in a future release. Please use '<data type> COMPRESSED... <character set clause> ...' instead 2477SHOW CREATE TABLE t1; 2478Table Create Table 2479t1 CREATE TABLE `t1` ( 2480 `a` blob /*!100301 COMPRESSED*/ DEFAULT NULL 2481) ENGINE=MyISAM DEFAULT CHARSET=latin1 2482DROP TABLE t1; 2483# 2484# The following statements fail by the grammar, 2485# because COMPRESSED immediately follows 'field_type'. 2486# 2487CREATE TABLE t1 (a BLOB COMPRESSED COMPRESSED); 2488ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'COMPRESSED)' at line 1 2489# 2490# The following statements are not prohibited by the *.yy grammar, 2491# because the sequence `field_type attribute COMPRESSED` is allowed 2492# (notice there is at least one attribute after `field_type`). 2493# The first COMPRESSED is parsed inside `field_type`. 2494# The second COMPRESSED passes through the parser but then is caught 2495# inside Column_definition::set_compressed_deprecated_with_type_check() 2496# and a syntax error is raised. 2497# 2498CREATE TABLE t1 (a BLOB COMPRESSED DEFAULT '' COMPRESSED); 2499ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'COMPRESSED)' at line 1 2500CREATE TABLE t1 (a BLOB COMPRESSED NULL COMPRESSED); 2501ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'COMPRESSED)' at line 1 2502# 2503# The following statements run without warnings. 2504# 2505CREATE TABLE t1 (a MEDIUMBLOB COMPRESSED); 2506SHOW CREATE TABLE t1; 2507Table Create Table 2508t1 CREATE TABLE `t1` ( 2509 `a` mediumblob /*!100301 COMPRESSED*/ DEFAULT NULL 2510) ENGINE=MyISAM DEFAULT CHARSET=latin1 2511DROP TABLE t1; 2512# 2513# The following statements run without warnings. 2514# They have extra column attributes (or GENERATED) after COMPRESSED. 2515# 2516CREATE TABLE t1 (a MEDIUMBLOB COMPRESSED DEFAULT ''); 2517SHOW CREATE TABLE t1; 2518Table Create Table 2519t1 CREATE TABLE `t1` ( 2520 `a` mediumblob /*!100301 COMPRESSED*/ DEFAULT '' 2521) ENGINE=MyISAM DEFAULT CHARSET=latin1 2522DROP TABLE t1; 2523CREATE TABLE t1 (a MEDIUMBLOB COMPRESSED NULL); 2524SHOW CREATE TABLE t1; 2525Table Create Table 2526t1 CREATE TABLE `t1` ( 2527 `a` mediumblob /*!100301 COMPRESSED*/ DEFAULT NULL 2528) ENGINE=MyISAM DEFAULT CHARSET=latin1 2529DROP TABLE t1; 2530CREATE TABLE t1 (a MEDIUMBLOB COMPRESSED GENERATED ALWAYS AS (REPEAT('a',10))); 2531SHOW CREATE TABLE t1; 2532Table Create Table 2533t1 CREATE TABLE `t1` ( 2534 `a` mediumblob /*!100301 COMPRESSED*/ GENERATED ALWAYS AS (repeat('a',10)) VIRTUAL 2535) ENGINE=MyISAM DEFAULT CHARSET=latin1 2536DROP TABLE t1; 2537# 2538# The following statements return deprecated syntax warnings 2539# 2540CREATE TABLE t1 (a MEDIUMBLOB DEFAULT '' COMPRESSED); 2541Warnings: 2542Warning 1287 '<data type> <character set clause> ... COMPRESSED...' is deprecated and will be removed in a future release. Please use '<data type> COMPRESSED... <character set clause> ...' instead 2543SHOW CREATE TABLE t1; 2544Table Create Table 2545t1 CREATE TABLE `t1` ( 2546 `a` mediumblob /*!100301 COMPRESSED*/ DEFAULT '' 2547) ENGINE=MyISAM DEFAULT CHARSET=latin1 2548DROP TABLE t1; 2549CREATE TABLE t1 (a MEDIUMBLOB NULL COMPRESSED); 2550Warnings: 2551Warning 1287 '<data type> <character set clause> ... COMPRESSED...' is deprecated and will be removed in a future release. Please use '<data type> COMPRESSED... <character set clause> ...' instead 2552SHOW CREATE TABLE t1; 2553Table Create Table 2554t1 CREATE TABLE `t1` ( 2555 `a` mediumblob /*!100301 COMPRESSED*/ DEFAULT NULL 2556) ENGINE=MyISAM DEFAULT CHARSET=latin1 2557DROP TABLE t1; 2558# 2559# The following statements fail by the grammar, 2560# because COMPRESSED immediately follows 'field_type'. 2561# 2562CREATE TABLE t1 (a MEDIUMBLOB COMPRESSED COMPRESSED); 2563ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'COMPRESSED)' at line 1 2564# 2565# The following statements are not prohibited by the *.yy grammar, 2566# because the sequence `field_type attribute COMPRESSED` is allowed 2567# (notice there is at least one attribute after `field_type`). 2568# The first COMPRESSED is parsed inside `field_type`. 2569# The second COMPRESSED passes through the parser but then is caught 2570# inside Column_definition::set_compressed_deprecated_with_type_check() 2571# and a syntax error is raised. 2572# 2573CREATE TABLE t1 (a MEDIUMBLOB COMPRESSED DEFAULT '' COMPRESSED); 2574ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'COMPRESSED)' at line 1 2575CREATE TABLE t1 (a MEDIUMBLOB COMPRESSED NULL COMPRESSED); 2576ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'COMPRESSED)' at line 1 2577# 2578# The following statements run without warnings. 2579# 2580CREATE TABLE t1 (a LONGBLOB COMPRESSED); 2581SHOW CREATE TABLE t1; 2582Table Create Table 2583t1 CREATE TABLE `t1` ( 2584 `a` longblob /*!100301 COMPRESSED*/ DEFAULT NULL 2585) ENGINE=MyISAM DEFAULT CHARSET=latin1 2586DROP TABLE t1; 2587# 2588# The following statements run without warnings. 2589# They have extra column attributes (or GENERATED) after COMPRESSED. 2590# 2591CREATE TABLE t1 (a LONGBLOB COMPRESSED DEFAULT ''); 2592SHOW CREATE TABLE t1; 2593Table Create Table 2594t1 CREATE TABLE `t1` ( 2595 `a` longblob /*!100301 COMPRESSED*/ DEFAULT '' 2596) ENGINE=MyISAM DEFAULT CHARSET=latin1 2597DROP TABLE t1; 2598CREATE TABLE t1 (a LONGBLOB COMPRESSED NULL); 2599SHOW CREATE TABLE t1; 2600Table Create Table 2601t1 CREATE TABLE `t1` ( 2602 `a` longblob /*!100301 COMPRESSED*/ DEFAULT NULL 2603) ENGINE=MyISAM DEFAULT CHARSET=latin1 2604DROP TABLE t1; 2605CREATE TABLE t1 (a LONGBLOB COMPRESSED GENERATED ALWAYS AS (REPEAT('a',10))); 2606SHOW CREATE TABLE t1; 2607Table Create Table 2608t1 CREATE TABLE `t1` ( 2609 `a` longblob /*!100301 COMPRESSED*/ GENERATED ALWAYS AS (repeat('a',10)) VIRTUAL 2610) ENGINE=MyISAM DEFAULT CHARSET=latin1 2611DROP TABLE t1; 2612# 2613# The following statements return deprecated syntax warnings 2614# 2615CREATE TABLE t1 (a LONGBLOB DEFAULT '' COMPRESSED); 2616Warnings: 2617Warning 1287 '<data type> <character set clause> ... COMPRESSED...' is deprecated and will be removed in a future release. Please use '<data type> COMPRESSED... <character set clause> ...' instead 2618SHOW CREATE TABLE t1; 2619Table Create Table 2620t1 CREATE TABLE `t1` ( 2621 `a` longblob /*!100301 COMPRESSED*/ DEFAULT '' 2622) ENGINE=MyISAM DEFAULT CHARSET=latin1 2623DROP TABLE t1; 2624CREATE TABLE t1 (a LONGBLOB NULL COMPRESSED); 2625Warnings: 2626Warning 1287 '<data type> <character set clause> ... COMPRESSED...' is deprecated and will be removed in a future release. Please use '<data type> COMPRESSED... <character set clause> ...' instead 2627SHOW CREATE TABLE t1; 2628Table Create Table 2629t1 CREATE TABLE `t1` ( 2630 `a` longblob /*!100301 COMPRESSED*/ DEFAULT NULL 2631) ENGINE=MyISAM DEFAULT CHARSET=latin1 2632DROP TABLE t1; 2633# 2634# The following statements fail by the grammar, 2635# because COMPRESSED immediately follows 'field_type'. 2636# 2637CREATE TABLE t1 (a LONGBLOB COMPRESSED COMPRESSED); 2638ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'COMPRESSED)' at line 1 2639# 2640# The following statements are not prohibited by the *.yy grammar, 2641# because the sequence `field_type attribute COMPRESSED` is allowed 2642# (notice there is at least one attribute after `field_type`). 2643# The first COMPRESSED is parsed inside `field_type`. 2644# The second COMPRESSED passes through the parser but then is caught 2645# inside Column_definition::set_compressed_deprecated_with_type_check() 2646# and a syntax error is raised. 2647# 2648CREATE TABLE t1 (a LONGBLOB COMPRESSED DEFAULT '' COMPRESSED); 2649ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'COMPRESSED)' at line 1 2650CREATE TABLE t1 (a LONGBLOB COMPRESSED NULL COMPRESSED); 2651ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'COMPRESSED)' at line 1 2652# 2653# NVARCHAR 2654# 2655CREATE TABLE t1 (a NVARCHAR(10) COMPRESSED); 2656SHOW CREATE TABLE t1; 2657Table Create Table 2658t1 CREATE TABLE `t1` ( 2659 `a` varchar(10) /*!100301 COMPRESSED*/ CHARACTER SET utf8 DEFAULT NULL 2660) ENGINE=MyISAM DEFAULT CHARSET=latin1 2661DROP TABLE t1; 2662CREATE TABLE t1 (a NVARCHAR(10) COMPRESSED BINARY COMPRESSED); 2663ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'COMPRESSED)' at line 1 2664CREATE TABLE t1 (a NVARCHAR(10) COMPRESSED DEFAULT '' COMPRESSED); 2665ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'COMPRESSED)' at line 1 2666# 2667# MDEV-21348 - Memory leak in Storage-Engine Independent Column 2668# Compression 2669# 2670CREATE TABLE t1(a BLOB COMPRESSED); 2671SET column_compression_threshold=0; 2672INSERT INTO t1 VALUES('aa'); 2673SET column_compression_threshold=DEFAULT; 2674DROP TABLE t1; 2675