1
by brian
clean slate |
1 |
drop table if exists t1,t2,t3; |
520.1.16
by Brian Aker
More test updates (one ulong fix) |
2 |
create table t1 (bandID INT NOT NULL PRIMARY KEY, payoutID int NOT NULL); |
1
by brian
clean slate |
3 |
insert into t1 (bandID,payoutID) VALUES (1,6),(2,6),(3,4),(4,9),(5,10),(6,1),(7,12),(8,12); |
520.1.16
by Brian Aker
More test updates (one ulong fix) |
4 |
create table t2 (payoutID int NOT NULL PRIMARY KEY); |
1
by brian
clean slate |
5 |
insert into t2 (payoutID) SELECT DISTINCT payoutID FROM t1; |
6 |
insert into t2 (payoutID) SELECT payoutID+10 FROM t1; |
|
7 |
ERROR 23000: Duplicate entry '16' for key 'PRIMARY' |
|
8 |
insert ignore into t2 (payoutID) SELECT payoutID+10 FROM t1; |
|
9 |
select * from t2; |
|
10 |
payoutID
|
|
11 |
1
|
|
12 |
4
|
|
13 |
6
|
|
14 |
9
|
|
15 |
10
|
|
16 |
11
|
|
17 |
12
|
|
18 |
14
|
|
19 |
16
|
|
20 |
19
|
|
21 |
20
|
|
22 |
22
|
|
23 |
drop table t1,t2; |
|
1063.9.6
by Stewart Smith
insert_select test for MyISAM as temp table only: 1 open table twice, 3x can be myisam temp. |
24 |
CREATE TEMPORARY TABLE `t1` ( |
520.1.16
by Brian Aker
More test updates (one ulong fix) |
25 |
`numeropost` bigint NOT NULL default '0', |
26 |
`icone` int NOT NULL default '0', |
|
27 |
`numreponse` bigint NOT NULL auto_increment, |
|
1
by brian
clean slate |
28 |
`contenu` text NOT NULL, |
29 |
`pseudo` varchar(50) NOT NULL default '', |
|
873.1.2
by Jay Pipes
Fixed Field_datetime to never accept any bad datetimes as a string. This broke |
30 |
`date` datetime, |
520.1.16
by Brian Aker
More test updates (one ulong fix) |
31 |
`ip` bigint NOT NULL default '0', |
32 |
`signature` int NOT NULL default '0', |
|
1
by brian
clean slate |
33 |
PRIMARY KEY (`numeropost`,`numreponse`) |
34 |
,KEY `ip` (`ip`), |
|
35 |
KEY `date` (`date`), |
|
36 |
KEY `pseudo` (`pseudo`), |
|
37 |
KEY `numreponse` (`numreponse`) |
|
38 |
) ENGINE=MyISAM; |
|
1063.9.6
by Stewart Smith
insert_select test for MyISAM as temp table only: 1 open table twice, 3x can be myisam temp. |
39 |
CREATE TEMPORARY TABLE `t2` ( |
520.1.16
by Brian Aker
More test updates (one ulong fix) |
40 |
`numeropost` bigint NOT NULL default '0', |
41 |
`icone` int NOT NULL default '0', |
|
42 |
`numreponse` bigint NOT NULL auto_increment, |
|
1
by brian
clean slate |
43 |
`contenu` text NOT NULL, |
44 |
`pseudo` varchar(50) NOT NULL default '', |
|
873.1.2
by Jay Pipes
Fixed Field_datetime to never accept any bad datetimes as a string. This broke |
45 |
`date` datetime, |
520.1.16
by Brian Aker
More test updates (one ulong fix) |
46 |
`ip` bigint NOT NULL default '0', |
47 |
`signature` int NOT NULL default '0', |
|
1
by brian
clean slate |
48 |
PRIMARY KEY (`numeropost`,`numreponse`), |
49 |
KEY `ip` (`ip`), |
|
50 |
KEY `date` (`date`), |
|
51 |
KEY `pseudo` (`pseudo`), |
|
52 |
KEY `numreponse` (`numreponse`) |
|
53 |
) ENGINE=MyISAM; |
|
54 |
INSERT INTO t2 |
|
55 |
(numeropost,icone,numreponse,contenu,pseudo,date,ip,signature) VALUES |
|
56 |
(9,1,56,'test','joce','2001-07-25 13:50:53' |
|
57 |
,3649052399,0); |
|
58 |
INSERT INTO t1 (numeropost,icone,contenu,pseudo,date,signature,ip) |
|
59 |
SELECT 1618,icone,contenu,pseudo,date,signature,ip FROM t2 |
|
60 |
WHERE numeropost=9 ORDER BY numreponse ASC; |
|
61 |
show variables like '%bulk%'; |
|
62 |
Variable_name Value |
|
63 |
bulk_insert_buffer_size 8388608 |
|
64 |
INSERT INTO t1 (numeropost,icone,contenu,pseudo,date,signature,ip) |
|
65 |
SELECT 1718,icone,contenu,pseudo,date,signature,ip FROM t2 |
|
66 |
WHERE numeropost=9 ORDER BY numreponse ASC; |
|
67 |
DROP TABLE t1,t2; |
|
68 |
create table t1 (a int not null); |
|
69 |
create table t2 (a int not null); |
|
70 |
insert into t1 values (1); |
|
71 |
insert into t1 values (a+2); |
|
72 |
insert into t1 values (a+3); |
|
73 |
insert into t1 values (4),(a+5); |
|
74 |
insert into t1 select * from t1; |
|
75 |
select * from t1; |
|
76 |
a
|
|
77 |
1
|
|
78 |
2
|
|
79 |
3
|
|
80 |
4
|
|
81 |
5
|
|
82 |
1
|
|
83 |
2
|
|
84 |
3
|
|
85 |
4
|
|
86 |
5
|
|
87 |
insert into t1 select * from t1 as t2; |
|
88 |
select * from t1; |
|
89 |
a
|
|
90 |
1
|
|
91 |
2
|
|
92 |
3
|
|
93 |
4
|
|
94 |
5
|
|
95 |
1
|
|
96 |
2
|
|
97 |
3
|
|
98 |
4
|
|
99 |
5
|
|
100 |
1
|
|
101 |
2
|
|
102 |
3
|
|
103 |
4
|
|
104 |
5
|
|
105 |
1
|
|
106 |
2
|
|
107 |
3
|
|
108 |
4
|
|
109 |
5
|
|
110 |
insert into t2 select * from t1 as t2; |
|
111 |
select * from t1; |
|
112 |
a
|
|
113 |
1
|
|
114 |
2
|
|
115 |
3
|
|
116 |
4
|
|
117 |
5
|
|
118 |
1
|
|
119 |
2
|
|
120 |
3
|
|
121 |
4
|
|
122 |
5
|
|
123 |
1
|
|
124 |
2
|
|
125 |
3
|
|
126 |
4
|
|
127 |
5
|
|
128 |
1
|
|
129 |
2
|
|
130 |
3
|
|
131 |
4
|
|
132 |
5
|
|
133 |
insert into t1 select t2.a from t1,t2; |
|
134 |
select * from t1; |
|
135 |
a
|
|
136 |
1
|
|
137 |
2
|
|
138 |
3
|
|
139 |
4
|
|
140 |
5
|
|
141 |
1
|
|
142 |
2
|
|
143 |
3
|
|
144 |
4
|
|
145 |
5
|
|
146 |
1
|
|
147 |
2
|
|
148 |
3
|
|
149 |
4
|
|
150 |
5
|
|
151 |
1
|
|
152 |
2
|
|
153 |
3
|
|
154 |
4
|
|
155 |
5
|
|
156 |
1
|
|
157 |
1
|
|
158 |
1
|
|
159 |
1
|
|
160 |
1
|
|
161 |
1
|
|
162 |
1
|
|
163 |
1
|
|
164 |
1
|
|
165 |
1
|
|
166 |
1
|
|
167 |
1
|
|
168 |
1
|
|
169 |
1
|
|
170 |
1
|
|
171 |
1
|
|
172 |
1
|
|
173 |
1
|
|
174 |
1
|
|
175 |
1
|
|
176 |
2
|
|
177 |
2
|
|
178 |
2
|
|
179 |
2
|
|
180 |
2
|
|
181 |
2
|
|
182 |
2
|
|
183 |
2
|
|
184 |
2
|
|
185 |
2
|
|
186 |
2
|
|
187 |
2
|
|
188 |
2
|
|
189 |
2
|
|
190 |
2
|
|
191 |
2
|
|
192 |
2
|
|
193 |
2
|
|
194 |
2
|
|
195 |
2
|
|
196 |
3
|
|
197 |
3
|
|
198 |
3
|
|
199 |
3
|
|
200 |
3
|
|
201 |
3
|
|
202 |
3
|
|
203 |
3
|
|
204 |
3
|
|
205 |
3
|
|
206 |
3
|
|
207 |
3
|
|
208 |
3
|
|
209 |
3
|
|
210 |
3
|
|
211 |
3
|
|
212 |
3
|
|
213 |
3
|
|
214 |
3
|
|
215 |
3
|
|
216 |
4
|
|
217 |
4
|
|
218 |
4
|
|
219 |
4
|
|
220 |
4
|
|
221 |
4
|
|
222 |
4
|
|
223 |
4
|
|
224 |
4
|
|
225 |
4
|
|
226 |
4
|
|
227 |
4
|
|
228 |
4
|
|
229 |
4
|
|
230 |
4
|
|
231 |
4
|
|
232 |
4
|
|
233 |
4
|
|
234 |
4
|
|
235 |
4
|
|
236 |
5
|
|
237 |
5
|
|
238 |
5
|
|
239 |
5
|
|
240 |
5
|
|
241 |
5
|
|
242 |
5
|
|
243 |
5
|
|
244 |
5
|
|
245 |
5
|
|
246 |
5
|
|
247 |
5
|
|
248 |
5
|
|
249 |
5
|
|
250 |
5
|
|
251 |
5
|
|
252 |
5
|
|
253 |
5
|
|
254 |
5
|
|
255 |
5
|
|
256 |
1
|
|
257 |
1
|
|
258 |
1
|
|
259 |
1
|
|
260 |
1
|
|
261 |
1
|
|
262 |
1
|
|
263 |
1
|
|
264 |
1
|
|
265 |
1
|
|
266 |
1
|
|
267 |
1
|
|
268 |
1
|
|
269 |
1
|
|
270 |
1
|
|
271 |
1
|
|
272 |
1
|
|
273 |
1
|
|
274 |
1
|
|
275 |
1
|
|
276 |
2
|
|
277 |
2
|
|
278 |
2
|
|
279 |
2
|
|
280 |
2
|
|
281 |
2
|
|
282 |
2
|
|
283 |
2
|
|
284 |
2
|
|
285 |
2
|
|
286 |
2
|
|
287 |
2
|
|
288 |
2
|
|
289 |
2
|
|
290 |
2
|
|
291 |
2
|
|
292 |
2
|
|
293 |
2
|
|
294 |
2
|
|
295 |
2
|
|
296 |
3
|
|
297 |
3
|
|
298 |
3
|
|
299 |
3
|
|
300 |
3
|
|
301 |
3
|
|
302 |
3
|
|
303 |
3
|
|
304 |
3
|
|
305 |
3
|
|
306 |
3
|
|
307 |
3
|
|
308 |
3
|
|
309 |
3
|
|
310 |
3
|
|
311 |
3
|
|
312 |
3
|
|
313 |
3
|
|
314 |
3
|
|
315 |
3
|
|
316 |
4
|
|
317 |
4
|
|
318 |
4
|
|
319 |
4
|
|
320 |
4
|
|
321 |
4
|
|
322 |
4
|
|
323 |
4
|
|
324 |
4
|
|
325 |
4
|
|
326 |
4
|
|
327 |
4
|
|
328 |
4
|
|
329 |
4
|
|
330 |
4
|
|
331 |
4
|
|
332 |
4
|
|
333 |
4
|
|
334 |
4
|
|
335 |
4
|
|
336 |
5
|
|
337 |
5
|
|
338 |
5
|
|
339 |
5
|
|
340 |
5
|
|
341 |
5
|
|
342 |
5
|
|
343 |
5
|
|
344 |
5
|
|
345 |
5
|
|
346 |
5
|
|
347 |
5
|
|
348 |
5
|
|
349 |
5
|
|
350 |
5
|
|
351 |
5
|
|
352 |
5
|
|
353 |
5
|
|
354 |
5
|
|
355 |
5
|
|
356 |
1
|
|
357 |
1
|
|
358 |
1
|
|
359 |
1
|
|
360 |
1
|
|
361 |
1
|
|
362 |
1
|
|
363 |
1
|
|
364 |
1
|
|
365 |
1
|
|
366 |
1
|
|
367 |
1
|
|
368 |
1
|
|
369 |
1
|
|
370 |
1
|
|
371 |
1
|
|
372 |
1
|
|
373 |
1
|
|
374 |
1
|
|
375 |
1
|
|
376 |
2
|
|
377 |
2
|
|
378 |
2
|
|
379 |
2
|
|
380 |
2
|
|
381 |
2
|
|
382 |
2
|
|
383 |
2
|
|
384 |
2
|
|
385 |
2
|
|
386 |
2
|
|
387 |
2
|
|
388 |
2
|
|
389 |
2
|
|
390 |
2
|
|
391 |
2
|
|
392 |
2
|
|
393 |
2
|
|
394 |
2
|
|
395 |
2
|
|
396 |
3
|
|
397 |
3
|
|
398 |
3
|
|
399 |
3
|
|
400 |
3
|
|
401 |
3
|
|
402 |
3
|
|
403 |
3
|
|
404 |
3
|
|
405 |
3
|
|
406 |
3
|
|
407 |
3
|
|
408 |
3
|
|
409 |
3
|
|
410 |
3
|
|
411 |
3
|
|
412 |
3
|
|
413 |
3
|
|
414 |
3
|
|
415 |
3
|
|
416 |
4
|
|
417 |
4
|
|
418 |
4
|
|
419 |
4
|
|
420 |
4
|
|
421 |
4
|
|
422 |
4
|
|
423 |
4
|
|
424 |
4
|
|
425 |
4
|
|
426 |
4
|
|
427 |
4
|
|
428 |
4
|
|
429 |
4
|
|
430 |
4
|
|
431 |
4
|
|
432 |
4
|
|
433 |
4
|
|
434 |
4
|
|
435 |
4
|
|
436 |
5
|
|
437 |
5
|
|
438 |
5
|
|
439 |
5
|
|
440 |
5
|
|
441 |
5
|
|
442 |
5
|
|
443 |
5
|
|
444 |
5
|
|
445 |
5
|
|
446 |
5
|
|
447 |
5
|
|
448 |
5
|
|
449 |
5
|
|
450 |
5
|
|
451 |
5
|
|
452 |
5
|
|
453 |
5
|
|
454 |
5
|
|
455 |
5
|
|
456 |
1
|
|
457 |
1
|
|
458 |
1
|
|
459 |
1
|
|
460 |
1
|
|
461 |
1
|
|
462 |
1
|
|
463 |
1
|
|
464 |
1
|
|
465 |
1
|
|
466 |
1
|
|
467 |
1
|
|
468 |
1
|
|
469 |
1
|
|
470 |
1
|
|
471 |
1
|
|
472 |
1
|
|
473 |
1
|
|
474 |
1
|
|
475 |
1
|
|
476 |
2
|
|
477 |
2
|
|
478 |
2
|
|
479 |
2
|
|
480 |
2
|
|
481 |
2
|
|
482 |
2
|
|
483 |
2
|
|
484 |
2
|
|
485 |
2
|
|
486 |
2
|
|
487 |
2
|
|
488 |
2
|
|
489 |
2
|
|
490 |
2
|
|
491 |
2
|
|
492 |
2
|
|
493 |
2
|
|
494 |
2
|
|
495 |
2
|
|
496 |
3
|
|
497 |
3
|
|
498 |
3
|
|
499 |
3
|
|
500 |
3
|
|
501 |
3
|
|
502 |
3
|
|
503 |
3
|
|
504 |
3
|
|
505 |
3
|
|
506 |
3
|
|
507 |
3
|
|
508 |
3
|
|
509 |
3
|
|
510 |
3
|
|
511 |
3
|
|
512 |
3
|
|
513 |
3
|
|
514 |
3
|
|
515 |
3
|
|
516 |
4
|
|
517 |
4
|
|
518 |
4
|
|
519 |
4
|
|
520 |
4
|
|
521 |
4
|
|
522 |
4
|
|
523 |
4
|
|
524 |
4
|
|
525 |
4
|
|
526 |
4
|
|
527 |
4
|
|
528 |
4
|
|
529 |
4
|
|
530 |
4
|
|
531 |
4
|
|
532 |
4
|
|
533 |
4
|
|
534 |
4
|
|
535 |
4
|
|
536 |
5
|
|
537 |
5
|
|
538 |
5
|
|
539 |
5
|
|
540 |
5
|
|
541 |
5
|
|
542 |
5
|
|
543 |
5
|
|
544 |
5
|
|
545 |
5
|
|
546 |
5
|
|
547 |
5
|
|
548 |
5
|
|
549 |
5
|
|
550 |
5
|
|
551 |
5
|
|
552 |
5
|
|
553 |
5
|
|
554 |
5
|
|
555 |
5
|
|
556 |
insert into t1 select * from t1,t1; |
|
557 |
ERROR 42000: Not unique table/alias: 't1' |
|
558 |
drop table t1,t2; |
|
559 |
create table t1 (a int not null primary key, b char(10)); |
|
560 |
create table t2 (a int not null, b char(10)); |
|
561 |
insert into t1 values (1,"t1:1"),(3,"t1:3"); |
|
562 |
insert into t2 values (2,"t2:2"), (3,"t2:3"); |
|
563 |
insert into t1 select * from t2; |
|
564 |
ERROR 23000: Duplicate entry '3' for key 'PRIMARY' |
|
565 |
select * from t1; |
|
566 |
a b |
|
567 |
1 t1:1 |
|
568 |
3 t1:3 |
|
569 |
replace into t1 select * from t2; |
|
570 |
select * from t1; |
|
571 |
a b |
|
572 |
1 t1:1 |
|
520.1.16
by Brian Aker
More test updates (one ulong fix) |
573 |
2 t2:2 |
1
by brian
clean slate |
574 |
3 t2:3 |
575 |
drop table t1,t2; |
|
520.1.16
by Brian Aker
More test updates (one ulong fix) |
576 |
CREATE TABLE t1 ( USID INTEGER, ServerID int, State ENUM ('unknown', 'Access-Granted', 'Session-Active', 'Session-Closed' ) NOT NULL DEFAULT 'unknown', SessionID CHAR(32), User CHAR(32) NOT NULL DEFAULT '<UNKNOWN>', NASAddr INTEGER, NASPort INTEGER, NASPortType INTEGER, ConnectSpeed INTEGER, CarrierType CHAR(32), CallingStationID CHAR(32), CalledStationID CHAR(32), AssignedAddr INTEGER, SessionTime INTEGER, PacketsIn INTEGER, OctetsIn INTEGER, PacketsOut INTEGER, OctetsOut INTEGER, TerminateCause INTEGER, UnauthTime int, AccessRequestTime DATETIME, AcctStartTime DATETIME, AcctLastTime DATETIME, LastModification TIMESTAMP NOT NULL); |
577 |
CREATE TABLE t2 ( USID INTEGER AUTO_INCREMENT, ServerID int, State ENUM ('unknown', 'Access-Granted', 'Session-Active', 'Session-Closed' ) NOT NULL DEFAULT 'unknown', SessionID CHAR(32), User TEXT NOT NULL, NASAddr INTEGER, NASPort INTEGER, NASPortType INTEGER, ConnectSpeed INTEGER, CarrierType CHAR(32), CallingStationID CHAR(32), CalledStationID CHAR(32), AssignedAddr INTEGER, SessionTime INTEGER, PacketsIn INTEGER, OctetsIn INTEGER, PacketsOut INTEGER, OctetsOut INTEGER, TerminateCause INTEGER, UnauthTime int, AccessRequestTime DATETIME, AcctStartTime DATETIME, AcctLastTime DATETIME, LastModification TIMESTAMP NOT NULL, INDEX(USID,ServerID,NASAddr,SessionID), INDEX(AssignedAddr)); |
|
1
by brian
clean slate |
578 |
INSERT INTO t1 VALUES (39,42,'Access-Granted','46','491721000045',2130706433,17690,NULL,NULL,'Localnet','491721000045','49172200000',754974766,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'2003-07-18 00:11:21',NULL,NULL,20030718001121); |
579 |
INSERT INTO t2 SELECT USID, ServerID, State, SessionID, User, NASAddr, NASPort, NASPortType, ConnectSpeed, CarrierType, CallingStationID, CalledStationID, AssignedAddr, SessionTime, PacketsIn, OctetsIn, PacketsOut, OctetsOut, TerminateCause, UnauthTime, AccessRequestTime, AcctStartTime, AcctLastTime, LastModification from t1 LIMIT 1; |
|
580 |
drop table t1,t2; |
|
581 |
CREATE TABLE t1( |
|
582 |
Month date NOT NULL, |
|
520.1.16
by Brian Aker
More test updates (one ulong fix) |
583 |
Type int NOT NULL auto_increment, |
584 |
Field int NOT NULL, |
|
585 |
Count int NOT NULL, |
|
586 |
PRIMARY KEY (Type), |
|
1
by brian
clean slate |
587 |
UNIQUE KEY Month (Month,Type,Field) |
588 |
);
|
|
520.1.16
by Brian Aker
More test updates (one ulong fix) |
589 |
insert into t1 Values (20030901, 1, 1, 100); |
590 |
insert into t1 Values (20030901, 2, 2, 100); |
|
591 |
insert into t1 Values (20030901, 3, 3, 100); |
|
592 |
insert into t1 Values (20030901, 4, 4, 100); |
|
593 |
insert into t1 Values (20030901, 5, 5, 100); |
|
1
by brian
clean slate |
594 |
select * from t1; |
595 |
Month Type Field Count |
|
596 |
2003-09-01 1 1 100 |
|
597 |
2003-09-01 2 2 100 |
|
520.1.16
by Brian Aker
More test updates (one ulong fix) |
598 |
2003-09-01 3 3 100 |
599 |
2003-09-01 4 4 100 |
|
600 |
2003-09-01 5 5 100 |
|
1
by brian
clean slate |
601 |
Select null, Field, Count From t1 Where Month=20030901 and Type=2; |
602 |
NULL Field Count |
|
603 |
NULL 2 100 |
|
604 |
create table t2(No int not null, Field int not null, Count int not null); |
|
605 |
insert into t2 Select 0, Field, Count From t1 Where Month=20030901 and Type=2; |
|
606 |
select * from t2; |
|
607 |
No Field Count |
|
608 |
0 2 100 |
|
609 |
drop table t1, t2; |
|
610 |
CREATE TABLE t1 ( |
|
520.1.16
by Brian Aker
More test updates (one ulong fix) |
611 |
ID int NOT NULL auto_increment, |
612 |
NO int NOT NULL default '0', |
|
613 |
SEQ int NOT NULL default '0', |
|
1
by brian
clean slate |
614 |
PRIMARY KEY (ID), |
615 |
KEY t1$NO (SEQ,NO) |
|
1063.9.6
by Stewart Smith
insert_select test for MyISAM as temp table only: 1 open table twice, 3x can be myisam temp. |
616 |
);
|
1
by brian
clean slate |
617 |
INSERT INTO t1 (SEQ, NO) SELECT "1" AS SEQ, IF(MAX(NO) IS NULL, 0, MAX(NO)) + 1 AS NO FROM t1 WHERE (SEQ = 1); |
618 |
select SQL_BUFFER_RESULT * from t1 WHERE (SEQ = 1); |
|
619 |
ID NO SEQ |
|
620 |
1 1 1 |
|
621 |
drop table t1; |
|
622 |
create table t1 (f1 int); |
|
623 |
create table t2 (ff1 int unique, ff2 int default 1); |
|
624 |
insert into t1 values (1),(1),(2); |
|
625 |
insert into t2(ff1) select f1 from t1 on duplicate key update ff2=ff2+1; |
|
626 |
select * from t2; |
|
627 |
ff1 ff2 |
|
628 |
1 2 |
|
629 |
2 1 |
|
630 |
drop table t1, t2; |
|
631 |
create table t1 (a int unique); |
|
632 |
create table t2 (a int, b int); |
|
633 |
create table t3 (c int, d int); |
|
634 |
insert into t1 values (1),(2); |
|
635 |
insert into t2 values (1,2); |
|
636 |
insert into t3 values (1,6),(3,7); |
|
637 |
select * from t1; |
|
638 |
a
|
|
639 |
1
|
|
640 |
2
|
|
641 |
insert into t1 select a from t2 on duplicate key update a= t1.a + t2.b; |
|
642 |
select * from t1; |
|
643 |
a
|
|
644 |
2
|
|
645 |
3
|
|
646 |
insert into t1 select a+1 from t2 on duplicate key update t1.a= t1.a + t2.b+1; |
|
647 |
select * from t1; |
|
648 |
a
|
|
649 |
3
|
|
650 |
5
|
|
651 |
insert into t1 select t3.c from t3 on duplicate key update a= a + t3.d; |
|
652 |
select * from t1; |
|
653 |
a
|
|
654 |
1
|
|
655 |
5
|
|
656 |
10
|
|
657 |
insert into t1 select t2.a from t2 group by t2.a on duplicate key update a= a + 10; |
|
658 |
insert into t1 select t2.a from t2 on duplicate key update a= a + t2.b; |
|
659 |
ERROR 23000: Column 'a' in field list is ambiguous |
|
660 |
insert into t1 select t2.a from t2 on duplicate key update t2.a= a + t2.b; |
|
661 |
ERROR 42S22: Unknown column 't2.a' in 'field list' |
|
662 |
insert into t1 select t2.a from t2 group by t2.a on duplicate key update a= t1.a + t2.b; |
|
663 |
ERROR 42S22: Unknown column 't2.b' in 'field list' |
|
664 |
drop table t1,t2,t3; |
|
665 |
create table t1(f1 varchar(5) key); |
|
666 |
insert into t1(f1) select if(max(f1) is null, '2000',max(f1)+1) from t1; |
|
667 |
insert into t1(f1) select if(max(f1) is null, '2000',max(f1)+1) from t1; |
|
668 |
insert into t1(f1) select if(max(f1) is null, '2000',max(f1)+1) from t1; |
|
669 |
select * from t1; |
|
670 |
f1
|
|
671 |
2000
|
|
672 |
2001
|
|
673 |
2002
|
|
674 |
drop table t1; |
|
675 |
create table t1(x int, y int); |
|
676 |
create table t2(x int, z int); |
|
677 |
insert into t1(x,y) select x,z from t2 on duplicate key update x=values(x); |
|
678 |
insert into t1(x,y) select x,z from t2 on duplicate key update x=values(z); |
|
679 |
ERROR 42S22: Unknown column 'z' in 'field list' |
|
680 |
insert into t1(x,y) select x,z from t2 on duplicate key update x=values(t2.x); |
|
681 |
ERROR 42S22: Unknown column 't2.x' in 'field list' |
|
682 |
drop table t1,t2; |
|
683 |
CREATE TABLE t1 (a int PRIMARY KEY); |
|
684 |
INSERT INTO t1 values (1), (2); |
|
685 |
flush status; |
|
686 |
INSERT INTO t1 SELECT a + 2 FROM t1 LIMIT 1; |
|
687 |
show status like 'Handler_read%'; |
|
688 |
Variable_name Value |
|
1273.16.1
by Brian Aker
More removal of show code. |
689 |
Handler_read_first # |
690 |
Handler_read_key # |
|
691 |
Handler_read_next # |
|
692 |
Handler_read_prev # |
|
693 |
Handler_read_rnd # |
|
694 |
Handler_read_rnd_next # |
|
1
by brian
clean slate |
695 |
DROP TABLE t1; |
696 |
CREATE TABLE t1 ( |
|
520.1.16
by Brian Aker
More test updates (one ulong fix) |
697 |
f1 int NOT NULL auto_increment PRIMARY KEY, |
1
by brian
clean slate |
698 |
f2 varchar(100) NOT NULL default '' |
699 |
);
|
|
700 |
CREATE TABLE t2 ( |
|
701 |
f1 varchar(10) NOT NULL default '', |
|
702 |
f2 char(3) NOT NULL default '', |
|
703 |
PRIMARY KEY (`f1`), |
|
704 |
KEY `k1` (`f2`, `f1`) |
|
705 |
);
|
|
706 |
INSERT INTO t1 values(NULL, ''); |
|
707 |
INSERT INTO `t2` VALUES ('486878','WDT'),('486910','WDT'); |
|
708 |
SELECT COUNT(*) FROM t1; |
|
709 |
COUNT(*) |
|
710 |
1
|
|
711 |
SELECT min(t2.f1) FROM t1, t2 where t2.f2 = 'SIR' GROUP BY t1.f1; |
|
712 |
min(t2.f1) |
|
713 |
INSERT INTO t1 (f2) |
|
714 |
SELECT min(t2.f1) FROM t1, t2 where t2.f2 = 'SIR' GROUP BY t1.f1; |
|
715 |
SELECT COUNT(*) FROM t1; |
|
716 |
COUNT(*) |
|
717 |
1
|
|
718 |
SELECT * FROM t1; |
|
719 |
f1 f2 |
|
720 |
1
|
|
721 |
DROP TABLE t1, t2; |
|
722 |
CREATE TABLE t1 (x int, y int); |
|
723 |
CREATE TABLE t2 (z int, y int); |
|
724 |
CREATE TABLE t3 (a int, b int); |
|
725 |
INSERT INTO t3 (SELECT x, y FROM t1 JOIN t2 USING (y) WHERE z = 1); |
|
726 |
DROP TABLE IF EXISTS t1,t2,t3; |
|
727 |
CREATE DATABASE bug21774_1; |
|
728 |
CREATE DATABASE bug21774_2; |
|
729 |
CREATE TABLE bug21774_1.t1(id VARCHAR(10) NOT NULL,label VARCHAR(255)); |
|
730 |
CREATE TABLE bug21774_2.t1(id VARCHAR(10) NOT NULL,label VARCHAR(255)); |
|
731 |
CREATE TABLE bug21774_1.t2(id VARCHAR(10) NOT NULL,label VARCHAR(255)); |
|
732 |
INSERT INTO bug21774_2.t1 SELECT t1.* FROM bug21774_1.t1; |
|
733 |
use bug21774_1; |
|
734 |
INSERT INTO bug21774_2.t1 SELECT t1.* FROM t1; |
|
735 |
DROP DATABASE bug21774_1; |
|
736 |
DROP DATABASE bug21774_2; |
|
737 |
USE test; |
|
738 |
create table t1(f1 int primary key, f2 int); |
|
739 |
insert into t1 values (1,1); |
|
740 |
affected rows: 1 |
|
741 |
insert into t1 values (1,1) on duplicate key update f2=1; |
|
742 |
affected rows: 0 |
|
743 |
insert into t1 values (1,1) on duplicate key update f2=2; |
|
744 |
affected rows: 2 |
|
745 |
select * from t1; |
|
746 |
f1 f2 |
|
747 |
1 2 |
|
748 |
drop table t1; |
|
749 |
CREATE TABLE t1 (f1 INT, f2 INT ); |
|
750 |
CREATE TABLE t2 (f1 INT PRIMARY KEY, f2 INT); |
|
751 |
INSERT INTO t1 VALUES (1,1),(2,2),(10,10); |
|
752 |
INSERT INTO t2 (f1, f2) SELECT f1, f2 FROM t1; |
|
753 |
INSERT INTO t2 (f1, f2) |
|
754 |
SELECT f1, f1 FROM t2 src WHERE f1 < 2 |
|
755 |
ON DUPLICATE KEY UPDATE f1 = 100 + src.f1; |
|
756 |
SELECT * FROM t2; |
|
757 |
f1 f2 |
|
758 |
2 2 |
|
759 |
10 10 |
|
520.1.16
by Brian Aker
More test updates (one ulong fix) |
760 |
101 1 |
1
by brian
clean slate |
761 |
DROP TABLE t1, t2; |
762 |
CREATE TABLE t1 ( |
|
763 |
id INT AUTO_INCREMENT PRIMARY KEY, |
|
764 |
prev_id INT, |
|
765 |
join_id INT DEFAULT 0); |
|
766 |
INSERT INTO t1 (prev_id) VALUES (NULL), (1), (2); |
|
767 |
SELECT * FROM t1; |
|
768 |
id prev_id join_id |
|
769 |
1 NULL 0 |
|
770 |
2 1 0 |
|
771 |
3 2 0 |
|
772 |
CREATE TABLE t2 (join_id INT); |
|
773 |
INSERT INTO t2 (join_id) VALUES (0); |
|
774 |
INSERT INTO t1 (prev_id) SELECT id |
|
775 |
FROM t2 LEFT JOIN t1 ON t1.join_id = t2.join_id |
|
776 |
ORDER BY id DESC LIMIT 1; |
|
777 |
SELECT * FROM t1; |
|
778 |
id prev_id join_id |
|
779 |
1 NULL 0 |
|
780 |
2 1 0 |
|
781 |
3 2 0 |
|
782 |
4 3 0 |
|
783 |
DROP TABLE t1,t2; |
|
784 |
#
|
|
785 |
# Bug#30384: Having SQL_BUFFER_RESULT option in the
|
|
786 |
# CREATE .. KEY(..) .. SELECT led to creating corrupted index.
|
|
787 |
#
|
|
788 |
create table t1(f1 int); |
|
789 |
insert into t1 values(1),(2),(3); |
|
1063.9.6
by Stewart Smith
insert_select test for MyISAM as temp table only: 1 open table twice, 3x can be myisam temp. |
790 |
create temporary table t2 (key(f1)) engine=myisam select sql_buffer_result f1 from t1; |
1222.1.10
by Brian Aker
Removes options from DDL left in Cursor for admin operations (they were |
791 |
check table t2; |
1
by brian
clean slate |
792 |
Table Op Msg_type Msg_text |
793 |
test.t2 check status OK |
|
794 |
drop table t1,t2; |
|
795 |
##################################################################
|