1133
# Bug#27219: Aggregate functions in ORDER BY.
1135
SET @save_sql_mode=@@sql_mode;
1136
SET @@sql_mode='ONLY_FULL_GROUP_BY';
1137
CREATE TABLE t1 (a INT, b INT, c INT DEFAULT 0);
1138
INSERT INTO t1 (a, b) VALUES (3,3), (2,2), (3,3), (2,2), (3,3), (4,4);
1139
CREATE TABLE t2 SELECT * FROM t1;
1140
SELECT 1 FROM t1 ORDER BY COUNT(*);
1143
SELECT 1 FROM t1 ORDER BY COUNT(*) + 1;
1146
SELECT 1 FROM t1 ORDER BY COUNT(*) + a;
1147
ERROR 42000: Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause
1148
SELECT 1 FROM t1 ORDER BY COUNT(*), 1;
1151
SELECT 1 FROM t1 ORDER BY COUNT(*), a;
1152
ERROR 42000: Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause
1153
SELECT 1 FROM t1 ORDER BY SUM(a);
1156
SELECT 1 FROM t1 ORDER BY SUM(a + 1);
1159
SELECT 1 FROM t1 ORDER BY SUM(a) + 1;
1162
SELECT 1 FROM t1 ORDER BY SUM(a), b;
1163
ERROR 42000: Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause
1164
SELECT a FROM t1 ORDER BY COUNT(b);
1165
ERROR 42000: Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause
1166
SELECT t1.a FROM t1 ORDER BY (SELECT SUM(t2.a) FROM t2);
1174
SELECT t1.a FROM t1 ORDER BY (SELECT SUM(t2.a), t2.a FROM t2);
1175
ERROR 42000: Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause
1176
SELECT t1.a FROM t1 ORDER BY (SELECT SUM(t2.a) FROM t2 ORDER BY t2.a);
1177
ERROR 42000: Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause
1178
SELECT t1.a FROM t1 ORDER BY (SELECT t2.a FROM t2 ORDER BY SUM(t2.b) LIMIT 1);
1179
ERROR 42000: Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause
1181
WHERE t1.a = (SELECT t2.a FROM t2 ORDER BY SUM(t2.b) LIMIT 1);
1182
ERROR 42000: Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause
1183
SELECT t1.a FROM t1 GROUP BY t1.a
1184
HAVING t1.a = (SELECT t2.a FROM t2 ORDER BY SUM(t2.a) LIMIT 1);
1185
ERROR 42000: Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause
1186
SELECT t1.a FROM t1 GROUP BY t1.a
1187
HAVING t1.a IN (SELECT t2.a FROM t2 ORDER BY SUM(t1.b));
1192
SELECT t1.a FROM t1 GROUP BY t1.a
1193
HAVING t1.a IN (SELECT t2.a FROM t2 ORDER BY t2.a, SUM(t2.b));
1194
ERROR 42000: Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause
1195
SELECT t1.a FROM t1 GROUP BY t1.a
1196
HAVING t1.a > ANY (SELECT t2.a FROM t2 ORDER BY t2.a, SUM(t2.b));
1197
ERROR 42000: Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause
1199
WHERE t1.a = (SELECT t2.a FROM t2 ORDER BY SUM(t1.b));
1200
ERROR 42000: Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause
1201
SELECT 1 FROM t1 GROUP BY t1.a
1202
HAVING (SELECT AVG(SUM(t1.b) + 1) FROM t2 ORDER BY SUM(t2.a) LIMIT 1);
1207
SELECT 1 FROM t1 GROUP BY t1.a
1208
HAVING (SELECT AVG(SUM(t1.b) + t2.b) FROM t2 ORDER BY SUM(t2.a) LIMIT 1);
1213
SELECT 1 FROM t1 GROUP BY t1.a
1214
HAVING (SELECT AVG(t1.b + t2.b) FROM t2 ORDER BY SUM(t2.a) LIMIT 1);
1219
SELECT 1 FROM t1 GROUP BY t1.a
1220
HAVING (SELECT AVG(SUM(t1.b) + 1) FROM t2 ORDER BY t2.a LIMIT 1);
1221
ERROR 42000: Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause
1222
SELECT 1 FROM t1 GROUP BY t1.a
1223
HAVING (SELECT AVG(SUM(t1.b) + t2.b) FROM t2 ORDER BY t2.a LIMIT 1);
1224
ERROR 42000: Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause
1225
SELECT 1 FROM t1 GROUP BY t1.a
1226
HAVING (SELECT AVG(t1.b + t2.b) FROM t2 ORDER BY t2.a LIMIT 1);
1227
ERROR 42000: Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause
1229
WHERE t1.a = (SELECT t2.a FROM t2 GROUP BY t2.a
1230
ORDER BY SUM(t2.b), SUM(t1.b) LIMIT 1);
1233
SELECT t1.a, SUM(t1.b) FROM t1
1234
WHERE t1.a = (SELECT SUM(t2.b) FROM t2 GROUP BY t2.a
1235
ORDER BY SUM(t2.b), SUM(t1.b) LIMIT 1)
1239
SELECT t1.a, SUM(t1.b) FROM t1
1240
WHERE t1.a = (SELECT SUM(t2.b) FROM t2
1241
ORDER BY SUM(t2.b) + SUM(t1.b) LIMIT 1)
1244
SELECT t1.a, SUM(t1.b) FROM t1
1245
WHERE t1.a = (SELECT SUM(t2.b) FROM t2
1246
ORDER BY SUM(t2.b + t1.a) LIMIT 1)
1249
SELECT t1.a FROM t1 GROUP BY t1.a
1250
HAVING (1, 1) = (SELECT SUM(t1.a), t1.a FROM t2 LIMIT 1);
1254
(select sum(outr.a + innr.a) from t1 as innr limit 1) as tt
1255
from t1 as outr order by outr.a limit 1))
1256
from t1 as most_outer;
1259
(select sum(outr.a + innr.a) from t1 as innr limit 1) as tt
1260
from t1 as outr order by outr.a limit 1))
1264
(select sum(outr.a + innr.a) from t1 as innr limit 1)) as tt
1265
from t1 as outr order by count(outr.a) limit 1)) as tt
1266
from t1 as most_outer;
1267
ERROR 42000: Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause
1268
select (select sum(outr.a + t1.a) from t1 limit 1) as tt from t1 as outr order by outr.a;
1276
SET sql_mode=@save_sql_mode;
1132
1278
End of 5.0 tests
1133
1279
CREATE TABLE t1 (a INT, b INT,
1134
1280
PRIMARY KEY (a),