MariaDB

Merge lp:~paul-mccullagh/maria/maria-pbxt into lp:~maria-captains/maria/5.1-converting

  1. maria-pbxt
  2. Merge into 5.1-converting
Proposed by Paul McCullagh
Status: Merged
Merged at revision: not available
Proposed branch: lp:~paul-mccullagh/maria/maria-pbxt
Merge into: lp:~maria-captains/maria/5.1-converting
Diff against target: None lines
To merge this branch: bzr merge lp:~paul-mccullagh/maria/maria-pbxt
Reviewer Review Type Date Requested Status
Maria-captains Pending
Review via email: mp+5190@code.launchpad.net
To post a comment you must log in.
Revision history for this message
Paul McCullagh (paul-mccullagh) wrote :

This branch includes the PBXT storage engine. Only changes are the addition of a storage/pbxt and a mysql-test/suite/pbxt directory.

The mysql-test/suite/pbxt contains a test suite for PBXT. The test includes 167 test files. All tests run through without error. To run the tests use:

./mysql-test-run --mysqld=--default-storage-engine=pbxt --suite=pbxt

PBXT only creates files when the first PBXT table is created. So if not used, it has no disk footprint.

Preview Diff

[H/L] Next/Prev Comment, [J/K] Next/Prev File, [N/P] Next/Prev Hunk
1=== added directory 'mysql-test/suite/pbxt'
2=== added directory 'mysql-test/suite/pbxt/r'
3=== added file 'mysql-test/suite/pbxt/r/alias.result'
4--- mysql-test/suite/pbxt/r/alias.result 1970-01-01 00:00:00 +0000
5+++ mysql-test/suite/pbxt/r/alias.result 2009-04-02 10:03:14 +0000
6@@ -0,0 +1,75 @@
7+DROP TABLE IF EXISTS t1;
8+CREATE TABLE t1 (
9+cont_nr int(11) NOT NULL auto_increment,
10+ver_nr int(11) NOT NULL default '0',
11+aufnr int(11) NOT NULL default '0',
12+username varchar(50) NOT NULL default '',
13+hdl_nr int(11) NOT NULL default '0',
14+eintrag date NOT NULL default '0000-00-00',
15+st_klasse varchar(40) NOT NULL default '',
16+st_wert varchar(40) NOT NULL default '',
17+st_zusatz varchar(40) NOT NULL default '',
18+st_bemerkung varchar(255) NOT NULL default '',
19+kunden_art varchar(40) NOT NULL default '',
20+mcbs_knr int(11) default NULL,
21+mcbs_aufnr int(11) NOT NULL default '0',
22+schufa_status char(1) default '?',
23+bemerkung text,
24+wirknetz text,
25+wf_igz int(11) NOT NULL default '0',
26+tarifcode varchar(80) default NULL,
27+recycle char(1) default NULL,
28+sim varchar(30) default NULL,
29+mcbs_tpl varchar(30) default NULL,
30+emp_nr int(11) NOT NULL default '0',
31+laufzeit int(11) default NULL,
32+hdl_name varchar(30) default NULL,
33+prov_hdl_nr int(11) NOT NULL default '0',
34+auto_wirknetz varchar(50) default NULL,
35+auto_billing varchar(50) default NULL,
36+touch timestamp NOT NULL,
37+kategorie varchar(50) default NULL,
38+kundentyp varchar(20) NOT NULL default '',
39+sammel_rech_msisdn varchar(30) NOT NULL default '',
40+p_nr varchar(9) NOT NULL default '',
41+suffix char(3) NOT NULL default '',
42+PRIMARY KEY (cont_nr),
43+KEY idx_aufnr(aufnr),
44+KEY idx_hdl_nr(hdl_nr),
45+KEY idx_st_klasse(st_klasse),
46+KEY ver_nr(ver_nr),
47+KEY eintrag_idx(eintrag),
48+KEY emp_nr_idx(emp_nr),
49+KEY wf_igz(wf_igz),
50+KEY touch(touch),
51+KEY hdl_tag(eintrag,hdl_nr),
52+KEY prov_hdl_nr(prov_hdl_nr),
53+KEY mcbs_aufnr(mcbs_aufnr),
54+KEY kundentyp(kundentyp),
55+KEY p_nr(p_nr,suffix)
56+) ENGINE=MyISAM;
57+INSERT INTO t1 VALUES (3359356,405,3359356,'Mustermann Musterfrau',52500,'2000-05-20','workflow','Auftrag erledigt','Originalvertrag eingegangen und geprüft','','privat',1485525,2122316,'+','','N',1909160,'MobilComSuper92000D2',NULL,NULL,'MS9ND2',3,24,'MobilCom Shop Koeln',52500,NULL,'auto',20010202105916,'Mobilfunk','PP','','','');
58+INSERT INTO t1 VALUES (3359357,468,3359357,'Mustermann Musterfrau',7001,'2000-05-20','workflow','Auftrag erledigt','Originalvertrag eingegangen und geprüft','','privat',1503580,2139699,'+','','P',1909171,'MobilComSuper9D1T10SFreisprech(Akquise)',NULL,NULL,'MS9NS1',327,24,'MobilCom Intern',7003,NULL,'auto',20010202105916,'Mobilfunk','PP','','','');
59+INSERT INTO t1 VALUES (3359358,407,3359358,'Mustermann Musterfrau',7001,'2000-05-20','workflow','Auftrag erledigt','Originalvertrag eingegangen und geprüft','','privat',1501358,2137473,'N','','N',1909159,'MobilComSuper92000D2',NULL,NULL,'MS9ND2',325,24,'MobilCom Intern',7003,NULL,'auto',20010202105916,'Mobilfunk','PP','','','');
60+INSERT INTO t1 VALUES (3359359,468,3359359,'Mustermann Musterfrau',7001,'2000-05-20','workflow','Auftrag erledigt','Originalvertrag eingegangen und geprüft','','privat',1507831,2143894,'+','','P',1909162,'MobilComSuper9D1T10SFreisprech(Akquise)',NULL,NULL,'MS9NS1',327,24,'MobilCom Intern',7003,NULL,'auto',20010202105916,'Mobilfunk','PP','','','');
61+INSERT INTO t1 VALUES (3359360,0,0,'Mustermann Musterfrau',29674907,'2000-05-20','workflow','Auftrag erledigt','Originalvertrag eingegangen und geprüft','','privat',1900169997,2414578,'+',NULL,'N',1909148,'',NULL,NULL,'RV99066_2',20,NULL,'POS',29674907,NULL,NULL,20010202105916,'Mobilfunk','','','97317481','007');
62+INSERT INTO t1 VALUES (3359361,406,3359361,'Mustermann Musterfrau',7001,'2000-05-20','workflow','Auftrag storniert','','(7001-84):Storno, Kd. möchte nicht mehr','privat',NULL,0,'+','','P',1909150,'MobilComSuper92000D1(Akquise)',NULL,NULL,'MS9ND1',325,24,'MobilCom Intern',7003,NULL,'auto',20010202105916,'Mobilfunk','PP','','','');
63+INSERT INTO t1 VALUES (3359362,406,3359362,'Mustermann Musterfrau',7001,'2000-05-20','workflow','Auftrag erledigt','Originalvertrag eingegangen und geprüft','','privat',1509984,2145874,'+','','P',1909154,'MobilComSuper92000D1(Akquise)',NULL,NULL,'MS9ND1',327,24,'MobilCom Intern',7003,NULL,'auto',20010202105916,'Mobilfunk','PP','','','');
64+SELECT ELT(FIELD(kundentyp,'PP','PPA','PG','PGA','FK','FKA','FP','FPA','K','KA','V','VA',''), 'Privat (Private Nutzung)','Privat (Private Nutzung) Sitz im Ausland','Privat (geschaeftliche Nutzung)','Privat (geschaeftliche Nutzung) Sitz im Ausland','Firma (Kapitalgesellschaft)','Firma (Kapitalgesellschaft) Sitz im Ausland','Firma (Personengesellschaft)','Firma (Personengesellschaft) Sitz im Ausland','oeff. rechtl. Koerperschaft','oeff. rechtl. Koerperschaft Sitz im Ausland','Eingetragener Verein','Eingetragener Verein Sitz im Ausland','Typ unbekannt') AS Kundentyp ,kategorie FROM t1 WHERE hdl_nr < 2000000 AND kategorie IN ('Prepaid','Mobilfunk') AND st_klasse = 'Workflow' GROUP BY kundentyp ORDER BY kategorie;
65+Kundentyp kategorie
66+Privat (Private Nutzung) Mobilfunk
67+Warnings:
68+Warning 1052 Column 'kundentyp' in group statement is ambiguous
69+drop table t1;
70+CREATE TABLE t1 (
71+AUFNR varchar(12) NOT NULL default '',
72+PLNFL varchar(6) NOT NULL default '',
73+VORNR varchar(4) NOT NULL default '',
74+xstatus_vor smallint(5) unsigned NOT NULL default '0'
75+);
76+INSERT INTO t1 VALUES ('40004712','000001','0010',9);
77+INSERT INTO t1 VALUES ('40004712','000001','0020',0);
78+UPDATE t1 SET t1.xstatus_vor = Greatest(t1.xstatus_vor,1) WHERE t1.aufnr =
79+"40004712" AND t1.plnfl = "000001" AND t1.vornr > "0010" ORDER BY t1.vornr
80+ASC LIMIT 1;
81+drop table t1;
82
83=== added file 'mysql-test/suite/pbxt/r/alter_table.result'
84--- mysql-test/suite/pbxt/r/alter_table.result 1970-01-01 00:00:00 +0000
85+++ mysql-test/suite/pbxt/r/alter_table.result 2009-04-02 10:03:14 +0000
86@@ -0,0 +1,913 @@
87+drop table if exists t1,t2;
88+drop database if exists mysqltest;
89+create table t1 (
90+col1 int not null auto_increment primary key,
91+col2 varchar(30) not null,
92+col3 varchar (20) not null,
93+col4 varchar(4) not null,
94+col5 enum('PENDING', 'ACTIVE', 'DISABLED') not null,
95+col6 int not null, to_be_deleted int);
96+insert into t1 values (2,4,3,5,"PENDING",1,7);
97+alter table t1
98+add column col4_5 varchar(20) not null after col4,
99+add column col7 varchar(30) not null after col5,
100+add column col8 datetime not null, drop column to_be_deleted,
101+change column col2 fourth varchar(30) not null after col3,
102+modify column col6 int not null first;
103+select * from t1;
104+col6 col1 col3 fourth col4 col4_5 col5 col7 col8
105+1 2 3 4 5 PENDING 0000-00-00 00:00:00
106+drop table t1;
107+create table t1 (bandID MEDIUMINT UNSIGNED NOT NULL PRIMARY KEY, payoutID SMALLINT UNSIGNED NOT NULL);
108+insert into t1 (bandID,payoutID) VALUES (1,6),(2,6),(3,4),(4,9),(5,10),(6,1),(7,12),(8,12);
109+alter table t1 add column new_col int, order by payoutid,bandid;
110+select * from t1;
111+bandID payoutID new_col
112+6 1 NULL
113+3 4 NULL
114+1 6 NULL
115+2 6 NULL
116+4 9 NULL
117+5 10 NULL
118+7 12 NULL
119+8 12 NULL
120+alter table t1 order by bandid,payoutid;
121+select * from t1;
122+bandID payoutID new_col
123+1 6 NULL
124+2 6 NULL
125+3 4 NULL
126+4 9 NULL
127+5 10 NULL
128+6 1 NULL
129+7 12 NULL
130+8 12 NULL
131+drop table t1;
132+CREATE TABLE t1 (
133+GROUP_ID int(10) unsigned DEFAULT '0' NOT NULL,
134+LANG_ID smallint(5) unsigned DEFAULT '0' NOT NULL,
135+NAME varchar(80) DEFAULT '' NOT NULL,
136+PRIMARY KEY (GROUP_ID,LANG_ID),
137+KEY NAME (NAME));
138+ALTER TABLE t1 CHANGE NAME NAME CHAR(80) not null;
139+SHOW FULL COLUMNS FROM t1;
140+Field Type Collation Null Key Default Extra Privileges Comment
141+GROUP_ID int(10) unsigned NULL NO PRI 0 #
142+LANG_ID smallint(5) unsigned NULL NO PRI 0 #
143+NAME char(80) latin1_swedish_ci NO MUL NULL #
144+DROP TABLE t1;
145+create table t1 (n int);
146+insert into t1 values(9),(3),(12),(10);
147+alter table t1 order by n;
148+select * from t1;
149+n
150+3
151+9
152+10
153+12
154+drop table t1;
155+CREATE TABLE t1 (
156+id int(11) unsigned NOT NULL default '0',
157+category_id tinyint(4) unsigned NOT NULL default '0',
158+type_id tinyint(4) unsigned NOT NULL default '0',
159+body text NOT NULL,
160+user_id int(11) unsigned NOT NULL default '0',
161+status enum('new','old') NOT NULL default 'new',
162+PRIMARY KEY (id)
163+) ENGINE=MyISAM;
164+ALTER TABLE t1 ORDER BY t1.id, t1.status, t1.type_id, t1.user_id, t1.body;
165+DROP TABLE t1;
166+CREATE TABLE t1 (AnamneseId int(10) unsigned NOT NULL auto_increment,B BLOB,PRIMARY KEY (AnamneseId)) engine=myisam;
167+insert into t1 values (null,"hello");
168+LOCK TABLES t1 WRITE;
169+ALTER TABLE t1 ADD Column new_col int not null;
170+UNLOCK TABLES;
171+OPTIMIZE TABLE t1;
172+Table Op Msg_type Msg_text
173+test.t1 optimize status OK
174+DROP TABLE t1;
175+create table t1 (i int unsigned not null auto_increment primary key);
176+insert into t1 values (null),(null),(null),(null);
177+alter table t1 drop i,add i int unsigned not null auto_increment, drop primary key, add primary key (i);
178+select * from t1;
179+i
180+1
181+2
182+3
183+4
184+drop table t1;
185+create table t1 (name char(15));
186+insert into t1 (name) values ("current");
187+create database mysqltest;
188+create table mysqltest.t1 (name char(15));
189+insert into mysqltest.t1 (name) values ("mysqltest");
190+select * from t1;
191+name
192+current
193+select * from mysqltest.t1;
194+name
195+mysqltest
196+alter table t1 rename mysqltest.t1;
197+ERROR 42S01: Table 't1' already exists
198+select * from t1;
199+name
200+current
201+select * from mysqltest.t1;
202+name
203+mysqltest
204+drop table t1;
205+drop database mysqltest;
206+create table t1 (n1 int not null, n2 int, n3 int, n4 float,
207+unique(n1),
208+key (n1, n2, n3, n4),
209+key (n2, n3, n4, n1),
210+key (n3, n4, n1, n2),
211+key (n4, n1, n2, n3) );
212+alter table t1 disable keys;
213+show keys from t1;
214+Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment
215+t1 0 n1 1 n1 NULL 0 NULL NULL BTREE
216+t1 1 n1_2 1 n1 NULL NULL NULL NULL BTREE
217+t1 1 n1_2 2 n2 NULL NULL NULL NULL YES BTREE
218+t1 1 n1_2 3 n3 NULL NULL NULL NULL YES BTREE
219+t1 1 n1_2 4 n4 NULL NULL NULL NULL YES BTREE
220+t1 1 n2 1 n2 NULL NULL NULL NULL YES BTREE
221+t1 1 n2 2 n3 NULL NULL NULL NULL YES BTREE
222+t1 1 n2 3 n4 NULL NULL NULL NULL YES BTREE
223+t1 1 n2 4 n1 NULL NULL NULL NULL BTREE
224+t1 1 n3 1 n3 NULL NULL NULL NULL YES BTREE
225+t1 1 n3 2 n4 NULL NULL NULL NULL YES BTREE
226+t1 1 n3 3 n1 NULL NULL NULL NULL BTREE
227+t1 1 n3 4 n2 NULL NULL NULL NULL YES BTREE
228+t1 1 n4 1 n4 NULL NULL NULL NULL YES BTREE
229+t1 1 n4 2 n1 NULL NULL NULL NULL BTREE
230+t1 1 n4 3 n2 NULL NULL NULL NULL YES BTREE
231+t1 1 n4 4 n3 NULL NULL NULL NULL YES BTREE
232+insert into t1 values(10,RAND()*1000,RAND()*1000,RAND());
233+insert into t1 values(9,RAND()*1000,RAND()*1000,RAND());
234+insert into t1 values(8,RAND()*1000,RAND()*1000,RAND());
235+insert into t1 values(7,RAND()*1000,RAND()*1000,RAND());
236+insert into t1 values(6,RAND()*1000,RAND()*1000,RAND());
237+insert into t1 values(5,RAND()*1000,RAND()*1000,RAND());
238+insert into t1 values(4,RAND()*1000,RAND()*1000,RAND());
239+insert into t1 values(3,RAND()*1000,RAND()*1000,RAND());
240+insert into t1 values(2,RAND()*1000,RAND()*1000,RAND());
241+insert into t1 values(1,RAND()*1000,RAND()*1000,RAND());
242+alter table t1 enable keys;
243+show keys from t1;
244+Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment
245+t1 0 n1 1 n1 NULL 10 NULL NULL BTREE
246+t1 1 n1_2 1 n1 NULL NULL NULL NULL BTREE
247+t1 1 n1_2 2 n2 NULL NULL NULL NULL YES BTREE
248+t1 1 n1_2 3 n3 NULL NULL NULL NULL YES BTREE
249+t1 1 n1_2 4 n4 NULL NULL NULL NULL YES BTREE
250+t1 1 n2 1 n2 NULL NULL NULL NULL YES BTREE
251+t1 1 n2 2 n3 NULL NULL NULL NULL YES BTREE
252+t1 1 n2 3 n4 NULL NULL NULL NULL YES BTREE
253+t1 1 n2 4 n1 NULL NULL NULL NULL BTREE
254+t1 1 n3 1 n3 NULL NULL NULL NULL YES BTREE
255+t1 1 n3 2 n4 NULL NULL NULL NULL YES BTREE
256+t1 1 n3 3 n1 NULL NULL NULL NULL BTREE
257+t1 1 n3 4 n2 NULL NULL NULL NULL YES BTREE
258+t1 1 n4 1 n4 NULL NULL NULL NULL YES BTREE
259+t1 1 n4 2 n1 NULL NULL NULL NULL BTREE
260+t1 1 n4 3 n2 NULL NULL NULL NULL YES BTREE
261+t1 1 n4 4 n3 NULL NULL NULL NULL YES BTREE
262+drop table t1;
263+create table t1 (i int unsigned not null auto_increment primary key);
264+alter table t1 rename t2;
265+alter table t2 rename t1, add c char(10) comment "no comment";
266+show columns from t1;
267+Field Type Null Key Default Extra
268+i int(10) unsigned NO PRI NULL auto_increment
269+c char(10) YES NULL
270+drop table t1;
271+create table t1 (a int, b int);
272+insert into t1 values(1,100), (2,100), (3, 100);
273+insert into t1 values(1,99), (2,99), (3, 99);
274+insert into t1 values(1,98), (2,98), (3, 98);
275+insert into t1 values(1,97), (2,97), (3, 97);
276+insert into t1 values(1,96), (2,96), (3, 96);
277+insert into t1 values(1,95), (2,95), (3, 95);
278+insert into t1 values(1,94), (2,94), (3, 94);
279+insert into t1 values(1,93), (2,93), (3, 93);
280+insert into t1 values(1,92), (2,92), (3, 92);
281+insert into t1 values(1,91), (2,91), (3, 91);
282+insert into t1 values(1,90), (2,90), (3, 90);
283+insert into t1 values(1,89), (2,89), (3, 89);
284+insert into t1 values(1,88), (2,88), (3, 88);
285+insert into t1 values(1,87), (2,87), (3, 87);
286+insert into t1 values(1,86), (2,86), (3, 86);
287+insert into t1 values(1,85), (2,85), (3, 85);
288+insert into t1 values(1,84), (2,84), (3, 84);
289+insert into t1 values(1,83), (2,83), (3, 83);
290+insert into t1 values(1,82), (2,82), (3, 82);
291+insert into t1 values(1,81), (2,81), (3, 81);
292+insert into t1 values(1,80), (2,80), (3, 80);
293+insert into t1 values(1,79), (2,79), (3, 79);
294+insert into t1 values(1,78), (2,78), (3, 78);
295+insert into t1 values(1,77), (2,77), (3, 77);
296+insert into t1 values(1,76), (2,76), (3, 76);
297+insert into t1 values(1,75), (2,75), (3, 75);
298+insert into t1 values(1,74), (2,74), (3, 74);
299+insert into t1 values(1,73), (2,73), (3, 73);
300+insert into t1 values(1,72), (2,72), (3, 72);
301+insert into t1 values(1,71), (2,71), (3, 71);
302+insert into t1 values(1,70), (2,70), (3, 70);
303+insert into t1 values(1,69), (2,69), (3, 69);
304+insert into t1 values(1,68), (2,68), (3, 68);
305+insert into t1 values(1,67), (2,67), (3, 67);
306+insert into t1 values(1,66), (2,66), (3, 66);
307+insert into t1 values(1,65), (2,65), (3, 65);
308+insert into t1 values(1,64), (2,64), (3, 64);
309+insert into t1 values(1,63), (2,63), (3, 63);
310+insert into t1 values(1,62), (2,62), (3, 62);
311+insert into t1 values(1,61), (2,61), (3, 61);
312+insert into t1 values(1,60), (2,60), (3, 60);
313+insert into t1 values(1,59), (2,59), (3, 59);
314+insert into t1 values(1,58), (2,58), (3, 58);
315+insert into t1 values(1,57), (2,57), (3, 57);
316+insert into t1 values(1,56), (2,56), (3, 56);
317+insert into t1 values(1,55), (2,55), (3, 55);
318+insert into t1 values(1,54), (2,54), (3, 54);
319+insert into t1 values(1,53), (2,53), (3, 53);
320+insert into t1 values(1,52), (2,52), (3, 52);
321+insert into t1 values(1,51), (2,51), (3, 51);
322+insert into t1 values(1,50), (2,50), (3, 50);
323+insert into t1 values(1,49), (2,49), (3, 49);
324+insert into t1 values(1,48), (2,48), (3, 48);
325+insert into t1 values(1,47), (2,47), (3, 47);
326+insert into t1 values(1,46), (2,46), (3, 46);
327+insert into t1 values(1,45), (2,45), (3, 45);
328+insert into t1 values(1,44), (2,44), (3, 44);
329+insert into t1 values(1,43), (2,43), (3, 43);
330+insert into t1 values(1,42), (2,42), (3, 42);
331+insert into t1 values(1,41), (2,41), (3, 41);
332+insert into t1 values(1,40), (2,40), (3, 40);
333+insert into t1 values(1,39), (2,39), (3, 39);
334+insert into t1 values(1,38), (2,38), (3, 38);
335+insert into t1 values(1,37), (2,37), (3, 37);
336+insert into t1 values(1,36), (2,36), (3, 36);
337+insert into t1 values(1,35), (2,35), (3, 35);
338+insert into t1 values(1,34), (2,34), (3, 34);
339+insert into t1 values(1,33), (2,33), (3, 33);
340+insert into t1 values(1,32), (2,32), (3, 32);
341+insert into t1 values(1,31), (2,31), (3, 31);
342+insert into t1 values(1,30), (2,30), (3, 30);
343+insert into t1 values(1,29), (2,29), (3, 29);
344+insert into t1 values(1,28), (2,28), (3, 28);
345+insert into t1 values(1,27), (2,27), (3, 27);
346+insert into t1 values(1,26), (2,26), (3, 26);
347+insert into t1 values(1,25), (2,25), (3, 25);
348+insert into t1 values(1,24), (2,24), (3, 24);
349+insert into t1 values(1,23), (2,23), (3, 23);
350+insert into t1 values(1,22), (2,22), (3, 22);
351+insert into t1 values(1,21), (2,21), (3, 21);
352+insert into t1 values(1,20), (2,20), (3, 20);
353+insert into t1 values(1,19), (2,19), (3, 19);
354+insert into t1 values(1,18), (2,18), (3, 18);
355+insert into t1 values(1,17), (2,17), (3, 17);
356+insert into t1 values(1,16), (2,16), (3, 16);
357+insert into t1 values(1,15), (2,15), (3, 15);
358+insert into t1 values(1,14), (2,14), (3, 14);
359+insert into t1 values(1,13), (2,13), (3, 13);
360+insert into t1 values(1,12), (2,12), (3, 12);
361+insert into t1 values(1,11), (2,11), (3, 11);
362+insert into t1 values(1,10), (2,10), (3, 10);
363+insert into t1 values(1,9), (2,9), (3, 9);
364+insert into t1 values(1,8), (2,8), (3, 8);
365+insert into t1 values(1,7), (2,7), (3, 7);
366+insert into t1 values(1,6), (2,6), (3, 6);
367+insert into t1 values(1,5), (2,5), (3, 5);
368+insert into t1 values(1,4), (2,4), (3, 4);
369+insert into t1 values(1,3), (2,3), (3, 3);
370+insert into t1 values(1,2), (2,2), (3, 2);
371+insert into t1 values(1,1), (2,1), (3, 1);
372+alter table t1 add unique (a,b), add key (b);
373+show keys from t1;
374+Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment
375+t1 0 a 1 a A NULL NULL NULL YES BTREE
376+t1 0 a 2 b A NULL NULL NULL YES BTREE
377+t1 1 b 1 b A NULL NULL NULL YES BTREE
378+analyze table t1;
379+Table Op Msg_type Msg_text
380+test.t1 analyze status OK
381+show keys from t1;
382+Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment
383+t1 0 a 1 a A NULL NULL NULL YES BTREE
384+t1 0 a 2 b A NULL NULL NULL YES BTREE
385+t1 1 b 1 b A NULL NULL NULL YES BTREE
386+drop table t1;
387+CREATE TABLE t1 (i int(10), index(i) );
388+ALTER TABLE t1 DISABLE KEYS;
389+INSERT INTO t1 VALUES(1),(2),(3);
390+ALTER TABLE t1 ENABLE KEYS;
391+drop table t1;
392+CREATE TABLE t1 (
393+Host varchar(16) binary NOT NULL default '',
394+User varchar(16) binary NOT NULL default '',
395+PRIMARY KEY (Host,User)
396+) ENGINE=MyISAM;
397+ALTER TABLE t1 DISABLE KEYS;
398+LOCK TABLES t1 WRITE;
399+INSERT INTO t1 VALUES ('localhost','root'),('localhost',''),('games','monty');
400+SHOW INDEX FROM t1;
401+Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment
402+t1 0 PRIMARY 1 Host A NULL NULL NULL BTREE
403+t1 0 PRIMARY 2 User A 0 NULL NULL BTREE
404+ALTER TABLE t1 ENABLE KEYS;
405+UNLOCK TABLES;
406+CHECK TABLES t1;
407+Table Op Msg_type Msg_text
408+test.t1 check status OK
409+DROP TABLE t1;
410+CREATE TABLE t1 (
411+Host varchar(16) binary NOT NULL default '',
412+User varchar(16) binary NOT NULL default '',
413+PRIMARY KEY (Host,User),
414+KEY (Host)
415+) ENGINE=MyISAM;
416+ALTER TABLE t1 DISABLE KEYS;
417+SHOW INDEX FROM t1;
418+Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment
419+t1 0 PRIMARY 1 Host A NULL NULL NULL BTREE
420+t1 0 PRIMARY 2 User A 0 NULL NULL BTREE
421+t1 1 Host 1 Host A NULL NULL NULL BTREE disabled
422+LOCK TABLES t1 WRITE;
423+INSERT INTO t1 VALUES ('localhost','root'),('localhost','');
424+SHOW INDEX FROM t1;
425+Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment
426+t1 0 PRIMARY 1 Host A NULL NULL NULL BTREE
427+t1 0 PRIMARY 2 User A 0 NULL NULL BTREE
428+t1 1 Host 1 Host A NULL NULL NULL BTREE disabled
429+ALTER TABLE t1 ENABLE KEYS;
430+SHOW INDEX FROM t1;
431+Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment
432+t1 0 PRIMARY 1 Host A NULL NULL NULL BTREE
433+t1 0 PRIMARY 2 User A 2 NULL NULL BTREE
434+t1 1 Host 1 Host A 1 NULL NULL BTREE
435+UNLOCK TABLES;
436+CHECK TABLES t1;
437+Table Op Msg_type Msg_text
438+test.t1 check status OK
439+LOCK TABLES t1 WRITE;
440+ALTER TABLE t1 RENAME t2;
441+UNLOCK TABLES;
442+select * from t2;
443+Host User
444+localhost
445+localhost root
446+DROP TABLE t2;
447+CREATE TABLE t1 (
448+Host varchar(16) binary NOT NULL default '',
449+User varchar(16) binary NOT NULL default '',
450+PRIMARY KEY (Host,User),
451+KEY (Host)
452+) ENGINE=MyISAM;
453+LOCK TABLES t1 WRITE;
454+ALTER TABLE t1 DISABLE KEYS;
455+SHOW INDEX FROM t1;
456+Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment
457+t1 0 PRIMARY 1 Host A NULL NULL NULL BTREE
458+t1 0 PRIMARY 2 User A 0 NULL NULL BTREE
459+t1 1 Host 1 Host A NULL NULL NULL BTREE disabled
460+DROP TABLE t1;
461+create table t1 (a int);
462+alter table t1 rename to ``;
463+ERROR 42000: Incorrect table name ''
464+rename table t1 to ``;
465+ERROR 42000: Incorrect table name ''
466+drop table t1;
467+drop table if exists t1, t2;
468+Warnings:
469+Note 1051 Unknown table 't1'
470+Note 1051 Unknown table 't2'
471+create table t1 ( a varchar(10) not null primary key ) engine=myisam;
472+create table t2 ( a varchar(10) not null primary key ) engine=merge union=(t1);
473+flush tables;
474+alter table t1 modify a varchar(10);
475+show create table t2;
476+Table Create Table
477+t2 CREATE TABLE `t2` (
478+ `a` varchar(10) NOT NULL,
479+ PRIMARY KEY (`a`)
480+) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 UNION=(`t1`)
481+flush tables;
482+alter table t1 modify a varchar(10) not null;
483+show create table t2;
484+Table Create Table
485+t2 CREATE TABLE `t2` (
486+ `a` varchar(10) NOT NULL,
487+ PRIMARY KEY (`a`)
488+) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 UNION=(`t1`)
489+drop table if exists t1, t2;
490+create table t1 (a int, b int, c int, d int, e int, f int, g int, h int,i int, primary key (a,b,c,d,e,f,g,i,h)) engine=MyISAM;
491+insert into t1 (a) values(1);
492+show table status like 't1';
493+Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options Comment
494+t1 MyISAM 10 Fixed 1 37 X X X X X X X X latin1_swedish_ci NULL
495+alter table t1 modify a int;
496+show table status like 't1';
497+Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options Comment
498+t1 MyISAM 10 Fixed 1 37 X X X X X X X X latin1_swedish_ci NULL
499+drop table t1;
500+create table t1 (a int not null, b int not null, c int not null, d int not null, e int not null, f int not null, g int not null, h int not null,i int not null, primary key (a,b,c,d,e,f,g,i,h)) engine=MyISAM;
501+insert into t1 (a) values(1);
502+Warnings:
503+Warning 1364 Field 'b' doesn't have a default value
504+Warning 1364 Field 'c' doesn't have a default value
505+Warning 1364 Field 'd' doesn't have a default value
506+Warning 1364 Field 'e' doesn't have a default value
507+Warning 1364 Field 'f' doesn't have a default value
508+Warning 1364 Field 'g' doesn't have a default value
509+Warning 1364 Field 'h' doesn't have a default value
510+Warning 1364 Field 'i' doesn't have a default value
511+show table status like 't1';
512+Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options Comment
513+t1 MyISAM 10 Fixed 1 37 X X X X X X X X latin1_swedish_ci NULL
514+drop table t1;
515+set names koi8r;
516+create table t1 (a char(10) character set koi8r);
517+insert into t1 values ('ÔÅÓÔ');
518+select a,hex(a) from t1;
519+a hex(a)
520+ÔÅÓÔ D4C5D3D4
521+alter table t1 change a a char(10) character set cp1251;
522+select a,hex(a) from t1;
523+a hex(a)
524+ÔÅÓÔ F2E5F1F2
525+alter table t1 change a a binary(4);
526+select a,hex(a) from t1;
527+a hex(a)
528+òåñò F2E5F1F2
529+alter table t1 change a a char(10) character set cp1251;
530+select a,hex(a) from t1;
531+a hex(a)
532+ÔÅÓÔ F2E5F1F2
533+alter table t1 change a a char(10) character set koi8r;
534+select a,hex(a) from t1;
535+a hex(a)
536+ÔÅÓÔ D4C5D3D4
537+alter table t1 change a a varchar(10) character set cp1251;
538+select a,hex(a) from t1;
539+a hex(a)
540+ÔÅÓÔ F2E5F1F2
541+alter table t1 change a a char(10) character set koi8r;
542+select a,hex(a) from t1;
543+a hex(a)
544+ÔÅÓÔ D4C5D3D4
545+alter table t1 change a a text character set cp1251;
546+select a,hex(a) from t1;
547+a hex(a)
548+ÔÅÓÔ F2E5F1F2
549+alter table t1 change a a char(10) character set koi8r;
550+select a,hex(a) from t1;
551+a hex(a)
552+ÔÅÓÔ D4C5D3D4
553+delete from t1;
554+show create table t1;
555+Table Create Table
556+t1 CREATE TABLE `t1` (
557+ `a` char(10) CHARACTER SET koi8r DEFAULT NULL
558+) ENGINE=PBXT DEFAULT CHARSET=latin1
559+alter table t1 DEFAULT CHARACTER SET latin1;
560+show create table t1;
561+Table Create Table
562+t1 CREATE TABLE `t1` (
563+ `a` char(10) CHARACTER SET koi8r DEFAULT NULL
564+) ENGINE=PBXT DEFAULT CHARSET=latin1
565+alter table t1 CONVERT TO CHARACTER SET latin1;
566+show create table t1;
567+Table Create Table
568+t1 CREATE TABLE `t1` (
569+ `a` char(10) DEFAULT NULL
570+) ENGINE=PBXT DEFAULT CHARSET=latin1
571+alter table t1 DEFAULT CHARACTER SET cp1251;
572+show create table t1;
573+Table Create Table
574+t1 CREATE TABLE `t1` (
575+ `a` char(10) CHARACTER SET latin1 DEFAULT NULL
576+) ENGINE=PBXT DEFAULT CHARSET=cp1251
577+drop table t1;
578+create table t1 (myblob longblob,mytext longtext)
579+default charset latin1 collate latin1_general_cs;
580+show create table t1;
581+Table Create Table
582+t1 CREATE TABLE `t1` (
583+ `myblob` longblob,
584+ `mytext` longtext COLLATE latin1_general_cs
585+) ENGINE=PBXT DEFAULT CHARSET=latin1 COLLATE=latin1_general_cs
586+alter table t1 character set latin2;
587+show create table t1;
588+Table Create Table
589+t1 CREATE TABLE `t1` (
590+ `myblob` longblob,
591+ `mytext` longtext CHARACTER SET latin1 COLLATE latin1_general_cs
592+) ENGINE=PBXT DEFAULT CHARSET=latin2
593+drop table t1;
594+CREATE TABLE t1 (a int PRIMARY KEY, b INT UNIQUE);
595+ALTER TABLE t1 DROP PRIMARY KEY;
596+SHOW CREATE TABLE t1;
597+Table Create Table
598+t1 CREATE TABLE `t1` (
599+ `a` int(11) NOT NULL,
600+ `b` int(11) DEFAULT NULL,
601+ UNIQUE KEY `b` (`b`)
602+) ENGINE=PBXT DEFAULT CHARSET=latin1
603+ALTER TABLE t1 DROP PRIMARY KEY;
604+ERROR 42000: Can't DROP 'PRIMARY'; check that column/key exists
605+DROP TABLE t1;
606+create table t1 (a int, b int, key(a));
607+insert into t1 values (1,1), (2,2);
608+alter table t1 drop key no_such_key;
609+ERROR 42000: Can't DROP 'no_such_key'; check that column/key exists
610+alter table t1 drop key a;
611+drop table t1;
612+CREATE TABLE T12207(a int) ENGINE=MYISAM;
613+ALTER TABLE T12207 DISCARD TABLESPACE;
614+ERROR HY000: Table storage engine for 'T12207' doesn't have this option
615+DROP TABLE T12207;
616+create table t1 (a text) character set koi8r;
617+insert into t1 values (_koi8r'ÔÅÓÔ');
618+select hex(a) from t1;
619+hex(a)
620+D4C5D3D4
621+alter table t1 convert to character set cp1251;
622+select hex(a) from t1;
623+hex(a)
624+F2E5F1F2
625+drop table t1;
626+create table t1 ( a timestamp );
627+alter table t1 add unique ( a(1) );
628+ERROR HY000: Incorrect prefix key; the used key part isn't a string, the used length is longer than the key part, or the storage engine doesn't support unique prefix keys
629+drop table t1;
630+drop table if exists t1;
631+create table t1 (a int, key(a));
632+show indexes from t1;
633+Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment
634+t1 1 a 1 a A NULL NULL NULL YES BTREE
635+"this used not to disable the index"
636+alter table t1 modify a int, disable keys;
637+show indexes from t1;
638+Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment
639+t1 1 a 1 a A NULL NULL NULL YES BTREE
640+alter table t1 enable keys;
641+show indexes from t1;
642+Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment
643+t1 1 a 1 a NULL NULL NULL NULL YES BTREE
644+alter table t1 modify a bigint, disable keys;
645+show indexes from t1;
646+Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment
647+t1 1 a 1 a A NULL NULL NULL YES BTREE
648+alter table t1 enable keys;
649+show indexes from t1;
650+Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment
651+t1 1 a 1 a NULL NULL NULL NULL YES BTREE
652+alter table t1 add b char(10), disable keys;
653+show indexes from t1;
654+Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment
655+t1 1 a 1 a A NULL NULL NULL YES BTREE
656+alter table t1 add c decimal(10,2), enable keys;
657+show indexes from t1;
658+Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment
659+t1 1 a 1 a A NULL NULL NULL YES BTREE
660+"this however did"
661+alter table t1 disable keys;
662+show indexes from t1;
663+Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment
664+t1 1 a 1 a NULL NULL NULL NULL YES BTREE
665+desc t1;
666+Field Type Null Key Default Extra
667+a bigint(20) YES MUL NULL
668+b char(10) YES NULL
669+c decimal(10,2) YES NULL
670+alter table t1 add d decimal(15,5);
671+"The key should still be disabled"
672+show indexes from t1;
673+Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment
674+t1 1 a 1 a A NULL NULL NULL YES BTREE
675+drop table t1;
676+"Now will test with one unique index"
677+create table t1(a int, b char(10), unique(a));
678+show indexes from t1;
679+Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment
680+t1 0 a 1 a A 0 NULL NULL YES BTREE
681+alter table t1 disable keys;
682+show indexes from t1;
683+Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment
684+t1 0 a 1 a NULL 0 NULL NULL YES BTREE
685+alter table t1 enable keys;
686+"If no copy on noop change, this won't touch the data file"
687+"Unique index, no change"
688+alter table t1 modify a int, disable keys;
689+show indexes from t1;
690+Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment
691+t1 0 a 1 a A 0 NULL NULL YES BTREE
692+"Change the type implying data copy"
693+"Unique index, no change"
694+alter table t1 modify a bigint, disable keys;
695+show indexes from t1;
696+Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment
697+t1 0 a 1 a A 0 NULL NULL YES BTREE
698+alter table t1 modify a bigint;
699+show indexes from t1;
700+Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment
701+t1 0 a 1 a A 0 NULL NULL YES BTREE
702+alter table t1 modify a int;
703+show indexes from t1;
704+Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment
705+t1 0 a 1 a A 0 NULL NULL YES BTREE
706+drop table t1;
707+"Now will test with one unique and one non-unique index"
708+create table t1(a int, b char(10), unique(a), key(b));
709+show indexes from t1;
710+Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment
711+t1 0 a 1 a A 0 NULL NULL YES BTREE
712+t1 1 b 1 b A NULL NULL NULL YES BTREE
713+alter table t1 disable keys;
714+show indexes from t1;
715+Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment
716+t1 0 a 1 a NULL 0 NULL NULL YES BTREE
717+t1 1 b 1 b NULL NULL NULL NULL YES BTREE
718+alter table t1 enable keys;
719+"If no copy on noop change, this won't touch the data file"
720+"The non-unique index will be disabled"
721+alter table t1 modify a int, disable keys;
722+show indexes from t1;
723+Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment
724+t1 0 a 1 a A 0 NULL NULL YES BTREE
725+t1 1 b 1 b A NULL NULL NULL YES BTREE
726+alter table t1 enable keys;
727+show indexes from t1;
728+Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment
729+t1 0 a 1 a NULL 0 NULL NULL YES BTREE
730+t1 1 b 1 b NULL NULL NULL NULL YES BTREE
731+"Change the type implying data copy"
732+"The non-unique index will be disabled"
733+alter table t1 modify a bigint, disable keys;
734+show indexes from t1;
735+Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment
736+t1 0 a 1 a A 0 NULL NULL YES BTREE
737+t1 1 b 1 b A NULL NULL NULL YES BTREE
738+"Change again the type, but leave the indexes as_is"
739+alter table t1 modify a int;
740+show indexes from t1;
741+Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment
742+t1 0 a 1 a A 0 NULL NULL YES BTREE
743+t1 1 b 1 b A NULL NULL NULL YES BTREE
744+"Try the same. When data is no copied on similar tables, this is noop"
745+alter table t1 modify a int;
746+show indexes from t1;
747+Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment
748+t1 0 a 1 a A 0 NULL NULL YES BTREE
749+t1 1 b 1 b A NULL NULL NULL YES BTREE
750+drop table t1;
751+create database mysqltest;
752+create table t1 (c1 int);
753+alter table t1 rename mysqltest.t1;
754+drop table t1;
755+ERROR 42S02: Unknown table 't1'
756+alter table mysqltest.t1 rename t1;
757+drop table t1;
758+create table t1 (c1 int);
759+use mysqltest;
760+drop database mysqltest;
761+alter table test.t1 rename t1;
762+ERROR 3D000: No database selected
763+alter table test.t1 rename test.t1;
764+use test;
765+drop table t1;
766+CREATE TABLE t1(a INT) ROW_FORMAT=FIXED;
767+CREATE INDEX i1 ON t1(a);
768+SHOW CREATE TABLE t1;
769+Table Create Table
770+t1 CREATE TABLE `t1` (
771+ `a` int(11) DEFAULT NULL,
772+ KEY `i1` (`a`)
773+) ENGINE=PBXT DEFAULT CHARSET=latin1 ROW_FORMAT=FIXED
774+DROP INDEX i1 ON t1;
775+SHOW CREATE TABLE t1;
776+Table Create Table
777+t1 CREATE TABLE `t1` (
778+ `a` int(11) DEFAULT NULL
779+) ENGINE=PBXT DEFAULT CHARSET=latin1 ROW_FORMAT=FIXED
780+DROP TABLE t1;
781+DROP TABLE IF EXISTS bug24219;
782+DROP TABLE IF EXISTS bug24219_2;
783+CREATE TABLE bug24219 (a INT, INDEX(a));
784+SHOW INDEX FROM bug24219;
785+Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment
786+bug24219 1 a 1 a A NULL NULL NULL YES BTREE
787+ALTER TABLE bug24219 RENAME TO bug24219_2, DISABLE KEYS;
788+SHOW INDEX FROM bug24219_2;
789+Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment
790+bug24219_2 1 a 1 a A NULL NULL NULL YES BTREE
791+DROP TABLE bug24219_2;
792+create table t1 (mycol int(10) not null);
793+alter table t1 alter column mycol set default 0;
794+desc t1;
795+Field Type Null Key Default Extra
796+mycol int(10) NO 0
797+drop table t1;
798+create table t1 (v varchar(32));
799+insert into t1 values ('def'),('abc'),('hij'),('3r4f');
800+select * from t1;
801+v
802+def
803+abc
804+hij
805+3r4f
806+alter table t1 change v v2 varchar(32);
807+select * from t1;
808+v2
809+def
810+abc
811+hij
812+3r4f
813+alter table t1 change v2 v varchar(64);
814+select * from t1;
815+v
816+def
817+abc
818+hij
819+3r4f
820+update t1 set v = 'lmn' where v = 'hij';
821+select * from t1;
822+v
823+def
824+abc
825+3r4f
826+lmn
827+alter table t1 add i int auto_increment not null primary key first;
828+select * from t1;
829+i v
830+1 def
831+2 abc
832+3 3r4f
833+4 lmn
834+update t1 set i=5 where i=3;
835+select * from t1;
836+i v
837+1 def
838+2 abc
839+4 lmn
840+5 3r4f
841+alter table t1 change i i bigint;
842+select * from t1;
843+i v
844+1 def
845+2 abc
846+4 lmn
847+5 3r4f
848+alter table t1 add unique key (i, v);
849+select * from t1 where i between 2 and 4 and v in ('def','3r4f','lmn');
850+i v
851+4 lmn
852+drop table t1;
853+create table t1 (t varchar(255) default null, key t (t(80)))
854+engine=myisam default charset=latin1;
855+alter table t1 change t t text;
856+drop table t1;
857+CREATE TABLE t1 (s CHAR(8) BINARY);
858+INSERT INTO t1 VALUES ('test');
859+SELECT LENGTH(s) FROM t1;
860+LENGTH(s)
861+4
862+ALTER TABLE t1 MODIFY s CHAR(10) BINARY;
863+SELECT LENGTH(s) FROM t1;
864+LENGTH(s)
865+4
866+DROP TABLE t1;
867+CREATE TABLE t1 (s BINARY(8));
868+INSERT INTO t1 VALUES ('test');
869+SELECT LENGTH(s) FROM t1;
870+LENGTH(s)
871+8
872+SELECT HEX(s) FROM t1;
873+HEX(s)
874+7465737400000000
875+ALTER TABLE t1 MODIFY s BINARY(10);
876+SELECT HEX(s) FROM t1;
877+HEX(s)
878+74657374000000000000
879+SELECT LENGTH(s) FROM t1;
880+LENGTH(s)
881+10
882+DROP TABLE t1;
883+CREATE TABLE t1 (v VARCHAR(3), b INT);
884+INSERT INTO t1 VALUES ('abc', 5);
885+SELECT * FROM t1;
886+v b
887+abc 5
888+ALTER TABLE t1 MODIFY COLUMN v VARCHAR(4);
889+SELECT * FROM t1;
890+v b
891+abc 5
892+DROP TABLE t1;
893+DROP TABLE IF EXISTS `t+1`, `t+2`;
894+CREATE TABLE `t+1` (c1 INT);
895+ALTER TABLE `t+1` RENAME `t+2`;
896+CREATE TABLE `t+1` (c1 INT);
897+ALTER TABLE `t+1` RENAME `t+2`;
898+ERROR 42S01: Table 't+2' already exists
899+DROP TABLE `t+1`, `t+2`;
900+CREATE TEMPORARY TABLE `tt+1` (c1 INT);
901+ALTER TABLE `tt+1` RENAME `tt+2`;
902+CREATE TEMPORARY TABLE `tt+1` (c1 INT);
903+ALTER TABLE `tt+1` RENAME `tt+2`;
904+ERROR 42S01: Table 'tt+2' already exists
905+SHOW CREATE TABLE `tt+1`;
906+Table Create Table
907+tt+1 CREATE TEMPORARY TABLE `tt+1` (
908+ `c1` int(11) DEFAULT NULL
909+) ENGINE=PBXT DEFAULT CHARSET=latin1
910+SHOW CREATE TABLE `tt+2`;
911+Table Create Table
912+tt+2 CREATE TEMPORARY TABLE `tt+2` (
913+ `c1` int(11) DEFAULT NULL
914+) ENGINE=PBXT DEFAULT CHARSET=latin1
915+DROP TABLE `tt+1`, `tt+2`;
916+CREATE TABLE `#sql1` (c1 INT);
917+CREATE TABLE `@0023sql2` (c1 INT);
918+SHOW TABLES;
919+Tables_in_test
920+#sql1
921+@0023sql2
922+RENAME TABLE `#sql1` TO `@0023sql1`;
923+RENAME TABLE `@0023sql2` TO `#sql2`;
924+SHOW TABLES;
925+Tables_in_test
926+#sql2
927+@0023sql1
928+ALTER TABLE `@0023sql1` RENAME `#sql-1`;
929+ALTER TABLE `#sql2` RENAME `@0023sql-2`;
930+SHOW TABLES;
931+Tables_in_test
932+#sql-1
933+@0023sql-2
934+INSERT INTO `#sql-1` VALUES (1);
935+INSERT INTO `@0023sql-2` VALUES (2);
936+DROP TABLE `#sql-1`, `@0023sql-2`;
937+CREATE TEMPORARY TABLE `#sql1` (c1 INT);
938+CREATE TEMPORARY TABLE `@0023sql2` (c1 INT);
939+SHOW TABLES;
940+Tables_in_test
941+ALTER TABLE `#sql1` RENAME `@0023sql1`;
942+ALTER TABLE `@0023sql2` RENAME `#sql2`;
943+SHOW TABLES;
944+Tables_in_test
945+INSERT INTO `#sql2` VALUES (1);
946+INSERT INTO `@0023sql1` VALUES (2);
947+SHOW CREATE TABLE `#sql2`;
948+Table Create Table
949+#sql2 CREATE TEMPORARY TABLE `#sql2` (
950+ `c1` int(11) DEFAULT NULL
951+) ENGINE=PBXT DEFAULT CHARSET=latin1
952+SHOW CREATE TABLE `@0023sql1`;
953+Table Create Table
954+@0023sql1 CREATE TEMPORARY TABLE `@0023sql1` (
955+ `c1` int(11) DEFAULT NULL
956+) ENGINE=PBXT DEFAULT CHARSET=latin1
957+DROP TABLE `#sql2`, `@0023sql1`;
958+DROP TABLE IF EXISTS t1;
959+DROP TABLE IF EXISTS t2;
960+CREATE TABLE t1 (
961+int_field INTEGER UNSIGNED NOT NULL,
962+char_field CHAR(10),
963+INDEX(`int_field`)
964+);
965+DESCRIBE t1;
966+Field Type Null Key Default Extra
967+int_field int(10) unsigned NO MUL NULL
968+char_field char(10) YES NULL
969+SHOW INDEXES FROM t1;
970+Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment
971+t1 1 int_field 1 int_field A NULL NULL NULL BTREE
972+INSERT INTO t1 VALUES (1, "edno"), (1, "edno"), (2, "dve"), (3, "tri"), (5, "pet");
973+"Non-copy data change - new frm, but old data and index files"
974+ALTER TABLE t1
975+CHANGE int_field unsigned_int_field INTEGER UNSIGNED NOT NULL,
976+RENAME t2;
977+SELECT * FROM t1 ORDER BY int_field;
978+ERROR 42S02: Table 'test.t1' doesn't exist
979+SELECT * FROM t2 ORDER BY unsigned_int_field;
980+unsigned_int_field char_field
981+1 edno
982+1 edno
983+2 dve
984+3 tri
985+5 pet
986+DESCRIBE t2;
987+Field Type Null Key Default Extra
988+unsigned_int_field int(10) unsigned NO MUL NULL
989+char_field char(10) YES NULL
990+DESCRIBE t2;
991+Field Type Null Key Default Extra
992+unsigned_int_field int(10) unsigned NO MUL NULL
993+char_field char(10) YES NULL
994+ALTER TABLE t2 MODIFY unsigned_int_field BIGINT UNSIGNED NOT NULL;
995+DESCRIBE t2;
996+Field Type Null Key Default Extra
997+unsigned_int_field bigint(20) unsigned NO MUL NULL
998+char_field char(10) YES NULL
999+DROP TABLE t2;
1000
1001=== added file 'mysql-test/suite/pbxt/r/analyse.result'
1002--- mysql-test/suite/pbxt/r/analyse.result 1970-01-01 00:00:00 +0000
1003+++ mysql-test/suite/pbxt/r/analyse.result 2009-04-02 10:03:14 +0000
1004@@ -0,0 +1,156 @@
1005+drop table if exists t1,t2;
1006+create table t1 (i int, j int, empty_string char(10), bool char(1), d date);
1007+insert into t1 values (1,2,"","Y","2002-03-03"), (3,4,"","N","2002-03-04"), (5,6,"","Y","2002-03-04"), (7,8,"","N","2002-03-05");
1008+select count(*) from t1 procedure analyse();
1009+Field_name Min_value Max_value Min_length Max_length Empties_or_zeros Nulls Avg_value_or_avg_length Std Optimal_fieldtype
1010+count(*) 4 4 1 1 0 0 4.0000 0.0000 ENUM('4') NOT NULL
1011+select * from t1 procedure analyse();
1012+Field_name Min_value Max_value Min_length Max_length Empties_or_zeros Nulls Avg_value_or_avg_length Std Optimal_fieldtype
1013+test.t1.i 1 7 1 1 0 0 4.0000 2.2361 ENUM('1','3','5','7') NOT NULL
1014+test.t1.j 2 8 1 1 0 0 5.0000 2.2361 ENUM('2','4','6','8') NOT NULL
1015+test.t1.empty_string 0 0 4 0 0.0000 NULL CHAR(0) NOT NULL
1016+test.t1.bool N Y 1 1 0 0 1.0000 NULL ENUM('N','Y') NOT NULL
1017+test.t1.d 2002-03-03 2002-03-05 10 10 0 0 10.0000 NULL ENUM('2002-03-03','2002-03-04','2002-03-05') NOT NULL
1018+select * from t1 procedure analyse(2);
1019+Field_name Min_value Max_value Min_length Max_length Empties_or_zeros Nulls Avg_value_or_avg_length Std Optimal_fieldtype
1020+test.t1.i 1 7 1 1 0 0 4.0000 2.2361 TINYINT(1) UNSIGNED NOT NULL
1021+test.t1.j 2 8 1 1 0 0 5.0000 2.2361 TINYINT(1) UNSIGNED NOT NULL
1022+test.t1.empty_string 0 0 4 0 0.0000 NULL CHAR(0) NOT NULL
1023+test.t1.bool N Y 1 1 0 0 1.0000 NULL ENUM('N','Y') NOT NULL
1024+test.t1.d 2002-03-03 2002-03-05 10 10 0 0 10.0000 NULL ENUM('2002-03-03','2002-03-04','2002-03-05') NOT NULL
1025+create table t2 select * from t1 procedure analyse();
1026+select * from t2;
1027+Field_name Min_value Max_value Min_length Max_length Empties_or_zeros Nulls Avg_value_or_avg_length Std Optimal_fieldtype
1028+test.t1.i 1 7 1 1 0 0 4.0000 2.2361 ENUM('1','3','5','7') NOT NULL
1029+test.t1.j 2 8 1 1 0 0 5.0000 2.2361 ENUM('2','4','6','8') NOT NULL
1030+test.t1.empty_string 0 0 4 0 0.0000 NULL CHAR(0) NOT NULL
1031+test.t1.bool N Y 1 1 0 0 1.0000 NULL ENUM('N','Y') NOT NULL
1032+test.t1.d 2002-03-03 2002-03-05 10 10 0 0 10.0000 NULL ENUM('2002-03-03','2002-03-04','2002-03-05') NOT NULL
1033+drop table t1,t2;
1034+EXPLAIN SELECT 1 FROM (SELECT 1) a PROCEDURE ANALYSE();
1035+id select_type table type possible_keys key key_len ref rows Extra
1036+1 PRIMARY <derived2> system NULL NULL NULL NULL 1
1037+2 DERIVED NULL NULL NULL NULL NULL NULL NULL No tables used
1038+create table t1 (a int not null);
1039+create table t2 select * from t1 where 0=1 procedure analyse();
1040+show create table t2;
1041+Table Create Table
1042+t2 CREATE TABLE `t2` (
1043+ `Field_name` varbinary(255) NOT NULL DEFAULT '',
1044+ `Min_value` varbinary(255) DEFAULT NULL,
1045+ `Max_value` varbinary(255) DEFAULT NULL,
1046+ `Min_length` bigint(11) NOT NULL DEFAULT '0',
1047+ `Max_length` bigint(11) NOT NULL DEFAULT '0',
1048+ `Empties_or_zeros` bigint(11) NOT NULL DEFAULT '0',
1049+ `Nulls` bigint(11) NOT NULL DEFAULT '0',
1050+ `Avg_value_or_avg_length` varbinary(255) NOT NULL DEFAULT '',
1051+ `Std` varbinary(255) DEFAULT NULL,
1052+ `Optimal_fieldtype` varbinary(64) NOT NULL DEFAULT ''
1053+) ENGINE=PBXT DEFAULT CHARSET=latin1
1054+select * from t1 where 0=1 procedure analyse();
1055+Field_name Min_value Max_value Min_length Max_length Empties_or_zeros Nulls Avg_value_or_avg_length Std Optimal_fieldtype
1056+insert into t1 values(1);
1057+drop table t2;
1058+create table t2 select * from t1 where 0=1 procedure analyse();
1059+show create table t2;
1060+Table Create Table
1061+t2 CREATE TABLE `t2` (
1062+ `Field_name` varbinary(255) NOT NULL DEFAULT '',
1063+ `Min_value` varbinary(255) DEFAULT NULL,
1064+ `Max_value` varbinary(255) DEFAULT NULL,
1065+ `Min_length` bigint(11) NOT NULL DEFAULT '0',
1066+ `Max_length` bigint(11) NOT NULL DEFAULT '0',
1067+ `Empties_or_zeros` bigint(11) NOT NULL DEFAULT '0',
1068+ `Nulls` bigint(11) NOT NULL DEFAULT '0',
1069+ `Avg_value_or_avg_length` varbinary(255) NOT NULL DEFAULT '',
1070+ `Std` varbinary(255) DEFAULT NULL,
1071+ `Optimal_fieldtype` varbinary(64) NOT NULL DEFAULT ''
1072+) ENGINE=PBXT DEFAULT CHARSET=latin1
1073+select * from t2;
1074+Field_name Min_value Max_value Min_length Max_length Empties_or_zeros Nulls Avg_value_or_avg_length Std Optimal_fieldtype
1075+insert into t2 select * from t1 procedure analyse();
1076+select * from t2;
1077+Field_name Min_value Max_value Min_length Max_length Empties_or_zeros Nulls Avg_value_or_avg_length Std Optimal_fieldtype
1078+test.t1.a 1 1 1 1 0 0 1.0000 0.0000 ENUM('1') NOT NULL
1079+insert into t1 values(2);
1080+drop table t2;
1081+create table t2 select * from t1 where 0=1 procedure analyse();
1082+show create table t2;
1083+Table Create Table
1084+t2 CREATE TABLE `t2` (
1085+ `Field_name` varbinary(255) NOT NULL DEFAULT '',
1086+ `Min_value` varbinary(255) DEFAULT NULL,
1087+ `Max_value` varbinary(255) DEFAULT NULL,
1088+ `Min_length` bigint(11) NOT NULL DEFAULT '0',
1089+ `Max_length` bigint(11) NOT NULL DEFAULT '0',
1090+ `Empties_or_zeros` bigint(11) NOT NULL DEFAULT '0',
1091+ `Nulls` bigint(11) NOT NULL DEFAULT '0',
1092+ `Avg_value_or_avg_length` varbinary(255) NOT NULL DEFAULT '',
1093+ `Std` varbinary(255) DEFAULT NULL,
1094+ `Optimal_fieldtype` varbinary(64) NOT NULL DEFAULT ''
1095+) ENGINE=PBXT DEFAULT CHARSET=latin1
1096+select * from t2;
1097+Field_name Min_value Max_value Min_length Max_length Empties_or_zeros Nulls Avg_value_or_avg_length Std Optimal_fieldtype
1098+insert into t2 select * from t1 procedure analyse();
1099+select * from t2;
1100+Field_name Min_value Max_value Min_length Max_length Empties_or_zeros Nulls Avg_value_or_avg_length Std Optimal_fieldtype
1101+test.t1.a 1 2 1 1 0 0 1.5000 0.5000 ENUM('1','2') NOT NULL
1102+drop table t1,t2;
1103+create table t1 (v varchar(128));
1104+insert into t1 values ('abc'),('abc\'def\\hij\"klm\0opq'),('\''),('\"'),('\\'),('a\0'),('b\''),('c\"'),('d\\'),('\'b'),('\"c'),('\\d'),('a\0\0\0b'),('a\'\'\'\'b'),('a\"\"\"\"b'),('a\\\\\\\\b'),('\'\0\\\"'),('\'\''),('\"\"'),('\\\\'),('The\ZEnd');
1105+select * from t1 procedure analyse();
1106+Field_name Min_value Max_value Min_length Max_length Empties_or_zeros Nulls Avg_value_or_avg_length Std Optimal_fieldtype
1107+test.t1.v " \\ 1 19 0 0 3.7619 NULL ENUM('"','""','"c','\'\0\\"','\'','\'\'','\'b','a\0\0\0b','a\0','a""""b','a\'\'\'\'b','abc','abc\'def\\hij"klm\0opq','a\\\\\\\\b','b\'','c"','d\\','The\ZEnd','\\','\\d','\\\\') NOT NULL
1108+drop table t1;
1109+create table t1 (df decimal(5,1));
1110+insert into t1 values(1.1);
1111+insert into t1 values(2.2);
1112+select * from t1 procedure analyse();
1113+Field_name Min_value Max_value Min_length Max_length Empties_or_zeros Nulls Avg_value_or_avg_length Std Optimal_fieldtype
1114+test.t1.df 1.1 2.2 13 13 0 0 1.65000 0.55000 ENUM('1.1','2.2') NOT NULL
1115+drop table t1;
1116+create table t1 (d double);
1117+insert into t1 values (100000);
1118+select * from t1 procedure analyse (1,1);
1119+Field_name Min_value Max_value Min_length Max_length Empties_or_zeros Nulls Avg_value_or_avg_length Std Optimal_fieldtype
1120+test.t1.d 100000 100000 6 6 0 0 100000 0 MEDIUMINT(6) UNSIGNED NOT NULL
1121+drop table t1;
1122+create table t1 (product varchar(32), country_id int not null, year int,
1123+profit int);
1124+insert into t1 values ( 'Computer', 2,2000, 1200),
1125+( 'TV', 1, 1999, 150),
1126+( 'Calculator', 1, 1999,50),
1127+( 'Computer', 1, 1999,1500),
1128+( 'Computer', 1, 2000,1500),
1129+( 'TV', 1, 2000, 150),
1130+( 'TV', 2, 2000, 100),
1131+( 'TV', 2, 2000, 100),
1132+( 'Calculator', 1, 2000,75),
1133+( 'Calculator', 2, 2000,75),
1134+( 'TV', 1, 1999, 100),
1135+( 'Computer', 1, 1999,1200),
1136+( 'Computer', 2, 2000,1500),
1137+( 'Calculator', 2, 2000,75),
1138+( 'Phone', 3, 2003,10)
1139+;
1140+create table t2 (country_id int primary key, country char(20) not null);
1141+insert into t2 values (1, 'USA'),(2,'India'), (3,'Finland');
1142+select product, sum(profit),avg(profit) from t1 group by product with rollup procedure analyse();
1143+Field_name Min_value Max_value Min_length Max_length Empties_or_zeros Nulls Avg_value_or_avg_length Std Optimal_fieldtype
1144+test.t1.product Computer TV 2 8 0 0 4.2500 NULL ENUM('Computer','Phone','TV') NOT NULL
1145+sum(profit) 10 6900 11 11 0 0 1946.2500 2867.6719 ENUM('10','275','600','6900') NOT NULL
1146+avg(profit) 10.0000 1380.0000 16 16 0 0 394.68750000 570.20033144 ENUM('10.0000','68.7500','120.0000','1380.0000') NOT NULL
1147+drop table t1,t2;
1148+create table t1 (f1 double(10,5), f2 char(10), f3 double(10,5));
1149+insert into t1 values (5.999, "5.9999", 5.99999), (9.555, "9.5555", 9.55555);
1150+select f1 from t1 procedure analyse(1, 1);
1151+Field_name Min_value Max_value Min_length Max_length Empties_or_zeros Nulls Avg_value_or_avg_length Std Optimal_fieldtype
1152+test.t1.f1 5.99900 9.55500 7 7 0 0 7.77700 1.77800 FLOAT(4,3) NOT NULL
1153+select f2 from t1 procedure analyse(1, 1);
1154+Field_name Min_value Max_value Min_length Max_length Empties_or_zeros Nulls Avg_value_or_avg_length Std Optimal_fieldtype
1155+test.t1.f2 5.9999 9.5555 6 6 0 0 6.0000 NULL FLOAT(5,4) UNSIGNED NOT NULL
1156+select f3 from t1 procedure analyse(1, 1);
1157+Field_name Min_value Max_value Min_length Max_length Empties_or_zeros Nulls Avg_value_or_avg_length Std Optimal_fieldtype
1158+test.t1.f3 5.99999 9.55555 7 7 0 0 7.77777 1.77778 FLOAT(6,5) NOT NULL
1159+drop table t1;
1160+End of 4.1 tests
1161
1162=== added file 'mysql-test/suite/pbxt/r/analyze.result'
1163--- mysql-test/suite/pbxt/r/analyze.result 1970-01-01 00:00:00 +0000
1164+++ mysql-test/suite/pbxt/r/analyze.result 2009-04-02 10:03:14 +0000
1165@@ -0,0 +1,60 @@
1166+create table t1 (a bigint);
1167+lock tables t1 write;
1168+insert into t1 values(0);
1169+analyze table t1;
1170+Table Op Msg_type Msg_text
1171+test.t1 analyze status OK
1172+unlock tables;
1173+check table t1;
1174+Table Op Msg_type Msg_text
1175+test.t1 check status OK
1176+drop table t1;
1177+create table t1 (a bigint);
1178+insert into t1 values(0);
1179+lock tables t1 write;
1180+delete from t1;
1181+analyze table t1;
1182+Table Op Msg_type Msg_text
1183+test.t1 analyze status OK
1184+unlock tables;
1185+check table t1;
1186+Table Op Msg_type Msg_text
1187+test.t1 check status OK
1188+drop table t1;
1189+create table t1 (a bigint);
1190+insert into t1 values(0);
1191+analyze table t1;
1192+Table Op Msg_type Msg_text
1193+test.t1 analyze status OK
1194+check table t1;
1195+Table Op Msg_type Msg_text
1196+test.t1 check status OK
1197+drop table t1;
1198+create table t1 (a mediumtext, fulltext key key1(a)) charset utf8 collate utf8_general_ci engine myisam;
1199+insert into t1 values ('hello');
1200+analyze table t1;
1201+Table Op Msg_type Msg_text
1202+test.t1 analyze status OK
1203+analyze table t1;
1204+Table Op Msg_type Msg_text
1205+test.t1 analyze status Table is already up to date
1206+drop table t1;
1207+CREATE TABLE t1 (a int);
1208+prepare stmt1 from "SELECT * FROM t1 PROCEDURE ANALYSE()";
1209+execute stmt1;
1210+Field_name Min_value Max_value Min_length Max_length Empties_or_zeros Nulls Avg_value_or_avg_length Std Optimal_fieldtype
1211+test.t1.a NULL NULL 0 0 0 0 0.0 0.0 CHAR(0) NOT NULL
1212+execute stmt1;
1213+Field_name Min_value Max_value Min_length Max_length Empties_or_zeros Nulls Avg_value_or_avg_length Std Optimal_fieldtype
1214+test.t1.a NULL NULL 0 0 0 0 0.0 0.0 CHAR(0) NOT NULL
1215+deallocate prepare stmt1;
1216+drop table t1;
1217+create temporary table t1(a int, index(a));
1218+insert into t1 values('1'),('2'),('3'),('4'),('5');
1219+analyze table t1;
1220+Table Op Msg_type Msg_text
1221+test.t1 analyze status OK
1222+show index from t1;
1223+Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment
1224+t1 1 a 1 a A NULL NULL NULL YES BTREE
1225+drop table t1;
1226
1227=== added file 'mysql-test/suite/pbxt/r/ansi.result'
1228--- mysql-test/suite/pbxt/r/ansi.result 1970-01-01 00:00:00 +0000
1229+++ mysql-test/suite/pbxt/r/ansi.result 2009-04-02 10:03:14 +0000
1230@@ -0,0 +1,48 @@
1231+drop table if exists t1;
1232+set sql_mode="MySQL40";
1233+select @@sql_mode;
1234+@@sql_mode
1235+MYSQL40,HIGH_NOT_PRECEDENCE
1236+set @@sql_mode="ANSI";
1237+select @@sql_mode;
1238+@@sql_mode
1239+REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ANSI
1240+SELECT 'A' || 'B';
1241+'A' || 'B'
1242+AB
1243+CREATE TABLE t1 (id INT, id2 int);
1244+SELECT id,NULL,1,1.1,'a' FROM t1 GROUP BY id;
1245+id NULL 1 1.1 a
1246+SELECT id FROM t1 GROUP BY id2;
1247+id
1248+drop table t1;
1249+SET @@SQL_MODE="";
1250+CREATE TABLE t1 (i int auto_increment NOT NULL, PRIMARY KEY (i));
1251+SHOW CREATE TABLE t1;
1252+Table Create Table
1253+t1 CREATE TABLE `t1` (
1254+ `i` int(11) NOT NULL AUTO_INCREMENT,
1255+ PRIMARY KEY (`i`)
1256+) ENGINE=PBXT DEFAULT CHARSET=latin1
1257+SET @@SQL_MODE="MYSQL323";
1258+SHOW CREATE TABLE t1;
1259+Table Create Table
1260+t1 CREATE TABLE `t1` (
1261+ `i` int(11) NOT NULL AUTO_INCREMENT,
1262+ PRIMARY KEY (`i`)
1263+) TYPE=PBXT
1264+SET @@SQL_MODE="MYSQL40";
1265+SHOW CREATE TABLE t1;
1266+Table Create Table
1267+t1 CREATE TABLE `t1` (
1268+ `i` int(11) NOT NULL AUTO_INCREMENT,
1269+ PRIMARY KEY (`i`)
1270+) TYPE=PBXT
1271+SET @@SQL_MODE="NO_FIELD_OPTIONS";
1272+SHOW CREATE TABLE t1;
1273+Table Create Table
1274+t1 CREATE TABLE `t1` (
1275+ `i` int(11) NOT NULL,
1276+ PRIMARY KEY (`i`)
1277+) ENGINE=PBXT DEFAULT CHARSET=latin1
1278+DROP TABLE t1;
1279
1280=== added file 'mysql-test/suite/pbxt/r/auto_increment.result'
1281--- mysql-test/suite/pbxt/r/auto_increment.result 1970-01-01 00:00:00 +0000
1282+++ mysql-test/suite/pbxt/r/auto_increment.result 2009-04-02 10:03:14 +0000
1283@@ -0,0 +1,454 @@
1284+drop table if exists t1;
1285+drop table if exists t2;
1286+SET SQL_WARNINGS=1;
1287+create table t1 (a int not null auto_increment,b int, primary key (a)) engine=myisam auto_increment=3;
1288+insert into t1 values (1,1),(NULL,3),(NULL,4);
1289+delete from t1 where a=4;
1290+insert into t1 values (NULL,5),(NULL,6);
1291+select * from t1;
1292+a b
1293+1 1
1294+3 3
1295+5 5
1296+6 6
1297+delete from t1 where a=6;
1298+replace t1 values (3,1);
1299+ALTER TABLE t1 add c int;
1300+replace t1 values (3,3,3);
1301+insert into t1 values (NULL,7,7);
1302+update t1 set a=8,b=b+1,c=c+1 where a=7;
1303+insert into t1 values (NULL,9,9);
1304+select * from t1;
1305+a b c
1306+1 1 NULL
1307+3 3 3
1308+5 5 NULL
1309+8 8 8
1310+9 9 9
1311+drop table t1;
1312+create table t1 (
1313+skey tinyint unsigned NOT NULL auto_increment PRIMARY KEY,
1314+sval char(20)
1315+);
1316+insert into t1 values (NULL, "hello");
1317+insert into t1 values (NULL, "hey");
1318+select * from t1;
1319+skey sval
1320+1 hello
1321+2 hey
1322+select _rowid,t1._rowid,skey,sval from t1;
1323+_rowid _rowid skey sval
1324+1 1 1 hello
1325+2 2 2 hey
1326+drop table t1;
1327+create table t1 (a char(10) not null, b int not null auto_increment, primary key(a,b));
1328+insert into t1 values ("a",1),("b",2),("a",2),("c",1);
1329+insert into t1 values ("a",NULL),("b",NULL),("c",NULL),("e",NULL);
1330+insert into t1 (a) values ("a"),("b"),("c"),("d");
1331+insert into t1 (a) values ('k'),('d');
1332+insert into t1 (a) values ("a");
1333+insert into t1 values ("d",last_insert_id());
1334+select * from t1;
1335+a b
1336+a 1
1337+a 2
1338+a 3
1339+a 7
1340+a 13
1341+b 2
1342+b 4
1343+b 8
1344+c 1
1345+c 5
1346+c 9
1347+d 10
1348+d 12
1349+d 13
1350+e 6
1351+k 11
1352+drop table t1;
1353+create table t1 (ordid int(8) not null auto_increment, ord varchar(50) not null, primary key (ordid), index(ord,ordid));
1354+insert into t1 (ordid,ord) values (NULL,'sdj'),(NULL,'sdj');
1355+select * from t1;
1356+ordid ord
1357+1 sdj
1358+2 sdj
1359+drop table t1;
1360+create table t1 (ordid int(8) not null auto_increment, ord varchar(50) not null, primary key (ord,ordid));
1361+insert into t1 values (NULL,'sdj'),(NULL,'sdj'),(NULL,"abc"),(NULL,'abc'),(NULL,'zzz'),(NULL,'sdj'),(NULL,'abc');
1362+select * from t1;
1363+ordid ord
1364+3 abc
1365+4 abc
1366+7 abc
1367+1 sdj
1368+2 sdj
1369+6 sdj
1370+5 zzz
1371+drop table t1;
1372+create table t1 (sid char(5), id int(2) NOT NULL auto_increment, key(sid, id));
1373+create table t2 (sid char(20), id int(2));
1374+insert into t2 values ('skr',NULL),('skr',NULL),('test',NULL);
1375+insert into t1 select * from t2;
1376+select * from t1;
1377+sid id
1378+skr 1
1379+skr 2
1380+test 3
1381+drop table t1,t2;
1382+create table t1 (a int not null primary key auto_increment);
1383+insert into t1 values (0);
1384+update t1 set a=0;
1385+select * from t1;
1386+a
1387+0
1388+check table t1;
1389+Table Op Msg_type Msg_text
1390+test.t1 check status OK
1391+drop table t1;
1392+create table t1 (a int not null auto_increment primary key);
1393+insert into t1 values (NULL);
1394+insert into t1 values (-1);
1395+select last_insert_id();
1396+last_insert_id()
1397+1
1398+insert into t1 values (NULL);
1399+select * from t1;
1400+a
1401+-1
1402+1
1403+2
1404+drop table t1;
1405+create table t1 (a int not null auto_increment primary key) /*!40102 engine=heap */;
1406+insert into t1 values (NULL);
1407+insert into t1 values (-1);
1408+select last_insert_id();
1409+last_insert_id()
1410+1
1411+insert into t1 values (NULL);
1412+select * from t1;
1413+a
1414+1
1415+-1
1416+2
1417+drop table t1;
1418+create table t1 (i tinyint unsigned not null auto_increment primary key);
1419+insert into t1 set i = 254;
1420+insert into t1 set i = null;
1421+select last_insert_id();
1422+last_insert_id()
1423+255
1424+explain extended select last_insert_id();
1425+id select_type table type possible_keys key key_len ref rows filtered Extra
1426+1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used
1427+Warnings:
1428+Note 1003 select last_insert_id() AS `last_insert_id()`
1429+insert into t1 set i = 254;
1430+ERROR 23000: Duplicate entry '254' for key 'PRIMARY'
1431+select last_insert_id();
1432+last_insert_id()
1433+255
1434+insert into t1 set i = null;
1435+ERROR HY000: Failed to read auto-increment value from storage engine
1436+select last_insert_id();
1437+last_insert_id()
1438+255
1439+drop table t1;
1440+create table t1 (i tinyint unsigned not null auto_increment, key (i));
1441+insert into t1 set i = 254;
1442+insert into t1 set i = null;
1443+select last_insert_id();
1444+last_insert_id()
1445+255
1446+insert into t1 set i = null;
1447+ERROR HY000: Failed to read auto-increment value from storage engine
1448+select last_insert_id();
1449+last_insert_id()
1450+255
1451+drop table t1;
1452+create table t1 (i tinyint unsigned not null auto_increment primary key, b int, unique (b));
1453+insert into t1 values (NULL, 10);
1454+select last_insert_id();
1455+last_insert_id()
1456+1
1457+insert into t1 values (NULL, 15);
1458+select last_insert_id();
1459+last_insert_id()
1460+2
1461+insert into t1 values (NULL, 10);
1462+ERROR 23000: Duplicate entry '10' for key 'b'
1463+select last_insert_id();
1464+last_insert_id()
1465+2
1466+drop table t1;
1467+create table t1(a int auto_increment,b int null,primary key(a));
1468+SET SQL_MODE=NO_AUTO_VALUE_ON_ZERO;
1469+insert into t1(a,b)values(NULL,1);
1470+insert into t1(a,b)values(200,2);
1471+insert into t1(a,b)values(0,3);
1472+insert into t1(b)values(4);
1473+insert into t1(b)values(5);
1474+insert into t1(b)values(6);
1475+insert into t1(b)values(7);
1476+select * from t1 order by b;
1477+a b
1478+1 1
1479+200 2
1480+0 3
1481+201 4
1482+202 5
1483+203 6
1484+204 7
1485+alter table t1 modify b mediumint;
1486+select * from t1 order by b;
1487+a b
1488+1 1
1489+200 2
1490+0 3
1491+201 4
1492+202 5
1493+203 6
1494+204 7
1495+create table t2 (a int);
1496+insert t2 values (1),(2);
1497+alter table t2 add b int auto_increment primary key;
1498+select * from t2;
1499+a b
1500+1 1
1501+2 2
1502+drop table t2;
1503+delete from t1 where a=0;
1504+update t1 set a=0 where b=5;
1505+select * from t1 order by b;
1506+a b
1507+1 1
1508+200 2
1509+201 4
1510+0 5
1511+203 6
1512+204 7
1513+delete from t1 where a=0;
1514+update t1 set a=NULL where b=6;
1515+ERROR 23000: Column 'a' cannot be null
1516+update t1 set a=300 where b=7;
1517+SET SQL_MODE='';
1518+insert into t1(a,b)values(NULL,8);
1519+insert into t1(a,b)values(400,9);
1520+insert into t1(a,b)values(0,10);
1521+insert into t1(b)values(11);
1522+insert into t1(b)values(12);
1523+insert into t1(b)values(13);
1524+insert into t1(b)values(14);
1525+select * from t1 order by b;
1526+a b
1527+1 1
1528+200 2
1529+201 4
1530+203 6
1531+300 7
1532+301 8
1533+400 9
1534+401 10
1535+402 11
1536+403 12
1537+404 13
1538+405 14
1539+delete from t1 where a=0;
1540+update t1 set a=0 where b=12;
1541+select * from t1 order by b;
1542+a b
1543+1 1
1544+200 2
1545+201 4
1546+203 6
1547+300 7
1548+301 8
1549+400 9
1550+401 10
1551+402 11
1552+0 12
1553+404 13
1554+405 14
1555+delete from t1 where a=0;
1556+update t1 set a=NULL where b=13;
1557+ERROR 23000: Column 'a' cannot be null
1558+update t1 set a=500 where b=14;
1559+select * from t1 order by b;
1560+a b
1561+1 1
1562+200 2
1563+201 4
1564+203 6
1565+300 7
1566+301 8
1567+400 9
1568+401 10
1569+402 11
1570+404 13
1571+500 14
1572+drop table t1;
1573+create table t1 (a bigint);
1574+insert into t1 values (1), (2), (3), (NULL), (NULL);
1575+alter table t1 modify a bigint not null auto_increment primary key;
1576+select * from t1;
1577+a
1578+1
1579+2
1580+3
1581+4
1582+5
1583+drop table t1;
1584+create table t1 (a bigint);
1585+insert into t1 values (1), (2), (3), (0), (0);
1586+alter table t1 modify a bigint not null auto_increment primary key;
1587+select * from t1;
1588+a
1589+1
1590+2
1591+3
1592+4
1593+5
1594+drop table t1;
1595+create table t1 (a bigint);
1596+insert into t1 values (0), (1), (2), (3);
1597+set sql_mode=NO_AUTO_VALUE_ON_ZERO;
1598+alter table t1 modify a bigint not null auto_increment primary key;
1599+set sql_mode= '';
1600+select * from t1;
1601+a
1602+0
1603+1
1604+2
1605+3
1606+drop table t1;
1607+create table t1 (a int auto_increment primary key , b int null);
1608+set sql_mode=NO_AUTO_VALUE_ON_ZERO;
1609+insert into t1 values (0,1),(1,2),(2,3);
1610+select * from t1;
1611+a b
1612+0 1
1613+1 2
1614+2 3
1615+set sql_mode= '';
1616+alter table t1 modify b varchar(255);
1617+insert into t1 values (0,4);
1618+select * from t1;
1619+a b
1620+0 1
1621+1 2
1622+2 3
1623+3 4
1624+drop table t1;
1625+CREATE TABLE t1 ( a INT AUTO_INCREMENT, b BLOB, PRIMARY KEY (a,b(10)));
1626+INSERT INTO t1 (b) VALUES ('aaaa');
1627+CHECK TABLE t1;
1628+Table Op Msg_type Msg_text
1629+test.t1 check status OK
1630+INSERT INTO t1 (b) VALUES ('');
1631+CHECK TABLE t1;
1632+Table Op Msg_type Msg_text
1633+test.t1 check status OK
1634+INSERT INTO t1 (b) VALUES ('bbbb');
1635+CHECK TABLE t1;
1636+Table Op Msg_type Msg_text
1637+test.t1 check status OK
1638+DROP TABLE IF EXISTS t1;
1639+CREATE TABLE `t1` (
1640+t1_name VARCHAR(255) DEFAULT NULL,
1641+t1_id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
1642+KEY (t1_name),
1643+PRIMARY KEY (t1_id)
1644+) AUTO_INCREMENT = 1000;
1645+INSERT INTO t1 (t1_name) VALUES('MySQL');
1646+INSERT INTO t1 (t1_name) VALUES('MySQL');
1647+INSERT INTO t1 (t1_name) VALUES('MySQL');
1648+SELECT * from t1;
1649+t1_name t1_id
1650+MySQL 1000
1651+MySQL 1001
1652+MySQL 1002
1653+SHOW CREATE TABLE `t1`;
1654+Table Create Table
1655+t1 CREATE TABLE `t1` (
1656+ `t1_name` varchar(255) DEFAULT NULL,
1657+ `t1_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
1658+ PRIMARY KEY (`t1_id`),
1659+ KEY `t1_name` (`t1_name`)
1660+) ENGINE=PBXT AUTO_INCREMENT=1000 DEFAULT CHARSET=latin1
1661+DROP TABLE `t1`;
1662+create table t1(a int not null auto_increment primary key);
1663+create table t2(a int not null auto_increment primary key, t1a int);
1664+insert into t1 values(NULL);
1665+insert into t2 values (NULL, LAST_INSERT_ID()), (NULL, LAST_INSERT_ID());
1666+insert into t1 values (NULL);
1667+insert into t2 values (NULL, LAST_INSERT_ID()), (NULL, LAST_INSERT_ID()),
1668+(NULL, LAST_INSERT_ID());
1669+insert into t1 values (NULL);
1670+insert into t2 values (NULL, LAST_INSERT_ID()), (NULL, LAST_INSERT_ID()),
1671+(NULL, LAST_INSERT_ID()), (NULL, LAST_INSERT_ID());
1672+select * from t2;
1673+a t1a
1674+1 1
1675+2 1
1676+3 2
1677+4 2
1678+5 2
1679+6 3
1680+7 3
1681+8 3
1682+9 3
1683+drop table t1, t2;
1684+End of 4.1 tests
1685+CREATE TABLE t1 ( `a` int(11) NOT NULL auto_increment, `b` int(11) default NULL,PRIMARY KEY (`a`),UNIQUE KEY `b` (`b`));
1686+insert into t1 (b) values (1);
1687+replace into t1 (b) values (2), (1), (3);
1688+select * from t1;
1689+a b
1690+2 2
1691+3 1
1692+4 3
1693+truncate table t1;
1694+insert into t1 (b) values (1);
1695+replace into t1 (b) values (2);
1696+replace into t1 (b) values (1);
1697+replace into t1 (b) values (3);
1698+select * from t1 order by a;
1699+a b
1700+2 2
1701+3 1
1702+4 3
1703+drop table t1;
1704+create table t1 (rowid int not null auto_increment, val int not null,primary
1705+key (rowid), unique(val));
1706+replace into t1 (val) values ('1'),('2');
1707+replace into t1 (val) values ('1'),('2');
1708+insert into t1 (val) values ('1'),('2');
1709+ERROR 23000: Duplicate entry '1' for key 'val'
1710+select * from t1;
1711+rowid val
1712+3 1
1713+4 2
1714+drop table t1;
1715+create table t1 (a int not null auto_increment primary key, val int);
1716+insert into t1 (val) values (1);
1717+update t1 set a=2 where a=1;
1718+insert into t1 (val) values (1);
1719+select * from t1 order by a;
1720+a val
1721+2 1
1722+3 1
1723+drop table t1;
1724+CREATE TABLE t1 (t1 INT(10) PRIMARY KEY, t2 INT(10));
1725+INSERT INTO t1 VALUES(0, 0);
1726+INSERT INTO t1 VALUES(1, 1);
1727+ALTER TABLE t1 CHANGE t1 t1 INT(10) auto_increment;
1728+ERROR 23000: ALTER TABLE causes auto_increment resequencing, resulting in duplicate entry '1' for key 'PRIMARY'
1729+DROP TABLE t1;
1730+create table t1 (a int primary key auto_increment, b int, c int, d timestamp default current_timestamp, unique(b),unique(c));
1731+insert into t1 values(null,1,1,now());
1732+insert into t1 values(null,0,0,null);
1733+replace into t1 values(null,1,0,null);
1734+select last_insert_id();
1735+last_insert_id()
1736+3
1737+drop table t1;
1738
1739=== added file 'mysql-test/suite/pbxt/r/bench_count_distinct.result'
1740--- mysql-test/suite/pbxt/r/bench_count_distinct.result 1970-01-01 00:00:00 +0000
1741+++ mysql-test/suite/pbxt/r/bench_count_distinct.result 2009-04-02 10:03:14 +0000
1742@@ -0,0 +1,11 @@
1743+drop table if exists t1;
1744+create table t1(n int not null, key(n)) delay_key_write = 1;
1745+select count(distinct n) from t1;
1746+count(distinct n)
1747+100
1748+explain extended select count(distinct n) from t1;
1749+id select_type table type possible_keys key key_len ref rows filtered Extra
1750+1 SIMPLE t1 index NULL n 4 NULL 200 100.00 Using index
1751+Warnings:
1752+Note 1003 select count(distinct `test`.`t1`.`n`) AS `count(distinct n)` from `test`.`t1`
1753+drop table t1;
1754
1755=== added file 'mysql-test/suite/pbxt/r/bigint.result'
1756--- mysql-test/suite/pbxt/r/bigint.result 1970-01-01 00:00:00 +0000
1757+++ mysql-test/suite/pbxt/r/bigint.result 2009-04-02 10:03:14 +0000
1758@@ -0,0 +1,354 @@
1759+drop table if exists t1, t2;
1760+select 0,256,00000000000000065536,2147483647,-2147483648,2147483648,+4294967296;
1761+0 256 00000000000000065536 2147483647 -2147483648 2147483648 4294967296
1762+0 256 65536 2147483647 -2147483648 2147483648 4294967296
1763+select 9223372036854775807,-009223372036854775808;
1764+9223372036854775807 -009223372036854775808
1765+9223372036854775807 -9223372036854775808
1766+select +9999999999999999999,-9999999999999999999;
1767+9999999999999999999 -9999999999999999999
1768+9999999999999999999 -9999999999999999999
1769+select cast(9223372036854775808 as unsigned)+1;
1770+cast(9223372036854775808 as unsigned)+1
1771+9223372036854775809
1772+select 9223372036854775808+1;
1773+9223372036854775808+1
1774+9223372036854775809
1775+select -(0-3),round(-(0-3)), round(9999999999999999999);
1776+-(0-3) round(-(0-3)) round(9999999999999999999)
1777+3 3 9999999999999999999
1778+select 1,11,101,1001,10001,100001,1000001,10000001,100000001,1000000001,10000000001,100000000001,1000000000001,10000000000001,100000000000001,1000000000000001,10000000000000001,100000000000000001,1000000000000000001,10000000000000000001;
1779+1 11 101 1001 10001 100001 1000001 10000001 100000001 1000000001 10000000001 100000000001 1000000000001 10000000000001 100000000000001 1000000000000001 10000000000000001 100000000000000001 1000000000000000001 10000000000000000001
1780+1 11 101 1001 10001 100001 1000001 10000001 100000001 1000000001 10000000001 100000000001 1000000000001 10000000000001 100000000000001 1000000000000001 10000000000000001 100000000000000001 1000000000000000001 10000000000000000001
1781+select -1,-11,-101,-1001,-10001,-100001,-1000001,-10000001,-100000001,-1000000001,-10000000001,-100000000001,-1000000000001,-10000000000001,-100000000000001,-1000000000000001,-10000000000000001,-100000000000000001,-1000000000000000001,-10000000000000000001;
1782+-1 -11 -101 -1001 -10001 -100001 -1000001 -10000001 -100000001 -1000000001 -10000000001 -100000000001 -1000000000001 -10000000000001 -100000000000001 -1000000000000001 -10000000000000001 -100000000000000001 -1000000000000000001 -10000000000000000001
1783+-1 -11 -101 -1001 -10001 -100001 -1000001 -10000001 -100000001 -1000000001 -10000000001 -100000000001 -1000000000001 -10000000000001 -100000000000001 -1000000000000001 -10000000000000001 -100000000000000001 -1000000000000000001 -10000000000000000001
1784+select conv(1,10,16),conv((1<<2)-1,10,16),conv((1<<10)-2,10,16),conv((1<<16)-3,10,16),conv((1<<25)-4,10,16),conv((1<<31)-5,10,16),conv((1<<36)-6,10,16),conv((1<<47)-7,10,16),conv((1<<48)-8,10,16),conv((1<<55)-9,10,16),conv((1<<56)-10,10,16),conv((1<<63)-11,10,16);
1785+conv(1,10,16) conv((1<<2)-1,10,16) conv((1<<10)-2,10,16) conv((1<<16)-3,10,16) conv((1<<25)-4,10,16) conv((1<<31)-5,10,16) conv((1<<36)-6,10,16) conv((1<<47)-7,10,16) conv((1<<48)-8,10,16) conv((1<<55)-9,10,16) conv((1<<56)-10,10,16) conv((1<<63)-11,10,16)
1786+1 3 3FE FFFD 1FFFFFC 7FFFFFFB FFFFFFFFA 7FFFFFFFFFF9 FFFFFFFFFFF8 7FFFFFFFFFFFF7 FFFFFFFFFFFFF6 7FFFFFFFFFFFFFF5
1787+create table t1 (a bigint unsigned not null, primary key(a));
1788+insert into t1 values (18446744073709551615), (0xFFFFFFFFFFFFFFFE), (18446744073709551613), (18446744073709551612);
1789+select * from t1;
1790+a
1791+18446744073709551612
1792+18446744073709551613
1793+18446744073709551614
1794+18446744073709551615
1795+select * from t1 where a=18446744073709551615;
1796+a
1797+18446744073709551615
1798+delete from t1 where a=18446744073709551615;
1799+select * from t1;
1800+a
1801+18446744073709551612
1802+18446744073709551613
1803+18446744073709551614
1804+drop table t1;
1805+create table t1 ( a int not null default 1, big bigint );
1806+insert into t1 (big) values (-1),(12345678901234567),(9223372036854775807),(18446744073709551615);
1807+Warnings:
1808+Warning 1264 Out of range value for column 'big' at row 4
1809+select * from t1;
1810+a big
1811+1 -1
1812+1 12345678901234567
1813+1 9223372036854775807
1814+1 9223372036854775807
1815+select min(big),max(big),max(big)-1 from t1;
1816+min(big) max(big) max(big)-1
1817+-1 9223372036854775807 9223372036854775806
1818+select min(big),max(big),max(big)-1 from t1 group by a;
1819+min(big) max(big) max(big)-1
1820+-1 9223372036854775807 9223372036854775806
1821+alter table t1 modify big bigint unsigned not null;
1822+Warnings:
1823+Warning 1264 Out of range value for column 'big' at row 1
1824+select min(big),max(big),max(big)-1 from t1;
1825+min(big) max(big) max(big)-1
1826+0 9223372036854775807 9223372036854775806
1827+select min(big),max(big),max(big)-1 from t1 group by a;
1828+min(big) max(big) max(big)-1
1829+0 9223372036854775807 9223372036854775806
1830+insert into t1 (big) values (18446744073709551615);
1831+select * from t1;
1832+a big
1833+1 0
1834+1 12345678901234567
1835+1 9223372036854775807
1836+1 9223372036854775807
1837+1 18446744073709551615
1838+select min(big),max(big),max(big)-1 from t1;
1839+min(big) max(big) max(big)-1
1840+0 18446744073709551615 18446744073709551614
1841+select min(big),max(big),max(big)-1 from t1 group by a;
1842+min(big) max(big) max(big)-1
1843+0 18446744073709551615 18446744073709551614
1844+alter table t1 add key (big);
1845+select min(big),max(big),max(big)-1 from t1;
1846+min(big) max(big) max(big)-1
1847+0 18446744073709551615 18446744073709551614
1848+select min(big),max(big),max(big)-1 from t1 group by a;
1849+min(big) max(big) max(big)-1
1850+0 18446744073709551615 18446744073709551614
1851+alter table t1 modify big bigint not null;
1852+Warnings:
1853+Warning 1264 Out of range value for column 'big' at row 5
1854+select * from t1;
1855+a big
1856+1 0
1857+1 12345678901234567
1858+1 9223372036854775807
1859+1 9223372036854775807
1860+1 9223372036854775807
1861+select min(big),max(big),max(big)-1 from t1;
1862+min(big) max(big) max(big)-1
1863+0 9223372036854775807 9223372036854775806
1864+select min(big),max(big),max(big)-1 from t1 group by a;
1865+min(big) max(big) max(big)-1
1866+0 9223372036854775807 9223372036854775806
1867+drop table t1;
1868+create table t1 (id bigint auto_increment primary key, a int) auto_increment=9999999999;
1869+insert into t1 values (null,1);
1870+select * from t1;
1871+id a
1872+9999999999 1
1873+select * from t1 limit 9999999999;
1874+id a
1875+9999999999 1
1876+drop table t1;
1877+CREATE TABLE t1 ( quantity decimal(60,0));
1878+insert into t1 values (10000000000000000000);
1879+insert into t1 values (10000000000000000000.0);
1880+insert into t1 values ('10000000000000000000');
1881+select * from t1;
1882+quantity
1883+10000000000000000000
1884+10000000000000000000
1885+10000000000000000000
1886+drop table t1;
1887+SELECT '0x8000000000000001'+0;
1888+'0x8000000000000001'+0
1889+0
1890+Warnings:
1891+Warning 1292 Truncated incorrect DOUBLE value: '0x8000000000000001'
1892+create table t1 (
1893+value64 bigint unsigned not null,
1894+value32 integer not null,
1895+primary key(value64, value32)
1896+);
1897+create table t2 (
1898+value64 bigint unsigned not null,
1899+value32 integer not null,
1900+primary key(value64, value32)
1901+);
1902+insert into t1 values(17156792991891826145, 1);
1903+insert into t1 values( 9223372036854775807, 2);
1904+insert into t2 values(17156792991891826145, 3);
1905+insert into t2 values( 9223372036854775807, 4);
1906+select * from t1;
1907+value64 value32
1908+9223372036854775807 2
1909+17156792991891826145 1
1910+select * from t2;
1911+value64 value32
1912+9223372036854775807 4
1913+17156792991891826145 3
1914+select * from t1, t2 where t1.value64=17156792991891826145 and
1915+t2.value64=17156792991891826145;
1916+value64 value32 value64 value32
1917+17156792991891826145 1 17156792991891826145 3
1918+select * from t1, t2 where t1.value64=17156792991891826145 and
1919+t2.value64=t1.value64;
1920+value64 value32 value64 value32
1921+17156792991891826145 1 17156792991891826145 3
1922+select * from t1, t2 where t1.value64= 9223372036854775807 and
1923+t2.value64=9223372036854775807;
1924+value64 value32 value64 value32
1925+9223372036854775807 2 9223372036854775807 4
1926+select * from t1, t2 where t1.value64= 9223372036854775807 and
1927+t2.value64=t1.value64;
1928+value64 value32 value64 value32
1929+9223372036854775807 2 9223372036854775807 4
1930+drop table t1, t2;
1931+create table t1 select 1 as 'a';
1932+show create table t1;
1933+Table Create Table
1934+t1 CREATE TABLE `t1` (
1935+ `a` int(1) NOT NULL DEFAULT '0'
1936+) ENGINE=PBXT DEFAULT CHARSET=latin1
1937+drop table t1;
1938+create table t1 select 9223372036854775809 as 'a';
1939+show create table t1;
1940+Table Create Table
1941+t1 CREATE TABLE `t1` (
1942+ `a` bigint(19) unsigned NOT NULL DEFAULT '0'
1943+) ENGINE=PBXT DEFAULT CHARSET=latin1
1944+select * from t1;
1945+a
1946+9223372036854775809
1947+drop table t1;
1948+DROP DATABASE IF EXISTS `scott`;
1949+Warnings:
1950+Note 1008 Can't drop database 'scott'; database doesn't exist
1951+create table t1 (a char(100), b varchar(100), c text, d blob);
1952+insert into t1 values(
1953+18446744073709551615,18446744073709551615,
1954+18446744073709551615, 18446744073709551615
1955+);
1956+insert into t1 values (-1 | 0,-1 | 0,-1 | 0 ,-1 | 0);
1957+select * from t1;
1958+a b c d
1959+18446744073709551615 18446744073709551615 18446744073709551615 18446744073709551615
1960+18446744073709551615 18446744073709551615 18446744073709551615 18446744073709551615
1961+drop table t1;
1962+create table t1 ( quantity decimal(2) unsigned);
1963+insert into t1 values (500), (-500), (~0), (-1);
1964+Warnings:
1965+Warning 1264 Out of range value for column 'quantity' at row 1
1966+Warning 1264 Out of range value for column 'quantity' at row 2
1967+Warning 1264 Out of range value for column 'quantity' at row 3
1968+Warning 1264 Out of range value for column 'quantity' at row 4
1969+select * from t1;
1970+quantity
1971+99
1972+0
1973+99
1974+0
1975+drop table t1;
1976+CREATE TABLE t1 (
1977+`col1` INT(1) NULL,
1978+`col2` INT(2) NULL,
1979+`col3` INT(3) NULL,
1980+`col4` INT(4) NULL,
1981+`col5` INT(5) NULL,
1982+`col6` INT(6) NULL,
1983+`col7` INT(7) NULL,
1984+`col8` INT(8) NULL,
1985+`col9` INT(9) NULL,
1986+`col10` BIGINT(10) NULL,
1987+`col11` BIGINT(11) NULL,
1988+`col12` BIGINT(12) NULL,
1989+`col13` BIGINT(13) NULL,
1990+`col14` BIGINT(14) NULL,
1991+`col15` BIGINT(15) NULL,
1992+`col16` BIGINT(16) NULL,
1993+`col17` BIGINT(17) NULL,
1994+`col18` BIGINT(18) NULL,
1995+`col19` DECIMAL(19, 0) NULL,
1996+`col20` DECIMAL(20, 0) NULL,
1997+`col21` DECIMAL(21, 0) NULL,
1998+`col22` DECIMAL(22, 0) NULL,
1999+`col23` DECIMAL(23, 0) NULL,
2000+`col24` DECIMAL(24, 0) NULL,
2001+`col25` DECIMAL(25, 0) NULL,
2002+`col26` DECIMAL(26, 0) NULL,
2003+`col27` DECIMAL(27, 0) NULL,
2004+`col28` DECIMAL(28, 0) NULL,
2005+`col29` DECIMAL(29, 0) NULL,
2006+`col30` DECIMAL(30, 0) NULL,
2007+`col31` DECIMAL(31, 0) NULL,
2008+`col32` DECIMAL(32, 0) NULL,
2009+`col33` DECIMAL(33, 0) NULL,
2010+`col34` DECIMAL(34, 0) NULL,
2011+`col35` DECIMAL(35, 0) NULL,
2012+`col36` DECIMAL(36, 0) NULL,
2013+`col37` DECIMAL(37, 0) NULL,
2014+`col38` DECIMAL(38, 0) NULL,
2015+`fix1` DECIMAL(38, 1) NULL,
2016+`fix2` DECIMAL(38, 2) NULL,
2017+`fix3` DECIMAL(38, 3) NULL,
2018+`fix4` DECIMAL(38, 4) NULL,
2019+`fix5` DECIMAL(38, 5) NULL,
2020+`fix6` DECIMAL(38, 6) NULL,
2021+`fix7` DECIMAL(38, 7) NULL,
2022+`fix8` DECIMAL(38, 8) NULL,
2023+`fix9` DECIMAL(38, 9) NULL,
2024+`fix10` DECIMAL(38, 10) NULL,
2025+`fix11` DECIMAL(38, 11) NULL,
2026+`fix12` DECIMAL(38, 12) NULL,
2027+`fix13` DECIMAL(38, 13) NULL,
2028+`fix14` DECIMAL(38, 14) NULL,
2029+`fix15` DECIMAL(38, 15) NULL,
2030+`fix16` DECIMAL(38, 16) NULL,
2031+`fix17` DECIMAL(38, 17) NULL,
2032+`fix18` DECIMAL(38, 18) NULL,
2033+`fix19` DECIMAL(38, 19) NULL,
2034+`fix20` DECIMAL(38, 20) NULL,
2035+`fix21` DECIMAL(38, 21) NULL,
2036+`fix22` DECIMAL(38, 22) NULL,
2037+`fix23` DECIMAL(38, 23) NULL,
2038+`fix24` DECIMAL(38, 24) NULL,
2039+`fix25` DECIMAL(38, 25) NULL,
2040+`fix26` DECIMAL(38, 26) NULL,
2041+`fix27` DECIMAL(38, 27) NULL,
2042+`fix28` DECIMAL(38, 28) NULL,
2043+`fix29` DECIMAL(38, 29) NULL,
2044+`fix30` DECIMAL(38, 30) NULL
2045+);
2046+INSERT INTO t1(`col1`, `col2`, `col3`, `col4`, `col5`, `col6`, `col7`, `col8`, `col9`, `col10`, `col11`, `col12`, `col13`, `col14`, `col15`, `col16`, `col17`, `col18`, `col19`, `col20`, `col21`, `col22`, `col23`, `col24`, `col25`, `col26`, `col27`, `col28`, `col29`, `col30`, `col31`, `col32`, `col33`, `col34`, `col35`, `col36`, `col37`, `col38`, `fix1`, `fix2`, `fix3`, `fix4`, `fix5`, `fix6`, `fix7`, `fix8`, `fix9`, `fix10`, `fix11`, `fix12`, `fix13`, `fix14`, `fix15`, `fix16`, `fix17`, `fix18`, `fix19`, `fix20`, `fix21`, `fix22`, `fix23`, `fix24`, `fix25`, `fix26`, `fix27`, `fix28`, `fix29`, `fix30`)
2047+VALUES (9, 99, 999, 9999, 99999, 999999, 9999999, 99999999, 999999999,
2048+9999999999, 99999999999, 999999999999, 9999999999999, 99999999999999,
2049+999999999999999, 9999999999999999, 99999999999999999, 999999999999999999,
2050+9999999999999999999, 99999999999999999999, 999999999999999999999,
2051+9999999999999999999999, 99999999999999999999999, 999999999999999999999999,
2052+9999999999999999999999999, 99999999999999999999999999,
2053+999999999999999999999999999, 9999999999999999999999999999,
2054+99999999999999999999999999999, 999999999999999999999999999999,
2055+9999999999999999999999999999999, 99999999999999999999999999999999,
2056+999999999999999999999999999999999, 9999999999999999999999999999999999,
2057+99999999999999999999999999999999999, 999999999999999999999999999999999999,
2058+9999999999999999999999999999999999999, 99999999999999999999999999999999999999,
2059+9999999999999999999999999999999999999.9,
2060+999999999999999999999999999999999999.99,
2061+99999999999999999999999999999999999.999,
2062+9999999999999999999999999999999999.9999,
2063+999999999999999999999999999999999.99999,
2064+99999999999999999999999999999999.999999,
2065+9999999999999999999999999999999.9999999,
2066+999999999999999999999999999999.99999999,
2067+99999999999999999999999999999.999999999,
2068+9999999999999999999999999999.9999999999,
2069+999999999999999999999999999.99999999999,
2070+99999999999999999999999999.999999999999,
2071+9999999999999999999999999.9999999999999,
2072+999999999999999999999999.99999999999999,
2073+99999999999999999999999.999999999999999,
2074+9999999999999999999999.9999999999999999,
2075+999999999999999999999.99999999999999999,
2076+99999999999999999999.999999999999999999,
2077+9999999999999999999.9999999999999999999,
2078+999999999999999999.99999999999999999999,
2079+99999999999999999.999999999999999999999,
2080+9999999999999999.9999999999999999999999,
2081+999999999999999.99999999999999999999999,
2082+99999999999999.999999999999999999999999,
2083+9999999999999.9999999999999999999999999,
2084+999999999999.99999999999999999999999999,
2085+99999999999.999999999999999999999999999,
2086+9999999999.9999999999999999999999999999,
2087+999999999.99999999999999999999999999999,
2088+99999999.999999999999999999999999999999);
2089+SELECT * FROM t1;
2090+col1 col2 col3 col4 col5 col6 col7 col8 col9 col10 col11 col12 col13 col14 col15 col16 col17 col18 col19 col20 col21 col22 col23 col24 col25 col26 col27 col28 col29 col30 col31 col32 col33 col34 col35 col36 col37 col38 fix1 fix2 fix3 fix4 fix5 fix6 fix7 fix8 fix9 fix10 fix11 fix12 fix13 fix14 fix15 fix16 fix17 fix18 fix19 fix20 fix21 fix22 fix23 fix24 fix25 fix26 fix27 fix28 fix29 fix30
2091+9 99 999 9999 99999 999999 9999999 99999999 999999999 9999999999 99999999999 999999999999 9999999999999 99999999999999 999999999999999 9999999999999999 99999999999999999 999999999999999999 9999999999999999999 99999999999999999999 999999999999999999999 9999999999999999999999 99999999999999999999999 999999999999999999999999 9999999999999999999999999 99999999999999999999999999 999999999999999999999999999 9999999999999999999999999999 99999999999999999999999999999 999999999999999999999999999999 9999999999999999999999999999999 99999999999999999999999999999999 999999999999999999999999999999999 9999999999999999999999999999999999 99999999999999999999999999999999999 999999999999999999999999999999999999 9999999999999999999999999999999999999 99999999999999999999999999999999999999 9999999999999999999999999999999999999.9 999999999999999999999999999999999999.99 99999999999999999999999999999999999.999 9999999999999999999999999999999999.9999 999999999999999999999999999999999.99999 99999999999999999999999999999999.999999 9999999999999999999999999999999.9999999 999999999999999999999999999999.99999999 99999999999999999999999999999.999999999 9999999999999999999999999999.9999999999 999999999999999999999999999.99999999999 99999999999999999999999999.999999999999 9999999999999999999999999.9999999999999 999999999999999999999999.99999999999999 99999999999999999999999.999999999999999 9999999999999999999999.9999999999999999 999999999999999999999.99999999999999999 99999999999999999999.999999999999999999 9999999999999999999.9999999999999999999 999999999999999999.99999999999999999999 99999999999999999.999999999999999999999 9999999999999999.9999999999999999999999 999999999999999.99999999999999999999999 99999999999999.999999999999999999999999 9999999999999.9999999999999999999999999 999999999999.99999999999999999999999999 99999999999.999999999999999999999999999 9999999999.9999999999999999999999999999 999999999.99999999999999999999999999999 99999999.999999999999999999999999999999
2092+DROP TABLE t1;
2093+create table t1 (bigint_col bigint unsigned);
2094+insert into t1 values (17666000000000000000);
2095+select * from t1 where bigint_col=17666000000000000000;
2096+bigint_col
2097+17666000000000000000
2098+select * from t1 where bigint_col='17666000000000000000';
2099+bigint_col
2100+17666000000000000000
2101+drop table t1;
2102+
2103+bug 19955 -- mod is signed with bigint
2104+select cast(10000002383263201056 as unsigned) mod 50 as result;
2105+result
2106+6
2107+create table t1 (c1 bigint unsigned);
2108+insert into t1 values (10000002383263201056);
2109+select c1 mod 50 as result from t1;
2110+result
2111+6
2112+drop table t1;
2113
2114=== added file 'mysql-test/suite/pbxt/r/binary.result'
2115--- mysql-test/suite/pbxt/r/binary.result 1970-01-01 00:00:00 +0000
2116+++ mysql-test/suite/pbxt/r/binary.result 2009-04-02 10:03:14 +0000
2117@@ -0,0 +1,162 @@
2118+drop table if exists t1,t2;
2119+create table t1 (name char(20) not null, primary key (name));
2120+create table t2 (name char(20) binary not null, primary key (name));
2121+insert into t1 values ("å");
2122+insert into t1 values ("ä");
2123+insert into t1 values ("ö");
2124+insert into t2 select * from t1;
2125+select * from t1 order by name;
2126+name
2127+å
2128+ä
2129+ö
2130+select concat("*",name,"*") from t1 order by 1;
2131+concat("*",name,"*")
2132+*å*
2133+*ä*
2134+*ö*
2135+select min(name),min(concat("*",name,"*")),max(name),max(concat("*",name,"*")) from t1;
2136+min(name) min(concat("*",name,"*")) max(name) max(concat("*",name,"*"))
2137+å *å* ö *ö*
2138+select * from t2 order by name;
2139+name
2140+ä
2141+å
2142+ö
2143+select concat("*",name,"*") from t2 order by 1;
2144+concat("*",name,"*")
2145+*ä*
2146+*å*
2147+*ö*
2148+select min(name),min(concat("*",name,"*")),max(name),max(concat("*",name,"*")) from t2;
2149+min(name) min(concat("*",name,"*")) max(name) max(concat("*",name,"*"))
2150+ä *ä* ö *ö*
2151+select name from t1 where name between 'Ä' and 'Ö';
2152+name
2153+ä
2154+ö
2155+select name from t2 where name between 'ä' and 'ö';
2156+name
2157+ä
2158+å
2159+ö
2160+select name from t2 where name between 'Ä' and 'Ö';
2161+name
2162+drop table t1,t2;
2163+create table t1 (a char(10) not null, b char(10) binary not null,key (a), key(b));
2164+insert into t1 values ("hello ","hello "),("hello2 ","hello2 ");
2165+select concat("-",a,"-",b,"-") from t1 where a="hello";
2166+concat("-",a,"-",b,"-")
2167+-hello-hello-
2168+select concat("-",a,"-",b,"-") from t1 where a="hello ";
2169+concat("-",a,"-",b,"-")
2170+-hello-hello-
2171+select concat("-",a,"-",b,"-") from t1 ignore index (a) where a="hello ";
2172+concat("-",a,"-",b,"-")
2173+-hello-hello-
2174+select concat("-",a,"-",b,"-") from t1 where b="hello";
2175+concat("-",a,"-",b,"-")
2176+-hello-hello-
2177+select concat("-",a,"-",b,"-") from t1 where b="hello ";
2178+concat("-",a,"-",b,"-")
2179+-hello-hello-
2180+select concat("-",a,"-",b,"-") from t1 ignore index (b) where b="hello ";
2181+concat("-",a,"-",b,"-")
2182+-hello-hello-
2183+alter table t1 modify b tinytext not null, drop key b, add key (b(100));
2184+select concat("-",a,"-",b,"-") from t1;
2185+concat("-",a,"-",b,"-")
2186+-hello-hello-
2187+-hello2-hello2-
2188+select concat("-",a,"-",b,"-") from t1 where b="hello ";
2189+concat("-",a,"-",b,"-")
2190+-hello-hello-
2191+select concat("-",a,"-",b,"-") from t1 ignore index (b) where b="hello ";
2192+concat("-",a,"-",b,"-")
2193+-hello-hello-
2194+drop table t1;
2195+create table t1 (b char(8));
2196+insert into t1 values(NULL);
2197+select b from t1 where binary b like '';
2198+b
2199+select b from t1 group by binary b like '';
2200+b
2201+NULL
2202+select b from t1 having binary b like '';
2203+b
2204+drop table t1;
2205+create table t1 (a char(3) binary, b binary(3));
2206+insert into t1 values ('aaa','bbb'),('AAA','BBB');
2207+select upper(a),upper(b) from t1;
2208+upper(a) upper(b)
2209+AAA bbb
2210+AAA BBB
2211+select lower(a),lower(b) from t1;
2212+lower(a) lower(b)
2213+aaa bbb
2214+aaa BBB
2215+select * from t1 where upper(a)='AAA';
2216+a b
2217+aaa bbb
2218+AAA BBB
2219+select * from t1 where lower(a)='aaa';
2220+a b
2221+aaa bbb
2222+AAA BBB
2223+select * from t1 where upper(b)='BBB';
2224+a b
2225+AAA BBB
2226+select * from t1 where lower(b)='bbb';
2227+a b
2228+aaa bbb
2229+select charset(a), charset(b), charset(binary 'ccc') from t1 limit 1;
2230+charset(a) charset(b) charset(binary 'ccc')
2231+latin1 binary binary
2232+select collation(a), collation(b), collation(binary 'ccc') from t1 limit 1;
2233+collation(a) collation(b) collation(binary 'ccc')
2234+latin1_bin binary binary
2235+drop table t1;
2236+create table t1( firstname char(20), lastname char(20));
2237+insert into t1 values ("john","doe"),("John","Doe");
2238+select * from t1 where firstname='john' and firstname like binary 'john';
2239+firstname lastname
2240+john doe
2241+select * from t1 where firstname='john' and binary 'john' = firstname;
2242+firstname lastname
2243+john doe
2244+select * from t1 where firstname='john' and firstname = binary 'john';
2245+firstname lastname
2246+john doe
2247+select * from t1 where firstname='John' and firstname like binary 'john';
2248+firstname lastname
2249+john doe
2250+select * from t1 where firstname='john' and firstname like binary 'John';
2251+firstname lastname
2252+John Doe
2253+drop table t1;
2254+create table t1 (a binary);
2255+show create table t1;
2256+Table Create Table
2257+t1 CREATE TABLE `t1` (
2258+ `a` binary(1) DEFAULT NULL
2259+) ENGINE=PBXT DEFAULT CHARSET=latin1
2260+drop table t1;
2261+create table t1 (col1 binary(4));
2262+insert into t1 values ('a'),('a ');
2263+select hex(col1) from t1;
2264+hex(col1)
2265+61000000
2266+61200000
2267+alter table t1 modify col1 binary(10);
2268+select hex(col1) from t1;
2269+hex(col1)
2270+61000000000000000000
2271+61200000000000000000
2272+insert into t1 values ('b'),('b ');
2273+select hex(col1) from t1;
2274+hex(col1)
2275+61000000000000000000
2276+61200000000000000000
2277+62000000000000000000
2278+62200000000000000000
2279+drop table t1;
2280
2281=== added file 'mysql-test/suite/pbxt/r/bool.result'
2282--- mysql-test/suite/pbxt/r/bool.result 1970-01-01 00:00:00 +0000
2283+++ mysql-test/suite/pbxt/r/bool.result 2009-04-02 10:03:14 +0000
2284@@ -0,0 +1,88 @@
2285+DROP TABLE IF EXISTS t1;
2286+SELECT IF(NULL AND 1, 1, 2), IF(1 AND NULL, 1, 2);
2287+IF(NULL AND 1, 1, 2) IF(1 AND NULL, 1, 2)
2288+2 2
2289+SELECT NULL AND 1, 1 AND NULL, 0 AND NULL, NULL and 0;
2290+NULL AND 1 1 AND NULL 0 AND NULL NULL and 0
2291+NULL NULL 0 0
2292+create table t1 (a int);
2293+insert into t1 values (0),(1),(NULL);
2294+SELECT * FROM t1 WHERE IF(a AND 1, 0, 1);
2295+a
2296+0
2297+NULL
2298+SELECT * FROM t1 WHERE IF(1 AND a, 0, 1);
2299+a
2300+0
2301+NULL
2302+SELECT * FROM t1 where NOT(a AND 1);
2303+a
2304+0
2305+SELECT * FROM t1 where NOT(1 AND a);
2306+a
2307+0
2308+SELECT * FROM t1 where (a AND 1)=0;
2309+a
2310+0
2311+SELECT * FROM t1 where (1 AND a)=0;
2312+a
2313+0
2314+SELECT * FROM t1 where (1 AND a)=1;
2315+a
2316+1
2317+SELECT * FROM t1 where (1 AND a) IS NULL;
2318+a
2319+NULL
2320+set sql_mode='high_not_precedence';
2321+select * from t1 where not a between 2 and 3;
2322+a
2323+set sql_mode=default;
2324+select * from t1 where not a between 2 and 3;
2325+a
2326+0
2327+1
2328+select a, a is false, a is true, a is unknown from t1;
2329+a a is false a is true a is unknown
2330+0 1 0 0
2331+1 0 1 0
2332+NULL 0 0 1
2333+select a, a is not false, a is not true, a is not unknown from t1;
2334+a a is not false a is not true a is not unknown
2335+0 0 1 1
2336+1 1 0 1
2337+NULL 1 1 0
2338+SET @a=0, @b=0;
2339+SELECT * FROM t1 WHERE NULL AND (@a:=@a+1);
2340+a
2341+SELECT * FROM t1 WHERE NOT(a>=0 AND NULL AND (@b:=@b+1));
2342+a
2343+SELECT * FROM t1 WHERE a=2 OR (NULL AND (@a:=@a+1));
2344+a
2345+SELECT * FROM t1 WHERE NOT(a=2 OR (NULL AND (@b:=@b+1)));
2346+a
2347+DROP TABLE t1;
2348+create table t1 (a int, b int);
2349+insert into t1 values(null, null), (0, null), (1, null), (null, 0), (null, 1), (0, 0), (0, 1), (1, 0), (1, 1);
2350+select ifnull(A, 'N') as A, ifnull(B, 'N') as B, ifnull(not A, 'N') as nA, ifnull(not B, 'N') as nB, ifnull(A and B, 'N') as AB, ifnull(not (A and B), 'N') as `n(AB)`, ifnull((not A or not B), 'N') as nAonB, ifnull(A or B, 'N') as AoB, ifnull(not(A or B), 'N') as `n(AoB)`, ifnull(not A and not B, 'N') as nAnB from t1;
2351+A B nA nB AB n(AB) nAonB AoB n(AoB) nAnB
2352+N N N N N N N N N N
2353+0 N 1 N 0 1 1 N N N
2354+1 N 0 N N N N 1 0 0
2355+N 0 N 1 0 1 1 N N N
2356+N 1 N 0 N N N 1 0 0
2357+0 0 1 1 0 1 1 0 1 1
2358+0 1 1 0 0 1 1 1 0 0
2359+1 0 0 1 0 1 1 1 0 0
2360+1 1 0 0 1 0 0 1 0 0
2361+select ifnull(A=1, 'N') as A, ifnull(B=1, 'N') as B, ifnull(not (A=1), 'N') as nA, ifnull(not (B=1), 'N') as nB, ifnull((A=1) and (B=1), 'N') as AB, ifnull(not ((A=1) and (B=1)), 'N') as `n(AB)`, ifnull((not (A=1) or not (B=1)), 'N') as nAonB, ifnull((A=1) or (B=1), 'N') as AoB, ifnull(not((A=1) or (B=1)), 'N') as `n(AoB)`, ifnull(not (A=1) and not (B=1), 'N') as nAnB from t1;
2362+A B nA nB AB n(AB) nAonB AoB n(AoB) nAnB
2363+N N N N N N N N N N
2364+0 N 1 N 0 1 1 N N N
2365+1 N 0 N N N N 1 0 0
2366+N 0 N 1 0 1 1 N N N
2367+N 1 N 0 N N N 1 0 0
2368+0 0 1 1 0 1 1 0 1 1
2369+0 1 1 0 0 1 1 1 0 0
2370+1 0 0 1 0 1 1 1 0 0
2371+1 1 0 0 1 0 0 1 0 0
2372+drop table t1;
2373
2374=== added file 'mysql-test/suite/pbxt/r/bulk_replace.result'
2375--- mysql-test/suite/pbxt/r/bulk_replace.result 1970-01-01 00:00:00 +0000
2376+++ mysql-test/suite/pbxt/r/bulk_replace.result 2009-04-02 10:03:14 +0000
2377@@ -0,0 +1,11 @@
2378+drop table if exists t1;
2379+CREATE TABLE t1 (a int, unique (a), b int not null, unique(b), c int not null, index(c));
2380+replace into t1 values (1,1,1),(2,2,2),(3,1,3);
2381+select * from t1 order by a;
2382+a b c
2383+2 2 2
2384+3 1 3
2385+check table t1;
2386+Table Op Msg_type Msg_text
2387+test.t1 check status OK
2388+drop table t1;
2389
2390=== added file 'mysql-test/suite/pbxt/r/case.result'
2391--- mysql-test/suite/pbxt/r/case.result 1970-01-01 00:00:00 +0000
2392+++ mysql-test/suite/pbxt/r/case.result 2009-04-02 10:03:14 +0000
2393@@ -0,0 +1,202 @@
2394+drop table if exists t1,t2;
2395+select CASE "b" when "a" then 1 when "b" then 2 END;
2396+CASE "b" when "a" then 1 when "b" then 2 END
2397+2
2398+select CASE "c" when "a" then 1 when "b" then 2 END;
2399+CASE "c" when "a" then 1 when "b" then 2 END
2400+NULL
2401+select CASE "c" when "a" then 1 when "b" then 2 ELSE 3 END;
2402+CASE "c" when "a" then 1 when "b" then 2 ELSE 3 END
2403+3
2404+select CASE BINARY "b" when "a" then 1 when "B" then 2 WHEN "b" then "ok" END;
2405+CASE BINARY "b" when "a" then 1 when "B" then 2 WHEN "b" then "ok" END
2406+ok
2407+select CASE "b" when "a" then 1 when binary "B" then 2 WHEN "b" then "ok" END;
2408+CASE "b" when "a" then 1 when binary "B" then 2 WHEN "b" then "ok" END
2409+ok
2410+select CASE concat("a","b") when concat("ab","") then "a" when "b" then "b" end;
2411+CASE concat("a","b") when concat("ab","") then "a" when "b" then "b" end
2412+a
2413+select CASE when 1=0 then "true" else "false" END;
2414+CASE when 1=0 then "true" else "false" END
2415+false
2416+select CASE 1 when 1 then "one" WHEN 2 then "two" ELSE "more" END;
2417+CASE 1 when 1 then "one" WHEN 2 then "two" ELSE "more" END
2418+one
2419+explain extended select CASE 1 when 1 then "one" WHEN 2 then "two" ELSE "more" END;
2420+id select_type table type possible_keys key key_len ref rows filtered Extra
2421+1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used
2422+Warnings:
2423+Note 1003 select (case 1 when 1 then 'one' when 2 then 'two' else 'more' end) AS `CASE 1 when 1 then "one" WHEN 2 then "two" ELSE "more" END`
2424+select CASE 2.0 when 1 then "one" WHEN 2.0 then "two" ELSE "more" END;
2425+CASE 2.0 when 1 then "one" WHEN 2.0 then "two" ELSE "more" END
2426+two
2427+select (CASE "two" when "one" then "1" WHEN "two" then "2" END) | 0;
2428+(CASE "two" when "one" then "1" WHEN "two" then "2" END) | 0
2429+2
2430+select (CASE "two" when "one" then 1.00 WHEN "two" then 2.00 END) +0.0;
2431+(CASE "two" when "one" then 1.00 WHEN "two" then 2.00 END) +0.0
2432+2.00
2433+select case 1/0 when "a" then "true" else "false" END;
2434+case 1/0 when "a" then "true" else "false" END
2435+false
2436+select case 1/0 when "a" then "true" END;
2437+case 1/0 when "a" then "true" END
2438+NULL
2439+select (case 1/0 when "a" then "true" END) | 0;
2440+(case 1/0 when "a" then "true" END) | 0
2441+NULL
2442+select (case 1/0 when "a" then "true" END) + 0.0;
2443+(case 1/0 when "a" then "true" END) + 0.0
2444+NULL
2445+select case when 1>0 then "TRUE" else "FALSE" END;
2446+case when 1>0 then "TRUE" else "FALSE" END
2447+TRUE
2448+select case when 1<0 then "TRUE" else "FALSE" END;
2449+case when 1<0 then "TRUE" else "FALSE" END
2450+FALSE
2451+create table t1 (a int);
2452+insert into t1 values(1),(2),(3),(4);
2453+select case a when 1 then 2 when 2 then 3 else 0 end as fcase, count(*) from t1 group by fcase;
2454+fcase count(*)
2455+0 2
2456+2 1
2457+3 1
2458+explain extended select case a when 1 then 2 when 2 then 3 else 0 end as fcase, count(*) from t1 group by fcase;
2459+id select_type table type possible_keys key key_len ref rows filtered Extra
2460+1 SIMPLE t1 ALL NULL NULL NULL NULL 4 100.00 Using temporary; Using filesort
2461+Warnings:
2462+Note 1003 select (case `test`.`t1`.`a` when 1 then 2 when 2 then 3 else 0 end) AS `fcase`,count(0) AS `count(*)` from `test`.`t1` group by (case `test`.`t1`.`a` when 1 then 2 when 2 then 3 else 0 end)
2463+select case a when 1 then "one" when 2 then "two" else "nothing" end as fcase, count(*) from t1 group by fcase;
2464+fcase count(*)
2465+nothing 2
2466+one 1
2467+two 1
2468+drop table t1;
2469+create table t1 (row int not null, col int not null, val varchar(255) not null);
2470+insert into t1 values (1,1,'orange'),(1,2,'large'),(2,1,'yellow'),(2,2,'medium'),(3,1,'green'),(3,2,'small');
2471+select max(case col when 1 then val else null end) as color from t1 group by row;
2472+color
2473+orange
2474+yellow
2475+green
2476+drop table t1;
2477+SET NAMES latin1;
2478+CREATE TABLE t1 SELECT
2479+CASE WHEN 1 THEN _latin1'a' COLLATE latin1_danish_ci ELSE _latin1'a' END AS c1,
2480+CASE WHEN 1 THEN _latin1'a' ELSE _latin1'a' COLLATE latin1_danish_ci END AS c2,
2481+CASE WHEN 1 THEN 'a' ELSE 1 END AS c3,
2482+CASE WHEN 1 THEN 1 ELSE 'a' END AS c4,
2483+CASE WHEN 1 THEN 'a' ELSE 1.0 END AS c5,
2484+CASE WHEN 1 THEN 1.0 ELSE 'a' END AS c6,
2485+CASE WHEN 1 THEN 1 ELSE 1.0 END AS c7,
2486+CASE WHEN 1 THEN 1.0 ELSE 1 END AS c8,
2487+CASE WHEN 1 THEN 1.0 END AS c9,
2488+CASE WHEN 1 THEN 0.1e1 else 0.1 END AS c10,
2489+CASE WHEN 1 THEN 0.1e1 else 1 END AS c11,
2490+CASE WHEN 1 THEN 0.1e1 else '1' END AS c12
2491+;
2492+SHOW CREATE TABLE t1;
2493+Table Create Table
2494+t1 CREATE TABLE `t1` (
2495+ `c1` varchar(1) CHARACTER SET latin1 COLLATE latin1_danish_ci NOT NULL DEFAULT '',
2496+ `c2` varchar(1) CHARACTER SET latin1 COLLATE latin1_danish_ci NOT NULL DEFAULT '',
2497+ `c3` varbinary(1) NOT NULL DEFAULT '',
2498+ `c4` varbinary(1) NOT NULL DEFAULT '',
2499+ `c5` varbinary(4) NOT NULL DEFAULT '',
2500+ `c6` varbinary(4) NOT NULL DEFAULT '',
2501+ `c7` decimal(2,1) NOT NULL DEFAULT '0.0',
2502+ `c8` decimal(2,1) NOT NULL DEFAULT '0.0',
2503+ `c9` decimal(2,1) DEFAULT NULL,
2504+ `c10` double NOT NULL DEFAULT '0',
2505+ `c11` double NOT NULL DEFAULT '0',
2506+ `c12` varbinary(5) NOT NULL DEFAULT ''
2507+) ENGINE=PBXT DEFAULT CHARSET=latin1
2508+DROP TABLE t1;
2509+SELECT CASE
2510+WHEN 1
2511+THEN _latin1'a' COLLATE latin1_danish_ci
2512+ELSE _latin1'a' COLLATE latin1_swedish_ci
2513+END;
2514+ERROR HY000: Illegal mix of collations (latin1_danish_ci,EXPLICIT) and (latin1_swedish_ci,EXPLICIT) for operation 'case'
2515+SELECT CASE _latin1'a' COLLATE latin1_general_ci
2516+WHEN _latin1'a' COLLATE latin1_danish_ci THEN 1
2517+WHEN _latin1'a' COLLATE latin1_swedish_ci THEN 2
2518+END;
2519+ERROR HY000: Illegal mix of collations (latin1_general_ci,EXPLICIT), (latin1_danish_ci,EXPLICIT), (latin1_swedish_ci,EXPLICIT) for operation 'case'
2520+SELECT
2521+CASE _latin1'a' COLLATE latin1_general_ci WHEN _latin1'A' THEN '1' ELSE 2 END,
2522+CASE _latin1'a' COLLATE latin1_bin WHEN _latin1'A' THEN '1' ELSE 2 END,
2523+CASE _latin1'a' WHEN _latin1'A' COLLATE latin1_swedish_ci THEN '1' ELSE 2 END,
2524+CASE _latin1'a' WHEN _latin1'A' COLLATE latin1_bin THEN '1' ELSE 2 END
2525+;
2526+CASE _latin1'a' COLLATE latin1_general_ci WHEN _latin1'A' THEN '1' ELSE 2 END CASE _latin1'a' COLLATE latin1_bin WHEN _latin1'A' THEN '1' ELSE 2 END CASE _latin1'a' WHEN _latin1'A' COLLATE latin1_swedish_ci THEN '1' ELSE 2 END CASE _latin1'a' WHEN _latin1'A' COLLATE latin1_bin THEN '1' ELSE 2 END
2527+1 2 1 2
2528+CREATE TABLE t1 SELECT COALESCE(_latin1'a',_latin2'a');
2529+ERROR HY000: Illegal mix of collations (latin1_swedish_ci,COERCIBLE) and (latin2_general_ci,COERCIBLE) for operation 'coalesce'
2530+CREATE TABLE t1 SELECT COALESCE('a' COLLATE latin1_swedish_ci,'b' COLLATE latin1_bin);
2531+ERROR HY000: Illegal mix of collations (latin1_swedish_ci,EXPLICIT) and (latin1_bin,EXPLICIT) for operation 'coalesce'
2532+CREATE TABLE t1 SELECT
2533+COALESCE(1), COALESCE(1.0),COALESCE('a'),
2534+COALESCE(1,1.0), COALESCE(1,'1'),COALESCE(1.1,'1'),
2535+COALESCE('a' COLLATE latin1_bin,'b');
2536+explain extended SELECT
2537+COALESCE(1), COALESCE(1.0),COALESCE('a'),
2538+COALESCE(1,1.0), COALESCE(1,'1'),COALESCE(1.1,'1'),
2539+COALESCE('a' COLLATE latin1_bin,'b');
2540+id select_type table type possible_keys key key_len ref rows filtered Extra
2541+1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used
2542+Warnings:
2543+Note 1003 select coalesce(1) AS `COALESCE(1)`,coalesce(1.0) AS `COALESCE(1.0)`,coalesce('a') AS `COALESCE('a')`,coalesce(1,1.0) AS `COALESCE(1,1.0)`,coalesce(1,'1') AS `COALESCE(1,'1')`,coalesce(1.1,'1') AS `COALESCE(1.1,'1')`,coalesce(('a' collate latin1_bin),'b') AS `COALESCE('a' COLLATE latin1_bin,'b')`
2544+SHOW CREATE TABLE t1;
2545+Table Create Table
2546+t1 CREATE TABLE `t1` (
2547+ `COALESCE(1)` int(1) NOT NULL DEFAULT '0',
2548+ `COALESCE(1.0)` decimal(2,1) NOT NULL DEFAULT '0.0',
2549+ `COALESCE('a')` varchar(1) NOT NULL DEFAULT '',
2550+ `COALESCE(1,1.0)` decimal(2,1) NOT NULL DEFAULT '0.0',
2551+ `COALESCE(1,'1')` varbinary(1) NOT NULL DEFAULT '',
2552+ `COALESCE(1.1,'1')` varbinary(4) NOT NULL DEFAULT '',
2553+ `COALESCE('a' COLLATE latin1_bin,'b')` varchar(1) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL DEFAULT ''
2554+) ENGINE=PBXT DEFAULT CHARSET=latin1
2555+DROP TABLE t1;
2556+SELECT 'case+union+test'
2557+UNION
2558+SELECT CASE LOWER('1') WHEN LOWER('2') THEN 'BUG' ELSE 'nobug' END;
2559+case+union+test
2560+case+union+test
2561+nobug
2562+SELECT CASE LOWER('1') WHEN LOWER('2') THEN 'BUG' ELSE 'nobug' END;
2563+CASE LOWER('1') WHEN LOWER('2') THEN 'BUG' ELSE 'nobug' END
2564+nobug
2565+SELECT 'case+union+test'
2566+UNION
2567+SELECT CASE '1' WHEN '2' THEN 'BUG' ELSE 'nobug' END;
2568+case+union+test
2569+case+union+test
2570+nobug
2571+create table t1(a float, b int default 3);
2572+insert into t1 (a) values (2), (11), (8);
2573+select min(a), min(case when 1=1 then a else NULL end),
2574+min(case when 1!=1 then NULL else a end)
2575+from t1 where b=3 group by b;
2576+min(a) min(case when 1=1 then a else NULL end) min(case when 1!=1 then NULL else a end)
2577+2 2 2
2578+drop table t1;
2579+CREATE TABLE t1 (EMPNUM INT);
2580+INSERT INTO t1 VALUES (0), (2);
2581+CREATE TABLE t2 (EMPNUM DECIMAL (4, 2));
2582+INSERT INTO t2 VALUES (0.0), (9.0);
2583+SELECT COALESCE(t2.EMPNUM,t1.EMPNUM) AS CEMPNUM,
2584+t1.EMPNUM AS EMPMUM1, t2.EMPNUM AS EMPNUM2
2585+FROM t1 LEFT JOIN t2 ON t1.EMPNUM=t2.EMPNUM;
2586+CEMPNUM EMPMUM1 EMPNUM2
2587+0.00 0 0.00
2588+2.00 2 NULL
2589+SELECT IFNULL(t2.EMPNUM,t1.EMPNUM) AS CEMPNUM,
2590+t1.EMPNUM AS EMPMUM1, t2.EMPNUM AS EMPNUM2
2591+FROM t1 LEFT JOIN t2 ON t1.EMPNUM=t2.EMPNUM;
2592+CEMPNUM EMPMUM1 EMPNUM2
2593+0.00 0 0.00
2594+2.00 2 NULL
2595+DROP TABLE t1,t2;
2596
2597=== added file 'mysql-test/suite/pbxt/r/cast.result'
2598--- mysql-test/suite/pbxt/r/cast.result 1970-01-01 00:00:00 +0000
2599+++ mysql-test/suite/pbxt/r/cast.result 2009-04-02 10:03:14 +0000
2600@@ -0,0 +1,396 @@
2601+select CAST(1-2 AS UNSIGNED);
2602+CAST(1-2 AS UNSIGNED)
2603+18446744073709551615
2604+select CAST(CAST(1-2 AS UNSIGNED) AS SIGNED INTEGER);
2605+CAST(CAST(1-2 AS UNSIGNED) AS SIGNED INTEGER)
2606+-1
2607+select CAST('10 ' as unsigned integer);
2608+CAST('10 ' as unsigned integer)
2609+10
2610+Warnings:
2611+Warning 1292 Truncated incorrect INTEGER value: '10 '
2612+select cast(-5 as unsigned) | 1, cast(-5 as unsigned) & -1;
2613+cast(-5 as unsigned) | 1 cast(-5 as unsigned) & -1
2614+18446744073709551611 18446744073709551611
2615+select cast(-5 as unsigned) -1, cast(-5 as unsigned) + 1;
2616+cast(-5 as unsigned) -1 cast(-5 as unsigned) + 1
2617+18446744073709551610 18446744073709551612
2618+select ~5, cast(~5 as signed);
2619+~5 cast(~5 as signed)
2620+18446744073709551610 -6
2621+explain extended select ~5, cast(~5 as signed);
2622+id select_type table type possible_keys key key_len ref rows filtered Extra
2623+1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used
2624+Warnings:
2625+Note 1003 select ~(5) AS `~5`,cast(~(5) as signed) AS `cast(~5 as signed)`
2626+select cast(5 as unsigned) -6.0;
2627+cast(5 as unsigned) -6.0
2628+-1.0
2629+select cast(NULL as signed), cast(1/0 as signed);
2630+cast(NULL as signed) cast(1/0 as signed)
2631+NULL NULL
2632+select cast(NULL as unsigned), cast(1/0 as unsigned);
2633+cast(NULL as unsigned) cast(1/0 as unsigned)
2634+NULL NULL
2635+select cast("A" as binary) = "a", cast(BINARY "a" as CHAR) = "A";
2636+cast("A" as binary) = "a" cast(BINARY "a" as CHAR) = "A"
2637+0 1
2638+select cast("2001-1-1" as DATE), cast("2001-1-1" as DATETIME);
2639+cast("2001-1-1" as DATE) cast("2001-1-1" as DATETIME)
2640+2001-01-01 2001-01-01 00:00:00
2641+select cast("1:2:3" as TIME);
2642+cast("1:2:3" as TIME)
2643+01:02:03
2644+select CONVERT("2004-01-22 21:45:33",DATE);
2645+CONVERT("2004-01-22 21:45:33",DATE)
2646+2004-01-22
2647+select 10+'10';
2648+10+'10'
2649+20
2650+select 10.0+'10';
2651+10.0+'10'
2652+20
2653+select 10E+0+'10';
2654+10E+0+'10'
2655+20
2656+select CONVERT(DATE "2004-01-22 21:45:33" USING latin1);
2657+CONVERT(DATE "2004-01-22 21:45:33" USING latin1)
2658+2004-01-22 21:45:33
2659+select CONVERT(DATE "2004-01-22 21:45:33",CHAR);
2660+CONVERT(DATE "2004-01-22 21:45:33",CHAR)
2661+2004-01-22 21:45:33
2662+select CONVERT(DATE "2004-01-22 21:45:33",CHAR(4));
2663+CONVERT(DATE "2004-01-22 21:45:33",CHAR(4))
2664+2004
2665+Warnings:
2666+Warning 1292 Truncated incorrect CHAR(4) value: '2004-01-22 21:45:33'
2667+select CONVERT(DATE "2004-01-22 21:45:33",BINARY(4));
2668+CONVERT(DATE "2004-01-22 21:45:33",BINARY(4))
2669+2004
2670+Warnings:
2671+Warning 1292 Truncated incorrect BINARY(4) value: '2004-01-22 21:45:33'
2672+select CAST(DATE "2004-01-22 21:45:33" AS BINARY(4));
2673+CAST(DATE "2004-01-22 21:45:33" AS BINARY(4))
2674+2004
2675+Warnings:
2676+Warning 1292 Truncated incorrect BINARY(4) value: '2004-01-22 21:45:33'
2677+select CAST(0xb3 as signed);
2678+CAST(0xb3 as signed)
2679+179
2680+select CAST(0x8fffffffffffffff as signed);
2681+CAST(0x8fffffffffffffff as signed)
2682+-8070450532247928833
2683+select CAST(0xffffffffffffffff as unsigned);
2684+CAST(0xffffffffffffffff as unsigned)
2685+18446744073709551615
2686+select CAST(0xfffffffffffffffe as signed);
2687+CAST(0xfffffffffffffffe as signed)
2688+-2
2689+select cast('-10a' as signed integer);
2690+cast('-10a' as signed integer)
2691+-10
2692+Warnings:
2693+Warning 1292 Truncated incorrect INTEGER value: '-10a'
2694+select cast('a10' as unsigned integer);
2695+cast('a10' as unsigned integer)
2696+0
2697+Warnings:
2698+Warning 1292 Truncated incorrect INTEGER value: 'a10'
2699+select 10+'a';
2700+10+'a'
2701+10
2702+Warnings:
2703+Warning 1292 Truncated incorrect DOUBLE value: 'a'
2704+select 10.0+cast('a' as decimal);
2705+10.0+cast('a' as decimal)
2706+10.0
2707+Warnings:
2708+Warning 1292 Truncated incorrect DECIMAL value: 'a'
2709+select 10E+0+'a';
2710+10E+0+'a'
2711+10
2712+Warnings:
2713+Warning 1292 Truncated incorrect DOUBLE value: 'a'
2714+select cast('18446744073709551616' as unsigned);
2715+cast('18446744073709551616' as unsigned)
2716+18446744073709551615
2717+Warnings:
2718+Warning 1292 Truncated incorrect INTEGER value: '18446744073709551616'
2719+select cast('18446744073709551616' as signed);
2720+cast('18446744073709551616' as signed)
2721+-1
2722+Warnings:
2723+Warning 1292 Truncated incorrect INTEGER value: '18446744073709551616'
2724+select cast('9223372036854775809' as signed);
2725+cast('9223372036854775809' as signed)
2726+-9223372036854775807
2727+Warnings:
2728+Warning 1105 Cast to signed converted positive out-of-range integer to it's negative complement
2729+select cast('-1' as unsigned);
2730+cast('-1' as unsigned)
2731+18446744073709551615
2732+Warnings:
2733+Warning 1105 Cast to unsigned converted negative integer to it's positive complement
2734+select cast('abc' as signed);
2735+cast('abc' as signed)
2736+0
2737+Warnings:
2738+Warning 1292 Truncated incorrect INTEGER value: 'abc'
2739+select cast('1a' as signed);
2740+cast('1a' as signed)
2741+1
2742+Warnings:
2743+Warning 1292 Truncated incorrect INTEGER value: '1a'
2744+select cast('' as signed);
2745+cast('' as signed)
2746+0
2747+Warnings:
2748+Warning 1292 Truncated incorrect INTEGER value: ''
2749+set names binary;
2750+select cast(_latin1'test' as char character set latin2);
2751+cast(_latin1'test' as char character set latin2)
2752+test
2753+select cast(_koi8r'ÔÅÓÔ' as char character set cp1251);
2754+cast(_koi8r'ÔÅÓÔ' as char character set cp1251)
2755+òåñò
2756+create table t1 select cast(_koi8r'ÔÅÓÔ' as char character set cp1251) as t;
2757+show create table t1;
2758+Table Create Table
2759+t1 CREATE TABLE `t1` (
2760+ `t` varchar(4) CHARACTER SET cp1251 NOT NULL DEFAULT ''
2761+) ENGINE=PBXT DEFAULT CHARSET=latin1
2762+drop table t1;
2763+select
2764+cast(_latin1'ab' AS char) as c1,
2765+cast(_latin1'a ' AS char) as c2,
2766+cast(_latin1'abc' AS char(2)) as c3,
2767+cast(_latin1'a ' AS char(2)) as c4,
2768+hex(cast(_latin1'a' AS char(2))) as c5;
2769+c1 c2 c3 c4 c5
2770+ab a ab a 6100
2771+Warnings:
2772+Warning 1292 Truncated incorrect BINARY(2) value: 'abc'
2773+Warning 1292 Truncated incorrect BINARY(2) value: 'a '
2774+select cast(1000 as CHAR(3));
2775+cast(1000 as CHAR(3))
2776+100
2777+Warnings:
2778+Warning 1292 Truncated incorrect BINARY(3) value: '1000'
2779+create table t1 select
2780+cast(_latin1'ab' AS char) as c1,
2781+cast(_latin1'a ' AS char) as c2,
2782+cast(_latin1'abc' AS char(2)) as c3,
2783+cast(_latin1'a ' AS char(2)) as c4,
2784+cast(_latin1'a' AS char(2)) as c5;
2785+Warnings:
2786+Warning 1292 Truncated incorrect BINARY(2) value: 'abc'
2787+Warning 1292 Truncated incorrect BINARY(2) value: 'a '
2788+select c1,c2,c3,c4,hex(c5) from t1;
2789+c1 c2 c3 c4 hex(c5)
2790+ab a ab a 6100
2791+show create table t1;
2792+Table Create Table
2793+t1 CREATE TABLE `t1` (
2794+ `c1` varbinary(2) NOT NULL DEFAULT '',
2795+ `c2` varbinary(2) NOT NULL DEFAULT '',
2796+ `c3` varbinary(2) NOT NULL DEFAULT '',
2797+ `c4` varbinary(2) NOT NULL DEFAULT '',
2798+ `c5` varbinary(2) NOT NULL DEFAULT ''
2799+) ENGINE=PBXT DEFAULT CHARSET=latin1
2800+drop table t1;
2801+select
2802+cast(_koi8r'ÆÇ' AS nchar) as c1,
2803+cast(_koi8r'Æ ' AS nchar) as c2,
2804+cast(_koi8r'ÆÇÈ' AS nchar(2)) as c3,
2805+cast(_koi8r'Æ ' AS nchar(2)) as c4,
2806+cast(_koi8r'Æ' AS nchar(2)) as c5;
2807+c1 c2 c3 c4 c5
2808+фг ф фг ф ф
2809+Warnings:
2810+Warning 1292 Truncated incorrect CHAR(4) value: 'фгх'
2811+Warning 1292 Truncated incorrect CHAR(3) value: 'Ñ„ '
2812+create table t1 select
2813+cast(_koi8r'ÆÇ' AS nchar) as c1,
2814+cast(_koi8r'Æ ' AS nchar) as c2,
2815+cast(_koi8r'ÆÇÈ' AS nchar(2)) as c3,
2816+cast(_koi8r'Æ ' AS nchar(2)) as c4,
2817+cast(_koi8r'Æ' AS nchar(2)) as c5;
2818+Warnings:
2819+Warning 1292 Truncated incorrect CHAR(4) value: 'фгх'
2820+Warning 1292 Truncated incorrect CHAR(3) value: 'Ñ„ '
2821+select * from t1;
2822+c1 c2 c3 c4 c5
2823+фг ф фг ф ф
2824+show create table t1;
2825+Table Create Table
2826+t1 CREATE TABLE `t1` (
2827+ `c1` varchar(2) CHARACTER SET utf8 NOT NULL DEFAULT '',
2828+ `c2` varchar(2) CHARACTER SET utf8 NOT NULL DEFAULT '',
2829+ `c3` varchar(2) CHARACTER SET utf8 NOT NULL DEFAULT '',
2830+ `c4` varchar(2) CHARACTER SET utf8 NOT NULL DEFAULT '',
2831+ `c5` varchar(2) CHARACTER SET utf8 NOT NULL DEFAULT ''
2832+) ENGINE=PBXT DEFAULT CHARSET=latin1
2833+drop table t1;
2834+create table t1 (a binary(4), b char(4) character set koi8r);
2835+insert into t1 values (_binary'ÔÅÓÔ',_binary'ÔÅÓÔ');
2836+select a,b,cast(a as char character set cp1251),cast(b as binary) from t1;
2837+a b cast(a as char character set cp1251) cast(b as binary)
2838+ÔÅÓÔ ÔÅÓÔ ÔÅÓÔ ÔÅÓÔ
2839+set names koi8r;
2840+select a,b,cast(a as char character set cp1251),cast(b as binary) from t1;
2841+a b cast(a as char character set cp1251) cast(b as binary)
2842+ÔÅÓÔ ÔÅÓÔ æåõæ ÔÅÓÔ
2843+set names cp1251;
2844+select a,b,cast(a as char character set cp1251),cast(b as binary) from t1;
2845+a b cast(a as char character set cp1251) cast(b as binary)
2846+ÔÅÓÔ òåñò ÔÅÓÔ ÔÅÓÔ
2847+drop table t1;
2848+set names binary;
2849+select cast("2001-1-1" as date) = "2001-01-01";
2850+cast("2001-1-1" as date) = "2001-01-01"
2851+1
2852+select cast("2001-1-1" as datetime) = "2001-01-01 00:00:00";
2853+cast("2001-1-1" as datetime) = "2001-01-01 00:00:00"
2854+1
2855+select cast("1:2:3" as TIME) = "1:02:03";
2856+cast("1:2:3" as TIME) = "1:02:03"
2857+0
2858+select cast(NULL as DATE);
2859+cast(NULL as DATE)
2860+NULL
2861+select cast(NULL as BINARY);
2862+cast(NULL as BINARY)
2863+NULL
2864+CREATE TABLE t1 (a enum ('aac','aab','aaa') not null);
2865+INSERT INTO t1 VALUES ('aaa'),('aab'),('aac');
2866+SELECT a, CAST(a AS CHAR) FROM t1 ORDER BY CAST(a AS UNSIGNED) ;
2867+a CAST(a AS CHAR)
2868+aac aac
2869+aab aab
2870+aaa aaa
2871+SELECT a, CAST(a AS CHAR(3)) FROM t1 ORDER BY CAST(a AS CHAR(2)), a;
2872+a CAST(a AS CHAR(3))
2873+aac aac
2874+aab aab
2875+aaa aaa
2876+Warnings:
2877+Warning 1292 Truncated incorrect BINARY(2) value: 'aaa'
2878+Warning 1292 Truncated incorrect BINARY(2) value: 'aab'
2879+Warning 1292 Truncated incorrect BINARY(2) value: 'aac'
2880+SELECT a, CAST(a AS UNSIGNED) FROM t1 ORDER BY CAST(a AS CHAR) ;
2881+a CAST(a AS UNSIGNED)
2882+aaa 3
2883+aab 2
2884+aac 1
2885+SELECT a, CAST(a AS CHAR(2)) FROM t1 ORDER BY CAST(a AS CHAR(3)), a;
2886+a CAST(a AS CHAR(2))
2887+aaa aa
2888+aab aa
2889+aac aa
2890+Warnings:
2891+Warning 1292 Truncated incorrect BINARY(2) value: 'aaa'
2892+Warning 1292 Truncated incorrect BINARY(2) value: 'aab'
2893+Warning 1292 Truncated incorrect BINARY(2) value: 'aac'
2894+DROP TABLE t1;
2895+select date_add(cast('2004-12-30 12:00:00' as date), interval 0 hour);
2896+date_add(cast('2004-12-30 12:00:00' as date), interval 0 hour)
2897+2004-12-30 00:00:00
2898+select timediff(cast('2004-12-30 12:00:00' as time), '12:00:00');
2899+timediff(cast('2004-12-30 12:00:00' as time), '12:00:00')
2900+00:00:00
2901+select timediff(cast('1 12:00:00' as time), '12:00:00');
2902+timediff(cast('1 12:00:00' as time), '12:00:00')
2903+24:00:00
2904+select cast(18446744073709551615 as unsigned);
2905+cast(18446744073709551615 as unsigned)
2906+18446744073709551615
2907+select cast(18446744073709551615 as signed);
2908+cast(18446744073709551615 as signed)
2909+-1
2910+select cast('18446744073709551615' as unsigned);
2911+cast('18446744073709551615' as unsigned)
2912+18446744073709551615
2913+select cast('18446744073709551615' as signed);
2914+cast('18446744073709551615' as signed)
2915+-1
2916+Warnings:
2917+Warning 1105 Cast to signed converted positive out-of-range integer to it's negative complement
2918+select cast('9223372036854775807' as signed);
2919+cast('9223372036854775807' as signed)
2920+9223372036854775807
2921+select cast(concat('184467440','73709551615') as unsigned);
2922+cast(concat('184467440','73709551615') as unsigned)
2923+18446744073709551615
2924+select cast(concat('184467440','73709551615') as signed);
2925+cast(concat('184467440','73709551615') as signed)
2926+-1
2927+Warnings:
2928+Warning 1105 Cast to signed converted positive out-of-range integer to it's negative complement
2929+select cast(repeat('1',20) as unsigned);
2930+cast(repeat('1',20) as unsigned)
2931+11111111111111111111
2932+select cast(repeat('1',20) as signed);
2933+cast(repeat('1',20) as signed)
2934+-7335632962598440505
2935+Warnings:
2936+Warning 1105 Cast to signed converted positive out-of-range integer to it's negative complement
2937+select cast(1.0e+300 as signed int);
2938+cast(1.0e+300 as signed int)
2939+9223372036854775807
2940+CREATE TABLE t1 (f1 double);
2941+INSERT INTO t1 SET f1 = -1.0e+30 ;
2942+INSERT INTO t1 SET f1 = +1.0e+30 ;
2943+SELECT f1 AS double_val, CAST(f1 AS SIGNED INT) AS cast_val FROM t1;
2944+double_val cast_val
2945+-1e+30 -9223372036854775808
2946+1e+30 9223372036854775807
2947+Warnings:
2948+Warning 1292 Truncated incorrect INTEGER value: '-1e+30'
2949+Warning 1292 Truncated incorrect INTEGER value: '1e+30'
2950+DROP TABLE t1;
2951+select cast('1.2' as decimal(3,2));
2952+cast('1.2' as decimal(3,2))
2953+1.20
2954+select 1e18 * cast('1.2' as decimal(3,2));
2955+1e18 * cast('1.2' as decimal(3,2))
2956+1.2e+18
2957+select cast(cast('1.2' as decimal(3,2)) as signed);
2958+cast(cast('1.2' as decimal(3,2)) as signed)
2959+1
2960+set @v1=1e18;
2961+select cast(@v1 as decimal(22, 2));
2962+cast(@v1 as decimal(22, 2))
2963+1000000000000000000.00
2964+select cast(-1e18 as decimal(22,2));
2965+cast(-1e18 as decimal(22,2))
2966+-1000000000000000000.00
2967+create table t1(s1 time);
2968+insert into t1 values ('11:11:11');
2969+select cast(s1 as decimal(7,2)) from t1;
2970+cast(s1 as decimal(7,2))
2971+99999.99
2972+Warnings:
2973+Error 1264 Out of range value for column 'cast(s1 as decimal(7,2))' at row 1
2974+drop table t1;
2975+CREATE TABLE t1 (v varchar(10), tt tinytext, t text,
2976+mt mediumtext, lt longtext);
2977+INSERT INTO t1 VALUES ('1.01', '2.02', '3.03', '4.04', '5.05');
2978+SELECT CAST(v AS DECIMAL), CAST(tt AS DECIMAL), CAST(t AS DECIMAL),
2979+CAST(mt AS DECIMAL), CAST(lt AS DECIMAL) from t1;
2980+CAST(v AS DECIMAL) CAST(tt AS DECIMAL) CAST(t AS DECIMAL) CAST(mt AS DECIMAL) CAST(lt AS DECIMAL)
2981+1 2 3 4 5
2982+DROP TABLE t1;
2983+select cast(NULL as decimal(6)) as t1;
2984+t1
2985+NULL
2986+set names latin1;
2987+select hex(cast('a' as char(2) binary));
2988+hex(cast('a' as char(2) binary))
2989+61
2990+select hex(cast('a' as binary(2)));
2991+hex(cast('a' as binary(2)))
2992+6100
2993+select hex(cast('a' as char(2) binary));
2994+hex(cast('a' as char(2) binary))
2995+61
2996+End of 5.0 tests
2997
2998=== added file 'mysql-test/suite/pbxt/r/check.result'
2999--- mysql-test/suite/pbxt/r/check.result 1970-01-01 00:00:00 +0000
3000+++ mysql-test/suite/pbxt/r/check.result 2009-04-02 10:03:14 +0000
3001@@ -0,0 +1,16 @@
3002+drop table if exists t1;
3003+create table t1(n int not null, key(n), key(n), key(n), key(n));
3004+check table t1 extended;
3005+insert into t1 values (200000);
3006+Table Op Msg_type Msg_text
3007+test.t1 check status OK
3008+drop table t1;
3009+Create table t1(f1 int);
3010+Create table t2(f1 int);
3011+Create view v1 as Select * from t1;
3012+Check Table v1,t2;
3013+Table Op Msg_type Msg_text
3014+test.v1 check status OK
3015+test.t2 check status OK
3016+drop view v1;
3017+drop table t1, t2;
3018
3019=== added file 'mysql-test/suite/pbxt/r/client_xml.result'
3020--- mysql-test/suite/pbxt/r/client_xml.result 1970-01-01 00:00:00 +0000
3021+++ mysql-test/suite/pbxt/r/client_xml.result 2009-04-02 10:03:14 +0000
3022@@ -0,0 +1,74 @@
3023+create table t1 (
3024+`a&b` int,
3025+`a<b` int,
3026+`a>b` text
3027+);
3028+insert into t1 values (1, 2, 'a&b a<b a>b');
3029+<?xml version="1.0"?>
3030+
3031+<resultset statement="select * from t1
3032+" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
3033+ <row>
3034+ <field name="a&amp;b">1</field>
3035+ <field name="a&lt;b">2</field>
3036+ <field name="a&gt;b">a&amp;b a&lt;b a&gt;b</field>
3037+ </row>
3038+</resultset>
3039+<?xml version="1.0"?>
3040+<mysqldump xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
3041+<database name="test">
3042+ <table_structure name="t1">
3043+ <field Field="a&amp;b" Type="int(11)" Null="YES" Key="" Extra="" />
3044+ <field Field="a&lt;b" Type="int(11)" Null="YES" Key="" Extra="" />
3045+ <field Field="a&gt;b" Type="text" Null="YES" Key="" Extra="" />
3046+ </table_structure>
3047+ <table_data name="t1">
3048+ <row>
3049+ <field name="a&amp;b">1</field>
3050+ <field name="a&lt;b">2</field>
3051+ <field name="a&gt;b">a&amp;b a&lt;b a&gt;b</field>
3052+ </row>
3053+ </table_data>
3054+</database>
3055+</mysqldump>
3056+<?xml version="1.0"?>
3057+
3058+<resultset statement="select count(*) from t1
3059+" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
3060+ <row>
3061+ <field name="count(*)">1</field>
3062+ </row>
3063+</resultset>
3064+<?xml version="1.0"?>
3065+
3066+<resultset statement="select 1 &lt; 2 from dual
3067+" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
3068+ <row>
3069+ <field name="1 &lt; 2">1</field>
3070+ </row>
3071+</resultset>
3072+<?xml version="1.0"?>
3073+
3074+<resultset statement="select 1 &gt; 2 from dual
3075+" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
3076+ <row>
3077+ <field name="1 &gt; 2">0</field>
3078+ </row>
3079+</resultset>
3080+<?xml version="1.0"?>
3081+
3082+<resultset statement="select 1 &amp; 3 from dual
3083+" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
3084+ <row>
3085+ <field name="1 &amp; 3">1</field>
3086+ </row>
3087+</resultset>
3088+<?xml version="1.0"?>
3089+
3090+<resultset statement="select null from dual
3091+" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
3092+ <row>
3093+ <field name="NULL" xsi:nil="true" />
3094+ </row>
3095+</resultset>
3096+drop table t1;
3097
3098=== added file 'mysql-test/suite/pbxt/r/comments.result'
3099--- mysql-test/suite/pbxt/r/comments.result 1970-01-01 00:00:00 +0000
3100+++ mysql-test/suite/pbxt/r/comments.result 2009-04-02 10:03:14 +0000
3101@@ -0,0 +1,28 @@
3102+select 1+2/*hello*/+3;
3103+1+2/*hello*/+3
3104+6
3105+select 1 /* long
3106+multi line comment */;
3107+1
3108+1
3109+;
3110+ERROR 42000: Query was empty
3111+select 1 /*!32301 +1 */;
3112+1 +1
3113+2
3114+select 1 /*!52301 +1 */;
3115+1
3116+1
3117+select 1--1;
3118+1--1
3119+2
3120+select 1 --2
3121++1;
3122+1 --2
3123++1
3124+4
3125+select 1 # The rest of the row will be ignored
3126+;
3127+1
3128+1
3129+/* line with only comment */;
3130
3131=== added file 'mysql-test/suite/pbxt/r/compare.result'
3132--- mysql-test/suite/pbxt/r/compare.result 1970-01-01 00:00:00 +0000
3133+++ mysql-test/suite/pbxt/r/compare.result 2009-04-02 10:03:14 +0000
3134@@ -0,0 +1,55 @@
3135+drop table if exists t1;
3136+CREATE TABLE t1 (id CHAR(12) not null, PRIMARY KEY (id));
3137+insert into t1 values ('000000000001'),('000000000002');
3138+explain select * from t1 where id=000000000001;
3139+id select_type table type possible_keys key key_len ref rows Extra
3140+1 SIMPLE t1 index PRIMARY PRIMARY 12 NULL 2 Using where; Using index
3141+select * from t1 where id=000000000001;
3142+id
3143+000000000001
3144+delete from t1 where id=000000000002;
3145+select * from t1;
3146+id
3147+000000000001
3148+drop table t1;
3149+SELECT 'a' = 'a ';
3150+'a' = 'a '
3151+1
3152+SELECT 'a\0' < 'a';
3153+'a\0' < 'a'
3154+1
3155+SELECT 'a\0' < 'a ';
3156+'a\0' < 'a '
3157+1
3158+SELECT 'a\t' < 'a';
3159+'a\t' < 'a'
3160+1
3161+SELECT 'a\t' < 'a ';
3162+'a\t' < 'a '
3163+1
3164+CREATE TABLE t1 (a char(10) not null);
3165+INSERT INTO t1 VALUES ('a'),('a\0'),('a\t'),('a ');
3166+SELECT hex(a),STRCMP(a,'a'), STRCMP(a,'a ') FROM t1;
3167+hex(a) STRCMP(a,'a') STRCMP(a,'a ')
3168+61 0 0
3169+6100 -1 -1
3170+6109 -1 -1
3171+61 0 0
3172+DROP TABLE t1;
3173+SELECT CHAR(31) = '', '' = CHAR(31);
3174+CHAR(31) = '' '' = CHAR(31)
3175+0 0
3176+SELECT CHAR(30) = '', '' = CHAR(30);
3177+CHAR(30) = '' '' = CHAR(30)
3178+0 0
3179+create table t1 (a tinyint(1),b binary(1));
3180+insert into t1 values (0x01,0x01);
3181+select * from t1 where a=b;
3182+a b
3183+Warnings:
3184+Warning 1292 Truncated incorrect DOUBLE value: ''
3185+select * from t1 where a=b and b=0x01;
3186+a b
3187+Warnings:
3188+Warning 1292 Truncated incorrect DOUBLE value: ''
3189+drop table if exists t1;
3190
3191=== added file 'mysql-test/suite/pbxt/r/connect.result'
3192--- mysql-test/suite/pbxt/r/connect.result 1970-01-01 00:00:00 +0000
3193+++ mysql-test/suite/pbxt/r/connect.result 2009-04-02 10:03:14 +0000
3194@@ -0,0 +1,117 @@
3195+drop table if exists t1,t2;
3196+show tables;
3197+Tables_in_mysql
3198+columns_priv
3199+db
3200+event
3201+func
3202+general_log
3203+help_category
3204+help_keyword
3205+help_relation
3206+help_topic
3207+host
3208+ndb_binlog_index
3209+plugin
3210+proc
3211+procs_priv
3212+servers
3213+slow_log
3214+tables_priv
3215+time_zone
3216+time_zone_leap_second
3217+time_zone_name
3218+time_zone_transition
3219+time_zone_transition_type
3220+user
3221+show tables;
3222+Tables_in_test
3223+connect(localhost,root,z,test2,MASTER_PORT,MASTER_SOCKET);
3224+ERROR 28000: Access denied for user 'root'@'localhost' (using password: YES)
3225+connect(localhost,root,z,test,MASTER_PORT,MASTER_SOCKET);
3226+ERROR 28000: Access denied for user 'root'@'localhost' (using password: YES)
3227+grant ALL on *.* to test@localhost identified by "gambling";
3228+grant ALL on *.* to test@127.0.0.1 identified by "gambling";
3229+show tables;
3230+Tables_in_mysql
3231+columns_priv
3232+db
3233+event
3234+func
3235+general_log
3236+help_category
3237+help_keyword
3238+help_relation
3239+help_topic
3240+host
3241+ndb_binlog_index
3242+plugin
3243+proc
3244+procs_priv
3245+servers
3246+slow_log
3247+tables_priv
3248+time_zone
3249+time_zone_leap_second
3250+time_zone_name
3251+time_zone_transition
3252+time_zone_transition_type
3253+user
3254+show tables;
3255+Tables_in_test
3256+connect(localhost,test,,test2,MASTER_PORT,MASTER_SOCKET);
3257+ERROR 28000: Access denied for user 'test'@'localhost' (using password: NO)
3258+connect(localhost,test,,"",MASTER_PORT,MASTER_SOCKET);
3259+ERROR 28000: Access denied for user 'test'@'localhost' (using password: NO)
3260+connect(localhost,test,zorro,test2,MASTER_PORT,MASTER_SOCKET);
3261+ERROR 28000: Access denied for user 'test'@'localhost' (using password: YES)
3262+connect(localhost,test,zorro,test,MASTER_PORT,MASTER_SOCKET);
3263+ERROR 28000: Access denied for user 'test'@'localhost' (using password: YES)
3264+update mysql.user set password=old_password("gambling2") where user=_binary"test";
3265+flush privileges;
3266+set password="";
3267+set password='gambling3';
3268+ERROR HY000: Password hash should be a 41-digit hexadecimal number
3269+set password=old_password('gambling3');
3270+show tables;
3271+Tables_in_mysql
3272+columns_priv
3273+db
3274+event
3275+func
3276+general_log
3277+help_category
3278+help_keyword
3279+help_relation
3280+help_topic
3281+host
3282+ndb_binlog_index
3283+plugin
3284+proc
3285+procs_priv
3286+servers
3287+slow_log
3288+tables_priv
3289+time_zone
3290+time_zone_leap_second
3291+time_zone_name
3292+time_zone_transition
3293+time_zone_transition_type
3294+user
3295+show tables;
3296+Tables_in_test
3297+connect(localhost,test,,test2,MASTER_PORT,MASTER_SOCKET);
3298+ERROR 28000: Access denied for user 'test'@'localhost' (using password: NO)
3299+connect(localhost,test,,test,MASTER_PORT,MASTER_SOCKET);
3300+ERROR 28000: Access denied for user 'test'@'localhost' (using password: NO)
3301+connect(localhost,test,zorro,test2,MASTER_PORT,MASTER_SOCKET);
3302+ERROR 28000: Access denied for user 'test'@'localhost' (using password: YES)
3303+connect(localhost,test,zorro,test,MASTER_PORT,MASTER_SOCKET);
3304+ERROR 28000: Access denied for user 'test'@'localhost' (using password: YES)
3305+delete from mysql.user where user=_binary"test";
3306+flush privileges;
3307+create table t1 (id integer not null auto_increment primary key);
3308+create temporary table t2(id integer not null auto_increment primary key);
3309+set @id := 1;
3310+delete from t1 where id like @id;
3311+drop table t1;
3312
3313=== added file 'mysql-test/suite/pbxt/r/consistent_snapshot.result'
3314--- mysql-test/suite/pbxt/r/consistent_snapshot.result 1970-01-01 00:00:00 +0000
3315+++ mysql-test/suite/pbxt/r/consistent_snapshot.result 2009-04-02 10:03:14 +0000
3316@@ -0,0 +1,15 @@
3317+drop table if exists t1;
3318+create table t1 (a int) engine=innodb;
3319+start transaction with consistent snapshot;
3320+insert into t1 values(1);
3321+select * from t1;
3322+a
3323+commit;
3324+delete from t1;
3325+start transaction;
3326+insert into t1 values(1);
3327+select * from t1;
3328+a
3329+1
3330+commit;
3331+drop table t1;
3332
3333=== added file 'mysql-test/suite/pbxt/r/constraints.result'
3334--- mysql-test/suite/pbxt/r/constraints.result 1970-01-01 00:00:00 +0000
3335+++ mysql-test/suite/pbxt/r/constraints.result 2009-04-02 10:03:14 +0000
3336@@ -0,0 +1,29 @@
3337+drop table if exists t1;
3338+create table t1 (a int check (a>0));
3339+insert into t1 values (1);
3340+insert into t1 values (0);
3341+drop table t1;
3342+create table t1 (a int ,b int, check a>b);
3343+insert into t1 values (1,0);
3344+insert into t1 values (0,1);
3345+drop table t1;
3346+create table t1 (a int ,b int, constraint abc check (a>b));
3347+insert into t1 values (1,0);
3348+insert into t1 values (0,1);
3349+drop table t1;
3350+create table t1 (a int null);
3351+insert into t1 values (1),(NULL);
3352+drop table t1;
3353+create table t1 (a int null);
3354+alter table t1 add constraint constraint_1 unique (a);
3355+alter table t1 add constraint unique key_1(a);
3356+alter table t1 add constraint constraint_2 unique key_2(a);
3357+show create table t1;
3358+Table Create Table
3359+t1 CREATE TABLE `t1` (
3360+ `a` int(11) DEFAULT NULL,
3361+ UNIQUE KEY `constraint_1` (`a`),
3362+ UNIQUE KEY `key_1` (`a`),
3363+ UNIQUE KEY `key_2` (`a`)
3364+) ENGINE=PBXT DEFAULT CHARSET=latin1
3365+drop table t1;
3366
3367=== added file 'mysql-test/suite/pbxt/r/contributors.result'
3368--- mysql-test/suite/pbxt/r/contributors.result 1970-01-01 00:00:00 +0000
3369+++ mysql-test/suite/pbxt/r/contributors.result 2009-04-02 10:03:14 +0000
3370@@ -0,0 +1,5 @@
3371+SHOW CONTRIBUTORS;
3372+Name Location Comment
3373+Ronald Bradford Brisbane, Australia EFF contribution for UC2006 Auction
3374+Sheeri Kritzer Boston, Mass. USA EFF contribution for UC2006 Auction
3375+Mark Shuttleworth London, UK. EFF contribution for UC2006 Auction
3376
3377=== added file 'mysql-test/suite/pbxt/r/count_distinct.result'
3378--- mysql-test/suite/pbxt/r/count_distinct.result 1970-01-01 00:00:00 +0000
3379+++ mysql-test/suite/pbxt/r/count_distinct.result 2009-04-02 10:03:14 +0000
3380@@ -0,0 +1,68 @@
3381+drop table if exists t1,t2,t3;
3382+create table t1 (libname varchar(21) not null, city text, primary key (libname));
3383+create table t2 (isbn varchar(21) not null, author text, title text, primary key (isbn));
3384+create table t3 (isbn varchar(21) not null, libname varchar(21) not null, quantity int ,primary key (isbn,libname));
3385+insert into t2 values ('001','Daffy','A duck''s life');
3386+insert into t2 values ('002','Bugs','A rabbit\'s life');
3387+insert into t2 values ('003','Cowboy','Life on the range');
3388+insert into t2 values ('000','Anonymous','Wanna buy this book?');
3389+insert into t2 values ('004','Best Seller','One Heckuva book');
3390+insert into t2 values ('005','EveryoneBuys','This very book');
3391+insert into t2 values ('006','San Fran','It is a san fran lifestyle');
3392+insert into t2 values ('007','BerkAuthor','Cool.Berkley.the.book');
3393+insert into t3 values('000','New York Public Libra','1');
3394+insert into t3 values('001','New York Public Libra','2');
3395+insert into t3 values('002','New York Public Libra','3');
3396+insert into t3 values('003','New York Public Libra','4');
3397+insert into t3 values('004','New York Public Libra','5');
3398+insert into t3 values('005','New York Public Libra','6');
3399+insert into t3 values('006','San Fransisco Public','5');
3400+insert into t3 values('007','Berkeley Public1','3');
3401+insert into t3 values('007','Berkeley Public2','3');
3402+insert into t3 values('001','NYC Lib','8');
3403+insert into t1 values ('New York Public Libra','New York');
3404+insert into t1 values ('San Fransisco Public','San Fran');
3405+insert into t1 values ('Berkeley Public1','Berkeley');
3406+insert into t1 values ('Berkeley Public2','Berkeley');
3407+insert into t1 values ('NYC Lib','New York');
3408+select t2.isbn,city,t1.libname,count(t1.libname) as a from t3 left join t1 on t3.libname=t1.libname left join t2 on t3.isbn=t2.isbn group by city,t1.libname;
3409+isbn city libname a
3410+007 Berkeley Berkeley Public1 1
3411+007 Berkeley Berkeley Public2 1
3412+000 New York New York Public Libra 6
3413+001 New York NYC Lib 1
3414+006 San Fran San Fransisco Public 1
3415+select t2.isbn,city,t1.libname,count(distinct t1.libname) as a from t3 left join t1 on t3.libname=t1.libname left join t2 on t3.isbn=t2.isbn group by city having count(distinct t1.libname) > 1;
3416+isbn city libname a
3417+007 Berkeley Berkeley Public1 2
3418+000 New York New York Public Libra 2
3419+select t2.isbn,city,t1.libname,count(distinct t1.libname) as a from t3 left join t1 on t3.libname=t1.libname left join t2 on t3.isbn=t2.isbn group by city having count(distinct concat(t1.libname,'a')) > 1;
3420+isbn city libname a
3421+007 Berkeley Berkeley Public1 2
3422+000 New York New York Public Libra 2
3423+drop table t1, t2, t3;
3424+create table t1 (f1 int);
3425+insert into t1 values (1);
3426+create table t2 (f1 int,f2 int);
3427+select t1.f1,count(distinct t2.f2),count(distinct 1,NULL) from t1 left join t2 on t1.f1=t2.f1 group by t1.f1;
3428+f1 count(distinct t2.f2) count(distinct 1,NULL)
3429+1 0 0
3430+drop table t1,t2;
3431+create table t1 (f int);
3432+select count(distinct f) from t1;
3433+count(distinct f)
3434+0
3435+drop table t1;
3436+create table t1 (a char(3), b char(20), primary key (a, b));
3437+insert into t1 values ('ABW', 'Dutch'), ('ABW', 'English');
3438+select count(distinct a) from t1 group by b;
3439+count(distinct a)
3440+1
3441+1
3442+drop table t1;
3443+create table t1 (f1 int, f2 int);
3444+insert into t1 values (0,1),(1,2);
3445+select count(distinct if(f1,3,f2)) from t1;
3446+count(distinct if(f1,3,f2))
3447+2
3448+drop table t1;
3449
3450=== added file 'mysql-test/suite/pbxt/r/count_distinct2.result'
3451--- mysql-test/suite/pbxt/r/count_distinct2.result 1970-01-01 00:00:00 +0000
3452+++ mysql-test/suite/pbxt/r/count_distinct2.result 2009-04-02 10:03:14 +0000
3453@@ -0,0 +1,129 @@
3454+drop table if exists t1;
3455+create table t1(n1 int, n2 int, s char(20), vs varchar(20), t text);
3456+insert into t1 values (1,11, 'one','eleven', 'eleven'),
3457+(1,11, 'one','eleven', 'eleven'),
3458+(2,11, 'two','eleven', 'eleven'),
3459+(2,12, 'two','twevle', 'twelve'),
3460+(2,13, 'two','thirteen', 'foo'),
3461+(2,13, 'two','thirteen', 'foo'),
3462+(2,13, 'two','thirteen', 'bar'),
3463+(NULL,13, 'two','thirteen', 'bar'),
3464+(2,NULL, 'two','thirteen', 'bar'),
3465+(2,13, NULL,'thirteen', 'bar'),
3466+(2,13, 'two',NULL, 'bar'),
3467+(2,13, 'two','thirteen', NULL);
3468+select distinct n1 from t1;
3469+n1
3470+1
3471+2
3472+NULL
3473+select count(distinct n1) from t1;
3474+count(distinct n1)
3475+2
3476+select distinct n2 from t1;
3477+n2
3478+11
3479+12
3480+13
3481+NULL
3482+select count(distinct n2) from t1;
3483+count(distinct n2)
3484+3
3485+select distinct s from t1;
3486+s
3487+one
3488+two
3489+NULL
3490+select count(distinct s) from t1;
3491+count(distinct s)
3492+2
3493+select distinct vs from t1;
3494+vs
3495+eleven
3496+twevle
3497+thirteen
3498+NULL
3499+select count(distinct vs) from t1;
3500+count(distinct vs)
3501+3
3502+select distinct t from t1;
3503+t
3504+eleven
3505+twelve
3506+foo
3507+bar
3508+NULL
3509+select count(distinct t) from t1;
3510+count(distinct t)
3511+4
3512+select distinct n1,n2 from t1;
3513+n1 n2
3514+1 11
3515+2 11
3516+2 12
3517+2 13
3518+NULL 13
3519+2 NULL
3520+select count(distinct n1,n2) from t1;
3521+count(distinct n1,n2)
3522+4
3523+select distinct n1,s from t1;
3524+n1 s
3525+1 one
3526+2 two
3527+NULL two
3528+2 NULL
3529+select count(distinct n1,s) from t1;
3530+count(distinct n1,s)
3531+2
3532+select distinct s,n1,vs from t1;
3533+s n1 vs
3534+one 1 eleven
3535+two 2 eleven
3536+two 2 twevle
3537+two 2 thirteen
3538+two NULL thirteen
3539+NULL 2 thirteen
3540+two 2 NULL
3541+select count(distinct s,n1,vs) from t1;
3542+count(distinct s,n1,vs)
3543+4
3544+select distinct s,t from t1;
3545+s t
3546+one eleven
3547+two eleven
3548+two twelve
3549+two foo
3550+two bar
3551+NULL bar
3552+two NULL
3553+select count(distinct s,t) from t1;
3554+count(distinct s,t)
3555+5
3556+select count(distinct n1), count(distinct n2) from t1;
3557+count(distinct n1) count(distinct n2)
3558+2 3
3559+select count(distinct n2), n1 from t1 group by n1;
3560+count(distinct n2) n1
3561+1 NULL
3562+1 1
3563+3 2
3564+drop table t1;
3565+create table t1 (n int default NULL);
3566+flush status;
3567+select count(distinct n) from t1;
3568+count(distinct n)
3569+5000
3570+show status like 'Created_tmp_disk_tables';
3571+Variable_name Value
3572+Created_tmp_disk_tables 0
3573+drop table t1;
3574+create table t1 (s text);
3575+flush status;
3576+select count(distinct s) from t1;
3577+count(distinct s)
3578+5000
3579+show status like 'Created_tmp_disk_tables';
3580+Variable_name Value
3581+Created_tmp_disk_tables 1
3582+drop table t1;
3583
3584=== added file 'mysql-test/suite/pbxt/r/count_distinct3.result'
3585--- mysql-test/suite/pbxt/r/count_distinct3.result 1970-01-01 00:00:00 +0000
3586+++ mysql-test/suite/pbxt/r/count_distinct3.result 2009-04-02 10:03:14 +0000
3587@@ -0,0 +1,8 @@
3588+DROP TABLE IF EXISTS t1, t2;
3589+CREATE TABLE t1 (id INTEGER, grp TINYINT, id_rev INTEGER);
3590+SELECT COUNT(*) FROM t1;
3591+COUNT(*)
3592+4181000
3593+SELECT COUNT(DISTINCT id) FROM t1 GROUP BY grp;
3594+DROP TABLE t1;
3595+set @@read_buffer_size=default;
3596
3597=== added file 'mysql-test/suite/pbxt/r/create.result'
3598--- mysql-test/suite/pbxt/r/create.result 1970-01-01 00:00:00 +0000
3599+++ mysql-test/suite/pbxt/r/create.result 2009-04-02 10:03:14 +0000
3600@@ -0,0 +1,837 @@
3601+drop table if exists t1,t2,t3,t4,t5;
3602+drop database if exists mysqltest;
3603+create table t1 (b char(0));
3604+insert into t1 values (""),(null);
3605+select * from t1;
3606+b
3607+
3608+NULL
3609+drop table if exists t1;
3610+create table t1 (b char(0) not null);
3611+create table if not exists t1 (b char(0) not null);
3612+Warnings:
3613+Note 1050 Table 't1' already exists
3614+insert into t1 values (""),(null);
3615+Warnings:
3616+Warning 1048 Column 'b' cannot be null
3617+select * from t1;
3618+b
3619+
3620+
3621+drop table t1;
3622+create table t1 (a int not null auto_increment,primary key (a)) engine=heap;
3623+drop table t1;
3624+create table t2 engine=heap select * from t1;
3625+ERROR 42S02: Table 'test.t1' doesn't exist
3626+create table t2 select auto+1 from t1;
3627+ERROR 42S02: Table 'test.t1' doesn't exist
3628+drop table if exists t1,t2;
3629+Warnings:
3630+Note 1051 Unknown table 't1'
3631+Note 1051 Unknown table 't2'
3632+create table t1 (b char(0) not null, index(b));
3633+ERROR 42000: The used storage engine can't index column 'b'
3634+create table t1 (a int not null,b text) engine=heap;
3635+ERROR 42000: The used table type doesn't support BLOB/TEXT columns
3636+drop table if exists t1;
3637+Warnings:
3638+Note 1051 Unknown table 't1'
3639+create table t1 (ordid int(8) not null auto_increment, ord varchar(50) not null, primary key (ord,ordid)) engine=heap;
3640+ERROR 42000: Incorrect table definition; there can be only one auto column and it must be defined as a key
3641+create table not_existing_database.test (a int);
3642+ERROR 42000: Unknown database 'not_existing_database'
3643+create table `a/a` (a int);
3644+show create table `a/a`;
3645+Table Create Table
3646+a/a CREATE TABLE `a/a` (
3647+ `a` int(11) DEFAULT NULL
3648+) ENGINE=PBXT DEFAULT CHARSET=latin1
3649+create table t1 like `a/a`;
3650+drop table `a/a`;
3651+drop table `t1`;
3652+create table `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa` (aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa int);
3653+ERROR 42000: Incorrect table name 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa'
3654+create table a (`aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa` int);
3655+ERROR 42000: Identifier name 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa' is too long
3656+create table t1 (a datetime default now());
3657+ERROR 42000: Invalid default value for 'a'
3658+create table t1 (a datetime on update now());
3659+ERROR HY000: Invalid ON UPDATE clause for 'a' column
3660+create table t1 (a int default 100 auto_increment);
3661+ERROR 42000: Invalid default value for 'a'
3662+create table t1 (a tinyint default 1000);
3663+ERROR 42000: Invalid default value for 'a'
3664+create table t1 (a varchar(5) default 'abcdef');
3665+ERROR 42000: Invalid default value for 'a'
3666+create table t1 (a varchar(5) default 'abcde');
3667+insert into t1 values();
3668+select * from t1;
3669+a
3670+abcde
3671+alter table t1 alter column a set default 'abcdef';
3672+ERROR 42000: Invalid default value for 'a'
3673+drop table t1;
3674+create table 1ea10 (1a20 int,1e int);
3675+insert into 1ea10 values(1,1);
3676+select 1ea10.1a20,1e+ 1e+10 from 1ea10;
3677+1a20 1e+ 1e+10
3678+1 10000000001
3679+drop table 1ea10;
3680+create table t1 (t1.index int);
3681+drop table t1;
3682+drop database if exists mysqltest;
3683+Warnings:
3684+Note 1008 Can't drop database 'mysqltest'; database doesn't exist
3685+create database mysqltest;
3686+create table mysqltest.$test1 (a$1 int, $b int, c$ int);
3687+insert into mysqltest.$test1 values (1,2,3);
3688+select a$1, $b, c$ from mysqltest.$test1;
3689+a$1 $b c$
3690+1 2 3
3691+create table mysqltest.test2$ (a int);
3692+drop table mysqltest.test2$;
3693+drop database mysqltest;
3694+create table `` (a int);
3695+ERROR 42000: Incorrect table name ''
3696+drop table if exists ``;
3697+ERROR 42000: Incorrect table name ''
3698+create table t1 (`` int);
3699+ERROR 42000: Incorrect column name ''
3700+create table t1 (i int, index `` (i));
3701+ERROR 42000: Incorrect index name ''
3702+create table t1 (a int auto_increment not null primary key, B CHAR(20));
3703+insert into t1 (b) values ("hello"),("my"),("world");
3704+create table t2 (key (b)) select * from t1;
3705+explain select * from t2 where b="world";
3706+id select_type table type possible_keys key key_len ref rows Extra
3707+1 SIMPLE t2 ref B B 21 const 1 Using where
3708+select * from t2 where b="world";
3709+a B
3710+3 world
3711+drop table t1,t2;
3712+create table t1(x varchar(50) );
3713+create table t2 select x from t1 where 1=2;
3714+describe t1;
3715+Field Type Null Key Default Extra
3716+x varchar(50) YES NULL
3717+describe t2;
3718+Field Type Null Key Default Extra
3719+x varchar(50) YES NULL
3720+drop table t2;
3721+create table t2 select now() as a , curtime() as b, curdate() as c , 1+1 as d , 1.0 + 1 as e , 33333333333333333 + 3 as f;
3722+describe t2;
3723+Field Type Null Key Default Extra
3724+a datetime NO 0000-00-00 00:00:00
3725+b time NO 00:00:00
3726+c date NO 0000-00-00
3727+d int(3) NO 0
3728+e decimal(3,1) NO 0.0
3729+f bigint(19) NO 0
3730+drop table t2;
3731+create table t2 select CAST("2001-12-29" AS DATE) as d, CAST("20:45:11" AS TIME) as t, CAST("2001-12-29 20:45:11" AS DATETIME) as dt;
3732+describe t2;
3733+Field Type Null Key Default Extra
3734+d date YES NULL
3735+t time YES NULL
3736+dt datetime YES NULL
3737+drop table t1,t2;
3738+create table t1 (a tinyint);
3739+create table t2 (a int) select * from t1;
3740+describe t1;
3741+Field Type Null Key Default Extra
3742+a tinyint(4) YES NULL
3743+describe t2;
3744+Field Type Null Key Default Extra
3745+a int(11) YES NULL
3746+drop table if exists t2;
3747+create table t2 (a int, a float) select * from t1;
3748+ERROR 42S21: Duplicate column name 'a'
3749+drop table if exists t2;
3750+Warnings:
3751+Note 1051 Unknown table 't2'
3752+create table t2 (a int) select a as b, a+1 as b from t1;
3753+ERROR 42S21: Duplicate column name 'b'
3754+drop table if exists t2;
3755+Warnings:
3756+Note 1051 Unknown table 't2'
3757+create table t2 (b int) select a as b, a+1 as b from t1;
3758+ERROR 42S21: Duplicate column name 'b'
3759+drop table if exists t1,t2;
3760+Warnings:
3761+Note 1051 Unknown table 't2'
3762+CREATE TABLE t1 (a int not null);
3763+INSERT INTO t1 values (1),(2),(1);
3764+CREATE TABLE t2 (primary key(a)) SELECT * FROM t1;
3765+ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
3766+SELECT * from t2;
3767+ERROR 42S02: Table 'test.t2' doesn't exist
3768+DROP TABLE t1;
3769+DROP TABLE IF EXISTS t2;
3770+Warnings:
3771+Note 1051 Unknown table 't2'
3772+create table t1 (a int not null, b int, primary key(a), key (b), key (b), key (b), key (b), key (b), key (b), key (b), key (b), key (b), key (b), key (b), key (b), key (b), key (b), key (b), key (b), key (b), key (b), key (b), key (b), key (b), key (b), key (b), key (b), key (b), key (b), key (b), key (b), key (b), key (b), key (b));
3773+show create table t1;
3774+Table Create Table
3775+t1 CREATE TABLE `t1` (
3776+ `a` int(11) NOT NULL,
3777+ `b` int(11) DEFAULT NULL,
3778+ PRIMARY KEY (`a`),
3779+ KEY `b` (`b`),
3780+ KEY `b_2` (`b`),
3781+ KEY `b_3` (`b`),
3782+ KEY `b_4` (`b`),
3783+ KEY `b_5` (`b`),
3784+ KEY `b_6` (`b`),
3785+ KEY `b_7` (`b`),
3786+ KEY `b_8` (`b`),
3787+ KEY `b_9` (`b`),
3788+ KEY `b_10` (`b`),
3789+ KEY `b_11` (`b`),
3790+ KEY `b_12` (`b`),
3791+ KEY `b_13` (`b`),
3792+ KEY `b_14` (`b`),
3793+ KEY `b_15` (`b`),
3794+ KEY `b_16` (`b`),
3795+ KEY `b_17` (`b`),
3796+ KEY `b_18` (`b`),
3797+ KEY `b_19` (`b`),
3798+ KEY `b_20` (`b`),
3799+ KEY `b_21` (`b`),
3800+ KEY `b_22` (`b`),
3801+ KEY `b_23` (`b`),
3802+ KEY `b_24` (`b`),
3803+ KEY `b_25` (`b`),
3804+ KEY `b_26` (`b`),
3805+ KEY `b_27` (`b`),
3806+ KEY `b_28` (`b`),
3807+ KEY `b_29` (`b`),
3808+ KEY `b_30` (`b`),
3809+ KEY `b_31` (`b`)
3810+) ENGINE=PBXT DEFAULT CHARSET=latin1
3811+drop table t1;
3812+create table t1 select if(1,'1','0'), month("2002-08-02");
3813+drop table t1;
3814+create table t1 select if('2002'='2002','Y','N');
3815+select * from t1;
3816+if('2002'='2002','Y','N')
3817+Y
3818+drop table if exists t1;
3819+SET SESSION storage_engine="heap";
3820+SELECT @@storage_engine;
3821+@@storage_engine
3822+MEMORY
3823+CREATE TABLE t1 (a int not null);
3824+show create table t1;
3825+Table Create Table
3826+t1 CREATE TABLE `t1` (
3827+ `a` int(11) NOT NULL
3828+) ENGINE=MEMORY DEFAULT CHARSET=latin1
3829+drop table t1;
3830+SET SESSION storage_engine="gemini";
3831+ERROR 42000: Unknown table engine 'gemini'
3832+SELECT @@storage_engine;
3833+@@storage_engine
3834+MEMORY
3835+CREATE TABLE t1 (a int not null);
3836+show create table t1;
3837+Table Create Table
3838+t1 CREATE TABLE `t1` (
3839+ `a` int(11) NOT NULL
3840+) ENGINE=MEMORY DEFAULT CHARSET=latin1
3841+SET SESSION storage_engine=default;
3842+drop table t1;
3843+create table t1 ( k1 varchar(2), k2 int, primary key(k1,k2));
3844+insert into t1 values ("a", 1), ("b", 2);
3845+insert into t1 values ("c", NULL);
3846+ERROR 23000: Column 'k2' cannot be null
3847+insert into t1 values (NULL, 3);
3848+ERROR 23000: Column 'k1' cannot be null
3849+insert into t1 values (NULL, NULL);
3850+ERROR 23000: Column 'k1' cannot be null
3851+drop table t1;
3852+create table t1 select x'4132';
3853+drop table t1;
3854+create table t1 select 1,2,3;
3855+create table if not exists t1 select 1,2;
3856+Warnings:
3857+Note 1050 Table 't1' already exists
3858+create table if not exists t1 select 1,2,3,4;
3859+ERROR 21S01: Column count doesn't match value count at row 1
3860+create table if not exists t1 select 1;
3861+Warnings:
3862+Note 1050 Table 't1' already exists
3863+select * from t1;
3864+1 2 3
3865+1 2 3
3866+0 1 2
3867+0 0 1
3868+drop table t1;
3869+flush status;
3870+create table t1 (a int not null, b int, primary key (a));
3871+insert into t1 values (1,1);
3872+create table if not exists t1 select 2;
3873+Warnings:
3874+Note 1050 Table 't1' already exists
3875+Warning 1364 Field 'a' doesn't have a default value
3876+select * from t1;
3877+a b
3878+1 1
3879+0 2
3880+create table if not exists t1 select 3 as 'a',4 as 'b';
3881+Warnings:
3882+Note 1050 Table 't1' already exists
3883+create table if not exists t1 select 3 as 'a',3 as 'b';
3884+ERROR 23000: Duplicate entry '3' for key 'PRIMARY'
3885+show warnings;
3886+Level Code Message
3887+Note 1050 Table 't1' already exists
3888+Error 1062 Duplicate entry '3' for key 'PRIMARY'
3889+show status like "Opened_tables";
3890+Variable_name Value
3891+Opened_tables 3
3892+select * from t1;
3893+a b
3894+1 1
3895+0 2
3896+3 4
3897+drop table t1;
3898+create table `t1 `(a int);
3899+ERROR 42000: Incorrect table name 't1 '
3900+create database `db1 `;
3901+ERROR 42000: Incorrect database name 'db1 '
3902+create table t1(`a ` int);
3903+ERROR 42000: Incorrect column name 'a '
3904+create table t1 (a int,);
3905+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')' at line 1
3906+create table t1 (a int,,b int);
3907+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'b int)' at line 1
3908+create table t1 (,b int);
3909+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'b int)' at line 1
3910+create table t1 (a int, key(a));
3911+create table t2 (b int, foreign key(b) references t1(a), key(b));
3912+drop table if exists t2,t1;
3913+create table t1(id int not null, name char(20));
3914+insert into t1 values(10,'mysql'),(20,'monty- the creator');
3915+create table t2(id int not null);
3916+insert into t2 values(10),(20);
3917+create table t3 like t1;
3918+show create table t3;
3919+Table Create Table
3920+t3 CREATE TABLE `t3` (
3921+ `id` int(11) NOT NULL,
3922+ `name` char(20) DEFAULT NULL
3923+) ENGINE=PBXT DEFAULT CHARSET=latin1
3924+select * from t3;
3925+id name
3926+create table if not exists t3 like t1;
3927+Warnings:
3928+Note 1050 Table 't3' already exists
3929+select @@warning_count;
3930+@@warning_count
3931+1
3932+create temporary table t3 like t2;
3933+show create table t3;
3934+Table Create Table
3935+t3 CREATE TEMPORARY TABLE `t3` (
3936+ `id` int(11) NOT NULL
3937+) ENGINE=PBXT DEFAULT CHARSET=latin1
3938+select * from t3;
3939+id
3940+drop table t3;
3941+show create table t3;
3942+Table Create Table
3943+t3 CREATE TABLE `t3` (
3944+ `id` int(11) NOT NULL,
3945+ `name` char(20) DEFAULT NULL
3946+) ENGINE=PBXT DEFAULT CHARSET=latin1
3947+select * from t3;
3948+id name
3949+drop table t2, t3;
3950+create database mysqltest;
3951+alter table t1;
3952+create table mysqltest.t3 like t1;
3953+create temporary table t3 like mysqltest.t3;
3954+show create table t3;
3955+Table Create Table
3956+t3 CREATE TEMPORARY TABLE `t3` (
3957+ `id` int(11) NOT NULL,
3958+ `name` char(20) DEFAULT NULL
3959+) ENGINE=PBXT DEFAULT CHARSET=latin1
3960+create table t2 like t3;
3961+show create table t2;
3962+Table Create Table
3963+t2 CREATE TABLE `t2` (
3964+ `id` int(11) NOT NULL,
3965+ `name` char(20) DEFAULT NULL
3966+) ENGINE=PBXT DEFAULT CHARSET=latin1
3967+select * from t2;
3968+id name
3969+create table t3 like t1;
3970+create table t3 like mysqltest.t3;
3971+ERROR 42S01: Table 't3' already exists
3972+create table non_existing_database.t1 like t1;
3973+ERROR 42000: Unknown database 'non_existing_database'
3974+create table t3 like non_existing_table;
3975+ERROR 42S02: Table 'test.non_existing_table' doesn't exist
3976+create temporary table t3 like t1;
3977+ERROR 42S01: Table 't3' already exists
3978+drop table t1, t2, t3;
3979+drop table t3;
3980+drop database mysqltest;
3981+SET SESSION storage_engine="heap";
3982+SELECT @@storage_engine;
3983+@@storage_engine
3984+MEMORY
3985+CREATE TABLE t1 (a int not null);
3986+show create table t1;
3987+Table Create Table
3988+t1 CREATE TABLE `t1` (
3989+ `a` int(11) NOT NULL
3990+) ENGINE=MEMORY DEFAULT CHARSET=latin1
3991+drop table t1;
3992+SET SESSION storage_engine="gemini";
3993+ERROR 42000: Unknown table engine 'gemini'
3994+SELECT @@storage_engine;
3995+@@storage_engine
3996+MEMORY
3997+CREATE TABLE t1 (a int not null);
3998+show create table t1;
3999+Table Create Table
4000+t1 CREATE TABLE `t1` (
4001+ `a` int(11) NOT NULL
4002+) ENGINE=MEMORY DEFAULT CHARSET=latin1
4003+SET SESSION storage_engine=default;
4004+drop table t1;
4005+create table t1(a int,b int,c int unsigned,d date,e char,f datetime,g time,h blob);
4006+insert into t1(a)values(1);
4007+insert into t1(a,b,c,d,e,f,g,h)
4008+values(2,-2,2,'1825-12-14','a','2003-1-1 3:2:1','4:3:2','binary data');
4009+select * from t1;
4010+a b c d e f g h
4011+1 NULL NULL NULL NULL NULL NULL NULL
4012+2 -2 2 1825-12-14 a 2003-01-01 03:02:01 04:03:02 binary data
4013+select a,
4014+ifnull(b,cast(-7 as signed)) as b,
4015+ifnull(c,cast(7 as unsigned)) as c,
4016+ifnull(d,cast('2000-01-01' as date)) as d,
4017+ifnull(e,cast('b' as char)) as e,
4018+ifnull(f,cast('2000-01-01' as datetime)) as f,
4019+ifnull(g,cast('5:4:3' as time)) as g,
4020+ifnull(h,cast('yet another binary data' as binary)) as h,
4021+addtime(cast('1:0:0' as time),cast('1:0:0' as time)) as dd
4022+from t1;
4023+a b c d e f g h dd
4024+1 -7 7 2000-01-01 b 2000-01-01 00:00:00 05:04:03 yet another binary data 02:00:00
4025+2 -2 2 1825-12-14 a 2003-01-01 03:02:01 04:03:02 binary data 02:00:00
4026+create table t2
4027+select
4028+a,
4029+ifnull(b,cast(-7 as signed)) as b,
4030+ifnull(c,cast(7 as unsigned)) as c,
4031+ifnull(d,cast('2000-01-01' as date)) as d,
4032+ifnull(e,cast('b' as char)) as e,
4033+ifnull(f,cast('2000-01-01' as datetime)) as f,
4034+ifnull(g,cast('5:4:3' as time)) as g,
4035+ifnull(h,cast('yet another binary data' as binary)) as h,
4036+addtime(cast('1:0:0' as time),cast('1:0:0' as time)) as dd
4037+from t1;
4038+explain t2;
4039+Field Type Null Key Default Extra
4040+a int(11) YES NULL
4041+b bigint(11) NO 0
4042+c bigint(11) unsigned NO 0
4043+d date YES NULL
4044+e varchar(1) NO
4045+f datetime YES NULL
4046+g time YES NULL
4047+h longblob NO NULL
4048+dd time YES NULL
4049+select * from t2;
4050+a b c d e f g h dd
4051+1 -7 7 2000-01-01 b 2000-01-01 00:00:00 05:04:03 yet another binary data 02:00:00
4052+2 -2 2 1825-12-14 a 2003-01-01 03:02:01 04:03:02 binary data 02:00:00
4053+drop table t1, t2;
4054+create table t1 (a tinyint, b smallint, c mediumint, d int, e bigint, f float(3,2), g double(4,3), h decimal(5,4), i year, j date, k timestamp, l datetime, m enum('a','b'), n set('a','b'), o char(10));
4055+create table t2 select ifnull(a,a), ifnull(b,b), ifnull(c,c), ifnull(d,d), ifnull(e,e), ifnull(f,f), ifnull(g,g), ifnull(h,h), ifnull(i,i), ifnull(j,j), ifnull(k,k), ifnull(l,l), ifnull(m,m), ifnull(n,n), ifnull(o,o) from t1;
4056+show create table t2;
4057+Table Create Table
4058+t2 CREATE TABLE `t2` (
4059+ `ifnull(a,a)` tinyint(4) DEFAULT NULL,
4060+ `ifnull(b,b)` smallint(6) DEFAULT NULL,
4061+ `ifnull(c,c)` mediumint(8) DEFAULT NULL,
4062+ `ifnull(d,d)` int(11) DEFAULT NULL,
4063+ `ifnull(e,e)` bigint(20) DEFAULT NULL,
4064+ `ifnull(f,f)` float(3,2) DEFAULT NULL,
4065+ `ifnull(g,g)` double(4,3) DEFAULT NULL,
4066+ `ifnull(h,h)` decimal(5,4) DEFAULT NULL,
4067+ `ifnull(i,i)` year(4) DEFAULT NULL,
4068+ `ifnull(j,j)` date DEFAULT NULL,
4069+ `ifnull(k,k)` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
4070+ `ifnull(l,l)` datetime DEFAULT NULL,
4071+ `ifnull(m,m)` varchar(1) DEFAULT NULL,
4072+ `ifnull(n,n)` varchar(3) DEFAULT NULL,
4073+ `ifnull(o,o)` varchar(10) DEFAULT NULL
4074+) ENGINE=PBXT DEFAULT CHARSET=latin1
4075+drop table t1,t2;
4076+create table t1(str varchar(10) default 'def',strnull varchar(10),intg int default '10',rel double default '3.14');
4077+insert into t1 values ('','',0,0.0);
4078+describe t1;
4079+Field Type Null Key Default Extra
4080+str varchar(10) YES def
4081+strnull varchar(10) YES NULL
4082+intg int(11) YES 10
4083+rel double YES 3.14
4084+create table t2 select default(str) as str, default(strnull) as strnull, default(intg) as intg, default(rel) as rel from t1;
4085+describe t2;
4086+Field Type Null Key Default Extra
4087+str varchar(10) YES NULL
4088+strnull varchar(10) YES NULL
4089+intg int(11) YES NULL
4090+rel double YES NULL
4091+drop table t1, t2;
4092+create table t1(name varchar(10), age smallint default -1);
4093+describe t1;
4094+Field Type Null Key Default Extra
4095+name varchar(10) YES NULL
4096+age smallint(6) YES -1
4097+create table t2(name varchar(10), age smallint default - 1);
4098+describe t2;
4099+Field Type Null Key Default Extra
4100+name varchar(10) YES NULL
4101+age smallint(6) YES -1
4102+drop table t1, t2;
4103+create table t1(cenum enum('a'), cset set('b'));
4104+create table t2(cenum enum('a','a'), cset set('b','b'));
4105+Warnings:
4106+Note 1291 Column 'cenum' has duplicated value 'a' in ENUM
4107+Note 1291 Column 'cset' has duplicated value 'b' in SET
4108+create table t3(cenum enum('a','A','a','c','c'), cset set('b','B','b','d','d'));
4109+Warnings:
4110+Note 1291 Column 'cenum' has duplicated value 'a' in ENUM
4111+Note 1291 Column 'cenum' has duplicated value 'A' in ENUM
4112+Note 1291 Column 'cenum' has duplicated value 'c' in ENUM
4113+Note 1291 Column 'cset' has duplicated value 'b' in SET
4114+Note 1291 Column 'cset' has duplicated value 'B' in SET
4115+Note 1291 Column 'cset' has duplicated value 'd' in SET
4116+drop table t1, t2, t3;
4117+create database mysqltest;
4118+use mysqltest;
4119+select database();
4120+database()
4121+mysqltest
4122+drop database mysqltest;
4123+select database();
4124+database()
4125+NULL
4126+create user mysqltest_1;
4127+select database(), user();
4128+database() user()
4129+NULL mysqltest_1@localhost
4130+drop user mysqltest_1;
4131+use test;
4132+create table t1 (a int, index `primary` (a));
4133+ERROR 42000: Incorrect index name 'primary'
4134+create table t1 (a int, index `PRIMARY` (a));
4135+ERROR 42000: Incorrect index name 'PRIMARY'
4136+create table t1 (`primary` int, index(`primary`));
4137+show create table t1;
4138+Table Create Table
4139+t1 CREATE TABLE `t1` (
4140+ `primary` int(11) DEFAULT NULL,
4141+ KEY `primary_2` (`primary`)
4142+) ENGINE=PBXT DEFAULT CHARSET=latin1
4143+create table t2 (`PRIMARY` int, index(`PRIMARY`));
4144+show create table t2;
4145+Table Create Table
4146+t2 CREATE TABLE `t2` (
4147+ `PRIMARY` int(11) DEFAULT NULL,
4148+ KEY `PRIMARY_2` (`PRIMARY`)
4149+) ENGINE=PBXT DEFAULT CHARSET=latin1
4150+create table t3 (a int);
4151+alter table t3 add index `primary` (a);
4152+ERROR 42000: Incorrect index name 'primary'
4153+alter table t3 add index `PRIMARY` (a);
4154+ERROR 42000: Incorrect index name 'PRIMARY'
4155+create table t4 (`primary` int);
4156+alter table t4 add index(`primary`);
4157+show create table t4;
4158+Table Create Table
4159+t4 CREATE TABLE `t4` (
4160+ `primary` int(11) DEFAULT NULL,
4161+ KEY `primary_2` (`primary`)
4162+) ENGINE=PBXT DEFAULT CHARSET=latin1
4163+create table t5 (`PRIMARY` int);
4164+alter table t5 add index(`PRIMARY`);
4165+show create table t5;
4166+Table Create Table
4167+t5 CREATE TABLE `t5` (
4168+ `PRIMARY` int(11) DEFAULT NULL,
4169+ KEY `PRIMARY_2` (`PRIMARY`)
4170+) ENGINE=PBXT DEFAULT CHARSET=latin1
4171+drop table t1, t2, t3, t4, t5;
4172+CREATE TABLE t1(id varchar(10) NOT NULL PRIMARY KEY, dsc longtext);
4173+INSERT INTO t1 VALUES ('5000000001', NULL),('5000000003', 'Test'),('5000000004', NULL);
4174+CREATE TABLE t2(id varchar(15) NOT NULL, proc varchar(100) NOT NULL, runID varchar(16) NOT NULL, start datetime NOT NULL, PRIMARY KEY (id,proc,runID,start));
4175+INSERT INTO t2 VALUES ('5000000001', 'proc01', '20031029090650', '2003-10-29 13:38:40'),('5000000001', 'proc02', '20031029090650', '2003-10-29 13:38:51'),('5000000001', 'proc03', '20031029090650', '2003-10-29 13:38:11'),('5000000002', 'proc09', '20031024013310', '2003-10-24 01:33:11'),('5000000002', 'proc09', '20031024153537', '2003-10-24 15:36:04'),('5000000004', 'proc01', '20031024013641', '2003-10-24 01:37:29'),('5000000004', 'proc02', '20031024013641', '2003-10-24 01:37:39');
4176+CREATE TABLE t3 SELECT t1.dsc,COUNT(DISTINCT t2.id) AS countOfRuns FROM t1 LEFT JOIN t2 ON (t1.id=t2.id) GROUP BY t1.id;
4177+SELECT * FROM t3;
4178+dsc countOfRuns
4179+NULL 1
4180+Test 0
4181+NULL 1
4182+drop table t1, t2, t3;
4183+create table t1 (b bool not null default false);
4184+create table t2 (b bool not null default true);
4185+insert into t1 values ();
4186+insert into t2 values ();
4187+select * from t1;
4188+b
4189+0
4190+select * from t2;
4191+b
4192+1
4193+drop table t1,t2;
4194+create table t1 (a int);
4195+create table t1 select * from t1;
4196+ERROR HY000: You can't specify target table 't1' for update in FROM clause
4197+create table t2 union = (t1) select * from t1;
4198+ERROR HY000: 'test.t2' is not BASE TABLE
4199+flush tables with read lock;
4200+unlock tables;
4201+drop table t1;
4202+create table t1(column.name int);
4203+ERROR 42000: Incorrect table name 'column'
4204+create table t1(test.column.name int);
4205+ERROR 42000: Incorrect table name 'column'
4206+create table t1(xyz.t1.name int);
4207+ERROR 42000: Incorrect database name 'xyz'
4208+create table t1(t1.name int);
4209+create table t2(test.t2.name int);
4210+drop table t1,t2;
4211+CREATE TABLE t1 (f1 VARCHAR(255) CHARACTER SET utf8);
4212+CREATE TABLE t2 AS SELECT LEFT(f1,171) AS f2 FROM t1 UNION SELECT LEFT(f1,171) AS f2 FROM t1;
4213+DESC t2;
4214+Field Type Null Key Default Extra
4215+f2 varchar(171) YES NULL
4216+DROP TABLE t1,t2;
4217+CREATE TABLE t12913 (f1 ENUM ('a','b')) AS SELECT 'a' AS f1;
4218+SELECT * FROM t12913;
4219+f1
4220+a
4221+DROP TABLE t12913;
4222+create database mysqltest;
4223+use mysqltest;
4224+drop database mysqltest;
4225+create table test.t1 like x;
4226+ERROR 3D000: No database selected
4227+drop table if exists test.t1;
4228+create database mysqltest;
4229+use mysqltest;
4230+create view v1 as select 'foo' from dual;
4231+create table t1 like v1;
4232+ERROR HY000: 'mysqltest.v1' is not BASE TABLE
4233+drop view v1;
4234+drop database mysqltest;
4235+create database mysqltest;
4236+create database if not exists mysqltest character set latin2;
4237+Warnings:
4238+Note 1007 Can't create database 'mysqltest'; database exists
4239+show create database mysqltest;
4240+Database Create Database
4241+mysqltest CREATE DATABASE `mysqltest` /*!40100 DEFAULT CHARACTER SET latin1 */
4242+drop database mysqltest;
4243+use test;
4244+create table t1 (a int);
4245+create table if not exists t1 (a int);
4246+Warnings:
4247+Note 1050 Table 't1' already exists
4248+drop table t1;
4249+create table t1 (
4250+a varchar(112) charset utf8 collate utf8_bin not null,
4251+primary key (a)
4252+) select 'test' as a ;
4253+show create table t1;
4254+Table Create Table
4255+t1 CREATE TABLE `t1` (
4256+ `a` varchar(112) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
4257+ PRIMARY KEY (`a`)
4258+) ENGINE=PBXT DEFAULT CHARSET=latin1
4259+drop table t1;
4260+CREATE TABLE t2 (
4261+a int(11) default NULL
4262+);
4263+insert into t2 values(111);
4264+create table t1 (
4265+a varchar(12) charset utf8 collate utf8_bin not null,
4266+b int not null, primary key (a)
4267+) select a, 1 as b from t2 ;
4268+show create table t1;
4269+Table Create Table
4270+t1 CREATE TABLE `t1` (
4271+ `a` varchar(12) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
4272+ `b` int(11) NOT NULL,
4273+ PRIMARY KEY (`a`)
4274+) ENGINE=PBXT DEFAULT CHARSET=latin1
4275+drop table t1;
4276+create table t1 (
4277+a varchar(12) charset utf8 collate utf8_bin not null,
4278+b int not null, primary key (a)
4279+) select a, 1 as c from t2 ;
4280+Warnings:
4281+Warning 1364 Field 'b' doesn't have a default value
4282+show create table t1;
4283+Table Create Table
4284+t1 CREATE TABLE `t1` (
4285+ `b` int(11) NOT NULL,
4286+ `a` varchar(12) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
4287+ `c` int(1) NOT NULL DEFAULT '0',
4288+ PRIMARY KEY (`a`)
4289+) ENGINE=PBXT DEFAULT CHARSET=latin1
4290+drop table t1;
4291+create table t1 (
4292+a varchar(12) charset utf8 collate utf8_bin not null,
4293+b int null, primary key (a)
4294+) select a, 1 as c from t2 ;
4295+show create table t1;
4296+Table Create Table
4297+t1 CREATE TABLE `t1` (
4298+ `b` int(11) DEFAULT NULL,
4299+ `a` varchar(12) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
4300+ `c` int(1) NOT NULL DEFAULT '0',
4301+ PRIMARY KEY (`a`)
4302+) ENGINE=PBXT DEFAULT CHARSET=latin1
4303+drop table t1;
4304+create table t1 (
4305+a varchar(12) charset utf8 collate utf8_bin not null,
4306+b int not null, primary key (a)
4307+) select 'a' as a , 1 as b from t2 ;
4308+show create table t1;
4309+Table Create Table
4310+t1 CREATE TABLE `t1` (
4311+ `a` varchar(12) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
4312+ `b` int(11) NOT NULL,
4313+ PRIMARY KEY (`a`)
4314+) ENGINE=PBXT DEFAULT CHARSET=latin1
4315+drop table t1;
4316+create table t1 (
4317+a varchar(12) charset utf8 collate utf8_bin,
4318+b int not null, primary key (a)
4319+) select 'a' as a , 1 as b from t2 ;
4320+show create table t1;
4321+Table Create Table
4322+t1 CREATE TABLE `t1` (
4323+ `a` varchar(12) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',
4324+ `b` int(11) NOT NULL,
4325+ PRIMARY KEY (`a`)
4326+) ENGINE=PBXT DEFAULT CHARSET=latin1
4327+drop table t1, t2;
4328+create table t1 (
4329+a1 int not null,
4330+a2 int, a3 int, a4 int, a5 int, a6 int, a7 int, a8 int, a9 int
4331+);
4332+insert into t1 values (1,1,1, 1,1,1, 1,1,1);
4333+create table t2 (
4334+a1 varchar(12) charset utf8 collate utf8_bin not null,
4335+a2 int, a3 int, a4 int, a5 int, a6 int, a7 int, a8 int, a9 int,
4336+primary key (a1)
4337+) select a1,a2,a3,a4,a5,a6,a7,a8,a9 from t1 ;
4338+drop table t2;
4339+create table t2 (
4340+a1 varchar(12) charset utf8 collate utf8_bin,
4341+a2 int, a3 int, a4 int, a5 int, a6 int, a7 int, a8 int, a9 int
4342+) select a1,a2,a3,a4,a5,a6,a7,a8,a9 from t1;
4343+drop table t1, t2;
4344+create table t1 (
4345+a1 int, a2 int, a3 int, a4 int, a5 int, a6 int, a7 int, a8 int, a9 int
4346+);
4347+insert into t1 values (1,1,1, 1,1,1, 1,1,1);
4348+create table t2 (
4349+a1 varchar(12) charset utf8 collate utf8_bin not null,
4350+a2 int, a3 int, a4 int, a5 int, a6 int, a7 int, a8 int, a9 int,
4351+primary key (a1)
4352+) select a1,a2,a3,a4,a5,a6,a7,a8,a9 from t1 ;
4353+drop table t2;
4354+create table t2 ( a int default 3, b int default 3)
4355+select a1,a2 from t1;
4356+show create table t2;
4357+Table Create Table
4358+t2 CREATE TABLE `t2` (
4359+ `a` int(11) DEFAULT '3',
4360+ `b` int(11) DEFAULT '3',
4361+ `a1` int(11) DEFAULT NULL,
4362+ `a2` int(11) DEFAULT NULL
4363+) ENGINE=PBXT DEFAULT CHARSET=latin1
4364+drop table t1, t2;
4365+create table t1(a set("a,b","c,d") not null);
4366+ERROR 22007: Illegal set 'a,b' value found during parsing
4367+create table t1 (i int) engine=myisam max_rows=100000000000;
4368+show create table t1;
4369+Table Create Table
4370+t1 CREATE TABLE `t1` (
4371+ `i` int(11) DEFAULT NULL
4372+) ENGINE=MyISAM DEFAULT CHARSET=latin1 MAX_ROWS=4294967295
4373+alter table t1 max_rows=100;
4374+show create table t1;
4375+Table Create Table
4376+t1 CREATE TABLE `t1` (
4377+ `i` int(11) DEFAULT NULL
4378+) ENGINE=MyISAM DEFAULT CHARSET=latin1 MAX_ROWS=100
4379+alter table t1 max_rows=100000000000;
4380+show create table t1;
4381+Table Create Table
4382+t1 CREATE TABLE `t1` (
4383+ `i` int(11) DEFAULT NULL
4384+) ENGINE=MyISAM DEFAULT CHARSET=latin1 MAX_ROWS=4294967295
4385+drop table t1;
4386+create table t1 (upgrade int);
4387+drop table t1;
4388+CREATE TABLE t1 (a int, b int);
4389+insert into t1 values (1,1),(1,2);
4390+CREATE TABLE t2 (primary key (a)) select * from t1;
4391+ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
4392+drop table if exists t2;
4393+Warnings:
4394+Note 1051 Unknown table 't2'
4395+CREATE TEMPORARY TABLE t2 (primary key (a)) select * from t1;
4396+ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
4397+drop table if exists t2;
4398+Warnings:
4399+Note 1051 Unknown table 't2'
4400+CREATE TABLE t2 (a int, b int, primary key (a));
4401+CREATE TABLE IF NOT EXISTS t2 (primary key (a)) select * from t1;
4402+ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
4403+SELECT * from t2;
4404+a b
4405+TRUNCATE table t2;
4406+INSERT INTO t2 select * from t1;
4407+ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
4408+SELECT * from t2;
4409+a b
4410+drop table t2;
4411+CREATE TEMPORARY TABLE t2 (a int, b int, primary key (a));
4412+CREATE TEMPORARY TABLE IF NOT EXISTS t2 (primary key (a)) select * from t1;
4413+ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
4414+SELECT * from t2;
4415+a b
4416+TRUNCATE table t2;
4417+INSERT INTO t2 select * from t1;
4418+ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
4419+SELECT * from t2;
4420+a b
4421+drop table t1,t2;
4422+CREATE DATABASE aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa;
4423+ERROR 42000: Incorrect database name 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa'
4424+DROP DATABASE aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa;
4425+ERROR 42000: Incorrect database name 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa'
4426+RENAME DATABASE aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa TO a;
4427+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DATABASE aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa' at line 1
4428+RENAME DATABASE mysqltest TO aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa;
4429+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DATABASE mysqltest TO aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa' at line 1
4430+create database mysqltest;
4431+RENAME DATABASE mysqltest TO aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa;
4432+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DATABASE mysqltest TO aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa' at line 1
4433+drop database mysqltest;
4434+USE aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa;
4435+ERROR 42000: Incorrect database name 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa'
4436+SHOW CREATE DATABASE aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa;
4437+ERROR 42000: Incorrect database name 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa'
4438
4439=== added file 'mysql-test/suite/pbxt/r/date_formats.result'
4440--- mysql-test/suite/pbxt/r/date_formats.result 1970-01-01 00:00:00 +0000
4441+++ mysql-test/suite/pbxt/r/date_formats.result 2009-04-02 10:03:14 +0000
4442@@ -0,0 +1,584 @@
4443+drop table if exists t1;
4444+SHOW GLOBAL VARIABLES LIKE "%_format%";
4445+Variable_name Value
4446+binlog_format <format>
4447+date_format %Y-%m-%d
4448+datetime_format %Y-%m-%d %H:%i:%s
4449+default_week_format 0
4450+time_format %H:%i:%s
4451+SHOW SESSION VARIABLES LIKE "%_format%";
4452+Variable_name Value
4453+binlog_format <format>
4454+date_format %Y-%m-%d
4455+datetime_format %Y-%m-%d %H:%i:%s
4456+default_week_format 0
4457+time_format %H:%i:%s
4458+SET time_format='%H%i%s';
4459+SET time_format='%H:%i:%s.%f';
4460+SET time_format='%h-%i-%s.%f%p';
4461+SET time_format='%h:%i:%s.%f %p';
4462+SET time_format='%h:%i:%s%p';
4463+SET date_format='%Y%m%d';
4464+SET date_format='%Y.%m.%d';
4465+SET date_format='%d.%m.%Y';
4466+SET date_format='%m-%d-%Y';
4467+set datetime_format= '%Y%m%d%H%i%s';
4468+set datetime_format= '%Y-%m-%d %H:%i:%s';
4469+set datetime_format= '%m-%d-%y %H:%i:%s.%f';
4470+set datetime_format= '%d-%m-%Y %h:%i:%s%p';
4471+set datetime_format= '%H:%i:%s %Y-%m-%d';
4472+set datetime_format= '%H:%i:%s.%f %m-%d-%Y';
4473+set datetime_format= '%h:%i:%s %p %Y-%m-%d';
4474+set datetime_format= '%h:%i:%s.%f %p %Y-%m-%d';
4475+SHOW SESSION VARIABLES LIKE "%format";
4476+Variable_name Value
4477+binlog_format <format>
4478+date_format %m-%d-%Y
4479+datetime_format %h:%i:%s.%f %p %Y-%m-%d
4480+default_week_format 0
4481+time_format %h:%i:%s%p
4482+SET time_format='%h:%i:%s';
4483+ERROR 42000: Variable 'time_format' can't be set to the value of '%h:%i:%s'
4484+SET time_format='%H %i:%s';
4485+ERROR 42000: Variable 'time_format' can't be set to the value of '%H %i:%s'
4486+SET time_format='%H::%i:%s';
4487+ERROR 42000: Variable 'time_format' can't be set to the value of '%H::%i:%s'
4488+SET time_format='%H:%i:%s%f';
4489+ERROR 42000: Variable 'time_format' can't be set to the value of '%H:%i:%s%f'
4490+SET time_format='%H:%i.%f:%s';
4491+ERROR 42000: Variable 'time_format' can't be set to the value of '%H:%i.%f:%s'
4492+SET time_format='%H:%i:%s%p';
4493+ERROR 42000: Variable 'time_format' can't be set to the value of '%H:%i:%s%p'
4494+SET time_format='%h:%i:%s.%f %p %Y-%m-%d';
4495+ERROR 42000: Variable 'time_format' can't be set to the value of '%h:%i:%s.%f %p %Y-%m-%d'
4496+SET time_format='%H%i%s.%f';
4497+ERROR 42000: Variable 'time_format' can't be set to the value of '%H%i%s.%f'
4498+SET time_format='%H:%i-%s.%f';
4499+ERROR 42000: Variable 'time_format' can't be set to the value of '%H:%i-%s.%f'
4500+SET date_format='%d.%m.%d';
4501+ERROR 42000: Variable 'date_format' can't be set to the value of '%d.%m.%d'
4502+SET datetime_format='%h.%m.%y %d.%i.%s';
4503+ERROR 42000: Variable 'datetime_format' can't be set to the value of '%h.%m.%y %d.%i.%s'
4504+set datetime_format= '%H:%i:%s.%f %p %Y-%m-%d';
4505+ERROR 42000: Variable 'datetime_format' can't be set to the value of '%H:%i:%s.%f %p %Y-%m-%d'
4506+set GLOBAL datetime_format= '%H:%i:%s %Y-%m-%d';
4507+SET SESSION datetime_format=default;
4508+select @@global.datetime_format, @@session.datetime_format;
4509+@@global.datetime_format @@session.datetime_format
4510+%H:%i:%s %Y-%m-%d %H:%i:%s %Y-%m-%d
4511+SET GLOBAL datetime_format=default;
4512+SET SESSION datetime_format=default;
4513+select @@global.datetime_format, @@session.datetime_format;
4514+@@global.datetime_format @@session.datetime_format
4515+%Y-%m-%d %H:%i:%s %Y-%m-%d %H:%i:%s
4516+SET GLOBAL date_format=default;
4517+SET GLOBAL time_format=default;
4518+SET GLOBAL datetime_format=default;
4519+SET time_format=default;
4520+SET date_format=default;
4521+SET datetime_format=default;
4522+select str_to_date(concat('15-01-2001',' 2:59:58.999'),
4523+concat('%d-%m-%Y',' ','%H:%i:%s.%f'));
4524+str_to_date(concat('15-01-2001',' 2:59:58.999'),
4525+concat('%d-%m-%Y',' ','%H:%i:%s.%f'))
4526+2001-01-15 02:59:58.999000
4527+select STR_TO_DATE('2004.12.12 22.30.61','%Y.%m.%d %T');
4528+STR_TO_DATE('2004.12.12 22.30.61','%Y.%m.%d %T')
4529+NULL
4530+Warnings:
4531+Error 1411 Incorrect time value: '22.30.61' for function str_to_date
4532+create table t1 (date char(30), format char(30) not null);
4533+insert into t1 values
4534+('2003-01-02 10:11:12', '%Y-%m-%d %H:%i:%S'),
4535+('03-01-02 8:11:2.123456', '%y-%m-%d %H:%i:%S.%#'),
4536+('2003-01-02 10:11:12 PM', '%Y-%m-%d %h:%i:%S %p'),
4537+('2003-01-02 01:11:12.12345AM', '%Y-%m-%d %h:%i:%S.%f%p'),
4538+('2003-01-02 02:11:12.12345AM', '%Y-%m-%d %h:%i:%S.%f %p'),
4539+('2003-01-02 12:11:12.12345 am', '%Y-%m-%d %h:%i:%S.%f%p'),
4540+('2003-01-02 11:11:12Pm', '%Y-%m-%d %h:%i:%S%p'),
4541+('10:20:10', '%H:%i:%s'),
4542+('10:20:10', '%h:%i:%s.%f'),
4543+('10:20:10', '%T'),
4544+('10:20:10AM', '%h:%i:%s%p'),
4545+('10:20:10AM', '%r'),
4546+('10:20:10.44AM', '%h:%i:%s.%f%p'),
4547+('15-01-2001 12:59:58', '%d-%m-%Y %H:%i:%S'),
4548+('15 September 2001', '%d %M %Y'),
4549+('15 SEPTEMB 2001', '%d %M %Y'),
4550+('15 MAY 2001', '%d %b %Y'),
4551+('15th May 2001', '%D %b %Y'),
4552+('Sunday 15 MAY 2001', '%W %d %b %Y'),
4553+('Sund 15 MAY 2001', '%W %d %b %Y'),
4554+('Tuesday 00 2002', '%W %U %Y'),
4555+('Thursday 53 1998', '%W %u %Y'),
4556+('Sunday 01 2001', '%W %v %x'),
4557+('Tuesday 52 2001', '%W %V %X'),
4558+('060 2004', '%j %Y'),
4559+('4 53 1998', '%w %u %Y'),
4560+('15-01-2001', '%d-%m-%Y %H:%i:%S'),
4561+('15-01-20', '%d-%m-%y'),
4562+('15-2001-1', '%d-%Y-%c');
4563+select date,format,str_to_date(date, format) as str_to_date from t1;
4564+date format str_to_date
4565+2003-01-02 10:11:12 %Y-%m-%d %H:%i:%S 2003-01-02 10:11:12
4566+03-01-02 8:11:2.123456 %y-%m-%d %H:%i:%S.%# 2003-01-02 08:11:02
4567+2003-01-02 10:11:12 PM %Y-%m-%d %h:%i:%S %p 2003-01-02 22:11:12
4568+2003-01-02 01:11:12.12345AM %Y-%m-%d %h:%i:%S.%f%p 2003-01-02 01:11:12.123450
4569+2003-01-02 02:11:12.12345AM %Y-%m-%d %h:%i:%S.%f %p 2003-01-02 02:11:12.123450
4570+2003-01-02 12:11:12.12345 am %Y-%m-%d %h:%i:%S.%f%p 2003-01-02 00:11:12.123450
4571+2003-01-02 11:11:12Pm %Y-%m-%d %h:%i:%S%p 2003-01-02 23:11:12
4572+10:20:10 %H:%i:%s 0000-00-00 10:20:10
4573+10:20:10 %h:%i:%s.%f 0000-00-00 10:20:10
4574+10:20:10 %T 0000-00-00 10:20:10
4575+10:20:10AM %h:%i:%s%p 0000-00-00 10:20:10
4576+10:20:10AM %r 0000-00-00 10:20:10
4577+10:20:10.44AM %h:%i:%s.%f%p 0000-00-00 10:20:10.440000
4578+15-01-2001 12:59:58 %d-%m-%Y %H:%i:%S 2001-01-15 12:59:58
4579+15 September 2001 %d %M %Y 2001-09-15 00:00:00
4580+15 SEPTEMB 2001 %d %M %Y 2001-09-15 00:00:00
4581+15 MAY 2001 %d %b %Y 2001-05-15 00:00:00
4582+15th May 2001 %D %b %Y 2001-05-15 00:00:00
4583+Sunday 15 MAY 2001 %W %d %b %Y 2001-05-15 00:00:00
4584+Sund 15 MAY 2001 %W %d %b %Y 2001-05-15 00:00:00
4585+Tuesday 00 2002 %W %U %Y 2002-01-01 00:00:00
4586+Thursday 53 1998 %W %u %Y 1998-12-31 00:00:00
4587+Sunday 01 2001 %W %v %x 2001-01-07 00:00:00
4588+Tuesday 52 2001 %W %V %X 2002-01-01 00:00:00
4589+060 2004 %j %Y 2004-02-29 00:00:00
4590+4 53 1998 %w %u %Y 1998-12-31 00:00:00
4591+15-01-2001 %d-%m-%Y %H:%i:%S 2001-01-15 00:00:00
4592+15-01-20 %d-%m-%y 2020-01-15 00:00:00
4593+15-2001-1 %d-%Y-%c 2001-01-15 00:00:00
4594+select date,format,concat('',str_to_date(date, format)) as con from t1;
4595+date format con
4596+2003-01-02 10:11:12 %Y-%m-%d %H:%i:%S 2003-01-02 10:11:12
4597+03-01-02 8:11:2.123456 %y-%m-%d %H:%i:%S.%# 2003-01-02 08:11:02
4598+2003-01-02 10:11:12 PM %Y-%m-%d %h:%i:%S %p 2003-01-02 22:11:12
4599+2003-01-02 01:11:12.12345AM %Y-%m-%d %h:%i:%S.%f%p 2003-01-02 01:11:12.123450
4600+2003-01-02 02:11:12.12345AM %Y-%m-%d %h:%i:%S.%f %p 2003-01-02 02:11:12.123450
4601+2003-01-02 12:11:12.12345 am %Y-%m-%d %h:%i:%S.%f%p 2003-01-02 00:11:12.123450
4602+2003-01-02 11:11:12Pm %Y-%m-%d %h:%i:%S%p 2003-01-02 23:11:12
4603+10:20:10 %H:%i:%s 0000-00-00 10:20:10
4604+10:20:10 %h:%i:%s.%f 0000-00-00 10:20:10
4605+10:20:10 %T 0000-00-00 10:20:10
4606+10:20:10AM %h:%i:%s%p 0000-00-00 10:20:10
4607+10:20:10AM %r 0000-00-00 10:20:10
4608+10:20:10.44AM %h:%i:%s.%f%p 0000-00-00 10:20:10.440000
4609+15-01-2001 12:59:58 %d-%m-%Y %H:%i:%S 2001-01-15 12:59:58
4610+15 September 2001 %d %M %Y 2001-09-15 00:00:00
4611+15 SEPTEMB 2001 %d %M %Y 2001-09-15 00:00:00
4612+15 MAY 2001 %d %b %Y 2001-05-15 00:00:00
4613+15th May 2001 %D %b %Y 2001-05-15 00:00:00
4614+Sunday 15 MAY 2001 %W %d %b %Y 2001-05-15 00:00:00
4615+Sund 15 MAY 2001 %W %d %b %Y 2001-05-15 00:00:00
4616+Tuesday 00 2002 %W %U %Y 2002-01-01 00:00:00
4617+Thursday 53 1998 %W %u %Y 1998-12-31 00:00:00
4618+Sunday 01 2001 %W %v %x 2001-01-07 00:00:00
4619+Tuesday 52 2001 %W %V %X 2002-01-01 00:00:00
4620+060 2004 %j %Y 2004-02-29 00:00:00
4621+4 53 1998 %w %u %Y 1998-12-31 00:00:00
4622+15-01-2001 %d-%m-%Y %H:%i:%S 2001-01-15 00:00:00
4623+15-01-20 %d-%m-%y 2020-01-15 00:00:00
4624+15-2001-1 %d-%Y-%c 2001-01-15 00:00:00
4625+select date,format,cast(str_to_date(date, format) as datetime) as datetime from t1;
4626+date format datetime
4627+2003-01-02 10:11:12 %Y-%m-%d %H:%i:%S 2003-01-02 10:11:12
4628+03-01-02 8:11:2.123456 %y-%m-%d %H:%i:%S.%# 2003-01-02 08:11:02
4629+2003-01-02 10:11:12 PM %Y-%m-%d %h:%i:%S %p 2003-01-02 22:11:12
4630+2003-01-02 01:11:12.12345AM %Y-%m-%d %h:%i:%S.%f%p 2003-01-02 01:11:12.123450
4631+2003-01-02 02:11:12.12345AM %Y-%m-%d %h:%i:%S.%f %p 2003-01-02 02:11:12.123450
4632+2003-01-02 12:11:12.12345 am %Y-%m-%d %h:%i:%S.%f%p 2003-01-02 00:11:12.123450
4633+2003-01-02 11:11:12Pm %Y-%m-%d %h:%i:%S%p 2003-01-02 23:11:12
4634+10:20:10 %H:%i:%s 0000-00-00 10:20:10
4635+10:20:10 %h:%i:%s.%f 0000-00-00 10:20:10
4636+10:20:10 %T 0000-00-00 10:20:10
4637+10:20:10AM %h:%i:%s%p 0000-00-00 10:20:10
4638+10:20:10AM %r 0000-00-00 10:20:10
4639+10:20:10.44AM %h:%i:%s.%f%p 0000-00-00 10:20:10.440000
4640+15-01-2001 12:59:58 %d-%m-%Y %H:%i:%S 2001-01-15 12:59:58
4641+15 September 2001 %d %M %Y 2001-09-15 00:00:00
4642+15 SEPTEMB 2001 %d %M %Y 2001-09-15 00:00:00
4643+15 MAY 2001 %d %b %Y 2001-05-15 00:00:00
4644+15th May 2001 %D %b %Y 2001-05-15 00:00:00
4645+Sunday 15 MAY 2001 %W %d %b %Y 2001-05-15 00:00:00
4646+Sund 15 MAY 2001 %W %d %b %Y 2001-05-15 00:00:00
4647+Tuesday 00 2002 %W %U %Y 2002-01-01 00:00:00
4648+Thursday 53 1998 %W %u %Y 1998-12-31 00:00:00
4649+Sunday 01 2001 %W %v %x 2001-01-07 00:00:00
4650+Tuesday 52 2001 %W %V %X 2002-01-01 00:00:00
4651+060 2004 %j %Y 2004-02-29 00:00:00
4652+4 53 1998 %w %u %Y 1998-12-31 00:00:00
4653+15-01-2001 %d-%m-%Y %H:%i:%S 2001-01-15 00:00:00
4654+15-01-20 %d-%m-%y 2020-01-15 00:00:00
4655+15-2001-1 %d-%Y-%c 2001-01-15 00:00:00
4656+select date,format,DATE(str_to_date(date, format)) as date2 from t1;
4657+date format date2
4658+2003-01-02 10:11:12 %Y-%m-%d %H:%i:%S 2003-01-02
4659+03-01-02 8:11:2.123456 %y-%m-%d %H:%i:%S.%# 2003-01-02
4660+2003-01-02 10:11:12 PM %Y-%m-%d %h:%i:%S %p 2003-01-02
4661+2003-01-02 01:11:12.12345AM %Y-%m-%d %h:%i:%S.%f%p 2003-01-02
4662+2003-01-02 02:11:12.12345AM %Y-%m-%d %h:%i:%S.%f %p 2003-01-02
4663+2003-01-02 12:11:12.12345 am %Y-%m-%d %h:%i:%S.%f%p 2003-01-02
4664+2003-01-02 11:11:12Pm %Y-%m-%d %h:%i:%S%p 2003-01-02
4665+10:20:10 %H:%i:%s 0000-00-00
4666+10:20:10 %h:%i:%s.%f 0000-00-00
4667+10:20:10 %T 0000-00-00
4668+10:20:10AM %h:%i:%s%p 0000-00-00
4669+10:20:10AM %r 0000-00-00
4670+10:20:10.44AM %h:%i:%s.%f%p 0000-00-00
4671+15-01-2001 12:59:58 %d-%m-%Y %H:%i:%S 2001-01-15
4672+15 September 2001 %d %M %Y 2001-09-15
4673+15 SEPTEMB 2001 %d %M %Y 2001-09-15
4674+15 MAY 2001 %d %b %Y 2001-05-15
4675+15th May 2001 %D %b %Y 2001-05-15
4676+Sunday 15 MAY 2001 %W %d %b %Y 2001-05-15
4677+Sund 15 MAY 2001 %W %d %b %Y 2001-05-15
4678+Tuesday 00 2002 %W %U %Y 2002-01-01
4679+Thursday 53 1998 %W %u %Y 1998-12-31
4680+Sunday 01 2001 %W %v %x 2001-01-07
4681+Tuesday 52 2001 %W %V %X 2002-01-01
4682+060 2004 %j %Y 2004-02-29
4683+4 53 1998 %w %u %Y 1998-12-31
4684+15-01-2001 %d-%m-%Y %H:%i:%S 2001-01-15
4685+15-01-20 %d-%m-%y 2020-01-15
4686+15-2001-1 %d-%Y-%c 2001-01-15
4687+select date,format,TIME(str_to_date(date, format)) as time from t1;
4688+date format time
4689+2003-01-02 10:11:12 %Y-%m-%d %H:%i:%S 10:11:12
4690+03-01-02 8:11:2.123456 %y-%m-%d %H:%i:%S.%# 08:11:02
4691+2003-01-02 10:11:12 PM %Y-%m-%d %h:%i:%S %p 22:11:12
4692+2003-01-02 01:11:12.12345AM %Y-%m-%d %h:%i:%S.%f%p 01:11:12.123450
4693+2003-01-02 02:11:12.12345AM %Y-%m-%d %h:%i:%S.%f %p 02:11:12.123450
4694+2003-01-02 12:11:12.12345 am %Y-%m-%d %h:%i:%S.%f%p 00:11:12.123450
4695+2003-01-02 11:11:12Pm %Y-%m-%d %h:%i:%S%p 23:11:12
4696+10:20:10 %H:%i:%s 10:20:10
4697+10:20:10 %h:%i:%s.%f 10:20:10
4698+10:20:10 %T 10:20:10
4699+10:20:10AM %h:%i:%s%p 10:20:10
4700+10:20:10AM %r 10:20:10
4701+10:20:10.44AM %h:%i:%s.%f%p 10:20:10.440000
4702+15-01-2001 12:59:58 %d-%m-%Y %H:%i:%S 12:59:58
4703+15 September 2001 %d %M %Y 00:00:00
4704+15 SEPTEMB 2001 %d %M %Y 00:00:00
4705+15 MAY 2001 %d %b %Y 00:00:00
4706+15th May 2001 %D %b %Y 00:00:00
4707+Sunday 15 MAY 2001 %W %d %b %Y 00:00:00
4708+Sund 15 MAY 2001 %W %d %b %Y 00:00:00
4709+Tuesday 00 2002 %W %U %Y 00:00:00
4710+Thursday 53 1998 %W %u %Y 00:00:00
4711+Sunday 01 2001 %W %v %x 00:00:00
4712+Tuesday 52 2001 %W %V %X 00:00:00
4713+060 2004 %j %Y 00:00:00
4714+4 53 1998 %w %u %Y 00:00:00
4715+15-01-2001 %d-%m-%Y %H:%i:%S 00:00:00
4716+15-01-20 %d-%m-%y 00:00:00
4717+15-2001-1 %d-%Y-%c 00:00:00
4718+select date,format,concat(TIME(str_to_date(date, format))) as time2 from t1;
4719+date format time2
4720+2003-01-02 10:11:12 %Y-%m-%d %H:%i:%S 10:11:12
4721+03-01-02 8:11:2.123456 %y-%m-%d %H:%i:%S.%# 08:11:02
4722+2003-01-02 10:11:12 PM %Y-%m-%d %h:%i:%S %p 22:11:12
4723+2003-01-02 01:11:12.12345AM %Y-%m-%d %h:%i:%S.%f%p 01:11:12.123450
4724+2003-01-02 02:11:12.12345AM %Y-%m-%d %h:%i:%S.%f %p 02:11:12.123450
4725+2003-01-02 12:11:12.12345 am %Y-%m-%d %h:%i:%S.%f%p 00:11:12.123450
4726+2003-01-02 11:11:12Pm %Y-%m-%d %h:%i:%S%p 23:11:12
4727+10:20:10 %H:%i:%s 10:20:10
4728+10:20:10 %h:%i:%s.%f 10:20:10
4729+10:20:10 %T 10:20:10
4730+10:20:10AM %h:%i:%s%p 10:20:10
4731+10:20:10AM %r 10:20:10
4732+10:20:10.44AM %h:%i:%s.%f%p 10:20:10.440000
4733+15-01-2001 12:59:58 %d-%m-%Y %H:%i:%S 12:59:58
4734+15 September 2001 %d %M %Y 00:00:00
4735+15 SEPTEMB 2001 %d %M %Y 00:00:00
4736+15 MAY 2001 %d %b %Y 00:00:00
4737+15th May 2001 %D %b %Y 00:00:00
4738+Sunday 15 MAY 2001 %W %d %b %Y 00:00:00
4739+Sund 15 MAY 2001 %W %d %b %Y 00:00:00
4740+Tuesday 00 2002 %W %U %Y 00:00:00
4741+Thursday 53 1998 %W %u %Y 00:00:00
4742+Sunday 01 2001 %W %v %x 00:00:00
4743+Tuesday 52 2001 %W %V %X 00:00:00
4744+060 2004 %j %Y 00:00:00
4745+4 53 1998 %w %u %Y 00:00:00
4746+15-01-2001 %d-%m-%Y %H:%i:%S 00:00:00
4747+15-01-20 %d-%m-%y 00:00:00
4748+15-2001-1 %d-%Y-%c 00:00:00
4749+select concat('',str_to_date('8:11:2.123456 03-01-02','%H:%i:%S.%f %y-%m-%d'));
4750+concat('',str_to_date('8:11:2.123456 03-01-02','%H:%i:%S.%f %y-%m-%d'))
4751+2003-01-02 08:11:02.123456
4752+truncate table t1;
4753+insert into t1 values
4754+('2003-01-02 10:11:12 PM', '%Y-%m-%d %H:%i:%S %p'),
4755+('2003-01-02 10:11:12.123456', '%Y-%m-%d %h:%i:%S %p'),
4756+('2003-01-02 10:11:12AM', '%Y-%m-%d %h:%i:%S.%f %p'),
4757+('2003-01-02 10:11:12AN', '%Y-%m-%d %h:%i:%S%p'),
4758+('2003-01-02 10:11:12 PM', '%y-%m-%d %H:%i:%S %p'),
4759+('10:20:10AM', '%H:%i:%s%p'),
4760+('15 Septembei 2001', '%d %M %Y'),
4761+('15 Ju 2001', '%d %M %Y'),
4762+('Sund 15 MA', '%W %d %b %Y'),
4763+('Thursdai 12 1998', '%W %u %Y'),
4764+('Sunday 01 2001', '%W %v %X'),
4765+('Tuesday 52 2001', '%W %V %x'),
4766+('Tuesday 52 2001', '%W %V %Y'),
4767+('Tuesday 52 2001', '%W %u %x'),
4768+('7 53 1998', '%w %u %Y'),
4769+(NULL, get_format(DATE,'USA'));
4770+select date,format,str_to_date(date, format) as str_to_date from t1;
4771+date format str_to_date
4772+2003-01-02 10:11:12 PM %Y-%m-%d %H:%i:%S %p NULL
4773+2003-01-02 10:11:12.123456 %Y-%m-%d %h:%i:%S %p NULL
4774+2003-01-02 10:11:12AM %Y-%m-%d %h:%i:%S.%f %p NULL
4775+2003-01-02 10:11:12AN %Y-%m-%d %h:%i:%S%p NULL
4776+2003-01-02 10:11:12 PM %y-%m-%d %H:%i:%S %p NULL
4777+10:20:10AM %H:%i:%s%p NULL
4778+15 Septembei 2001 %d %M %Y NULL
4779+15 Ju 2001 %d %M %Y NULL
4780+Sund 15 MA %W %d %b %Y NULL
4781+Thursdai 12 1998 %W %u %Y NULL
4782+Sunday 01 2001 %W %v %X NULL
4783+Tuesday 52 2001 %W %V %x NULL
4784+Tuesday 52 2001 %W %V %Y NULL
4785+Tuesday 52 2001 %W %u %x NULL
4786+7 53 1998 %w %u %Y NULL
4787+NULL %m.%d.%Y NULL
4788+Warnings:
4789+Error 1411 Incorrect datetime value: '2003-01-02 10:11:12 PM' for function str_to_date
4790+Error 1411 Incorrect datetime value: '2003-01-02 10:11:12.123456' for function str_to_date
4791+Error 1411 Incorrect datetime value: '2003-01-02 10:11:12AM' for function str_to_date
4792+Error 1411 Incorrect datetime value: '2003-01-02 10:11:12AN' for function str_to_date
4793+Error 1411 Incorrect datetime value: '2003-01-02 10:11:12 PM' for function str_to_date
4794+Error 1411 Incorrect datetime value: '10:20:10AM' for function str_to_date
4795+Error 1411 Incorrect datetime value: '15 Septembei 2001' for function str_to_date
4796+Error 1411 Incorrect datetime value: '15 Ju 2001' for function str_to_date
4797+Error 1411 Incorrect datetime value: 'Sund 15 MA' for function str_to_date
4798+Error 1411 Incorrect datetime value: 'Thursdai 12 1998' for function str_to_date
4799+Error 1411 Incorrect datetime value: 'Sunday 01 2001' for function str_to_date
4800+Error 1411 Incorrect datetime value: 'Tuesday 52 2001' for function str_to_date
4801+Error 1411 Incorrect datetime value: 'Tuesday 52 2001' for function str_to_date
4802+Error 1411 Incorrect datetime value: 'Tuesday 52 2001' for function str_to_date
4803+Error 1411 Incorrect datetime value: '7 53 1998' for function str_to_date
4804+select date,format,concat(str_to_date(date, format),'') as con from t1;
4805+date format con
4806+2003-01-02 10:11:12 PM %Y-%m-%d %H:%i:%S %p NULL
4807+2003-01-02 10:11:12.123456 %Y-%m-%d %h:%i:%S %p NULL
4808+2003-01-02 10:11:12AM %Y-%m-%d %h:%i:%S.%f %p NULL
4809+2003-01-02 10:11:12AN %Y-%m-%d %h:%i:%S%p NULL
4810+2003-01-02 10:11:12 PM %y-%m-%d %H:%i:%S %p NULL
4811+10:20:10AM %H:%i:%s%p NULL
4812+15 Septembei 2001 %d %M %Y NULL
4813+15 Ju 2001 %d %M %Y NULL
4814+Sund 15 MA %W %d %b %Y NULL
4815+Thursdai 12 1998 %W %u %Y NULL
4816+Sunday 01 2001 %W %v %X NULL
4817+Tuesday 52 2001 %W %V %x NULL
4818+Tuesday 52 2001 %W %V %Y NULL
4819+Tuesday 52 2001 %W %u %x NULL
4820+7 53 1998 %w %u %Y NULL
4821+NULL %m.%d.%Y NULL
4822+Warnings:
4823+Error 1411 Incorrect datetime value: '2003-01-02 10:11:12 PM' for function str_to_date
4824+Error 1411 Incorrect datetime value: '2003-01-02 10:11:12.123456' for function str_to_date
4825+Error 1411 Incorrect datetime value: '2003-01-02 10:11:12AM' for function str_to_date
4826+Error 1411 Incorrect datetime value: '2003-01-02 10:11:12AN' for function str_to_date
4827+Error 1411 Incorrect datetime value: '2003-01-02 10:11:12 PM' for function str_to_date
4828+Error 1411 Incorrect datetime value: '10:20:10AM' for function str_to_date
4829+Error 1411 Incorrect datetime value: '15 Septembei 2001' for function str_to_date
4830+Error 1411 Incorrect datetime value: '15 Ju 2001' for function str_to_date
4831+Error 1411 Incorrect datetime value: 'Sund 15 MA' for function str_to_date
4832+Error 1411 Incorrect datetime value: 'Thursdai 12 1998' for function str_to_date
4833+Error 1411 Incorrect datetime value: 'Sunday 01 2001' for function str_to_date
4834+Error 1411 Incorrect datetime value: 'Tuesday 52 2001' for function str_to_date
4835+Error 1411 Incorrect datetime value: 'Tuesday 52 2001' for function str_to_date
4836+Error 1411 Incorrect datetime value: 'Tuesday 52 2001' for function str_to_date
4837+Error 1411 Incorrect datetime value: '7 53 1998' for function str_to_date
4838+truncate table t1;
4839+insert into t1 values
4840+('10:20:10AM', '%h:%i:%s'),
4841+('2003-01-02 10:11:12', '%Y-%m-%d %h:%i:%S'),
4842+('03-01-02 10:11:12 PM', '%Y-%m-%d %h:%i:%S %p');
4843+select date,format,str_to_date(date, format) as str_to_date from t1;
4844+date format str_to_date
4845+10:20:10AM %h:%i:%s 0000-00-00 10:20:10
4846+2003-01-02 10:11:12 %Y-%m-%d %h:%i:%S 2003-01-02 10:11:12
4847+03-01-02 10:11:12 PM %Y-%m-%d %h:%i:%S %p 2003-01-02 22:11:12
4848+Warnings:
4849+Warning 1292 Incorrect datetime value: '10:20:10AM'
4850+select date,format,concat(str_to_date(date, format),'') as con from t1;
4851+date format con
4852+10:20:10AM %h:%i:%s 0000-00-00 10:20:10
4853+2003-01-02 10:11:12 %Y-%m-%d %h:%i:%S 2003-01-02 10:11:12
4854+03-01-02 10:11:12 PM %Y-%m-%d %h:%i:%S %p 2003-01-02 22:11:12
4855+Warnings:
4856+Warning 1292 Incorrect datetime value: '10:20:10AM'
4857+drop table t1;
4858+select get_format(DATE, 'USA') as a;
4859+a
4860+%m.%d.%Y
4861+select get_format(TIME, 'internal') as a;
4862+a
4863+%H%i%s
4864+select get_format(DATETIME, 'eur') as a;
4865+a
4866+%Y-%m-%d %H.%i.%s
4867+select get_format(TIMESTAMP, 'eur') as a;
4868+a
4869+%Y-%m-%d %H.%i.%s
4870+select get_format(DATE, 'TEST') as a;
4871+a
4872+NULL
4873+select str_to_date('15-01-2001 12:59:59', GET_FORMAT(DATE,'USA'));
4874+str_to_date('15-01-2001 12:59:59', GET_FORMAT(DATE,'USA'))
4875+NULL
4876+Warnings:
4877+Error 1411 Incorrect datetime value: '15-01-2001 12:59:59' for function str_to_date
4878+explain extended select makedate(1997,1), addtime("31.12.97 11.59.59.999999 PM", "1 1.1.1.000002"),subtime("31.12.97 11.59.59.999999 PM", "1 1.1.1.000002"),timediff("01.01.97 11:59:59.000001 PM","31.12.95 11:59:59.000002 PM"),cast(str_to_date("15-01-2001 12:59:59", "%d-%m-%Y %H:%i:%S") as TIME), maketime(23,11,12),microsecond("1997-12-31 23:59:59.000001");
4879+id select_type table type possible_keys key key_len ref rows filtered Extra
4880+1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used
4881+Warnings:
4882+Note 1003 select makedate(1997,1) AS `makedate(1997,1)`,addtime('31.12.97 11.59.59.999999 PM','1 1.1.1.000002') AS `addtime("31.12.97 11.59.59.999999 PM", "1 1.1.1.000002")`,subtime('31.12.97 11.59.59.999999 PM','1 1.1.1.000002') AS `subtime("31.12.97 11.59.59.999999 PM", "1 1.1.1.000002")`,timediff('01.01.97 11:59:59.000001 PM','31.12.95 11:59:59.000002 PM') AS `timediff("01.01.97 11:59:59.000001 PM","31.12.95 11:59:59.000002 PM")`,cast(str_to_date('15-01-2001 12:59:59','%d-%m-%Y %H:%i:%S') as time) AS `cast(str_to_date("15-01-2001 12:59:59", "%d-%m-%Y %H:%i:%S") as TIME)`,maketime(23,11,12) AS `maketime(23,11,12)`,microsecond('1997-12-31 23:59:59.000001') AS `microsecond("1997-12-31 23:59:59.000001")`
4883+create table t1 (d date);
4884+insert into t1 values ('2004-07-14'),('2005-07-14');
4885+select date_format(d,"%d") from t1 order by 1;
4886+date_format(d,"%d")
4887+14
4888+14
4889+drop table t1;
4890+select str_to_date("2003-....01ABCD-02 10:11:12.0012", "%Y-%.%m%@-%d %H:%i:%S.%f") as a;
4891+a
4892+2003-01-02 10:11:12.001200
4893+create table t1 select str_to_date("2003-01-02 10:11:12.0012", "%Y-%m-%d %H:%i:%S.%f") as f1,
4894+str_to_date("10:11:12.0012", "%H:%i:%S.%f") as f2,
4895+str_to_date("2003-01-02", "%Y-%m-%d") as f3,
4896+str_to_date("02", "%d") as f4, str_to_date("02 10", "%d %H") as f5;
4897+describe t1;
4898+Field Type Null Key Default Extra
4899+f1 datetime YES NULL
4900+f2 time YES NULL
4901+f3 date YES NULL
4902+f4 date YES NULL
4903+f5 time YES NULL
4904+select * from t1;
4905+f1 f2 f3 f4 f5
4906+2003-01-02 10:11:12 10:11:12 2003-01-02 0000-00-02 58:00:00
4907+drop table t1;
4908+create table t1 select "02 10" as a, "%d %H" as b;
4909+select str_to_date(a,b) from t1;
4910+str_to_date(a,b)
4911+0000-00-02 10:00:00
4912+create table t2 select str_to_date(a,b) from t1;
4913+describe t2;
4914+Field Type Null Key Default Extra
4915+str_to_date(a,b) datetime YES NULL
4916+select str_to_date("2003-01-02 10:11:12.0012", "%Y-%m-%d %H:%i:%S.%f") as f1,
4917+str_to_date("2003-01-02 10:11:12.0012", "%Y-%m-%d %H:%i:%S") as f2,
4918+str_to_date("2003-01-02", "%Y-%m-%d") as f3,
4919+str_to_date("02 10:11:12", "%d %H:%i:%S.%f") as f4,
4920+str_to_date("02 10:11:12", "%d %H:%i:%S") as f5,
4921+str_to_date("02 10", "%d %f") as f6;
4922+f1 f2 f3 f4 f5 f6
4923+2003-01-02 10:11:12.001200 2003-01-02 10:11:12 2003-01-02 58:11:12 58:11:12 48:00:00.100000
4924+Warnings:
4925+Warning 1292 Truncated incorrect datetime value: '2003-01-02 10:11:12.0012'
4926+drop table t1, t2;
4927+select str_to_date("2003-01-02 10:11:12.0012ABCD", "%Y-%m-%d %H:%i:%S.%f") as f1,
4928+addtime("-01:01:01.01 GGG", "-23:59:59.1") as f2,
4929+microsecond("1997-12-31 23:59:59.01XXXX") as f3;
4930+f1 f2 f3
4931+2003-01-02 10:11:12.001200 -25:01:00.110000 10000
4932+Warnings:
4933+Warning 1292 Truncated incorrect datetime value: '2003-01-02 10:11:12.0012ABCD'
4934+Warning 1292 Truncated incorrect time value: '-01:01:01.01 GGG'
4935+Warning 1292 Truncated incorrect time value: '1997-12-31 23:59:59.01XXXX'
4936+select str_to_date("2003-04-05 g", "%Y-%m-%d") as f1,
4937+str_to_date("2003-04-05 10:11:12.101010234567", "%Y-%m-%d %H:%i:%S.%f") as f2;
4938+f1 f2
4939+2003-04-05 2003-04-05 10:11:12.101010
4940+Warnings:
4941+Warning 1292 Truncated incorrect date value: '2003-04-05 g'
4942+Warning 1292 Truncated incorrect datetime value: '2003-04-05 10:11:12.101010234567'
4943+set names latin1;
4944+select date_format('2004-01-01','%W (%a), %e %M (%b) %Y');
4945+date_format('2004-01-01','%W (%a), %e %M (%b) %Y')
4946+Thursday (Thu), 1 January (Jan) 2004
4947+set lc_time_names=ru_RU;
4948+set names koi8r;
4949+select date_format('2004-01-01','%W (%a), %e %M (%b) %Y');
4950+date_format('2004-01-01','%W (%a), %e %M (%b) %Y')
4951+þÅÔ×ÅÒÇ (þÔ×), 1 ñÎ×ÁÒÑ (ñÎ×) 2004
4952+set lc_time_names=de_DE;
4953+set names latin1;
4954+select date_format('2004-01-01','%W (%a), %e %M (%b) %Y');
4955+date_format('2004-01-01','%W (%a), %e %M (%b) %Y')
4956+Donnerstag (Do), 1 Januar (Jan) 2004
4957+set names latin1;
4958+set lc_time_names=en_US;
4959+create table t1 (f1 datetime);
4960+insert into t1 (f1) values ("2005-01-01");
4961+insert into t1 (f1) values ("2005-02-01");
4962+select date_format(f1, "%m") as d1, date_format(f1, "%M") as d2 from t1 order by date_format(f1, "%M");
4963+d1 d2
4964+02 February
4965+01 January
4966+drop table t1;
4967+select str_to_date( 1, NULL );
4968+str_to_date( 1, NULL )
4969+NULL
4970+select str_to_date( NULL, 1 );
4971+str_to_date( NULL, 1 )
4972+NULL
4973+select str_to_date( 1, IF(1=1,NULL,NULL) );
4974+str_to_date( 1, IF(1=1,NULL,NULL) )
4975+NULL
4976+SELECT TIME_FORMAT("24:00:00", '%r');
4977+TIME_FORMAT("24:00:00", '%r')
4978+12:00:00 AM
4979+SELECT TIME_FORMAT("00:00:00", '%r');
4980+TIME_FORMAT("00:00:00", '%r')
4981+12:00:00 AM
4982+SELECT TIME_FORMAT("12:00:00", '%r');
4983+TIME_FORMAT("12:00:00", '%r')
4984+12:00:00 PM
4985+SELECT TIME_FORMAT("15:00:00", '%r');
4986+TIME_FORMAT("15:00:00", '%r')
4987+03:00:00 PM
4988+SELECT TIME_FORMAT("01:00:00", '%r');
4989+TIME_FORMAT("01:00:00", '%r')
4990+01:00:00 AM
4991+SELECT TIME_FORMAT("25:00:00", '%r');
4992+TIME_FORMAT("25:00:00", '%r')
4993+01:00:00 AM
4994+SELECT TIME_FORMAT("00:00:00", '%l %p');
4995+TIME_FORMAT("00:00:00", '%l %p')
4996+12 AM
4997+SELECT TIME_FORMAT("01:00:00", '%l %p');
4998+TIME_FORMAT("01:00:00", '%l %p')
4999+1 AM
5000+SELECT TIME_FORMAT("12:00:00", '%l %p');
The diff has been truncated for viewing.

Subscribers

People subscribed via source and target branches