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