1CREATE TABLE t_int (c1 TINYINT, 2c2 SMALLINT, 3c3 MEDIUMINT, 4c4 INT, 5c5 INTEGER, 6c6 SMALLINT UNSIGNED, 7c7 MEDIUMINT UNSIGNED, 8c8 INT UNSIGNED, 9c9 INTEGER); 10CREATE TABLE t_bigint (c1 BIGINT, 11c2 BIGINT UNSIGNED); 12CREATE TABLE t_real (c1 REAL UNSIGNED, 13c2 DOUBLE UNSIGNED, 14c3 FLOAT UNSIGNED, 15c4 DOUBLE, 16c5 FLOAT); 17CREATE TABLE t_dec (c1 DECIMAL(4,2) UNSIGNED, 18c2 NUMERIC(4,2) UNSIGNED, 19c3 DECIMAL(4,2), 20c4 NUMERIC(4,2)); 21CREATE TABLE t_date (c1 DATE, 22c2 TIME, 23c3 TIMESTAMP, 24c4 DATETIME, 25c5 YEAR); 26CREATE TABLE t_char (c1 CHAR(32), 27c2 VARCHAR(128) BINARY, 28c3 VARCHAR(128), 29c4 BINARY(128), 30c5 VARBINARY(2048)); 31CREATE TABLE t_lob (c1 TINYBLOB, 32c2 BLOB, 33c3 MEDIUMBLOB, 34c4 LONGBLOB, 35c5 TINYTEXT, 36c6 TEXT, 37c7 MEDIUMTEXT, 38c8 LONGTEXT, 39c9 TINYTEXT BINARY, 40c10 TEXT BINARY, 41c11 MEDIUMTEXT BINARY, 42c12 LONGTEXT BINARY); 43CREATE TABLE t_spec (c1 BIT(16), 44c2 ENUM('v1','v2','v3'), 45c3 SET('v1','v2','v3'), 46c4 GEOMETRY); 47INSERT INTO t_int VALUES (91,92,93,94,95,96,97,98,99); 48INSERT INTO t_bigint VALUES (91,92); 49INSERT INTO t_real VALUES (9.51,9.52,9.53,9.54,9.55); 50INSERT INTO t_dec VALUES (9.51,9.52,9.53,9.54); 51INSERT INTO t_date VALUES ('2015-07-06','20:15','2015-12-31 14:59:48','2014-11-30 13:58:47','2015'); 52INSERT INTO t_char VALUES ('char-text','varchar-binary-text\nnext line','varchar-text','binary-text\nnext line','varbinary-text\nline 1\nline 2'); 53INSERT INTO t_lob VALUES ('tinyblob-text readable','blob-text readable','mediumblob-text readable', 54'longblob-text readable','tinytext','text','mediumtext','longtext', 55'tinytext-binary\nnext line','text-binary\nnext line', 56'mediumtext-binary\nnext line','longtext-binary \nnext line'); 57INSERT INTO t_spec VALUES ( b'111', 'v1', 'v1', ST_GEOMFROMTEXT('MULTIPOINT(0 0,2 2,4 4,6 6,8 8,10 10)')); 58SELECT * FROM t_int ; 59c1 c2 c3 c4 c5 c6 c7 c8 c9 6091 92 93 94 95 96 97 98 99 61SELECT * FROM t_bigint ; 62c1 c2 6391 92 64SELECT * FROM t_real; 65c1 c2 c3 c4 c5 669.51 9.52 9.53 9.54 9.55 67SELECT * FROM t_dec; 68c1 c2 c3 c4 699.51 9.52 9.53 9.54 70SELECT * FROM t_date; 71c1 c2 c3 c4 c5 722015-07-06 20:15:00 2015-12-31 14:59:48 2014-11-30 13:58:47 2015 73SELECT c3 FROM t_char; 74c3 75varchar-text 76SELECT c5,c6,c7,c8 FROM t_lob ; 77c5 c6 c7 c8 78tinytext text mediumtext longtext 79SELECT c2,c3 FROM t_spec; 80c2 c3 81v1 v1 82########################################## 83# Run plugin 84########################################## 85INSTALL PLUGIN test_sql_all_col_types SONAME 'TEST_SQL_ALL_COL_TYPES'; 86########################################## 87# Stop plugin 88########################################## 89UNINSTALL PLUGIN test_sql_all_col_types; 90########################################## 91# Plugin log 92########################################## 93======================================================================== 94Test in a server thread 95======================================================================== 96Open session_1 97Text representation 98======================================================================== 99 100Session 1: SELECT * FROM test.t_int 101num_cols: 9 102nb rows: 1 103c1 c2 c3 c4 c5 c6 c7 c8 c9 104TINY(1) SHORT(2) INT24(9) LONG(3) LONG(3) SHORT(2) INT24(9) LONG(3) LONG(3) 105() () () () () (UNSIGNED ) (UNSIGNED ) (UNSIGNED ) () 106Write a string 10791 92 93 94 95 96 97 98 99 108 109affected rows : 0 110server status : 34 111warn count : 0 112 113Session 1: SELECT * FROM test.t_bigint 114num_cols: 2 115nb rows: 1 116c1 c2 117LONGLONG(8) LONGLONG(8) 118() (UNSIGNED ) 119Write a string 12091 92 121 122affected rows : 0 123server status : 34 124warn count : 0 125 126Session 1: SELECT * FROM test.t_real 127num_cols: 5 128nb rows: 1 129c1 c2 c3 c4 c5 130DOUBLE(5) DOUBLE(5) FLOAT(4) DOUBLE(5) FLOAT(4) 131(UNSIGNED ) (UNSIGNED ) (UNSIGNED ) () () 132Write a string 1339.51 9.52 9.53 9.54 9.55 134 135affected rows : 0 136server status : 34 137warn count : 0 138 139Session 1: SELECT * FROM test.t_dec 140num_cols: 4 141nb rows: 1 142c1 c2 c3 c4 143NEWDECIMAL(246) NEWDECIMAL(246) NEWDECIMAL(246) NEWDECIMAL(246) 144(UNSIGNED ) (UNSIGNED ) () () 145Write a string 1469.51 9.52 9.53 9.54 147 148affected rows : 0 149server status : 34 150warn count : 0 151 152Session 1: SELECT * FROM test.t_date 153num_cols: 5 154nb rows: 1 155c1 c2 c3 c4 c5 156DATE(10) TIME(11) TIMESTAMP(7) DATETIME(12) YEAR(13) 157(BINARY ) (BINARY ) (NOT_NULL BINARY TIMESTAMP ON_UPDATE_NOW ) (BINARY ) (UNSIGNED ZEROFILL ) 158Write a string 1592015-07-06 20:15:00 2015-12-31 14:59:48 2014-11-30 13:58:47 2015 160 161affected rows : 0 162server status : 34 163warn count : 0 164 165Session 1: SELECT * FROM test.t_char 166num_cols: 5 167nb rows: 1 168c1 c2 c3 c4 c5 169STRING(254) VARCHAR(15) VARCHAR(15) STRING(254) VARCHAR(15) 170() (BINARY ) () (BINARY ) (BINARY ) 171Write a string 172char-text varchar-binary-text 173next line varchar-text binary-text 174next line varbinary-text 175line 1 176line 2 177 178affected rows : 0 179server status : 34 180warn count : 0 181 182Session 1: SELECT * FROM test.t_lob 183num_cols: 12 184nb rows: 1 185c1 c2 c3 c4 c5 c6 c7 c8 c9 c10 c11 c12 186BLOB(252) BLOB(252) BLOB(252) BLOB(252) BLOB(252) BLOB(252) BLOB(252) BLOB(252) BLOB(252) BLOB(252) BLOB(252) BLOB(252) 187(BLOB BINARY ) (BLOB BINARY ) (BLOB BINARY ) (BLOB BINARY ) (BLOB ) (BLOB ) (BLOB ) (BLOB ) (BLOB BINARY ) (BLOB BINARY ) (BLOB BINARY ) (BLOB BINARY ) 188Write a string 189tinyblob-text readable blob-text readable mediumblob-text readable longblob-text readable tinytext text mediumtext longtext tinytext-binary 190next line text-binary 191next line mediumtext-binary 192next line longtext-binary 193next line 194 195affected rows : 0 196server status : 34 197warn count : 0 198 199Session 1: SELECT * FROM test.t_spec 200num_cols: 4 201nb rows: 1 202c1 c2 c3 c4 203BIT(16) STRING(254) STRING(254) GEOMETRY(255) 204(UNSIGNED ) (ENUM ) (SET ) (BLOB BINARY ) 205Write a string 206 v1 v1 207 208affected rows : 0 209server status : 34 210warn count : 0 211======================================================================== 212Binary representation 213======================================================================== 214 215Session 1: SELECT * FROM test.t_int 216num_cols: 9 217nb rows: 1 218c1 c2 c3 c4 c5 c6 c7 c8 c9 219TINY(1) SHORT(2) INT24(9) LONG(3) LONG(3) SHORT(2) INT24(9) LONG(3) LONG(3) 220() () () () () (UNSIGNED ) (UNSIGNED ) (UNSIGNED ) () 22191*10=910 92*10=920 93*10=930 94*10=940 95*10=950 96*10=960 97*10=970 98*10=980 99*10=990 222 223affected rows : 0 224server status : 34 225warn count : 0 226 227Session 1: SELECT * FROM test.t_bigint 228num_cols: 2 229nb rows: 1 230c1 c2 231LONGLONG(8) LONGLONG(8) 232() (UNSIGNED ) 23391*10=910 92*10=920 234 235affected rows : 0 236server status : 34 237warn count : 0 238 239Session 1: SELECT * FROM test.t_real 240num_cols: 5 241nb rows: 1 242c1 c2 c3 c4 c5 243DOUBLE(5) DOUBLE(5) FLOAT(4) DOUBLE(5) FLOAT(4) 244(UNSIGNED ) (UNSIGNED ) (UNSIGNED ) () () 2459.510000*10=95.100000 9.520000*10=95.200000 9.530000*10=95.299997 9.540000*10=95.400000 9.550000*10=95.500002 246 247affected rows : 0 248server status : 34 249warn count : 0 250 251Session 1: SELECT * FROM test.t_dec 252num_cols: 4 253nb rows: 1 254c1 c2 c3 c4 255NEWDECIMAL(246) NEWDECIMAL(246) NEWDECIMAL(246) NEWDECIMAL(246) 256(UNSIGNED ) (UNSIGNED ) () () 2579.51 9.52 9.53 9.54 258 259affected rows : 0 260server status : 34 261warn count : 0 262 263Session 1: SELECT * FROM test.t_date 264num_cols: 5 265nb rows: 1 266c1 c2 c3 c4 c5 267DATE(10) TIME(11) TIMESTAMP(7) DATETIME(12) YEAR(13) 268(BINARY ) (BINARY ) (NOT_NULL BINARY TIMESTAMP ON_UPDATE_NOW ) (BINARY ) (UNSIGNED ZEROFILL ) 269 2015-07-06 | 20:15:00 | 2015-12-31 14:59:48 | 2014-11-30 13:58:47 |2015 270 271affected rows : 0 272server status : 34 273warn count : 0 274 275Session 1: SELECT * FROM test.t_char 276num_cols: 5 277nb rows: 1 278c1 c2 c3 c4 c5 279STRING(254) VARCHAR(15) VARCHAR(15) STRING(254) VARCHAR(15) 280() (BINARY ) () (BINARY ) (BINARY ) 28100:char-text 28201:varchar-binary-text 283next line 28402:varchar-text 28503:binary-text 286next line 28704:varbinary-text 288line 1 289line 2 290 291 292affected rows : 0 293server status : 34 294warn count : 0 295 296Session 1: SELECT * FROM test.t_lob 297num_cols: 12 298nb rows: 1 299c1 c2 c3 c4 c5 c6 c7 c8 c9 c10 c11 c12 300BLOB(252) BLOB(252) BLOB(252) BLOB(252) BLOB(252) BLOB(252) BLOB(252) BLOB(252) BLOB(252) BLOB(252) BLOB(252) BLOB(252) 301(BLOB BINARY ) (BLOB BINARY ) (BLOB BINARY ) (BLOB BINARY ) (BLOB ) (BLOB ) (BLOB ) (BLOB ) (BLOB BINARY ) (BLOB BINARY ) (BLOB BINARY ) (BLOB BINARY ) 30200:tinyblob-text readable 30301:blob-text readable 30402:mediumblob-text readable 30503:longblob-text readable 30604:tinytext 30705:text 30806:mediumtext 30907:longtext 31008:tinytext-binary 311next line 31209:text-binary 313next line 31410:mediumtext-binary 315next line 31611:longtext-binary 317next line 318 319 320affected rows : 0 321server status : 34 322warn count : 0 323 324Session 1: SELECT * FROM test.t_spec 325num_cols: 4 326nb rows: 1 327c1 c2 c3 c4 328BIT(16) STRING(254) STRING(254) GEOMETRY(255) 329(UNSIGNED ) (ENUM ) (SET ) (BLOB BINARY ) 330get_? 33101:v1 33202:v1 333get_? 334 335 336affected rows : 0 337server status : 34 338warn count : 0 339sql_session_close_session. 340Follows threaded run 341======================================================================== 342init thread 343======================================================================== 344Open session_1 345Text representation 346======================================================================== 347 348Session 1: SELECT * FROM test.t_int 349num_cols: 9 350nb rows: 1 351c1 c2 c3 c4 c5 c6 c7 c8 c9 352TINY(1) SHORT(2) INT24(9) LONG(3) LONG(3) SHORT(2) INT24(9) LONG(3) LONG(3) 353() () () () () (UNSIGNED ) (UNSIGNED ) (UNSIGNED ) () 354Write a string 35591 92 93 94 95 96 97 98 99 356 357affected rows : 0 358server status : 34 359warn count : 0 360 361Session 1: SELECT * FROM test.t_bigint 362num_cols: 2 363nb rows: 1 364c1 c2 365LONGLONG(8) LONGLONG(8) 366() (UNSIGNED ) 367Write a string 36891 92 369 370affected rows : 0 371server status : 34 372warn count : 0 373 374Session 1: SELECT * FROM test.t_real 375num_cols: 5 376nb rows: 1 377c1 c2 c3 c4 c5 378DOUBLE(5) DOUBLE(5) FLOAT(4) DOUBLE(5) FLOAT(4) 379(UNSIGNED ) (UNSIGNED ) (UNSIGNED ) () () 380Write a string 3819.51 9.52 9.53 9.54 9.55 382 383affected rows : 0 384server status : 34 385warn count : 0 386 387Session 1: SELECT * FROM test.t_dec 388num_cols: 4 389nb rows: 1 390c1 c2 c3 c4 391NEWDECIMAL(246) NEWDECIMAL(246) NEWDECIMAL(246) NEWDECIMAL(246) 392(UNSIGNED ) (UNSIGNED ) () () 393Write a string 3949.51 9.52 9.53 9.54 395 396affected rows : 0 397server status : 34 398warn count : 0 399 400Session 1: SELECT * FROM test.t_date 401num_cols: 5 402nb rows: 1 403c1 c2 c3 c4 c5 404DATE(10) TIME(11) TIMESTAMP(7) DATETIME(12) YEAR(13) 405(BINARY ) (BINARY ) (NOT_NULL BINARY TIMESTAMP ON_UPDATE_NOW ) (BINARY ) (UNSIGNED ZEROFILL ) 406Write a string 4072015-07-06 20:15:00 2015-12-31 14:59:48 2014-11-30 13:58:47 2015 408 409affected rows : 0 410server status : 34 411warn count : 0 412 413Session 1: SELECT * FROM test.t_char 414num_cols: 5 415nb rows: 1 416c1 c2 c3 c4 c5 417STRING(254) VARCHAR(15) VARCHAR(15) STRING(254) VARCHAR(15) 418() (BINARY ) () (BINARY ) (BINARY ) 419Write a string 420char-text varchar-binary-text 421next line varchar-text binary-text 422next line varbinary-text 423line 1 424line 2 425 426affected rows : 0 427server status : 34 428warn count : 0 429 430Session 1: SELECT * FROM test.t_lob 431num_cols: 12 432nb rows: 1 433c1 c2 c3 c4 c5 c6 c7 c8 c9 c10 c11 c12 434BLOB(252) BLOB(252) BLOB(252) BLOB(252) BLOB(252) BLOB(252) BLOB(252) BLOB(252) BLOB(252) BLOB(252) BLOB(252) BLOB(252) 435(BLOB BINARY ) (BLOB BINARY ) (BLOB BINARY ) (BLOB BINARY ) (BLOB ) (BLOB ) (BLOB ) (BLOB ) (BLOB BINARY ) (BLOB BINARY ) (BLOB BINARY ) (BLOB BINARY ) 436Write a string 437tinyblob-text readable blob-text readable mediumblob-text readable longblob-text readable tinytext text mediumtext longtext tinytext-binary 438next line text-binary 439next line mediumtext-binary 440next line longtext-binary 441next line 442 443affected rows : 0 444server status : 34 445warn count : 0 446 447Session 1: SELECT * FROM test.t_spec 448num_cols: 4 449nb rows: 1 450c1 c2 c3 c4 451BIT(16) STRING(254) STRING(254) GEOMETRY(255) 452(UNSIGNED ) (ENUM ) (SET ) (BLOB BINARY ) 453Write a string 454 v1 v1 455 456affected rows : 0 457server status : 34 458warn count : 0 459======================================================================== 460Binary representation 461======================================================================== 462 463Session 1: SELECT * FROM test.t_int 464num_cols: 9 465nb rows: 1 466c1 c2 c3 c4 c5 c6 c7 c8 c9 467TINY(1) SHORT(2) INT24(9) LONG(3) LONG(3) SHORT(2) INT24(9) LONG(3) LONG(3) 468() () () () () (UNSIGNED ) (UNSIGNED ) (UNSIGNED ) () 46991*10=910 92*10=920 93*10=930 94*10=940 95*10=950 96*10=960 97*10=970 98*10=980 99*10=990 470 471affected rows : 0 472server status : 34 473warn count : 0 474 475Session 1: SELECT * FROM test.t_bigint 476num_cols: 2 477nb rows: 1 478c1 c2 479LONGLONG(8) LONGLONG(8) 480() (UNSIGNED ) 48191*10=910 92*10=920 482 483affected rows : 0 484server status : 34 485warn count : 0 486 487Session 1: SELECT * FROM test.t_real 488num_cols: 5 489nb rows: 1 490c1 c2 c3 c4 c5 491DOUBLE(5) DOUBLE(5) FLOAT(4) DOUBLE(5) FLOAT(4) 492(UNSIGNED ) (UNSIGNED ) (UNSIGNED ) () () 4939.510000*10=95.100000 9.520000*10=95.200000 9.530000*10=95.299997 9.540000*10=95.400000 9.550000*10=95.500002 494 495affected rows : 0 496server status : 34 497warn count : 0 498 499Session 1: SELECT * FROM test.t_dec 500num_cols: 4 501nb rows: 1 502c1 c2 c3 c4 503NEWDECIMAL(246) NEWDECIMAL(246) NEWDECIMAL(246) NEWDECIMAL(246) 504(UNSIGNED ) (UNSIGNED ) () () 5059.51 9.52 9.53 9.54 506 507affected rows : 0 508server status : 34 509warn count : 0 510 511Session 1: SELECT * FROM test.t_date 512num_cols: 5 513nb rows: 1 514c1 c2 c3 c4 c5 515DATE(10) TIME(11) TIMESTAMP(7) DATETIME(12) YEAR(13) 516(BINARY ) (BINARY ) (NOT_NULL BINARY TIMESTAMP ON_UPDATE_NOW ) (BINARY ) (UNSIGNED ZEROFILL ) 517 2015-07-06 | 20:15:00 | 2015-12-31 14:59:48 | 2014-11-30 13:58:47 |2015 518 519affected rows : 0 520server status : 34 521warn count : 0 522 523Session 1: SELECT * FROM test.t_char 524num_cols: 5 525nb rows: 1 526c1 c2 c3 c4 c5 527STRING(254) VARCHAR(15) VARCHAR(15) STRING(254) VARCHAR(15) 528() (BINARY ) () (BINARY ) (BINARY ) 52900:char-text 53001:varchar-binary-text 531next line 53202:varchar-text 53303:binary-text 534next line 53504:varbinary-text 536line 1 537line 2 538 539 540affected rows : 0 541server status : 34 542warn count : 0 543 544Session 1: SELECT * FROM test.t_lob 545num_cols: 12 546nb rows: 1 547c1 c2 c3 c4 c5 c6 c7 c8 c9 c10 c11 c12 548BLOB(252) BLOB(252) BLOB(252) BLOB(252) BLOB(252) BLOB(252) BLOB(252) BLOB(252) BLOB(252) BLOB(252) BLOB(252) BLOB(252) 549(BLOB BINARY ) (BLOB BINARY ) (BLOB BINARY ) (BLOB BINARY ) (BLOB ) (BLOB ) (BLOB ) (BLOB ) (BLOB BINARY ) (BLOB BINARY ) (BLOB BINARY ) (BLOB BINARY ) 55000:tinyblob-text readable 55101:blob-text readable 55202:mediumblob-text readable 55303:longblob-text readable 55404:tinytext 55505:text 55606:mediumtext 55707:longtext 55808:tinytext-binary 559next line 56009:text-binary 561next line 56210:mediumtext-binary 563next line 56411:longtext-binary 565next line 566 567 568affected rows : 0 569server status : 34 570warn count : 0 571 572Session 1: SELECT * FROM test.t_spec 573num_cols: 4 574nb rows: 1 575c1 c2 c3 c4 576BIT(16) STRING(254) STRING(254) GEOMETRY(255) 577(UNSIGNED ) (ENUM ) (SET ) (BLOB BINARY ) 578get_? 57901:v1 58002:v1 581get_? 582 583 584affected rows : 0 585server status : 34 586warn count : 0 587sql_session_close_session. 588deinit thread 589########################################## 590# Cleanup 591########################################## 592DROP TABLE IF EXISTS t_int; 593DROP TABLE IF EXISTS t_bigint; 594DROP TABLE IF EXISTS t_real; 595DROP TABLE IF EXISTS t_dec; 596DROP TABLE IF EXISTS t_date; 597DROP TABLE IF EXISTS t_char; 598DROP TABLE IF EXISTS t_lob ; 599DROP TABLE IF EXISTS t_spec; 600