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
|
SET @sav_dpi= @@div_precision_increment;
SET div_precision_increment= 5;
SHOW VARIABLES LIKE 'div_precision_increment';
Variable_name Value
div_precision_increment 5
# Bug#25727892: Refactor Item::const_item() as a non-virtual function
Extended coverage for ROLLUP and const expressions
CREATE TABLE t1 (a INTEGER) ENGINE=InnoDB;
CREATE TABLE t2 (c INTEGER) ENGINE=MyISAM;
INSERT INTO t1 VALUES (7),(8);
INSERT INTO t2 VALUES (-3);
SELECT c, a, SUM(c) OVER () FROM t1,t2 GROUP BY c,a WITH ROLLUP;
c a SUM(c) OVER ()
-3 7 -9
-3 8 -9
-3 NULL -9
NULL NULL -9
DROP TABLE t1, t2;
#
# Bug#27530568: SIG11 IN FIELD::REAL_MAYBE_NULL IN SQL/FIELD.H
#
CREATE TABLE t1 (pk INTEGER, PRIMARY KEY (pk))ENGINE= MYISAM;
CREATE TABLE t2 (pk INTEGER, PRIMARY KEY (pk))ENGINE= MYISAM;
INSERT INTO t2 VALUES (1),(2);
SELECT GROUPING(alias2.pk) AS field2 FROM t2 AS alias1 LEFT JOIN t1 AS alias2
ON 0 GROUP BY alias2.pk WITH ROLLUP ORDER BY GROUPING(alias2.pk);
field2
0
1
DROP TABLE t1,t2;
CREATE TABLE t1 (
pk INTEGER NOT NULL AUTO_INCREMENT,
col_varchar_key varchar(1) DEFAULT NULL,
PRIMARY KEY (pk),
KEY col_varchar_key (col_varchar_key)
);
INSERT INTO t1 VALUES (12,'a'),(16,'c'),(3,'d'),(20,'g'),(6,'h'),(15,'h'),
(4,'i'),(19,'l'),(7,'p'),(9,'p'),(17,'q'),(13,'r'),(5,'t'),(14,'u'),(8,'v'),
(10,'w'),(2,'x'),(18,'x'),(1,'z'),(11,'z');
CREATE TABLE t2(
pk INTEGER NOT NULL AUTO_INCREMENT,
PRIMARY KEY (pk)
) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4;
INSERT INTO t2 VALUES (1);
SELECT CONCAT(alias1.col_varchar_key,1) AS field1 FROM t1 AS alias1
RIGHT JOIN t2 ON (alias1.pk = 1) GROUP BY field1 WITH ROLLUP HAVING
field1 >= 'n' ORDER BY field1;
field1
z1
DROP TABLE t1,t2;
CREATE TABLE t1 (
pk INTEGER NOT NULL AUTO_INCREMENT,
col_int INTEGER DEFAULT NULL,
PRIMARY KEY (pk)
) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4;
INSERT INTO t1 VALUES (1,1);
SELECT ((table1.col_int) * (table1.col_int)) AS field2 FROM (t1 AS table1)
WHERE (table1.col_int != 1 OR table1.pk) GROUP BY field2 WITH ROLLUP
HAVING (field2 <> 239 ) ORDER BY GROUPING(field2);
field2
1
DROP TABLE t1;
set div_precision_increment= @sav_dpi;
|
