1set optimizer_switch='batched_key_access=on,block_nested_loop=off,mrr_cost_based=off';
2set optimizer_switch='semijoin=on,materialization=on,firstmatch=on,loosescan=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=off';
3drop table if exists t1,t2,t3,t4,t11;
4drop table if exists t1_1,t1_2,t9_1,t9_2,t1aa,t2aa;
5drop view if exists v1;
6CREATE TABLE t1 (
7Period smallint(4) unsigned zerofill DEFAULT '0000' NOT NULL,
8Varor_period smallint(4) unsigned DEFAULT '0' NOT NULL
9);
10INSERT INTO t1 VALUES (9410,9412);
11select period from t1;
12period
139410
14select * from t1;
15Period	Varor_period
169410	9412
17select t1.* from t1;
18Period	Varor_period
199410	9412
20CREATE TABLE t2 (
21auto int not null auto_increment,
22fld1 int(6) unsigned zerofill DEFAULT '000000' NOT NULL,
23companynr tinyint(2) unsigned zerofill DEFAULT '00' NOT NULL,
24fld3 char(30) DEFAULT '' NOT NULL,
25fld4 char(35) DEFAULT '' NOT NULL,
26fld5 char(35) DEFAULT '' NOT NULL,
27fld6 char(4) DEFAULT '' NOT NULL,
28UNIQUE fld1 (fld1),
29KEY fld3 (fld3),
30PRIMARY KEY (auto)
31);
32select t2.fld3 from t2 where companynr = 58 and fld3 like "%imaginable%";
33fld3
34imaginable
35select fld3 from t2 where fld3 like "%cultivation" ;
36fld3
37cultivation
38select t2.fld3,companynr from t2 where companynr = 57+1 order by fld3;
39fld3	companynr
40concoct	58
41druggists	58
42engrossing	58
43Eurydice	58
44exclaimers	58
45ferociousness	58
46hopelessness	58
47Huey	58
48imaginable	58
49judges	58
50merging	58
51ostrich	58
52peering	58
53Phelps	58
54presumes	58
55Ruth	58
56sentences	58
57Shylock	58
58straggled	58
59synergy	58
60thanking	58
61tying	58
62unlocks	58
63select fld3,companynr from t2 where companynr = 58 order by fld3;
64fld3	companynr
65concoct	58
66druggists	58
67engrossing	58
68Eurydice	58
69exclaimers	58
70ferociousness	58
71hopelessness	58
72Huey	58
73imaginable	58
74judges	58
75merging	58
76ostrich	58
77peering	58
78Phelps	58
79presumes	58
80Ruth	58
81sentences	58
82Shylock	58
83straggled	58
84synergy	58
85thanking	58
86tying	58
87unlocks	58
88select fld3 from t2 order by fld3 desc limit 10;
89fld3
90youthfulness
91yelped
92Wotan
93workers
94Witt
95witchcraft
96Winsett
97Willy
98willed
99wildcats
100select fld3 from t2 order by fld3 desc limit 5;
101fld3
102youthfulness
103yelped
104Wotan
105workers
106Witt
107select fld3 from t2 order by fld3 desc limit 5,5;
108fld3
109witchcraft
110Winsett
111Willy
112willed
113wildcats
114select t2.fld3 from t2 where fld3 = 'honeysuckle';
115fld3
116honeysuckle
117select t2.fld3 from t2 where fld3 LIKE 'honeysuckl_';
118fld3
119honeysuckle
120select t2.fld3 from t2 where fld3 LIKE 'hon_ysuckl_';
121fld3
122honeysuckle
123select t2.fld3 from t2 where fld3 LIKE 'honeysuckle%';
124fld3
125honeysuckle
126select t2.fld3 from t2 where fld3 LIKE 'h%le';
127fld3
128honeysuckle
129select t2.fld3 from t2 where fld3 LIKE 'honeysuckle_';
130fld3
131select t2.fld3 from t2 where fld3 LIKE 'don_t_find_me_please%';
132fld3
133explain select t2.fld3 from t2 where fld3 = 'honeysuckle';
134id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1351	SIMPLE	t2	ref	fld3	fld3	30	const	1	Using where; Using index
136explain select fld3 from t2 ignore index (fld3) where fld3 = 'honeysuckle';
137id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1381	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	1199	Using where
139explain select fld3 from t2 use index (fld1) where fld3 = 'honeysuckle';
140id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1411	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	1199	Using where
142explain select fld3 from t2 use index (fld3) where fld3 = 'honeysuckle';
143id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1441	SIMPLE	t2	ref	fld3	fld3	30	const	1	Using where; Using index
145explain select fld3 from t2 use index (fld1,fld3) where fld3 = 'honeysuckle';
146id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1471	SIMPLE	t2	ref	fld3	fld3	30	const	1	Using where; Using index
148explain select fld3 from t2 ignore index (fld3,not_used);
149ERROR 42000: Key 'not_used' doesn't exist in table 't2'
150explain select fld3 from t2 use index (not_used);
151ERROR 42000: Key 'not_used' doesn't exist in table 't2'
152select t2.fld3 from t2 where fld3 >= 'honeysuckle' and fld3 <= 'honoring' order by fld3;
153fld3
154honeysuckle
155honoring
156explain select t2.fld3 from t2 where fld3 >= 'honeysuckle' and fld3 <= 'honoring' order by fld3;
157id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1581	SIMPLE	t2	range	fld3	fld3	30	NULL	2	Using where; Using index
159select fld1,fld3 from t2 where fld3="Colombo" or fld3 = "nondecreasing" order by fld3;
160fld1	fld3
161148504	Colombo
162068305	Colombo
163000000	nondecreasing
164select fld1,fld3 from t2 where companynr = 37 and fld3 = 'appendixes';
165fld1	fld3
166232605	appendixes
1671232605	appendixes
1681232606	appendixes
1691232607	appendixes
1701232608	appendixes
1711232609	appendixes
172select fld1 from t2 where fld1=250501 or fld1="250502";
173fld1
174250501
175250502
176explain select fld1 from t2 where fld1=250501 or fld1="250502";
177id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1781	SIMPLE	t2	range	fld1	fld1	4	NULL	2	Using where; Using index
179select fld1 from t2 where fld1=250501 or fld1=250502 or fld1 >= 250505 and fld1 <= 250601 or fld1 between 250501 and 250502;
180fld1
181250501
182250502
183250505
184250601
185explain select fld1 from t2 where fld1=250501 or fld1=250502 or fld1 >= 250505 and fld1 <= 250601 or fld1 between 250501 and 250502;
186id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1871	SIMPLE	t2	range	fld1	fld1	4	NULL	4	Using where; Using index
188select fld1,fld3 from t2 where companynr = 37 and fld3 like 'f%';
189fld1	fld3
190012001	flanking
191013602	foldout
192013606	fingerings
193018007	fanatic
194018017	featherweight
195018054	fetters
196018103	flint
197018104	flopping
198036002	funereal
199038017	fetched
200038205	firearm
201058004	Fenton
202088303	feminine
203186002	freakish
204188007	flurried
205188505	fitting
206198006	furthermore
207202301	Fitzpatrick
208208101	fiftieth
209208113	freest
210218008	finishers
211218022	feed
212218401	faithful
213226205	foothill
214226209	furnishings
215228306	forthcoming
216228311	fated
217231315	freezes
218232102	forgivably
219238007	filial
220238008	fixedly
221select fld3 from t2 where fld3 like "L%" and fld3 = "ok";
222fld3
223select fld3 from t2 where (fld3 like "C%" and fld3 = "Chantilly");
224fld3
225Chantilly
226select fld1,fld3 from t2 where fld1 like "25050%";
227fld1	fld3
228250501	poisoning
229250502	Iraqis
230250503	heaving
231250504	population
232250505	bomb
233select fld1,fld3 from t2 where fld1 like "25050_";
234fld1	fld3
235250501	poisoning
236250502	Iraqis
237250503	heaving
238250504	population
239250505	bomb
240select distinct companynr from t2;
241companynr
24200
24337
24436
24550
24658
24729
24840
24953
25065
25141
25234
25368
254select distinct companynr from t2 order by companynr;
255companynr
25600
25729
25834
25936
26037
26140
26241
26350
26453
26558
26665
26768
268select distinct companynr from t2 order by companynr desc;
269companynr
27068
27165
27258
27353
27450
27541
27640
27737
27836
27934
28029
28100
282select distinct t2.fld3,period from t2,t1 where companynr=37 and fld3 like "O%";
283fld3	period
284obliterates	9410
285offload	9410
286opaquely	9410
287organizer	9410
288overestimating	9410
289overlay	9410
290select distinct fld3 from t2 where companynr = 34 order by fld3;
291fld3
292absentee
293accessed
294ahead
295alphabetic
296Asiaticizations
297attitude
298aye
299bankruptcies
300belays
301Blythe
302bomb
303boulevard
304bulldozes
305cannot
306caressing
307charcoal
308checksumming
309chess
310clubroom
311colorful
312cosy
313creator
314crying
315Darius
316diffusing
317duality
318Eiffel
319Epiphany
320Ernestine
321explorers
322exterminated
323famine
324forked
325Gershwins
326heaving
327Hodges
328Iraqis
329Italianization
330Lagos
331landslide
332libretto
333Majorca
334mastering
335narrowed
336occurred
337offerers
338Palestine
339Peruvianizes
340pharmaceutic
341poisoning
342population
343Pygmalion
344rats
345realest
346recording
347regimented
348retransmitting
349reviver
350rouses
351scars
352sicker
353sleepwalk
354stopped
355sugars
356translatable
357uncles
358unexpected
359uprisings
360versatility
361vest
362select distinct fld3 from t2 limit 10;
363fld3
364abates
365abiding
366Abraham
367abrogating
368absentee
369abut
370accessed
371accruing
372accumulating
373accuracies
374select distinct fld3 from t2 having fld3 like "A%" limit 10;
375fld3
376abates
377abiding
378Abraham
379abrogating
380absentee
381abut
382accessed
383accruing
384accumulating
385accuracies
386select distinct substring(fld3,1,3) from t2 where fld3 like "A%";
387substring(fld3,1,3)
388aba
389abi
390Abr
391abs
392abu
393acc
394acq
395acu
396Ade
397adj
398Adl
399adm
400Ado
401ads
402adv
403aer
404aff
405afi
406afl
407afo
408agi
409ahe
410aim
411air
412Ald
413alg
414ali
415all
416alp
417alr
418ama
419ame
420amm
421ana
422and
423ane
424Ang
425ani
426Ann
427Ant
428api
429app
430aqu
431Ara
432arc
433Arm
434arr
435Art
436Asi
437ask
438asp
439ass
440ast
441att
442aud
443Aug
444aut
445ave
446avo
447awe
448aye
449Azt
450select distinct substring(fld3,1,3) as a from t2 having a like "A%" order by a limit 10;
451a
452aba
453abi
454Abr
455abs
456abu
457acc
458acq
459acu
460Ade
461adj
462select distinct substring(fld3,1,3) from t2 where fld3 like "A%" limit 10;
463substring(fld3,1,3)
464aba
465abi
466Abr
467abs
468abu
469acc
470acq
471acu
472Ade
473adj
474select distinct substring(fld3,1,3) as a from t2 having a like "A%" limit 10;
475a
476aba
477abi
478Abr
479abs
480abu
481acc
482acq
483acu
484Ade
485adj
486create table t3 (
487period    int not null,
488name      char(32) not null,
489companynr int not null,
490price     double(11,0),
491price2     double(11,0),
492key (period),
493key (name)
494);
495create temporary table tmp engine = myisam select * from t3;
496insert into t3 select * from tmp;
497insert into tmp select * from t3;
498insert into t3 select * from tmp;
499insert into tmp select * from t3;
500insert into t3 select * from tmp;
501insert into tmp select * from t3;
502insert into t3 select * from tmp;
503insert into tmp select * from t3;
504insert into t3 select * from tmp;
505insert into tmp select * from t3;
506insert into t3 select * from tmp;
507insert into tmp select * from t3;
508insert into t3 select * from tmp;
509insert into tmp select * from t3;
510insert into t3 select * from tmp;
511insert into tmp select * from t3;
512insert into t3 select * from tmp;
513alter table t3 add t2nr int not null auto_increment primary key first;
514drop table tmp;
515SET BIG_TABLES=1;
516select distinct concat(fld3," ",fld3) as namn from t2,t3 where t2.fld1=t3.t2nr order by namn limit 10;
517namn
518Abraham Abraham
519abrogating abrogating
520admonishing admonishing
521Adolph Adolph
522afield afield
523aging aging
524ammonium ammonium
525analyzable analyzable
526animals animals
527animized animized
528SET BIG_TABLES=0;
529select distinct concat(fld3," ",fld3) from t2,t3 where t2.fld1=t3.t2nr order by fld3 limit 10;
530concat(fld3," ",fld3)
531Abraham Abraham
532abrogating abrogating
533admonishing admonishing
534Adolph Adolph
535afield afield
536aging aging
537ammonium ammonium
538analyzable analyzable
539animals animals
540animized animized
541select distinct fld5 from t2 limit 10;
542fld5
543neat
544Steinberg
545jarring
546tinily
547balled
548persist
549attainments
550fanatic
551measures
552rightfulness
553select distinct fld3,count(*) from t2 group by companynr,fld3 limit 10;
554fld3	count(*)
555affixed	1
556and	1
557annoyers	1
558Anthony	1
559assayed	1
560assurers	1
561attendants	1
562bedlam	1
563bedpost	1
564boasted	1
565SET BIG_TABLES=1;
566select distinct fld3,count(*) from t2 group by companynr,fld3 limit 10;
567fld3	count(*)
568affixed	1
569and	1
570annoyers	1
571Anthony	1
572assayed	1
573assurers	1
574attendants	1
575bedlam	1
576bedpost	1
577boasted	1
578SET BIG_TABLES=0;
579select distinct fld3,repeat("a",length(fld3)),count(*) from t2 group by companynr,fld3 limit 100,10;
580fld3	repeat("a",length(fld3))	count(*)
581circus	aaaaaa	1
582cited	aaaaa	1
583Colombo	aaaaaaa	1
584congresswoman	aaaaaaaaaaaaa	1
585contrition	aaaaaaaaaa	1
586corny	aaaaa	1
587cultivation	aaaaaaaaaaa	1
588definiteness	aaaaaaaaaaaa	1
589demultiplex	aaaaaaaaaaa	1
590disappointing	aaaaaaaaaaaaa	1
591select distinct companynr,rtrim(space(512+companynr)) from t3 order by 1,2;
592companynr	rtrim(space(512+companynr))
59337
59478
595101
596154
597311
598447
599512
600select distinct fld3 from t2,t3 where t2.companynr = 34 and t2.fld1=t3.t2nr order by fld3;
601fld3
602explain select t3.t2nr,fld3 from t2,t3 where t2.companynr = 34 and t2.fld1=t3.t2nr order by t3.t2nr,fld3;
603id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
6041	SIMPLE	t2	ALL	fld1	NULL	NULL	NULL	1199	Using where; Using temporary; Using filesort
6051	SIMPLE	t3	eq_ref	PRIMARY	PRIMARY	4	test.t2.fld1	1	Using where; Using index
606explain select * from t3 as t1,t3 where t1.period=t3.period order by t3.period;
607id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
6081	SIMPLE	t1	ALL	period	NULL	NULL	NULL	41810	Using temporary; Using filesort
6091	SIMPLE	t3	ref	period	period	4	test.t1.period	4181	Using join buffer (Batched Key Access)
610explain select * from t3 as t1,t3 where t1.period=t3.period order by t3.period limit 10;
611id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
6121	SIMPLE	t3	ALL	period	NULL	NULL	NULL	41810	Using temporary; Using filesort
6131	SIMPLE	t1	ref	period	period	4	test.t3.period	4181	Using join buffer (Batched Key Access)
614explain select * from t3 as t1,t3 where t1.period=t3.period order by t1.period limit 10;
615id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
6161	SIMPLE	t1	ALL	period	NULL	NULL	NULL	41810	Using temporary; Using filesort
6171	SIMPLE	t3	ref	period	period	4	test.t1.period	4181	Using join buffer (Batched Key Access)
618select period from t1;
619period
6209410
621select period from t1 where period=1900;
622period
623select fld3,period from t1,t2 where fld1 = 011401 order by period;
624fld3	period
625breaking	9410
626select fld3,period from t2,t3 where t2.fld1 = 011401 and t2.fld1=t3.t2nr and t3.period=1001;
627fld3	period
628breaking	1001
629explain select fld3,period from t2,t3 where t2.fld1 = 011401 and t3.t2nr=t2.fld1 and 1001 = t3.period;
630id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
6311	SIMPLE	t2	const	fld1	fld1	4	const	1	NULL
6321	SIMPLE	t3	const	PRIMARY,period	PRIMARY	4	const	1	NULL
633select fld3,period from t2,t1 where companynr*10 = 37*10;
634fld3	period
635breaking	9410
636Romans	9410
637intercepted	9410
638bewilderingly	9410
639astound	9410
640admonishing	9410
641sumac	9410
642flanking	9410
643combed	9410
644subjective	9410
645scatterbrain	9410
646Eulerian	9410
647Kane	9410
648overlay	9410
649perturb	9410
650goblins	9410
651annihilates	9410
652Wotan	9410
653snatching	9410
654concludes	9410
655laterally	9410
656yelped	9410
657grazing	9410
658Baird	9410
659celery	9410
660misunderstander	9410
661handgun	9410
662foldout	9410
663mystic	9410
664succumbed	9410
665Nabisco	9410
666fingerings	9410
667aging	9410
668afield	9410
669ammonium	9410
670boat	9410
671intelligibility	9410
672Augustine	9410
673teethe	9410
674dreaded	9410
675scholastics	9410
676audiology	9410
677wallet	9410
678parters	9410
679eschew	9410
680quitter	9410
681neat	9410
682Steinberg	9410
683jarring	9410
684tinily	9410
685balled	9410
686persist	9410
687attainments	9410
688fanatic	9410
689measures	9410
690rightfulness	9410
691capably	9410
692impulsive	9410
693starlet	9410
694terminators	9410
695untying	9410
696announces	9410
697featherweight	9410
698pessimist	9410
699daughter	9410
700decliner	9410
701lawgiver	9410
702stated	9410
703readable	9410
704attrition	9410
705cascade	9410
706motors	9410
707interrogate	9410
708pests	9410
709stairway	9410
710dopers	9410
711testicle	9410
712Parsifal	9410
713leavings	9410
714postulation	9410
715squeaking	9410
716contrasted	9410
717leftover	9410
718whiteners	9410
719erases	9410
720Punjab	9410
721Merritt	9410
722Quixotism	9410
723sweetish	9410
724dogging	9410
725scornfully	9410
726bellow	9410
727bills	9410
728cupboard	9410
729sureties	9410
730puddings	9410
731fetters	9410
732bivalves	9410
733incurring	9410
734Adolph	9410
735pithed	9410
736Miles	9410
737trimmings	9410
738tragedies	9410
739skulking	9410
740flint	9410
741flopping	9410
742relaxing	9410
743offload	9410
744suites	9410
745lists	9410
746animized	9410
747multilayer	9410
748standardizes	9410
749Judas	9410
750vacuuming	9410
751dentally	9410
752humanness	9410
753inch	9410
754Weissmuller	9410
755irresponsibly	9410
756luckily	9410
757culled	9410
758medical	9410
759bloodbath	9410
760subschema	9410
761animals	9410
762Micronesia	9410
763repetitions	9410
764Antares	9410
765ventilate	9410
766pityingly	9410
767interdependent	9410
768Graves	9410
769neonatal	9410
770chafe	9410
771honoring	9410
772realtor	9410
773elite	9410
774funereal	9410
775abrogating	9410
776sorters	9410
777Conley	9410
778lectured	9410
779Abraham	9410
780Hawaii	9410
781cage	9410
782hushes	9410
783Simla	9410
784reporters	9410
785Dutchman	9410
786descendants	9410
787groupings	9410
788dissociate	9410
789coexist	9410
790Beebe	9410
791Taoism	9410
792Connally	9410
793fetched	9410
794checkpoints	9410
795rusting	9410
796galling	9410
797obliterates	9410
798traitor	9410
799resumes	9410
800analyzable	9410
801terminator	9410
802gritty	9410
803firearm	9410
804minima	9410
805Selfridge	9410
806disable	9410
807witchcraft	9410
808betroth	9410
809Manhattanize	9410
810imprint	9410
811peeked	9410
812swelling	9410
813interrelationships	9410
814riser	9410
815Gandhian	9410
816peacock	9410
817bee	9410
818kanji	9410
819dental	9410
820scarf	9410
821chasm	9410
822insolence	9410
823syndicate	9410
824alike	9410
825imperial	9410
826convulsion	9410
827railway	9410
828validate	9410
829normalizes	9410
830comprehensive	9410
831chewing	9410
832denizen	9410
833schemer	9410
834chronicle	9410
835Kline	9410
836Anatole	9410
837partridges	9410
838brunch	9410
839recruited	9410
840dimensions	9410
841Chicana	9410
842announced	9410
843praised	9410
844employing	9410
845linear	9410
846quagmire	9410
847western	9410
848relishing	9410
849serving	9410
850scheduling	9410
851lore	9410
852eventful	9410
853arteriole	9410
854disentangle	9410
855cured	9410
856Fenton	9410
857avoidable	9410
858drains	9410
859detectably	9410
860husky	9410
861impelling	9410
862undoes	9410
863evened	9410
864squeezes	9410
865destroyer	9410
866rudeness	9410
867beaner	9410
868boorish	9410
869Everhart	9410
870encompass	9410
871mushrooms	9410
872Alison	9410
873externally	9410
874pellagra	9410
875cult	9410
876creek	9410
877Huffman	9410
878Majorca	9410
879governing	9410
880gadfly	9410
881reassigned	9410
882intentness	9410
883craziness	9410
884psychic	9410
885squabbled	9410
886burlesque	9410
887capped	9410
888extracted	9410
889DiMaggio	9410
890exclamation	9410
891subdirectory	9410
892Gothicism	9410
893feminine	9410
894metaphysically	9410
895sanding	9410
896Miltonism	9410
897freakish	9410
898index	9410
899straight	9410
900flurried	9410
901denotative	9410
902coming	9410
903commencements	9410
904gentleman	9410
905gifted	9410
906Shanghais	9410
907sportswriting	9410
908sloping	9410
909navies	9410
910leaflet	9410
911shooter	9410
912Joplin	9410
913babies	9410
914assails	9410
915admiring	9410
916swaying	9410
917Goldstine	9410
918fitting	9410
919Norwalk	9410
920analogy	9410
921deludes	9410
922cokes	9410
923Clayton	9410
924exhausts	9410
925causality	9410
926sating	9410
927icon	9410
928throttles	9410
929communicants	9410
930dehydrate	9410
931priceless	9410
932publicly	9410
933incidentals	9410
934commonplace	9410
935mumbles	9410
936furthermore	9410
937cautioned	9410
938parametrized	9410
939registration	9410
940sadly	9410
941positioning	9410
942babysitting	9410
943eternal	9410
944hoarder	9410
945congregates	9410
946rains	9410
947workers	9410
948sags	9410
949unplug	9410
950garage	9410
951boulder	9410
952specifics	9410
953Teresa	9410
954Winsett	9410
955convenient	9410
956buckboards	9410
957amenities	9410
958resplendent	9410
959sews	9410
960participated	9410
961Simon	9410
962certificates	9410
963Fitzpatrick	9410
964Evanston	9410
965misted	9410
966textures	9410
967save	9410
968count	9410
969rightful	9410
970chaperone	9410
971Lizzy	9410
972clenched	9410
973effortlessly	9410
974accessed	9410
975beaters	9410
976Hornblower	9410
977vests	9410
978indulgences	9410
979infallibly	9410
980unwilling	9410
981excrete	9410
982spools	9410
983crunches	9410
984overestimating	9410
985ineffective	9410
986humiliation	9410
987sophomore	9410
988star	9410
989rifles	9410
990dialysis	9410
991arriving	9410
992indulge	9410
993clockers	9410
994languages	9410
995Antarctica	9410
996percentage	9410
997ceiling	9410
998specification	9410
999regimented	9410
1000ciphers	9410
1001pictures	9410
1002serpents	9410
1003allot	9410
1004realized	9410
1005mayoral	9410
1006opaquely	9410
1007hostess	9410
1008fiftieth	9410
1009incorrectly	9410
1010decomposition	9410
1011stranglings	9410
1012mixture	9410
1013electroencephalography	9410
1014similarities	9410
1015charges	9410
1016freest	9410
1017Greenberg	9410
1018tinting	9410
1019expelled	9410
1020warm	9410
1021smoothed	9410
1022deductions	9410
1023Romano	9410
1024bitterroot	9410
1025corset	9410
1026securing	9410
1027environing	9410
1028cute	9410
1029Crays	9410
1030heiress	9410
1031inform	9410
1032avenge	9410
1033universals	9410
1034Kinsey	9410
1035ravines	9410
1036bestseller	9410
1037equilibrium	9410
1038extents	9410
1039relatively	9410
1040pressure	9410
1041critiques	9410
1042befouled	9410
1043rightfully	9410
1044mechanizing	9410
1045Latinizes	9410
1046timesharing	9410
1047Aden	9410
1048embassies	9410
1049males	9410
1050shapelessly	9410
1051mastering	9410
1052Newtonian	9410
1053finishers	9410
1054abates	9410
1055teem	9410
1056kiting	9410
1057stodgy	9410
1058feed	9410
1059guitars	9410
1060airships	9410
1061store	9410
1062denounces	9410
1063Pyle	9410
1064Saxony	9410
1065serializations	9410
1066Peruvian	9410
1067taxonomically	9410
1068kingdom	9410
1069stint	9410
1070Sault	9410
1071faithful	9410
1072Ganymede	9410
1073tidiness	9410
1074gainful	9410
1075contrary	9410
1076Tipperary	9410
1077tropics	9410
1078theorizers	9410
1079renew	9410
1080already	9410
1081terminal	9410
1082Hegelian	9410
1083hypothesizer	9410
1084warningly	9410
1085journalizing	9410
1086nested	9410
1087Lars	9410
1088saplings	9410
1089foothill	9410
1090labeled	9410
1091imperiously	9410
1092reporters	9410
1093furnishings	9410
1094precipitable	9410
1095discounts	9410
1096excises	9410
1097Stalin	9410
1098despot	9410
1099ripeness	9410
1100Arabia	9410
1101unruly	9410
1102mournfulness	9410
1103boom	9410
1104slaughter	9410
1105Sabine	9410
1106handy	9410
1107rural	9410
1108organizer	9410
1109shipyard	9410
1110civics	9410
1111inaccuracy	9410
1112rules	9410
1113juveniles	9410
1114comprised	9410
1115investigations	9410
1116stabilizes	9410
1117seminaries	9410
1118Hunter	9410
1119sporty	9410
1120test	9410
1121weasels	9410
1122CERN	9410
1123tempering	9410
1124afore	9410
1125Galatean	9410
1126techniques	9410
1127error	9410
1128veranda	9410
1129severely	9410
1130Cassites	9410
1131forthcoming	9410
1132guides	9410
1133vanish	9410
1134lied	9410
1135sawtooth	9410
1136fated	9410
1137gradually	9410
1138widens	9410
1139preclude	9410
1140evenhandedly	9410
1141percentage	9410
1142disobedience	9410
1143humility	9410
1144gleaning	9410
1145petted	9410
1146bloater	9410
1147minion	9410
1148marginal	9410
1149apiary	9410
1150measures	9410
1151precaution	9410
1152repelled	9410
1153primary	9410
1154coverings	9410
1155Artemia	9410
1156navigate	9410
1157spatial	9410
1158Gurkha	9410
1159meanwhile	9410
1160Melinda	9410
1161Butterfield	9410
1162Aldrich	9410
1163previewing	9410
1164glut	9410
1165unaffected	9410
1166inmate	9410
1167mineral	9410
1168impending	9410
1169meditation	9410
1170ideas	9410
1171miniaturizes	9410
1172lewdly	9410
1173title	9410
1174youthfulness	9410
1175creak	9410
1176Chippewa	9410
1177clamored	9410
1178freezes	9410
1179forgivably	9410
1180reduce	9410
1181McGovern	9410
1182Nazis	9410
1183epistle	9410
1184socializes	9410
1185conceptions	9410
1186Kevin	9410
1187uncovering	9410
1188chews	9410
1189appendixes	9410
1190appendixes	9410
1191appendixes	9410
1192appendixes	9410
1193appendixes	9410
1194appendixes	9410
1195raining	9410
1196infest	9410
1197compartment	9410
1198minting	9410
1199ducks	9410
1200roped	9410
1201waltz	9410
1202Lillian	9410
1203repressions	9410
1204chillingly	9410
1205noncritical	9410
1206lithograph	9410
1207spongers	9410
1208parenthood	9410
1209posed	9410
1210instruments	9410
1211filial	9410
1212fixedly	9410
1213relives	9410
1214Pandora	9410
1215watering	9410
1216ungrateful	9410
1217secures	9410
1218poison	9410
1219dusted	9410
1220encompasses	9410
1221presentation	9410
1222Kantian	9410
1223select fld3,period,price,price2 from t2,t3 where t2.fld1=t3.t2nr and period >= 1001 and period <= 1002 and t2.companynr = 37 order by fld3,period, price;
1224fld3	period	price	price2
1225admonishing	1002	28357832	8723648
1226analyzable	1002	28357832	8723648
1227annihilates	1001	5987435	234724
1228Antares	1002	28357832	8723648
1229astound	1001	5987435	234724
1230audiology	1001	5987435	234724
1231Augustine	1002	28357832	8723648
1232Baird	1002	28357832	8723648
1233bewilderingly	1001	5987435	234724
1234breaking	1001	5987435	234724
1235Conley	1001	5987435	234724
1236dentally	1002	28357832	8723648
1237dissociate	1002	28357832	8723648
1238elite	1001	5987435	234724
1239eschew	1001	5987435	234724
1240Eulerian	1001	5987435	234724
1241flanking	1001	5987435	234724
1242foldout	1002	28357832	8723648
1243funereal	1002	28357832	8723648
1244galling	1002	28357832	8723648
1245Graves	1001	5987435	234724
1246grazing	1001	5987435	234724
1247groupings	1001	5987435	234724
1248handgun	1001	5987435	234724
1249humility	1002	28357832	8723648
1250impulsive	1002	28357832	8723648
1251inch	1001	5987435	234724
1252intelligibility	1001	5987435	234724
1253jarring	1001	5987435	234724
1254lawgiver	1001	5987435	234724
1255lectured	1002	28357832	8723648
1256Merritt	1002	28357832	8723648
1257neonatal	1001	5987435	234724
1258offload	1002	28357832	8723648
1259parters	1002	28357832	8723648
1260pityingly	1002	28357832	8723648
1261puddings	1002	28357832	8723648
1262Punjab	1001	5987435	234724
1263quitter	1002	28357832	8723648
1264realtor	1001	5987435	234724
1265relaxing	1001	5987435	234724
1266repetitions	1001	5987435	234724
1267resumes	1001	5987435	234724
1268Romans	1002	28357832	8723648
1269rusting	1001	5987435	234724
1270scholastics	1001	5987435	234724
1271skulking	1002	28357832	8723648
1272stated	1002	28357832	8723648
1273suites	1002	28357832	8723648
1274sureties	1001	5987435	234724
1275testicle	1002	28357832	8723648
1276tinily	1002	28357832	8723648
1277tragedies	1001	5987435	234724
1278trimmings	1001	5987435	234724
1279vacuuming	1001	5987435	234724
1280ventilate	1001	5987435	234724
1281wallet	1001	5987435	234724
1282Weissmuller	1002	28357832	8723648
1283Wotan	1002	28357832	8723648
1284select t2.fld1,fld3,period,price,price2 from t2,t3 where t2.fld1>= 18201 and t2.fld1 <= 18811 and t2.fld1=t3.t2nr and period = 1001 and t2.companynr = 37;
1285fld1	fld3	period	price	price2
1286018201	relaxing	1001	5987435	234724
1287018601	vacuuming	1001	5987435	234724
1288018801	inch	1001	5987435	234724
1289018811	repetitions	1001	5987435	234724
1290create table t4 (
1291companynr tinyint(2) unsigned zerofill NOT NULL default '00',
1292companyname char(30) NOT NULL default '',
1293PRIMARY KEY (companynr),
1294UNIQUE KEY companyname(companyname)
1295) ENGINE=MyISAM MAX_ROWS=50 PACK_KEYS=1 COMMENT='companynames';
1296select STRAIGHT_JOIN t2.companynr,companyname from t4,t2 where t2.companynr=t4.companynr group by t2.companynr;
1297companynr	companyname
129800	Unknown
129929	company 1
130034	company 2
130136	company 3
130237	company 4
130340	company 5
130441	company 6
130550	company 11
130653	company 7
130758	company 8
130865	company 9
130968	company 10
1310select SQL_SMALL_RESULT t2.companynr,companyname from t4,t2 where t2.companynr=t4.companynr group by t2.companynr;
1311companynr	companyname
131200	Unknown
131329	company 1
131434	company 2
131536	company 3
131637	company 4
131740	company 5
131841	company 6
131950	company 11
132053	company 7
132158	company 8
132265	company 9
132368	company 10
1324select * from t1,t1 t12;
1325Period	Varor_period	Period	Varor_period
13269410	9412	9410	9412
1327select t2.fld1,t22.fld1 from t2,t2 t22 where t2.fld1 >= 250501 and t2.fld1 <= 250505 and t22.fld1 >= 250501 and t22.fld1 <= 250505;
1328fld1	fld1
1329250501	250501
1330250501	250502
1331250501	250503
1332250501	250504
1333250501	250505
1334250502	250501
1335250502	250502
1336250502	250503
1337250502	250504
1338250502	250505
1339250503	250501
1340250503	250502
1341250503	250503
1342250503	250504
1343250503	250505
1344250504	250501
1345250504	250502
1346250504	250503
1347250504	250504
1348250504	250505
1349250505	250501
1350250505	250502
1351250505	250503
1352250505	250504
1353250505	250505
1354insert into t2 (fld1, companynr) values (999999,99);
1355select t2.companynr,companyname from t2 left join t4 using (companynr) where t4.companynr is null;
1356companynr	companyname
135799	NULL
1358select count(*) from t2 left join t4 using (companynr) where t4.companynr is not null;
1359count(*)
13601199
1361explain select t2.companynr,companyname from t2 left join t4 using (companynr) where t4.companynr is null;
1362id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
13631	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	1200	NULL
13641	SIMPLE	t4	eq_ref	PRIMARY	PRIMARY	1	test.t2.companynr	1	Using where; Not exists; Using join buffer (Batched Key Access)
1365explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr is null;
1366id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
13671	SIMPLE	t4	ALL	NULL	NULL	NULL	NULL	12	NULL
13681	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	1200	Using where; Not exists
1369select companynr,companyname from t2 left join t4 using (companynr) where companynr is null;
1370companynr	companyname
1371select count(*) from t2 left join t4 using (companynr) where companynr is not null;
1372count(*)
13731200
1374explain select companynr,companyname from t2 left join t4 using (companynr) where companynr is null;
1375id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
13761	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
1377explain select companynr,companyname from t4 left join t2 using (companynr) where companynr is null;
1378id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
13791	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
1380delete from t2 where fld1=999999;
1381explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr > 0;
1382id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
13831	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	1199	Using where
13841	SIMPLE	t4	eq_ref	PRIMARY	PRIMARY	1	test.t2.companynr	1	Using join buffer (Batched Key Access)
1385explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr > 0 or t2.companynr < 0;
1386id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
13871	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	1199	Using where
13881	SIMPLE	t4	eq_ref	PRIMARY	PRIMARY	1	test.t2.companynr	1	Using join buffer (Batched Key Access)
1389explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr > 0 and t4.companynr > 0;
1390id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
13911	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	1199	Using where
13921	SIMPLE	t4	eq_ref	PRIMARY	PRIMARY	1	test.t2.companynr	1	Using join buffer (Batched Key Access)
1393explain select companynr,companyname from t4 left join t2 using (companynr) where companynr > 0;
1394id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
13951	SIMPLE	t4	range	PRIMARY	PRIMARY	1	NULL	11	Using index condition; Using MRR
13961	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	1199	Using where
1397explain select companynr,companyname from t4 left join t2 using (companynr) where companynr > 0 or companynr < 0;
1398id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
13991	SIMPLE	t4	range	PRIMARY	PRIMARY	1	NULL	12	Using index condition; Using MRR
14001	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	1199	Using where
1401explain select companynr,companyname from t4 left join t2 using (companynr) where companynr > 0 and companynr > 0;
1402id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
14031	SIMPLE	t4	range	PRIMARY	PRIMARY	1	NULL	11	Using index condition; Using MRR
14041	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	1199	Using where
1405explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr > 0 or t2.companynr is null;
1406id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
14071	SIMPLE	t4	ALL	NULL	NULL	NULL	NULL	12	NULL
14081	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	1199	Using where
1409explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr > 0 or t2.companynr < 0 or t4.companynr > 0;
1410id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
14111	SIMPLE	t4	ALL	PRIMARY	NULL	NULL	NULL	12	NULL
14121	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	1199	Using where
1413explain select t2.companynr,companyname from t4 left join t2 using (companynr) where ifnull(t2.companynr,1)>0;
1414id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
14151	SIMPLE	t4	ALL	NULL	NULL	NULL	NULL	12	NULL
14161	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	1199	Using where
1417explain select companynr,companyname from t4 left join t2 using (companynr) where companynr > 0 or companynr is null;
1418id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
14191	SIMPLE	t4	range	PRIMARY	PRIMARY	1	NULL	11	Using index condition; Using MRR
14201	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	1199	Using where
1421explain select companynr,companyname from t4 left join t2 using (companynr) where companynr > 0 or companynr < 0 or companynr > 0;
1422id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
14231	SIMPLE	t4	range	PRIMARY	PRIMARY	1	NULL	12	Using index condition; Using MRR
14241	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	1199	Using where
1425explain select companynr,companyname from t4 left join t2 using (companynr) where ifnull(companynr,1)>0;
1426id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
14271	SIMPLE	t4	ALL	NULL	NULL	NULL	NULL	12	Using where
14281	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	1199	Using where
1429select distinct t2.companynr,t4.companynr from t2,t4 where t2.companynr=t4.companynr+1;
1430companynr	companynr
143137	36
143241	40
1433explain select distinct t2.companynr,t4.companynr from t2,t4 where t2.companynr=t4.companynr+1;
1434id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
14351	SIMPLE	t4	index	NULL	PRIMARY	1	NULL	12	Using index; Using temporary
14361	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	1199	Using where
1437select t2.fld1,t2.companynr,fld3,period from t3,t2 where t2.fld1 = 38208 and t2.fld1=t3.t2nr and period = 1008 or t2.fld1 = 38008 and t2.fld1 =t3.t2nr and period = 1008;
1438fld1	companynr	fld3	period
1439038008	37	reporters	1008
1440038208	37	Selfridge	1008
1441select t2.fld1,t2.companynr,fld3,period from t3,t2 where (t2.fld1 = 38208 or t2.fld1 = 38008) and t2.fld1=t3.t2nr and period>=1008 and period<=1009;
1442fld1	companynr	fld3	period
1443038008	37	reporters	1008
1444038208	37	Selfridge	1008
1445select t2.fld1,t2.companynr,fld3,period from t3,t2 where (t3.t2nr = 38208 or t3.t2nr = 38008) and t2.fld1=t3.t2nr and period>=1008 and period<=1009;
1446fld1	companynr	fld3	period
1447038008	37	reporters	1008
1448038208	37	Selfridge	1008
1449select period from t1 where (((period > 0) or period < 10000 or (period = 1900)) and (period=1900 and period <= 1901) or (period=1903 and (period=1903)) and period>=1902) or ((period=1904 or period=1905) or (period=1906 or period>1907)) or (period=1908 and period = 1909);
1450period
14519410
1452select period from t1 where ((period > 0 and period < 1) or (((period > 0 and period < 100) and (period > 10)) or (period > 10)) or (period > 0 and (period > 5 or period > 6)));
1453period
14549410
1455select a.fld1 from t2 as a,t2 b where ((a.fld1 = 250501 and a.fld1=b.fld1) or a.fld1=250502 or a.fld1=250503 or (a.fld1=250505 and a.fld1<=b.fld1 and b.fld1>=a.fld1)) and a.fld1=b.fld1;
1456fld1
1457250501
1458250502
1459250503
1460250505
1461select fld1 from t2 where fld1 in (250502,98005,98006,250503,250605,250606) and fld1 >=250502 and fld1 not in (250605,250606);
1462fld1
1463250502
1464250503
1465select fld1 from t2 where fld1 between 250502 and 250504;
1466fld1
1467250502
1468250503
1469250504
1470select fld3 from t2 where (((fld3 like "_%L%" ) or (fld3 like "%ok%")) and ( fld3 like "L%" or fld3 like "G%")) and fld3 like "L%" ;
1471fld3
1472label
1473labeled
1474labeled
1475landslide
1476laterally
1477leaflet
1478lewdly
1479Lillian
1480luckily
1481select count(*) from t1;
1482count(*)
14831
1484select companynr,count(*),sum(fld1) from t2 group by companynr;
1485companynr	count(*)	sum(fld1)
148600	82	10355753
148729	95	14473298
148834	70	17788966
148936	215	22786296
149037	588	83602098
149140	37	6618386
149241	52	12816335
149350	11	1595438
149453	4	793210
149558	23	2254293
149665	10	2284055
149768	12	3097288
1498select companynr,count(*) from t2 group by companynr order by companynr desc limit 5;
1499companynr	count(*)
150068	12
150165	10
150258	23
150353	4
150450	11
1505select count(*),min(fld4),max(fld4),sum(fld1),avg(fld1),std(fld1),variance(fld1) from t2 where companynr = 34 and fld4<>"";
1506count(*)	min(fld4)	max(fld4)	sum(fld1)	avg(fld1)	std(fld1)	variance(fld1)
150770	absentee	vest	17788966	254128.0857	3272.5939722090234	10709871.306938833
1508explain extended select count(*),min(fld4),max(fld4),sum(fld1),avg(fld1),std(fld1),variance(fld1) from t2 where companynr = 34 and fld4<>"";
1509id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
15101	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	1199	100.00	Using where
1511Warnings:
1512Note	1003	/* select#1 */ select count(0) AS `count(*)`,min(`test`.`t2`.`fld4`) AS `min(fld4)`,max(`test`.`t2`.`fld4`) AS `max(fld4)`,sum(`test`.`t2`.`fld1`) AS `sum(fld1)`,avg(`test`.`t2`.`fld1`) AS `avg(fld1)`,std(`test`.`t2`.`fld1`) AS `std(fld1)`,variance(`test`.`t2`.`fld1`) AS `variance(fld1)` from `test`.`t2` where ((`test`.`t2`.`companynr` = 34) and (`test`.`t2`.`fld4` <> ''))
1513select companynr,count(*),min(fld4),max(fld4),sum(fld1),avg(fld1),std(fld1),variance(fld1) from t2 group by companynr limit 3;
1514companynr	count(*)	min(fld4)	max(fld4)	sum(fld1)	avg(fld1)	std(fld1)	variance(fld1)
151500	82	Anthony	windmills	10355753	126289.6707	115550.97568479746	13352027981.708656
151629	95	abut	wetness	14473298	152350.5053	8368.547956641249	70032594.90260443
151734	70	absentee	vest	17788966	254128.0857	3272.5939722090234	10709871.306938833
1518select companynr,t2nr,count(price),sum(price),min(price),max(price),avg(price) from t3 where companynr = 37 group by companynr,t2nr limit 10;
1519companynr	t2nr	count(price)	sum(price)	min(price)	max(price)	avg(price)
152037	1	1	5987435	5987435	5987435	5987435.0000
152137	2	1	28357832	28357832	28357832	28357832.0000
152237	3	1	39654943	39654943	39654943	39654943.0000
152337	11	1	5987435	5987435	5987435	5987435.0000
152437	12	1	28357832	28357832	28357832	28357832.0000
152537	13	1	39654943	39654943	39654943	39654943.0000
152637	21	1	5987435	5987435	5987435	5987435.0000
152737	22	1	28357832	28357832	28357832	28357832.0000
152837	23	1	39654943	39654943	39654943	39654943.0000
152937	31	1	5987435	5987435	5987435	5987435.0000
1530select /*! SQL_SMALL_RESULT */ companynr,t2nr,count(price),sum(price),min(price),max(price),avg(price) from t3 where companynr = 37 group by companynr,t2nr limit 10;
1531companynr	t2nr	count(price)	sum(price)	min(price)	max(price)	avg(price)
153237	1	1	5987435	5987435	5987435	5987435.0000
153337	2	1	28357832	28357832	28357832	28357832.0000
153437	3	1	39654943	39654943	39654943	39654943.0000
153537	11	1	5987435	5987435	5987435	5987435.0000
153637	12	1	28357832	28357832	28357832	28357832.0000
153737	13	1	39654943	39654943	39654943	39654943.0000
153837	21	1	5987435	5987435	5987435	5987435.0000
153937	22	1	28357832	28357832	28357832	28357832.0000
154037	23	1	39654943	39654943	39654943	39654943.0000
154137	31	1	5987435	5987435	5987435	5987435.0000
1542select companynr,count(price),sum(price),min(price),max(price),avg(price) from t3 group by companynr ;
1543companynr	count(price)	sum(price)	min(price)	max(price)	avg(price)
154437	12543	309394878010	5987435	39654943	24666736.6667
154578	8362	414611089292	726498	98439034	49582766.0000
1546101	4181	3489454238	834598	834598	834598.0000
1547154	4181	4112197254950	983543950	983543950	983543950.0000
1548311	4181	979599938	234298	234298	234298.0000
1549447	4181	9929180954	2374834	2374834	2374834.0000
1550512	4181	3288532102	786542	786542	786542.0000
1551select distinct mod(companynr,10) from t4 group by companynr;
1552mod(companynr,10)
15530
15549
15554
15566
15577
15581
15593
15608
15615
1562select distinct 1 from t4 group by companynr;
15631
15641
1565select count(distinct fld1) from t2;
1566count(distinct fld1)
15671199
1568select companynr,count(distinct fld1) from t2 group by companynr;
1569companynr	count(distinct fld1)
157000	82
157129	95
157234	70
157336	215
157437	588
157540	37
157641	52
157750	11
157853	4
157958	23
158065	10
158168	12
1582select companynr,count(*) from t2 group by companynr;
1583companynr	count(*)
158400	82
158529	95
158634	70
158736	215
158837	588
158940	37
159041	52
159150	11
159253	4
159358	23
159465	10
159568	12
1596select companynr,count(distinct concat(fld1,repeat(65,1000))) from t2 group by companynr;
1597companynr	count(distinct concat(fld1,repeat(65,1000)))
159800	82
159929	95
160034	70
160136	215
160237	588
160340	37
160441	52
160550	11
160653	4
160758	23
160865	10
160968	12
1610select companynr,count(distinct concat(fld1,repeat(65,200))) from t2 group by companynr;
1611companynr	count(distinct concat(fld1,repeat(65,200)))
161200	82
161329	95
161434	70
161536	215
161637	588
161740	37
161841	52
161950	11
162053	4
162158	23
162265	10
162368	12
1624select companynr,count(distinct floor(fld1/100)) from t2 group by companynr;
1625companynr	count(distinct floor(fld1/100))
162600	47
162729	35
162834	14
162936	69
163037	108
163140	16
163241	11
163350	9
163453	1
163558	1
163665	1
163768	1
1638select companynr,count(distinct concat(repeat(65,1000),floor(fld1/100))) from t2 group by companynr;
1639companynr	count(distinct concat(repeat(65,1000),floor(fld1/100)))
164000	47
164129	35
164234	14
164336	69
164437	108
164540	16
164641	11
164750	9
164853	1
164958	1
165065	1
165168	1
1652select sum(fld1),fld3 from t2 where fld3="Romans" group by fld1 limit 10;
1653sum(fld1)	fld3
165411402	Romans
1655select name,count(*) from t3 where name='cloakroom' group by name;
1656name	count(*)
1657cloakroom	4181
1658select name,count(*) from t3 where name='cloakroom' and price>10 group by name;
1659name	count(*)
1660cloakroom	4181
1661select count(*) from t3 where name='cloakroom' and price2=823742;
1662count(*)
16634181
1664select name,count(*) from t3 where name='cloakroom' and price2=823742 group by name;
1665name	count(*)
1666cloakroom	4181
1667select name,count(*) from t3 where name >= "extramarital" and price <= 39654943 group by name;
1668name	count(*)
1669extramarital	4181
1670gazer	4181
1671gems	4181
1672Iranizes	4181
1673spates	4181
1674tucked	4181
1675violinist	4181
1676select t2.fld3,count(*) from t2,t3 where t2.fld1=158402 and t3.name=t2.fld3 group by t3.name;
1677fld3	count(*)
1678spates	4181
1679select companynr|0,companyname from t4 group by 1;
1680companynr|0	companyname
16810	Unknown
168229	company 1
168334	company 2
168436	company 3
168537	company 4
168640	company 5
168741	company 6
168850	company 11
168953	company 7
169058	company 8
169165	company 9
169268	company 10
1693select t2.companynr,companyname,count(*) from t2,t4 where t2.companynr=t4.companynr group by t2.companynr order by companyname;
1694companynr	companyname	count(*)
169529	company 1	95
169668	company 10	12
169750	company 11	11
169834	company 2	70
169936	company 3	215
170037	company 4	588
170140	company 5	37
170241	company 6	52
170353	company 7	4
170458	company 8	23
170565	company 9	10
170600	Unknown	82
1707select t2.fld1,count(*) from t2,t3 where t2.fld1=158402 and t3.name=t2.fld3 group by t3.name;
1708fld1	count(*)
1709158402	4181
1710select sum(Period)/count(*) from t1;
1711sum(Period)/count(*)
17129410.0000
1713select companynr,count(price) as "count",sum(price) as "sum" ,abs(sum(price)/count(price)-avg(price)) as "diff",(0+count(price))*companynr as func from t3 group by companynr;
1714companynr	count	sum	diff	func
171537	12543	309394878010	0.0000	464091
171678	8362	414611089292	0.0000	652236
1717101	4181	3489454238	0.0000	422281
1718154	4181	4112197254950	0.0000	643874
1719311	4181	979599938	0.0000	1300291
1720447	4181	9929180954	0.0000	1868907
1721512	4181	3288532102	0.0000	2140672
1722select companynr,sum(price)/count(price) as avg from t3 group by companynr having avg > 70000000 order by avg;
1723companynr	avg
1724154	983543950.0000
1725select companynr,count(*) from t2 group by companynr order by 2 desc;
1726companynr	count(*)
172737	588
172836	215
172929	95
173000	82
173134	70
173241	52
173340	37
173458	23
173568	12
173650	11
173765	10
173853	4
1739select companynr,count(*) from t2 where companynr > 40 group by companynr order by 2 desc;
1740companynr	count(*)
174141	52
174258	23
174368	12
174450	11
174565	10
174653	4
1747select t2.fld4,t2.fld1,count(price),sum(price),min(price),max(price),avg(price) from t3,t2 where t3.companynr = 37 and t2.fld1 = t3.t2nr group by fld1,t2.fld4;
1748fld4	fld1	count(price)	sum(price)	min(price)	max(price)	avg(price)
1749teethe	000001	1	5987435	5987435	5987435	5987435.0000
1750dreaded	011401	1	5987435	5987435	5987435	5987435.0000
1751scholastics	011402	1	28357832	28357832	28357832	28357832.0000
1752audiology	011403	1	39654943	39654943	39654943	39654943.0000
1753wallet	011501	1	5987435	5987435	5987435	5987435.0000
1754parters	011701	1	5987435	5987435	5987435	5987435.0000
1755eschew	011702	1	28357832	28357832	28357832	28357832.0000
1756quitter	011703	1	39654943	39654943	39654943	39654943.0000
1757neat	012001	1	5987435	5987435	5987435	5987435.0000
1758Steinberg	012003	1	39654943	39654943	39654943	39654943.0000
1759balled	012301	1	5987435	5987435	5987435	5987435.0000
1760persist	012302	1	28357832	28357832	28357832	28357832.0000
1761attainments	012303	1	39654943	39654943	39654943	39654943.0000
1762capably	012501	1	5987435	5987435	5987435	5987435.0000
1763impulsive	012602	1	28357832	28357832	28357832	28357832.0000
1764starlet	012603	1	39654943	39654943	39654943	39654943.0000
1765featherweight	012701	1	5987435	5987435	5987435	5987435.0000
1766pessimist	012702	1	28357832	28357832	28357832	28357832.0000
1767daughter	012703	1	39654943	39654943	39654943	39654943.0000
1768lawgiver	013601	1	5987435	5987435	5987435	5987435.0000
1769stated	013602	1	28357832	28357832	28357832	28357832.0000
1770readable	013603	1	39654943	39654943	39654943	39654943.0000
1771testicle	013801	1	5987435	5987435	5987435	5987435.0000
1772Parsifal	013802	1	28357832	28357832	28357832	28357832.0000
1773leavings	013803	1	39654943	39654943	39654943	39654943.0000
1774squeaking	013901	1	5987435	5987435	5987435	5987435.0000
1775contrasted	016001	1	5987435	5987435	5987435	5987435.0000
1776leftover	016201	1	5987435	5987435	5987435	5987435.0000
1777whiteners	016202	1	28357832	28357832	28357832	28357832.0000
1778erases	016301	1	5987435	5987435	5987435	5987435.0000
1779Punjab	016302	1	28357832	28357832	28357832	28357832.0000
1780Merritt	016303	1	39654943	39654943	39654943	39654943.0000
1781sweetish	018001	1	5987435	5987435	5987435	5987435.0000
1782dogging	018002	1	28357832	28357832	28357832	28357832.0000
1783scornfully	018003	1	39654943	39654943	39654943	39654943.0000
1784fetters	018012	1	28357832	28357832	28357832	28357832.0000
1785bivalves	018013	1	39654943	39654943	39654943	39654943.0000
1786skulking	018021	1	5987435	5987435	5987435	5987435.0000
1787flint	018022	1	28357832	28357832	28357832	28357832.0000
1788flopping	018023	1	39654943	39654943	39654943	39654943.0000
1789Judas	018032	1	28357832	28357832	28357832	28357832.0000
1790vacuuming	018033	1	39654943	39654943	39654943	39654943.0000
1791medical	018041	1	5987435	5987435	5987435	5987435.0000
1792bloodbath	018042	1	28357832	28357832	28357832	28357832.0000
1793subschema	018043	1	39654943	39654943	39654943	39654943.0000
1794interdependent	018051	1	5987435	5987435	5987435	5987435.0000
1795Graves	018052	1	28357832	28357832	28357832	28357832.0000
1796neonatal	018053	1	39654943	39654943	39654943	39654943.0000
1797sorters	018061	1	5987435	5987435	5987435	5987435.0000
1798epistle	018062	1	28357832	28357832	28357832	28357832.0000
1799Conley	018101	1	5987435	5987435	5987435	5987435.0000
1800lectured	018102	1	28357832	28357832	28357832	28357832.0000
1801Abraham	018103	1	39654943	39654943	39654943	39654943.0000
1802cage	018201	1	5987435	5987435	5987435	5987435.0000
1803hushes	018202	1	28357832	28357832	28357832	28357832.0000
1804Simla	018402	1	28357832	28357832	28357832	28357832.0000
1805reporters	018403	1	39654943	39654943	39654943	39654943.0000
1806coexist	018601	1	5987435	5987435	5987435	5987435.0000
1807Beebe	018602	1	28357832	28357832	28357832	28357832.0000
1808Taoism	018603	1	39654943	39654943	39654943	39654943.0000
1809Connally	018801	1	5987435	5987435	5987435	5987435.0000
1810fetched	018802	1	28357832	28357832	28357832	28357832.0000
1811checkpoints	018803	1	39654943	39654943	39654943	39654943.0000
1812gritty	018811	1	5987435	5987435	5987435	5987435.0000
1813firearm	018812	1	28357832	28357832	28357832	28357832.0000
1814minima	019101	1	5987435	5987435	5987435	5987435.0000
1815Selfridge	019102	1	28357832	28357832	28357832	28357832.0000
1816disable	019103	1	39654943	39654943	39654943	39654943.0000
1817witchcraft	019201	1	5987435	5987435	5987435	5987435.0000
1818betroth	030501	1	5987435	5987435	5987435	5987435.0000
1819Manhattanize	030502	1	28357832	28357832	28357832	28357832.0000
1820imprint	030503	1	39654943	39654943	39654943	39654943.0000
1821swelling	031901	1	5987435	5987435	5987435	5987435.0000
1822interrelationships	036001	1	5987435	5987435	5987435	5987435.0000
1823riser	036002	1	28357832	28357832	28357832	28357832.0000
1824bee	038001	1	5987435	5987435	5987435	5987435.0000
1825kanji	038002	1	28357832	28357832	28357832	28357832.0000
1826dental	038003	1	39654943	39654943	39654943	39654943.0000
1827railway	038011	1	5987435	5987435	5987435	5987435.0000
1828validate	038012	1	28357832	28357832	28357832	28357832.0000
1829normalizes	038013	1	39654943	39654943	39654943	39654943.0000
1830Kline	038101	1	5987435	5987435	5987435	5987435.0000
1831Anatole	038102	1	28357832	28357832	28357832	28357832.0000
1832partridges	038103	1	39654943	39654943	39654943	39654943.0000
1833recruited	038201	1	5987435	5987435	5987435	5987435.0000
1834dimensions	038202	1	28357832	28357832	28357832	28357832.0000
1835Chicana	038203	1	39654943	39654943	39654943	39654943.0000
1836select t3.companynr,fld3,sum(price) from t3,t2 where t2.fld1 = t3.t2nr and t3.companynr = 512 group by companynr,fld3;
1837companynr	fld3	sum(price)
1838512	boat	786542
1839512	capably	786542
1840512	cupboard	786542
1841512	decliner	786542
1842512	descendants	786542
1843512	dopers	786542
1844512	erases	786542
1845512	Micronesia	786542
1846512	Miles	786542
1847512	skies	786542
1848select t2.companynr,count(*),min(fld3),max(fld3),sum(price),avg(price) from t2,t3 where t3.companynr >= 30 and t3.companynr <= 58 and t3.t2nr = t2.fld1 and 1+1=2 group by t2.companynr;
1849companynr	count(*)	min(fld3)	max(fld3)	sum(price)	avg(price)
185000	1	Omaha	Omaha	5987435	5987435.0000
185136	1	dubbed	dubbed	28357832	28357832.0000
185237	83	Abraham	Wotan	1908978016	22999735.1325
185350	2	scribbled	tapestry	68012775	34006387.5000
1854select t3.companynr+0,t3.t2nr,fld3,sum(price) from t3,t2 where t2.fld1 = t3.t2nr and t3.companynr = 37 group by 1,t3.t2nr,fld3,fld3,fld3,fld3,fld3 order by fld1;
1855t3.companynr+0	t2nr	fld3	sum(price)
185637	1	Omaha	5987435
185737	11401	breaking	5987435
185837	11402	Romans	28357832
185937	11403	intercepted	39654943
186037	11501	bewilderingly	5987435
186137	11701	astound	5987435
186237	11702	admonishing	28357832
186337	11703	sumac	39654943
186437	12001	flanking	5987435
186537	12003	combed	39654943
186637	12301	Eulerian	5987435
186737	12302	dubbed	28357832
186837	12303	Kane	39654943
186937	12501	annihilates	5987435
187037	12602	Wotan	28357832
187137	12603	snatching	39654943
187237	12701	grazing	5987435
187337	12702	Baird	28357832
187437	12703	celery	39654943
187537	13601	handgun	5987435
187637	13602	foldout	28357832
187737	13603	mystic	39654943
187837	13801	intelligibility	5987435
187937	13802	Augustine	28357832
188037	13803	teethe	39654943
188137	13901	scholastics	5987435
188237	16001	audiology	5987435
188337	16201	wallet	5987435
188437	16202	parters	28357832
188537	16301	eschew	5987435
188637	16302	quitter	28357832
188737	16303	neat	39654943
188837	18001	jarring	5987435
188937	18002	tinily	28357832
189037	18003	balled	39654943
189137	18012	impulsive	28357832
189237	18013	starlet	39654943
189337	18021	lawgiver	5987435
189437	18022	stated	28357832
189537	18023	readable	39654943
189637	18032	testicle	28357832
189737	18033	Parsifal	39654943
189837	18041	Punjab	5987435
189937	18042	Merritt	28357832
190037	18043	Quixotism	39654943
190137	18051	sureties	5987435
190237	18052	puddings	28357832
190337	18053	tapestry	39654943
190437	18061	trimmings	5987435
190537	18062	humility	28357832
190637	18101	tragedies	5987435
190737	18102	skulking	28357832
190837	18103	flint	39654943
190937	18201	relaxing	5987435
191037	18202	offload	28357832
191137	18402	suites	28357832
191237	18403	lists	39654943
191337	18601	vacuuming	5987435
191437	18602	dentally	28357832
191537	18603	humanness	39654943
191637	18801	inch	5987435
191737	18802	Weissmuller	28357832
191837	18803	irresponsibly	39654943
191937	18811	repetitions	5987435
192037	18812	Antares	28357832
192137	19101	ventilate	5987435
192237	19102	pityingly	28357832
192337	19103	interdependent	39654943
192437	19201	Graves	5987435
192537	30501	neonatal	5987435
192637	30502	scribbled	28357832
192737	30503	chafe	39654943
192837	31901	realtor	5987435
192937	36001	elite	5987435
193037	36002	funereal	28357832
193137	38001	Conley	5987435
193237	38002	lectured	28357832
193337	38003	Abraham	39654943
193437	38011	groupings	5987435
193537	38012	dissociate	28357832
193637	38013	coexist	39654943
193737	38101	rusting	5987435
193837	38102	galling	28357832
193937	38103	obliterates	39654943
194037	38201	resumes	5987435
194137	38202	analyzable	28357832
194237	38203	terminator	39654943
1943select sum(price) from t3,t2 where t2.fld1 = t3.t2nr and t3.companynr = 512 and t3.t2nr = 38008 and t2.fld1 = 38008 or t2.fld1= t3.t2nr and t3.t2nr = 38008 and t2.fld1 = 38008;
1944sum(price)
1945234298
1946select t2.fld1,sum(price) from t3,t2 where t2.fld1 = t3.t2nr and t3.companynr = 512 and t3.t2nr = 38008 and t2.fld1 = 38008 or t2.fld1 = t3.t2nr and t3.t2nr = 38008 and t2.fld1 = 38008 or t3.t2nr = t2.fld1 and t2.fld1 = 38008 group by t2.fld1;
1947fld1	sum(price)
1948038008	234298
1949explain select fld3 from t2 where 1>2 or 2>3;
1950id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
19511	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
1952explain select fld3 from t2 where fld1=fld1;
1953id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
19541	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	1199	NULL
1955select companynr,fld1 from t2 HAVING fld1=250501 or fld1=250502;
1956companynr	fld1
195734	250501
195834	250502
1959select companynr,fld1 from t2 WHERE fld1>=250501 HAVING fld1<=250502;
1960companynr	fld1
196134	250501
196234	250502
1963select companynr,count(*) as count,sum(fld1) as sum from t2 group by companynr having count > 40 and sum/count >= 120000;
1964companynr	count	sum
196500	82	10355753
196629	95	14473298
196734	70	17788966
196837	588	83602098
196941	52	12816335
1970select companynr from t2 group by companynr having count(*) > 40 and sum(fld1)/count(*) >= 120000 ;
1971companynr
197200
197329
197434
197537
197641
1977select t2.companynr,companyname,count(*) from t2,t4 where t2.companynr=t4.companynr group by companyname having t2.companynr >= 40;
1978companynr	companyname	count(*)
197968	company 10	12
198050	company 11	11
198140	company 5	37
198241	company 6	52
198353	company 7	4
198458	company 8	23
198565	company 9	10
1986select count(*) from t2;
1987count(*)
19881199
1989select count(*) from t2 where fld1 < 098024;
1990count(*)
1991387
1992select min(fld1) from t2 where fld1>= 098024;
1993min(fld1)
199498024
1995select max(fld1) from t2 where fld1>= 098024;
1996max(fld1)
19971232609
1998select count(*) from t3 where price2=76234234;
1999count(*)
20004181
2001select count(*) from t3 where companynr=512 and price2=76234234;
2002count(*)
20034181
2004explain select min(fld1),max(fld1),count(*) from t2;
2005id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
20061	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Select tables optimized away
2007select min(fld1),max(fld1),count(*) from t2;
2008min(fld1)	max(fld1)	count(*)
20090	1232609	1199
2010select min(t2nr),max(t2nr) from t3 where t2nr=2115 and price2=823742;
2011min(t2nr)	max(t2nr)
20122115	2115
2013select count(*),min(t2nr),max(t2nr) from t3 where name='spates' and companynr=78;
2014count(*)	min(t2nr)	max(t2nr)
20154181	4	41804
2016select t2nr,count(*) from t3 where name='gems' group by t2nr limit 20;
2017t2nr	count(*)
20189	1
201919	1
202029	1
202139	1
202249	1
202359	1
202469	1
202579	1
202689	1
202799	1
2028109	1
2029119	1
2030129	1
2031139	1
2032149	1
2033159	1
2034169	1
2035179	1
2036189	1
2037199	1
2038select max(t2nr) from t3 where price=983543950;
2039max(t2nr)
204041807
2041select t1.period from t3 = t1 limit 1;
2042period
20431001
2044select t1.period from t1 as t1 limit 1;
2045period
20469410
2047select t1.period as "Nuvarande period" from t1 as t1 limit 1;
2048Nuvarande period
20499410
2050select period as ok_period from t1 limit 1;
2051ok_period
20529410
2053select period as ok_period from t1 group by ok_period limit 1;
2054ok_period
20559410
2056select 1+1 as summa from t1 group by summa limit 1;
2057summa
20582
2059select period as "Nuvarande period" from t1 group by "Nuvarande period" limit 1;
2060Nuvarande period
20619410
2062show tables;
2063Tables_in_test
2064t1
2065t2
2066t3
2067t4
2068show tables from test like "s%";
2069Tables_in_test (s%)
2070show tables from test like "t?";
2071Tables_in_test (t?)
2072show full columns from t2;
2073Field	Type	Collation	Null	Key	Default	Extra	Privileges	Comment
2074auto	int(11)	NULL	NO	PRI	NULL	auto_increment	#
2075fld1	int(6) unsigned zerofill	NULL	NO	UNI	000000		#
2076companynr	tinyint(2) unsigned zerofill	NULL	NO		00		#
2077fld3	char(30)	latin1_swedish_ci	NO	MUL			#
2078fld4	char(35)	latin1_swedish_ci	NO				#
2079fld5	char(35)	latin1_swedish_ci	NO				#
2080fld6	char(4)	latin1_swedish_ci	NO				#
2081show full columns from t2 from test like 'f%';
2082Field	Type	Collation	Null	Key	Default	Extra	Privileges	Comment
2083fld1	int(6) unsigned zerofill	NULL	NO	UNI	000000		#
2084fld3	char(30)	latin1_swedish_ci	NO	MUL			#
2085fld4	char(35)	latin1_swedish_ci	NO				#
2086fld5	char(35)	latin1_swedish_ci	NO				#
2087fld6	char(4)	latin1_swedish_ci	NO				#
2088show full columns from t2 from test like 's%';
2089Field	Type	Collation	Null	Key	Default	Extra	Privileges	Comment
2090show keys from t2;
2091Table	Non_unique	Key_name	Seq_in_index	Column_name	Collation	Cardinality	Sub_part	Packed	Null	Index_type	Comment	Index_comment
2092t2	0	PRIMARY	1	auto	A	1199	NULL	NULL		BTREE
2093t2	0	fld1	1	fld1	A	1199	NULL	NULL		BTREE
2094t2	1	fld3	1	fld3	A	NULL	NULL	NULL		BTREE
2095drop table t4, t3, t2, t1;
2096DO 1;
2097DO benchmark(100,1+1),1,1;
2098do default;
2099ERROR 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
2100do foobar;
2101ERROR 42S22: Unknown column 'foobar' in 'field list'
2102CREATE TABLE t1 (
2103id mediumint(8) unsigned NOT NULL auto_increment,
2104pseudo varchar(35) NOT NULL default '',
2105PRIMARY KEY  (id),
2106UNIQUE KEY pseudo (pseudo)
2107);
2108INSERT INTO t1 (pseudo) VALUES ('test');
2109INSERT INTO t1 (pseudo) VALUES ('test1');
2110SELECT 1 as rnd1 from t1 where rand() > 2;
2111rnd1
2112DROP TABLE t1;
2113CREATE TABLE t1 (gvid int(10) unsigned default NULL,  hmid int(10) unsigned default NULL,  volid int(10) unsigned default NULL,  mmid int(10) unsigned default NULL,  hdid int(10) unsigned default NULL,  fsid int(10) unsigned default NULL,  ctid int(10) unsigned default NULL,  dtid int(10) unsigned default NULL,  cost int(10) unsigned default NULL,  performance int(10) unsigned default NULL,  serialnumber bigint(20) unsigned default NULL,  monitored tinyint(3) unsigned default '1',  removed tinyint(3) unsigned default '0',  target tinyint(3) unsigned default '0',  dt_modified timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,  name varchar(255) binary default NULL,  description varchar(255) default NULL,  UNIQUE KEY hmid (hmid,volid)) ENGINE=MyISAM;
2114INSERT INTO t1 VALUES (200001,2,1,1,100,1,1,1,0,0,0,1,0,1,20020425060057,'\\\\ARKIVIO-TESTPDC\\E$',''),(200002,2,2,1,101,1,1,1,0,0,0,1,0,1,20020425060057,'\\\\ARKIVIO-TESTPDC\\C$',''),(200003,1,3,2,NULL,NULL,NULL,NULL,NULL,NULL,NULL,1,0,1,20020425060427,'c:',NULL);
2115CREATE TABLE t2 (  hmid int(10) unsigned default NULL,  volid int(10) unsigned default NULL,  sampletid smallint(5) unsigned default NULL,  sampletime datetime default NULL,  samplevalue bigint(20) unsigned default NULL,  KEY idx1 (hmid,volid,sampletid,sampletime)) ENGINE=MyISAM;
2116INSERT INTO t2 VALUES (1,3,10,'2002-06-01 08:00:00',35),(1,3,1010,'2002-06-01 12:00:01',35);
2117SELECT a.gvid, (SUM(CASE b.sampletid WHEN 140 THEN b.samplevalue ELSE 0 END)) as the_success,(SUM(CASE b.sampletid WHEN 141 THEN b.samplevalue ELSE 0 END)) as the_fail,(SUM(CASE b.sampletid WHEN 142 THEN b.samplevalue ELSE 0 END)) as the_size,(SUM(CASE b.sampletid WHEN 143 THEN b.samplevalue ELSE 0 END)) as the_time FROM t1 a, t2 b WHERE a.hmid = b.hmid AND a.volid = b.volid AND b.sampletime >= 'wrong-date-value' AND b.sampletime < 'wrong-date-value' AND b.sampletid IN (140, 141, 142, 143) GROUP BY a.gvid;
2118gvid	the_success	the_fail	the_size	the_time
2119Warnings:
2120Warning	1292	Incorrect datetime value: 'wrong-date-value' for column 'sampletime' at row 1
2121Warning	1292	Incorrect datetime value: 'wrong-date-value' for column 'sampletime' at row 1
2122SELECT a.gvid, (SUM(CASE b.sampletid WHEN 140 THEN b.samplevalue ELSE 0 END)) as the_success,(SUM(CASE b.sampletid WHEN 141 THEN b.samplevalue ELSE 0 END)) as the_fail,(SUM(CASE b.sampletid WHEN 142 THEN b.samplevalue ELSE 0 END)) as the_size,(SUM(CASE b.sampletid WHEN 143 THEN b.samplevalue ELSE 0 END)) as the_time FROM t1 a, t2 b WHERE a.hmid = b.hmid AND a.volid = b.volid AND b.sampletime >= NULL AND b.sampletime < NULL AND b.sampletid IN (140, 141, 142, 143) GROUP BY a.gvid;
2123gvid	the_success	the_fail	the_size	the_time
2124DROP TABLE t1,t2;
2125create table  t1 (  A_Id bigint(20) NOT NULL default '0',  A_UpdateBy char(10) NOT NULL default '',  A_UpdateDate bigint(20) NOT NULL default '0',  A_UpdateSerial int(11) NOT NULL default '0',  other_types bigint(20) NOT NULL default '0',  wss_type bigint(20) NOT NULL default '0');
2126INSERT INTO t1 VALUES (102935998719055004,'brade',1029359987,2,102935229116544068,102935229216544093);
2127select wss_type from t1 where wss_type ='102935229216544106';
2128wss_type
2129select wss_type from t1 where wss_type ='102935229216544105';
2130wss_type
2131select wss_type from t1 where wss_type ='102935229216544104';
2132wss_type
2133select wss_type from t1 where wss_type ='102935229216544093';
2134wss_type
2135102935229216544093
2136select wss_type from t1 where wss_type =102935229216544093;
2137wss_type
2138102935229216544093
2139drop table t1;
2140select 1+2,"aaaa",3.13*2.0 into @a,@b,@c;
2141select @a;
2142@a
21433
2144select @b;
2145@b
2146aaaa
2147select @c;
2148@c
21496.260
2150create table t1 (a int not null auto_increment primary key);
2151insert into t1 values ();
2152insert into t1 values ();
2153insert into t1 values ();
2154select * from (t1 as t2 left join t1 as t3 using (a)), t1;
2155a	a
21561	1
21571	2
21581	3
21592	1
21602	2
21612	3
21623	1
21633	2
21643	3
2165select * from t1, (t1 as t2 left join t1 as t3 using (a));
2166a	a
21671	1
21681	2
21691	3
21702	1
21712	2
21722	3
21733	1
21743	2
21753	3
2176select * from (t1 as t2 left join t1 as t3 using (a)) straight_join t1;
2177a	a
21781	1
21791	2
21801	3
21812	1
21822	2
21832	3
21843	1
21853	2
21863	3
2187select * from t1 straight_join (t1 as t2 left join t1 as t3 using (a));
2188a	a
21891	1
21901	2
21911	3
21922	1
21932	2
21942	3
21953	1
21963	2
21973	3
2198select * from (t1 as t2 left join t1 as t3 using (a)) inner join t1 on t1.a>1;
2199a	a
22001	2
22012	2
22023	2
22031	3
22042	3
22053	3
2206select * from t1 inner join (t1 as t2 left join t1 as t3 using (a)) on t1.a>1;
2207a	a
22082	1
22092	2
22102	3
22113	1
22123	2
22133	3
2214select * from (t1 as t2 left join t1 as t3 using (a)) inner join t1 using ( a );
2215a
22161
22172
22183
2219select * from t1 inner join (t1 as t2 left join t1 as t3 using (a)) using ( a );
2220a
22211
22222
22233
2224select * from (t1 as t2 left join t1 as t3 using (a)) left outer join t1 on t1.a>1;
2225a	a
22261	2
22271	3
22282	2
22292	3
22303	2
22313	3
2232select * from t1 left outer join (t1 as t2 left join t1 as t3 using (a)) on t1.a>1;
2233a	a
22341	NULL
22352	1
22362	2
22372	3
22383	1
22393	2
22403	3
2241select * from (t1 as t2 left join t1 as t3 using (a)) left join t1 using ( a );
2242a
22431
22442
22453
2246select * from t1 left join (t1 as t2 left join t1 as t3 using (a)) using ( a );
2247a
22481
22492
22503
2251select * from (t1 as t2 left join t1 as t3 using (a)) natural left join t1;
2252a
22531
22542
22553
2256select * from t1 natural left join (t1 as t2 left join t1 as t3 using (a));
2257a
22581
22592
22603
2261select * from (t1 as t2 left join t1 as t3 using (a)) right join t1 on t1.a>1;
2262a	a
2263NULL	1
22641	2
22652	2
22663	2
22671	3
22682	3
22693	3
2270select * from t1 right join (t1 as t2 left join t1 as t3 using (a)) on t1.a>1;
2271a	a
22722	1
22732	2
22742	3
22753	1
22763	2
22773	3
2278select * from (t1 as t2 left join t1 as t3 using (a)) right outer join t1 using ( a );
2279a
22801
22812
22823
2283select * from t1 right outer join (t1 as t2 left join t1 as t3 using (a)) using ( a );
2284a
22851
22862
22873
2288select * from (t1 as t2 left join t1 as t3 using (a)) natural right join t1;
2289a
22901
22912
22923
2293select * from t1 natural right join (t1 as t2 left join t1 as t3 using (a));
2294a
22951
22962
22973
2298select * from t1 natural join (t1 as t2 left join t1 as t3 using (a));
2299a
23001
23012
23023
2303select * from (t1 as t2 left join t1 as t3 using (a)) natural join t1;
2304a
23051
23062
23073
2308drop table t1;
2309CREATE TABLE t1 (  aa char(2),  id int(11) NOT NULL auto_increment,  t2_id int(11) NOT NULL default '0',  PRIMARY KEY  (id),  KEY replace_id (t2_id)) ENGINE=MyISAM;
2310INSERT INTO t1 VALUES ("1",8264,2506),("2",8299,2517),("3",8301,2518),("4",8302,2519),("5",8303,2520),("6",8304,2521),("7",8305,2522);
2311CREATE TABLE t2 ( id int(11) NOT NULL auto_increment,  PRIMARY KEY  (id)) ENGINE=MyISAM;
2312INSERT INTO t2 VALUES (2517), (2518), (2519), (2520), (2521), (2522);
2313select * from t1, t2 WHERE t1.t2_id = t2.id and t1.t2_id > 0   order by t1.id   LIMIT 0, 5;
2314aa	id	t2_id	id
23152	8299	2517	2517
23163	8301	2518	2518
23174	8302	2519	2519
23185	8303	2520	2520
23196	8304	2521	2521
2320drop table t1,t2;
2321create table t1 (id1 int NOT NULL);
2322create table t2 (id2 int NOT NULL);
2323create table t3 (id3 int NOT NULL);
2324create table t4 (id4 int NOT NULL, id44 int NOT NULL, KEY (id4));
2325insert into t1 values (1);
2326insert into t1 values (2);
2327insert into t2 values (1);
2328insert into t4 values (1,1);
2329explain select * from t1 left join t2 on id1 = id2 left join t3 on id1 = id3
2330left join t4 on id3 = id4 where id2 = 1 or id4 = 1;
2331id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
23321	SIMPLE	t3	system	NULL	NULL	NULL	NULL	0	const row not found
23331	SIMPLE	t4	const	id4	NULL	NULL	NULL	1	NULL
23341	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	NULL
23351	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	1	Using where
2336select * from t1 left join t2 on id1 = id2 left join t3 on id1 = id3
2337left join t4 on id3 = id4 where id2 = 1 or id4 = 1;
2338id1	id2	id3	id4	id44
23391	1	NULL	NULL	NULL
2340drop table t1,t2,t3,t4;
2341create table t1(s varchar(10) not null);
2342create table t2(s varchar(10) not null primary key);
2343create table t3(s varchar(10) not null primary key);
2344insert into t1 values ('one\t'), ('two\t');
2345insert into t2 values ('one\r'), ('two\t');
2346insert into t3 values ('one '), ('two\t');
2347select * from t1 where s = 'one';
2348s
2349select * from t2 where s = 'one';
2350s
2351select * from t3 where s = 'one';
2352s
2353one
2354select * from t1,t2 where t1.s = t2.s;
2355s	s
2356two		two
2357select * from t2,t3 where t2.s = t3.s;
2358s	s
2359two		two
2360drop table t1, t2, t3;
2361create table t1 (a integer,  b integer, index(a), index(b));
2362create table t2 (c integer,  d integer, index(c), index(d));
2363insert into t1 values (1,2), (2,2), (3,2), (4,2);
2364insert into t2 values (1,3), (2,3), (3,4), (4,4);
2365explain select * from t1 left join t2 on a=c where d in (4);
2366id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
23671	SIMPLE	t2	ref	c,d	d	5	const	2	Using where
23681	SIMPLE	t1	ref	a	a	5	test.t2.c	2	Using join buffer (Batched Key Access)
2369select * from t1 left join t2 on a=c where d in (4);
2370a	b	c	d
23713	2	3	4
23724	2	4	4
2373explain select * from t1 left join t2 on a=c where d = 4;
2374id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
23751	SIMPLE	t2	ref	c,d	d	5	const	2	Using where
23761	SIMPLE	t1	ref	a	a	5	test.t2.c	2	Using join buffer (Batched Key Access)
2377select * from t1 left join t2 on a=c where d = 4;
2378a	b	c	d
23793	2	3	4
23804	2	4	4
2381drop table t1, t2;
2382CREATE TABLE t1 (
2383i int(11) NOT NULL default '0',
2384c char(10) NOT NULL default '',
2385PRIMARY KEY  (i),
2386UNIQUE KEY c (c)
2387) ENGINE=MyISAM;
2388INSERT INTO t1 VALUES (1,'a');
2389INSERT INTO t1 VALUES (2,'b');
2390INSERT INTO t1 VALUES (3,'c');
2391EXPLAIN SELECT i FROM t1 WHERE i=1;
2392id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
23931	SIMPLE	t1	const	PRIMARY	PRIMARY	4	const	1	Using index
2394DROP TABLE t1;
2395CREATE TABLE t1 ( a BLOB, INDEX (a(20)) );
2396CREATE TABLE t2 ( a BLOB, INDEX (a(20)) );
2397INSERT INTO t1 VALUES ('one'),('two'),('three'),('four'),('five');
2398INSERT INTO t2 VALUES ('one'),('two'),('three'),('four'),('five');
2399EXPLAIN SELECT * FROM t1 LEFT JOIN t2 USE INDEX (a) ON t1.a=t2.a;
2400id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
24011	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	5	NULL
24021	SIMPLE	t2	ref	a	a	23	test.t1.a	2	Using where
2403EXPLAIN SELECT * FROM t1 LEFT JOIN t2 FORCE INDEX (a) ON t1.a=t2.a;
2404id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
24051	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	5	NULL
24061	SIMPLE	t2	ref	a	a	23	test.t1.a	2	Using where
2407DROP TABLE t1, t2;
2408CREATE TABLE t1 ( city char(30) );
2409INSERT INTO t1 VALUES ('London');
2410INSERT INTO t1 VALUES ('Paris');
2411SELECT * FROM t1 WHERE city='London';
2412city
2413London
2414SELECT * FROM t1 WHERE city='london';
2415city
2416London
2417EXPLAIN SELECT * FROM t1 WHERE city='London' AND city='london';
2418id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
24191	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	Using where
2420SELECT * FROM t1 WHERE city='London' AND city='london';
2421city
2422London
2423EXPLAIN SELECT * FROM t1 WHERE city LIKE '%london%' AND city='London';
2424id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
24251	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	Using where
2426SELECT * FROM t1 WHERE city LIKE '%london%' AND city='London';
2427city
2428London
2429DROP TABLE t1;
2430create table t1 (a int(11) unsigned, b int(11) unsigned);
2431insert into t1 values (1,0), (1,1), (18446744073709551615,0);
2432Warnings:
2433Warning	1264	Out of range value for column 'a' at row 3
2434select a-b  from t1 order by 1;
2435a-b
24360
24371
24384294967295
2439select a-b , (a-b < 0)  from t1 order by 1;
2440a-b	(a-b < 0)
24410	0
24421	0
24434294967295	0
2444select a-b as d, (a-b >= 0), b from t1 group by b having d >= 0;
2445d	(a-b >= 0)	b
24461	1	0
24470	1	1
2448select cast((a - b) as unsigned) from t1 order by 1;
2449cast((a - b) as unsigned)
24500
24511
24524294967295
2453drop table t1;
2454create table t1 (a int(11));
2455select all all * from t1;
2456a
2457select distinct distinct * from t1;
2458a
2459select all distinct * from t1;
2460ERROR HY000: Incorrect usage of ALL and DISTINCT
2461select distinct all * from t1;
2462ERROR HY000: Incorrect usage of ALL and DISTINCT
2463drop table t1;
2464CREATE TABLE t1 (
2465kunde_intern_id int(10) unsigned NOT NULL default '0',
2466kunde_id int(10) unsigned NOT NULL default '0',
2467FK_firma_id int(10) unsigned NOT NULL default '0',
2468aktuell enum('Ja','Nein') NOT NULL default 'Ja',
2469vorname varchar(128) NOT NULL default '',
2470nachname varchar(128) NOT NULL default '',
2471geloescht enum('Ja','Nein') NOT NULL default 'Nein',
2472firma varchar(128) NOT NULL default ''
2473);
2474INSERT INTO t1 VALUES
2475(3964,3051,1,'Ja','Vorname1','1Nachname','Nein','Print Schau XXXX'),
2476(3965,3051111,1,'Ja','Vorname1111','1111Nachname','Nein','Print Schau XXXX');
2477SELECT kunde_id ,FK_firma_id ,aktuell, vorname, nachname, geloescht FROM t1
2478WHERE
2479(
2480(
2481( '' != '' AND firma LIKE CONCAT('%', '', '%'))
2482OR
2483(vorname LIKE CONCAT('%', 'Vorname1', '%') AND
2484nachname LIKE CONCAT('%', '1Nachname', '%') AND
2485'Vorname1' != '' AND 'xxxx' != '')
2486)
2487AND
2488(
2489aktuell = 'Ja' AND geloescht = 'Nein' AND FK_firma_id = 2
2490)
2491)
2492;
2493kunde_id	FK_firma_id	aktuell	vorname	nachname	geloescht
2494SELECT kunde_id ,FK_firma_id ,aktuell, vorname, nachname,
2495geloescht FROM t1
2496WHERE
2497(
2498(
2499aktuell = 'Ja' AND geloescht = 'Nein' AND FK_firma_id = 2
2500)
2501AND
2502(
2503( '' != '' AND firma LIKE CONCAT('%', '', '%')  )
2504OR
2505(  vorname LIKE CONCAT('%', 'Vorname1', '%') AND
2506nachname LIKE CONCAT('%', '1Nachname', '%') AND 'Vorname1' != '' AND
2507'xxxx' != '')
2508)
2509)
2510;
2511kunde_id	FK_firma_id	aktuell	vorname	nachname	geloescht
2512SELECT COUNT(*) FROM t1 WHERE
2513( 0 OR (vorname LIKE '%Vorname1%' AND nachname LIKE '%1Nachname%' AND 1))
2514AND FK_firma_id = 2;
2515COUNT(*)
25160
2517drop table t1;
2518CREATE TABLE t1 (b BIGINT(20) UNSIGNED NOT NULL, PRIMARY KEY (b));
2519INSERT INTO t1 VALUES (0x8000000000000000);
2520SELECT b FROM t1 WHERE b=0x8000000000000000;
2521b
25229223372036854775808
2523DROP TABLE t1;
2524CREATE TABLE `t1` ( `gid` int(11) default NULL, `uid` int(11) default NULL);
2525CREATE TABLE `t2` ( `ident` int(11) default NULL, `level` char(16) default NULL);
2526INSERT INTO `t2` VALUES (0,'READ');
2527CREATE TABLE `t3` ( `id` int(11) default NULL, `name` char(16) default NULL);
2528INSERT INTO `t3` VALUES (1,'fs');
2529select * from t3 left join t1 on t3.id = t1.uid, t2 where t2.ident in (0, t1.gid, t3.id, 0);
2530id	name	gid	uid	ident	level
25311	fs	NULL	NULL	0	READ
2532drop table t1,t2,t3;
2533CREATE TABLE t1 (
2534acct_id int(11) NOT NULL default '0',
2535profile_id smallint(6) default NULL,
2536UNIQUE KEY t1$acct_id (acct_id),
2537KEY t1$profile_id (profile_id)
2538);
2539INSERT INTO t1 VALUES (132,17),(133,18);
2540CREATE TABLE t2 (
2541profile_id smallint(6) default NULL,
2542queue_id int(11) default NULL,
2543seq int(11) default NULL,
2544KEY t2$queue_id (queue_id)
2545);
2546INSERT INTO t2 VALUES (17,31,4),(17,30,3),(17,36,2),(17,37,1);
2547CREATE TABLE t3 (
2548id int(11) NOT NULL default '0',
2549qtype int(11) default NULL,
2550seq int(11) default NULL,
2551warn_lvl int(11) default NULL,
2552crit_lvl int(11) default NULL,
2553rr1 tinyint(4) NOT NULL default '0',
2554rr2 int(11) default NULL,
2555default_queue tinyint(4) NOT NULL default '0',
2556KEY t3$qtype (qtype),
2557KEY t3$id (id)
2558);
2559INSERT INTO t3 VALUES (30,1,29,NULL,NULL,0,NULL,0),(31,1,28,NULL,NULL,0,NULL,0),
2560(36,1,34,NULL,NULL,0,NULL,0),(37,1,35,NULL,NULL,0,121,0);
2561SELECT COUNT(*) FROM t1 a STRAIGHT_JOIN t2 pq STRAIGHT_JOIN t3 q
2562WHERE
2563(pq.profile_id = a.profile_id) AND (a.acct_id = 132) AND
2564(pq.queue_id = q.id) AND (q.rr1 <> 1);
2565COUNT(*)
25664
2567drop table t1,t2,t3;
2568create table t1 (f1 int);
2569insert into t1 values (1),(NULL);
2570create table t2 (f2 int, f3 int, f4 int);
2571create index idx1 on t2 (f4);
2572insert into t2 values (1,2,3),(2,4,6);
2573select A.f2 from t1 left join t2 A on A.f2 = f1 where A.f3=(select min(f3)
2574from  t2 C where A.f4 = C.f4) or A.f3 IS NULL;
2575f2
25761
2577NULL
2578drop table t1,t2;
2579create table t2 (a tinyint unsigned);
2580create index t2i on t2(a);
2581insert into t2 values (0), (254), (255);
2582explain select * from t2 where a > -1;
2583id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
25841	SIMPLE	t2	index	t2i	t2i	2	NULL	3	Using where; Using index
2585select * from t2 where a > -1;
2586a
25870
2588254
2589255
2590drop table t2;
2591CREATE TABLE t1 (a int, b int, c int);
2592INSERT INTO t1
2593SELECT 50, 3, 3 FROM DUAL
2594WHERE NOT EXISTS
2595(SELECT * FROM t1 WHERE a = 50 AND b = 3);
2596SELECT * FROM t1;
2597a	b	c
259850	3	3
2599INSERT INTO t1
2600SELECT 50, 3, 3 FROM DUAL
2601WHERE NOT EXISTS
2602(SELECT * FROM t1 WHERE a = 50 AND b = 3);
2603select found_rows();
2604found_rows()
26050
2606SELECT * FROM t1;
2607a	b	c
260850	3	3
2609select count(*) from t1;
2610count(*)
26111
2612select found_rows();
2613found_rows()
26141
2615select count(*) from t1 limit 2,3;
2616count(*)
2617select found_rows();
2618found_rows()
26190
2620select SQL_CALC_FOUND_ROWS count(*) from t1 limit 2,3;
2621count(*)
2622select found_rows();
2623found_rows()
26241
2625DROP TABLE t1;
2626CREATE TABLE t1 (a INT, b INT);
2627(SELECT a, b AS c FROM t1) ORDER BY c+1;
2628a	c
2629(SELECT a, b AS c FROM t1) ORDER BY b+1;
2630a	c
2631SELECT a, b AS c FROM t1 ORDER BY c+1;
2632a	c
2633SELECT a, b AS c FROM t1 ORDER BY b+1;
2634a	c
2635drop table t1;
2636create table t1(f1 int, f2 int);
2637create table t2(f3 int);
2638select f1 from t1,t2 where f1=f2 and (f1,f2) = ((1,1));
2639f1
2640select f1 from t1,t2 where f1=f2 and (f1,NULL) = ((1,1));
2641f1
2642select f1 from t1,t2 where f1=f2 and (f1,f2) = ((1,NULL));
2643f1
2644insert into t1 values(1,1),(2,null);
2645insert into t2 values(2);
2646select * from t1,t2 where f1=f3 and (f1,f2) = (2,null);
2647f1	f2	f3
2648select * from t1,t2 where f1=f3 and (f1,f2) <=> (2,null);
2649f1	f2	f3
26502	NULL	2
2651drop table t1,t2;
2652create table t1 (f1 int not null auto_increment primary key, f2 varchar(10));
2653create table t11 like t1;
2654insert into t1 values(1,""),(2,"");
2655show table status like 't1%';
2656Name	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
2657t1	MyISAM	10	Dynamic	2	20	X	X	X	X	X	X	X	X	latin1_swedish_ci	NULL
2658t11	MyISAM	10	Dynamic	0	0	X	X	X	X	X	X	X	X	latin1_swedish_ci	NULL
2659select 123 as a from t1 where f1 is null;
2660a
2661drop table t1,t11;
2662CREATE TABLE t1 ( a INT NOT NULL, b INT NOT NULL, UNIQUE idx (a,b) );
2663INSERT INTO t1 VALUES (1,1),(1,2),(1,3),(1,4);
2664CREATE TABLE t2 ( a INT NOT NULL, b INT NOT NULL, e INT );
2665INSERT INTO t2 VALUES ( 1,10,1), (1,10,2), (1,11,1), (1,11,2), (1,2,1), (1,2,2),(1,2,3);
2666SELECT t2.a, t2.b, IF(t1.b IS NULL,'',e) AS c, COUNT(*) AS d FROM t2 LEFT JOIN
2667t1 ON t2.a = t1.a AND t2.b = t1.b GROUP BY a, b, c;
2668a	b	c	d
26691	2	1	1
26701	2	2	1
26711	2	3	1
26721	10		2
26731	11		2
2674SELECT t2.a, t2.b, IF(t1.b IS NULL,'',e) AS c, COUNT(*) AS d FROM t2 LEFT JOIN
2675t1 ON t2.a = t1.a AND t2.b = t1.b GROUP BY t1.a, t1.b, c;
2676a	b	c	d
26771	10		4
26781	2	1	1
26791	2	2	1
26801	2	3	1
2681SELECT t2.a, t2.b, IF(t1.b IS NULL,'',e) AS c, COUNT(*) AS d FROM t2 LEFT JOIN
2682t1 ON t2.a = t1.a AND t2.b = t1.b GROUP BY t2.a, t2.b, c;
2683a	b	c	d
26841	2	1	1
26851	2	2	1
26861	2	3	1
26871	10		2
26881	11		2
2689SELECT t2.a, t2.b, IF(t1.b IS NULL,'',e) AS c, COUNT(*) AS d FROM t2,t1
2690WHERE t2.a = t1.a AND t2.b = t1.b GROUP BY a, b, c;
2691a	b	c	d
26921	2	1	1
26931	2	2	1
26941	2	3	1
2695DROP TABLE IF EXISTS t1, t2;
2696create table t1 (f1 int primary key, f2 int);
2697create table t2 (f3 int, f4 int, primary key(f3,f4));
2698insert into t1 values (1,1);
2699insert into t2 values (1,1),(1,2);
2700select distinct count(f2) >0 from t1 left join t2 on f1=f3 group by f1;
2701count(f2) >0
27021
2703drop table t1,t2;
2704create table t1 (f1 int,f2 int);
2705insert into t1 values(1,1);
2706create table t2 (f3 int, f4 int, primary key(f3,f4));
2707insert into t2 values(1,1);
2708select * from t1 where f1 in (select f3 from t2 where (f3,f4)= (select f3,f4 from t2));
2709f1	f2
27101	1
2711drop table t1,t2;
2712CREATE TABLE t1(a int, b int, c int, KEY b(b), KEY c(c));
2713insert into t1 values (1,0,0),(2,0,0);
2714CREATE TABLE t2 (a int, b varchar(2), c varchar(2), PRIMARY KEY(a));
2715insert into t2 values (1,'',''), (2,'','');
2716CREATE TABLE t3 (a int, b int, PRIMARY KEY (a,b), KEY a (a), KEY b (b));
2717insert into t3 values (1,1),(1,2);
2718explain select straight_join DISTINCT t2.a,t2.b, t1.c from t1, t3, t2
2719where (t1.c=t2.a or (t1.c=t3.a and t2.a=t3.b)) and t1.b=556476786 and
2720t2.b like '%%' order by t2.b limit 0,1;
2721id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
27221	SIMPLE	t1	ref	b,c	b	5	const	1	Using temporary; Using filesort
27231	SIMPLE	t3	index	PRIMARY,a,b	PRIMARY	8	NULL	2	Using index
27241	SIMPLE	t2	ALL	PRIMARY	NULL	NULL	NULL	2	Range checked for each record (index map: 0x1)
2725DROP TABLE t1,t2,t3;
2726CREATE TABLE t1 (a int, INDEX idx(a));
2727INSERT INTO t1 VALUES (2), (3), (1);
2728EXPLAIN SELECT * FROM t1 IGNORE INDEX (idx);
2729id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
27301	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	NULL
2731EXPLAIN SELECT * FROM t1 IGNORE INDEX (a);
2732ERROR 42000: Key 'a' doesn't exist in table 't1'
2733EXPLAIN SELECT * FROM t1 FORCE INDEX (a);
2734ERROR 42000: Key 'a' doesn't exist in table 't1'
2735DROP TABLE t1;
2736CREATE TABLE t1 (a int, b int);
2737INSERT INTO t1 VALUES (1,1), (2,1), (4,10);
2738CREATE TABLE t2 (a int PRIMARY KEY, b int, KEY b (b));
2739INSERT INTO t2 VALUES (1,NULL), (2,10);
2740ALTER TABLE t1 ENABLE KEYS;
2741EXPLAIN SELECT STRAIGHT_JOIN SQL_NO_CACHE COUNT(*) FROM t2, t1 WHERE t1.b = t2.b OR t2.b IS NULL;
2742id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
27431	SIMPLE	t2	index	b	b	5	NULL	2	Using index
27441	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	Using where
2745SELECT STRAIGHT_JOIN SQL_NO_CACHE * FROM t2, t1 WHERE t1.b = t2.b OR t2.b IS NULL;
2746a	b	a	b
27471	NULL	1	1
27481	NULL	2	1
27491	NULL	4	10
27502	10	4	10
2751EXPLAIN SELECT STRAIGHT_JOIN SQL_NO_CACHE COUNT(*) FROM t2, t1 WHERE t1.b = t2.b OR t2.b IS NULL;
2752id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
27531	SIMPLE	t2	index	b	b	5	NULL	2	Using index
27541	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	Using where
2755SELECT STRAIGHT_JOIN SQL_NO_CACHE * FROM t2, t1 WHERE t1.b = t2.b OR t2.b IS NULL;
2756a	b	a	b
27571	NULL	1	1
27581	NULL	2	1
27591	NULL	4	10
27602	10	4	10
2761DROP TABLE IF EXISTS t1,t2;
2762CREATE TABLE t1 (key1 float default NULL, UNIQUE KEY key1 (key1));
2763CREATE TABLE t2 (key2 float default NULL, UNIQUE KEY key2 (key2));
2764INSERT INTO t1 VALUES (0.3762),(0.3845),(0.6158),(0.7941);
2765INSERT INTO t2 VALUES (1.3762),(1.3845),(1.6158),(1.7941);
2766explain select max(key1) from t1 where key1 <= 0.6158;
2767id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
27681	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Select tables optimized away
2769explain select max(key2) from t2 where key2 <= 1.6158;
2770id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
27711	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Select tables optimized away
2772explain select min(key1) from t1 where key1 >= 0.3762;
2773id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
27741	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Select tables optimized away
2775explain select min(key2) from t2 where key2 >= 1.3762;
2776id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
27771	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Select tables optimized away
2778explain select max(key1), min(key2) from t1, t2
2779where key1 <= 0.6158 and key2 >= 1.3762;
2780id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
27811	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Select tables optimized away
2782explain select max(key1) from t1 where key1 <= 0.6158 and rand() + 0.5 >= 0.5;
2783id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
27841	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Select tables optimized away
2785explain select min(key1) from t1 where key1 >= 0.3762 and rand() + 0.5 >= 0.5;
2786id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
27871	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Select tables optimized away
2788select max(key1) from t1 where key1 <= 0.6158;
2789max(key1)
27900.6158000230789185
2791select max(key2) from t2 where key2 <= 1.6158;
2792max(key2)
27931.6158000230789185
2794select min(key1) from t1 where key1 >= 0.3762;
2795min(key1)
27960.37619999051094055
2797select min(key2) from t2 where key2 >= 1.3762;
2798min(key2)
27991.3761999607086182
2800select max(key1), min(key2) from t1, t2
2801where key1 <= 0.6158 and key2 >= 1.3762;
2802max(key1)	min(key2)
28030.6158000230789185	1.3761999607086182
2804select max(key1) from t1 where key1 <= 0.6158 and rand() + 0.5 >= 0.5;
2805max(key1)
28060.6158000230789185
2807select min(key1) from t1 where key1 >= 0.3762 and rand() + 0.5 >= 0.5;
2808min(key1)
28090.37619999051094055
2810DROP TABLE t1,t2;
2811CREATE TABLE t1 (i BIGINT UNSIGNED NOT NULL);
2812INSERT INTO t1 VALUES (10);
2813SELECT i='1e+01',i=1e+01, i in (1e+01,1e+01), i in ('1e+01','1e+01') FROM t1;
2814i='1e+01'	i=1e+01	i in (1e+01,1e+01)	i in ('1e+01','1e+01')
28151	1	1	1
2816DROP TABLE t1;
2817create table t1(a bigint unsigned, b bigint);
2818insert into t1 values (0xfffffffffffffffff, 0xfffffffffffffffff),
2819(0x10000000000000000, 0x10000000000000000),
2820(0x8fffffffffffffff, 0x8fffffffffffffff);
2821Warnings:
2822Warning	1264	Out of range value for column 'a' at row 1
2823Warning	1264	Out of range value for column 'b' at row 1
2824Warning	1264	Out of range value for column 'a' at row 2
2825Warning	1264	Out of range value for column 'b' at row 2
2826Warning	1264	Out of range value for column 'b' at row 3
2827select hex(a), hex(b) from t1;
2828hex(a)	hex(b)
2829FFFFFFFFFFFFFFFF	7FFFFFFFFFFFFFFF
2830FFFFFFFFFFFFFFFF	7FFFFFFFFFFFFFFF
28318FFFFFFFFFFFFFFF	7FFFFFFFFFFFFFFF
2832drop table t1;
2833CREATE TABLE t1 (c0 int);
2834CREATE TABLE t2 (c0 int);
2835INSERT INTO t1 VALUES(@@connect_timeout);
2836INSERT INTO t2 VALUES(@@connect_timeout);
2837SELECT * FROM t1 JOIN t2 ON t1.c0 = t2.c0 WHERE (t1.c0 <=> @@connect_timeout);
2838c0	c0
2839X	X
2840DROP TABLE t1, t2;
2841End of 4.1 tests
2842CREATE TABLE t1 (
2843K2C4 varchar(4) character set latin1 collate latin1_bin NOT NULL default '',
2844K4N4 varchar(4) character set latin1 collate latin1_bin NOT NULL default '0000',
2845F2I4 int(11) NOT NULL default '0'
2846) ENGINE=MyISAM DEFAULT CHARSET=latin1;
2847INSERT INTO t1 VALUES
2848('W%RT', '0100',  1),
2849('W-RT', '0100', 1),
2850('WART', '0100', 1),
2851('WART', '0200', 1),
2852('WERT', '0100', 2),
2853('WORT','0200', 2),
2854('WT', '0100', 2),
2855('W_RT', '0100', 2),
2856('WaRT', '0100', 3),
2857('WART', '0300', 3),
2858('WRT' , '0400', 3),
2859('WURM', '0500', 3),
2860('W%T', '0600', 4),
2861('WA%T', '0700', 4),
2862('WA_T', '0800', 4);
2863SELECT K2C4, K4N4, F2I4 FROM t1
2864WHERE  K2C4 = 'WART' AND
2865(F2I4 = 2 AND K2C4 = 'WART' OR (F2I4 = 2 OR K4N4 = '0200'));
2866K2C4	K4N4	F2I4
2867WART	0200	1
2868SELECT K2C4, K4N4, F2I4 FROM t1
2869WHERE  K2C4 = 'WART' AND (K2C4 = 'WART' OR K4N4 = '0200');
2870K2C4	K4N4	F2I4
2871WART	0100	1
2872WART	0200	1
2873WART	0300	3
2874DROP TABLE t1;
2875create table t1 (a int, b int);
2876create table t2 like t1;
2877select t1.a from (t1 inner join t2 on t1.a=t2.a) where t2.a=1;
2878a
2879select t1.a from ((t1 inner join t2 on t1.a=t2.a)) where t2.a=1;
2880a
2881select x.a, y.a, z.a from ( (t1 x inner join t2 y on x.a=y.a) inner join t2 z on y.a=z.a) WHERE x.a=1;
2882a	a	a
2883drop table t1,t2;
2884create table t1 (s1 varchar(5));
2885insert into t1 values ('Wall');
2886select min(s1) from t1 group by s1 with rollup;
2887min(s1)
2888Wall
2889Wall
2890drop table t1;
2891create table t1 (s1 int) engine=myisam;
2892insert into t1 values (0);
2893select avg(distinct s1) from t1 group by s1 with rollup;
2894avg(distinct s1)
28950.0000
28960.0000
2897drop table t1;
2898create table t1 (s1 int);
2899insert into t1 values (null),(1);
2900select avg(s1) as x from t1 group by s1 with rollup;
2901x
2902NULL
29031.0000
29041.0000
2905select distinct avg(s1) as x from t1 group by s1 with rollup;
2906ERROR HY000: Incorrect usage of WITH ROLLUP and DISTINCT
2907drop table t1;
2908CREATE TABLE t1 (a int);
2909CREATE TABLE t2 (a int);
2910INSERT INTO t1 VALUES (1), (2), (3), (4), (5);
2911INSERT INTO t2 VALUES (2), (4), (6);
2912SELECT t1.a FROM t1 STRAIGHT_JOIN t2 ON t1.a=t2.a;
2913a
29142
29154
2916EXPLAIN SELECT t1.a FROM t1 STRAIGHT_JOIN t2 ON t1.a=t2.a;
2917id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
29181	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	5	NULL
29191	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	3	Using where
2920EXPLAIN SELECT t1.a FROM t1 INNER JOIN t2 ON t1.a=t2.a;
2921id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
29221	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	3	NULL
29231	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	5	Using where
2924DROP TABLE t1,t2;
2925select x'10' + 0, X'10' + 0, b'10' + 0, B'10' + 0;
2926x'10' + 0	X'10' + 0	b'10' + 0	B'10' + 0
292716	16	2	2
2928create table t1 (f1 varchar(6) default NULL, f2 int(6) primary key not null);
2929create table t2 (f3 varchar(5) not null, f4 varchar(5) not null, UNIQUE KEY UKEY (f3,f4));
2930insert into t1 values (" 2", 2);
2931insert into t2 values (" 2", " one "),(" 2", " two ");
2932select * from t1 left join t2 on f1 = f3;
2933f1	f2	f3	f4
2934 2	2	 2	 one
2935 2	2	 2	 two
2936drop table t1,t2;
2937create table t1 (empnum smallint, grp int);
2938create table t2 (empnum int, name char(5));
2939insert into t1 values(1,1);
2940insert into t2 values(1,'bob');
2941create view v1 as select * from t2 inner join t1 using (empnum);
2942select * from v1;
2943empnum	name	grp
29441	bob	1
2945drop table t1,t2;
2946drop view v1;
2947create table t1 (pk int primary key, b int);
2948create table t2 (pk int primary key, c int);
2949select pk from t1 inner join t2 using (pk);
2950pk
2951drop table t1,t2;
2952create table t1 (s1 int, s2 char(5), s3 decimal(10));
2953create view v1 as select s1, s2, 'x' as s3 from t1;
2954select * from t1 natural join v1;
2955s1	s2	s3
2956insert into t1 values (1,'x',5);
2957select * from t1 natural join v1;
2958s1	s2	s3
2959Warnings:
2960Warning	1292	Truncated incorrect DOUBLE value: 'x'
2961drop table t1;
2962drop view v1;
2963create table t1(a1 int);
2964create table t2(a2 int);
2965insert into t1 values(1),(2);
2966insert into t2 values(1),(2);
2967create view v2 (c) as select a1 from t1;
2968select * from t1 natural left join t2;
2969a1	a2
29701	1
29711	2
29722	1
29732	2
2974select * from t1 natural right join t2;
2975a2	a1
29761	1
29771	2
29782	1
29792	2
2980select * from v2 natural left join t2;
2981c	a2
29821	1
29831	2
29842	1
29852	2
2986select * from v2 natural right join t2;
2987a2	c
29881	1
29891	2
29902	1
29912	2
2992drop table t1, t2;
2993drop view v2;
2994create table t1 (a int(10), t1_val int(10));
2995create table t2 (b int(10), t2_val int(10));
2996create table t3 (a int(10), b int(10));
2997insert into t1 values (1,1),(2,2);
2998insert into t2 values (1,1),(2,2),(3,3);
2999insert into t3 values (1,1),(2,1),(3,1),(4,1);
3000select * from t1 natural join t2 natural join t3;
3001a	b	t1_val	t2_val
30021	1	1	1
30032	1	2	1
3004select * from t1 natural join t3 natural join t2;
3005b	a	t1_val	t2_val
30061	1	1	1
30071	2	2	1
3008drop table t1, t2, t3;
3009DO IFNULL(NULL, NULL);
3010SELECT CAST(IFNULL(NULL, NULL) AS DECIMAL);
3011CAST(IFNULL(NULL, NULL) AS DECIMAL)
3012NULL
3013SELECT ABS(IFNULL(NULL, NULL));
3014ABS(IFNULL(NULL, NULL))
3015NULL
3016SELECT IFNULL(NULL, NULL);
3017IFNULL(NULL, NULL)
3018NULL
3019SET @OLD_SQL_MODE12595=@@SQL_MODE, @@SQL_MODE='';
3020SHOW LOCAL VARIABLES LIKE 'SQL_MODE';
3021Variable_name	Value
3022sql_mode
3023CREATE TABLE BUG_12595(a varchar(100));
3024INSERT INTO BUG_12595 VALUES ('hakan%'), ('hakank'), ("ha%an");
3025SELECT * FROM BUG_12595 WHERE a LIKE 'hakan\%';
3026a
3027hakan%
3028SELECT * FROM BUG_12595 WHERE a LIKE 'hakan*%' ESCAPE '*';
3029a
3030hakan%
3031SELECT * FROM BUG_12595 WHERE a LIKE 'hakan**%' ESCAPE '**';
3032ERROR HY000: Incorrect arguments to ESCAPE
3033SELECT * FROM BUG_12595 WHERE a LIKE 'hakan%' ESCAPE '';
3034a
3035hakan%
3036hakank
3037SELECT * FROM BUG_12595 WHERE a LIKE 'hakan\%' ESCAPE '';
3038a
3039SELECT * FROM BUG_12595 WHERE a LIKE 'ha\%an' ESCAPE 0x5c;
3040a
3041ha%an
3042SELECT * FROM BUG_12595 WHERE a LIKE 'ha%%an' ESCAPE '%';
3043a
3044ha%an
3045SELECT * FROM BUG_12595 WHERE a LIKE 'ha\%an' ESCAPE '\\';
3046a
3047ha%an
3048SELECT * FROM BUG_12595 WHERE a LIKE 'ha|%an' ESCAPE '|';
3049a
3050ha%an
3051SET @@SQL_MODE='NO_BACKSLASH_ESCAPES';
3052SHOW LOCAL VARIABLES LIKE 'SQL_MODE';
3053Variable_name	Value
3054sql_mode	NO_BACKSLASH_ESCAPES
3055SELECT * FROM BUG_12595 WHERE a LIKE 'hakan\%';
3056a
3057SELECT * FROM BUG_12595 WHERE a LIKE 'hakan*%' ESCAPE '*';
3058a
3059hakan%
3060SELECT * FROM BUG_12595 WHERE a LIKE 'hakan**%' ESCAPE '**';
3061ERROR HY000: Incorrect arguments to ESCAPE
3062SELECT * FROM BUG_12595 WHERE a LIKE 'hakan\%' ESCAPE '\\';
3063ERROR HY000: Incorrect arguments to ESCAPE
3064SELECT * FROM BUG_12595 WHERE a LIKE 'hakan%' ESCAPE '';
3065ERROR HY000: Incorrect arguments to ESCAPE
3066SELECT * FROM BUG_12595 WHERE a LIKE 'ha\%an' ESCAPE 0x5c;
3067a
3068ha%an
3069SELECT * FROM BUG_12595 WHERE a LIKE 'ha|%an' ESCAPE '|';
3070a
3071ha%an
3072SELECT * FROM BUG_12595 WHERE a LIKE 'hakan\n%' ESCAPE '\n';
3073ERROR HY000: Incorrect arguments to ESCAPE
3074SET @@SQL_MODE=@OLD_SQL_MODE12595;
3075DROP TABLE BUG_12595;
3076create table t1 (a char(1));
3077create table t2 (a char(1));
3078insert into t1 values ('a'),('b'),('c');
3079insert into t2 values ('b'),('c'),('d');
3080select a from t1 natural join t2;
3081a
3082b
3083c
3084select * from t1 natural join t2 where a = 'b';
3085a
3086b
3087drop table t1, t2;
3088CREATE TABLE t1 (`id` TINYINT);
3089CREATE TABLE t2 (`id` TINYINT);
3090CREATE TABLE t3 (`id` TINYINT);
3091INSERT INTO t1 VALUES (1),(2),(3);
3092INSERT INTO t2 VALUES (2);
3093INSERT INTO t3 VALUES (3);
3094SELECT t1.id,t3.id FROM t1 JOIN t2 ON (t2.id=t1.id) LEFT JOIN t3 USING (id);
3095ERROR 23000: Column 'id' in from clause is ambiguous
3096SELECT t1.id,t3.id FROM t1 JOIN t2 ON (t2.notacolumn=t1.id) LEFT JOIN t3 USING (id);
3097ERROR 23000: Column 'id' in from clause is ambiguous
3098SELECT id,t3.id FROM t1 JOIN t2 ON (t2.id=t1.id) LEFT JOIN t3 USING (id);
3099ERROR 23000: Column 'id' in from clause is ambiguous
3100SELECT id,t3.id FROM (t1 JOIN t2 ON (t2.id=t1.id)) LEFT JOIN t3 USING (id);
3101ERROR 23000: Column 'id' in from clause is ambiguous
3102drop table t1, t2, t3;
3103create table t1 (a int(10),b int(10));
3104create table t2 (a int(10),b int(10));
3105insert into t1 values (1,10),(2,20),(3,30);
3106insert into t2 values (1,10);
3107select * from t1 inner join t2 using (A);
3108a	b	b
31091	10	10
3110select * from t1 inner join t2 using (a);
3111a	b	b
31121	10	10
3113drop table t1, t2;
3114create table t1 (a int, c int);
3115create table t2 (b int);
3116create table t3 (b int, a int);
3117create table t4 (c int);
3118insert into t1 values (1,1);
3119insert into t2 values (1);
3120insert into t3 values (1,1);
3121insert into t4 values (1);
3122select * from t1 join t2 join t3 on (t2.b = t3.b and t1.a = t3.a);
3123a	c	b	b	a
31241	1	1	1	1
3125select * from t1, t2 join t3 on (t2.b = t3.b and t1.a = t3.a);
3126ERROR 42S22: Unknown column 't1.a' in 'on clause'
3127select * from t1 join t2 join t3 join t4 on (t1.a = t4.c and t2.b = t4.c);
3128a	c	b	b	a	c
31291	1	1	1	1	1
3130select * from t1 join t2 join t4 using (c);
3131c	a	b
31321	1	1
3133drop table t1, t2, t3, t4;
3134create table t1(x int, y int);
3135create table t2(x int, y int);
3136create table t3(x int, primary key(x));
3137insert into t1 values (1, 1), (2, 1), (3, 1), (4, 3), (5, 6), (6, 6);
3138insert into t2 values (1, 1), (2, 1), (3, 3), (4, 6), (5, 6);
3139insert into t3 values (1), (2), (3), (4), (5);
3140select t1.x, t3.x from t1, t2, t3  where t1.x = t2.x and t3.x >= t1.y and t3.x <= t2.y;
3141x	x
31421	1
31432	1
31443	1
31453	2
31463	3
31474	3
31484	4
31494	5
3150drop table t1,t2,t3;
3151create table t1 (id char(16) not null default '', primary key  (id));
3152insert into t1 values ('100'),('101'),('102');
3153create table t2 (id char(16) default null);
3154insert into t2 values (1);
3155create view v1 as select t1.id from t1;
3156create view v2 as select t2.id from t2;
3157create view v3 as select (t1.id+2) as id from t1 natural left join t2;
3158select t1.id from t1 left join v2 using (id);
3159id
3160100
3161101
3162102
3163select t1.id from v2 right join t1 using (id);
3164id
3165100
3166101
3167102
3168select t1.id from t1 left join v3 using (id);
3169id
3170100
3171101
3172102
3173select * from t1 left join v2 using (id);
3174id
3175100
3176101
3177102
3178select * from v2 right join t1 using (id);
3179id
3180100
3181101
3182102
3183select * from t1 left join v3 using (id);
3184id
3185100
3186101
3187102
3188select v1.id from v1 left join v2 using (id);
3189id
3190100
3191101
3192102
3193select v1.id from v2 right join v1 using (id);
3194id
3195100
3196101
3197102
3198select v1.id from v1 left join v3 using (id);
3199id
3200100
3201101
3202102
3203select * from v1 left join v2 using (id);
3204id
3205100
3206101
3207102
3208select * from v2 right join v1 using (id);
3209id
3210100
3211101
3212102
3213select * from v1 left join v3 using (id);
3214id
3215100
3216101
3217102
3218drop table t1, t2;
3219drop view v1, v2, v3;
3220create table t1 (id int(11) not null default '0');
3221insert into t1 values (123),(191),(192);
3222create table t2 (id char(16) character set utf8 not null);
3223insert into t2 values ('58013'),('58014'),('58015'),('58016');
3224create table t3 (a_id int(11) not null, b_id char(16) character set utf8);
3225insert into t3 values (123,null),(123,null),(123,null),(123,null),(123,null),(123,'58013');
3226select count(*)
3227from t1 inner join (t3 left join t2 on t2.id = t3.b_id) on t1.id = t3.a_id;
3228count(*)
32296
3230select count(*)
3231from t1 inner join (t2 right join t3 on t2.id = t3.b_id) on t1.id = t3.a_id;
3232count(*)
32336
3234drop table t1,t2,t3;
3235create table t1 (a int);
3236create table t2 (b int);
3237create table t3 (c int);
3238select * from t1 join t2 join t3 on (t1.a=t3.c);
3239a	b	c
3240select * from t1 join t2 left join t3 on (t1.a=t3.c);
3241a	b	c
3242select * from t1 join t2 right join t3 on (t1.a=t3.c);
3243a	b	c
3244select * from t1 join t2 straight_join t3 on (t1.a=t3.c);
3245a	b	c
3246drop table t1, t2 ,t3;
3247create table t1(f1 int, f2 date);
3248insert into t1 values(1,'2005-01-01'),(2,'2005-09-01'),(3,'2005-09-30'),
3249(4,'2005-10-01'),(5,'2005-12-30');
3250select * from t1 where f2 >= 0            order by f2;
3251f1	f2
32521	2005-01-01
32532	2005-09-01
32543	2005-09-30
32554	2005-10-01
32565	2005-12-30
3257select * from t1 where f2 >= '0000-00-00' order by f2;
3258f1	f2
32591	2005-01-01
32602	2005-09-01
32613	2005-09-30
32624	2005-10-01
32635	2005-12-30
3264select * from t1 where f2 >= '2005-09-31' order by f2;
3265f1	f2
32664	2005-10-01
32675	2005-12-30
3268select * from t1 where f2 >= '2005-09-3a' order by f2;
3269f1	f2
32703	2005-09-30
32714	2005-10-01
32725	2005-12-30
3273Warnings:
3274Warning	1292	Incorrect date value: '2005-09-3a' for column 'f2' at row 1
3275select * from t1 where f2 <= '2005-09-31' order by f2;
3276f1	f2
32771	2005-01-01
32782	2005-09-01
32793	2005-09-30
3280select * from t1 where f2 <= '2005-09-3a' order by f2;
3281f1	f2
32821	2005-01-01
32832	2005-09-01
3284Warnings:
3285Warning	1292	Incorrect date value: '2005-09-3a' for column 'f2' at row 1
3286drop table t1;
3287create table t1 (f1 int, f2 int);
3288insert into t1 values (1, 30), (2, 20), (3, 10);
3289create algorithm=merge view v1 as select f1, f2 from t1;
3290create algorithm=merge view v2 (f2, f1) as select f1, f2 from t1;
3291create algorithm=merge view v3 as select t1.f1 as f2, t1.f2 as f1 from t1;
3292select t1.f1 as x1, f1 from t1 order by t1.f1;
3293x1	f1
32941	1
32952	2
32963	3
3297select v1.f1 as x1, f1 from v1 order by v1.f1;
3298x1	f1
32991	1
33002	2
33013	3
3302select v2.f1 as x1, f1 from v2 order by v2.f1;
3303x1	f1
330410	10
330520	20
330630	30
3307select v3.f1 as x1, f1 from v3 order by v3.f1;
3308x1	f1
330910	10
331020	20
331130	30
3312select f1, f2, v1.f1 as x1 from v1 order by v1.f1;
3313f1	f2	x1
33141	30	1
33152	20	2
33163	10	3
3317select f1, f2, v2.f1 as x1 from v2 order by v2.f1;
3318f1	f2	x1
331910	3	10
332020	2	20
332130	1	30
3322select f1, f2, v3.f1 as x1 from v3 order by v3.f1;
3323f1	f2	x1
332410	3	10
332520	2	20
332630	1	30
3327drop table t1;
3328drop view v1, v2, v3;
3329CREATE TABLE t1(key_a int4 NOT NULL, optimus varchar(32), PRIMARY KEY(key_a));
3330CREATE TABLE t2(key_a int4 NOT NULL, prime varchar(32), PRIMARY KEY(key_a));
3331CREATE table t3(key_a int4 NOT NULL, key_b int4 NOT NULL, foo varchar(32),
3332PRIMARY KEY(key_a,key_b));
3333INSERT INTO t1 VALUES (0,'');
3334INSERT INTO t1 VALUES (1,'i');
3335INSERT INTO t1 VALUES (2,'j');
3336INSERT INTO t1 VALUES (3,'k');
3337INSERT INTO t2 VALUES (1,'r');
3338INSERT INTO t2 VALUES (2,'s');
3339INSERT INTO t2 VALUES (3,'t');
3340INSERT INTO t3 VALUES (1,5,'x');
3341INSERT INTO t3 VALUES (1,6,'y');
3342INSERT INTO t3 VALUES (2,5,'xx');
3343INSERT INTO t3 VALUES (2,6,'yy');
3344INSERT INTO t3 VALUES (2,7,'zz');
3345INSERT INTO t3 VALUES (3,5,'xxx');
3346SELECT t2.key_a,foo
3347FROM t1 INNER JOIN t2 ON t1.key_a = t2.key_a
3348INNER JOIN t3 ON t1.key_a = t3.key_a
3349WHERE t2.key_a=2 and key_b=5;
3350key_a	foo
33512	xx
3352EXPLAIN SELECT t2.key_a,foo
3353FROM t1 INNER JOIN t2 ON t1.key_a = t2.key_a
3354INNER JOIN t3 ON t1.key_a = t3.key_a
3355WHERE t2.key_a=2 and key_b=5;
3356id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
33571	SIMPLE	t1	const	PRIMARY	PRIMARY	4	const	1	Using index
33581	SIMPLE	t2	const	PRIMARY	PRIMARY	4	const	1	Using index
33591	SIMPLE	t3	const	PRIMARY	PRIMARY	8	const,const	1	NULL
3360SELECT t2.key_a,foo
3361FROM t1 INNER JOIN t2 ON t2.key_a = t1.key_a
3362INNER JOIN t3 ON t1.key_a = t3.key_a
3363WHERE t2.key_a=2 and key_b=5;
3364key_a	foo
33652	xx
3366EXPLAIN SELECT t2.key_a,foo
3367FROM t1 INNER JOIN t2 ON t2.key_a = t1.key_a
3368INNER JOIN t3 ON t1.key_a = t3.key_a
3369WHERE t2.key_a=2 and key_b=5;
3370id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
33711	SIMPLE	t1	const	PRIMARY	PRIMARY	4	const	1	Using index
33721	SIMPLE	t2	const	PRIMARY	PRIMARY	4	const	1	Using index
33731	SIMPLE	t3	const	PRIMARY	PRIMARY	8	const,const	1	NULL
3374DROP TABLE t1,t2,t3;
3375create  table t1 (f1 int);
3376insert into t1 values(1),(2);
3377create table t2 (f2 int, f3 int, key(f2));
3378insert into t2 values(1,1),(2,2);
3379create table t3 (f4 int not null);
3380insert into t3 values (2),(2),(2);
3381select f1,(select count(*) from t2,t3 where f2=f1 and f3=f4) as count from t1;
3382f1	count
33831	0
33842	3
3385drop table t1,t2,t3;
3386create table t1 (f1 int unique);
3387create table t2 (f2 int unique);
3388create table t3 (f3 int unique);
3389insert into t1 values(1),(2);
3390insert into t2 values(1),(2);
3391insert into t3 values(1),(NULL);
3392select * from t3 where f3 is null;
3393f3
3394NULL
3395select t2.f2 from t1 left join t2 on f1=f2 join t3 on f1=f3 where f1=1;
3396f2
33971
3398drop table t1,t2,t3;
3399create table t1(f1 char, f2 char not null);
3400insert into t1 values(null,'a');
3401create table t2 (f2 char not null);
3402insert into t2 values('b');
3403select * from t1 left join t2 on f1=t2.f2 where t1.f2='a';
3404f1	f2	f2
3405NULL	a	NULL
3406drop table t1,t2;
3407select * from (select * left join t on f1=f2) tt;
3408ERROR 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 'on f1=f2) tt' at line 1
3409CREATE TABLE t1 (sku int PRIMARY KEY, pr int);
3410CREATE TABLE t2 (sku int PRIMARY KEY, sppr int, name varchar(255));
3411INSERT INTO t1 VALUES
3412(10, 10), (20, 10), (30, 20), (40, 30), (50, 10), (60, 10);
3413INSERT INTO t2 VALUES
3414(10, 10, 'aaa'), (20, 10, 'bbb'), (30, 10, 'ccc'), (40, 20, 'ddd'),
3415(50, 10, 'eee'), (60, 20, 'fff'), (70, 20, 'ggg'), (80, 30, 'hhh');
3416SELECT t2.sku, t2.sppr, t2.name, t1.sku, t1.pr
3417FROM t2, t1 WHERE t2.sku=20 AND (t2.sku=t1.sku OR t2.sppr=t1.sku);
3418sku	sppr	name	sku	pr
341920	10	bbb	10	10
342020	10	bbb	20	10
3421EXPLAIN
3422SELECT t2.sku, t2.sppr, t2.name, t1.sku, t1.pr
3423FROM t2, t1 WHERE t2.sku=20 AND (t2.sku=t1.sku OR t2.sppr=t1.sku);
3424id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
34251	SIMPLE	t2	const	PRIMARY	PRIMARY	4	const	1	NULL
34261	SIMPLE	t1	range	PRIMARY	PRIMARY	4	NULL	2	Using index condition; Using MRR
3427DROP TABLE t1,t2;
3428SET SQL_MODE='NO_UNSIGNED_SUBTRACTION';
3429CREATE TABLE t1 (i TINYINT UNSIGNED NOT NULL);
3430INSERT t1 SET i = 0;
3431UPDATE t1 SET i = -1;
3432Warnings:
3433Warning	1264	Out of range value for column 'i' at row 1
3434SELECT * FROM t1;
3435i
34360
3437UPDATE t1 SET i = CAST(i - 1 AS SIGNED);
3438Warnings:
3439Warning	1264	Out of range value for column 'i' at row 1
3440SELECT * FROM t1;
3441i
34420
3443UPDATE t1 SET i = i - 1;
3444Warnings:
3445Warning	1264	Out of range value for column 'i' at row 1
3446SELECT * FROM t1;
3447i
34480
3449DROP TABLE t1;
3450SET SQL_MODE=default;
3451create table t1 (a int);
3452insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
3453create table t2 (a int, b int, c int, e int, primary key(a,b,c));
3454insert into t2 select A.a, B.a, C.a, C.a from t1 A, t1 B, t1 C;
3455analyze table t2;
3456Table	Op	Msg_type	Msg_text
3457test.t2	analyze	status	OK
3458select 'In next EXPLAIN, B.rows must be exactly 10:' Z;
3459Z
3460In next EXPLAIN, B.rows must be exactly 10:
3461explain select * from t2 A, t2 B where A.a=5 and A.b=5 and A.C<5
3462and B.a=5 and B.b=A.e and (B.b =1 or B.b = 3 or B.b=5);
3463id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
34641	SIMPLE	A	range	PRIMARY	PRIMARY	12	NULL	5	Using index condition; Using where; Using MRR
34651	SIMPLE	B	ref	PRIMARY	PRIMARY	8	const,test.A.e	10	Using join buffer (Batched Key Access)
3466drop table t1, t2;
3467CREATE TABLE t1 (a int PRIMARY KEY, b int, INDEX(b));
3468INSERT INTO t1 VALUES (1, 3), (9,4), (7,5), (4,5), (6,2),
3469(3,1), (5,1), (8,9), (2,2), (0,9);
3470CREATE TABLE t2 (c int, d int, f int, INDEX(c,f));
3471INSERT INTO t2 VALUES
3472(1,0,0), (1,0,1), (2,0,0), (2,0,1), (3,0,0), (4,0,1),
3473(5,0,0), (5,0,1), (6,0,0), (0,0,1), (7,0,0), (7,0,1),
3474(0,0,0), (0,0,1), (8,0,0), (8,0,1), (9,0,0), (9,0,1);
3475EXPLAIN
3476SELECT a, c, d, f FROM t1,t2 WHERE a=c AND b BETWEEN 4 AND 6;
3477id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
34781	SIMPLE	t1	range	PRIMARY,b	b	5	NULL	3	Using index condition; Using MRR
34791	SIMPLE	t2	ref	c	c	5	test.t1.a	2	Using join buffer (Batched Key Access)
3480EXPLAIN
3481SELECT a, c, d, f FROM t1,t2 WHERE a=c AND b BETWEEN 4 AND 6 AND a > 0;
3482id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
34831	SIMPLE	t1	range	PRIMARY,b	b	5	NULL	3	Using index condition; Using where; Using MRR
34841	SIMPLE	t2	ref	c	c	5	test.t1.a	2	Using join buffer (Batched Key Access)
3485DROP TABLE t1, t2;
3486create table t1 (
3487a int unsigned    not null auto_increment primary key,
3488b bit             not null,
3489c bit             not null
3490);
3491create table t2 (
3492a int unsigned    not null auto_increment primary key,
3493b bit             not null,
3494c int unsigned    not null,
3495d varchar(50)
3496);
3497insert into t1 (b,c) values (0,1), (0,1);
3498insert into t2 (b,c) values (0,1);
3499select t1.a, t1.b + 0, t1.c + 0, t2.a, t2.b + 0, t2.c, t2.d
3500from t1 left outer join t2 on t1.a = t2.c and t2.b <> 1
3501where t1.b <> 1 order by t1.a;
3502a	t1.b + 0	t1.c + 0	a	t2.b + 0	c	d
35031	0	1	1	0	1	NULL
35042	0	1	NULL	NULL	NULL	NULL
3505drop table t1,t2;
3506SELECT 0.9888889889 * 1.011111411911;
35070.9888889889 * 1.011111411911
35080.9998769417899202067879
3509prepare stmt from 'select 1 as " a "';
3510Warnings:
3511Warning	1466	Leading spaces are removed from name ' a '
3512execute stmt;
3513a
35141
3515CREATE TABLE t1 (a int NOT NULL PRIMARY KEY, b int NOT NULL);
3516INSERT INTO t1 VALUES (1,1), (2,2), (3,3), (4,4);
3517CREATE TABLE t2 (c int NOT NULL, INDEX idx(c));
3518INSERT INTO t2 VALUES
3519(1), (1), (1), (1), (1), (1), (1), (1),
3520(2), (2), (2), (2),
3521(3), (3),
3522(4);
3523EXPLAIN SELECT b FROM t1, t2 WHERE b=c AND a=1;
3524id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
35251	SIMPLE	t1	const	PRIMARY	PRIMARY	4	const	1	NULL
35261	SIMPLE	t2	ref	idx	idx	4	const	7	Using index
3527EXPLAIN SELECT b FROM t1, t2 WHERE b=c AND a=4;
3528id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
35291	SIMPLE	t1	const	PRIMARY	PRIMARY	4	const	1	NULL
35301	SIMPLE	t2	ref	idx	idx	4	const	1	Using index
3531DROP TABLE t1, t2;
3532CREATE TABLE t1 (id int NOT NULL PRIMARY KEY, a int);
3533INSERT INTO t1 VALUES (1,2), (2,NULL), (3,2);
3534CREATE TABLE t2 (b int, c INT, INDEX idx1(b));
3535INSERT INTO t2 VALUES (2,1), (3,2);
3536CREATE TABLE t3 (d int,  e int, INDEX idx1(d));
3537INSERT INTO t3 VALUES (2,10), (2,20), (1,30), (2,40), (2,50);
3538EXPLAIN
3539SELECT * FROM t1 LEFT JOIN t2 ON t2.b=t1.a INNER JOIN t3 ON t3.d=t1.id
3540WHERE t1.id=2;
3541id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
35421	SIMPLE	t1	const	PRIMARY	PRIMARY	4	const	1	NULL
35431	SIMPLE	t2	const	idx1	NULL	NULL	NULL	1	NULL
35441	SIMPLE	t3	ref	idx1	idx1	5	const	3	NULL
3545SELECT * FROM t1 LEFT JOIN t2 ON t2.b=t1.a INNER JOIN t3 ON t3.d=t1.id
3546WHERE t1.id=2;
3547id	a	b	c	d	e
35482	NULL	NULL	NULL	2	10
35492	NULL	NULL	NULL	2	20
35502	NULL	NULL	NULL	2	40
35512	NULL	NULL	NULL	2	50
3552DROP TABLE t1,t2,t3;
3553create table t1 (c1 varchar(1), c2 int, c3 int, c4 int, c5 int, c6 int,
3554c7 int, c8 int, c9 int, fulltext key (`c1`));
3555select distinct match (`c1`) against ('z') , c2, c3, c4,c5, c6,c7, c8
3556from t1 where c9=1 order by c2, c2;
3557match (`c1`) against ('z')	c2	c3	c4	c5	c6	c7	c8
3558drop table t1;
3559CREATE TABLE t1 (pk varchar(10) PRIMARY KEY, fk varchar(16));
3560CREATE TABLE t2 (pk varchar(16) PRIMARY KEY, fk varchar(10));
3561INSERT INTO t1 VALUES
3562('d','dddd'), ('i','iii'), ('a','aa'), ('b','bb'), ('g','gg'),
3563('e','eee'), ('c','cccc'), ('h','hhh'), ('j','jjj'), ('f','fff');
3564INSERT INTO t2 VALUES
3565('jjj', 'j'), ('cc','c'), ('ccc','c'), ('aaa', 'a'), ('jjjj','j'),
3566('hhh','h'), ('gg','g'), ('fff','f'), ('ee','e'), ('ffff','f'),
3567('bbb','b'), ('ff','f'), ('cccc','c'), ('dddd','d'), ('jj','j'),
3568('aaaa','a'), ('bb','b'), ('eeee','e'), ('aa','a'), ('hh','h');
3569EXPLAIN SELECT t2.*
3570FROM t1 JOIN t2 ON t2.fk=t1.pk
3571WHERE t2.fk < 'c' AND t2.pk=t1.fk;
3572id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
35731	SIMPLE	t1	range	PRIMARY	PRIMARY	12	NULL	3	Using index condition; Using where; Using MRR
35741	SIMPLE	t2	eq_ref	PRIMARY	PRIMARY	18	test.t1.fk	1	Using where; Using join buffer (Batched Key Access)
3575EXPLAIN SELECT t2.*
3576FROM t1 JOIN t2 ON t2.fk=t1.pk
3577WHERE t2.fk BETWEEN 'a' AND 'b' AND t2.pk=t1.fk;
3578id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
35791	SIMPLE	t1	range	PRIMARY	PRIMARY	12	NULL	2	Using index condition; Using where; Using MRR
35801	SIMPLE	t2	eq_ref	PRIMARY	PRIMARY	18	test.t1.fk	1	Using where; Using join buffer (Batched Key Access)
3581EXPLAIN SELECT t2.*
3582FROM t1 JOIN t2 ON t2.fk=t1.pk
3583WHERE t2.fk IN ('a','b') AND t2.pk=t1.fk;
3584id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
35851	SIMPLE	t1	range	PRIMARY	PRIMARY	12	NULL	2	Using index condition; Using where; Using MRR
35861	SIMPLE	t2	eq_ref	PRIMARY	PRIMARY	18	test.t1.fk	1	Using where; Using join buffer (Batched Key Access)
3587DROP TABLE t1,t2;
3588CREATE TABLE t1 (a int, b varchar(20) NOT NULL, PRIMARY KEY(a));
3589CREATE TABLE t2 (a int, b varchar(20) NOT NULL,
3590PRIMARY KEY (a), UNIQUE KEY (b));
3591INSERT INTO t1 VALUES (1,'a'),(2,'b'),(3,'c');
3592INSERT INTO t2 VALUES (1,'a'),(2,'b'),(3,'c');
3593EXPLAIN SELECT t1.a FROM t1 LEFT JOIN t2 ON t2.b=t1.b WHERE t1.a=3;
3594id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
35951	SIMPLE	t1	const	PRIMARY	PRIMARY	4	const	1	NULL
35961	SIMPLE	t2	const	b	b	22	const	1	Using index
3597DROP TABLE t1,t2;
3598CREATE TABLE t1(id int PRIMARY KEY, b int, e int);
3599CREATE TABLE t2(i int, a int, INDEX si(i), INDEX ai(a));
3600CREATE TABLE t3(a int PRIMARY KEY, c char(4), INDEX ci(c));
3601INSERT INTO t1 VALUES
3602(1,10,19), (2,20,22), (4,41,42), (9,93,95), (7, 77,79),
3603(6,63,67), (5,55,58), (3,38,39), (8,81,89);
3604INSERT INTO t2 VALUES
3605(21,210), (41,410), (82,820), (83,830), (84,840),
3606(65,650), (51,510), (37,370), (94,940), (76,760),
3607(22,220), (33,330), (40,400), (95,950), (38,380),
3608(67,670), (88,880), (57,570), (96,960), (97,970);
3609INSERT INTO t3 VALUES
3610(210,'bb'), (950,'ii'), (400,'ab'), (500,'ee'), (220,'gg'),
3611(440,'gg'), (310,'eg'), (380,'ee'), (840,'bb'), (830,'ff'),
3612(230,'aa'), (960,'ii'), (410,'aa'), (510,'ee'), (290,'bb'),
3613(450,'gg'), (320,'dd'), (390,'hh'), (850,'jj'), (860,'ff');
3614EXPLAIN
3615SELECT t3.a FROM t1,t2 FORCE INDEX (si),t3
3616WHERE t1.id = 8 AND t2.i BETWEEN t1.b AND t1.e AND
3617t3.a=t2.a AND t3.c IN ('bb','ee');
3618id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
36191	SIMPLE	t1	const	PRIMARY	PRIMARY	4	const	1	NULL
36201	SIMPLE	t2	range	si	si	5	NULL	4	Using index condition; Using where; Using MRR
36211	SIMPLE	t3	eq_ref	PRIMARY,ci	PRIMARY	4	test.t2.a	1	Using where; Using join buffer (Batched Key Access)
3622EXPLAIN
3623SELECT t3.a FROM t1,t2,t3
3624WHERE t1.id = 8 AND t2.i BETWEEN t1.b AND t1.e AND
3625t3.a=t2.a AND t3.c IN ('bb','ee') ;
3626id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
36271	SIMPLE	t1	const	PRIMARY	PRIMARY	4	const	1	NULL
36281	SIMPLE	t2	range	si,ai	si	5	NULL	4	Using index condition; Using where; Using MRR
36291	SIMPLE	t3	eq_ref	PRIMARY,ci	PRIMARY	4	test.t2.a	1	Using where; Using join buffer (Batched Key Access)
3630EXPLAIN
3631SELECT t3.a FROM t1,t2 FORCE INDEX (si),t3
3632WHERE t1.id = 8 AND (t2.i=t1.b OR t2.i=t1.e) AND t3.a=t2.a AND
3633t3.c IN ('bb','ee');
3634id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
36351	SIMPLE	t1	const	PRIMARY	PRIMARY	4	const	1	NULL
36361	SIMPLE	t2	range	si	si	5	NULL	2	Using index condition; Using where; Using MRR
36371	SIMPLE	t3	eq_ref	PRIMARY,ci	PRIMARY	4	test.t2.a	1	Using where; Using join buffer (Batched Key Access)
3638EXPLAIN
3639SELECT t3.a FROM t1,t2,t3
3640WHERE t1.id = 8 AND (t2.i=t1.b OR t2.i=t1.e) AND t3.a=t2.a AND
3641t3.c IN ('bb','ee');
3642id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
36431	SIMPLE	t1	const	PRIMARY	PRIMARY	4	const	1	NULL
36441	SIMPLE	t2	range	si,ai	si	5	NULL	2	Using index condition; Using where; Using MRR
36451	SIMPLE	t3	eq_ref	PRIMARY,ci	PRIMARY	4	test.t2.a	1	Using where; Using join buffer (Batched Key Access)
3646DROP TABLE t1,t2,t3;
3647CREATE TABLE t1 ( f1 int primary key, f2 int, f3 int, f4 int, f5 int, f6 int, checked_out int);
3648CREATE TABLE t2 ( f11 int PRIMARY KEY );
3649INSERT INTO t1 VALUES (1,1,1,0,0,0,0),(2,1,1,3,8,1,0),(3,1,1,4,12,1,0);
3650INSERT INTO t2 VALUES (62);
3651SELECT * FROM t1 LEFT JOIN t2 ON f11 = t1.checked_out GROUP BY f1 ORDER BY f2, f3, f4, f5 LIMIT 0, 1;
3652f1	f2	f3	f4	f5	f6	checked_out	f11
36531	1	1	0	0	0	0	NULL
3654DROP TABLE t1, t2;
3655DROP TABLE IF EXISTS t1;
3656CREATE TABLE t1(a int);
3657INSERT into t1 values (1), (2), (3);
3658SELECT * FROM t1 LIMIT 2, -1;
3659ERROR 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 '-1' at line 1
3660DROP TABLE t1;
3661CREATE TABLE t1 (
3662ID_with_null int NULL,
3663ID_better int NOT NULL,
3664INDEX idx1 (ID_with_null),
3665INDEX idx2 (ID_better)
3666);
3667INSERT INTO t1 VALUES (1,1), (2,1), (null,3), (null,3), (null,3), (null,3);
3668INSERT INTO t1 SELECT * FROM t1 WHERE ID_with_null IS NULL;
3669INSERT INTO t1 SELECT * FROM t1 WHERE ID_with_null IS NULL;
3670INSERT INTO t1 SELECT * FROM t1 WHERE ID_with_null IS NULL;
3671INSERT INTO t1 SELECT * FROM t1 WHERE ID_with_null IS NULL;
3672INSERT INTO t1 SELECT * FROM t1 WHERE ID_with_null IS NULL;
3673SELECT COUNT(*) FROM t1 WHERE ID_with_null IS NULL;
3674COUNT(*)
3675128
3676SELECT COUNT(*) FROM t1 WHERE ID_better=1;
3677COUNT(*)
36782
3679EXPLAIN SELECT * FROM t1 WHERE ID_better=1 AND ID_with_null IS NULL;
3680id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
36811	SIMPLE	t1	ref	idx1,idx2	idx2	4	const	1	Using where
3682DROP INDEX idx1 ON t1;
3683CREATE UNIQUE INDEX idx1 ON t1(ID_with_null);
3684EXPLAIN SELECT * FROM t1 WHERE ID_better=1 AND ID_with_null IS NULL;
3685id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
36861	SIMPLE	t1	ref	idx1,idx2	idx2	4	const	1	Using where
3687DROP TABLE t1;
3688CREATE TABLE t1 (
3689ID1_with_null int NULL,
3690ID2_with_null int NULL,
3691ID_better int NOT NULL,
3692INDEX idx1 (ID1_with_null, ID2_with_null),
3693INDEX idx2 (ID_better)
3694);
3695INSERT INTO t1 VALUES (1,1,1), (2,2,1), (3,null,3), (null,3,3), (null,null,3),
3696(3,null,3), (null,3,3), (null,null,3), (3,null,3), (null,3,3), (null,null,3);
3697INSERT INTO t1 SELECT * FROM t1 WHERE ID1_with_null IS NULL;
3698INSERT INTO t1 SELECT * FROM t1 WHERE ID2_with_null IS NULL;
3699INSERT INTO t1 SELECT * FROM t1 WHERE ID1_with_null IS NULL;
3700INSERT INTO t1 SELECT * FROM t1 WHERE ID2_with_null IS NULL;
3701INSERT INTO t1 SELECT * FROM t1 WHERE ID1_with_null IS NULL;
3702INSERT INTO t1 SELECT * FROM t1 WHERE ID2_with_null IS NULL;
3703SELECT COUNT(*) FROM t1 WHERE ID1_with_null IS NULL AND ID2_with_null=3;
3704COUNT(*)
370524
3706SELECT COUNT(*) FROM t1 WHERE ID1_with_null=3 AND ID2_with_null IS NULL;
3707COUNT(*)
370824
3709SELECT COUNT(*) FROM t1 WHERE ID1_with_null IS NULL AND ID2_with_null IS NULL;
3710COUNT(*)
3711192
3712SELECT COUNT(*) FROM t1 WHERE ID_better=1;
3713COUNT(*)
37142
3715EXPLAIN SELECT * FROM t1
3716WHERE ID_better=1 AND ID1_with_null IS NULL AND ID2_with_null=3 ;
3717id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
37181	SIMPLE	t1	ref	idx1,idx2	idx2	4	const	1	Using where
3719EXPLAIN SELECT * FROM t1
3720WHERE ID_better=1 AND ID1_with_null=3 AND ID2_with_null=3 IS NULL ;
3721id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
37221	SIMPLE	t1	ref	idx1,idx2	idx2	4	const	1	Using where
3723EXPLAIN SELECT * FROM t1
3724WHERE ID_better=1 AND ID1_with_null IS NULL AND ID2_with_null IS NULL;
3725id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
37261	SIMPLE	t1	ref	idx1,idx2	idx2	4	const	1	Using where
3727DROP INDEX idx1 ON t1;
3728CREATE UNIQUE INDEX idx1 ON t1(ID1_with_null,ID2_with_null);
3729EXPLAIN SELECT * FROM t1
3730WHERE ID_better=1 AND ID1_with_null IS NULL AND ID2_with_null=3 ;
3731id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
37321	SIMPLE	t1	ref	idx1,idx2	idx2	4	const	1	Using where
3733EXPLAIN SELECT * FROM t1
3734WHERE ID_better=1 AND ID1_with_null=3 AND ID2_with_null IS NULL ;
3735id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
37361	SIMPLE	t1	ref	idx1,idx2	idx2	4	const	1	Using where
3737EXPLAIN SELECT * FROM t1
3738WHERE ID_better=1 AND ID1_with_null IS NULL AND ID2_with_null IS NULL;
3739id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
37401	SIMPLE	t1	ref	idx1,idx2	idx2	4	const	1	Using where
3741EXPLAIN SELECT * FROM t1
3742WHERE ID_better=1 AND ID1_with_null IS NULL AND
3743(ID2_with_null=1 OR ID2_with_null=2);
3744id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
37451	SIMPLE	t1	ref	idx1,idx2	idx2	4	const	1	Using where
3746DROP TABLE t1;
3747CREATE TABLE t1 (a INT, ts TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, KEY ts(ts));
3748INSERT INTO t1 VALUES (30,"2006-01-03 23:00:00"), (31,"2006-01-03 23:00:00");
3749ANALYZE TABLE t1;
3750Table	Op	Msg_type	Msg_text
3751test.t1	analyze	status	OK
3752CREATE TABLE t2 (a INT, dt1 DATETIME, dt2 DATETIME, PRIMARY KEY (a));
3753INSERT INTO t2 VALUES (30, "2006-01-01 00:00:00", "2999-12-31 00:00:00");
3754INSERT INTO t2 SELECT a+1,dt1,dt2 FROM t2;
3755ANALYZE TABLE t2;
3756Table	Op	Msg_type	Msg_text
3757test.t2	analyze	status	OK
3758EXPLAIN
3759SELECT * FROM t1 LEFT JOIN t2 ON (t1.a=t2.a) WHERE t1.a=30
3760AND t1.ts BETWEEN t2.dt1 AND t2.dt2
3761AND t1.ts BETWEEN "2006-01-01" AND "2006-12-31";
3762id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
37631	SIMPLE	t2	const	PRIMARY	PRIMARY	4	const	1	NULL
37641	SIMPLE	t1	range	ts	ts	4	NULL	1	Using index condition; Using where; Using MRR
3765Warnings:
3766Warning	1292	Incorrect datetime value: '2999-12-31 00:00:00' for column 'ts' at row 1
3767SELECT * FROM t1 LEFT JOIN t2 ON (t1.a=t2.a) WHERE t1.a=30
3768AND t1.ts BETWEEN t2.dt1 AND t2.dt2
3769AND t1.ts BETWEEN "2006-01-01" AND "2006-12-31";
3770a	ts	a	dt1	dt2
377130	2006-01-03 23:00:00	30	2006-01-01 00:00:00	2999-12-31 00:00:00
3772Warnings:
3773Warning	1292	Incorrect datetime value: '2999-12-31 00:00:00' for column 'ts' at row 1
3774DROP TABLE t1,t2;
3775create table t1 (a bigint unsigned);
3776insert into t1 values
3777(if(1, 9223372036854775808, 1)),
3778(case when 1 then 9223372036854775808 else 1 end),
3779(coalesce(9223372036854775808, 1));
3780select * from t1;
3781a
37829223372036854775808
37839223372036854775808
37849223372036854775808
3785drop table t1;
3786create table t1 select
3787if(1, 9223372036854775808, 1) i,
3788case when 1 then 9223372036854775808 else 1 end c,
3789coalesce(9223372036854775808, 1) co;
3790show create table t1;
3791Table	Create Table
3792t1	CREATE TABLE `t1` (
3793  `i` decimal(19,0) NOT NULL DEFAULT '0',
3794  `c` decimal(19,0) NOT NULL DEFAULT '0',
3795  `co` decimal(19,0) NOT NULL DEFAULT '0'
3796) ENGINE=MyISAM DEFAULT CHARSET=latin1
3797drop table t1;
3798select
3799if(1, cast(1111111111111111111 as unsigned), 1) i,
3800case when 1 then cast(1111111111111111111 as unsigned) else 1 end c,
3801coalesce(cast(1111111111111111111 as unsigned), 1) co;
3802i	c	co
38031111111111111111111	1111111111111111111	1111111111111111111
3804CREATE TABLE t1 (name varchar(255));
3805CREATE TABLE t2 (name varchar(255), n int, KEY (name(3)));
3806INSERT INTO t1 VALUES ('ccc'), ('bb'), ('cc '), ('aa  '), ('aa');
3807INSERT INTO t2 VALUES ('bb',1), ('aa',2), ('cc   ',3);
3808INSERT INTO t2 VALUES (concat('cc ', 0x06), 4);
3809INSERT INTO t2 VALUES ('cc',5), ('bb ',6), ('cc ',7);
3810SELECT * FROM t2;
3811name	n
3812bb	1
3813aa	2
3814cc   	3
3815cc 	4
3816cc	5
3817bb 	6
3818cc 	7
3819SELECT * FROM t2 ORDER BY name;
3820name	n
3821aa	2
3822bb	1
3823bb 	6
3824cc 	4
3825cc   	3
3826cc	5
3827cc 	7
3828SELECT name, LENGTH(name), n FROM t2 ORDER BY name;
3829name	LENGTH(name)	n
3830aa	2	2
3831bb	2	1
3832bb 	3	6
3833cc 	4	4
3834cc   	5	3
3835cc	2	5
3836cc 	3	7
3837EXPLAIN SELECT name, LENGTH(name), n FROM t2 WHERE name='cc ';
3838id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
38391	SIMPLE	t2	ref	name	name	6	const	3	Using where
3840SELECT name, LENGTH(name), n FROM t2 WHERE name='cc ';
3841name	LENGTH(name)	n
3842cc   	5	3
3843cc	2	5
3844cc 	3	7
3845EXPLAIN SELECT name , LENGTH(name), n FROM t2 WHERE name LIKE 'cc%';
3846id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
38471	SIMPLE	t2	range	name	name	6	NULL	3	Using where
3848SELECT name , LENGTH(name), n FROM t2 WHERE name LIKE 'cc%';
3849name	LENGTH(name)	n
3850cc   	5	3
3851cc 	4	4
3852cc	2	5
3853cc 	3	7
3854EXPLAIN SELECT name , LENGTH(name), n FROM t2 WHERE name LIKE 'cc%' ORDER BY name;
3855id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
38561	SIMPLE	t2	range	name	name	6	NULL	3	Using where; Using filesort
3857SELECT name , LENGTH(name), n FROM t2 WHERE name LIKE 'cc%' ORDER BY name;
3858name	LENGTH(name)	n
3859cc 	4	4
3860cc   	5	3
3861cc	2	5
3862cc 	3	7
3863EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON t1.name=t2.name;
3864id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
38651	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	5	NULL
38661	SIMPLE	t2	ref	name	name	6	test.t1.name	2	Using where
3867SELECT * FROM t1 LEFT JOIN t2 ON t1.name=t2.name;
3868name	name	n
3869ccc	NULL	NULL
3870bb	bb	1
3871bb	bb 	6
3872cc 	cc   	3
3873cc 	cc	5
3874cc 	cc 	7
3875aa  	aa	2
3876aa	aa	2
3877DROP TABLE t1,t2;
3878CREATE TABLE t1 (name text);
3879CREATE TABLE t2 (name text, n int, KEY (name(3)));
3880INSERT INTO t1 VALUES ('ccc'), ('bb'), ('cc '), ('aa  '), ('aa');
3881INSERT INTO t2 VALUES ('bb',1), ('aa',2), ('cc   ',3);
3882INSERT INTO t2 VALUES (concat('cc ', 0x06), 4);
3883INSERT INTO t2 VALUES ('cc',5), ('bb ',6), ('cc ',7);
3884SELECT * FROM t2;
3885name	n
3886bb	1
3887aa	2
3888cc   	3
3889cc 	4
3890cc	5
3891bb 	6
3892cc 	7
3893SELECT * FROM t2 ORDER BY name;
3894name	n
3895aa	2
3896bb	1
3897bb 	6
3898cc 	4
3899cc   	3
3900cc	5
3901cc 	7
3902SELECT name, LENGTH(name), n FROM t2 ORDER BY name;
3903name	LENGTH(name)	n
3904aa	2	2
3905bb	2	1
3906bb 	3	6
3907cc 	4	4
3908cc   	5	3
3909cc	2	5
3910cc 	3	7
3911EXPLAIN SELECT name, LENGTH(name), n FROM t2 WHERE name='cc ';
3912id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
39131	SIMPLE	t2	ref	name	name	6	const	3	Using where
3914SELECT name, LENGTH(name), n FROM t2 WHERE name='cc ';
3915name	LENGTH(name)	n
3916cc   	5	3
3917cc	2	5
3918cc 	3	7
3919EXPLAIN SELECT name , LENGTH(name), n FROM t2 WHERE name LIKE 'cc%';
3920id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
39211	SIMPLE	t2	range	name	name	6	NULL	3	Using where
3922SELECT name , LENGTH(name), n FROM t2 WHERE name LIKE 'cc%';
3923name	LENGTH(name)	n
3924cc   	5	3
3925cc 	4	4
3926cc	2	5
3927cc 	3	7
3928EXPLAIN SELECT name , LENGTH(name), n FROM t2 WHERE name LIKE 'cc%' ORDER BY name;
3929id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
39301	SIMPLE	t2	range	name	name	6	NULL	3	Using where; Using filesort
3931SELECT name , LENGTH(name), n FROM t2 WHERE name LIKE 'cc%' ORDER BY name;
3932name	LENGTH(name)	n
3933cc 	4	4
3934cc   	5	3
3935cc	2	5
3936cc 	3	7
3937EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON t1.name=t2.name;
3938id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
39391	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	5	NULL
39401	SIMPLE	t2	ref	name	name	6	test.t1.name	2	Using where
3941SELECT * FROM t1 LEFT JOIN t2 ON t1.name=t2.name;
3942name	name	n
3943ccc	NULL	NULL
3944bb	bb	1
3945bb	bb 	6
3946cc 	cc   	3
3947cc 	cc	5
3948cc 	cc 	7
3949aa  	aa	2
3950aa	aa	2
3951DROP TABLE t1,t2;
3952CREATE TABLE t1 (
3953access_id int NOT NULL default '0',
3954name varchar(20) default NULL,
3955rank int NOT NULL default '0',
3956KEY idx (access_id)
3957);
3958CREATE TABLE t2 (
3959faq_group_id int NOT NULL default '0',
3960faq_id int NOT NULL default '0',
3961access_id int default NULL,
3962UNIQUE KEY idx1 (faq_id),
3963KEY idx2 (faq_group_id,faq_id)
3964);
3965INSERT INTO t1 VALUES
3966(1,'Everyone',2),(2,'Help',3),(3,'Technical Support',1),(4,'Chat User',4);
3967INSERT INTO t2 VALUES
3968(261,265,1),(490,494,1);
3969SELECT t2.faq_id
3970FROM t1 INNER JOIN t2 IGNORE INDEX (idx1)
3971ON (t1.access_id = t2.access_id)
3972LEFT JOIN t2 t
3973ON (t.faq_group_id = t2.faq_group_id AND
3974find_in_set(t.access_id, '1,4') < find_in_set(t2.access_id, '1,4'))
3975WHERE
3976t2.access_id IN (1,4) AND t.access_id IS NULL AND t2.faq_id in (265);
3977faq_id
3978265
3979SELECT t2.faq_id
3980FROM t1 INNER JOIN t2
3981ON (t1.access_id = t2.access_id)
3982LEFT JOIN t2 t
3983ON (t.faq_group_id = t2.faq_group_id AND
3984find_in_set(t.access_id, '1,4') < find_in_set(t2.access_id, '1,4'))
3985WHERE
3986t2.access_id IN (1,4) AND t.access_id IS NULL AND t2.faq_id in (265);
3987faq_id
3988265
3989DROP TABLE t1,t2;
3990CREATE TABLE t1 (a INT, b INT, KEY inx (b,a));
3991INSERT INTO t1 VALUES (1,1), (1,2), (1,3), (1,4), (1,5), (1, 6), (1,7);
3992EXPLAIN SELECT COUNT(*) FROM t1 f1 INNER JOIN t1 f2
3993ON ( f1.b=f2.b AND f1.a<f2.a )
3994WHERE 1 AND f1.b NOT IN (100,2232,3343,51111);
3995id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
39961	SIMPLE	f1	index	inx	inx	10	NULL	7	Using where; Using index
39971	SIMPLE	f2	ref	inx	inx	5	test.f1.b	1	Using where; Using index
3998DROP TABLE t1;
3999CREATE TABLE t1 (c1 INT, c2 INT);
4000INSERT INTO t1 VALUES (1,11), (2,22), (2,22);
4001EXPLAIN SELECT c1 FROM t1 WHERE (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT COUNT(c2)))))))))))))))))))))))))))))) > 0;
4002id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
40031	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	Using where
40042	DEPENDENT SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
40053	DEPENDENT SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
40064	DEPENDENT SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
40075	DEPENDENT SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
40086	DEPENDENT SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
40097	DEPENDENT SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
40108	DEPENDENT SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
40119	DEPENDENT SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
401210	DEPENDENT SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
401311	DEPENDENT SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
401412	DEPENDENT SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
401513	DEPENDENT SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
401614	DEPENDENT SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
401715	DEPENDENT SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
401816	DEPENDENT SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
401917	DEPENDENT SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
402018	DEPENDENT SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
402119	DEPENDENT SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
402220	DEPENDENT SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
402321	DEPENDENT SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
402422	DEPENDENT SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
402523	DEPENDENT SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
402624	DEPENDENT SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
402725	DEPENDENT SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
402826	DEPENDENT SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
402927	DEPENDENT SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
403028	DEPENDENT SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
403129	DEPENDENT SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
403230	DEPENDENT SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
4033EXPLAIN SELECT c1 FROM t1 WHERE (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT COUNT(c2))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))) > 0;
4034ERROR HY000: Too high level of nesting for select
4035DROP TABLE t1;
4036CREATE TABLE t1 (
4037c1 int(11) NOT NULL AUTO_INCREMENT,
4038c2 varchar(1000) DEFAULT NULL,
4039c3 bigint(20) DEFAULT NULL,
4040c4 bigint(20) DEFAULT NULL,
4041PRIMARY KEY (c1)
4042);
4043EXPLAIN EXTENDED
4044SELECT  join_2.c1
4045FROM
4046t1 AS join_0,
4047t1 AS join_1,
4048t1 AS join_2,
4049t1 AS join_3,
4050t1 AS join_4,
4051t1 AS join_5,
4052t1 AS join_6,
4053t1 AS join_7
4054WHERE
4055join_0.c1=join_1.c1  AND
4056join_1.c1=join_2.c1  AND
4057join_2.c1=join_3.c1  AND
4058join_3.c1=join_4.c1  AND
4059join_4.c1=join_5.c1  AND
4060join_5.c1=join_6.c1  AND
4061join_6.c1=join_7.c1
4062OR
4063join_0.c2 < '?'  AND
4064join_1.c2 < '?'  AND
4065join_2.c2 > '?'  AND
4066join_2.c2 < '!'  AND
4067join_3.c2 > '?'  AND
4068join_4.c2 = '?'  AND
4069join_5.c2 <> '?' AND
4070join_6.c2 <> '?' AND
4071join_7.c2 >= '?' AND
4072join_0.c1=join_1.c1  AND
4073join_1.c1=join_2.c1  AND
4074join_2.c1=join_3.c1  AND
4075join_3.c1=join_4.c1  AND
4076join_4.c1=join_5.c1  AND
4077join_5.c1=join_6.c1  AND
4078join_6.c1=join_7.c1
4079GROUP BY
4080join_3.c1,
4081join_2.c1,
4082join_7.c1,
4083join_1.c1,
4084join_0.c1;
4085id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
40861	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
4087Warnings:
4088Note	1003	/* select#1 */ select NULL AS `c1` from `test`.`t1` `join_0` join `test`.`t1` `join_1` join `test`.`t1` `join_2` join `test`.`t1` `join_3` join `test`.`t1` `join_4` join `test`.`t1` `join_5` join `test`.`t1` `join_6` join `test`.`t1` `join_7` where 0 group by NULL,NULL,NULL,NULL,NULL
4089SHOW WARNINGS;
4090Level	Code	Message
4091Note	1003	/* select#1 */ select NULL AS `c1` from `test`.`t1` `join_0` join `test`.`t1` `join_1` join `test`.`t1` `join_2` join `test`.`t1` `join_3` join `test`.`t1` `join_4` join `test`.`t1` `join_5` join `test`.`t1` `join_6` join `test`.`t1` `join_7` where 0 group by NULL,NULL,NULL,NULL,NULL
4092DROP TABLE t1;
4093SELECT 1 AS ` `;
4094
40951
4096Warnings:
4097Warning	1474	Name ' ' has become ''
4098SELECT 1 AS `  `;
4099
41001
4101Warnings:
4102Warning	1474	Name '  ' has become ''
4103SELECT 1 AS ` x`;
4104x
41051
4106Warnings:
4107Warning	1466	Leading spaces are removed from name ' x'
4108CREATE VIEW v1 AS SELECT 1 AS ``;
4109ERROR 42000: Incorrect column name ''
4110CREATE VIEW v1 AS SELECT 1 AS ` `;
4111ERROR 42000: Incorrect column name ' '
4112CREATE VIEW v1 AS SELECT 1 AS `  `;
4113ERROR 42000: Incorrect column name '  '
4114CREATE VIEW v1 AS SELECT (SELECT 1 AS `  `);
4115ERROR 42000: Incorrect column name '  '
4116CREATE VIEW v1 AS SELECT 1 AS ` x`;
4117Warnings:
4118Warning	1466	Leading spaces are removed from name ' x'
4119SELECT `x` FROM v1;
4120x
41211
4122ALTER VIEW v1 AS SELECT 1 AS ` `;
4123ERROR 42000: Incorrect column name ' '
4124DROP VIEW v1;
4125select str_to_date('2007-10-09','%Y-%m-%d') between '2007/10/01 00:00:00 GMT'
4126                                                and '2007/10/20 00:00:00 GMT';
4127str_to_date('2007-10-09','%Y-%m-%d') between '2007/10/01 00:00:00 GMT'
4128                                                and '2007/10/20 00:00:00 GMT'
41291
4130Warnings:
4131Warning	1292	Truncated incorrect datetime value: '2007/10/01 00:00:00 GMT'
4132Warning	1292	Truncated incorrect datetime value: '2007/10/20 00:00:00 GMT'
4133select str_to_date('2007-10-09','%Y-%m-%d') > '2007/10/01 00:00:00 GMT-6';
4134str_to_date('2007-10-09','%Y-%m-%d') > '2007/10/01 00:00:00 GMT-6'
41351
4136Warnings:
4137Warning	1292	Truncated incorrect date value: '2007/10/01 00:00:00 GMT-6'
4138select str_to_date('2007-10-09','%Y-%m-%d') <= '2007/10/2000:00:00 GMT-6';
4139str_to_date('2007-10-09','%Y-%m-%d') <= '2007/10/2000:00:00 GMT-6'
41401
4141Warnings:
4142Warning	1292	Truncated incorrect date value: '2007/10/2000:00:00 GMT-6'
4143select str_to_date('2007-10-01','%Y-%m-%d') = '2007-10-1 00:00:00 GMT-6';
4144str_to_date('2007-10-01','%Y-%m-%d') = '2007-10-1 00:00:00 GMT-6'
41451
4146Warnings:
4147Warning	1292	Truncated incorrect date value: '2007-10-1 00:00:00 GMT-6'
4148select str_to_date('2007-10-01','%Y-%m-%d') = '2007-10-01 x00:00:00 GMT-6';
4149str_to_date('2007-10-01','%Y-%m-%d') = '2007-10-01 x00:00:00 GMT-6'
41501
4151Warnings:
4152Warning	1292	Truncated incorrect date value: '2007-10-01 x00:00:00 GMT-6'
4153select str_to_date('2007-10-01','%Y-%m-%d %H:%i:%s') = '2007-10-01 00:00:00 GMT-6';
4154str_to_date('2007-10-01','%Y-%m-%d %H:%i:%s') = '2007-10-01 00:00:00 GMT-6'
41551
4156Warnings:
4157Warning	1292	Truncated incorrect datetime value: '2007-10-01 00:00:00 GMT-6'
4158select str_to_date('2007-10-01','%Y-%m-%d %H:%i:%s') = '2007-10-01 00:x00:00 GMT-6';
4159str_to_date('2007-10-01','%Y-%m-%d %H:%i:%s') = '2007-10-01 00:x00:00 GMT-6'
41601
4161Warnings:
4162Warning	1292	Truncated incorrect datetime value: '2007-10-01 00:x00:00 GMT-6'
4163select str_to_date('2007-10-01','%Y-%m-%d %H:%i:%s') = '2007-10-01 x12:34:56 GMT-6';
4164str_to_date('2007-10-01','%Y-%m-%d %H:%i:%s') = '2007-10-01 x12:34:56 GMT-6'
41651
4166Warnings:
4167Warning	1292	Truncated incorrect datetime value: '2007-10-01 x12:34:56 GMT-6'
4168select str_to_date('2007-10-01 12:34:00','%Y-%m-%d %H:%i:%s') = '2007-10-01 12:34x:56 GMT-6';
4169str_to_date('2007-10-01 12:34:00','%Y-%m-%d %H:%i:%s') = '2007-10-01 12:34x:56 GMT-6'
41701
4171Warnings:
4172Warning	1292	Truncated incorrect datetime value: '2007-10-01 12:34x:56 GMT-6'
4173select str_to_date('2007-10-01 12:34:56','%Y-%m-%d %H:%i:%s') = '2007-10-01 12:34x:56 GMT-6';
4174str_to_date('2007-10-01 12:34:56','%Y-%m-%d %H:%i:%s') = '2007-10-01 12:34x:56 GMT-6'
41750
4176Warnings:
4177Warning	1292	Truncated incorrect datetime value: '2007-10-01 12:34x:56 GMT-6'
4178select str_to_date('2007-10-01 12:34:56','%Y-%m-%d %H:%i:%s') = '2007-10-01 12:34:56';
4179str_to_date('2007-10-01 12:34:56','%Y-%m-%d %H:%i:%s') = '2007-10-01 12:34:56'
41801
4181select str_to_date('2007-10-01','%Y-%m-%d') = '2007-10-01 12:00:00';
4182str_to_date('2007-10-01','%Y-%m-%d') = '2007-10-01 12:00:00'
41830
4184select str_to_date('2007-10-01 12','%Y-%m-%d %H') = '2007-10-01 12:00:00';
4185str_to_date('2007-10-01 12','%Y-%m-%d %H') = '2007-10-01 12:00:00'
41861
4187select str_to_date('2007-10-01 12:34','%Y-%m-%d %H') = '2007-10-01 12:00:00';
4188str_to_date('2007-10-01 12:34','%Y-%m-%d %H') = '2007-10-01 12:00:00'
41891
4190Warnings:
4191Warning	1292	Truncated incorrect datetime value: '2007-10-01 12:34'
4192select str_to_date('2007-02-30 12:34','%Y-%m-%d %H:%i') = '2007-02-30 12:34';
4193str_to_date('2007-02-30 12:34','%Y-%m-%d %H:%i') = '2007-02-30 12:34'
41941
4195select str_to_date('2007-10-00 12:34','%Y-%m-%d %H:%i') = '2007-10-00 12:34';
4196str_to_date('2007-10-00 12:34','%Y-%m-%d %H:%i') = '2007-10-00 12:34'
41971
4198select str_to_date('2007-10-00','%Y-%m-%d') between '2007/09/01 00:00:00'
4199                                                and '2007/10/20 00:00:00';
4200str_to_date('2007-10-00','%Y-%m-%d') between '2007/09/01 00:00:00'
4201                                                and '2007/10/20 00:00:00'
42021
4203set SQL_MODE=TRADITIONAL;
4204select str_to_date('2007-10-00 12:34','%Y-%m-%d %H:%i') = '2007-10-00 12:34';
4205str_to_date('2007-10-00 12:34','%Y-%m-%d %H:%i') = '2007-10-00 12:34'
4206NULL
4207Warnings:
4208Warning	1292	Truncated incorrect datetime value: '2007-10-00 12:34'
4209Warning	1411	Incorrect datetime value: '2007-10-00 12:34' for function str_to_date
4210select str_to_date('2007-10-01 12:34','%Y-%m-%d %H:%i') = '2007-10-00 12:34';
4211str_to_date('2007-10-01 12:34','%Y-%m-%d %H:%i') = '2007-10-00 12:34'
42120
4213Warnings:
4214Warning	1292	Truncated incorrect datetime value: '2007-10-00 12:34'
4215select str_to_date('2007-10-00 12:34','%Y-%m-%d %H:%i') = '2007-10-01 12:34';
4216str_to_date('2007-10-00 12:34','%Y-%m-%d %H:%i') = '2007-10-01 12:34'
4217NULL
4218Warnings:
4219Warning	1411	Incorrect datetime value: '2007-10-00 12:34' for function str_to_date
4220select str_to_date('2007-10-00','%Y-%m-%d') between '2007/09/01'
4221                                                and '2007/10/20';
4222str_to_date('2007-10-00','%Y-%m-%d') between '2007/09/01'
4223                                                and '2007/10/20'
4224NULL
4225Warnings:
4226Warning	1411	Incorrect datetime value: '2007-10-00' for function str_to_date
4227set SQL_MODE=DEFAULT;
4228select str_to_date('2007-10-00','%Y-%m-%d') between '' and '2007/10/20';
4229str_to_date('2007-10-00','%Y-%m-%d') between '' and '2007/10/20'
42301
4231Warnings:
4232Warning	1292	Truncated incorrect datetime value: ''
4233select str_to_date('','%Y-%m-%d') between '2007/10/01' and '2007/10/20';
4234str_to_date('','%Y-%m-%d') between '2007/10/01' and '2007/10/20'
42350
4236select str_to_date('','%Y-%m-%d %H:%i') = '2007-10-01 12:34';
4237str_to_date('','%Y-%m-%d %H:%i') = '2007-10-01 12:34'
42380
4239select str_to_date(NULL,'%Y-%m-%d %H:%i') = '2007-10-01 12:34';
4240str_to_date(NULL,'%Y-%m-%d %H:%i') = '2007-10-01 12:34'
4241NULL
4242select str_to_date('2007-10-00 12:34','%Y-%m-%d %H:%i') = '';
4243str_to_date('2007-10-00 12:34','%Y-%m-%d %H:%i') = ''
42440
4245Warnings:
4246Warning	1292	Truncated incorrect datetime value: ''
4247select str_to_date('1','%Y-%m-%d') = '1';
4248str_to_date('1','%Y-%m-%d') = '1'
42490
4250Warnings:
4251Warning	1292	Truncated incorrect date value: '1'
4252select str_to_date('1','%Y-%m-%d') = '1';
4253str_to_date('1','%Y-%m-%d') = '1'
42540
4255Warnings:
4256Warning	1292	Truncated incorrect date value: '1'
4257select str_to_date('','%Y-%m-%d') = '';
4258str_to_date('','%Y-%m-%d') = ''
42590
4260Warnings:
4261Warning	1292	Truncated incorrect date value: ''
4262select str_to_date('1000-01-01','%Y-%m-%d') between '0000-00-00' and NULL;
4263str_to_date('1000-01-01','%Y-%m-%d') between '0000-00-00' and NULL
42640
4265select str_to_date('1000-01-01','%Y-%m-%d') between NULL and '2000-00-00';
4266str_to_date('1000-01-01','%Y-%m-%d') between NULL and '2000-00-00'
42670
4268select str_to_date('1000-01-01','%Y-%m-%d') between NULL and NULL;
4269str_to_date('1000-01-01','%Y-%m-%d') between NULL and NULL
42700
4271CREATE TABLE t1 (c11 INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY);
4272CREATE TABLE t2 (c21 INT UNSIGNED NOT NULL,
4273c22 INT DEFAULT NULL,
4274KEY(c21, c22));
4275CREATE TABLE t3 (c31 INT UNSIGNED NOT NULL DEFAULT 0,
4276c32 INT DEFAULT NULL,
4277c33 INT NOT NULL,
4278c34 INT UNSIGNED DEFAULT 0,
4279KEY (c33, c34, c32));
4280INSERT INTO t1 values (),(),(),(),();
4281INSERT INTO t2 SELECT a.c11, b.c11 FROM t1 a, t1 b;
4282INSERT INTO t3 VALUES (1, 1, 1, 0),
4283(2, 2, 0, 0),
4284(3, 3, 1, 0),
4285(4, 4, 0, 0),
4286(5, 5, 1, 0);
4287SELECT c32 FROM t1, t2, t3 WHERE t1.c11 IN (1, 3, 5) AND
4288t3.c31 = t1.c11 AND t2.c21 = t1.c11 AND
4289t3.c33 = 1 AND t2.c22 in (1, 3)
4290ORDER BY c32;
4291c32
42921
42931
42943
42953
42965
42975
4298SELECT c32 FROM t1, t2, t3 WHERE t1.c11 IN (1, 3, 5) AND
4299t3.c31 = t1.c11 AND t2.c21 = t1.c11 AND
4300t3.c33 = 1 AND t2.c22 in (1, 3)
4301ORDER BY c32 DESC;
4302c32
43035
43045
43053
43063
43071
43081
4309DROP TABLE t1, t2, t3;
4310
4311#
4312# Bug#30736: Row Size Too Large Error Creating a Table and
4313# Inserting Data.
4314#
4315DROP TABLE IF EXISTS t1;
4316DROP TABLE IF EXISTS t2;
4317
4318CREATE TABLE t1(
4319c1 DECIMAL(10, 2),
4320c2 FLOAT);
4321
4322INSERT INTO t1 VALUES (0, 1), (2, 3), (4, 5);
4323
4324CREATE TABLE t2(
4325c3 DECIMAL(10, 2))
4326SELECT
4327c1 * c2 AS c3
4328FROM t1;
4329
4330SELECT * FROM t1;
4331c1	c2
43320.00	1
43332.00	3
43344.00	5
4335
4336SELECT * FROM t2;
4337c3
43380.00
43396.00
434020.00
4341
4342DROP TABLE t1;
4343DROP TABLE t2;
4344
4345CREATE TABLE t1 (c1 BIGINT NOT NULL);
4346INSERT INTO t1 (c1) VALUES (1);
4347SELECT * FROM t1 WHERE c1 > NULL + 1;
4348c1
4349DROP TABLE t1;
4350
4351CREATE TABLE t1 (a VARCHAR(10) NOT NULL PRIMARY KEY);
4352INSERT INTO t1 (a) VALUES ('foo0'), ('bar0'), ('baz0');
4353SELECT * FROM t1 WHERE a IN (CONCAT('foo', 0), 'bar');
4354a
4355foo0
4356DROP TABLE t1;
4357CREATE TABLE t1 (a INT, b INT);
4358CREATE TABLE t2 (a INT, c INT, KEY(a));
4359INSERT INTO t1 VALUES (1, 1), (2, 2);
4360INSERT INTO t2 VALUES (1, 1), (1, 2), (1, 3), (1, 4), (1, 5),
4361(2, 1), (2, 2), (2, 3), (2, 4), (2, 5),
4362(3, 1), (3, 2), (3, 3), (3, 4), (3, 5),
4363(4, 1), (4, 2), (4, 3), (4, 4), (4, 5);
4364FLUSH STATUS;
4365SELECT DISTINCT b FROM t1 LEFT JOIN t2 USING(a) WHERE c <= 3;
4366b
43671
43682
4369SHOW STATUS LIKE 'Handler_read%';
4370Variable_name	Value
4371Handler_read_first	0
4372Handler_read_key	2
4373Handler_read_last	0
4374Handler_read_next	10
4375Handler_read_prev	0
4376Handler_read_rnd	10
4377Handler_read_rnd_next	7
4378DROP TABLE t1, t2;
4379CREATE TABLE t1 (f1 bigint(20) NOT NULL default '0',
4380f2 int(11) NOT NULL default '0',
4381f3 bigint(20) NOT NULL default '0',
4382f4 varchar(255) NOT NULL default '',
4383PRIMARY KEY (f1),
4384KEY key1 (f4),
4385KEY key2 (f2));
4386CREATE TABLE t2 (f1 int(11) NOT NULL default '0',
4387f2 enum('A1','A2','A3') NOT NULL default 'A1',
4388f3 int(11) NOT NULL default '0',
4389PRIMARY KEY (f1),
4390KEY key1 (f3));
4391CREATE TABLE t3 (f1 bigint(20) NOT NULL default '0',
4392f2 datetime NOT NULL default '1980-01-01 00:00:00',
4393PRIMARY KEY (f1));
4394insert into t1 values (1, 1, 1, 'abc');
4395insert into t1 values (2, 1, 2, 'def');
4396insert into t1 values (3, 1, 2, 'def');
4397insert into t2 values (1, 'A1', 1);
4398insert into t3 values (1, '1980-01-01');
4399SELECT a.f3, cr.f4, count(*) count
4400FROM t2 a
4401STRAIGHT_JOIN t1 cr ON cr.f2 = a.f1
4402LEFT JOIN
4403(t1 cr2
4404JOIN t3 ae2 ON cr2.f3 = ae2.f1
4405) ON a.f1 = cr2.f2 AND ae2.f2 < now() - INTERVAL 7 DAY AND
4406cr.f4 = cr2.f4
4407GROUP BY a.f3, cr.f4;
4408f3	f4	count
44091	abc	1
44101	def	2
4411drop table t1, t2, t3;
4412CREATE TABLE t1 (a INT KEY, b INT);
4413INSERT INTO t1 VALUES (1,1), (2,2), (3,3), (4,4);
4414EXPLAIN EXTENDED SELECT a, b FROM t1 WHERE a > 1 AND a = b LIMIT 2;
4415id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
44161	SIMPLE	t1	range	PRIMARY	PRIMARY	4	NULL	3	100.00	Using index condition; Using where; Using MRR
4417Warnings:
4418Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where ((`test`.`t1`.`b` = `test`.`t1`.`a`) and (`test`.`t1`.`a` > 1)) limit 2
4419EXPLAIN EXTENDED SELECT a, b FROM t1 WHERE a > 1 AND b = a LIMIT 2;
4420id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
44211	SIMPLE	t1	range	PRIMARY	PRIMARY	4	NULL	3	100.00	Using index condition; Using where; Using MRR
4422Warnings:
4423Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where ((`test`.`t1`.`a` = `test`.`t1`.`b`) and (`test`.`t1`.`a` > 1)) limit 2
4424DROP TABLE t1;
4425#
4426# Bug#47019: Assertion failed: 0, file .\rt_mbr.c, line 138 when
4427# forcing a spatial index
4428#
4429CREATE TABLE t1(a LINESTRING NOT NULL, SPATIAL KEY(a));
4430INSERT INTO t1 VALUES
4431(GEOMFROMTEXT('LINESTRING(-1 -1, 1 -1, -1 -1, -1 1, 1 1)')),
4432(GEOMFROMTEXT('LINESTRING(-1 -1, 1 -1, -1 -1, -1 1, 1 1)'));
4433EXPLAIN SELECT 1 FROM t1 NATURAL LEFT JOIN t1 AS t2;
4434id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
44351	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	NULL
44361	SIMPLE	t2	ALL	a	NULL	NULL	NULL	2	Range checked for each record (index map: 0x1)
4437SELECT 1 FROM t1 NATURAL LEFT JOIN t1 AS t2;
44381
44391
44401
44411
44421
4443EXPLAIN SELECT 1 FROM t1 NATURAL LEFT JOIN t1 AS t2 FORCE INDEX(a);
4444id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
44451	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	NULL
44461	SIMPLE	t2	ALL	a	NULL	NULL	NULL	2	Range checked for each record (index map: 0x1)
4447SELECT 1 FROM t1 NATURAL LEFT JOIN t1 AS t2 FORCE INDEX(a);
44481
44491
44501
44511
44521
4453DROP TABLE t1;
4454#
4455# Bug #48291 : crash with row() operator,select into @var, and
4456#   subquery returning multiple rows
4457#
4458CREATE TABLE t1(a INT);
4459INSERT INTO t1 VALUES (2),(3);
4460# Should not crash
4461SELECT 1 FROM t1 WHERE a <> 1 AND NOT
4462ROW(1,a) <=> ROW(1,(SELECT 1 FROM t1))
4463INTO @var0;
4464ERROR 21000: Subquery returns more than 1 row
4465DROP TABLE t1;
4466#
4467# Bug #48458: simple query tries to allocate enormous amount of
4468#   memory
4469#
4470CREATE TABLE t1(a INT NOT NULL, b YEAR);
4471INSERT INTO t1 VALUES ();
4472Warnings:
4473Warning	1364	Field 'a' doesn't have a default value
4474CREATE TABLE t2(c INT);
4475# Should not err out because of out-of-memory
4476SELECT 1 FROM t2 JOIN t1 ON 1=1
4477WHERE a != '1' AND NOT a >= b OR NOT ROW(b,a )<> ROW(a,a);
44781
4479DROP TABLE t1,t2;
4480#
4481# Bug #49199: Optimizer handles incorrectly:
4482# field='const1' AND field='const2' in some cases
4483
4484CREATE TABLE t1(a DATETIME NOT NULL);
4485INSERT INTO t1 VALUES('2001-01-01');
4486SELECT * FROM t1 WHERE a='2001-01-01' AND a='2001-01-01 00:00:00';
4487a
44882001-01-01 00:00:00
4489EXPLAIN EXTENDED SELECT * FROM t1 WHERE a='2001-01-01' AND a='2001-01-01 00:00:00';
4490id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
44911	SIMPLE	t1	system	NULL	NULL	NULL	NULL	1	100.00	NULL
4492Warnings:
4493Note	1003	/* select#1 */ select '2001-01-01 00:00:00' AS `a` from dual where 1
4494DROP TABLE t1;
4495CREATE TABLE t1(a DATE NOT NULL);
4496INSERT INTO t1 VALUES('2001-01-01');
4497SELECT * FROM t1 WHERE a='2001-01-01' AND a='2001-01-01 00:00:00';
4498a
44992001-01-01
4500EXPLAIN EXTENDED SELECT * FROM t1 WHERE a='2001-01-01' AND a='2001-01-01 00:00:00';
4501id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
45021	SIMPLE	t1	system	NULL	NULL	NULL	NULL	1	100.00	NULL
4503Warnings:
4504Note	1003	/* select#1 */ select '2001-01-01' AS `a` from dual where 1
4505DROP TABLE t1;
4506CREATE TABLE t1(a TIMESTAMP NOT NULL NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP);
4507INSERT INTO t1 VALUES('2001-01-01');
4508SELECT * FROM t1 WHERE a='2001-01-01' AND a='2001-01-01 00:00:00';
4509a
45102001-01-01 00:00:00
4511EXPLAIN EXTENDED SELECT * FROM t1 WHERE a='2001-01-01' AND a='2001-01-01 00:00:00';
4512id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
45131	SIMPLE	t1	system	NULL	NULL	NULL	NULL	1	100.00	NULL
4514Warnings:
4515Note	1003	/* select#1 */ select '2001-01-01 00:00:00' AS `a` from dual where 1
4516DROP TABLE t1;
4517CREATE TABLE t1(a DATETIME NOT NULL, b DATE NOT NULL);
4518INSERT INTO t1 VALUES('2001-01-01', '2001-01-01');
4519SELECT * FROM t1 WHERE a='2001-01-01' AND a=b AND b='2001-01-01 00:00:00';
4520a	b
45212001-01-01 00:00:00	2001-01-01
4522EXPLAIN EXTENDED SELECT * FROM t1 WHERE a='2001-01-01' AND a=b AND b='2001-01-01 00:00:00';
4523id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
45241	SIMPLE	t1	system	NULL	NULL	NULL	NULL	1	100.00	NULL
4525Warnings:
4526Note	1003	/* select#1 */ select '2001-01-01 00:00:00' AS `a`,'2001-01-01' AS `b` from dual where 1
4527DROP TABLE t1;
4528CREATE TABLE t1(a DATETIME NOT NULL, b VARCHAR(20) NOT NULL);
4529INSERT INTO t1 VALUES('2001-01-01', '2001-01-01');
4530SELECT * FROM t1 WHERE a='2001-01-01' AND a=b AND b='2001-01-01 00:00:00';
4531a	b
4532EXPLAIN EXTENDED SELECT * FROM t1 WHERE a='2001-01-01' AND a=b AND b='2001-01-01 00:00:00';
4533id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
45341	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
4535Warnings:
4536Note	1003	/* select#1 */ select '2001-01-01 00:00:00' AS `a`,'2001-01-01' AS `b` from dual where 0
4537SELECT * FROM t1 WHERE a='2001-01-01 00:00:00' AND a=b AND b='2001-01-01';
4538a	b
45392001-01-01 00:00:00	2001-01-01
4540EXPLAIN EXTENDED SELECT * FROM t1 WHERE a='2001-01-01 00:00:00' AND a=b AND b='2001-01-01';
4541id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
45421	SIMPLE	t1	system	NULL	NULL	NULL	NULL	1	100.00	NULL
4543Warnings:
4544Note	1003	/* select#1 */ select '2001-01-01 00:00:00' AS `a`,'2001-01-01' AS `b` from dual where 1
4545DROP TABLE t1;
4546CREATE TABLE t1(a DATETIME NOT NULL, b DATE NOT NULL);
4547INSERT INTO t1 VALUES('2001-01-01', '2001-01-01');
4548SELECT x.a, y.a, z.a FROM t1 x
4549JOIN t1 y ON x.a=y.a
4550JOIN t1 z ON y.a=z.a
4551WHERE x.a='2001-01-01' AND z.a='2001-01-01 00:00:00';
4552a	a	a
45532001-01-01 00:00:00	2001-01-01 00:00:00	2001-01-01 00:00:00
4554EXPLAIN EXTENDED SELECT x.a, y.a, z.a FROM t1 x
4555JOIN t1 y ON x.a=y.a
4556JOIN t1 z ON y.a=z.a
4557WHERE x.a='2001-01-01' AND z.a='2001-01-01 00:00:00';
4558id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
45591	SIMPLE	x	system	NULL	NULL	NULL	NULL	1	100.00	NULL
45601	SIMPLE	y	system	NULL	NULL	NULL	NULL	1	100.00	NULL
45611	SIMPLE	z	system	NULL	NULL	NULL	NULL	1	100.00	NULL
4562Warnings:
4563Note	1003	/* select#1 */ select '2001-01-01 00:00:00' AS `a`,'2001-01-01 00:00:00' AS `a`,'2001-01-01 00:00:00' AS `a` from dual where 1
4564DROP TABLE t1;
4565#
4566# Bug #49897: crash in ptr_compare when char(0) NOT NULL
4567# column is used for ORDER BY
4568#
4569SET @old_sort_buffer_size= @@session.sort_buffer_size;
4570SET @@sort_buffer_size= 40000;
4571CREATE TABLE t1(a CHAR(0) NOT NULL);
4572INSERT INTO t1 VALUES (0), (0), (0);
4573INSERT INTO t1 SELECT t11.a FROM t1 t11, t1 t12;
4574INSERT INTO t1 SELECT t11.a FROM t1 t11, t1 t12;
4575INSERT INTO t1 SELECT t11.a FROM t1 t11, t1 t12;
4576EXPLAIN SELECT a FROM t1 ORDER BY a;
4577id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
45781	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	24492	NULL
4579SELECT a FROM t1 ORDER BY a;
4580DROP TABLE t1;
4581CREATE TABLE t1(a CHAR(0) NOT NULL, b CHAR(0) NOT NULL, c int);
4582INSERT INTO t1 VALUES (0, 0, 0), (0, 0, 2), (0, 0, 1);
4583INSERT INTO t1 SELECT t11.a, t11.b, t11.c FROM t1 t11, t1 t12;
4584INSERT INTO t1 SELECT t11.a, t11.b, t11.c FROM t1 t11, t1 t12;
4585INSERT INTO t1 SELECT t11.a, t11.b, t11.c FROM t1 t11, t1 t12;
4586EXPLAIN SELECT a FROM t1 ORDER BY a LIMIT 5;
4587id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
45881	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	24492	NULL
4589SELECT a FROM t1 ORDER BY a LIMIT 5;
4590a
4591
4592
4593
4594
4595
4596EXPLAIN SELECT * FROM t1 ORDER BY a, b LIMIT 5;
4597id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
45981	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	24492	NULL
4599SELECT * FROM t1 ORDER BY a, b LIMIT 5;
4600a	b	c
4601		0
4602		2
4603		1
4604		0
4605		0
4606EXPLAIN SELECT * FROM t1 ORDER BY a, b, c LIMIT 5;
4607id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
46081	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	24492	Using filesort
4609SELECT * FROM t1 ORDER BY a, b, c LIMIT 5;
4610a	b	c
4611		0
4612		0
4613		0
4614		0
4615		0
4616EXPLAIN SELECT * FROM t1 ORDER BY c, a LIMIT 5;
4617id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
46181	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	24492	Using filesort
4619SELECT * FROM t1 ORDER BY c, a LIMIT 5;
4620a	b	c
4621		0
4622		0
4623		0
4624		0
4625		0
4626SET @@sort_buffer_size= @old_sort_buffer_size;
4627DROP TABLE t1;
4628End of 5.0 tests
4629create table t1(a INT, KEY (a));
4630INSERT INTO t1 VALUES (1),(2),(3),(4),(5);
4631SELECT a FROM t1 ORDER BY a LIMIT 2;
4632a
46331
46342
4635SELECT a FROM t1 ORDER BY a LIMIT 2,4294967296;
4636a
46373
46384
46395
4640SELECT a FROM t1 ORDER BY a LIMIT 2,4294967297;
4641a
46423
46434
46445
4645DROP TABLE t1;
4646CREATE TABLE A (date_key date);
4647CREATE TABLE C (
4648pk int,
4649int_nokey int,
4650int_key int,
4651date_key date NOT NULL,
4652date_nokey date,
4653varchar_key varchar(1)
4654);
4655INSERT INTO C VALUES
4656(1,1,1,'0000-00-00',NULL,NULL),
4657(1,1,1,'0000-00-00',NULL,NULL);
4658SELECT 1 FROM C WHERE pk > ANY (SELECT 1 FROM C);
46591
4660SELECT COUNT(DISTINCT 1) FROM C
4661WHERE date_key = (SELECT 1 FROM A WHERE C.date_key IS NULL) GROUP BY pk;
4662COUNT(DISTINCT 1)
4663SELECT date_nokey FROM C
4664WHERE int_key IN (SELECT 1 FROM A)
4665HAVING date_nokey = '10:41:7'
4666ORDER BY date_key;
4667date_nokey
4668Warnings:
4669Warning	1292	Incorrect date value: '10:41:7' for column 'date_nokey' at row 1
4670DROP TABLE A,C;
4671CREATE TABLE t1 (a INT NOT NULL, b INT);
4672INSERT INTO t1 VALUES (1, 1);
4673EXPLAIN EXTENDED SELECT * FROM t1 WHERE (a=a AND a=a) OR b > 2;
4674id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
46751	SIMPLE	t1	system	NULL	NULL	NULL	NULL	1	100.00	NULL
4676Warnings:
4677Note	1003	/* select#1 */ select '1' AS `a`,'1' AS `b` from dual where 1
4678SELECT * FROM t1 WHERE (a=a AND a=a) OR b > 2;
4679a	b
46801	1
4681DROP TABLE t1;
4682CREATE TABLE t1 (a INT NOT NULL, b INT NOT NULL, c INT NOT NULL);
4683EXPLAIN EXTENDED SELECT * FROM t1 WHERE (a=a AND b=b AND c=c) OR b > 20;
4684id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
46851	SIMPLE	t1	system	NULL	NULL	NULL	NULL	0	0.00	const row not found
4686Warnings:
4687Note	1003	/* select#1 */ select NULL AS `a`,NULL AS `b`,NULL AS `c` from `test`.`t1` where 1
4688EXPLAIN EXTENDED SELECT * FROM t1 WHERE (a=a AND a=a AND b=b) OR b > 20;
4689id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
46901	SIMPLE	t1	system	NULL	NULL	NULL	NULL	0	0.00	const row not found
4691Warnings:
4692Note	1003	/* select#1 */ select NULL AS `a`,NULL AS `b`,NULL AS `c` from `test`.`t1` where 1
4693EXPLAIN EXTENDED SELECT * FROM t1 WHERE (a=a AND b=b AND a=a) OR b > 20;
4694id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
46951	SIMPLE	t1	system	NULL	NULL	NULL	NULL	0	0.00	const row not found
4696Warnings:
4697Note	1003	/* select#1 */ select NULL AS `a`,NULL AS `b`,NULL AS `c` from `test`.`t1` where 1
4698DROP TABLE t1;
4699#
4700# Bug#45266: Uninitialized variable lead to an empty result.
4701#
4702drop table if exists A,AA,B,BB;
4703CREATE TABLE `A` (
4704`pk` int(11) NOT NULL AUTO_INCREMENT,
4705`date_key` date NOT NULL,
4706`date_nokey` date NOT NULL,
4707`datetime_key` datetime NOT NULL,
4708`int_nokey` int(11) NOT NULL,
4709`time_key` time NOT NULL,
4710`time_nokey` time NOT NULL,
4711PRIMARY KEY (`pk`),
4712KEY `date_key` (`date_key`),
4713KEY `time_key` (`time_key`),
4714KEY `datetime_key` (`datetime_key`)
4715);
4716CREATE TABLE `AA` (
4717`pk` int(11) NOT NULL AUTO_INCREMENT,
4718`int_nokey` int(11) NOT NULL,
4719`time_key` time NOT NULL,
4720KEY `time_key` (`time_key`),
4721PRIMARY KEY (`pk`)
4722);
4723CREATE TABLE `B` (
4724`date_nokey` date NOT NULL,
4725`date_key` date NOT NULL,
4726`time_key` time NOT NULL,
4727`datetime_nokey` datetime NOT NULL,
4728`varchar_key` varchar(1) NOT NULL,
4729KEY `date_key` (`date_key`),
4730KEY `time_key` (`time_key`),
4731KEY `varchar_key` (`varchar_key`)
4732);
4733INSERT INTO `B` VALUES ('2003-07-28','2003-07-28','15:13:38','0000-00-00 00:00:00','f'),('0000-00-00','0000-00-00','00:05:48','2004-07-02 14:34:13','x');
4734CREATE TABLE `BB` (
4735`pk` int(11) NOT NULL AUTO_INCREMENT,
4736`int_nokey` int(11) NOT NULL,
4737`date_key` date NOT NULL,
4738`varchar_nokey` varchar(1) NOT NULL,
4739`date_nokey` date NOT NULL,
4740PRIMARY KEY (`pk`),
4741KEY `date_key` (`date_key`)
4742);
4743INSERT INTO `BB` VALUES (10,8,'0000-00-00','i','0000-00-00'),(11,0,'2005-08-18','','2005-08-18');
4744SELECT table1 . `pk` AS field1
4745FROM
4746(BB AS table1 INNER JOIN
4747(AA AS table2 STRAIGHT_JOIN A AS table3
4748ON ( table3 . `date_key` = table2 . `pk` ))
4749ON ( table3 . `datetime_key` = table2 . `int_nokey` ))
4750WHERE  ( table3 . `date_key` <= 4 AND table2 . `pk` = table1 . `varchar_nokey`)
4751GROUP BY field1 ;
4752field1
4753SELECT table3 .`date_key` field1
4754FROM
4755B table1 LEFT JOIN B table3 JOIN
4756(BB table6 JOIN A table7 ON table6 .`varchar_nokey`)
4757ON table6 .`int_nokey` ON table6 .`date_key`
4758  WHERE  NOT ( table1 .`varchar_key`  AND table7 .`pk`) GROUP  BY field1;
4759field1
4760NULL
4761SELECT table4 . `time_nokey` AS field1 FROM
4762(AA AS table1 CROSS JOIN
4763(AA AS table2 STRAIGHT_JOIN
4764(B AS table3 STRAIGHT_JOIN A AS table4
4765ON ( table4 . `date_key` = table3 . `time_key` ))
4766ON ( table4 . `pk` = table3 . `date_nokey` ))
4767ON ( table4 . `time_key` = table3 . `datetime_nokey` ))
4768WHERE  ( table4 . `time_key` < table1 . `time_key` AND
4769table1 . `int_nokey` != 'f')
4770GROUP BY field1  ORDER BY field1 , field1;
4771field1
4772SELECT table1 .`time_key` field2  FROM B table1  LEFT JOIN  BB JOIN A table5 ON table5 .`date_nokey`  ON table5 .`int_nokey` GROUP  BY field2;
4773field2
477400:05:48
477515:13:38
4776drop table A,AA,B,BB;
4777#end of test for bug#45266
4778#
4779# Bug#33546: Slowdown on re-evaluation of constant expressions.
4780#
4781CREATE TABLE t1 (a INT);
4782INSERT INTO t1 VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);
4783CREATE TABLE t2 (b INT);
4784INSERT INTO t2 VALUES (2);
4785SELECT * FROM t1 WHERE a = 1 + 1;
4786a
47872
4788EXPLAIN EXTENDED SELECT * FROM t1 WHERE a = 1 + 1;
4789id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
47901	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	10	100.00	Using where
4791Warnings:
4792Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`a` = <cache>((1 + 1)))
4793SELECT * FROM t1 HAVING a = 1 + 1;
4794a
47952
4796EXPLAIN EXTENDED SELECT * FROM t1 HAVING a = 1 + 1;
4797id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
47981	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	10	100.00	NULL
4799Warnings:
4800Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` having (`test`.`t1`.`a` = <cache>((1 + 1)))
4801SELECT * FROM t1, t2 WHERE a = b + (1 + 1);
4802a	b
48034	2
4804EXPLAIN EXTENDED SELECT * FROM t1, t2 WHERE a = b + (1 + 1);
4805id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
48061	SIMPLE	t2	system	NULL	NULL	NULL	NULL	1	100.00	NULL
48071	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	10	100.00	Using where
4808Warnings:
4809Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a`,'2' AS `b` from `test`.`t1` where (`test`.`t1`.`a` = <cache>(('2' + (1 + 1))))
4810SELECT * FROM t2 LEFT JOIN t1 ON a = b + 1;
4811b	a
48122	3
4813EXPLAIN EXTENDED SELECT * FROM t2 LEFT JOIN t1 ON a = b + 1;
4814id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
48151	SIMPLE	t2	system	NULL	NULL	NULL	NULL	1	100.00	NULL
48161	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	10	100.00	Using where
4817Warnings:
4818Note	1003	/* select#1 */ select '2' AS `b`,`test`.`t1`.`a` AS `a` from `test`.`t1` where 1
4819EXPLAIN EXTENDED SELECT * FROM t1 WHERE a > UNIX_TIMESTAMP('2009-03-10 00:00:00');
4820id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
48211	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	10	100.00	Using where
4822Warnings:
4823Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`a` > <cache>(unix_timestamp('2009-03-10 00:00:00')))
4824CREATE FUNCTION f1() RETURNS INT DETERMINISTIC
4825BEGIN
4826SET @cnt := @cnt + 1;
4827RETURN 1;
4828END;|
4829SET @cnt := 0;
4830SELECT * FROM t1 WHERE a = f1();
4831a
48321
4833SELECT @cnt;
4834@cnt
48351
4836EXPLAIN EXTENDED SELECT * FROM t1 WHERE a = f1();
4837id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
48381	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	10	100.00	Using where
4839Warnings:
4840Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`a` = <cache>(`f1`()))
4841DROP TABLE t1, t2;
4842DROP FUNCTION f1;
4843# End of bug#33546
4844#
4845# BUG#48052: Valgrind warning - uninitialized value in init_read_record()
4846#
4847# Disable Index condition pushdown
4848SELECT @old_optimizer_switch:=@@optimizer_switch;
4849@old_optimizer_switch:=@@optimizer_switch
4850#
4851CREATE TABLE t1 (
4852pk int(11) NOT NULL,
4853i int(11) DEFAULT NULL,
4854v varchar(1) DEFAULT NULL,
4855PRIMARY KEY (pk)
4856);
4857INSERT INTO t1 VALUES (2,7,'m');
4858INSERT INTO t1 VALUES (3,9,'m');
4859SELECT  v
4860FROM t1
4861WHERE NOT pk > 0
4862HAVING v <= 't'
4863ORDER BY pk;
4864v
4865# Restore old value for Index condition pushdown
4866SET SESSION optimizer_switch=@old_optimizer_switch;
4867DROP TABLE t1;
4868#
4869# Bug#49489 Uninitialized cache led to a wrong result.
4870#
4871CREATE TABLE t1(c1 DOUBLE(5,4));
4872INSERT INTO t1 VALUES (9.1234);
4873SELECT * FROM t1 WHERE c1 < 9.12345;
4874c1
48759.1234
4876DROP TABLE t1;
4877# End of test for bug#49489.
4878#
4879# Bug #49517: Inconsistent behavior while using
4880# NULLable BIGINT and INT columns in comparison
4881#
4882CREATE TABLE t1(a BIGINT UNSIGNED NOT NULL, b BIGINT NULL, c INT NULL);
4883INSERT INTO t1 VALUES(105, NULL, NULL);
4884SELECT * FROM t1 WHERE b < 102;
4885a	b	c
4886SELECT * FROM t1 WHERE c < 102;
4887a	b	c
4888SELECT * FROM t1 WHERE 102 < b;
4889a	b	c
4890SELECT * FROM t1 WHERE 102 < c;
4891a	b	c
4892DROP TABLE t1;
4893#
4894# Bug #54459: Assertion failed: param.sort_length,
4895# file .\filesort.cc, line 149 (part II)
4896#
4897CREATE TABLE t1(a ENUM('') NOT NULL);
4898INSERT INTO t1 VALUES (), (), ();
4899EXPLAIN SELECT 1 FROM t1 ORDER BY a COLLATE latin1_german2_ci;
4900id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
49011	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	NULL
4902SELECT 1 FROM t1 ORDER BY a COLLATE latin1_german2_ci;
49031
49041
49051
49061
4907DROP TABLE t1;
4908#
4909# Bug #58422: Incorrect result when OUTER JOIN'ing
4910# with an empty table
4911#
4912CREATE TABLE t_empty(pk INT PRIMARY KEY, i INT) ENGINE = MYISAM;
4913CREATE TABLE t1(pk INT PRIMARY KEY, i INT) ENGINE = MYISAM;
4914INSERT INTO t1 VALUES (1,1), (2,2), (3,3);
4915CREATE TABLE t2(pk INT PRIMARY KEY, i INT) ENGINE = MYISAM;
4916INSERT INTO t2 VALUES (1,1), (2,2), (3,3);
4917EXPLAIN
4918SELECT *
4919FROM
4920t1
4921LEFT OUTER JOIN
4922(t2 INNER JOIN t_empty ON TRUE)
4923ON t1.pk=t2.pk
4924WHERE t2.pk <> 2;
4925id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
49261	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
4927SELECT *
4928FROM
4929t1
4930LEFT OUTER JOIN
4931(t2 INNER JOIN t_empty ON TRUE)
4932ON t1.pk=t2.pk
4933WHERE t2.pk <> 2;
4934pk	i	pk	i	pk	i
4935EXPLAIN
4936SELECT *
4937FROM
4938t1
4939LEFT OUTER JOIN
4940(t2 CROSS JOIN t_empty)
4941ON t1.pk=t2.pk
4942WHERE t2.pk <> 2;
4943id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
49441	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
4945SELECT *
4946FROM
4947t1
4948LEFT OUTER JOIN
4949(t2 CROSS JOIN t_empty)
4950ON t1.pk=t2.pk
4951WHERE t2.pk <> 2;
4952pk	i	pk	i	pk	i
4953EXPLAIN
4954SELECT *
4955FROM
4956t1
4957LEFT OUTER JOIN
4958(t2 INNER JOIN t_empty ON t_empty.i=t2.i)
4959ON t1.pk=t2.pk
4960WHERE t2.pk <> 2;
4961id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
49621	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
4963SELECT *
4964FROM
4965t1
4966LEFT OUTER JOIN
4967(t2 INNER JOIN t_empty ON t_empty.i=t2.i)
4968ON t1.pk=t2.pk
4969WHERE t2.pk <> 2;
4970pk	i	pk	i	pk	i
4971DROP TABLE t1,t2,t_empty;
4972End of 5.1 tests
4973#
4974# Bug#45227: Lost HAVING clause led to a wrong result.
4975#
4976CREATE TABLE `CC` (
4977`int_nokey` int(11) NOT NULL,
4978`int_key` int(11) NOT NULL,
4979`varchar_key` varchar(1) NOT NULL,
4980`varchar_nokey` varchar(1) NOT NULL,
4981KEY `int_key` (`int_key`),
4982KEY `varchar_key` (`varchar_key`)
4983);
4984INSERT INTO `CC` VALUES
4985(0,8,'q','q'),(5,8,'m','m'),(7,3,'j','j'),(1,2,'z','z'),(8,2,'a','a'),(2,6,'',''),(1,8,'e'
4986,'e'),(8,9,'t','t'),(5,2,'q','q'),(4,6,'b','b'),(5,5,'w','w'),(3,2,'m','m'),(0,4,'x','x'),
4987(8,9,'',''),(0,6,'w','w'),(4,5,'x','x'),(0,0,'e','e'),(0,0,'e','e'),(2,8,'p','p'),(0,0,'x'
4988,'x');
4989EXPLAIN SELECT `varchar_nokey` G1  FROM CC  WHERE `int_nokey` AND `int_key`  <= 4
4990HAVING G1  ORDER  BY `varchar_key` LIMIT  6   ;
4991id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
49921	SIMPLE	CC	range	int_key	int_key	4	NULL	10	Using index condition; Using where; Using MRR; Using filesort
4993SELECT `varchar_nokey` G1  FROM CC  WHERE `int_nokey` AND `int_key`  <= 4
4994HAVING G1  ORDER  BY `varchar_key` LIMIT  6   ;
4995G1
4996Warnings:
4997Warning	1292	Truncated incorrect DOUBLE value: 'j'
4998Warning	1292	Truncated incorrect DOUBLE value: 'z'
4999Warning	1292	Truncated incorrect DOUBLE value: 'a'
5000Warning	1292	Truncated incorrect DOUBLE value: 'q'
5001Warning	1292	Truncated incorrect DOUBLE value: 'm'
5002DROP TABLE CC;
5003# End of test#45227
5004#
5005# Bug#54515: Crash in opt_range.cc::get_best_group_min_max on
5006#            SELECT from VIEW with GROUP BY
5007#
5008CREATE TABLE t1 (
5009col_int_key int DEFAULT NULL,
5010KEY int_key (col_int_key)
5011) ;
5012INSERT INTO t1 VALUES (1),(2);
5013CREATE VIEW view_t1 AS
5014SELECT t1.col_int_key AS col_int_key
5015FROM t1;
5016SELECT col_int_key FROM view_t1 GROUP BY col_int_key;
5017col_int_key
50181
50192
5020DROP VIEW view_t1;
5021DROP TABLE t1;
5022# End of test BUG#54515
5023#
5024# Bug #57203 Assertion `field_length <= 255' failed.
5025#
5026SELECT coalesce((avg(distinct (geomfromtext("point(25379 -22010)")))))
5027UNION ALL
5028SELECT coalesce((avg(distinct (geomfromtext("point(25379 -22010)")))))
5029AS foo
5030;
5031coalesce((avg(distinct (geomfromtext("point(25379 -22010)")))))
50320.0000
50330.0000
5034CREATE table t1(a text);
5035INSERT INTO t1 VALUES (''), ('');
5036SELECT avg(distinct(t1.a)) FROM t1, t1 t2
5037GROUP BY t2.a ORDER BY t1.a;
5038avg(distinct(t1.a))
50390
5040DROP TABLE t1;
5041# End of test BUG#57203
5042#
5043# Bug#63020: Function "format"'s 'locale' argument is not considered
5044#	     when creating a "view'
5045#
5046CREATE TABLE t1 (f1 DECIMAL(10,2));
5047INSERT INTO t1 VALUES (11.67),(17865.3),(12345678.92);
5048CREATE VIEW view_t1 AS SELECT FORMAT(f1,1,'sk_SK') AS f1 FROM t1;
5049SHOW CREATE VIEW view_t1;
5050View	Create View	character_set_client	collation_connection
5051view_t1	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `view_t1` AS select format(`t1`.`f1`,1,'sk_SK') AS `f1` from `t1`	latin1	latin1_swedish_ci
5052SELECT * FROM view_t1;
5053f1
505411,7
505517 865,3
505612 345 678,9
5057DROP TABLE t1;
5058DROP VIEW view_t1;
5059# End of test  BUG#63020
5060#
5061# Bug #13571700 TINYBLOB NOT NULL, CRASH IN PROTOCOL::NET_STORE_DATA
5062#
5063CREATE TABLE t1 (a TINYBLOB NOT NULL);
5064SELECT a, COUNT(*) FROM t1 WHERE 0;
5065a	COUNT(*)
5066NULL	0
5067DROP TABLE t1;
5068# End of test BUG#13571700
5069set optimizer_switch=default;
5070set optimizer_switch=default;
5071