Debian Codegraph @

mysql-test/suite/storage_engine/col_opt_not_null.result

raw
1 #
2 # BINARY columns
3 #
4 DROP TABLE IF EXISTS t1;
5 CREATE TABLE t1 (b BINARY <CUSTOM_COL_OPTIONS> NOT NULL,
6 b0 BINARY(0) <CUSTOM_COL_OPTIONS> NOT NULL,
7 b1 BINARY(1) <CUSTOM_COL_OPTIONS> NOT NULL,
8 b20 BINARY(20) <CUSTOM_COL_OPTIONS> NOT NULL,
9 b255 BINARY(255) <CUSTOM_COL_OPTIONS> NOT NULL
10 ) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>;
11 SHOW COLUMNS IN t1;
12 Field Type Null Key Default Extra
13 b binary(1) # # # #
14 b0 binary(0) # # # #
15 b1 binary(1) # # # #
16 b20 binary(20) # # # #
17 b255 binary(255) # # # #
18 INSERT INTO t1 (b,b0,b1,b20,b255) VALUES ('','','','','');
19 INSERT INTO t1 (b,b0,b1,b20,b255) VALUES ('a','','b','abcdefghi klmnopqrst', 'Creating an article for the Knowledgebase is similar to asking questions. First, navigate to the category where you feel the article should be. Once there, double check that an article doesn\'t already exist which would work.');
20 SELECT HEX(b), HEX(b0), HEX(b1), HEX(b20), HEX(b255) FROM t1;
21 HEX(b) HEX(b0) HEX(b1) HEX(b20) HEX(b255)
22 00 00 0000000000000000000000000000000000000000 000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
23 61 62 616263646566676869206B6C6D6E6F7071727374 4372656174696E6720616E2061727469636C6520666F7220746865204B6E6F776C65646765626173652069732073696D696C617220746F2061736B696E67207175657374696F6E732E2046697273742C206E6176696761746520746F207468652063617465676F727920776865726520796F75206665656C207468652061727469636C652073686F756C642062652E204F6E63652074686572652C20646F75626C6520636865636B207468617420616E2061727469636C6520646F65736E277420616C726561647920657869737420776869636820776F756C6420776F726B2E00000000000000000000000000000000000000000000000000000000000000
24 INSERT INTO t1 (b,b0,b1,b20,b255) VALUES ('abc', 'a', 'abc', REPEAT('a',21), REPEAT('x',256));
25 Warnings:
26 Warning 1265 Data truncated for column 'b' at row 1
27 Warning 1265 Data truncated for column 'b0' at row 1
28 Warning 1265 Data truncated for column 'b1' at row 1
29 Warning 1265 Data truncated for column 'b20' at row 1
30 Warning 1265 Data truncated for column 'b255' at row 1
31 INSERT INTO t1 (b,b0,b1,b20,b255) SELECT b255, b255, b255, b255, CONCAT(b255,b255) FROM t1;
32 Warnings:
33 Warning 1265 Data truncated for column 'b' at row 1
34 Warning 1265 Data truncated for column 'b0' at row 1
35 Warning 1265 Data truncated for column 'b1' at row 1
36 Warning 1265 Data truncated for column 'b20' at row 1
37 Warning 1265 Data truncated for column 'b255' at row 1
38 Warning 1265 Data truncated for column 'b' at row 2
39 Warning 1265 Data truncated for column 'b0' at row 2
40 Warning 1265 Data truncated for column 'b1' at row 2
41 Warning 1265 Data truncated for column 'b20' at row 2
42 Warning 1265 Data truncated for column 'b255' at row 2
43 Warning 1265 Data truncated for column 'b' at row 3
44 Warning 1265 Data truncated for column 'b0' at row 3
45 Warning 1265 Data truncated for column 'b1' at row 3
46 Warning 1265 Data truncated for column 'b20' at row 3
47 Warning 1265 Data truncated for column 'b255' at row 3
48 SELECT HEX(b), HEX(b0), HEX(b1), HEX(b20), HEX(b255) FROM t1;
49 HEX(b) HEX(b0) HEX(b1) HEX(b20) HEX(b255)
50 00 00 0000000000000000000000000000000000000000 000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
51 00 00 0000000000000000000000000000000000000000 000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
52 43 43 4372656174696E6720616E2061727469636C6520 4372656174696E6720616E2061727469636C6520666F7220746865204B6E6F776C65646765626173652069732073696D696C617220746F2061736B696E67207175657374696F6E732E2046697273742C206E6176696761746520746F207468652063617465676F727920776865726520796F75206665656C207468652061727469636C652073686F756C642062652E204F6E63652074686572652C20646F75626C6520636865636B207468617420616E2061727469636C6520646F65736E277420616C726561647920657869737420776869636820776F756C6420776F726B2E00000000000000000000000000000000000000000000000000000000000000
53 61 61 6161616161616161616161616161616161616161 787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878
54 61 62 616263646566676869206B6C6D6E6F7071727374 4372656174696E6720616E2061727469636C6520666F7220746865204B6E6F776C65646765626173652069732073696D696C617220746F2061736B696E67207175657374696F6E732E2046697273742C206E6176696761746520746F207468652063617465676F727920776865726520796F75206665656C207468652061727469636C652073686F756C642062652E204F6E63652074686572652C20646F75626C6520636865636B207468617420616E2061727469636C6520646F65736E277420616C726561647920657869737420776869636820776F756C6420776F726B2E00000000000000000000000000000000000000000000000000000000000000
55 78 78 7878787878787878787878787878787878787878 787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878
56 ALTER TABLE t1 ADD COLUMN b257 BINARY(257) <CUSTOM_COL_OPTIONS> NOT NULL;
57 ERROR 42000: Column length too big for column 'b257' (max = 255); use BLOB or TEXT instead
58 SHOW COLUMNS IN t1;
59 Field Type Null Key Default Extra
60 b binary(1) # # # #
61 b0 binary(0) # # # #
62 b1 binary(1) # # # #
63 b20 binary(20) # # # #
64 b255 binary(255) # # # #
65 DROP TABLE t1;
66 DROP TABLE IF EXISTS t1;
67 CREATE TABLE t1 (c BINARY <CUSTOM_COL_OPTIONS> NOT NULL) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>;
68 SHOW COLUMNS IN t1;
69 Field Type Null Key Default Extra
70 c binary(1) NO NULL
71 INSERT INTO t1 (c) VALUES (NULL);
72 ERROR 23000: Column 'c' cannot be null
73 DROP TABLE t1;
74 CREATE TABLE t1 (c BINARY <CUSTOM_COL_OPTIONS> NOT NULL,
75 c2 BINARY <CUSTOM_COL_OPTIONS> NOT NULL DEFAULT 0
76 ) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>;
77 SHOW COLUMNS IN t1;
78 Field Type Null Key Default Extra
79 c binary(1) NO NULL
80 c2 binary(1) NO 0
81 ALTER TABLE t1 ADD COLUMN err BINARY <CUSTOM_COL_OPTIONS> NOT NULL DEFAULT NULL;
82 ERROR 42000: Invalid default value for 'err'
83 INSERT INTO t1 (c) VALUES (NULL);
84 ERROR 23000: Column 'c' cannot be null
85 SELECT HEX(c), HEX(c2) FROM t1;
86 HEX(c) HEX(c2)
87 INSERT INTO t1 (c2) VALUES (NULL);
88 ERROR 23000: Column 'c2' cannot be null
89 INSERT INTO t1 (c) VALUES (0);
90 SELECT COUNT(c), COUNT(c2) FROM t1;
91 COUNT(c) COUNT(c2)
92 1 1
93 DROP TABLE t1;
94 #
95 # VARBINARY columns
96 #
97 DROP TABLE IF EXISTS t1, t2;
98 CREATE TABLE t1 (v0 VARBINARY(0) <CUSTOM_COL_OPTIONS> NOT NULL,
99 v1 VARBINARY(1) <CUSTOM_COL_OPTIONS> NOT NULL,
100 v64 VARBINARY(64) <CUSTOM_COL_OPTIONS> NOT NULL,
101 v65000 VARBINARY(65000) <CUSTOM_COL_OPTIONS> NOT NULL
102 ) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>;
103 SHOW COLUMNS IN t1;
104 Field Type Null Key Default Extra
105 v0 varbinary(0) # # #
106 v1 varbinary(1) # # #
107 v64 varbinary(64) # # #
108 v65000 varbinary(65000) # # #
109 CREATE TABLE t2 (v VARBINARY(65532) <CUSTOM_COL_OPTIONS> NOT NULL) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>;
110 SHOW COLUMNS IN t2;
111 Field Type Null Key Default Extra
112 v varbinary(65532) # # #
113 INSERT INTO t1 (v0,v1,v64,v65000) VALUES ('','','','');
114 INSERT INTO t1 (v0,v1,v64,v65000) VALUES ('','y','Once there, double check that an article doesn\'t already exist','Here is a list of recommended books on MariaDB and MySQL. We\'ve provided links to Amazon.com here for convenience, but they can be found at many other bookstores, both online and off.
115
116 If you want to have your favorite MySQL / MariaDB book listed here, please leave a comment.
117 For developers who want to code on MariaDB or MySQL
118
119 * Understanding MySQL Internals by Sasha Pachev, former MySQL developer at MySQL AB.
120 o This is the only book we know about that describes the internals of MariaDB / MySQL. A must have for anyone who wants to understand and develop on MariaDB!
121 o Not all topics are covered and some parts are slightly outdated, but still the best book on this topic.
122 * MySQL 5.1 Plugin Development by Sergei Golubchik and Andrew Hutchings
123 o A must read for anyone wanting to write a plugin for MariaDB, written by the Sergei who designed the plugin interface for MySQL and MariaDB!
124
125 For MariaDB / MySQL end users
126
127 * MariaDB Crash Course by Ben Forta
128 o First MariaDB book!
129 o For people who want to learn SQL and the basics of MariaDB.
130 o Now shipping. Purchase at Amazon.com or your favorite bookseller.
131
132 * SQL-99 Complete, Really by Peter Gulutzan & Trudy Pelzer.
133 o Everything you wanted to know about the SQL 99 standard. Excellent reference book!
134 o Free to read in the Knowledgebase!
135
136 * MySQL (4th Edition) by Paul DuBois
137 o The \'default\' book to read if you wont to learn to use MySQL / MariaDB.
138
139 * MySQL Cookbook by Paul DuBois
140 o A lot of examples of how to use MySQL. As with all of Paul\'s books, it\'s worth its weight in gold and even enjoyable reading for such a \'dry\' subject.
141
142 * High Performance MySQL, Second Edition, By Baron Schwartz, Peter Zaitsev, Vadim Tkachenko, Jeremy D. Zawodny, Arjen Lentz, Derek J. Balling, et al.
143 o \"High Performance MySQL is the definitive guide to building fast, reliable systems with MySQL. Written by noted experts with years of real-world experience building very large systems, this book covers every aspect of MySQL performance in detail, and focuses on robustness, security, and data integrity. Learn advanced techniques in depth so you can bring out MySQL\'s full power.\" (From the book description at O\'Reilly)
144
145 * MySQL Admin Cookbook
146 o A quick step-by-step guide for MySQL users and database administrators to tackle real-world challenges with MySQL configuration and administration
147
148 * MySQL 5.0 Certification Study Guide, By Paul DuBois, Stefan Hinz, Carsten Pedersen
149 o This is the official guide to cover the passing of the two MySQL Certification examinations. It is valid till version 5.0 of the server, so while it misses all the features available in MySQL 5.1 and greater (including MariaDB 5.1 and greater), it provides a good basic understanding of MySQL for the end-user. ');
150 SELECT HEX(v0), HEX(v1), HEX(v64), HEX(v65000) FROM t1;
151 HEX(v0) HEX(v1) HEX(v64) HEX(v65000)
152
153 79 4F6E63652074686572652C20646F75626C6520636865636B207468617420616E2061727469636C6520646F65736E277420616C7265616479206578697374 486572652069732061206C697374206F66207265636F6D6D656E64656420626F6F6B73206F6E204D61726961444220616E64204D7953514C2E2057652776652070726F7669646564206C696E6B7320746F20416D617A6F6E2E636F6D206865726520666F7220636F6E76656E69656E63652C2062757420746865792063616E20626520666F756E64206174206D616E79206F7468657220626F6F6B73746F7265732C20626F7468206F6E6C696E6520616E64206F66662E0A0A2020496620796F752077616E7420746F206861766520796F7572206661766F72697465204D7953514C202F204D61726961444220626F6F6B206C697374656420686572652C20706C65617365206C65617665206120636F6D6D656E742E0A2020466F7220646576656C6F706572732077686F2077616E7420746F20636F6465206F6E204D617269614442206F72204D7953514C0A0A2020202020202A20556E6465727374616E64696E67204D7953514C20496E7465726E616C73206279205361736861205061636865762C20666F726D6572204D7953514C20646576656C6F706572206174204D7953514C2041422E0A2020202020202020202020206F205468697320697320746865206F6E6C7920626F6F6B207765206B6E6F772061626F75742074686174206465736372696265732074686520696E7465726E616C73206F66204D617269614442202F204D7953514C2E2041206D757374206861766520666F7220616E796F6E652077686F2077616E747320746F20756E6465727374616E6420616E6420646576656C6F70206F6E204D617269614442210A2020202020202020202020206F204E6F7420616C6C20746F706963732061726520636F766572656420616E6420736F6D652070617274732061726520736C696768746C79206F757464617465642C20627574207374696C6C20746865206265737420626F6F6B206F6E207468697320746F7069632E200A2020202020202A204D7953514C20352E3120506C7567696E20446576656C6F706D656E742062792053657267656920476F6C75626368696B20616E6420416E64726577204875746368696E67730A2020202020202020202020206F2041206D757374207265616420666F7220616E796F6E652077616E74696E6720746F207772697465206120706C7567696E20666F72204D6172696144422C207772697474656E20627920746865205365726765692077686F2064657369676E65642074686520706C7567696E20696E7465726661636520666F72204D7953514C20616E64204D61726961444221200A0A2020466F72204D617269614442202F204D7953514C20656E642075736572730A0A2020202020202A204D61726961444220437261736820436F757273652062792042656E20466F7274610A2020202020202020202020206F204669727374204D61726961444220626F6F6B210A2020202020202020202020206F20466F722070656F706C652077686F2077616E7420746F206C6561726E2053514C20616E642074686520626173696373206F66204D6172696144422E0A2020202020202020202020206F204E6F77207368697070696E672E20507572636861736520617420416D617A6F6E2E636F6D206F7220796F7572206661766F7269746520626F6F6B73656C6C65722E200A0A2020202020202A2053514C2D393920436F6D706C6574652C205265616C6C792062792050657465722047756C75747A616E20262054727564792050656C7A65722E0A2020202020202020202020206F2045766572797468696E6720796F752077616E74656420746F206B6E6F772061626F7574207468652053514C203939207374616E646172642E20457863656C6C656E74207265666572656E636520626F6F6B210A2020202020202020202020206F204672656520746F207265616420696E20746865204B6E6F776C656467656261736521200A0A2020202020202A204D7953514C20283474682045646974696F6E29206279205061756C204475426F69730A2020202020202020202020206F20546865202764656661756C742720626F6F6B20746F207265616420696620796F7520776F6E7420746F206C6561726E20746F20757365204D7953514C202F204D6172696144422E200A0A2020202020202A204D7953514C20436F6F6B626F6F6B206279205061756C204475426F69730A2020202020202020202020206F2041206C6F74206F66206578616D706C6573206F6620686F7720746F20757365204D7953514C2E204173207769746820616C6C206F66205061756C277320626F6F6B732C206974277320776F727468206974732077656967687420696E20676F6C6420616E64206576656E20656E6A6F7961626C652072656164696E6720666F7220737563682061202764727927207375626A6563742E200A0A2020202020202A204869676820506572666F726D616E6365204D7953514C2C205365636F6E642045646974696F6E2C204279204261726F6E20536368776172747A2C205065746572205A6169747365762C20566164696D20546B616368656E6B6F2C204A6572656D7920442E205A61776F646E792C2041726A656E204C656E747A2C20446572656B204A2E2042616C6C696E672C20657420616C2E0A2020202020202020202020206F20224869676820506572666F726D616E6365204D7953514C2069732074686520646566696E697469766520677569646520746F206275696C64696E6720666173742C2072656C6961626C652073797374656D732077697468204D7953514C2E205772697474656E206279206E6F74656420657870657274732077697468207965617273206F66207265616C2D776F726C6420657870657269656E6365206275696C64696E672076657279206C617267652073797374656D732C207468697320626F6F6B20636F7665727320657665727920617370656374206F66204D7953514C20706572666F726D616E636520696E2064657461696C2C20616E6420666F6375736573206F6E20726F627573746E6573732C2073656375726974792C20616E64206461746120696E746567726974792E204C6561726E20616476616E63656420746563686E697175657320696E20646570746820736F20796F752063616E206272696E67206F7574204D7953514C27732066756C6C20706F7765722E22202846726F6D2074686520626F6F6B206465736372697074696F6E206174204F275265696C6C7929200A0A2020202020202A204D7953514C2041646D696E20436F6F6B626F6F6B0A2020202020202020202020206F204120717569636B20737465702D62792D7374657020677569646520666F72204D7953514C20757365727320616E642064617461626173652061646D696E6973747261746F727320746F207461636B6C65207265616C2D776F726C64206368616C6C656E6765732077697468204D7953514C20636F6E66696775726174696F6E20616E642061646D696E697374726174696F6E200A0A2020202020202A204D7953514C20352E302043657274696669636174696F6E2053747564792047756964652C204279205061756C204475426F69732C2053746566616E2048696E7A2C204361727374656E20506564657273656E0A2020202020202020202020206F205468697320697320746865206F6666696369616C20677569646520746F20636F766572207468652070617373696E67206F66207468652074776F204D7953514C2043657274696669636174696F6E206578616D696E6174696F6E732E2049742069732076616C69642074696C6C2076657273696F6E20352E30206F6620746865207365727665722C20736F207768696C65206974206D697373657320616C6C2074686520666561747572657320617661696C61626C6520696E204D7953514C20352E3120616E6420677265617465722028696E636C7564696E67204D61726961444220352E3120616E642067726561746572292C2069742070726F7669646573206120676F6F6420626173696320756E6465727374616E64696E67206F66204D7953514C20666F722074686520656E642D757365722E20
154 INSERT INTO t1 (v0,v1,v64,v65000) VALUES ('y', 'yy', REPEAT('c',65), REPEAT('abcdefghi ',6501));
155 Warnings:
156 Warning 1265 Data truncated for column 'v0' at row 1
157 Warning 1265 Data truncated for column 'v1' at row 1
158 Warning 1265 Data truncated for column 'v64' at row 1
159 Warning 1265 Data truncated for column 'v65000' at row 1
160 INSERT INTO t1 (v0,v1,v64,v65000) SELECT v65000, v65000, v65000, CONCAT(v65000,v1) FROM t1;
161 Warnings:
162 Warning 1265 Data truncated for column 'v0' at row 2
163 Warning 1265 Data truncated for column 'v1' at row 2
164 Warning 1265 Data truncated for column 'v64' at row 2
165 Warning 1265 Data truncated for column 'v0' at row 3
166 Warning 1265 Data truncated for column 'v1' at row 3
167 Warning 1265 Data truncated for column 'v64' at row 3
168 Warning 1265 Data truncated for column 'v65000' at row 3
169 SELECT HEX(v0), HEX(v1), HEX(v64), LENGTH(HEX(v65000)) FROM t1;
170 HEX(v0) HEX(v1) HEX(v64) LENGTH(HEX(v65000))
171 0
172 0
173 48 486572652069732061206C697374206F66207265636F6D6D656E64656420626F6F6B73206F6E204D61726961444220616E64204D7953514C2E20576527766520 5932
174 61 61626364656667686920616263646566676869206162636465666768692061626364656667686920616263646566676869206162636465666768692061626364 130000
175 79 4F6E63652074686572652C20646F75626C6520636865636B207468617420616E2061727469636C6520646F65736E277420616C7265616479206578697374 5930
176 79 63636363636363636363636363636363636363636363636363636363636363636363636363636363636363636363636363636363636363636363636363636363 130000
177 ALTER TABLE t1 ADD COLUMN v65536 VARBINARY(65536) <CUSTOM_COL_OPTIONS> NOT NULL;
178 Warnings:
179 Note 1246 Converting column 'v65536' from VARBINARY to BLOB
180 SHOW COLUMNS IN t1;
181 Field Type Null Key Default Extra
182 v0 varbinary(0) # # #
183 v1 varbinary(1) # # #
184 v64 varbinary(64) # # #
185 v65000 varbinary(65000) # # #
186 v65536 mediumblob # # #
187 DROP TABLE t1, t2;
188 DROP TABLE IF EXISTS t1;
189 CREATE TABLE t1 (c VARBINARY(64) <CUSTOM_COL_OPTIONS> NOT NULL) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>;
190 SHOW COLUMNS IN t1;
191 Field Type Null Key Default Extra
192 c varbinary(64) NO NULL
193 INSERT INTO t1 (c) VALUES (NULL);
194 ERROR 23000: Column 'c' cannot be null
195 DROP TABLE t1;
196 CREATE TABLE t1 (c VARBINARY(64) <CUSTOM_COL_OPTIONS> NOT NULL,
197 c2 VARBINARY(64) <CUSTOM_COL_OPTIONS> NOT NULL DEFAULT 'test'
198 ) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>;
199 SHOW COLUMNS IN t1;
200 Field Type Null Key Default Extra
201 c varbinary(64) NO NULL
202 c2 varbinary(64) NO test
203 ALTER TABLE t1 ADD COLUMN err VARBINARY(64) <CUSTOM_COL_OPTIONS> NOT NULL DEFAULT NULL;
204 ERROR 42000: Invalid default value for 'err'
205 INSERT INTO t1 (c) VALUES (NULL);
206 ERROR 23000: Column 'c' cannot be null
207 SELECT HEX(c), HEX(c2) FROM t1;
208 HEX(c) HEX(c2)
209 INSERT INTO t1 (c2) VALUES (NULL);
210 ERROR 23000: Column 'c2' cannot be null
211 INSERT INTO t1 (c) VALUES ('test');
212 SELECT COUNT(c), COUNT(c2) FROM t1;
213 COUNT(c) COUNT(c2)
214 1 1
215 DROP TABLE t1;
216 #
217 # BIT columns
218 #
219 DROP TABLE IF EXISTS t1;
220 CREATE TABLE t1 (a BIT <CUSTOM_COL_OPTIONS> NOT NULL,
221 b BIT(20) <CUSTOM_COL_OPTIONS> NOT NULL,
222 c BIT(64) <CUSTOM_COL_OPTIONS> NOT NULL,
223 d BIT(1) <CUSTOM_COL_OPTIONS> NOT NULL
224 ) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>;
225 SHOW COLUMNS IN t1;
226 Field Type Null Key Default Extra
227 a bit(1) # # #
228 b bit(20) # # #
229 c bit(64) # # #
230 d bit(1) # # #
231 ALTER TABLE t1 DROP COLUMN d;
232 ALTER TABLE t1 ADD COLUMN d BIT(0) <CUSTOM_COL_OPTIONS> NOT NULL;
233 SHOW COLUMNS IN t1;
234 Field Type Null Key Default Extra
235 a bit(1) # # #
236 b bit(20) # # #
237 c bit(64) # # #
238 d bit(1) # # #
239 INSERT INTO t1 (a,b,c,d) VALUES (0,POW(2,20)-1,b'1111111111111111111111111111111111111111111111111111111111111111',1);
240 SELECT BIN(a), HEX(b), c+0 FROM t1 WHERE d>0;
241 BIN(a) HEX(b) c+0
242 0 FFFFF 18446744073709551615
243 INSERT INTO t1 (a,b,c,d) VALUES (1,0,-1,0);
244 SELECT a+0, b+0, c+0 FROM t1 WHERE d<100;
245 a+0 b+0 c+0
246 0 1048575 18446744073709551615
247 1 0 18446744073709551615
248 INSERT INTO t1 (a,b,c,d) VALUES (b'1', 'f', 0xFF, 0x0);
249 SELECT a+0, b+0, c+0 FROM t1 WHERE d IN (0, 2);
250 a+0 b+0 c+0
251 1 0 18446744073709551615
252 1 102 255
253 INSERT INTO t1 (a,b,c,d) VALUES (0x10,0,0,1);
254 Warnings:
255 Warning 1264 Out of range value for column 'a' at row 1
256 SELECT a,b,c,d FROM t1;
257 a b c d
258 INSERT INTO t1 (a,b,c,d) VALUES (0x01,0,0x10000000000000000,0);
259 Warnings:
260 Warning 1264 Out of range value for column 'c' at row 1
261 SELECT a,b,c,d FROM t1;
262 a b c d
263 DROP TABLE t1;
264 CREATE TABLE t1 (a BIT(65) <CUSTOM_COL_OPTIONS> NOT NULL) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>;
265 ERROR 42000: Display width out of range for 'a' (max = 64)
266 DROP TABLE IF EXISTS t1;
267 CREATE TABLE t1 (c BIT <CUSTOM_COL_OPTIONS> NOT NULL) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>;
268 SHOW COLUMNS IN t1;
269 Field Type Null Key Default Extra
270 c bit(1) NO NULL
271 INSERT INTO t1 (c) VALUES (NULL);
272 ERROR 23000: Column 'c' cannot be null
273 DROP TABLE t1;
274 CREATE TABLE t1 (c BIT <CUSTOM_COL_OPTIONS> NOT NULL,
275 c2 BIT <CUSTOM_COL_OPTIONS> NOT NULL DEFAULT 1
276 ) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>;
277 SHOW COLUMNS IN t1;
278 Field Type Null Key Default Extra
279 c bit(1) NO NULL
280 c2 bit(1) NO b'1'
281 ALTER TABLE t1 ADD COLUMN err BIT <CUSTOM_COL_OPTIONS> NOT NULL DEFAULT NULL;
282 ERROR 42000: Invalid default value for 'err'
283 INSERT INTO t1 (c) VALUES (NULL);
284 ERROR 23000: Column 'c' cannot be null
285 SELECT HEX(c), HEX(c2) FROM t1;
286 HEX(c) HEX(c2)
287 INSERT INTO t1 (c2) VALUES (NULL);
288 ERROR 23000: Column 'c2' cannot be null
289 INSERT INTO t1 (c) VALUES (1);
290 SELECT COUNT(c), COUNT(c2) FROM t1;
291 COUNT(c) COUNT(c2)
292 1 1
293 DROP TABLE t1;
294 #
295 # BLOB columns
296 #
297 DROP TABLE IF EXISTS t1;
298 CREATE TABLE t1 (b BLOB <CUSTOM_COL_OPTIONS> NOT NULL,
299 b0 BLOB(0) <CUSTOM_COL_OPTIONS> NOT NULL,
300 b1 BLOB(1) <CUSTOM_COL_OPTIONS> NOT NULL,
301 b300 BLOB(300) <CUSTOM_COL_OPTIONS> NOT NULL,
302 bm BLOB(65535) <CUSTOM_COL_OPTIONS> NOT NULL,
303 b70k BLOB(70000) <CUSTOM_COL_OPTIONS> NOT NULL,
304 b17m BLOB(17000000) <CUSTOM_COL_OPTIONS> NOT NULL,
305 t TINYBLOB <CUSTOM_COL_OPTIONS> NOT NULL,
306 m MEDIUMBLOB <CUSTOM_COL_OPTIONS> NOT NULL,
307 l LONGBLOB <CUSTOM_COL_OPTIONS> NOT NULL
308 ) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>;
309 SHOW COLUMNS IN t1;
310 Field Type Null Key Default Extra
311 b blob # # #
312 b0 blob # # #
313 b1 tinyblob # # #
314 b300 blob # # #
315 bm blob # # #
316 b70k mediumblob # # #
317 b17m longblob # # #
318 t tinyblob # # #
319 m mediumblob # # #
320 l longblob # # #
321 INSERT INTO t1 (b,b0,b1,b300,bm,b70k,b17m,t,m,l) VALUES
322 ('','','','','','','','','',''),
323 ('a','b','c','d','e','f','g','h','i','j'),
324 ('test1','test2','test3','test4','test5','test6','test7','test8','test9','test10'),
325 ( REPEAT('a',65535), REPEAT('b',65535), REPEAT('c',255), REPEAT('d',65535), REPEAT('e',65535), REPEAT('f',1048576), HEX(REPEAT('g',1048576)), REPEAT('h',255), REPEAT('i',1048576), HEX(REPEAT('j',1048576)) );
326 SELECT LENGTH(b), LENGTH(b0), LENGTH(b1), LENGTH(b300), LENGTH(bm), LENGTH(b70k), LENGTH(b17m), LENGTH(t), LENGTH(m), LENGTH(l) FROM t1;
327 LENGTH(b) LENGTH(b0) LENGTH(b1) LENGTH(b300) LENGTH(bm) LENGTH(b70k) LENGTH(b17m) LENGTH(t) LENGTH(m) LENGTH(l)
328 0 0 0 0 0 0 0 0 0 0
329 1 1 1 1 1 1 1 1 1 1
330 5 5 5 5 5 5 5 5 5 6
331 65535 65535 255 65535 65535 1048576 2097152 255 1048576 2097152
332 INSERT INTO t1 (b,b0,b1,b300,bm,b70k,b17m,t,m,l) VALUES
333 ( REPEAT('a',65536), REPEAT('b',65536), REPEAT('c',256), REPEAT('d',65536), REPEAT('e',65536), REPEAT('f',1048576), REPEAT('g',1048576), REPEAT('h',256), REPEAT('i',1048576), REPEAT('j',1048576) );
334 Warnings:
335 Warning 1265 Data truncated for column 'b' at row 1
336 Warning 1265 Data truncated for column 'b0' at row 1
337 Warning 1265 Data truncated for column 'b1' at row 1
338 Warning 1265 Data truncated for column 'b300' at row 1
339 Warning 1265 Data truncated for column 'bm' at row 1
340 Warning 1265 Data truncated for column 't' at row 1
341 SELECT LENGTH(b), LENGTH(b0), LENGTH(b1), LENGTH(b300), LENGTH(bm), LENGTH(b70k), LENGTH(b17m), LENGTH(t), LENGTH(m), LENGTH(l) FROM t1;
342 LENGTH(b) LENGTH(b0) LENGTH(b1) LENGTH(b300) LENGTH(bm) LENGTH(b70k) LENGTH(b17m) LENGTH(t) LENGTH(m) LENGTH(l)
343 0 0 0 0 0 0 0 0 0 0
344 1 1 1 1 1 1 1 1 1 1
345 5 5 5 5 5 5 5 5 5 6
346 65535 65535 255 65535 65535 1048576 1048576 255 1048576 1048576
347 65535 65535 255 65535 65535 1048576 2097152 255 1048576 2097152
348 ALTER TABLE t1 ADD COLUMN bbb BLOB(4294967296);
349 ERROR 42000: Display width out of range for 'bbb' (max = 4294967295)
350 DROP TABLE t1;
351 DROP TABLE IF EXISTS t1;
352 CREATE TABLE t1 (c BLOB <CUSTOM_COL_OPTIONS> NOT NULL) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>;
353 SHOW COLUMNS IN t1;
354 Field Type Null Key Default Extra
355 c blob NO NULL
356 INSERT INTO t1 (c) VALUES (NULL);
357 ERROR 23000: Column 'c' cannot be null
358 DROP TABLE t1;
359 DROP TABLE IF EXISTS t1;
360 CREATE TABLE t1 (c TINYBLOB <CUSTOM_COL_OPTIONS> NOT NULL) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>;
361 SHOW COLUMNS IN t1;
362 Field Type Null Key Default Extra
363 c tinyblob NO NULL
364 INSERT INTO t1 (c) VALUES (NULL);
365 ERROR 23000: Column 'c' cannot be null
366 DROP TABLE t1;
367 DROP TABLE IF EXISTS t1;
368 CREATE TABLE t1 (c MEDIUMBLOB <CUSTOM_COL_OPTIONS> NOT NULL) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>;
369 SHOW COLUMNS IN t1;
370 Field Type Null Key Default Extra
371 c mediumblob NO NULL
372 INSERT INTO t1 (c) VALUES (NULL);
373 ERROR 23000: Column 'c' cannot be null
374 DROP TABLE t1;
375 DROP TABLE IF EXISTS t1;
376 CREATE TABLE t1 (c LONGBLOB <CUSTOM_COL_OPTIONS> NOT NULL) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>;
377 SHOW COLUMNS IN t1;
378 Field Type Null Key Default Extra
379 c longblob NO NULL
380 INSERT INTO t1 (c) VALUES (NULL);
381 ERROR 23000: Column 'c' cannot be null
382 DROP TABLE t1;
383 #
384 # BOOL columns
385 #
386 DROP TABLE IF EXISTS t1;
387 CREATE TABLE t1 (b1 BOOL <CUSTOM_COL_OPTIONS> NOT NULL,
388 b2 BOOLEAN <CUSTOM_COL_OPTIONS> NOT NULL
389 ) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>;
390 SHOW COLUMNS IN t1;
391 Field Type Null Key Default Extra
392 b1 tinyint(1) # # #
393 b2 tinyint(1) # # #
394 INSERT INTO t1 (b1,b2) VALUES (1,TRUE);
395 SELECT b1,b2 FROM t1;
396 b1 b2
397 1 1
398 INSERT INTO t1 (b1,b2) VALUES (FALSE,0);
399 SELECT b1,b2 FROM t1;
400 b1 b2
401 0 0
402 1 1
403 INSERT INTO t1 (b1,b2) VALUES (2,3);
404 SELECT b1,b2 FROM t1;
405 b1 b2
406 0 0
407 1 1
408 2 3
409 INSERT INTO t1 (b1,b2) VALUES (-1,-2);
410 SELECT b1,b2 FROM t1;
411 b1 b2
412 -1 -2
413 0 0
414 1 1
415 2 3
416 SELECT IF(b1,'true','false') AS a, IF(b2,'true','false') AS b FROM t1;
417 a b
418 false false
419 true true
420 true true
421 true true
422 SELECT b1,b2 FROM t1 WHERE b1 = TRUE;
423 b1 b2
424 1 1
425 SELECT b1,b2 FROM t1 WHERE b2 = FALSE;
426 b1 b2
427 0 0
428 INSERT INTO t1 (b1,b2) VALUES ('a','b');
429 Warnings:
430 Warning 1366 Incorrect integer value: 'a' for column 'b1' at row 1
431 Warning 1366 Incorrect integer value: 'b' for column 'b2' at row 1
432 SELECT b1,b2 FROM t1;
433 b1 b2
434 -1 -2
435 0 0
436 0 0
437 1 1
438 2 3
439 INSERT INTO t1 (b1,b2) VALUES (128,-129);
440 Warnings:
441 Warning 1264 Out of range value for column 'b1' at row 1
442 Warning 1264 Out of range value for column 'b2' at row 1
443 SELECT b1,b2 FROM t1;
444 b1 b2
445 -1 -2
446 0 0
447 0 0
448 1 1
449 127 -128
450 2 3
451 ALTER TABLE t1 ADD COLUMN b3 BOOLEAN UNSIGNED;
452 ERROR 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 'UNSIGNED' at line 1
453 ALTER TABLE t1 ADD COLUMN b3 BOOL ZEROFILL;
454 ERROR 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 'ZEROFILL' at line 1
455 DROP TABLE t1;
456 DROP TABLE IF EXISTS t1;
457 CREATE TABLE t1 (c BOOL <CUSTOM_COL_OPTIONS> NOT NULL) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>;
458 SHOW COLUMNS IN t1;
459 Field Type Null Key Default Extra
460 c tinyint(1) NO NULL
461 INSERT INTO t1 (c) VALUES (NULL);
462 ERROR 23000: Column 'c' cannot be null
463 DROP TABLE t1;
464 CREATE TABLE t1 (c BOOL <CUSTOM_COL_OPTIONS> NOT NULL,
465 c2 BOOL <CUSTOM_COL_OPTIONS> NOT NULL DEFAULT '0'
466 ) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>;
467 SHOW COLUMNS IN t1;
468 Field Type Null Key Default Extra
469 c tinyint(1) NO NULL
470 c2 tinyint(1) NO 0
471 ALTER TABLE t1 ADD COLUMN err BOOL <CUSTOM_COL_OPTIONS> NOT NULL DEFAULT NULL;
472 ERROR 42000: Invalid default value for 'err'
473 INSERT INTO t1 (c) VALUES (NULL);
474 ERROR 23000: Column 'c' cannot be null
475 SELECT HEX(c), HEX(c2) FROM t1;
476 HEX(c) HEX(c2)
477 INSERT INTO t1 (c2) VALUES (NULL);
478 ERROR 23000: Column 'c2' cannot be null
479 INSERT INTO t1 (c) VALUES ('0');
480 SELECT COUNT(c), COUNT(c2) FROM t1;
481 COUNT(c) COUNT(c2)
482 1 1
483 DROP TABLE t1;
484 #
485 # CHAR columns
486 #
487 DROP TABLE IF EXISTS t1;
488 CREATE TABLE t1 (c CHAR <CUSTOM_COL_OPTIONS> NOT NULL,
489 c0 CHAR(0) <CUSTOM_COL_OPTIONS> NOT NULL,
490 c1 CHAR(1) <CUSTOM_COL_OPTIONS> NOT NULL,
491 c20 CHAR(20) <CUSTOM_COL_OPTIONS> NOT NULL,
492 c255 CHAR(255) <CUSTOM_COL_OPTIONS> NOT NULL
493 ) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>;
494 SHOW COLUMNS IN t1;
495 Field Type Null Key Default Extra
496 c char(1) # # #
497 c0 char(0) # # #
498 c1 char(1) # # #
499 c20 char(20) # # #
500 c255 char(255) # # #
501 INSERT INTO t1 (c,c0,c1,c20,c255) VALUES ('','','','','');
502 INSERT INTO t1 (c,c0,c1,c20,c255) VALUES ('a','','b','abcdefghi klmnopqrst', 'Creating an article for the Knowledgebase is similar to asking questions. First, navigate to the category where you feel the article should be. Once there, double check that an article doesn\'t already exist which would work.');
503 SELECT c,c0,c1,c20,c255 FROM t1;
504 c c0 c1 c20 c255
505
506 a b abcdefghi klmnopqrst Creating an article for the Knowledgebase is similar to asking questions. First, navigate to the category where you feel the article should be. Once there, double check that an article doesn't already exist which would work.
507 INSERT INTO t1 (c,c0,c1,c20,c255) VALUES ('abc', 'a', 'abc', REPEAT('a',21), REPEAT('x',256));
508 Warnings:
509 Warning 1265 Data truncated for column 'c' at row 1
510 Warning 1265 Data truncated for column 'c0' at row 1
511 Warning 1265 Data truncated for column 'c1' at row 1
512 Warning 1265 Data truncated for column 'c20' at row 1
513 Warning 1265 Data truncated for column 'c255' at row 1
514 INSERT INTO t1 (c,c0,c1,c20,c255) SELECT c255, c255, c255, c255, CONCAT(c255,c1) FROM t1;
515 Warnings:
516 Warning 1265 Data truncated for column 'c' at row 2
517 Warning 1265 Data truncated for column 'c0' at row 2
518 Warning 1265 Data truncated for column 'c1' at row 2
519 Warning 1265 Data truncated for column 'c20' at row 2
520 Warning 1265 Data truncated for column 'c' at row 3
521 Warning 1265 Data truncated for column 'c0' at row 3
522 Warning 1265 Data truncated for column 'c1' at row 3
523 Warning 1265 Data truncated for column 'c20' at row 3
524 Warning 1265 Data truncated for column 'c255' at row 3
525 SELECT c,c0,c1,c20,c255 FROM t1;
526 c c0 c1 c20 c255
527
528
529 C C Creating an article Creating an article for the Knowledgebase is similar to asking questions. First, navigate to the category where you feel the article should be. Once there, double check that an article doesn't already exist which would work.b
530 a a aaaaaaaaaaaaaaaaaaaa xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
531 a b abcdefghi klmnopqrst Creating an article for the Knowledgebase is similar to asking questions. First, navigate to the category where you feel the article should be. Once there, double check that an article doesn't already exist which would work.
532 x x xxxxxxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
533 SELECT DISTINCT c20, REPEAT('a',LENGTH(c20)), COUNT(*) FROM t1 GROUP BY c1, c20;
534 c20 REPEAT('a',LENGTH(c20)) COUNT(*)
535 2
536 Creating an article aaaaaaaaaaaaaaaaaaa 1
537 aaaaaaaaaaaaaaaaaaaa aaaaaaaaaaaaaaaaaaaa 1
538 abcdefghi klmnopqrst aaaaaaaaaaaaaaaaaaaa 1
539 xxxxxxxxxxxxxxxxxxxx aaaaaaaaaaaaaaaaaaaa 1
540 ALTER TABLE t1 ADD COLUMN c257 CHAR(257) <CUSTOM_COL_OPTIONS> NOT NULL;
541 ERROR 42000: Column length too big for column 'c257' (max = 255); use BLOB or TEXT instead
542 DROP TABLE t1;
543 DROP TABLE IF EXISTS t1;
544 CREATE TABLE t1 (c CHAR <CUSTOM_COL_OPTIONS> NOT NULL) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>;
545 SHOW COLUMNS IN t1;
546 Field Type Null Key Default Extra
547 c char(1) NO NULL
548 INSERT INTO t1 (c) VALUES (NULL);
549 ERROR 23000: Column 'c' cannot be null
550 DROP TABLE t1;
551 CREATE TABLE t1 (c CHAR <CUSTOM_COL_OPTIONS> NOT NULL,
552 c2 CHAR <CUSTOM_COL_OPTIONS> NOT NULL DEFAULT '_'
553 ) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>;
554 SHOW COLUMNS IN t1;
555 Field Type Null Key Default Extra
556 c char(1) NO NULL
557 c2 char(1) NO _
558 ALTER TABLE t1 ADD COLUMN err CHAR <CUSTOM_COL_OPTIONS> NOT NULL DEFAULT NULL;
559 ERROR 42000: Invalid default value for 'err'
560 INSERT INTO t1 (c) VALUES (NULL);
561 ERROR 23000: Column 'c' cannot be null
562 SELECT HEX(c), HEX(c2) FROM t1;
563 HEX(c) HEX(c2)
564 INSERT INTO t1 (c2) VALUES (NULL);
565 ERROR 23000: Column 'c2' cannot be null
566 INSERT INTO t1 (c) VALUES ('_');
567 SELECT COUNT(c), COUNT(c2) FROM t1;
568 COUNT(c) COUNT(c2)
569 1 1
570 DROP TABLE t1;
571 #
572 # VARCHAR columns
573 #
574 DROP TABLE IF EXISTS t1, t2;
575 CREATE TABLE t1 (v0 VARCHAR(0) <CUSTOM_COL_OPTIONS> NOT NULL,
576 v1 VARCHAR(1) <CUSTOM_COL_OPTIONS> NOT NULL,
577 v64 VARCHAR(64) <CUSTOM_COL_OPTIONS> NOT NULL,
578 v65000 VARCHAR(65000) <CUSTOM_COL_OPTIONS> NOT NULL
579 ) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>;
580 SHOW COLUMNS IN t1;
581 Field Type Null Key Default Extra
582 v0 varchar(0) # # #
583 v1 varchar(1) # # #
584 v64 varchar(64) # # #
585 v65000 varchar(65000) # # #
586 CREATE TABLE t2 (v VARCHAR(65532) <CUSTOM_COL_OPTIONS> NOT NULL) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>;
587 SHOW COLUMNS IN t2;
588 Field Type Null Key Default Extra
589 v varchar(65532) # # #
590 INSERT INTO t1 (v0,v1,v64,v65000) VALUES ('','','','');
591 INSERT INTO t1 (v0,v1,v64,v65000) VALUES ('','y','Once there, double check that an article doesn\'t already exist','Here is a list of recommended books on MariaDB and MySQL. We\'ve provided links to Amazon.com here for convenience, but they can be found at many other bookstores, both online and off.
592
593 If you want to have your favorite MySQL / MariaDB book listed here, please leave a comment.
594 For developers who want to code on MariaDB or MySQL
595
596 * Understanding MySQL Internals by Sasha Pachev, former MySQL developer at MySQL AB.
597 o This is the only book we know about that describes the internals of MariaDB / MySQL. A must have for anyone who wants to understand and develop on MariaDB!
598 o Not all topics are covered and some parts are slightly outdated, but still the best book on this topic.
599 * MySQL 5.1 Plugin Development by Sergei Golubchik and Andrew Hutchings
600 o A must read for anyone wanting to write a plugin for MariaDB, written by the Sergei who designed the plugin interface for MySQL and MariaDB!
601
602 For MariaDB / MySQL end users
603
604 * MariaDB Crash Course by Ben Forta
605 o First MariaDB book!
606 o For people who want to learn SQL and the basics of MariaDB.
607 o Now shipping. Purchase at Amazon.com or your favorite bookseller.
608
609 * SQL-99 Complete, Really by Peter Gulutzan & Trudy Pelzer.
610 o Everything you wanted to know about the SQL 99 standard. Excellent reference book!
611 o Free to read in the Knowledgebase!
612
613 * MySQL (4th Edition) by Paul DuBois
614 o The \'default\' book to read if you wont to learn to use MySQL / MariaDB.
615
616 * MySQL Cookbook by Paul DuBois
617 o A lot of examples of how to use MySQL. As with all of Paul\'s books, it\'s worth its weight in gold and even enjoyable reading for such a \'dry\' subject.
618
619 * High Performance MySQL, Second Edition, By Baron Schwartz, Peter Zaitsev, Vadim Tkachenko, Jeremy D. Zawodny, Arjen Lentz, Derek J. Balling, et al.
620 o \"High Performance MySQL is the definitive guide to building fast, reliable systems with MySQL. Written by noted experts with years of real-world experience building very large systems, this book covers every aspect of MySQL performance in detail, and focuses on robustness, security, and data integrity. Learn advanced techniques in depth so you can bring out MySQL\'s full power.\" (From the book description at O\'Reilly)
621
622 * MySQL Admin Cookbook
623 o A quick step-by-step guide for MySQL users and database administrators to tackle real-world challenges with MySQL configuration and administration
624
625 * MySQL 5.0 Certification Study Guide, By Paul DuBois, Stefan Hinz, Carsten Pedersen
626 o This is the official guide to cover the passing of the two MySQL Certification examinations. It is valid till version 5.0 of the server, so while it misses all the features available in MySQL 5.1 and greater (including MariaDB 5.1 and greater), it provides a good basic understanding of MySQL for the end-user. ');
627 SELECT v0,v1,v64,v65000 FROM t1;
628 v0 v1 v64 v65000
629
630
631
632
633
634
635
636
637
638
639
640 y Once there, double check that an article doesn't already exist Here is a list of recommended books on MariaDB and MySQL. We've provided links to Amazon.com here for convenience, but they can be found at many other bookstores, both online and off.
641 o "High Performance MySQL is the definitive guide to building fast, reliable systems with MySQL. Written by noted experts with years of real-world experience building very large systems, this book covers every aspect of MySQL performance in detail, and focuses on robustness, security, and data integrity. Learn advanced techniques in depth so you can bring out MySQL's full power." (From the book description at O'Reilly)
642 o A lot of examples of how to use MySQL. As with all of Paul's books, it's worth its weight in gold and even enjoyable reading for such a 'dry' subject.
643 o A must read for anyone wanting to write a plugin for MariaDB, written by the Sergei who designed the plugin interface for MySQL and MariaDB!
644 o A quick step-by-step guide for MySQL users and database administrators to tackle real-world challenges with MySQL configuration and administration
645 o Everything you wanted to know about the SQL 99 standard. Excellent reference book!
646 o First MariaDB book!
647 o For people who want to learn SQL and the basics of MariaDB.
648 o Free to read in the Knowledgebase!
649 o Not all topics are covered and some parts are slightly outdated, but still the best book on this topic.
650 o Now shipping. Purchase at Amazon.com or your favorite bookseller.
651 o The 'default' book to read if you wont to learn to use MySQL / MariaDB.
652 o This is the official guide to cover the passing of the two MySQL Certification examinations. It is valid till version 5.0 of the server, so while it misses all the features available in MySQL 5.1 and greater (including MariaDB 5.1 and greater), it provides a good basic understanding of MySQL for the end-user.
653 o This is the only book we know about that describes the internals of MariaDB / MySQL. A must have for anyone who wants to understand and develop on MariaDB!
654 * High Performance MySQL, Second Edition, By Baron Schwartz, Peter Zaitsev, Vadim Tkachenko, Jeremy D. Zawodny, Arjen Lentz, Derek J. Balling, et al.
655 * MariaDB Crash Course by Ben Forta
656 * MySQL (4th Edition) by Paul DuBois
657 * MySQL 5.0 Certification Study Guide, By Paul DuBois, Stefan Hinz, Carsten Pedersen
658 * MySQL 5.1 Plugin Development by Sergei Golubchik and Andrew Hutchings
659 * MySQL Admin Cookbook
660 * MySQL Cookbook by Paul DuBois
661 * SQL-99 Complete, Really by Peter Gulutzan & Trudy Pelzer.
662 * Understanding MySQL Internals by Sasha Pachev, former MySQL developer at MySQL AB.
663 For MariaDB / MySQL end users
664 For developers who want to code on MariaDB or MySQL
665 If you want to have your favorite MySQL / MariaDB book listed here, please leave a comment.
666 INSERT INTO t1 (v0,v1,v64,v65000) VALUES ('y', 'yy', REPEAT('c',65), REPEAT('abcdefghi ',6501));
667 Warnings:
668 Warning 1265 Data truncated for column 'v0' at row 1
669 Warning 1265 Data truncated for column 'v1' at row 1
670 Warning 1265 Data truncated for column 'v64' at row 1
671 Warning 1265 Data truncated for column 'v65000' at row 1
672 INSERT INTO t1 (v0,v1,v64,v65000) SELECT v65000, v65000, v65000, CONCAT(v65000,v1) FROM t1;
673 Warnings:
674 Warning 1265 Data truncated for column 'v0' at row 2
675 Warning 1265 Data truncated for column 'v1' at row 2
676 Warning 1265 Data truncated for column 'v64' at row 2
677 Warning 1265 Data truncated for column 'v0' at row 3
678 Warning 1265 Data truncated for column 'v1' at row 3
679 Warning 1265 Data truncated for column 'v64' at row 3
680 Warning 1265 Data truncated for column 'v65000' at row 3
681 SELECT v0, v1, v64, LENGTH(v65000) FROM t1;
682 v0 v1 v64 LENGTH(v65000)
683 0
684 0
685 H Here is a list of recommended books on MariaDB and MySQL. We've 2966
686 a abcdefghi abcdefghi abcdefghi abcdefghi abcdefghi abcdefghi abcd 65000
687 y Once there, double check that an article doesn't already exist 2965
688 y cccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccc 65000
689 ALTER TABLE t1 ADD COLUMN v65536 VARCHAR(65536) <CUSTOM_COL_OPTIONS> NOT NULL;
690 Warnings:
691 Note 1246 Converting column 'v65536' from VARCHAR to TEXT
692 SHOW COLUMNS IN t1;
693 Field Type Null Key Default Extra
694 v0 varchar(0) # # #
695 v1 varchar(1) # # #
696 v64 varchar(64) # # #
697 v65000 varchar(65000) # # #
698 v65536 mediumtext # # #
699 DROP TABLE t1, t2;
700 DROP TABLE IF EXISTS t1;
701 CREATE TABLE t1 (c VARCHAR(64) <CUSTOM_COL_OPTIONS> NOT NULL) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>;
702 SHOW COLUMNS IN t1;
703 Field Type Null Key Default Extra
704 c varchar(64) NO NULL
705 INSERT INTO t1 (c) VALUES (NULL);
706 ERROR 23000: Column 'c' cannot be null
707 DROP TABLE t1;
708 CREATE TABLE t1 (c VARCHAR(64) <CUSTOM_COL_OPTIONS> NOT NULL,
709 c2 VARCHAR(64) <CUSTOM_COL_OPTIONS> NOT NULL DEFAULT 'test default'
710 ) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>;
711 SHOW COLUMNS IN t1;
712 Field Type Null Key Default Extra
713 c varchar(64) NO NULL
714 c2 varchar(64) NO test default
715 ALTER TABLE t1 ADD COLUMN err VARCHAR(64) <CUSTOM_COL_OPTIONS> NOT NULL DEFAULT NULL;
716 ERROR 42000: Invalid default value for 'err'
717 INSERT INTO t1 (c) VALUES (NULL);
718 ERROR 23000: Column 'c' cannot be null
719 SELECT HEX(c), HEX(c2) FROM t1;
720 HEX(c) HEX(c2)
721 INSERT INTO t1 (c2) VALUES (NULL);
722 ERROR 23000: Column 'c2' cannot be null
723 INSERT INTO t1 (c) VALUES ('test default');
724 SELECT COUNT(c), COUNT(c2) FROM t1;
725 COUNT(c) COUNT(c2)
726 1 1
727 DROP TABLE t1;
728 #
729 # date and time columns
730 #
731 DROP TABLE IF EXISTS t1;
732 CREATE TABLE t1 (d DATE <CUSTOM_COL_OPTIONS> NOT NULL,
733 dt DATETIME <CUSTOM_COL_OPTIONS> NOT NULL,
734 ts TIMESTAMP <CUSTOM_COL_OPTIONS> NOT NULL,
735 t TIME <CUSTOM_COL_OPTIONS> NOT NULL,
736 y YEAR <CUSTOM_COL_OPTIONS> NOT NULL,
737 y4 YEAR(4) <CUSTOM_COL_OPTIONS> NOT NULL,
738 y2 YEAR(2) <CUSTOM_COL_OPTIONS> NOT NULL
739 ) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>;
740 Warnings:
741 Note 1287 'YEAR(2)' is deprecated and will be removed in a future release. Please use YEAR(4) instead
742 SHOW COLUMNS IN t1;
743 Field Type Null Key Default Extra
744 d date # # #
745 dt datetime # # #
746 ts timestamp # # # on update CURRENT_TIMESTAMP
747 t time # # #
748 y year(4) # # #
749 y4 year(4) # # #
750 y2 year(2) # # #
751 SET @tm = '2012-04-09 05:27:00';
752 INSERT INTO t1 (d,dt,ts,t,y,y4,y2) VALUES
753 ('1000-01-01', '1000-01-01 00:00:00', FROM_UNIXTIME(1), '-838:59:59', '1901', '1901', '00'),
754 ('9999-12-31', '9999-12-31 23:59:59', FROM_UNIXTIME(2147483647), '838:59:59', '2155', '2155', '99'),
755 ('0000-00-00', '0000-00-00 00:00:00', '0000-00-00 00:00:00', '00:00:00', '0', '0', '0'),
756 (DATE(@tm),@tm,TIMESTAMP(@tm),TIME(@tm),YEAR(@tm),YEAR(@tm),YEAR(@tm));
757 SELECT d,dt,ts,t,y,y4,y2 FROM t1;
758 d dt ts t y y4 y2
759 0000-00-00 0000-00-00 00:00:00 0000-00-00 00:00:00 00:00:00 2000 2000 00
760 1000-01-01 1000-01-01 00:00:00 1970-01-01 00:00:01 -838:59:59 1901 1901 00
761 2012-04-09 2012-04-09 05:27:00 2012-04-09 05:27:00 05:27:00 2012 2012 12
762 9999-12-31 9999-12-31 23:59:59 2038-01-19 03:14:07 838:59:59 2155 2155 99
763 INSERT INTO t1 (d,dt,ts,t,y,y4,y2) VALUES
764 ('999-13-32', '999-11-31 00:00:00', '0', '-839:00:00', '1900', '1900', '-1' );
765 Warnings:
766 Warning 1265 Data truncated for column 'd' at row 1
767 Warning 1265 Data truncated for column 'dt' at row 1
768 Warning 1265 Data truncated for column 'ts' at row 1
769 Warning 1264 Out of range value for column 't' at row 1
770 Warning 1264 Out of range value for column 'y' at row 1
771 Warning 1264 Out of range value for column 'y4' at row 1
772 Warning 1264 Out of range value for column 'y2' at row 1
773 SELECT d,dt,ts,t,y,y4,y2 FROM t1;
774 d dt ts t y y4 y2
775 0000-00-00 0000-00-00 00:00:00 0000-00-00 00:00:00 -838:59:59 0000 0000 00
776 0000-00-00 0000-00-00 00:00:00 0000-00-00 00:00:00 00:00:00 2000 2000 00
777 1000-01-01 1000-01-01 00:00:00 1970-01-01 00:00:01 -838:59:59 1901 1901 00
778 2012-04-09 2012-04-09 05:27:00 2012-04-09 05:27:00 05:27:00 2012 2012 12
779 9999-12-31 9999-12-31 23:59:59 2038-01-19 03:14:07 838:59:59 2155 2155 99
780 DROP TABLE t1;
781 DROP TABLE IF EXISTS t1;
782 CREATE TABLE t1 (c DATE <CUSTOM_COL_OPTIONS> NOT NULL) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>;
783 SHOW COLUMNS IN t1;
784 Field Type Null Key Default Extra
785 c date NO NULL
786 INSERT INTO t1 (c) VALUES (NULL);
787 ERROR 23000: Column 'c' cannot be null
788 DROP TABLE t1;
789 CREATE TABLE t1 (c DATE <CUSTOM_COL_OPTIONS> NOT NULL,
790 c2 DATE <CUSTOM_COL_OPTIONS> NOT NULL DEFAULT '2012-12-21'
791 ) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>;
792 SHOW COLUMNS IN t1;
793 Field Type Null Key Default Extra
794 c date NO NULL
795 c2 date NO 2012-12-21
796 ALTER TABLE t1 ADD COLUMN err DATE <CUSTOM_COL_OPTIONS> NOT NULL DEFAULT NULL;
797 ERROR 42000: Invalid default value for 'err'
798 INSERT INTO t1 (c) VALUES (NULL);
799 ERROR 23000: Column 'c' cannot be null
800 SELECT HEX(c), HEX(c2) FROM t1;
801 HEX(c) HEX(c2)
802 INSERT INTO t1 (c2) VALUES (NULL);
803 ERROR 23000: Column 'c2' cannot be null
804 INSERT INTO t1 (c) VALUES ('2012-12-21');
805 SELECT COUNT(c), COUNT(c2) FROM t1;
806 COUNT(c) COUNT(c2)
807 1 1
808 DROP TABLE t1;
809 DROP TABLE IF EXISTS t1;
810 CREATE TABLE t1 (c DATETIME <CUSTOM_COL_OPTIONS> NOT NULL) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>;
811 SHOW COLUMNS IN t1;
812 Field Type Null Key Default Extra
813 c datetime NO NULL
814 INSERT INTO t1 (c) VALUES (NULL);
815 ERROR 23000: Column 'c' cannot be null
816 DROP TABLE t1;
817 CREATE TABLE t1 (c DATETIME <CUSTOM_COL_OPTIONS> NOT NULL,
818 c2 DATETIME <CUSTOM_COL_OPTIONS> NOT NULL DEFAULT '2012-12-21 12:21:12'
819 ) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>;
820 SHOW COLUMNS IN t1;
821 Field Type Null Key Default Extra
822 c datetime NO NULL
823 c2 datetime NO 2012-12-21 12:21:12
824 ALTER TABLE t1 ADD COLUMN err DATETIME <CUSTOM_COL_OPTIONS> NOT NULL DEFAULT NULL;
825 ERROR 42000: Invalid default value for 'err'
826 INSERT INTO t1 (c) VALUES (NULL);
827 ERROR 23000: Column 'c' cannot be null
828 SELECT HEX(c), HEX(c2) FROM t1;
829 HEX(c) HEX(c2)
830 INSERT INTO t1 (c2) VALUES (NULL);
831 ERROR 23000: Column 'c2' cannot be null
832 INSERT INTO t1 (c) VALUES ('2012-12-21 12:21:12');
833 SELECT COUNT(c), COUNT(c2) FROM t1;
834 COUNT(c) COUNT(c2)
835 1 1
836 DROP TABLE t1;
837 CREATE TABLE t1 (c TIMESTAMP <CUSTOM_COL_OPTIONS> NOT NULL,
838 c2 TIMESTAMP <CUSTOM_COL_OPTIONS> NOT NULL DEFAULT '2012-02-21 12:21:12'
839 ) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>;
840 ALTER TABLE t1 ADD COLUMN err TIMESTAMP <CUSTOM_COL_OPTIONS> NOT NULL DEFAULT NULL;
841 ERROR 42000: Invalid default value for 'err'
842 INSERT INTO t1 (c) VALUES (NULL);
843 INSERT INTO t1 (c2) VALUES (NULL);
844 SELECT c, c2 FROM t1;
845 c c2
846 <TIMESTAMP> <DEFAULT_TIMESTAMP>
847 <TIMESTAMP> <TIMESTAMP>
848 DROP TABLE t1;
849 CREATE TABLE t1 (c TIMESTAMP <CUSTOM_COL_OPTIONS> NOT NULL) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>;
850 SHOW COLUMNS IN t1;
851 Field Type Null Key Default Extra
852 c timestamp NO CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP
853 INSERT INTO t1 (c) VALUES (NULL);
854 SELECT c FROM t1;
855 c
856 <TIMESTAMP>
857 DROP TABLE t1;
858 DROP TABLE IF EXISTS t1;
859 CREATE TABLE t1 (c TIME <CUSTOM_COL_OPTIONS> NOT NULL) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>;
860 SHOW COLUMNS IN t1;
861 Field Type Null Key Default Extra
862 c time NO NULL
863 INSERT INTO t1 (c) VALUES (NULL);
864 ERROR 23000: Column 'c' cannot be null
865 DROP TABLE t1;
866 CREATE TABLE t1 (c TIME <CUSTOM_COL_OPTIONS> NOT NULL,
867 c2 TIME <CUSTOM_COL_OPTIONS> NOT NULL DEFAULT '12:21:12'
868 ) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>;
869 SHOW COLUMNS IN t1;
870 Field Type Null Key Default Extra
871 c time NO NULL
872 c2 time NO 12:21:12
873 ALTER TABLE t1 ADD COLUMN err TIME <CUSTOM_COL_OPTIONS> NOT NULL DEFAULT NULL;
874 ERROR 42000: Invalid default value for 'err'
875 INSERT INTO t1 (c) VALUES (NULL);
876 ERROR 23000: Column 'c' cannot be null
877 SELECT HEX(c), HEX(c2) FROM t1;
878 HEX(c) HEX(c2)
879 INSERT INTO t1 (c2) VALUES (NULL);
880 ERROR 23000: Column 'c2' cannot be null
881 INSERT INTO t1 (c) VALUES ('12:21:12');
882 SELECT COUNT(c), COUNT(c2) FROM t1;
883 COUNT(c) COUNT(c2)
884 1 1
885 DROP TABLE t1;
886 DROP TABLE IF EXISTS t1;
887 CREATE TABLE t1 (c YEAR <CUSTOM_COL_OPTIONS> NOT NULL) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>;
888 SHOW COLUMNS IN t1;
889 Field Type Null Key Default Extra
890 c year(4) NO NULL
891 INSERT INTO t1 (c) VALUES (NULL);
892 ERROR 23000: Column 'c' cannot be null
893 DROP TABLE t1;
894 CREATE TABLE t1 (c YEAR <CUSTOM_COL_OPTIONS> NOT NULL,
895 c2 YEAR <CUSTOM_COL_OPTIONS> NOT NULL DEFAULT '2012'
896 ) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>;
897 SHOW COLUMNS IN t1;
898 Field Type Null Key Default Extra
899 c year(4) NO NULL
900 c2 year(4) NO 2012
901 ALTER TABLE t1 ADD COLUMN err YEAR <CUSTOM_COL_OPTIONS> NOT NULL DEFAULT NULL;
902 ERROR 42000: Invalid default value for 'err'
903 INSERT INTO t1 (c) VALUES (NULL);
904 ERROR 23000: Column 'c' cannot be null
905 SELECT HEX(c), HEX(c2) FROM t1;
906 HEX(c) HEX(c2)
907 INSERT INTO t1 (c2) VALUES (NULL);
908 ERROR 23000: Column 'c2' cannot be null
909 INSERT INTO t1 (c) VALUES ('2012');
910 SELECT COUNT(c), COUNT(c2) FROM t1;
911 COUNT(c) COUNT(c2)
912 1 1
913 DROP TABLE t1;
914 DROP TABLE IF EXISTS t1;
915 CREATE TABLE t1 (c YEAR(2) <CUSTOM_COL_OPTIONS> NOT NULL) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>;
916 Warnings:
917 Note 1287 'YEAR(2)' is deprecated and will be removed in a future release. Please use YEAR(4) instead
918 SHOW COLUMNS IN t1;
919 Field Type Null Key Default Extra
920 c year(2) NO NULL
921 INSERT INTO t1 (c) VALUES (NULL);
922 ERROR 23000: Column 'c' cannot be null
923 DROP TABLE t1;
924 CREATE TABLE t1 (c YEAR(2) <CUSTOM_COL_OPTIONS> NOT NULL,
925 c2 YEAR(2) <CUSTOM_COL_OPTIONS> NOT NULL DEFAULT '12'
926 ) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>;
927 Warnings:
928 Note 1287 'YEAR(2)' is deprecated and will be removed in a future release. Please use YEAR(4) instead
929 Note 1287 'YEAR(2)' is deprecated and will be removed in a future release. Please use YEAR(4) instead
930 SHOW COLUMNS IN t1;
931 Field Type Null Key Default Extra
932 c year(2) NO NULL
933 c2 year(2) NO 12
934 ALTER TABLE t1 ADD COLUMN err YEAR(2) <CUSTOM_COL_OPTIONS> NOT NULL DEFAULT NULL;
935 ERROR 42000: Invalid default value for 'err'
936 INSERT INTO t1 (c) VALUES (NULL);
937 ERROR 23000: Column 'c' cannot be null
938 SELECT HEX(c), HEX(c2) FROM t1;
939 HEX(c) HEX(c2)
940 INSERT INTO t1 (c2) VALUES (NULL);
941 ERROR 23000: Column 'c2' cannot be null
942 INSERT INTO t1 (c) VALUES ('12');
943 SELECT COUNT(c), COUNT(c2) FROM t1;
944 COUNT(c) COUNT(c2)
945 1 1
946 DROP TABLE t1;
947 #
948 # ENUM columns
949 #
950 DROP TABLE IF EXISTS t1;
951 CREATE TABLE t1 (a ENUM('') <CUSTOM_COL_OPTIONS> NOT NULL,
952 b ENUM('test1','test2','test3','test4','test5') <CUSTOM_COL_OPTIONS> NOT NULL,
953 c ENUM('1','2','3','4','5','6','7','8','9','a','b','c','d','e','f','g','h','i','j','k','l','m','n','o','p','q','r','s','t','u','v','w','x','y','z',' ','11','12','13','14','15','16','17','18','19','1a','1b','1c','1d','1e','1f','1g','1h','1i','1j','1k','1l','1m','1n','1o','1p','1q','1r','1s','1t','1u','1v','1w','1x','1y','1z','20','21','22','23','24','25','26','27','28','29','2a','2b','2c','2d','2e','2f','2g','2h','2i','2j','2k','2l','2m','2n','2o','2p','2q','2r','2s','2t','2u','2v','2w','2x','2y','2z','30','31','32','33','34','35','36','37','38','39','3a','3b','3c','3d','3e','3f','3g','3h','3i','3j','3k','3l','3m','3n','3o','3p','3q','3r','3s','3t','3u','3v','3w','3x','3y','3z','40','41','42','43','44','45','46','47','48','49','4a','4b','4c','4d','4e','4f','4g','4h','4i','4j','4k','4l','4m','4n','4o','4p','4q','4r','4s','4t','4u','4v','4w','4x','4y','4z','50','51','52','53','54','55','56','57','58','59','5a','5b','5c','5d','5e','5f','5g','5h','5i','5j','5k','5l','5m','5n','5o','5p','5q','5r','5s','5t','5u','5v','5w','5x','5y','5z','60','61','62','63','64','65','66','67','68','69','6a','6b','6c','6d','6e','6f','6g','6h','6i','6j','6k','6l','6m','6n','6o','6p','6q','6r','6s','6t','6u','6v','6w','6x','6y','6z','70','71','72','73','74','75') <CUSTOM_COL_OPTIONS> NOT NULL
954 ) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>;
955 SHOW COLUMNS IN t1;
956 Field Type Null Key Default Extra
957 a enum('') # # #
958 b enum('test1','test2','test3','test4','test5') # # #
959 c enum('1','2','3','4','5','6','7','8','9','a','b','c','d','e','f','g','h','i','j','k','l','m','n','o','p','q','r','s','t','u','v','w','x','y','z','','11','12','13','14','15','16','17','18','19','1a','1b','1c','1d','1e','1f','1g','1h','1i','1j','1k','1l','1m','1n','1o','1p','1q','1r','1s','1t','1u','1v','1w','1x','1y','1z','20','21','22','23','24','25','26','27','28','29','2a','2b','2c','2d','2e','2f','2g','2h','2i','2j','2k','2l','2m','2n','2o','2p','2q','2r','2s','2t','2u','2v','2w','2x','2y','2z','30','31','32','33','34','35','36','37','38','39','3a','3b','3c','3d','3e','3f','3g','3h','3i','3j','3k','3l','3m','3n','3o','3p','3q','3r','3s','3t','3u','3v','3w','3x','3y','3z','40','41','42','43','44','45','46','47','48','49','4a','4b','4c','4d','4e','4f','4g','4h','4i','4j','4k','4l','4m','4n','4o','4p','4q','4r','4s','4t','4u','4v','4w','4x','4y','4z','50','51','52','53','54','55','56','57','58','59','5a','5b','5c','5d','5e','5f','5g','5h','5i','5j','5k','5l','5m','5n','5o','5p','5q','5r','5s','5t','5u','5v','5w','5x','5y','5z','60','61','62','63','64','65','66','67','68','69','6a','6b','6c','6d','6e','6f','6g','6h','6i','6j','6k','6l','6m','6n','6o','6p','6q','6r','6s','6t','6u','6v','6w','6x','6y','6z','70','71','72','73','74','75') # # #
960 INSERT INTO t1 (a,b,c) VALUES ('','test2','4'),('',5,2);
961 SELECT a,b,c FROM t1;
962 a b c
963 test2 4
964 test5 2
965 INSERT INTO t1 (a,b,c) VALUES (0,'test6',-1);
966 Warnings:
967 Warning 1265 Data truncated for column 'a' at row 1
968 Warning 1265 Data truncated for column 'b' at row 1
969 Warning 1265 Data truncated for column 'c' at row 1
970 SELECT a,b,c FROM t1;
971 a b c
972
973 test2 4
974 test5 2
975 ALTER TABLE t1 ADD COLUMN e ENUM('a','A') <CUSTOM_COL_OPTIONS> NOT NULL;
976 Warnings:
977 Note 1291 Column 'e' has duplicated value 'a' in ENUM
978 SHOW COLUMNS IN t1;
979 Field Type Null Key Default Extra
980 a enum('') # # #
981 b enum('test1','test2','test3','test4','test5') # # #
982 c enum('1','2','3','4','5','6','7','8','9','a','b','c','d','e','f','g','h','i','j','k','l','m','n','o','p','q','r','s','t','u','v','w','x','y','z','','11','12','13','14','15','16','17','18','19','1a','1b','1c','1d','1e','1f','1g','1h','1i','1j','1k','1l','1m','1n','1o','1p','1q','1r','1s','1t','1u','1v','1w','1x','1y','1z','20','21','22','23','24','25','26','27','28','29','2a','2b','2c','2d','2e','2f','2g','2h','2i','2j','2k','2l','2m','2n','2o','2p','2q','2r','2s','2t','2u','2v','2w','2x','2y','2z','30','31','32','33','34','35','36','37','38','39','3a','3b','3c','3d','3e','3f','3g','3h','3i','3j','3k','3l','3m','3n','3o','3p','3q','3r','3s','3t','3u','3v','3w','3x','3y','3z','40','41','42','43','44','45','46','47','48','49','4a','4b','4c','4d','4e','4f','4g','4h','4i','4j','4k','4l','4m','4n','4o','4p','4q','4r','4s','4t','4u','4v','4w','4x','4y','4z','50','51','52','53','54','55','56','57','58','59','5a','5b','5c','5d','5e','5f','5g','5h','5i','5j','5k','5l','5m','5n','5o','5p','5q','5r','5s','5t','5u','5v','5w','5x','5y','5z','60','61','62','63','64','65','66','67','68','69','6a','6b','6c','6d','6e','6f','6g','6h','6i','6j','6k','6l','6m','6n','6o','6p','6q','6r','6s','6t','6u','6v','6w','6x','6y','6z','70','71','72','73','74','75') # # #
983 e enum('a','A') # # #
984 INSERT INTO t1 (a,b,c,e) VALUES ('','test3','75','A');
985 SELECT a,b,c,e FROM t1;
986 a b c e
987 a
988 test2 4 a
989 test3 75 a
990 test5 2 a
991 SELECT a,b,c,e FROM t1 WHERE b='test2' OR a != '';
992 a b c e
993 test2 4 a
994 DROP TABLE t1;
995 DROP TABLE IF EXISTS t1;
996 CREATE TABLE t1 (c ENUM('test1','test2','test3') <CUSTOM_COL_OPTIONS> NOT NULL) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>;
997 SHOW COLUMNS IN t1;
998 Field Type Null Key Default Extra
999 c enum('test1','test2','test3') NO NULL
1000 INSERT INTO t1 (c) VALUES (NULL);
1001 ERROR 23000: Column 'c' cannot be null
1002 DROP TABLE t1;
1003 CREATE TABLE t1 (c ENUM('test1','test2','test3') <CUSTOM_COL_OPTIONS> NOT NULL,
1004 c2 ENUM('test1','test2','test3') <CUSTOM_COL_OPTIONS> NOT NULL DEFAULT 'test2'
1005 ) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>;
1006 SHOW COLUMNS IN t1;
1007 Field Type Null Key Default Extra
1008 c enum('test1','test2','test3') NO NULL
1009 c2 enum('test1','test2','test3') NO test2
1010 ALTER TABLE t1 ADD COLUMN err ENUM('test1','test2','test3') <CUSTOM_COL_OPTIONS> NOT NULL DEFAULT NULL;
1011 ERROR 42000: Invalid default value for 'err'
1012 INSERT INTO t1 (c) VALUES (NULL);
1013 ERROR 23000: Column 'c' cannot be null
1014 SELECT HEX(c), HEX(c2) FROM t1;
1015 HEX(c) HEX(c2)
1016 INSERT INTO t1 (c2) VALUES (NULL);
1017 ERROR 23000: Column 'c2' cannot be null
1018 INSERT INTO t1 (c) VALUES ('test2');
1019 SELECT COUNT(c), COUNT(c2) FROM t1;
1020 COUNT(c) COUNT(c2)
1021 1 1
1022 DROP TABLE t1;
1023 #
1024 # Fixed point columns (NUMERIC, DECIMAL)
1025 #
1026 DROP TABLE IF EXISTS t1;
1027 CREATE TABLE t1 (d DECIMAL <CUSTOM_COL_OPTIONS> NOT NULL,
1028 d0 DECIMAL(0) <CUSTOM_COL_OPTIONS> NOT NULL,
1029 d1_1 DECIMAL(1,1) <CUSTOM_COL_OPTIONS> NOT NULL,
1030 d10_2 DECIMAL(10,2) <CUSTOM_COL_OPTIONS> NOT NULL,
1031 d60_10 DECIMAL(60,10) <CUSTOM_COL_OPTIONS> NOT NULL,
1032 n NUMERIC <CUSTOM_COL_OPTIONS> NOT NULL,
1033 n0_0 NUMERIC(0,0) <CUSTOM_COL_OPTIONS> NOT NULL,
1034 n1 NUMERIC(1) <CUSTOM_COL_OPTIONS> NOT NULL,
1035 n20_4 NUMERIC(20,4) <CUSTOM_COL_OPTIONS> NOT NULL,
1036 n65_4 NUMERIC(65,4) <CUSTOM_COL_OPTIONS> NOT NULL
1037 ) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>;
1038 SHOW COLUMNS IN t1;
1039 Field Type Null Key Default Extra
1040 d decimal(10,0) # # #
1041 d0 decimal(10,0) # # #
1042 d1_1 decimal(1,1) # # #
1043 d10_2 decimal(10,2) # # #
1044 d60_10 decimal(60,10) # # #
1045 n decimal(10,0) # # #
1046 n0_0 decimal(10,0) # # #
1047 n1 decimal(1,0) # # #
1048 n20_4 decimal(20,4) # # #
1049 n65_4 decimal(65,4) # # #
1050 INSERT INTO t1 (d,d0,d1_1,d10_2,d60_10,n,n0_0,n1,n20_4,n65_4) VALUES (100,123456,0.3,40000.25,123456789123456789.10001,1024,7000.0,8.0,999999.9,9223372036854775807);
1051 INSERT INTO t1 (d,d0,d1_1,d10_2,d60_10,n,n0_0,n1,n20_4,n65_4) VALUES (0,0,0,0,0,0,0,0,0,0);
1052 INSERT INTO t1 (d,d0,d1_1,d10_2,d60_10,n,n0_0,n1,n20_4,n65_4) VALUES (9999999999.0,9999999999.0,0.9,99999999.99,99999999999999999999999999999999999999999999999999.9999999999,9999999999.0,9999999999.0,9.0,9999999999999999.9999,9999999999999999999999999999999999999999999999999999999999999.9999);
1053 SELECT d,d0,d1_1,d10_2,d60_10,n,n0_0,n1,n20_4,n65_4 FROM t1;
1054 d d0 d1_1 d10_2 d60_10 n n0_0 n1 n20_4 n65_4
1055 0 0 0.0 0.00 0.0000000000 0 0 0 0.0000 0.0000
1056 100 123456 0.3 40000.25 123456789123456789.1000100000 1024 7000 8 999999.9000 9223372036854775807.0000
1057 9999999999 9999999999 0.9 99999999.99 99999999999999999999999999999999999999999999999999.9999999999 9999999999 9999999999 9 9999999999999999.9999 9999999999999999999999999999999999999999999999999999999999999.9999
1058 INSERT INTO t1 (d,d0,d1_1,d10_2,d60_10,n,n0_0,n1,n20_4,n65_4) VALUES (-100,-123456,-0.3,-40000.25,-123456789123456789.10001,-1024,-7000.0,-8.0,-999999.9,-9223372036854775807);
1059 INSERT INTO t1 (d,d0,d1_1,d10_2,d60_10,n,n0_0,n1,n20_4,n65_4) VALUES (-9999999999.0,-9999999999.0,-0.9,-99999999.99,-99999999999999999999999999999999999999999999999999.9999999999,-9999999999.0,-9999999999.0,-9.0,-9999999999999999.9999,-9999999999999999999999999999999999999999999999999999999999999.9999);
1060 SELECT d,d0,d1_1,d10_2,d60_10,n,n0_0,n1,n20_4,n65_4 FROM t1;
1061 d d0 d1_1 d10_2 d60_10 n n0_0 n1 n20_4 n65_4
1062 -100 -123456 -0.3 -40000.25 -123456789123456789.1000100000 -1024 -7000 -8 -999999.9000 -9223372036854775807.0000
1063 -9999999999 -9999999999 -0.9 -99999999.99 -99999999999999999999999999999999999999999999999999.9999999999 -9999999999 -9999999999 -9 -9999999999999999.9999 -9999999999999999999999999999999999999999999999999999999999999.9999
1064 0 0 0.0 0.00 0.0000000000 0 0 0 0.0000 0.0000
1065 100 123456 0.3 40000.25 123456789123456789.1000100000 1024 7000 8 999999.9000 9223372036854775807.0000
1066 9999999999 9999999999 0.9 99999999.99 99999999999999999999999999999999999999999999999999.9999999999 9999999999 9999999999 9 9999999999999999.9999 9999999999999999999999999999999999999999999999999999999999999.9999
1067 SELECT d,d0,d1_1,d10_2,d60_10,n,n0_0,n1,n20_4,n65_4 FROM t1 WHERE n20_4 = 9999999999999999.9999 OR d < 100;
1068 d d0 d1_1 d10_2 d60_10 n n0_0 n1 n20_4 n65_4
1069 -100 -123456 -0.3 -40000.25 -123456789123456789.1000100000 -1024 -7000 -8 -999999.9000 -9223372036854775807.0000
1070 -9999999999 -9999999999 -0.9 -99999999.99 -99999999999999999999999999999999999999999999999999.9999999999 -9999999999 -9999999999 -9 -9999999999999999.9999 -9999999999999999999999999999999999999999999999999999999999999.9999
1071 0 0 0.0 0.00 0.0000000000 0 0 0 0.0000 0.0000
1072 9999999999 9999999999 0.9 99999999.99 99999999999999999999999999999999999999999999999999.9999999999 9999999999 9999999999 9 9999999999999999.9999 9999999999999999999999999999999999999999999999999999999999999.9999
1073 INSERT INTO t1 (d,d0,d1_1,d10_2,d60_10,n,n0_0,n1,n20_4,n65_4) SELECT n65_4, n65_4, n65_4, n65_4, n65_4, n65_4, n65_4, n65_4, n65_4, n65_4 FROM t1 WHERE n65_4 = ( SELECT MAX(n65_4) FROM t1 );
1074 Warnings:
1075 Warning 1264 Out of range value for column 'd' at row 1
1076 Warning 1264 Out of range value for column 'd0' at row 1
1077 Warning 1264 Out of range value for column 'd1_1' at row 1
1078 Warning 1264 Out of range value for column 'd10_2' at row 1
1079 Warning 1264 Out of range value for column 'd60_10' at row 1
1080 Warning 1264 Out of range value for column 'n' at row 1
1081 Warning 1264 Out of range value for column 'n0_0' at row 1
1082 Warning 1264 Out of range value for column 'n1' at row 1
1083 Warning 1264 Out of range value for column 'n20_4' at row 1
1084 SELECT d,d0,d1_1,d10_2,d60_10,n,n0_0,n1,n20_4,n65_4 FROM t1;
1085 d d0 d1_1 d10_2 d60_10 n n0_0 n1 n20_4 n65_4
1086 -100 -123456 -0.3 -40000.25 -123456789123456789.1000100000 -1024 -7000 -8 -999999.9000 -9223372036854775807.0000
1087 -9999999999 -9999999999 -0.9 -99999999.99 -99999999999999999999999999999999999999999999999999.9999999999 -9999999999 -9999999999 -9 -9999999999999999.9999 -9999999999999999999999999999999999999999999999999999999999999.9999
1088 0 0 0.0 0.00 0.0000000000 0 0 0 0.0000 0.0000
1089 100 123456 0.3 40000.25 123456789123456789.1000100000 1024 7000 8 999999.9000 9223372036854775807.0000
1090 9999999999 9999999999 0.9 99999999.99 99999999999999999999999999999999999999999999999999.9999999999 9999999999 9999999999 9 9999999999999999.9999 9999999999999999999999999999999999999999999999999999999999999.9999
1091 9999999999 9999999999 0.9 99999999.99 99999999999999999999999999999999999999999999999999.9999999999 9999999999 9999999999 9 9999999999999999.9999 9999999999999999999999999999999999999999999999999999999999999.9999
1092 INSERT INTO t1 (d,d0,d1_1,d10_2,d60_10,n,n0_0,n1,n20_4,n65_4) VALUES (10000000000.0,10000000000.0,1.1,100000000.99,100000000000000000000000000000000000000000000000000.0,10000000000.0,10000000000.0,10.0,10000000000000000.9999,10000000000000000000000000000000000000000000000000000000000000.9999);
1093 Warnings:
1094 Warning 1264 Out of range value for column 'd' at row 1
1095 Warning 1264 Out of range value for column 'd0' at row 1
1096 Warning 1264 Out of range value for column 'd1_1' at row 1
1097 Warning 1264 Out of range value for column 'd10_2' at row 1
1098 Warning 1264 Out of range value for column 'd60_10' at row 1
1099 Warning 1264 Out of range value for column 'n' at row 1
1100 Warning 1264 Out of range value for column 'n0_0' at row 1
1101 Warning 1264 Out of range value for column 'n1' at row 1
1102 Warning 1264 Out of range value for column 'n20_4' at row 1
1103 Warning 1264 Out of range value for column 'n65_4' at row 1
1104 SELECT d,d0,d1_1,d10_2,d60_10,n,n0_0,n1,n20_4,n65_4 FROM t1;
1105 d d0 d1_1 d10_2 d60_10 n n0_0 n1 n20_4 n65_4
1106 -100 -123456 -0.3 -40000.25 -123456789123456789.1000100000 -1024 -7000 -8 -999999.9000 -9223372036854775807.0000
1107 -9999999999 -9999999999 -0.9 -99999999.99 -99999999999999999999999999999999999999999999999999.9999999999 -9999999999 -9999999999 -9 -9999999999999999.9999 -9999999999999999999999999999999999999999999999999999999999999.9999
1108 0 0 0.0 0.00 0.0000000000 0 0 0 0.0000 0.0000
1109 100 123456 0.3 40000.25 123456789123456789.1000100000 1024 7000 8 999999.9000 9223372036854775807.0000
1110 9999999999 9999999999 0.9 99999999.99 99999999999999999999999999999999999999999999999999.9999999999 9999999999 9999999999 9 9999999999999999.9999 9999999999999999999999999999999999999999999999999999999999999.9999
1111 9999999999 9999999999 0.9 99999999.99 99999999999999999999999999999999999999999999999999.9999999999 9999999999 9999999999 9 9999999999999999.9999 9999999999999999999999999999999999999999999999999999999999999.9999
1112 9999999999 9999999999 0.9 99999999.99 99999999999999999999999999999999999999999999999999.9999999999 9999999999 9999999999 9 9999999999999999.9999 9999999999999999999999999999999999999999999999999999999999999.9999
1113 INSERT INTO t1 (d,d0,d1_1,d10_2,d60_10,n,n0_0,n1,n20_4,n65_4) VALUES (9999999999.1,9999999999.1,1.9,99999999.001,99999999999999999999999999999999999999999999999999.99999999991,9999999999.1,9999999999.1,9.1,9999999999999999.00001,9999999999999999999999999999999999999999999999999999999999999.11111);
1114 Warnings:
1115 Note 1265 Data truncated for column 'd' at row 1
1116 Note 1265 Data truncated for column 'd0' at row 1
1117 Warning 1264 Out of range value for column 'd1_1' at row 1
1118 Note 1265 Data truncated for column 'd10_2' at row 1
1119 Note 1265 Data truncated for column 'd60_10' at row 1
1120 Note 1265 Data truncated for column 'n' at row 1
1121 Note 1265 Data truncated for column 'n0_0' at row 1
1122 Note 1265 Data truncated for column 'n1' at row 1
1123 Note 1265 Data truncated for column 'n20_4' at row 1
1124 Note 1265 Data truncated for column 'n65_4' at row 1
1125 SELECT d,d0,d1_1,d10_2,d60_10,n,n0_0,n1,n20_4,n65_4 FROM t1;
1126 d d0 d1_1 d10_2 d60_10 n n0_0 n1 n20_4 n65_4
1127 -100 -123456 -0.3 -40000.25 -123456789123456789.1000100000 -1024 -7000 -8 -999999.9000 -9223372036854775807.0000
1128 -9999999999 -9999999999 -0.9 -99999999.99 -99999999999999999999999999999999999999999999999999.9999999999 -9999999999 -9999999999 -9 -9999999999999999.9999 -9999999999999999999999999999999999999999999999999999999999999.9999
1129 0 0 0.0 0.00 0.0000000000 0 0 0 0.0000 0.0000
1130 100 123456 0.3 40000.25 123456789123456789.1000100000 1024 7000 8 999999.9000 9223372036854775807.0000
1131 9999999999 9999999999 0.9 99999999.00 99999999999999999999999999999999999999999999999999.9999999999 9999999999 9999999999 9 9999999999999999.0000 9999999999999999999999999999999999999999999999999999999999999.1111
1132 9999999999 9999999999 0.9 99999999.99 99999999999999999999999999999999999999999999999999.9999999999 9999999999 9999999999 9 9999999999999999.9999 9999999999999999999999999999999999999999999999999999999999999.9999
1133 9999999999 9999999999 0.9 99999999.99 99999999999999999999999999999999999999999999999999.9999999999 9999999999 9999999999 9 9999999999999999.9999 9999999999999999999999999999999999999999999999999999999999999.9999
1134 9999999999 9999999999 0.9 99999999.99 99999999999999999999999999999999999999999999999999.9999999999 9999999999 9999999999 9 9999999999999999.9999 9999999999999999999999999999999999999999999999999999999999999.9999
1135 ALTER TABLE t1 ADD COLUMN n66 NUMERIC(66);
1136 ERROR 42000: Too big precision 66 specified for 'n66'. Maximum is 65.
1137 ALTER TABLE t1 ADD COLUMN n66_6 DECIMAL(66,6);
1138 ERROR 42000: Too big precision 66 specified for 'n66_6'. Maximum is 65.
1139 ALTER TABLE t1 ADD COLUMN n66_66 DECIMAL(66,66);
1140 ERROR 42000: Too big scale 66 specified for 'n66_66'. Maximum is 30.
1141 DROP TABLE t1;
1142 DROP TABLE IF EXISTS t1;
1143 CREATE TABLE t1 (c DECIMAL <CUSTOM_COL_OPTIONS> NOT NULL) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>;
1144 SHOW COLUMNS IN t1;
1145 Field Type Null Key Default Extra
1146 c decimal(10,0) NO NULL
1147 INSERT INTO t1 (c) VALUES (NULL);
1148 ERROR 23000: Column 'c' cannot be null
1149 DROP TABLE t1;
1150 CREATE TABLE t1 (c DECIMAL <CUSTOM_COL_OPTIONS> NOT NULL,
1151 c2 DECIMAL <CUSTOM_COL_OPTIONS> NOT NULL DEFAULT 1.1
1152 ) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>;
1153 Warnings:
1154 Note 1265 Data truncated for column 'c2' at row 1
1155 SHOW COLUMNS IN t1;
1156 Field Type Null Key Default Extra
1157 c decimal(10,0) NO NULL
1158 c2 decimal(10,0) NO 1
1159 ALTER TABLE t1 ADD COLUMN err DECIMAL <CUSTOM_COL_OPTIONS> NOT NULL DEFAULT NULL;
1160 ERROR 42000: Invalid default value for 'err'
1161 INSERT INTO t1 (c) VALUES (NULL);
1162 ERROR 23000: Column 'c' cannot be null
1163 SELECT HEX(c), HEX(c2) FROM t1;
1164 HEX(c) HEX(c2)
1165 INSERT INTO t1 (c2) VALUES (NULL);
1166 ERROR 23000: Column 'c2' cannot be null
1167 INSERT INTO t1 (c) VALUES (1.1);
1168 Warnings:
1169 Note 1265 Data truncated for column 'c' at row 1
1170 SELECT COUNT(c), COUNT(c2) FROM t1;
1171 COUNT(c) COUNT(c2)
1172 1 1
1173 DROP TABLE t1;
1174 DROP TABLE IF EXISTS t1;
1175 CREATE TABLE t1 (c NUMERIC <CUSTOM_COL_OPTIONS> NOT NULL) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>;
1176 SHOW COLUMNS IN t1;
1177 Field Type Null Key Default Extra
1178 c decimal(10,0) NO NULL
1179 INSERT INTO t1 (c) VALUES (NULL);
1180 ERROR 23000: Column 'c' cannot be null
1181 DROP TABLE t1;
1182 CREATE TABLE t1 (c NUMERIC <CUSTOM_COL_OPTIONS> NOT NULL,
1183 c2 NUMERIC <CUSTOM_COL_OPTIONS> NOT NULL DEFAULT 0
1184 ) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>;
1185 SHOW COLUMNS IN t1;
1186 Field Type Null Key Default Extra
1187 c decimal(10,0) NO NULL
1188 c2 decimal(10,0) NO 0
1189 ALTER TABLE t1 ADD COLUMN err NUMERIC <CUSTOM_COL_OPTIONS> NOT NULL DEFAULT NULL;
1190 ERROR 42000: Invalid default value for 'err'
1191 INSERT INTO t1 (c) VALUES (NULL);
1192 ERROR 23000: Column 'c' cannot be null
1193 SELECT HEX(c), HEX(c2) FROM t1;
1194 HEX(c) HEX(c2)
1195 INSERT INTO t1 (c2) VALUES (NULL);
1196 ERROR 23000: Column 'c2' cannot be null
1197 INSERT INTO t1 (c) VALUES (0);
1198 SELECT COUNT(c), COUNT(c2) FROM t1;
1199 COUNT(c) COUNT(c2)
1200 1 1
1201 DROP TABLE t1;
1202 #
1203 # Floating point columns (FLOAT, DOUBLE)
1204 #
1205 DROP TABLE IF EXISTS t1;
1206 CREATE TABLE t1 (f FLOAT <CUSTOM_COL_OPTIONS> NOT NULL,
1207 f0 FLOAT(0) <CUSTOM_COL_OPTIONS> NOT NULL,
1208 r1_1 REAL(1,1) <CUSTOM_COL_OPTIONS> NOT NULL,
1209 f23_0 FLOAT(23) <CUSTOM_COL_OPTIONS> NOT NULL,
1210 f20_3 FLOAT(20,3) <CUSTOM_COL_OPTIONS> NOT NULL,
1211 d DOUBLE <CUSTOM_COL_OPTIONS> NOT NULL,
1212 d1_0 DOUBLE(1,0) <CUSTOM_COL_OPTIONS> NOT NULL,
1213 d10_10 DOUBLE PRECISION (10,10) <CUSTOM_COL_OPTIONS> NOT NULL,
1214 d53 DOUBLE(53,0) <CUSTOM_COL_OPTIONS> NOT NULL,
1215 d53_10 DOUBLE(53,10) <CUSTOM_COL_OPTIONS> NOT NULL
1216 ) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>;
1217 SHOW COLUMNS IN t1;
1218 Field Type Null Key Default Extra
1219 f float # # #
1220 f0 float # # #
1221 r1_1 double(1,1) # # #
1222 f23_0 float # # #
1223 f20_3 float(20,3) # # #
1224 d double # # #
1225 d1_0 double(1,0) # # #
1226 d10_10 double(10,10) # # #
1227 d53 double(53,0) # # #
1228 d53_10 double(53,10) # # #
1229 INSERT INTO t1 (f,f0,r1_1,f23_0,f20_3,d,d1_0,d10_10,d53,d53_10) VALUES (12345.12345,12345.12345,0.9,123456789.123,56789.987,11111111.111,8.0,0.0123456789,1234566789123456789,99999999999999999.99999999);
1230 SELECT f,f0,r1_1,f23_0,f20_3,d,d1_0,d10_10,d53,d53_10 FROM t1;
1231 f 12345.1
1232 d 11111111.111
1233 d10_10 0.0123456789
1234 d1_0 8
1235 d53 1234566789123456800
1236 d53_10 100000000000000000.0000000000
1237 f0 12345.1
1238 f20_3 56789.988
1239 f23_0 123457000
1240 r1_1 0.9
1241 INSERT INTO t1 (f,f0,r1_1,f23_0,f20_3,d,d1_0,d10_10,d53,d53_10) VALUES (0,0,0,0,0,0,0,0,0,0);
1242 INSERT INTO t1 (f,f0,r1_1,f23_0,f20_3,d,d1_0,d10_10,d53,d53_10) VALUES (
1243 99999999999999999999999999999999999999,
1244 99999999999999999999999999999999999999.9999999999999999,
1245 0.9,
1246 99999999999999999999999999999999999999.9,
1247 99999999999999999.999,
1248 999999999999999999999999999999999999999999999999999999999999999999999999999999999,
1249 9,
1250 0.9999999999,
1251 1999999999999999999999999999999999999999999999999999999,
1252 19999999999999999999999999999999999999999999.9999999999
1253 );
1254 Warnings:
1255 Warning 1264 Out of range value for column 'd53' at row 1
1256 Warning 1264 Out of range value for column 'd53_10' at row 1
1257 SELECT f,f0,r1_1,f23_0,f20_3,d,d1_0,d10_10,d53,d53_10 FROM t1;
1258 f 12345.1
1259 d 0
1260 d 11111111.111
1261 d 1e81
1262 d10_10 0.0000000000
1263 d10_10 0.0123456789
1264 d10_10 0.9999999999
1265 d1_0 0
1266 d1_0 8
1267 d1_0 9
1268 d53 0
1269 d53 100000000000000000000000000000000000000000000000000000
1270 d53 1234566789123456800
1271 d53_10 0.0000000000
1272 d53_10 100000000000000000.0000000000
1273 d53_10 10000000000000000000000000000000000000000000.0000000000
1274 f 0
1275 f 1e38
1276 f0 0
1277 f0 12345.1
1278 f0 1e38
1279 f20_3 0.000
1280 f20_3 56789.988
1281 f20_3 99999998430674940.000
1282 f23_0 0
1283 f23_0 123457000
1284 f23_0 1e38
1285 r1_1 0.0
1286 r1_1 0.9
1287 r1_1 0.9
1288 INSERT INTO t1 (f,f0,r1_1,f23_0,f20_3,d,d1_0,d10_10,d53,d53_10) VALUES (-999999999999999999999999,-99999999999.999999999999,-0.9,-999.99999999999999999999,-99999999999999999.999,-999999999999999999999999999999999999999999999999999999999999-0.999,-9,-.9999999999,-999999999999999999999999999999.99999999999999999999999,-9999999999999999999999999999999999999999999.9999999999);
1289 SELECT f,f0,r1_1,f23_0,f20_3,d,d1_0,d10_10,d53,d53_10 FROM t1;
1290 f 12345.1
1291 d -1e60
1292 d 0
1293 d 11111111.111
1294 d 1e81
1295 d10_10 -0.9999999999
1296 d10_10 0.0000000000
1297 d10_10 0.0123456789
1298 d10_10 0.9999999999
1299 d1_0 -9
1300 d1_0 0
1301 d1_0 8
1302 d1_0 9
1303 d53 -1000000000000000000000000000000
1304 d53 0
1305 d53 100000000000000000000000000000000000000000000000000000
1306 d53 1234566789123456800
1307 d53_10 -10000000000000000000000000000000000000000000.0000000000
1308 d53_10 0.0000000000
1309 d53_10 100000000000000000.0000000000
1310 d53_10 10000000000000000000000000000000000000000000.0000000000
1311 f -1e24
1312 f 0
1313 f 1e38
1314 f0 -100000000000
1315 f0 0
1316 f0 12345.1
1317 f0 1e38
1318 f20_3 -99999998430674940.000
1319 f20_3 0.000
1320 f20_3 56789.988
1321 f20_3 99999998430674940.000
1322 f23_0 -1000
1323 f23_0 0
1324 f23_0 123457000
1325 f23_0 1e38
1326 r1_1 -0.9
1327 r1_1 0.0
1328 r1_1 0.9
1329 r1_1 0.9
1330 SELECT MAX(f), MAX(f0), MAX(r1_1), MAX(f23_0), MAX(f20_3), MAX(d), MAX(d1_0), MAX(d10_10), MAX(d53), MAX(d53_10) FROM t1;
1331 MAX(f) 9.999999680285692e37
1332 MAX(d) 1e81
1333 MAX(d10_10) 0.9999999999
1334 MAX(d1_0) 9
1335 MAX(d53) 100000000000000000000000000000000000000000000000000000
1336 MAX(d53_10) 10000000000000000000000000000000000000000000.0000000000
1337 MAX(f0) 9.999999680285692e37
1338 MAX(f20_3) 99999998430674940.000
1339 MAX(f23_0) 9.999999680285692e37
1340 MAX(r1_1) 0.9
1341 INSERT INTO t1 (f,f0,r1_1,f23_0,f20_3,d,d1_0,d10_10,d53,d53_10) SELECT d53_10, d53_10, d53_10, d53_10, d53_10, d53_10, d53_10, d53_10, d53_10, d53_10 FROM t1 ORDER BY d53_10 DESC LIMIT 1;
1342 Warnings:
1343 Warning 1264 Out of range value for column 'f' at row 1
1344 Warning 1264 Out of range value for column 'f0' at row 1
1345 Warning 1264 Out of range value for column 'r1_1' at row 1
1346 Warning 1264 Out of range value for column 'f23_0' at row 1
1347 Warning 1264 Out of range value for column 'f20_3' at row 1
1348 Warning 1264 Out of range value for column 'd1_0' at row 1
1349 SELECT f,f0,r1_1,f23_0,f20_3,d,d1_0,d10_10,d53,d53_10 FROM t1;
1350 f 12345.1
1351 d -1e60
1352 d 0
1353 d 11111111.111
1354 d 1e43
1355 d 1e81
1356 d10_10 -0.9999999999
1357 d10_10 0.0000000000
1358 d10_10 0.0123456789
1359 d10_10 0.9999999999
1360 d10_10 10000000000000000000000000000000000000000000.0000000000
1361 d1_0 -9
1362 d1_0 0
1363 d1_0 8
1364 d1_0 9
1365 d1_0 9
1366 d53 -1000000000000000000000000000000
1367 d53 0
1368 d53 10000000000000000000000000000000000000000000
1369 d53 100000000000000000000000000000000000000000000000000000
1370 d53 1234566789123456800
1371 d53_10 -10000000000000000000000000000000000000000000.0000000000
1372 d53_10 0.0000000000
1373 d53_10 100000000000000000.0000000000
1374 d53_10 10000000000000000000000000000000000000000000.0000000000
1375 d53_10 10000000000000000000000000000000000000000000.0000000000
1376 f -1e24
1377 f 0
1378 f 1e38
1379 f 3.40282e38
1380 f0 -100000000000
1381 f0 0
1382 f0 12345.1
1383 f0 1e38
1384 f0 3.40282e38
1385 f20_3 -99999998430674940.000
1386 f20_3 0.000
1387 f20_3 56789.988
1388 f20_3 99999998430674940.000
1389 f20_3 99999998430674940.000
1390 f23_0 -1000
1391 f23_0 0
1392 f23_0 123457000
1393 f23_0 1e38
1394 f23_0 3.40282e38
1395 r1_1 -0.9
1396 r1_1 0.0
1397 r1_1 0.9
1398 r1_1 0.9
1399 r1_1 0.9
1400 INSERT INTO t1 (f,f0,r1_1,f23_0,f20_3,d,d1_0,d10_10,d53,d53_10) VALUES (
1401 999999999999999999999999999999999999999,
1402 999999999999999999999999999999999999999.9999999999999999,
1403 1.9,
1404 999999999999999999999999999999999999999.9,
1405 999999999999999999.999,
1406 9999999999999999999999999999999999999999999999999999999999999999999999999999999999,
1407 99,
1408 1.9999999999,
1409 1999999999999999999999999999999999999999999999999999999,
1410 19999999999999999999999999999999999999999999.9999999999
1411 );
1412 Warnings:
1413 Warning 1916 Got overflow when converting '' to DECIMAL. Value truncated
1414 Warning 1264 Out of range value for column 'f' at row 1
1415 Warning 1264 Out of range value for column 'f0' at row 1
1416 Warning 1264 Out of range value for column 'r1_1' at row 1
1417 Warning 1264 Out of range value for column 'f23_0' at row 1
1418 Warning 1264 Out of range value for column 'f20_3' at row 1
1419 Warning 1264 Out of range value for column 'd1_0' at row 1
1420 Warning 1264 Out of range value for column 'd10_10' at row 1
1421 Warning 1264 Out of range value for column 'd53' at row 1
1422 Warning 1264 Out of range value for column 'd53_10' at row 1
1423 SELECT f,f0,r1_1,f23_0,f20_3,d,d1_0,d10_10,d53,d53_10 FROM t1;
1424 f 12345.1
1425 d -1e60
1426 d 0
1427 d 11111111.111
1428 d 1e43
1429 d 1e65
1430 d 1e81
1431 d10_10 -0.9999999999
1432 d10_10 0.0000000000
1433 d10_10 0.0123456789
1434 d10_10 0.9999999999
1435 d10_10 0.9999999999
1436 d10_10 10000000000000000000000000000000000000000000.0000000000
1437 d1_0 -9
1438 d1_0 0
1439 d1_0 8
1440 d1_0 9
1441 d1_0 9
1442 d1_0 9
1443 d53 -1000000000000000000000000000000
1444 d53 0
1445 d53 10000000000000000000000000000000000000000000
1446 d53 100000000000000000000000000000000000000000000000000000
1447 d53 100000000000000000000000000000000000000000000000000000
1448 d53 1234566789123456800
1449 d53_10 -10000000000000000000000000000000000000000000.0000000000
1450 d53_10 0.0000000000
1451 d53_10 100000000000000000.0000000000
1452 d53_10 10000000000000000000000000000000000000000000.0000000000
1453 d53_10 10000000000000000000000000000000000000000000.0000000000
1454 d53_10 10000000000000000000000000000000000000000000.0000000000
1455 f -1e24
1456 f 0
1457 f 1e38
1458 f 3.40282e38
1459 f 3.40282e38
1460 f0 -100000000000
1461 f0 0
1462 f0 12345.1
1463 f0 1e38
1464 f0 3.40282e38
1465 f0 3.40282e38
1466 f20_3 -99999998430674940.000
1467 f20_3 0.000
1468 f20_3 56789.988
1469 f20_3 99999998430674940.000
1470 f20_3 99999998430674940.000
1471 f20_3 99999998430674940.000
1472 f23_0 -1000
1473 f23_0 0
1474 f23_0 123457000
1475 f23_0 1e38
1476 f23_0 3.40282e38
1477 f23_0 3.40282e38
1478 r1_1 -0.9
1479 r1_1 0.0
1480 r1_1 0.9
1481 r1_1 0.9
1482 r1_1 0.9
1483 r1_1 0.9
1484 ALTER TABLE t1 ADD COLUMN d0_0 DOUBLE(0,0);
1485 # ERROR: Statement succeeded (expected results: ER_TOO_BIG_DISPLAYWIDTH)
1486 # ------------ UNEXPECTED RESULT ------------
1487 # [ ALTER TABLE t1 ADD COLUMN d0_0 DOUBLE(0,0) ]
1488 # The statement|command succeeded unexpectedly.
1489 # ALTER TABLE or the mix could be unsupported|malfunctioning, or the problem was caused by previous errors.
1490 # You can change the engine code, or create an rdiff, or disable the test by adding it to disabled.def.
1491 # Further in this test, the message might sometimes be suppressed; a part of the test might be skipped.
1492 # Also, this problem may cause a chain effect (more errors of different kinds in the test).
1493 # -------------------------------------------
1494 ALTER TABLE t1 ADD COLUMN n66_6 DECIMAL(256,1);
1495 ERROR 42000: Too big precision 256 specified for 'n66_6'. Maximum is 65.
1496 ALTER TABLE t1 ADD COLUMN n66_66 DECIMAL(40,35);
1497 ERROR 42000: Too big scale 35 specified for 'n66_66'. Maximum is 30.
1498 DROP TABLE t1;
1499 DROP TABLE IF EXISTS t1;
1500 CREATE TABLE t1 (c FLOAT <CUSTOM_COL_OPTIONS> NOT NULL) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>;
1501 SHOW COLUMNS IN t1;
1502 Field Type Null Key Default Extra
1503 c float NO NULL
1504 INSERT INTO t1 (c) VALUES (NULL);
1505 ERROR 23000: Column 'c' cannot be null
1506 DROP TABLE t1;
1507 CREATE TABLE t1 (c FLOAT <CUSTOM_COL_OPTIONS> NOT NULL,
1508 c2 FLOAT <CUSTOM_COL_OPTIONS> NOT NULL DEFAULT 1.1
1509 ) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>;
1510 SHOW COLUMNS IN t1;
1511 Field Type Null Key Default Extra
1512 c float NO NULL
1513 c2 float NO 1.1
1514 ALTER TABLE t1 ADD COLUMN err FLOAT <CUSTOM_COL_OPTIONS> NOT NULL DEFAULT NULL;
1515 ERROR 42000: Invalid default value for 'err'
1516 INSERT INTO t1 (c) VALUES (NULL);
1517 ERROR 23000: Column 'c' cannot be null
1518 SELECT HEX(c), HEX(c2) FROM t1;
1519 HEX(c) HEX(c2)
1520 INSERT INTO t1 (c2) VALUES (NULL);
1521 ERROR 23000: Column 'c2' cannot be null
1522 INSERT INTO t1 (c) VALUES (1.1 );
1523 SELECT COUNT(c), COUNT(c2) FROM t1;
1524 COUNT(c) COUNT(c2)
1525 1 1
1526 DROP TABLE t1;
1527 DROP TABLE IF EXISTS t1;
1528 CREATE TABLE t1 (c DOUBLE <CUSTOM_COL_OPTIONS> NOT NULL) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>;
1529 SHOW COLUMNS IN t1;
1530 Field Type Null Key Default Extra
1531 c double NO NULL
1532 INSERT INTO t1 (c) VALUES (NULL);
1533 ERROR 23000: Column 'c' cannot be null
1534 DROP TABLE t1;
1535 CREATE TABLE t1 (c DOUBLE <CUSTOM_COL_OPTIONS> NOT NULL,
1536 c2 DOUBLE <CUSTOM_COL_OPTIONS> NOT NULL DEFAULT 0
1537 ) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>;
1538 SHOW COLUMNS IN t1;
1539 Field Type Null Key Default Extra
1540 c double NO NULL
1541 c2 double NO 0
1542 ALTER TABLE t1 ADD COLUMN err DOUBLE <CUSTOM_COL_OPTIONS> NOT NULL DEFAULT NULL;
1543 ERROR 42000: Invalid default value for 'err'
1544 INSERT INTO t1 (c) VALUES (NULL);
1545 ERROR 23000: Column 'c' cannot be null
1546 SELECT HEX(c), HEX(c2) FROM t1;
1547 HEX(c) HEX(c2)
1548 INSERT INTO t1 (c2) VALUES (NULL);
1549 ERROR 23000: Column 'c2' cannot be null
1550 INSERT INTO t1 (c) VALUES (0);
1551 SELECT COUNT(c), COUNT(c2) FROM t1;
1552 COUNT(c) COUNT(c2)
1553 1 1
1554 DROP TABLE t1;
1555 #
1556 # INT columns
1557 #
1558 DROP TABLE IF EXISTS t1;
1559 CREATE TABLE t1 (i INT <CUSTOM_COL_OPTIONS> NOT NULL,
1560 i0 INT(0) <CUSTOM_COL_OPTIONS> NOT NULL,
1561 i1 INT(1) <CUSTOM_COL_OPTIONS> NOT NULL,
1562 i20 INT(20) <CUSTOM_COL_OPTIONS> NOT NULL,
1563 t TINYINT <CUSTOM_COL_OPTIONS> NOT NULL,
1564 t0 TINYINT(0) <CUSTOM_COL_OPTIONS> NOT NULL,
1565 t1 TINYINT(1) <CUSTOM_COL_OPTIONS> NOT NULL,
1566 t20 TINYINT(20) <CUSTOM_COL_OPTIONS> NOT NULL,
1567 s SMALLINT <CUSTOM_COL_OPTIONS> NOT NULL,
1568 s0 SMALLINT(0) <CUSTOM_COL_OPTIONS> NOT NULL,
1569 s1 SMALLINT(1) <CUSTOM_COL_OPTIONS> NOT NULL,
1570 s20 SMALLINT(20) <CUSTOM_COL_OPTIONS> NOT NULL,
1571 m MEDIUMINT <CUSTOM_COL_OPTIONS> NOT NULL,
1572 m0 MEDIUMINT(0) <CUSTOM_COL_OPTIONS> NOT NULL,
1573 m1 MEDIUMINT(1) <CUSTOM_COL_OPTIONS> NOT NULL,
1574 m20 MEDIUMINT(20) <CUSTOM_COL_OPTIONS> NOT NULL,
1575 b BIGINT <CUSTOM_COL_OPTIONS> NOT NULL,
1576 b0 BIGINT(0) <CUSTOM_COL_OPTIONS> NOT NULL,
1577 b1 BIGINT(1) <CUSTOM_COL_OPTIONS> NOT NULL,
1578 b20 BIGINT(20) <CUSTOM_COL_OPTIONS> NOT NULL
1579 ) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>;
1580 SHOW COLUMNS IN t1;
1581 Field Type Null Key Default Extra
1582 i int(11) # # #
1583 i0 int(11) # # #
1584 i1 int(1) # # #
1585 i20 int(20) # # #
1586 t tinyint(4) # # #
1587 t0 tinyint(4) # # #
1588 t1 tinyint(1) # # #
1589 t20 tinyint(20) # # #
1590 s smallint(6) # # #
1591 s0 smallint(6) # # #
1592 s1 smallint(1) # # #
1593 s20 smallint(20) # # #
1594 m mediumint(9) # # #
1595 m0 mediumint(9) # # #
1596 m1 mediumint(1) # # #
1597 m20 mediumint(20) # # #
1598 b bigint(20) # # #
1599 b0 bigint(20) # # #
1600 b1 bigint(1) # # #
1601 b20 bigint(20) # # #
1602 INSERT INTO t1 (i,i0,i1,i20,t,t0,t1,t20,s,s0,s1,s20,m,m0,m1,m20,b,b0,b1,b20) VALUES (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20);
1603 INSERT INTO t1 (i,i0,i1,i20,t,t0,t1,t20,s,s0,s1,s20,m,m0,m1,m20,b,b0,b1,b20) VALUES (0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0);
1604 INSERT INTO t1 (i,i0,i1,i20,t,t0,t1,t20,s,s0,s1,s20,m,m0,m1,m20,b,b0,b1,b20) VALUES (2147483647,2147483647,2147483647,2147483647,127,127,127,127,32767,32767,32767,32767,8388607,8388607,8388607,8388607,9223372036854775807,9223372036854775807,9223372036854775807,9223372036854775807);
1605 SELECT i,i0,i1,i20,t,t0,t1,t20,s,s0,s1,s20,m,m0,m1,m20,b,b0,b1,b20 FROM t1;
1606 i i0 i1 i20 t t0 t1 t20 s s0 s1 s20 m m0 m1 m20 b b0 b1 b20
1607 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
1608 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20
1609 2147483647 2147483647 2147483647 2147483647 127 127 127 127 32767 32767 32767 32767 8388607 8388607 8388607 8388607 9223372036854775807 9223372036854775807 9223372036854775807 9223372036854775807
1610 INSERT INTO t1 (i,i0,i1,i20,t,t0,t1,t20,s,s0,s1,s20,m,m0,m1,m20,b,b0,b1,b20) VALUES (-2147483648,-2147483648,-2147483648,-2147483648,-128,-128,-128,-128,-32768,-32768,-32768,-32768,-8388608,-8388608,-8388608,-8388608,-9223372036854775808,-9223372036854775808,-9223372036854775808,-9223372036854775808);
1611 INSERT INTO t1 (i,i0,i1,i20,t,t0,t1,t20,s,s0,s1,s20,m,m0,m1,m20,b,b0,b1,b20) VALUES (4294967295,4294967295,4294967295,4294967295,255,255,255,255,65535,65535,65535,65535,16777215,16777215,16777215,16777215,18446744073709551615,18446744073709551615,18446744073709551615,18446744073709551615);
1612 Warnings:
1613 Warning 1264 Out of range value for column 'i' at row 1
1614 Warning 1264 Out of range value for column 'i0' at row 1
1615 Warning 1264 Out of range value for column 'i1' at row 1
1616 Warning 1264 Out of range value for column 'i20' at row 1
1617 Warning 1264 Out of range value for column 't' at row 1
1618 Warning 1264 Out of range value for column 't0' at row 1
1619 Warning 1264 Out of range value for column 't1' at row 1
1620 Warning 1264 Out of range value for column 't20' at row 1
1621 Warning 1264 Out of range value for column 's' at row 1
1622 Warning 1264 Out of range value for column 's0' at row 1
1623 Warning 1264 Out of range value for column 's1' at row 1
1624 Warning 1264 Out of range value for column 's20' at row 1
1625 Warning 1264 Out of range value for column 'm' at row 1
1626 Warning 1264 Out of range value for column 'm0' at row 1
1627 Warning 1264 Out of range value for column 'm1' at row 1
1628 Warning 1264 Out of range value for column 'm20' at row 1
1629 Warning 1264 Out of range value for column 'b' at row 1
1630 Warning 1264 Out of range value for column 'b0' at row 1
1631 Warning 1264 Out of range value for column 'b1' at row 1
1632 Warning 1264 Out of range value for column 'b20' at row 1
1633 SELECT i,i0,i1,i20,t,t0,t1,t20,s,s0,s1,s20,m,m0,m1,m20,b,b0,b1,b20 FROM t1;
1634 i i0 i1 i20 t t0 t1 t20 s s0 s1 s20 m m0 m1 m20 b b0 b1 b20
1635 -2147483648 -2147483648 -2147483648 -2147483648 -128 -128 -128 -128 -32768 -32768 -32768 -32768 -8388608 -8388608 -8388608 -8388608 -9223372036854775808 -9223372036854775808 -9223372036854775808 -9223372036854775808
1636 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
1637 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20
1638 2147483647 2147483647 2147483647 2147483647 127 127 127 127 32767 32767 32767 32767 8388607 8388607 8388607 8388607 9223372036854775807 9223372036854775807 9223372036854775807 9223372036854775807
1639 2147483647 2147483647 2147483647 2147483647 127 127 127 127 32767 32767 32767 32767 8388607 8388607 8388607 8388607 9223372036854775807 9223372036854775807 9223372036854775807 9223372036854775807
1640 INSERT INTO t1 (i,i0,i1,i20,t,t0,t1,t20,s,s0,s1,s20,m,m0,m1,m20,b,b0,b1,b20) VALUES (-2147483649,-2147483649,-2147483649,-2147483649,-129,-129,-129,-129,-32769,-32769,-32769,-32769,-8388609,-8388609,-8388609,-8388609,-9223372036854775809,-9223372036854775809,-9223372036854775809,-9223372036854775809);
1641 Warnings:
1642 Warning 1264 Out of range value for column 'i' at row 1
1643 Warning 1264 Out of range value for column 'i0' at row 1
1644 Warning 1264 Out of range value for column 'i1' at row 1
1645 Warning 1264 Out of range value for column 'i20' at row 1
1646 Warning 1264 Out of range value for column 't' at row 1
1647 Warning 1264 Out of range value for column 't0' at row 1
1648 Warning 1264 Out of range value for column 't1' at row 1
1649 Warning 1264 Out of range value for column 't20' at row 1
1650 Warning 1264 Out of range value for column 's' at row 1
1651 Warning 1264 Out of range value for column 's0' at row 1
1652 Warning 1264 Out of range value for column 's1' at row 1
1653 Warning 1264 Out of range value for column 's20' at row 1
1654 Warning 1264 Out of range value for column 'm' at row 1
1655 Warning 1264 Out of range value for column 'm0' at row 1
1656 Warning 1264 Out of range value for column 'm1' at row 1
1657 Warning 1264 Out of range value for column 'm20' at row 1
1658 Warning 1264 Out of range value for column 'b' at row 1
1659 Warning 1264 Out of range value for column 'b0' at row 1
1660 Warning 1264 Out of range value for column 'b1' at row 1
1661 Warning 1264 Out of range value for column 'b20' at row 1
1662 INSERT INTO t1 (i,i0,i1,i20,t,t0,t1,t20,s,s0,s1,s20,m,m0,m1,m20,b,b0,b1,b20) VALUES (4294967296,4294967296,4294967296,4294967296,256,256,256,256,65536,65536,65536,65536,16777216,16777216,16777216,16777216,18446744073709551616,18446744073709551616,18446744073709551616,18446744073709551616);
1663 Warnings:
1664 Warning 1264 Out of range value for column 'i' at row 1
1665 Warning 1264 Out of range value for column 'i0' at row 1
1666 Warning 1264 Out of range value for column 'i1' at row 1
1667 Warning 1264 Out of range value for column 'i20' at row 1
1668 Warning 1264 Out of range value for column 't' at row 1
1669 Warning 1264 Out of range value for column 't0' at row 1
1670 Warning 1264 Out of range value for column 't1' at row 1
1671 Warning 1264 Out of range value for column 't20' at row 1
1672 Warning 1264 Out of range value for column 's' at row 1
1673 Warning 1264 Out of range value for column 's0' at row 1
1674 Warning 1264 Out of range value for column 's1' at row 1
1675 Warning 1264 Out of range value for column 's20' at row 1
1676 Warning 1264 Out of range value for column 'm' at row 1
1677 Warning 1264 Out of range value for column 'm0' at row 1
1678 Warning 1264 Out of range value for column 'm1' at row 1
1679 Warning 1264 Out of range value for column 'm20' at row 1
1680 Warning 1264 Out of range value for column 'b' at row 1
1681 Warning 1264 Out of range value for column 'b0' at row 1
1682 Warning 1264 Out of range value for column 'b1' at row 1
1683 Warning 1264 Out of range value for column 'b20' at row 1
1684 INSERT INTO t1 (i,i0,i1,i20,t,t0,t1,t20,s,s0,s1,s20,m,m0,m1,m20,b,b0,b1,b20) SELECT b,b,b,b,b,b,b,b,b,b,b,b,b,b,b,b,b,b,b,b FROM t1 WHERE b IN (-9223372036854775808,9223372036854775807,18446744073709551615);
1685 Warnings:
1686 Warning 1264 Out of range value for column 'i' at row 1
1687 Warning 1264 Out of range value for column 'i0' at row 1
1688 Warning 1264 Out of range value for column 'i1' at row 1
1689 Warning 1264 Out of range value for column 'i20' at row 1
1690 Warning 1264 Out of range value for column 't' at row 1
1691 Warning 1264 Out of range value for column 't0' at row 1
1692 Warning 1264 Out of range value for column 't1' at row 1
1693 Warning 1264 Out of range value for column 't20' at row 1
1694 Warning 1264 Out of range value for column 's' at row 1
1695 Warning 1264 Out of range value for column 's0' at row 1
1696 Warning 1264 Out of range value for column 's1' at row 1
1697 Warning 1264 Out of range value for column 's20' at row 1
1698 Warning 1264 Out of range value for column 'm' at row 1
1699 Warning 1264 Out of range value for column 'm0' at row 1
1700 Warning 1264 Out of range value for column 'm1' at row 1
1701 Warning 1264 Out of range value for column 'm20' at row 1
1702 Warning 1264 Out of range value for column 'i' at row 2
1703 Warning 1264 Out of range value for column 'i0' at row 2
1704 Warning 1264 Out of range value for column 'i1' at row 2
1705 Warning 1264 Out of range value for column 'i20' at row 2
1706 Warning 1264 Out of range value for column 't' at row 2
1707 Warning 1264 Out of range value for column 't0' at row 2
1708 Warning 1264 Out of range value for column 't1' at row 2
1709 Warning 1264 Out of range value for column 't20' at row 2
1710 Warning 1264 Out of range value for column 's' at row 2
1711 Warning 1264 Out of range value for column 's0' at row 2
1712 Warning 1264 Out of range value for column 's1' at row 2
1713 Warning 1264 Out of range value for column 's20' at row 2
1714 Warning 1264 Out of range value for column 'm' at row 2
1715 Warning 1264 Out of range value for column 'm0' at row 2
1716 Warning 1264 Out of range value for column 'm1' at row 2
1717 Warning 1264 Out of range value for column 'm20' at row 2
1718 Warning 1264 Out of range value for column 'i' at row 3
1719 Warning 1264 Out of range value for column 'i0' at row 3
1720 Warning 1264 Out of range value for column 'i1' at row 3
1721 Warning 1264 Out of range value for column 'i20' at row 3
1722 Warning 1264 Out of range value for column 't' at row 3
1723 Warning 1264 Out of range value for column 't0' at row 3
1724 Warning 1264 Out of range value for column 't1' at row 3
1725 Warning 1264 Out of range value for column 't20' at row 3
1726 Warning 1264 Out of range value for column 's' at row 3
1727 Warning 1264 Out of range value for column 's0' at row 3
1728 Warning 1264 Out of range value for column 's1' at row 3
1729 Warning 1264 Out of range value for column 's20' at row 3
1730 Warning 1264 Out of range value for column 'm' at row 3
1731 Warning 1264 Out of range value for column 'm0' at row 3
1732 Warning 1264 Out of range value for column 'm1' at row 3
1733 Warning 1264 Out of range value for column 'm20' at row 3
1734 Warning 1264 Out of range value for column 'i' at row 4
1735 Warning 1264 Out of range value for column 'i0' at row 4
1736 Warning 1264 Out of range value for column 'i1' at row 4
1737 Warning 1264 Out of range value for column 'i20' at row 4
1738 Warning 1264 Out of range value for column 't' at row 4
1739 Warning 1264 Out of range value for column 't0' at row 4
1740 Warning 1264 Out of range value for column 't1' at row 4
1741 Warning 1264 Out of range value for column 't20' at row 4
1742 Warning 1264 Out of range value for column 's' at row 4
1743 Warning 1264 Out of range value for column 's0' at row 4
1744 Warning 1264 Out of range value for column 's1' at row 4
1745 Warning 1264 Out of range value for column 's20' at row 4
1746 Warning 1264 Out of range value for column 'm' at row 4
1747 Warning 1264 Out of range value for column 'm0' at row 4
1748 Warning 1264 Out of range value for column 'm1' at row 4
1749 Warning 1264 Out of range value for column 'm20' at row 4
1750 SELECT i,i0,i1,i20,t,t0,t1,t20,s,s0,s1,s20,m,m0,m1,m20,b,b0,b1,b20 FROM t1;
1751 i i0 i1 i20 t t0 t1 t20 s s0 s1 s20 m m0 m1 m20 b b0 b1 b20
1752 -2147483648 -2147483648 -2147483648 -2147483648 -128 -128 -128 -128 -32768 -32768 -32768 -32768 -8388608 -8388608 -8388608 -8388608 -9223372036854775808 -9223372036854775808 -9223372036854775808 -9223372036854775808
1753 -2147483648 -2147483648 -2147483648 -2147483648 -128 -128 -128 -128 -32768 -32768 -32768 -32768 -8388608 -8388608 -8388608 -8388608 -9223372036854775808 -9223372036854775808 -9223372036854775808 -9223372036854775808
1754 -2147483648 -2147483648 -2147483648 -2147483648 -128 -128 -128 -128 -32768 -32768 -32768 -32768 -8388608 -8388608 -8388608 -8388608 -9223372036854775808 -9223372036854775808 -9223372036854775808 -9223372036854775808
1755 -2147483648 -2147483648 -2147483648 -2147483648 -128 -128 -128 -128 -32768 -32768 -32768 -32768 -8388608 -8388608 -8388608 -8388608 -9223372036854775808 -9223372036854775808 -9223372036854775808 -9223372036854775808
1756 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
1757 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20
1758 2147483647 2147483647 2147483647 2147483647 127 127 127 127 32767 32767 32767 32767 8388607 8388607 8388607 8388607 9223372036854775807 9223372036854775807 9223372036854775807 9223372036854775807
1759 2147483647 2147483647 2147483647 2147483647 127 127 127 127 32767 32767 32767 32767 8388607 8388607 8388607 8388607 9223372036854775807 9223372036854775807 9223372036854775807 9223372036854775807
1760 2147483647 2147483647 2147483647 2147483647 127 127 127 127 32767 32767 32767 32767 8388607 8388607 8388607 8388607 9223372036854775807 9223372036854775807 9223372036854775807 9223372036854775807
1761 2147483647 2147483647 2147483647 2147483647 127 127 127 127 32767 32767 32767 32767 8388607 8388607 8388607 8388607 9223372036854775807 9223372036854775807 9223372036854775807 9223372036854775807
1762 2147483647 2147483647 2147483647 2147483647 127 127 127 127 32767 32767 32767 32767 8388607 8388607 8388607 8388607 9223372036854775807 9223372036854775807 9223372036854775807 9223372036854775807
1763 2147483647 2147483647 2147483647 2147483647 127 127 127 127 32767 32767 32767 32767 8388607 8388607 8388607 8388607 9223372036854775807 9223372036854775807 9223372036854775807 9223372036854775807
1764 ALTER TABLE t1 ADD COLUMN i257 INT(257);
1765 ERROR 42000: Display width out of range for 'i257' (max = 255)
1766 DROP TABLE t1;
1767 DROP TABLE IF EXISTS t1;
1768 CREATE TABLE t1 (c INT <CUSTOM_COL_OPTIONS> NOT NULL) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>;
1769 SHOW COLUMNS IN t1;
1770 Field Type Null Key Default Extra
1771 c int(11) NO NULL
1772 INSERT INTO t1 (c) VALUES (NULL);
1773 ERROR 23000: Column 'c' cannot be null
1774 DROP TABLE t1;
1775 CREATE TABLE t1 (c INT <CUSTOM_COL_OPTIONS> NOT NULL,
1776 c2 INT <CUSTOM_COL_OPTIONS> NOT NULL DEFAULT 2147483647
1777 ) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>;
1778 SHOW COLUMNS IN t1;
1779 Field Type Null Key Default Extra
1780 c int(11) NO NULL
1781 c2 int(11) NO 2147483647
1782 ALTER TABLE t1 ADD COLUMN err INT <CUSTOM_COL_OPTIONS> NOT NULL DEFAULT NULL;
1783 ERROR 42000: Invalid default value for 'err'
1784 INSERT INTO t1 (c) VALUES (NULL);
1785 ERROR 23000: Column 'c' cannot be null
1786 SELECT HEX(c), HEX(c2) FROM t1;
1787 HEX(c) HEX(c2)
1788 INSERT INTO t1 (c2) VALUES (NULL);
1789 ERROR 23000: Column 'c2' cannot be null
1790 INSERT INTO t1 (c) VALUES (2147483647);
1791 SELECT COUNT(c), COUNT(c2) FROM t1;
1792 COUNT(c) COUNT(c2)
1793 1 1
1794 DROP TABLE t1;
1795 DROP TABLE IF EXISTS t1;
1796 CREATE TABLE t1 (c TINYINT <CUSTOM_COL_OPTIONS> NOT NULL) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>;
1797 SHOW COLUMNS IN t1;
1798 Field Type Null Key Default Extra
1799 c tinyint(4) NO NULL
1800 INSERT INTO t1 (c) VALUES (NULL);
1801 ERROR 23000: Column 'c' cannot be null
1802 DROP TABLE t1;
1803 CREATE TABLE t1 (c TINYINT <CUSTOM_COL_OPTIONS> NOT NULL,
1804 c2 TINYINT <CUSTOM_COL_OPTIONS> NOT NULL DEFAULT 127
1805 ) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>;
1806 SHOW COLUMNS IN t1;
1807 Field Type Null Key Default Extra
1808 c tinyint(4) NO NULL
1809 c2 tinyint(4) NO 127
1810 ALTER TABLE t1 ADD COLUMN err TINYINT <CUSTOM_COL_OPTIONS> NOT NULL DEFAULT NULL;
1811 ERROR 42000: Invalid default value for 'err'
1812 INSERT INTO t1 (c) VALUES (NULL);
1813 ERROR 23000: Column 'c' cannot be null
1814 SELECT HEX(c), HEX(c2) FROM t1;
1815 HEX(c) HEX(c2)
1816 INSERT INTO t1 (c2) VALUES (NULL);
1817 ERROR 23000: Column 'c2' cannot be null
1818 INSERT INTO t1 (c) VALUES (127 );
1819 SELECT COUNT(c), COUNT(c2) FROM t1;
1820 COUNT(c) COUNT(c2)
1821 1 1
1822 DROP TABLE t1;
1823 DROP TABLE IF EXISTS t1;
1824 CREATE TABLE t1 (c SMALLINT <CUSTOM_COL_OPTIONS> NOT NULL) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>;
1825 SHOW COLUMNS IN t1;
1826 Field Type Null Key Default Extra
1827 c smallint(6) NO NULL
1828 INSERT INTO t1 (c) VALUES (NULL);
1829 ERROR 23000: Column 'c' cannot be null
1830 DROP TABLE t1;
1831 CREATE TABLE t1 (c SMALLINT <CUSTOM_COL_OPTIONS> NOT NULL,
1832 c2 SMALLINT <CUSTOM_COL_OPTIONS> NOT NULL DEFAULT 0
1833 ) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>;
1834 SHOW COLUMNS IN t1;
1835 Field Type Null Key Default Extra
1836 c smallint(6) NO NULL
1837 c2 smallint(6) NO 0
1838 ALTER TABLE t1 ADD COLUMN err SMALLINT <CUSTOM_COL_OPTIONS> NOT NULL DEFAULT NULL;
1839 ERROR 42000: Invalid default value for 'err'
1840 INSERT INTO t1 (c) VALUES (NULL);
1841 ERROR 23000: Column 'c' cannot be null
1842 SELECT HEX(c), HEX(c2) FROM t1;
1843 HEX(c) HEX(c2)
1844 INSERT INTO t1 (c2) VALUES (NULL);
1845 ERROR 23000: Column 'c2' cannot be null
1846 INSERT INTO t1 (c) VALUES (0);
1847 SELECT COUNT(c), COUNT(c2) FROM t1;
1848 COUNT(c) COUNT(c2)
1849 1 1
1850 DROP TABLE t1;
1851 DROP TABLE IF EXISTS t1;
1852 CREATE TABLE t1 (c MEDIUMINT <CUSTOM_COL_OPTIONS> NOT NULL) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>;
1853 SHOW COLUMNS IN t1;
1854 Field Type Null Key Default Extra
1855 c mediumint(9) NO NULL
1856 INSERT INTO t1 (c) VALUES (NULL);
1857 ERROR 23000: Column 'c' cannot be null
1858 DROP TABLE t1;
1859 CREATE TABLE t1 (c MEDIUMINT <CUSTOM_COL_OPTIONS> NOT NULL,
1860 c2 MEDIUMINT <CUSTOM_COL_OPTIONS> NOT NULL DEFAULT 1
1861 ) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>;
1862 SHOW COLUMNS IN t1;
1863 Field Type Null Key Default Extra
1864 c mediumint(9) NO NULL
1865 c2 mediumint(9) NO 1
1866 ALTER TABLE t1 ADD COLUMN err MEDIUMINT <CUSTOM_COL_OPTIONS> NOT NULL DEFAULT NULL;
1867 ERROR 42000: Invalid default value for 'err'
1868 INSERT INTO t1 (c) VALUES (NULL);
1869 ERROR 23000: Column 'c' cannot be null
1870 SELECT HEX(c), HEX(c2) FROM t1;
1871 HEX(c) HEX(c2)
1872 INSERT INTO t1 (c2) VALUES (NULL);
1873 ERROR 23000: Column 'c2' cannot be null
1874 INSERT INTO t1 (c) VALUES (1);
1875 SELECT COUNT(c), COUNT(c2) FROM t1;
1876 COUNT(c) COUNT(c2)
1877 1 1
1878 DROP TABLE t1;
1879 DROP TABLE IF EXISTS t1;
1880 CREATE TABLE t1 (c BIGINT <CUSTOM_COL_OPTIONS> NOT NULL) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>;
1881 SHOW COLUMNS IN t1;
1882 Field Type Null Key Default Extra
1883 c bigint(20) NO NULL
1884 INSERT INTO t1 (c) VALUES (NULL);
1885 ERROR 23000: Column 'c' cannot be null
1886 DROP TABLE t1;
1887 CREATE TABLE t1 (c BIGINT <CUSTOM_COL_OPTIONS> NOT NULL,
1888 c2 BIGINT <CUSTOM_COL_OPTIONS> NOT NULL DEFAULT 9223372036854775807
1889 ) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>;
1890 SHOW COLUMNS IN t1;
1891 Field Type Null Key Default Extra
1892 c bigint(20) NO NULL
1893 c2 bigint(20) NO 9223372036854775807
1894 ALTER TABLE t1 ADD COLUMN err BIGINT <CUSTOM_COL_OPTIONS> NOT NULL DEFAULT NULL;
1895 ERROR 42000: Invalid default value for 'err'
1896 INSERT INTO t1 (c) VALUES (NULL);
1897 ERROR 23000: Column 'c' cannot be null
1898 SELECT HEX(c), HEX(c2) FROM t1;
1899 HEX(c) HEX(c2)
1900 INSERT INTO t1 (c2) VALUES (NULL);
1901 ERROR 23000: Column 'c2' cannot be null
1902 INSERT INTO t1 (c) VALUES (9223372036854775807);
1903 SELECT COUNT(c), COUNT(c2) FROM t1;
1904 COUNT(c) COUNT(c2)
1905 1 1
1906 DROP TABLE t1;
1907 #
1908 # SET columns
1909 #
1910 DROP TABLE IF EXISTS t1;
1911 CREATE TABLE t1 (a SET('') <CUSTOM_COL_OPTIONS> NOT NULL,
1912 b SET('test1','test2','test3','test4','test5') <CUSTOM_COL_OPTIONS> NOT NULL,
1913 c SET('01','02','03','04','05','06','07','08','09','10','11','12','13','14','15','16','17','18','19','20','21','22','23','24','25','26','27','28','29','30','31','32','33','34','35','36','37','38','39','40','41','42','43','44','45','46','47','48','49','50''51','52','53','54','55','56','57','58','59','60','61','62','63','64') <CUSTOM_COL_OPTIONS> NOT NULL
1914 ) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>;
1915 SHOW COLUMNS IN t1;
1916 Field Type Null Key Default Extra
1917 a set('') # # #
1918 b set('test1','test2','test3','test4','test5') # # #
1919 c set('01','02','03','04','05','06','07','08','09','10','11','12','13','14','15','16','17','18','19','20','21','22','23','24','25','26','27','28','29','30','31','32','33','34','35','36','37','38','39','40','41','42','43','44','45','46','47','48','49','50''51','52','53','54','55','56','57','58','59','60','61','62','63','64') # # #
1920 INSERT INTO t1 (a,b,c) VALUES
1921 ('','test2,test3','01,34,44,,23'),
1922 ('',5,2),
1923 (',','test4,test2','');
1924 Warnings:
1925 Warning 1265 Data truncated for column 'c' at row 1
1926 SELECT a,b,c FROM t1;
1927 a b c
1928 test1,test3 02
1929 test2,test3 01,23,34,44
1930 test2,test4
1931 INSERT INTO t1 (a,b,c) VALUES (0,'test6',-1);
1932 Warnings:
1933 Warning 1265 Data truncated for column 'b' at row 1
1934 Warning 1265 Data truncated for column 'c' at row 1
1935 SELECT a,b,c FROM t1;
1936 a b c
1937 01,02,03,04,05,06,07,08,09,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50'51,52,53,54,55,56,57,58,59,60,61,62,63,64
1938 test1,test3 02
1939 test2,test3 01,23,34,44
1940 test2,test4
1941 ALTER TABLE t1 ADD COLUMN e SET('a','A') <CUSTOM_COL_OPTIONS> NOT NULL;
1942 Warnings:
1943 Note 1291 Column 'e' has duplicated value 'a' in SET
1944 SHOW COLUMNS IN t1;
1945 Field Type Null Key Default Extra
1946 a set('') # # #
1947 b set('test1','test2','test3','test4','test5') # # #
1948 c set('01','02','03','04','05','06','07','08','09','10','11','12','13','14','15','16','17','18','19','20','21','22','23','24','25','26','27','28','29','30','31','32','33','34','35','36','37','38','39','40','41','42','43','44','45','46','47','48','49','50''51','52','53','54','55','56','57','58','59','60','61','62','63','64') # # #
1949 e set('a','A') # # #
1950 ALTER TABLE t1 ADD COLUMN f SET('1','2','3','4','5','6','7','8','9','a','b','c','d','e','f','g','h','i','j','k','l','m','n','o','p','q','r','s','t','u','v','w','x','y','z',' ','11','12','13','14','15','16','17','18','19','1a','1b','1c','1d','1e','1f','1g','1h','1i','1j','1k','1l','1m','1n','1o','1p','1q','1r','1s','1t','1u','1v','1w','1x','1y','1z','20','21','22','23','24','25','26','27','28','29','2a','2b','2c','2d','2e','2f','2g','2h','2i','2j','2k','2l','2m','2n','2o','2p','2q','2r','2s','2t','2u','2v','2w','2x','2y','2z','30','31','32','33','34','35','36','37','38','39','3a','3b','3c','3d','3e','3f','3g','3h','3i') <CUSTOM_COL_OPTIONS> NOT NULL;
1951 ERROR HY000: Too many strings for column f and SET
1952 SELECT a,b,c,e FROM t1 WHERE FIND_IN_SET('test2',b)>0 OR a != '';
1953 a b c e
1954 test2,test3 01,23,34,44
1955 test2,test4
1956 DROP TABLE t1;
1957 DROP TABLE IF EXISTS t1;
1958 CREATE TABLE t1 (c SET('test1','test2','test3') <CUSTOM_COL_OPTIONS> NOT NULL) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>;
1959 SHOW COLUMNS IN t1;
1960 Field Type Null Key Default Extra
1961 c set('test1','test2','test3') NO NULL
1962 INSERT INTO t1 (c) VALUES (NULL);
1963 ERROR 23000: Column 'c' cannot be null
1964 DROP TABLE t1;
1965 CREATE TABLE t1 (c SET('test1','test2','test3') <CUSTOM_COL_OPTIONS> NOT NULL,
1966 c2 SET('test1','test2','test3') <CUSTOM_COL_OPTIONS> NOT NULL DEFAULT 'test2,test3'
1967 ) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>;
1968 SHOW COLUMNS IN t1;
1969 Field Type Null Key Default Extra
1970 c set('test1','test2','test3') NO NULL
1971 c2 set('test1','test2','test3') NO test2,test3
1972 ALTER TABLE t1 ADD COLUMN err SET('test1','test2','test3') <CUSTOM_COL_OPTIONS> NOT NULL DEFAULT NULL;
1973 ERROR 42000: Invalid default value for 'err'
1974 INSERT INTO t1 (c) VALUES (NULL);
1975 ERROR 23000: Column 'c' cannot be null
1976 SELECT HEX(c), HEX(c2) FROM t1;
1977 HEX(c) HEX(c2)
1978 INSERT INTO t1 (c2) VALUES (NULL);
1979 ERROR 23000: Column 'c2' cannot be null
1980 INSERT INTO t1 (c) VALUES ('test2,test3');
1981 SELECT COUNT(c), COUNT(c2) FROM t1;
1982 COUNT(c) COUNT(c2)
1983 1 1
1984 DROP TABLE t1;
1985 #
1986 # TEXT columns
1987 #
1988 DROP TABLE IF EXISTS t1;
1989 CREATE TABLE t1 (t TEXT <CUSTOM_COL_OPTIONS> NOT NULL,
1990 t0 TEXT(0) <CUSTOM_COL_OPTIONS> NOT NULL,
1991 t1 TEXT(1) <CUSTOM_COL_OPTIONS> NOT NULL,
1992 t300 TEXT(300) <CUSTOM_COL_OPTIONS> NOT NULL,
1993 tm TEXT(65535) <CUSTOM_COL_OPTIONS> NOT NULL,
1994 t70k TEXT(70000) <CUSTOM_COL_OPTIONS> NOT NULL,
1995 t17m TEXT(17000000) <CUSTOM_COL_OPTIONS> NOT NULL,
1996 tt TINYTEXT <CUSTOM_COL_OPTIONS> NOT NULL,
1997 m MEDIUMTEXT <CUSTOM_COL_OPTIONS> NOT NULL,
1998 l LONGTEXT <CUSTOM_COL_OPTIONS> NOT NULL
1999 ) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>;
2000 SHOW COLUMNS IN t1;
2001 Field Type Null Key Default Extra
2002 t text # # #
2003 t0 text # # #
2004 t1 tinytext # # #
2005 t300 text # # #
2006 tm text # # #
2007 t70k mediumtext # # #
2008 t17m longtext # # #
2009 tt tinytext # # #
2010 m mediumtext # # #
2011 l longtext # # #
2012 INSERT INTO t1 (t,t0,t1,t300,tm,t70k,t17m,tt,m,l) VALUES
2013 ('','','','','','','','','',''),
2014 ('a','b','c','d','e','f','g','h','i','j'),
2015 ('test1','test2','test3','test4','test5','test6','test7','test8','test9','test10'),
2016 ( REPEAT('a',65535), REPEAT('b',65535), REPEAT('c',255), REPEAT('d',65535), REPEAT('e',65535), REPEAT('f',1048576), REPEAT('g',1048576), REPEAT('h',255), REPEAT('i',1048576), REPEAT('j',1048576) );
2017 SELECT LENGTH(t), LENGTH(t0), LENGTH(t1), LENGTH(t300), LENGTH(tm), LENGTH(t70k), LENGTH(t17m), LENGTH(tt), LENGTH(m), LENGTH(l) FROM t1;
2018 LENGTH(t) LENGTH(t0) LENGTH(t1) LENGTH(t300) LENGTH(tm) LENGTH(t70k) LENGTH(t17m) LENGTH(tt) LENGTH(m) LENGTH(l)
2019 0 0 0 0 0 0 0 0 0 0
2020 1 1 1 1 1 1 1 1 1 1
2021 5 5 5 5 5 5 5 5 5 6
2022 65535 65535 255 65535 65535 1048576 1048576 255 1048576 1048576
2023 INSERT INTO t1 (t,t0,t1,t300,tm,t70k,t17m,tt,m,l) VALUES
2024 ( REPEAT('a',65536), REPEAT('b',65536), REPEAT('c',256), REPEAT('d',65536), REPEAT('e',65536), REPEAT('f',1048576), REPEAT('g',1048576), REPEAT('h',256), REPEAT('i',1048576), REPEAT('j',1048576) );
2025 Warnings:
2026 Warning 1265 Data truncated for column 't' at row 1
2027 Warning 1265 Data truncated for column 't0' at row 1
2028 Warning 1265 Data truncated for column 't1' at row 1
2029 Warning 1265 Data truncated for column 't300' at row 1
2030 Warning 1265 Data truncated for column 'tm' at row 1
2031 Warning 1265 Data truncated for column 'tt' at row 1
2032 SELECT LENGTH(t), LENGTH(t0), LENGTH(t1), LENGTH(t300), LENGTH(tm), LENGTH(t70k), LENGTH(t17m), LENGTH(tt), LENGTH(m), LENGTH(l) FROM t1;
2033 LENGTH(t) LENGTH(t0) LENGTH(t1) LENGTH(t300) LENGTH(tm) LENGTH(t70k) LENGTH(t17m) LENGTH(tt) LENGTH(m) LENGTH(l)
2034 0 0 0 0 0 0 0 0 0 0
2035 1 1 1 1 1 1 1 1 1 1
2036 5 5 5 5 5 5 5 5 5 6
2037 65535 65535 255 65535 65535 1048576 1048576 255 1048576 1048576
2038 65535 65535 255 65535 65535 1048576 1048576 255 1048576 1048576
2039 ALTER TABLE t1 ADD COLUMN ttt TEXT(4294967296);
2040 ERROR 42000: Display width out of range for 'ttt' (max = 4294967295)
2041 DROP TABLE t1;
2042 DROP TABLE IF EXISTS t1;
2043 CREATE TABLE t1 (c TEXT <CUSTOM_COL_OPTIONS> NOT NULL) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>;
2044 SHOW COLUMNS IN t1;
2045 Field Type Null Key Default Extra
2046 c text NO NULL
2047 INSERT INTO t1 (c) VALUES (NULL);
2048 ERROR 23000: Column 'c' cannot be null
2049 DROP TABLE t1;
2050 DROP TABLE IF EXISTS t1;
2051 CREATE TABLE t1 (c TINYTEXT <CUSTOM_COL_OPTIONS> NOT NULL) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>;
2052 SHOW COLUMNS IN t1;
2053 Field Type Null Key Default Extra
2054 c tinytext NO NULL
2055 INSERT INTO t1 (c) VALUES (NULL);
2056 ERROR 23000: Column 'c' cannot be null
2057 DROP TABLE t1;
2058 DROP TABLE IF EXISTS t1;
2059 CREATE TABLE t1 (c MEDIUMTEXT <CUSTOM_COL_OPTIONS> NOT NULL) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>;
2060 SHOW COLUMNS IN t1;
2061 Field Type Null Key Default Extra
2062 c mediumtext NO NULL
2063 INSERT INTO t1 (c) VALUES (NULL);
2064 ERROR 23000: Column 'c' cannot be null
2065 DROP TABLE t1;
2066 DROP TABLE IF EXISTS t1;
2067 CREATE TABLE t1 (c LONGTEXT <CUSTOM_COL_OPTIONS> NOT NULL) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>;
2068 SHOW COLUMNS IN t1;
2069 Field Type Null Key Default Extra
2070 c longtext NO NULL
2071 INSERT INTO t1 (c) VALUES (NULL);
2072 ERROR 23000: Column 'c' cannot be null
2073 DROP TABLE t1;