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
|
#
# Test GROUP BY queries that utilize InnoDB extended keys
#
--source include/have_innodb.inc
set @save_ext_key_optimizer_switch=@@optimizer_switch;
--echo #
--echo # MDEV-3992 Server crash or valgrind errors in test_if_skip_sort_order/test_if_cheaper_ordering
--echo # on GROUP BY with indexes on InnoDB table
--echo #
CREATE TABLE t1 (
pk INT PRIMARY KEY,
a VARCHAR(1) NOT NULL,
KEY (pk)
) ENGINE=InnoDB;
set optimizer_switch='extended_keys=on';
INSERT INTO t1 VALUES (1,'a'),(2,'b');
EXPLAIN
SELECT COUNT(*), pk field1, pk AS field2
FROM t1 WHERE a = 'r' OR pk = 183
GROUP BY field1, field2;
SELECT COUNT(*), pk field1, pk AS field2
FROM t1 WHERE a = 'r' OR pk = 183
GROUP BY field1, field2;
EXPLAIN
SELECT COUNT(*), pk field1 FROM t1
WHERE a = 'r' OR pk = 183 GROUP BY field1, field1;
SELECT COUNT(*), pk field1 FROM t1
WHERE a = 'r' OR pk = 183 GROUP BY field1, field1;
drop table t1;
set optimizer_switch=@save_ext_key_optimizer_switch;
--echo #
--echo # MDEV-4002 Server crash or valgrind errors in Item_func_group_concat::setup and Item_func_group_concat::add
--echo #
CREATE TABLE t1 (
pk INT NOT NULL PRIMARY KEY,
d1 DOUBLE,
d2 DOUBLE,
i INT NOT NULL DEFAULT '0',
KEY (i)
) ENGINE=InnoDB;
INSERT INTO t1 VALUES (1,1.0,1.1,1),(2,2.0,2.2,2);
PREPARE stmt FROM "
SELECT DISTINCT i, GROUP_CONCAT( d1, d2 ORDER BY d1, d2 )
FROM t1 a1 NATURAL JOIN t1 a2 GROUP BY i WITH ROLLUP
";
EXECUTE stmt;
EXECUTE stmt;
DROP TABLE t1;
--echo End of 5.5 tests
--echo #
--echo # MDEV-5719: Wrong result with GROUP BY and LEFT OUTER JOIN
--echo #
CREATE TABLE t1 (oidGroup INT, oid INT PRIMARY KEY)ENGINE=INNODB;
INSERT INTO t1 VALUES (1,1),(1,2),(1,3),(1,4);
CREATE TABLE t2 (oid INT PRIMARY KEY)ENGINE=INNODB;
INSERT INTO t2 VALUES (3);
# Returns a value
SELECT a.oidGroup, a.oid, b.oid FROM t1 a LEFT JOIN t2 b ON
a.oid=b.oid WHERE a.oidGroup=1;
SELECT a.oidGroup, a.oid, b.oid FROM t1 a LEFT JOIN t2 b ON
a.oid=b.oid WHERE a.oidGroup=1 GROUP BY a.oid;
DROP TABLE t1, t2;
--echo #
--echo # MDEV-7193: Incorrect Query Result (MySQL Bug 68897) in MariaDB 10.0.14
--echo # (fixed by MDEV-5719)
--echo #
CREATE TABLE `t1` (
`param` int(11) NOT NULL,
`idx` int(11) NOT NULL,
`text` varchar(255) default NULL,
PRIMARY KEY (`param`,`idx`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `t1` (`param`, `idx`, `text`) VALUES
(1, 0, 'select'),
(1, 1, 'Kabel mit Stecker 5-polig'),
(1, 2, 'Kabel ohne Stecker'),
(2, 0, 'number'),
(2, 1, '22'),
(2, 2, '25');
CREATE TABLE `t2` (
`id` int PRIMARY KEY
);
INSERT INTO t2 VALUES (1),(2),(3),(4);
SELECT t2.id AS id, T.text AS xtext,GROUP_CONCAT(T3.text) AS optionen
FROM t2
LEFT JOIN t1 AS T ON(T.param=t2.id AND T.idx=0 )
LEFT JOIN t1 AS T3 ON(T3.param=t2.id AND T3.idx>0 )
GROUP BY t2.id
ORDER BY id ASC;
SELECT t2.id AS id, T.text AS xtext,GROUP_CONCAT(T3.text) AS optionen
FROM t2
LEFT JOIN t1 AS T ON(T.param=t2.id AND T.idx=0 )
LEFT JOIN t1 AS T3 ON(T3.param=t2.id AND T3.idx>0 )
GROUP BY t2.id
ORDER BY id DESC;
DROP TABLE t1, t2;
--echo #
--echo # MDEV-11162: Assertion `num_records == m_idx_array.size()' failed in Filesort_buffer::alloc_sort_buffer(uint, uint)
--echo #
CREATE TABLE t1 (i INT) ENGINE=InnoDB;
SELECT ( SELECT DISTINCT GROUP_CONCAT(SLEEP(0)) FROM t1 GROUP BY i );
SELECT i FROM t1 order by i LIMIT 1;
DROP TABLE t1;
--echo # Port of testcase:
--echo #
--echo # Bug#20819199 ASSERTION FAILED IN TEST_IF_SKIP_SORT_ORDER
--echo #
CREATE TABLE t0 ( a INT );
INSERT INTO t0 VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);
CREATE TABLE t1 (
pk INT NOT NULL AUTO_INCREMENT,
a INT,
b INT,
PRIMARY KEY (pk),
KEY idx1 (a),
KEY idx2 (b, a),
KEY idx3 (a, b)
) ENGINE = InnoDB;
INSERT INTO t1 (a, b) SELECT t01.a, t02.a FROM t0 t01, t0 t02;
ANALYZE TABLE t1;
let $query=
SELECT DISTINCT a, MAX(b) FROM t1 WHERE a >= 0 GROUP BY a,a;
eval EXPLAIN $query;
eval $query;
DROP TABLE t0, t1;
--echo # End of tests
|
