Debian Codegraph @

mysql-test/suite/vcol/t/vcol_select_myisam.test

raw
1 ###############################################################################
2 # t/vcol_select.test #
3 # #
4 # Purpose: #
5 # Testing different SELECTs. #
6 # #
7 # MyISAM branch #
8 # #
9 #-----------------------------------------------------------------------------#
10 # Original Author: Andrey Zhakov #
11 # Original Date: 2008-09-18 #
12 # Change Author: #
13 # Change Date: #
14 # Change: #
15 ###############################################################################
16
17 #
18 # NOTE: PLEASE DO NOT ADD NOT MYISAM SPECIFIC TESTCASES HERE !
19 # TESTCASES WHICH MUST BE APPLIED TO ALL STORAGE ENGINES MUST BE ADDED IN
20 # THE SOURCED FILES ONLY.
21 #
22
23 #-----------------------------------------------------------------------------#
24 # Cleanup
25 --source include/default_optimizer_switch.inc
26 --source suite/vcol/inc/vcol_cleanup.inc
27
28 #-----------------------------------------------------------------------------#
29 # General not engine specific settings and requirements
30 --source suite/vcol/inc/vcol_init_vars.pre
31
32 #-----------------------------------------------------------------------------#
33 # Engine specific settings and requirements
34
35 ##### Storage engine to be tested
36 # Set the session storage engine
37 eval SET @@session.default_storage_engine = 'MyISAM';
38
39 ##### Workarounds for known open engine specific bugs
40 # none
41
42 #-----------------------------------------------------------------------------#
43 # Execute the tests to be applied to all storage engines
44 --source suite/vcol/inc/vcol_select.inc
45
46 #-----------------------------------------------------------------------------#
47 # Execute storage engine specific tests
48
49 #-----------------------------------------------------------------------------#
50 # Cleanup
51 --source suite/vcol/inc/vcol_cleanup.inc
52
53 --echo #
54 --echo # Bug #806057: join with USING over a virtual column
55 --echo #
56
57 CREATE TABLE t1 (b int);
58 INSERT INTO t1 VALUES (NULL),( 78), (185), (0), (154);
59
60 CREATE TABLE t2 (a int, b int AS (a) VIRTUAL);
61 INSERT IGNORE INTO t2 VALUES (187,187), (9,9), (187,187);
62
63 EXPLAIN EXTENDED
64 SELECT * FROM t1 JOIN t2 USING (b);
65 SELECT * FROM t1 JOIN t2 USING (b);
66
67 EXPLAIN EXTENDED
68 SELECT * FROM t1 NATURAL JOIN t2;
69 SELECT * FROM t1 NATURAL JOIN t2;
70
71 DROP TABLE t1,t2;
72
73 #
74 # MDEV-11525 Assertion `cp + len <= buff + buff_size' failed in JOIN_CACHE::write_record_data
75 #
76
77 create table t1 (
78 pk integer auto_increment,
79 bi integer not null,
80 vi integer generated always as (bi) persistent,
81 bc varchar(1) not null,
82 vc varchar(2) generated always as (concat(bc, bc)) persistent,
83 primary key (pk),
84 key (vi, vc));
85 insert t1 (bi, bc) values (0, 'x'), (0, 'n'), (1, 'w'), (7, 's'), (0, 'a'), (4, 'd'), (1, 'w'), (1, 'j'), (1, 'm'), (4, 'k'), (7, 't'), (4, 'k'), (2, 'e'), (0, 'i'), (1, 't'), (6, 'z'), (3, 'c'), (6, 'i'), (8, 'v');
86 create table t2 (
87 pk integer auto_increment,
88 bi integer not null,
89 vi integer generated always as (bi) persistent,
90 bc varchar(257) not null,
91 vc varchar(2) generated always as (concat(bc, bc)) persistent,
92 primary key (pk),
93 key (vi, vc));
94 insert t2 (bi, bc) values (1, 'c'), (8, 'm'), (9, 'd'), (6, 'y'), (1, 't'), (6, 'd'), (2, 's'), (4, 'r'), (8, 'm'), (4, 'b'), (4, 'x'), (7, 'g'), (4, 'p'), (1, 'q'), (9, 'w'), (4, 'd'), (8, 'e'), (4, 'b'), (8, 'y');
95 explain # should be using join buffer
96 select t2.vi from (t2 as t3 right join (t2 left join t1 on (t1.bi = t2.vi)) on (t1.vc = t2.vc));
97 --sorted_result
98 select t2.vi from (t2 as t3 right join (t2 left join t1 on (t1.bi = t2.vi)) on (t1.vc = t2.vc));
99 drop table t2,t1;
100
101 #
102 # End of 5.5 tests
103 #
104
105 #
106 # MDEV-11640 gcol.gcol_select_myisam fails in buildbot on Power
107 # (same as MDEV-11525 above, but for virtual columns)
108 #
109
110 create table t1 (
111 pk integer auto_increment,
112 bi integer not null,
113 vi integer generated always as (bi) virtual,
114 bc varchar(1) not null,
115 vc varchar(2) generated always as (concat(bc, bc)),
116 primary key (pk),
117 key (vi, vc));
118 insert t1 (bi, bc) values (0, 'x'), (0, 'n'), (1, 'w'), (7, 's'), (0, 'a'), (4, 'd'), (1, 'w'), (1, 'j'), (1, 'm'), (4, 'k'), (7, 't'), (4, 'k'), (2, 'e'), (0, 'i'), (1, 't'), (6, 'z'), (3, 'c'), (6, 'i'), (8, 'v');
119 create table t2 (
120 pk integer auto_increment,
121 bi integer not null,
122 vi integer generated always as (bi) virtual,
123 bc varchar(257) not null,
124 vc varchar(2) generated always as (concat(bc, bc)),
125 primary key (pk),
126 key (vi, vc));
127 insert t2 (bi, bc) values (1, 'c'), (8, 'm'), (9, 'd'), (6, 'y'), (1, 't'), (6, 'd'), (2, 's'), (4, 'r'), (8, 'm'), (4, 'b'), (4, 'x'), (7, 'g'), (4, 'p'), (1, 'q'), (9, 'w'), (4, 'd'), (8, 'e'), (4, 'b'), (8, 'y');
128 explain # should be using join buffer
129 select t2.vi from (t2 as t3 right join (t2 left join t1 on (t1.bi = t2.vi)) on (t1.vc = t2.vc));
130 --sorted_result
131 select t2.vi from (t2 as t3 right join (t2 left join t1 on (t1.bi = t2.vi)) on (t1.vc = t2.vc));
132 drop table t2,t1;