hHyxY718

1rollup ignores group by having constraint
2+----+-------+-------+
3| id | style | color |
4+----+-------+-------+
5|  1 |     1 | red   |
6|  2 |     1 | blue  |
7|  3 |     2 | red   |
8|  4 |     2 | blue  |
9|  5 |     2 | green |
10|  6 |     3 | blue  |
11+----+-------+-------+
12	
13SELECT style, COUNT(*) as count from t GROUP BY style WITH ROLLUP HAVING count > 1;
14	
15+-------+-------+
16| style | count |
17+-------+-------+
18|     1 |     2 |
19|     2 |     3 |
20|  NULL |     6 |
21+-------+-------+
22	
23SELECT style,COUNT(1) as count
24FROM t
25WHERE NOT EXISTS
26(
27    SELECT t1.style as count
28    FROM
29    (
30        SELECT style from t GROUP BY style HAVING count(*) = 1
31    ) t1 WHERE t.style = t1.style
32)
33GROUP BY style
34WITH ROLLUP;
35	
36drop database if exists rollup_test;
37create database rollup_test;
38use rollup_test
39create table t (id int not null auto_increment,
40style int,color varchar(10),primary key (id));
41insert into t (style,color) values
42(1,'red'),(1,'blue'),(2,'red'),
43(2,'blue'),(2,'green'),(3,'blue');
44select * from t;
45	
46mysql> drop database if exists rollup_test;
47Query OK, 1 row affected (0.07 sec)
48
49mysql> create database rollup_test;
50Query OK, 1 row affected (0.00 sec)
51
52mysql> use rollup_test
53Database changed
54mysql> create table t (id int not null auto_increment,
55    -> style int,color varchar(10),primary key (id));
56Query OK, 0 rows affected (0.10 sec)
57
58mysql> insert into t (style,color) values
59    -> (1,'red'),(1,'blue'),(2,'red'),
60    -> (2,'blue'),(2,'green'),(3,'blue');
61Query OK, 6 rows affected (0.05 sec)
62Records: 6  Duplicates: 0  Warnings: 0
63
64mysql> select * from t;
65+----+-------+-------+
66| id | style | color |
67+----+-------+-------+
68|  1 |     1 | red   |
69|  2 |     1 | blue  |
70|  3 |     2 | red   |
71|  4 |     2 | blue  |
72|  5 |     2 | green |
73|  6 |     3 | blue  |
74+----+-------+-------+
756 rows in set (0.00 sec)
76
77mysql>
78	
79mysql> SELECT style,COUNT(1) as count
80    -> FROM t
81    -> WHERE NOT EXISTS
82    -> (
83    ->     SELECT t1.style as count
84    ->     FROM
85    ->     (
86    ->         SELECT style from t GROUP BY style HAVING count(*) = 1
87    ->     ) t1 WHERE t.style = t1.style
88    -> )
89    -> GROUP BY style
90    -> WITH ROLLUP;
91+-------+-------+
92| style | count |
93+-------+-------+
94|     1 |     2 |
95|     2 |     3 |
96|  NULL |     5 |
97+-------+-------+
983 rows in set (0.00 sec)
99
100mysql>