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
|
#
# 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 # End of tests
|
