summaryrefslogtreecommitdiff
path: root/mysql-test/suite/gcol/r/gcol_keys_myisam.result
blob: a91a77aedf3a050f98840394a28720d526ea7188 (plain)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
817
818
819
820
821
822
823
824
825
826
827
828
829
830
831
832
833
834
835
836
837
838
839
840
841
842
843
844
845
846
847
848
849
850
851
852
853
854
855
856
857
858
859
860
861
862
863
864
865
866
867
868
869
870
871
872
873
874
875
876
877
878
879
880
881
882
883
884
885
886
SET @@session.default_storage_engine = 'MyISAM';
#            - UNIQUE KEY
#            - INDEX
#            - FULLTEXT INDEX
#            - SPATIAL INDEX (not supported)
#            - FOREIGN INDEX (partially supported)
#            - CHECK (allowed but not used)
# UNIQUE
create table t1 (a int, b int generated always as (a*2) virtual unique);
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` int(11) DEFAULT NULL,
  `b` int(11) GENERATED ALWAYS AS (`a` * 2) VIRTUAL,
  UNIQUE KEY `b` (`b`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
describe t1;
Field	Type	Null	Key	Default	Extra
a	int(11)	YES		NULL	
b	int(11)	YES	UNI	NULL	VIRTUAL GENERATED
drop table t1;
create table t1 (a int, b int generated always as (a*2) stored unique);
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` int(11) DEFAULT NULL,
  `b` int(11) GENERATED ALWAYS AS (`a` * 2) STORED,
  UNIQUE KEY `b` (`b`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
describe t1;
Field	Type	Null	Key	Default	Extra
a	int(11)	YES		NULL	
b	int(11)	YES	UNI	NULL	STORED GENERATED
drop table t1;
create table t1 (a int, b int generated always as (a*2) virtual, unique key (b));
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` int(11) DEFAULT NULL,
  `b` int(11) GENERATED ALWAYS AS (`a` * 2) VIRTUAL,
  UNIQUE KEY `b` (`b`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
describe t1;
Field	Type	Null	Key	Default	Extra
a	int(11)	YES		NULL	
b	int(11)	YES	UNI	NULL	VIRTUAL GENERATED
drop table t1;
create table t1 (a int, b int generated always as (a*2) stored, unique (b));
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` int(11) DEFAULT NULL,
  `b` int(11) GENERATED ALWAYS AS (`a` * 2) STORED,
  UNIQUE KEY `b` (`b`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
describe t1;
Field	Type	Null	Key	Default	Extra
a	int(11)	YES		NULL	
b	int(11)	YES	UNI	NULL	STORED GENERATED
drop table t1;
create table t1 (a int, b int generated always as (a*2) virtual);
alter table t1 add unique key (b);
drop table t1;
create table t1 (a int, b int generated always as (a*2) stored);
alter table t1 add unique key (b);
drop table t1;
# Testing data manipulation operations involving UNIQUE keys
# on generated columns can be found in:
#  - gcol_ins_upd.inc
#  - gcol_select.inc
#
# INDEX
create table t1 (a int, b int generated always as (a*2) virtual, index (b));
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` int(11) DEFAULT NULL,
  `b` int(11) GENERATED ALWAYS AS (`a` * 2) VIRTUAL,
  KEY `b` (`b`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
describe t1;
Field	Type	Null	Key	Default	Extra
a	int(11)	YES		NULL	
b	int(11)	YES	MUL	NULL	VIRTUAL GENERATED
drop table t1;
create table t1 (a int, b int generated always as (a*2) virtual, index (a,b));
drop table t1;
create table t1 (a int, b int generated always as (a*2) stored, index (b));
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` int(11) DEFAULT NULL,
  `b` int(11) GENERATED ALWAYS AS (`a` * 2) STORED,
  KEY `b` (`b`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
describe t1;
Field	Type	Null	Key	Default	Extra
a	int(11)	YES		NULL	
b	int(11)	YES	MUL	NULL	STORED GENERATED
drop table t1;
create table t1 (a int, b int generated always as (a*2) stored, index (a,b));
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` int(11) DEFAULT NULL,
  `b` int(11) GENERATED ALWAYS AS (`a` * 2) STORED,
  KEY `a` (`a`,`b`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
describe t1;
Field	Type	Null	Key	Default	Extra
a	int(11)	YES	MUL	NULL	
b	int(11)	YES		NULL	STORED GENERATED
drop table t1;
create table t1 (a int, b int generated always as (a*2) virtual);
alter table t1 add index (b);
alter table t1 add index (a,b);
drop table t1;
create table t1 (a int, b int generated always as (a*2) stored);
alter table t1 add index (b);
drop table t1;
create table t1 (a int, b int generated always as (a*2) stored);
alter table t1 add index (a,b);
create table t2 like t1;
drop table t2;
drop table t1;
# Testing data manipulation operations involving INDEX
# on generated columns can be found in:
#  - gcol_select.inc
#
# TODO: FULLTEXT INDEX
# SPATIAL INDEX
# Error "All parts of a SPATIAL index must be geometrical"
create table t1 (a int, b int generated always as (a+1) stored, spatial index (b));
ERROR HY000: Incorrect arguments to SPATIAL INDEX
create table t1 (a int, b int generated always as (a+1) stored);
alter table t1 add spatial index (b);
ERROR HY000: Incorrect arguments to SPATIAL INDEX
drop table t1;
# FOREIGN KEY
# Rejected FK options.
create table t1 (a int, b int generated always as (a+1) stored,
foreign key (b) references t2(a) on update set null);
ERROR HY000: Cannot define foreign key with ON UPDATE SET NULL clause on a generated column
create table t1 (a int, b int generated always as (a+1) stored,
foreign key (b) references t2(a) on update cascade);
ERROR HY000: Cannot define foreign key with ON UPDATE CASCADE clause on a generated column
create table t1 (a int, b int generated always as (a+1) stored,
foreign key (b) references t2(a) on delete set null);
ERROR HY000: Cannot define foreign key with ON DELETE SET NULL clause on a generated column
create table t1 (a int, b int generated always as (a+1) stored);
alter table t1 add foreign key (b) references t2(a) on update set null;
ERROR HY000: Cannot define foreign key with ON UPDATE SET NULL clause on a generated column
alter table t1 add foreign key (b) references t2(a) on update cascade;
ERROR HY000: Cannot define foreign key with ON UPDATE CASCADE clause on a generated column
alter table t1 add foreign key (b) references t2(a) on delete set null;
ERROR HY000: Cannot define foreign key with ON DELETE SET NULL clause on a generated column
drop table t1;
# Allowed FK options.
create table t2 (a int primary key, b char(5));
create table t1 (a int, b int generated always as (a % 10) stored,
foreign key (b) references t2(a) on update restrict);
drop table t1;
create table t1 (a int, b int generated always as (a % 10) stored,
foreign key (b) references t2(a) on update no action);
drop table t1;
create table t1 (a int, b int generated always as (a % 10) stored,
foreign key (b) references t2(a) on delete restrict);
drop table t1;
create table t1 (a int, b int generated always as (a % 10) stored,
foreign key (b) references t2(a) on delete cascade);
drop table t1;
create table t1 (a int, b int generated always as (a % 10) stored,
foreign key (b) references t2(a) on delete no action);
drop table t1,t2;
#
# Bug#20553262: WL8149: ASSERTION `DELSUM+(INT) Y/4-TEMP >= 0' FAILED
#
CREATE TABLE c (
pk integer AUTO_INCREMENT,
col_datetime_nokey DATETIME /*! NULL */,
col_time_nokey TIME /*! NULL */,
col_datetime_key DATETIME GENERATED ALWAYS AS
(ADDTIME(col_datetime_nokey, col_time_nokey)),
col_time_key TIME GENERATED ALWAYS AS
(ADDTIME(col_datetime_nokey, col_time_nokey)),
col_varchar_nokey VARCHAR(1) /*! NULL */,
PRIMARY KEY (pk),
KEY (col_time_key),
KEY (col_datetime_key));
INSERT INTO c ( col_time_nokey,col_datetime_nokey,col_varchar_nokey) values
('14:03:03.042673','2001-11-28 00:50:27.051028', 'c'),
('01:46:09.016386','2007-10-09 19:53:04.008332', NULL),
('16:21:18.052408','2001-11-08 21:02:12.009395', 'x'),
('18:56:33.027423','2003-04-01 00:00:00', 'i');
Warnings:
Note	1265	Data truncated for column 'col_time_key' at row 1
Note	1265	Data truncated for column 'col_time_key' at row 2
Note	1265	Data truncated for column 'col_time_key' at row 3
Note	1265	Data truncated for column 'col_time_key' at row 4
EXPLAIN SELECT
outr.col_time_key AS x
FROM c as outr
WHERE
outr.col_varchar_nokey in ('c', 'x', 'i')
AND (outr.col_time_key IS NULL OR
outr.col_datetime_key = '2009-09-27');
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	outr	ALL	col_time_key,col_datetime_key	NULL	NULL	NULL	4	x
SELECT
outr.col_time_key AS x
FROM c AS outr
WHERE
outr.col_varchar_nokey in ('c', 'x', 'i')
AND (outr.col_time_key IS NULL OR
outr.col_datetime_key = '2009-09-27');
x
DROP TABLE c;
#
# Bug#20913803: WL8149: SIG 11 IN DFIELD_DUP |
# INNOBASE/INCLUDE/DATA0DATA.IC:253
#
CREATE TABLE A (
col_varchar_nokey TEXT ,
col_varchar_key TEXT GENERATED ALWAYS AS (REPEAT(col_varchar_nokey, 1000)),
KEY (col_varchar_key(50))
);
INSERT INTO A (col_varchar_nokey) VALUES ('');
CREATE TABLE D (
pk INTEGER AUTO_INCREMENT,
col_date_nokey BLOB,
col_date_key BLOB GENERATED ALWAYS AS (REPEAT(col_date_nokey,1000)) VIRTUAL,
col_datetime_nokey LONGBLOB,
col_time_nokey LONGTEXT,
col_datetime_key LONGBLOB GENERATED ALWAYS AS (REPEAT(col_datetime_nokey, 1000)),
col_time_key LONGTEXT GENERATED ALWAYS AS (REPEAT(col_datetime_nokey, 1000)),
col_varchar_nokey TEXT,
col_varchar_key TEXT GENERATED ALWAYS AS (REPEAT(col_varchar_nokey, 1000)),
PRIMARY KEY (pk),
KEY (col_varchar_key(50)),
KEY (col_date_key(20)),
KEY (col_time_key(20)),
KEY (col_datetime_key(20)),
KEY (col_varchar_key(10), col_date_key(10), col_time_key(5), col_datetime_key(5))
);
INSERT INTO D (
col_date_nokey,
col_time_nokey,
col_datetime_nokey,
col_varchar_nokey
) VALUES ('', '', '', ''),('', '', '', '');
DELETE FROM OUTR1.* USING D AS OUTR1 RIGHT JOIN A AS OUTR2 ON
( OUTR1 . `col_varchar_nokey` = OUTR2 . `col_varchar_nokey` );
DROP TABLE IF EXISTS A,D;
#
# Bug#21024896: SIG 11 INNOBASE_ADD_ONE_VIRTUAL |
# INNOBASE/HANDLER/HANDLER0ALTER.CC
#
CREATE TABLE t1 (
col1 int(11) DEFAULT NULL,
col2 int(11) DEFAULT NULL,
col3 int(11) NOT NULL,
col4 int(11) DEFAULT NULL,
col5 int(11) GENERATED ALWAYS AS (col2 / col2) VIRTUAL,
col7 int(11) GENERATED ALWAYS AS (col5 + col5) VIRTUAL,
col8 int(11) GENERATED ALWAYS AS (col5 * col5) VIRTUAL,
col9 text,
col6 int(11) DEFAULT NULL,
PRIMARY KEY (`col3`),
UNIQUE KEY uidx (`col2`),
KEY idx (`col5`)
);
INSERT INTO t1(col1,col2,col3,col4,col9,col6)
VALUES(1,1,0,1,REPEAT(col1,1000),0), (3,2,1,1,REPEAT(col1,1000),NULL);
ALTER TABLE t1 ADD COLUMN extra INT;
DROP TABLE t1;
#
# Bug#21316860: WL8149:INNODB: FAILING ASSERTION:
# TEMPL->CLUST_REC_FIELD_NO != ULINT_UNDEFINED
#
CREATE TABLE t1 (
pk int(11) NOT NULL,
col_int_nokey int(11),
col_int_key int(11) GENERATED ALWAYS AS (col_int_nokey) VIRTUAL,
col_date_nokey date,
col_date_key date GENERATED ALWAYS AS (col_date_nokey) VIRTUAL,
PRIMARY KEY (pk),
UNIQUE KEY col_int_key (col_int_key)
);
ALTER TABLE t1 DROP COLUMN pk;
DROP TABLE t1;
# Remove the impact on PK choose by index on virtual generated column
CREATE TABLE t1 (
pk int(11) NOT NULL,
col_int_nokey int(11) DEFAULT NULL,
col_int_key int(11) GENERATED ALWAYS AS (col_int_nokey) VIRTUAL,
UNIQUE KEY col_int_key (col_int_key)
);
ALTER TABLE t1 add unique index idx(pk);
DESC t1;
Field	Type	Null	Key	Default	Extra
pk	int(11)	NO	PRI	NULL	
col_int_nokey	int(11)	YES		NULL	
col_int_key	int(11)	YES	UNI	NULL	VIRTUAL GENERATED
DROP TABLE t1;
#
# Bug#21320151 WL8149: WRONG RESULT WITH INDEX SCAN
#
CREATE TABLE t1 (
id INTEGER NOT NULL,
b INTEGER GENERATED ALWAYS AS (id+1) VIRTUAL,
UNIQUE KEY (b)
);
INSERT INTO t1 (id) VALUES (2),(3),(4),(5),(6),(7),(8),(9),(10);
EXPLAIN SELECT b FROM t1 FORCE INDEX(b);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	index	NULL	b	5	NULL	9	Using index
SELECT b FROM t1 FORCE INDEX(b);
b
3
4
5
6
7
8
9
10
11
EXPLAIN SELECT b FROM t1 FORCE INDEX(b) WHERE b BETWEEN 1 AND 5;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	range	b	b	5	NULL	3	Using where; Using index
SELECT b FROM t1 FORCE INDEX(b) WHERE b BETWEEN 1 AND 5;
b
3
4
5
DROP TABLE t1;

# Testing data manipulation operations involving FOREIGN KEY
# on generated columns can be found in:
#  - gcol_ins_upd.inc
#  - gcol_select.inc
#
# TODO: CHECK
#
# Test how optimizer picks indexes defined on a GC
#
CREATE TABLE t1 (f1 int, gc int AS (f1 + 1) STORED, UNIQUE(gc));
INSERT INTO t1(f1) VALUES (1),(2),(0),(9),(3),(4),(8),(7),(5),(6);
ANALYZE TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	analyze	status	OK
# Should use index
SELECT * FROM t1 WHERE f1 + 1 > 7;
f1	gc
7	8
8	9
9	10
EXPLAIN SELECT * FROM t1 WHERE f1 + 1 > 7;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	10	Using where
SELECT * FROM t1 WHERE f1 + 1 = 7;
f1	gc
6	7
EXPLAIN SELECT * FROM t1 WHERE f1 + 1 = 7;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	10	Using where
SELECT * FROM t1 WHERE f1 + 1 IN (7,5);
f1	gc
4	5
6	7
EXPLAIN SELECT * FROM t1 WHERE f1 + 1 IN(7,5);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	10	Using where
SELECT * FROM t1 WHERE f1 + 1 BETWEEN 5 AND 7;
f1	gc
4	5
5	6
6	7
EXPLAIN SELECT * FROM t1 WHERE f1 + 1 BETWEEN 5 AND 7;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	10	Using where
# Check that expression isn't transformed for a disabled key
SELECT * FROM t1 IGNORE KEY (gc) WHERE f1 + 1 BETWEEN 5 AND 7;
f1	gc
4	5
5	6
6	7
EXPLAIN SELECT * FROM t1 IGNORE KEY (gc) WHERE f1 + 1 BETWEEN 5 AND 7;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	10	Using where
# Check that ORDER BY could be optimized
SELECT * FROM t1 ORDER BY f1 + 1;
f1	gc
0	1
1	2
2	3
3	4
4	5
5	6
6	7
7	8
8	9
9	10
EXPLAIN SELECT * FROM t1 ORDER BY f1 + 1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	10	Using filesort
EXPLAIN SELECT * FROM t1 IGNORE KEY (gc) ORDER BY f1 + 1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	10	Using filesort
# Check that GROUP BY could be optimized
SELECT f1 + 1, MAX(GC) FROM t1 GROUP BY f1 + 1;
f1 + 1	MAX(GC)
1	1
2	2
3	3
4	4
5	5
6	6
7	7
8	8
9	9
10	10
EXPLAIN SELECT f1 + 1, MAX(GC) FROM t1 GROUP BY f1 + 1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	10	Using temporary; Using filesort
EXPLAIN SELECT f1 + 1, MAX(GC)
FROM t1 IGNORE KEY (gc) GROUP BY f1 + 1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	10	Using temporary; Using filesort
# Shouldn't use index
SELECT * FROM t1 WHERE f1 + 1 > 7.0;
f1	gc
7	8
8	9
9	10
EXPLAIN SELECT * FROM t1 WHERE f1 + 1 > 7.0;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	10	Using where
DROP TABLE t1;
# Pick index with proper type
CREATE TABLE t1 (f1 int,
gc_int int AS (f1 + 1) STORED,
gc_date DATE AS (f1 + 1) STORED,
KEY gc_int_idx(gc_int),
KEY gc_date_idx(gc_date));
INSERT INTO t1(f1) VALUES
(030303),(040404),
(050505),(060606),
(010101),(020202),
(030303),(040404),
(050505),(060606),
(010101),(020202),
(090909),(101010),
(010101),(020202),
(070707),(080808);
ANALYZE TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	analyze	status	OK
SELECT * FROM t1 WHERE f1 + 1 > 070707;
f1	gc_int	gc_date
101010	101011	2010-10-11
70707	70708	2007-07-08
80808	80809	2008-08-09
90909	90910	2009-09-10
# INT column & index should be picked
EXPLAIN SELECT * FROM t1 WHERE f1 + 1 > 070707;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	18	Using where
SELECT * FROM t1 WHERE f1 + 1 > CAST(070707 AS DATE);
f1	gc_int	gc_date
101010	101011	2010-10-11
70707	70708	2007-07-08
80808	80809	2008-08-09
90909	90910	2009-09-10
# DATE column & index should be picked
EXPLAIN SELECT * FROM t1 WHERE f1 + 1 > CAST(070707 AS DATE);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	18	Using where
DROP TABLE t1;
#
# BUG#21229846: WL8170: SIGNAL 11 IN JOIN::MAKE_SUM_FUNC_LIST
#
CREATE TABLE t1 (
pk int primary key auto_increment,
col_int_key INTEGER ,
col_int_gc_key INT GENERATED ALWAYS AS (col_int_key + 1) STORED,
KEY col_int_gc_key(col_int_gc_key)
);
INSERT INTO t1 ( col_int_key) VALUES (7);
ANALYZE TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	analyze	status	OK
SELECT  table1.col_int_key + 1 AS field1, table2.col_int_key AS field2
FROM (t1 AS table1  JOIN t1 AS table2 ON (table2.pk = table1.pk))
ORDER BY field1, field2;
field1	field2
8	7
EXPLAIN SELECT  table1.col_int_key + 1 AS field1, table2.col_int_key AS field2
FROM (t1 AS table1  JOIN t1 AS table2 ON (table2.pk = table1.pk))
ORDER BY field1, field2;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	table1	system	PRIMARY	NULL	NULL	NULL	1	
1	SIMPLE	table2	system	PRIMARY	NULL	NULL	NULL	1	
SELECT  table1.col_int_key + 1 AS field1, table2.col_int_key AS field2
FROM (t1 AS table1  JOIN t1 AS table2 ON (table2.pk = table1.pk))
GROUP BY field1, field2;
field1	field2
8	7
EXPLAIN SELECT  table1.col_int_key + 1 AS field1, table2.col_int_key AS field2
FROM (t1 AS table1  JOIN t1 AS table2 ON (table2.pk = table1.pk))
GROUP BY field1, field2;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	table1	system	PRIMARY	NULL	NULL	NULL	1	
1	SIMPLE	table2	system	PRIMARY	NULL	NULL	NULL	1	
DROP TABLE t1;
#
# Bug#21391781 ASSERT WHEN RUNNING ALTER TABLE ON A TABLE WITH INDEX
#              ON VIRTUAL COLUMN
#
CREATE TABLE t1 (
col1 INTEGER NOT NULL,
col2 INTEGER NOT NULL,
gcol1 INTEGER GENERATED ALWAYS AS (col1 + col2) VIRTUAL,
col3 INTEGER NOT NULL,
col4 INTEGER NOT NULL,
col5 INTEGER DEFAULT NULL,
col6 INTEGER DEFAULT NULL,
col7 INTEGER DEFAULT NULL,
col8 INTEGER DEFAULT NULL,
col9 INTEGER DEFAULT NULL,
col10 INTEGER DEFAULT NULL,
col11 INTEGER DEFAULT NULL,
col12 INTEGER DEFAULT NULL,
col13 INTEGER DEFAULT NULL,
col14 INTEGER DEFAULT NULL,
col15 INTEGER DEFAULT NULL,
col16 INTEGER DEFAULT NULL,
col17 INTEGER DEFAULT NULL,
col18 INTEGER DEFAULT NULL,
col19 INTEGER DEFAULT NULL,
col20 INTEGER DEFAULT NULL,
col21 INTEGER DEFAULT NULL,
col22 INTEGER DEFAULT NULL,
col23 INTEGER DEFAULT NULL,
col24 INTEGER DEFAULT NULL,
col25 INTEGER DEFAULT NULL,
col26 INTEGER DEFAULT NULL,
col27 INTEGER DEFAULT NULL,
col28 INTEGER DEFAULT NULL,
col29 INTEGER DEFAULT NULL,
col30 INTEGER DEFAULT NULL,
col31 INTEGER DEFAULT NULL,
col32 INTEGER DEFAULT NULL,
col33 INTEGER DEFAULT NULL,
col34 INTEGER DEFAULT NULL,
col35 INTEGER DEFAULT NULL,
col36 INTEGER DEFAULT NULL,
col37 INTEGER DEFAULT NULL,
col38 INTEGER DEFAULT NULL,
col39 INTEGER DEFAULT NULL,
col40 INTEGER DEFAULT NULL,
col41 INTEGER DEFAULT NULL,
col42 INTEGER DEFAULT NULL,
col43 INTEGER DEFAULT NULL,
col44 INTEGER DEFAULT NULL,
col45 INTEGER DEFAULT NULL,
col46 INTEGER DEFAULT NULL,
col47 INTEGER DEFAULT NULL,
col48 INTEGER DEFAULT NULL,
col49 INTEGER DEFAULT NULL,
col50 INTEGER DEFAULT NULL,
col51 INTEGER DEFAULT NULL,
col52 INTEGER DEFAULT NULL,
col53 INTEGER DEFAULT NULL,
col54 INTEGER DEFAULT NULL,
col55 INTEGER DEFAULT NULL,
col56 INTEGER DEFAULT NULL,
col57 INTEGER DEFAULT NULL,
col58 INTEGER DEFAULT NULL,
col59 INTEGER DEFAULT NULL,
col60 INTEGER DEFAULT NULL,
col61 INTEGER DEFAULT NULL,
col62 INTEGER DEFAULT NULL,
col63 INTEGER DEFAULT NULL,
col64 INTEGER DEFAULT NULL,
col65 INTEGER DEFAULT NULL,
gcol2 INTEGER GENERATED ALWAYS AS (col3 / col4) VIRTUAL,
KEY idx1 (gcol1)
);
INSERT INTO t1 (col1, col2, col3, col4)
VALUES (1,1,1,1), (2,2,2,2), (3,3,3,3), (4,4,4,4), (5,5,5,5);
ALTER TABLE t1 ADD COLUMN extra INTEGER;
SELECT gcol1 FROM t1 FORCE INDEX(idx1);
gcol1
2
4
6
8
10
DROP TABLE t1;
CREATE TABLE t1 (
col1 INTEGER NOT NULL,
col2 INTEGER NOT NULL,
gcol1 INTEGER GENERATED ALWAYS AS (col1 + col2) VIRTUAL,
col3 INTEGER NOT NULL,
col4 INTEGER NOT NULL,
col5 INTEGER DEFAULT NULL,
col6 INTEGER DEFAULT NULL,
col7 INTEGER DEFAULT NULL,
col8 INTEGER DEFAULT NULL,
col9 INTEGER DEFAULT NULL,
col10 INTEGER DEFAULT NULL,
col11 INTEGER DEFAULT NULL,
col12 INTEGER DEFAULT NULL,
col13 INTEGER DEFAULT NULL,
col14 INTEGER DEFAULT NULL,
col15 INTEGER DEFAULT NULL,
col16 INTEGER DEFAULT NULL,
col17 INTEGER DEFAULT NULL,
col18 INTEGER DEFAULT NULL,
col19 INTEGER DEFAULT NULL,
col20 INTEGER DEFAULT NULL,
col21 INTEGER DEFAULT NULL,
col22 INTEGER DEFAULT NULL,
col23 INTEGER DEFAULT NULL,
col24 INTEGER DEFAULT NULL,
col25 INTEGER DEFAULT NULL,
col26 INTEGER DEFAULT NULL,
col27 INTEGER DEFAULT NULL,
col28 INTEGER DEFAULT NULL,
col29 INTEGER DEFAULT NULL,
col30 INTEGER DEFAULT NULL,
col31 INTEGER DEFAULT NULL,
col32 INTEGER DEFAULT NULL,
col33 INTEGER DEFAULT NULL,
col34 INTEGER DEFAULT NULL,
col35 INTEGER DEFAULT NULL,
col36 INTEGER DEFAULT NULL,
col37 INTEGER DEFAULT NULL,
col38 INTEGER DEFAULT NULL,
col39 INTEGER DEFAULT NULL,
col40 INTEGER DEFAULT NULL,
col41 INTEGER DEFAULT NULL,
col42 INTEGER DEFAULT NULL,
col43 INTEGER DEFAULT NULL,
col44 INTEGER DEFAULT NULL,
col45 INTEGER DEFAULT NULL,
col46 INTEGER DEFAULT NULL,
col47 INTEGER DEFAULT NULL,
col48 INTEGER DEFAULT NULL,
col49 INTEGER DEFAULT NULL,
col50 INTEGER DEFAULT NULL,
col51 INTEGER DEFAULT NULL,
col52 INTEGER DEFAULT NULL,
col53 INTEGER DEFAULT NULL,
col54 INTEGER DEFAULT NULL,
col55 INTEGER DEFAULT NULL,
col56 INTEGER DEFAULT NULL,
col57 INTEGER DEFAULT NULL,
col58 INTEGER DEFAULT NULL,
col59 INTEGER DEFAULT NULL,
col60 INTEGER DEFAULT NULL,
col61 INTEGER DEFAULT NULL,
col62 INTEGER DEFAULT NULL,
col63 INTEGER DEFAULT NULL,
col64 INTEGER DEFAULT NULL,
col65 INTEGER DEFAULT NULL,
gcol2 INTEGER GENERATED ALWAYS AS (col3 / col4) VIRTUAL,
KEY idx1 (gcol2)
);
INSERT INTO t1 (col1, col2, col3, col4)
VALUES (1,1,1,1), (2,2,2,2), (3,3,3,3), (4,4,4,4), (5,5,5,5);
ALTER TABLE t1 ADD COLUMN extra INTEGER;
SELECT gcol2 FROM t1 FORCE INDEX(idx1);
gcol2
1
1
1
1
1
DROP TABLE t1;
#
# Bug#21628161 CRASH/MEMORY CORRUPTION ADDING INDEXES TO VIRTUAL COLUMN
#
CREATE TABLE t (a INT,
b BOOLEAN GENERATED ALWAYS AS (a+10000) VIRTUAL,
c BLOB GENERATED ALWAYS AS (b=2) VIRTUAL);
INSERT INTO t(a) VALUES (1);
SELECT * FROM t WHERE c = '0';
a	b	c
1	127	0
SET STATEMENT sql_mode = 'NO_ENGINE_SUBSTITUTION' FOR
ALTER TABLE t ADD UNIQUE INDEX (c(1));
Warnings:
Warning	1264	Out of range value for column 'b' at row 1
SELECT * FROM t WHERE c = '0';
a	b	c
1	127	0
DROP TABLE t;
#
# Bug#21688115 VIRTUAL COLUMN COMPUTATION SAVE_IN_FIELD()
#              DID NOT RETURN TRUE WITH DIVIDE 0
#
CREATE TABLE t (a INT, b INT, h VARCHAR(10));
INSERT INTO t VALUES (12, 3, "ss");
INSERT INTO t VALUES (13, 4, "ss");
INSERT INTO t VALUES (14, 0, "ss");
SET STATEMENT sql_mode = 'NO_ENGINE_SUBSTITUTION' FOR
ALTER TABLE t ADD c INT GENERATED ALWAYS AS (a/b) VIRTUAL;
CREATE INDEX idx ON t(c);
ERROR 22012: Division by 0
CALL mtr.add_suppression("\\[Warning\\] InnoDB: Compute virtual column values failed");
DROP TABLE t;
#
# Bug#21770798 OPTIMIZER DOES NOT USE INDEX FOR GENERATED EXPRESSIONS
#              WITH LOGICAL OPERATORS
#
CREATE TABLE t (a INT, b INT,
gc_and INT GENERATED ALWAYS AS (a AND b) STORED,
gc_or INT GENERATED ALWAYS AS (a OR b) STORED,
gc_xor INT GENERATED ALWAYS AS (a XOR b) STORED,
gc_not INT GENERATED ALWAYS AS (NOT a) STORED,
gc_case INT GENERATED ALWAYS AS
(CASE WHEN (a AND b) THEN a ELSE b END) STORED,
INDEX(gc_and), INDEX(gc_or), INDEX(gc_xor), INDEX(gc_not),
INDEX(gc_case));
INSERT INTO t (a, b) VALUES (0, 0), (0, 1), (1, 0), (1, 1);
ANALYZE TABLE t;
Table	Op	Msg_type	Msg_text
test.t	analyze	status	OK
EXPLAIN SELECT a, b FROM t WHERE (a AND b) = 1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t	ALL	NULL	NULL	NULL	NULL	4	Using where
SELECT a, b FROM t WHERE (a AND b) = 1;
a	b
1	1
EXPLAIN SELECT a, b FROM t WHERE 1 = (a AND b);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t	ALL	NULL	NULL	NULL	NULL	4	Using where
SELECT a, b FROM t WHERE 1 = (a AND b);
a	b
1	1
EXPLAIN SELECT a, b FROM t WHERE (a AND b) IN (1, 2, 3);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t	ALL	NULL	NULL	NULL	NULL	4	Using where
SELECT a, b FROM t WHERE (a AND b) IN (1, 2, 3);
a	b
1	1
EXPLAIN SELECT a, b FROM t WHERE (a OR b) = 1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t	ALL	NULL	NULL	NULL	NULL	4	Using where
SELECT a, b FROM t WHERE (a OR b) = 1;
a	b
0	1
1	0
1	1
EXPLAIN SELECT a, b FROM t WHERE (a OR b) BETWEEN 1 AND 10;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t	ALL	NULL	NULL	NULL	NULL	4	Using where
SELECT a, b FROM t WHERE (a OR b) BETWEEN 1 AND 10;
a	b
0	1
1	0
1	1
EXPLAIN SELECT a, b FROM t WHERE (a XOR b) = 1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t	ALL	NULL	NULL	NULL	NULL	4	Using where
SELECT a, b FROM t WHERE (a XOR b) = 1;
a	b
0	1
1	0
EXPLAIN SELECT a FROM t WHERE (NOT a) = 1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t	ALL	NULL	NULL	NULL	NULL	4	Using where
SELECT a FROM t WHERE (NOT a) = 1;
a
0
0
EXPLAIN SELECT a FROM t WHERE (CASE WHEN (a AND b) THEN a ELSE b END) = 1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t	ALL	NULL	NULL	NULL	NULL	4	Using where
SELECT a FROM t WHERE (CASE WHEN (a AND b) THEN a ELSE b END) = 1;
a
0
1
EXPLAIN SELECT a, b FROM t WHERE 1 = (b AND a);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t	ALL	NULL	NULL	NULL	NULL	4	Using where
SELECT a, b FROM t WHERE 1 = (b AND a);
a	b
1	1
EXPLAIN SELECT a, b FROM t WHERE 1 = (b OR a);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t	ALL	NULL	NULL	NULL	NULL	4	Using where
SELECT a, b FROM t WHERE 1 = (b OR a);
a	b
0	1
1	0
1	1
DROP TABLE t;
#
# Bug#22810883: ASSERTION FAILED:
#               !(USED_TABS & (~READ_TABLES & ~FILTER_FOR_TABLE))
#
CREATE TABLE t1 (a1 INTEGER GENERATED ALWAYS AS (1 AND 0) STORED,
a2 INTEGER, KEY (a1));
INSERT INTO t1 VALUES ();
CREATE TABLE t2 (b INTEGER);
INSERT INTO t2 VALUES (1);
ANALYZE TABLE t1, t2;
Table	Op	Msg_type	Msg_text
test.t1	analyze	status	OK
test.t2	analyze	status	OK
# Used to choose the index on a1 and get wrong results.
EXPLAIN SELECT * FROM t1 WHERE (a2 AND a2) = 0;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
SELECT * FROM t1 WHERE (a2 AND a2) = 0;
a1	a2
# Used to get assertion or wrong results.
EXPLAIN SELECT * FROM t1 STRAIGHT_JOIN t2 ON b WHERE (b AND b) = 1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	system	NULL	NULL	NULL	NULL	1	
1	SIMPLE	t2	system	NULL	NULL	NULL	NULL	1	
SELECT * FROM t1 STRAIGHT_JOIN t2 ON b WHERE (b AND b) = 1;
a1	a2	b
0	NULL	1
DROP TABLE t1, t2;
#
# MDEV-20618 Assertion `btr_validate_index(index, 0, false)' failed
# in row_upd_sec_index_entry
#
CREATE TABLE t1 (A BIT(15), VA BIT(10) GENERATED ALWAYS AS (A),PK INT,
PRIMARY KEY (PK), UNIQUE KEY (VA));
INSERT IGNORE INTO t1 VALUES ( '\r1','a',1);
Warnings:
Warning	1906	The value specified for generated column 'VA' in table 't1' has been ignored
Warning	1264	Out of range value for column 'VA' at row 1
REPLACE INTO t1 (PK) VALUES (1);
ERROR 22001: Data too long for column 'VA' at row 1
DROP TABLE t1;
#
# MDEV-17890 Record in index was not found on update, server crash in
# row_upd_build_difference_binary or
# Assertion `0' failed in row_upd_sec_index_entry
#
CREATE TABLE t1 (
pk BIGINT AUTO_INCREMENT,
b BIT(15),
v BIT(10) AS (b) VIRTUAL,
PRIMARY KEY(pk),
UNIQUE(v)
);
INSERT IGNORE INTO t1 (b) VALUES (b'101110001110100'),(b'011101');
Warnings:
Warning	1264	Out of range value for column 'v' at row 1
SELECT pk, b INTO OUTFILE 'load.data' FROM t1;
LOAD DATA INFILE 'load.data' REPLACE INTO TABLE t1 (pk, b);
ERROR 22001: Data too long for column 'v' at row 1
DROP TABLE t1;
#
# MDEV-17834 Server crashes in row_upd_build_difference_binary
# on LOAD DATA into table with indexed virtual column
#
CREATE TABLE t1 (
pk INT,
i TINYINT,
ts TIMESTAMP NULL,
vi TINYINT AS (i+1) PERSISTENT,
vts TIMESTAMP(5) AS (ts) VIRTUAL,
PRIMARY KEY(pk),
UNIQUE(vts)
);
INSERT IGNORE INTO t1 (pk,i) VALUES (1,127);
Warnings:
Warning	1264	Out of range value for column 'vi' at row 1
LOAD DATA INFILE 'MYSQLTEST_VARDIR/tmp/load.data' REPLACE INTO TABLE t1 (pk,i,ts);
ERROR 22003: Out of range value for column 'vi' at row 1
DROP TABLE t1;
DROP VIEW  IF EXISTS v1,v2;
DROP TABLE IF EXISTS t1,t2,t3;
DROP PROCEDURE IF EXISTS p1;
DROP FUNCTION IF EXISTS f1;
DROP TRIGGER IF EXISTS trg1;
DROP TRIGGER IF EXISTS trg2;
set sql_warnings = 0;