summaryrefslogtreecommitdiff
path: root/mysql-test
diff options
context:
space:
mode:
Diffstat (limited to 'mysql-test')
-rw-r--r--mysql-test/r/bdb.result19
-rw-r--r--mysql-test/r/func_gconcat.result2
-rw-r--r--mysql-test/r/rpl_trigger.result12
-rw-r--r--mysql-test/r/sp.result82
-rw-r--r--mysql-test/r/subselect.result143
-rw-r--r--mysql-test/t/bdb.test20
-rw-r--r--mysql-test/t/func_gconcat.test2
-rw-r--r--mysql-test/t/rpl_trigger.test29
-rw-r--r--mysql-test/t/sp.test74
-rw-r--r--mysql-test/t/subselect.test83
10 files changed, 428 insertions, 38 deletions
diff --git a/mysql-test/r/bdb.result b/mysql-test/r/bdb.result
index ac14f891622..a564fd1045c 100644
--- a/mysql-test/r/bdb.result
+++ b/mysql-test/r/bdb.result
@@ -1289,6 +1289,25 @@ SELECT id FROM t1 WHERE (list_id = 1) AND (term = "letterd");
id
4
DROP TABLE t1;
+create table t1 (a int, key(a)) engine=bdb;
+create table t2 (b int, key(b)) engine=bdb;
+insert into t1 values (1),(1),(2),(3),(4);
+insert into t2 values (1),(5),(6),(7);
+delete from t1 where (a in (select b from t2));
+select count(*) from t1;
+count(*)
+3
+insert into t1 set a=(select b from t2);
+ERROR 21000: Subquery returns more than 1 row
+select count(*) from t1;
+count(*)
+3
+update t1 set a = a + 1 where (a in (select b from t2));
+select count(*) from t1;
+count(*)
+3
+drop table t1, t2;
+End of 4.1 tests
create temporary table t1 (a int, primary key(a)) engine=bdb;
select * from t1;
a
diff --git a/mysql-test/r/func_gconcat.result b/mysql-test/r/func_gconcat.result
index 7987ceca712..2a36e6fe17b 100644
--- a/mysql-test/r/func_gconcat.result
+++ b/mysql-test/r/func_gconcat.result
@@ -163,7 +163,7 @@ show warnings;
Level Code Message
Warning 1260 1 line(s) were cut by GROUP_CONCAT()
set group_concat_max_len = 1024;
-select group_concat(sum(a)) from t1 group by grp;
+select group_concat(sum(c)) from t1 group by grp;
ERROR HY000: Invalid use of group function
select grp,group_concat(c order by 2) from t1 group by grp;
ERROR 42S22: Unknown column '2' in 'order clause'
diff --git a/mysql-test/r/rpl_trigger.result b/mysql-test/r/rpl_trigger.result
index 999af131b8b..7613f2547f0 100644
--- a/mysql-test/r/rpl_trigger.result
+++ b/mysql-test/r/rpl_trigger.result
@@ -122,3 +122,15 @@ a=b && a=c
1
drop function bug12480;
drop table t1;
+create table t1 (i int);
+create table t2 (i int);
+create trigger tr1 before insert on t1 for each row
+begin
+insert into t2 values (1);
+end|
+create database other;
+use other;
+insert into test.t1 values (1);
+use test;
+drop table t1,t2;
+drop database other;
diff --git a/mysql-test/r/sp.result b/mysql-test/r/sp.result
index 9601cae5c3b..192a85ffd04 100644
--- a/mysql-test/r/sp.result
+++ b/mysql-test/r/sp.result
@@ -4110,23 +4110,23 @@ call bug14376(4711)|
x
4711
drop procedure bug14376|
-drop procedure if exists p1|
-drop table if exists t1|
-create table t1 (a varchar(255))|
-insert into t1 (a) values ("a - table column")|
-create procedure p1(a varchar(255))
+drop procedure if exists bug5967|
+drop table if exists t3|
+create table t3 (a varchar(255))|
+insert into t3 (a) values ("a - table column")|
+create procedure bug5967(a varchar(255))
begin
declare i varchar(255);
-declare c cursor for select a from t1;
+declare c cursor for select a from t3;
select a;
-select a from t1 into i;
+select a from t3 into i;
select i as 'Parameter takes precedence over table column'; open c;
fetch c into i;
close c;
select i as 'Parameter takes precedence over table column in cursors';
begin
declare a varchar(255) default 'a - local variable';
-declare c1 cursor for select a from t1;
+declare c1 cursor for select a from t3;
select a as 'A local variable takes precedence over parameter';
open c1;
fetch c1 into i;
@@ -4134,9 +4134,9 @@ close c1;
select i as 'A local variable takes precedence over parameter in cursors';
begin
declare a varchar(255) default 'a - local variable in a nested compound statement';
-declare c2 cursor for select a from t1;
+declare c2 cursor for select a from t3;
select a as 'A local variable in a nested compound statement takes precedence over a local variable in the outer statement';
-select a from t1 into i;
+select a from t3 into i;
select i as 'A local variable in a nested compound statement takes precedence over table column';
open c2;
fetch c2 into i;
@@ -4145,7 +4145,7 @@ select i as 'A local variable in a nested compound statement takes precedence o
end;
end;
end|
-call p1("a - stored procedure parameter")|
+call bug5967("a - stored procedure parameter")|
a
a - stored procedure parameter
Parameter takes precedence over table column
@@ -4162,7 +4162,7 @@ A local variable in a nested compound statement takes precedence over table colu
a - local variable in a nested compound statement
A local variable in a nested compound statement takes precedence over table column in cursors
a - local variable in a nested compound statement
-drop procedure p1|
+drop procedure bug5967|
drop procedure if exists bug13012|
create procedure bug13012()
BEGIN
@@ -4190,17 +4190,17 @@ call bug13012()|
Table Op Msg_type Msg_text
test.t1 repair status OK
test.t2 repair status OK
-test.t3 repair error Table 'test.t3' doesn't exist
+test.t3 repair status OK
test.v1 repair error 'test.v1' is not BASE TABLE
Table Op Msg_type Msg_text
test.t1 optimize status OK
test.t2 optimize status OK
-test.t3 optimize error Table 'test.t3' doesn't exist
+test.t3 optimize status OK
test.v1 optimize error 'test.v1' is not BASE TABLE
Table Op Msg_type Msg_text
test.t1 analyze status Table is already up to date
test.t2 analyze status Table is already up to date
-test.t3 analyze error Table 'test.t3' doesn't exist
+test.t3 analyze status Table is already up to date
test.v1 analyze error 'test.v1' is not BASE TABLE
Warnings:
Error 1146 Table 'test.t3' doesn't exist
@@ -4213,17 +4213,17 @@ call bug13012()|
Table Op Msg_type Msg_text
test.t1 repair status OK
test.t2 repair status OK
-test.t3 repair error Table 'test.t3' doesn't exist
+test.t3 repair status OK
test.v1 repair error 'test.v1' is not BASE TABLE
Table Op Msg_type Msg_text
test.t1 optimize status OK
test.t2 optimize status OK
-test.t3 optimize error Table 'test.t3' doesn't exist
+test.t3 optimize status OK
test.v1 optimize error 'test.v1' is not BASE TABLE
Table Op Msg_type Msg_text
test.t1 analyze status Table is already up to date
test.t2 analyze status Table is already up to date
-test.t3 analyze error Table 'test.t3' doesn't exist
+test.t3 analyze status Table is already up to date
test.v1 analyze error 'test.v1' is not BASE TABLE
Warnings:
Error 1146 Table 'test.t3' doesn't exist
@@ -4236,17 +4236,17 @@ call bug13012()|
Table Op Msg_type Msg_text
test.t1 repair status OK
test.t2 repair status OK
-test.t3 repair error Table 'test.t3' doesn't exist
+test.t3 repair status OK
test.v1 repair error 'test.v1' is not BASE TABLE
Table Op Msg_type Msg_text
test.t1 optimize status OK
test.t2 optimize status OK
-test.t3 optimize error Table 'test.t3' doesn't exist
+test.t3 optimize status OK
test.v1 optimize error 'test.v1' is not BASE TABLE
Table Op Msg_type Msg_text
test.t1 analyze status Table is already up to date
test.t2 analyze status Table is already up to date
-test.t3 analyze error Table 'test.t3' doesn't exist
+test.t3 analyze status Table is already up to date
test.v1 analyze error 'test.v1' is not BASE TABLE
Warnings:
Error 1146 Table 'test.t3' doesn't exist
@@ -4258,8 +4258,17 @@ Error 1347 'test.v1' is not BASE TABLE
drop procedure bug13012|
drop view v1;
select * from t1|
-a
-a - table column
+id data
+aa 0
+aa 1
+aa 2
+aa 3
+aa 4
+aa 5
+aa 6
+aa 7
+aa 8
+aa 9
drop schema if exists mysqltest1|
Warnings:
Note 1008 Can't drop database 'mysqltest1'; database doesn't exist
@@ -4299,4 +4308,31 @@ drop schema if exists mysqltest1|
drop schema if exists mysqltest2|
drop schema if exists mysqltest3|
use test|
+drop table if exists t3|
+drop procedure if exists bug15441|
+create table t3 (id int not null primary key, county varchar(25))|
+insert into t3 (id, county) values (1, 'York')|
+create procedure bug15441(c varchar(25))
+begin
+update t3 set id=2, county=values(c);
+end|
+call bug15441('county')|
+ERROR 42S22: Unknown column 'c' in 'field list'
+drop procedure bug15441|
+create procedure bug15441(county varchar(25))
+begin
+declare c varchar(25) default "hello";
+insert into t3 (id, county) values (1, county)
+on duplicate key update county= values(county);
+select * from t3;
+update t3 set id=2, county=values(id);
+select * from t3;
+end|
+call bug15441('Yale')|
+id county
+1 Yale
+id county
+2 NULL
+drop table t3|
+drop procedure bug15441|
drop table t1,t2;
diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result
index d42e439f4de..6094d23b0d0 100644
--- a/mysql-test/r/subselect.result
+++ b/mysql-test/r/subselect.result
@@ -2988,3 +2988,146 @@ max(fld)
1
drop table t1;
purge master logs before (select adddate(current_timestamp(), interval -4 day));
+CREATE TABLE t1 (a int, b int);
+CREATE TABLE t2 (c int, d int);
+CREATE TABLE t3 (e int);
+INSERT INTO t1 VALUES
+(1,10), (2,10), (1,20), (2,20), (3,20), (2,30), (4,40);
+INSERT INTO t2 VALUES
+(2,10), (2,20), (4,10), (5,10), (3,20), (2,40);
+INSERT INTO t3 VALUES (10), (30), (10), (20) ;
+SELECT a, MAX(b), MIN(b) FROM t1 GROUP BY a;
+a MAX(b) MIN(b)
+1 20 10
+2 30 10
+3 20 20
+4 40 40
+SELECT * FROM t2;
+c d
+2 10
+2 20
+4 10
+5 10
+3 20
+2 40
+SELECT * FROM t3;
+e
+10
+30
+10
+20
+SELECT a FROM t1 GROUP BY a
+HAVING a IN (SELECT c FROM t2 WHERE MAX(b)>20);
+a
+2
+4
+SELECT a FROM t1 GROUP BY a
+HAVING a IN (SELECT c FROM t2 WHERE MAX(b)<d);
+a
+2
+SELECT a FROM t1 GROUP BY a
+HAVING a IN (SELECT c FROM t2 WHERE MAX(b)>d);
+a
+2
+4
+SELECT a FROM t1 GROUP BY a
+HAVING a IN (SELECT c FROM t2
+WHERE d >= SOME(SELECT e FROM t3 WHERE MAX(b)=e));
+a
+2
+3
+SELECT a FROM t1 GROUP BY a
+HAVING a IN (SELECT c FROM t2
+WHERE EXISTS(SELECT e FROM t3 WHERE MAX(b)=e AND e <= d));
+a
+2
+3
+SELECT a FROM t1 GROUP BY a
+HAVING a IN (SELECT c FROM t2
+WHERE d > SOME(SELECT e FROM t3 WHERE MAX(b)=e));
+a
+2
+SELECT a FROM t1 GROUP BY a
+HAVING a IN (SELECT c FROM t2
+WHERE EXISTS(SELECT e FROM t3 WHERE MAX(b)=e AND e < d));
+a
+2
+SELECT a FROM t1 GROUP BY a
+HAVING a IN (SELECT c FROM t2
+WHERE MIN(b) < d AND
+EXISTS(SELECT e FROM t3 WHERE MAX(b)=e AND e <= d));
+a
+2
+SELECT a, SUM(a) FROM t1 GROUP BY a;
+a SUM(a)
+1 2
+2 6
+3 3
+4 4
+SELECT a FROM t1
+WHERE EXISTS(SELECT c FROM t2 GROUP BY c HAVING SUM(a) = c) GROUP BY a;
+a
+3
+4
+SELECT a FROM t1 GROUP BY a
+HAVING EXISTS(SELECT c FROM t2 GROUP BY c HAVING SUM(a) = c);
+a
+1
+3
+4
+SELECT a FROM t1
+WHERE a < 3 AND
+EXISTS(SELECT c FROM t2 GROUP BY c HAVING SUM(a) != c) GROUP BY a;
+a
+1
+2
+SELECT a FROM t1
+WHERE a < 3 AND
+EXISTS(SELECT c FROM t2 GROUP BY c HAVING SUM(a) != c);
+a
+1
+2
+1
+2
+2
+SELECT t1.a FROM t1 GROUP BY t1.a
+HAVING t1.a < ALL(SELECT t2.c FROM t2 GROUP BY t2.c
+HAVING EXISTS(SELECT t3.e FROM t3 GROUP BY t3.e
+HAVING SUM(t1.a+t2.c) < t3.e/4));
+a
+1
+2
+SELECT t1.a FROM t1 GROUP BY t1.a
+HAVING t1.a > ALL(SELECT t2.c FROM t2
+WHERE EXISTS(SELECT t3.e FROM t3 GROUP BY t3.e
+HAVING SUM(t1.a+t2.c) < t3.e/4));
+a
+4
+SELECT t1.a FROM t1 GROUP BY t1.a
+HAVING t1.a > ALL(SELECT t2.c FROM t2
+WHERE EXISTS(SELECT t3.e FROM t3
+WHERE SUM(t1.a+t2.c) < t3.e/4));
+ERROR HY000: Invalid use of group function
+SELECT t1.a from t1 GROUP BY t1.a HAVING AVG(SUM(t1.b)) > 20;
+ERROR HY000: Invalid use of group function
+SELECT t1.a FROM t1 GROUP BY t1.a
+HAVING t1.a IN (SELECT t2.c FROM t2 GROUP BY t2.c
+HAVING AVG(t2.c+SUM(t1.b)) > 20);
+a
+2
+3
+4
+SELECT t1.a FROM t1 GROUP BY t1.a
+HAVING t1.a IN (SELECT t2.c FROM t2 GROUP BY t2.c
+HAVING AVG(SUM(t1.b)) > 20);
+a
+2
+4
+SELECT t1.a, SUM(b) AS sum FROM t1 GROUP BY t1.a
+HAVING t1.a IN (SELECT t2.c FROM t2 GROUP BY t2.c
+HAVING t2.c+sum > 20);
+a sum
+2 60
+3 20
+4 40
+DROP TABLE t1,t2,t3;
diff --git a/mysql-test/t/bdb.test b/mysql-test/t/bdb.test
index 72b3ee89ed5..d3068b29e28 100644
--- a/mysql-test/t/bdb.test
+++ b/mysql-test/t/bdb.test
@@ -938,7 +938,25 @@ SELECT id FROM t1 WHERE (list_id = 1) AND (term = "lettera");
SELECT id FROM t1 WHERE (list_id = 1) AND (term = "letterd");
DROP TABLE t1;
-# End of 4.1 tests
+#
+# Bug #15536: Crash when DELETE with subquery using BDB tables
+#
+create table t1 (a int, key(a)) engine=bdb;
+create table t2 (b int, key(b)) engine=bdb;
+insert into t1 values (1),(1),(2),(3),(4);
+insert into t2 values (1),(5),(6),(7);
+delete from t1 where (a in (select b from t2));
+select count(*) from t1;
+# INSERT also blows up
+--error 1242
+insert into t1 set a=(select b from t2);
+select count(*) from t1;
+# UPDATE also blows up
+update t1 set a = a + 1 where (a in (select b from t2));
+select count(*) from t1;
+drop table t1, t2;
+
+--echo End of 4.1 tests
#
# alter temp table
diff --git a/mysql-test/t/func_gconcat.test b/mysql-test/t/func_gconcat.test
index cd686585dd8..058df9af56b 100644
--- a/mysql-test/t/func_gconcat.test
+++ b/mysql-test/t/func_gconcat.test
@@ -69,7 +69,7 @@ set group_concat_max_len = 1024;
# Test errors
--error 1111
-select group_concat(sum(a)) from t1 group by grp;
+select group_concat(sum(c)) from t1 group by grp;
--error 1054
select grp,group_concat(c order by 2) from t1 group by grp;
diff --git a/mysql-test/t/rpl_trigger.test b/mysql-test/t/rpl_trigger.test
index bf2836b6049..fa6054372c7 100644
--- a/mysql-test/t/rpl_trigger.test
+++ b/mysql-test/t/rpl_trigger.test
@@ -134,6 +134,35 @@ drop function bug12480;
drop table t1;
#
+# #14614: Replication of tables with trigger generates error message if databases is changed
+# Note. The error message is emitted by _myfree() using fprintf() to the stderr
+# and because of that does not fall into the .result file.
+#
+
+create table t1 (i int);
+create table t2 (i int);
+
+delimiter |;
+create trigger tr1 before insert on t1 for each row
+begin
+ insert into t2 values (1);
+end|
+delimiter ;|
+
+create database other;
+use other;
+insert into test.t1 values (1);
+
+save_master_pos;
+connection slave;
+sync_with_master;
+
+connection master;
+use test;
+drop table t1,t2;
+drop database other;
+
+#
# End of test
#
save_master_pos;
diff --git a/mysql-test/t/sp.test b/mysql-test/t/sp.test
index f8b638da59d..f73288f04ba 100644
--- a/mysql-test/t/sp.test
+++ b/mysql-test/t/sp.test
@@ -4914,24 +4914,24 @@ drop procedure bug14376|
#
--disable_warnings
-drop procedure if exists p1|
-drop table if exists t1|
+drop procedure if exists bug5967|
+drop table if exists t3|
--enable_warnings
-create table t1 (a varchar(255))|
-insert into t1 (a) values ("a - table column")|
-create procedure p1(a varchar(255))
+create table t3 (a varchar(255))|
+insert into t3 (a) values ("a - table column")|
+create procedure bug5967(a varchar(255))
begin
declare i varchar(255);
- declare c cursor for select a from t1;
+ declare c cursor for select a from t3;
select a;
- select a from t1 into i;
+ select a from t3 into i;
select i as 'Parameter takes precedence over table column'; open c;
fetch c into i;
close c;
select i as 'Parameter takes precedence over table column in cursors';
begin
declare a varchar(255) default 'a - local variable';
- declare c1 cursor for select a from t1;
+ declare c1 cursor for select a from t3;
select a as 'A local variable takes precedence over parameter';
open c1;
fetch c1 into i;
@@ -4939,9 +4939,9 @@ begin
select i as 'A local variable takes precedence over parameter in cursors';
begin
declare a varchar(255) default 'a - local variable in a nested compound statement';
- declare c2 cursor for select a from t1;
+ declare c2 cursor for select a from t3;
select a as 'A local variable in a nested compound statement takes precedence over a local variable in the outer statement';
- select a from t1 into i;
+ select a from t3 into i;
select i as 'A local variable in a nested compound statement takes precedence over table column';
open c2;
fetch c2 into i;
@@ -4950,8 +4950,8 @@ begin
end;
end;
end|
-call p1("a - stored procedure parameter")|
-drop procedure p1|
+call bug5967("a - stored procedure parameter")|
+drop procedure bug5967|
#
# Bug#13012 "SP: REPAIR/BACKUP/RESTORE TABLE crashes the server"
@@ -5029,6 +5029,56 @@ drop schema if exists mysqltest3|
use test|
#
+# Bug#15441 "Running SP causes Server to Crash": check that an SP variable
+# can not be used in VALUES() function.
+#
+--disable_warnings
+drop table if exists t3|
+drop procedure if exists bug15441|
+--enable_warnings
+create table t3 (id int not null primary key, county varchar(25))|
+insert into t3 (id, county) values (1, 'York')|
+
+# First check that a stored procedure that refers to a parameter in VALUES()
+# function won't parse.
+
+create procedure bug15441(c varchar(25))
+begin
+ update t3 set id=2, county=values(c);
+end|
+--error ER_BAD_FIELD_ERROR
+call bug15441('county')|
+drop procedure bug15441|
+
+# Now check the case when there is an ambiguity between column names
+# and stored procedure parameters: the parser shall resolve the argument
+# of VALUES() function to the column name.
+
+# It's hard to deduce what county refers to in every case (INSERT statement):
+# 1st county refers to the column
+# 2nd county refers to the procedure parameter
+# 3d and 4th county refers to the column, again, but
+# for 4th county it has the value of SP parameter
+
+# In UPDATE statement, just check that values() function returns NULL for
+# non- INSERT...UPDATE statements, as stated in the manual.
+
+create procedure bug15441(county varchar(25))
+begin
+ declare c varchar(25) default "hello";
+
+ insert into t3 (id, county) values (1, county)
+ on duplicate key update county= values(county);
+ select * from t3;
+
+ update t3 set id=2, county=values(id);
+ select * from t3;
+end|
+call bug15441('Yale')|
+drop table t3|
+drop procedure bug15441|
+
+#
# BUG#NNNN: New bug synopsis
#
#--disable_warnings
diff --git a/mysql-test/t/subselect.test b/mysql-test/t/subselect.test
index cc621fb5835..762ff36ba63 100644
--- a/mysql-test/t/subselect.test
+++ b/mysql-test/t/subselect.test
@@ -1968,3 +1968,86 @@ drop table t1;
purge master logs before (select adddate(current_timestamp(), interval -4 day));
+#
+# Test for bug #11762: subquery with an aggregate function in HAVING
+#
+
+CREATE TABLE t1 (a int, b int);
+CREATE TABLE t2 (c int, d int);
+CREATE TABLE t3 (e int);
+
+INSERT INTO t1 VALUES
+ (1,10), (2,10), (1,20), (2,20), (3,20), (2,30), (4,40);
+INSERT INTO t2 VALUES
+ (2,10), (2,20), (4,10), (5,10), (3,20), (2,40);
+INSERT INTO t3 VALUES (10), (30), (10), (20) ;
+
+SELECT a, MAX(b), MIN(b) FROM t1 GROUP BY a;
+SELECT * FROM t2;
+SELECT * FROM t3;
+
+SELECT a FROM t1 GROUP BY a
+ HAVING a IN (SELECT c FROM t2 WHERE MAX(b)>20);
+SELECT a FROM t1 GROUP BY a
+ HAVING a IN (SELECT c FROM t2 WHERE MAX(b)<d);
+SELECT a FROM t1 GROUP BY a
+ HAVING a IN (SELECT c FROM t2 WHERE MAX(b)>d);
+SELECT a FROM t1 GROUP BY a
+ HAVING a IN (SELECT c FROM t2
+ WHERE d >= SOME(SELECT e FROM t3 WHERE MAX(b)=e));
+SELECT a FROM t1 GROUP BY a
+ HAVING a IN (SELECT c FROM t2
+ WHERE EXISTS(SELECT e FROM t3 WHERE MAX(b)=e AND e <= d));
+SELECT a FROM t1 GROUP BY a
+ HAVING a IN (SELECT c FROM t2
+ WHERE d > SOME(SELECT e FROM t3 WHERE MAX(b)=e));
+SELECT a FROM t1 GROUP BY a
+ HAVING a IN (SELECT c FROM t2
+ WHERE EXISTS(SELECT e FROM t3 WHERE MAX(b)=e AND e < d));
+SELECT a FROM t1 GROUP BY a
+ HAVING a IN (SELECT c FROM t2
+ WHERE MIN(b) < d AND
+ EXISTS(SELECT e FROM t3 WHERE MAX(b)=e AND e <= d));
+
+SELECT a, SUM(a) FROM t1 GROUP BY a;
+
+SELECT a FROM t1
+ WHERE EXISTS(SELECT c FROM t2 GROUP BY c HAVING SUM(a) = c) GROUP BY a;
+SELECT a FROM t1 GROUP BY a
+ HAVING EXISTS(SELECT c FROM t2 GROUP BY c HAVING SUM(a) = c);
+
+SELECT a FROM t1
+ WHERE a < 3 AND
+ EXISTS(SELECT c FROM t2 GROUP BY c HAVING SUM(a) != c) GROUP BY a;
+SELECT a FROM t1
+ WHERE a < 3 AND
+ EXISTS(SELECT c FROM t2 GROUP BY c HAVING SUM(a) != c);
+
+SELECT t1.a FROM t1 GROUP BY t1.a
+ HAVING t1.a < ALL(SELECT t2.c FROM t2 GROUP BY t2.c
+ HAVING EXISTS(SELECT t3.e FROM t3 GROUP BY t3.e
+ HAVING SUM(t1.a+t2.c) < t3.e/4));
+SELECT t1.a FROM t1 GROUP BY t1.a
+ HAVING t1.a > ALL(SELECT t2.c FROM t2
+ WHERE EXISTS(SELECT t3.e FROM t3 GROUP BY t3.e
+ HAVING SUM(t1.a+t2.c) < t3.e/4));
+-- error 1111
+SELECT t1.a FROM t1 GROUP BY t1.a
+ HAVING t1.a > ALL(SELECT t2.c FROM t2
+ WHERE EXISTS(SELECT t3.e FROM t3
+ WHERE SUM(t1.a+t2.c) < t3.e/4));
+-- error 1111
+SELECT t1.a from t1 GROUP BY t1.a HAVING AVG(SUM(t1.b)) > 20;
+
+SELECT t1.a FROM t1 GROUP BY t1.a
+ HAVING t1.a IN (SELECT t2.c FROM t2 GROUP BY t2.c
+ HAVING AVG(t2.c+SUM(t1.b)) > 20);
+SELECT t1.a FROM t1 GROUP BY t1.a
+ HAVING t1.a IN (SELECT t2.c FROM t2 GROUP BY t2.c
+ HAVING AVG(SUM(t1.b)) > 20);
+
+SELECT t1.a, SUM(b) AS sum FROM t1 GROUP BY t1.a
+ HAVING t1.a IN (SELECT t2.c FROM t2 GROUP BY t2.c
+ HAVING t2.c+sum > 20);
+
+DROP TABLE t1,t2,t3;
View Lorry Controller Status