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
|
set @save_ext_key_optimizer_switch=@@optimizer_switch;
#
# MDEV-3992 Server crash or valgrind errors in test_if_skip_sort_order/test_if_cheaper_ordering
# on GROUP BY with indexes on InnoDB table
#
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;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 index PRIMARY,pk PRIMARY 4 NULL 2 Using where
SELECT COUNT(*), pk field1, pk AS field2
FROM t1 WHERE a = 'r' OR pk = 183
GROUP BY field1, field2;
COUNT(*) field1 field2
EXPLAIN
SELECT COUNT(*), pk field1 FROM t1
WHERE a = 'r' OR pk = 183 GROUP BY field1, field1;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 index PRIMARY,pk PRIMARY 4 NULL 2 Using where
SELECT COUNT(*), pk field1 FROM t1
WHERE a = 'r' OR pk = 183 GROUP BY field1, field1;
COUNT(*) field1
drop table t1;
set optimizer_switch=@save_ext_key_optimizer_switch;
#
# MDEV-4002 Server crash or valgrind errors in Item_func_group_concat::setup and Item_func_group_concat::add
#
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;
i GROUP_CONCAT( d1, d2 ORDER BY d1, d2 )
1 11.1
2 22.2
NULL 11.1,22.2
EXECUTE stmt;
i GROUP_CONCAT( d1, d2 ORDER BY d1, d2 )
1 11.1
2 22.2
NULL 11.1,22.2
DROP TABLE t1;
End of 5.5 tests
#
# MDEV-5719: Wrong result with GROUP BY and LEFT OUTER JOIN
#
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);
SELECT a.oidGroup, a.oid, b.oid FROM t1 a LEFT JOIN t2 b ON
a.oid=b.oid WHERE a.oidGroup=1;
oidGroup oid oid
1 1 NULL
1 2 NULL
1 3 3
1 4 NULL
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;
oidGroup oid oid
1 1 NULL
1 2 NULL
1 3 3
1 4 NULL
DROP TABLE t1, t2;
#
# MDEV-7193: Incorrect Query Result (MySQL Bug 68897) in MariaDB 10.0.14
# (fixed by MDEV-5719)
#
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;
id xtext optionen
1 select Kabel mit Stecker 5-polig,Kabel ohne Stecker
2 number 22,25
3 NULL NULL
4 NULL NULL
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;
id xtext optionen
4 NULL NULL
3 NULL NULL
2 number 22,25
1 select Kabel mit Stecker 5-polig,Kabel ohne Stecker
DROP TABLE t1, t2;
#
# MDEV-11162: Assertion `num_records == m_idx_array.size()' failed in Filesort_buffer::alloc_sort_buffer(uint, uint)
#
CREATE TABLE t1 (i INT) ENGINE=InnoDB;
SELECT ( SELECT DISTINCT GROUP_CONCAT(SLEEP(0)) FROM t1 GROUP BY i );
( SELECT DISTINCT GROUP_CONCAT(SLEEP(0)) FROM t1 GROUP BY i )
NULL
SELECT i FROM t1 order by i LIMIT 1;
i
DROP TABLE t1;
# Port of testcase:
#
# Bug#20819199 ASSERTION FAILED IN TEST_IF_SKIP_SORT_ORDER
#
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;
Table Op Msg_type Msg_text
test.t1 analyze status Engine-independent statistics collected
test.t1 analyze status OK
EXPLAIN SELECT DISTINCT a, MAX(b) FROM t1 WHERE a >= 0 GROUP BY a,a;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 range idx1,idx3 idx3 5 NULL 100 Using where; Using index
SELECT DISTINCT a, MAX(b) FROM t1 WHERE a >= 0 GROUP BY a,a;
a MAX(b)
1 10
2 10
3 10
4 10
5 10
6 10
7 10
8 10
9 10
10 10
DROP TABLE t0, t1;
# End of tests
|
