Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.4(EOL), 10.5, 10.6, 10.7(EOL), 10.8(EOL), 10.9(EOL), 10.10(EOL), 10.11, 11.1(EOL)
-
None
-
None
Description
Not sure how important or practical this is, because having is normally used with group by, but just in case it is a bug worth addressing, here's the mtr case
|
11.1 4e5b771e980edfdad5c5414aa62c81d409d585a4 |
create table t1 (a1 int, a2 char(8)); |
create table t2 (b1 int, b2 char(8)); |
|
|
insert into t1 values (0, '2 - 00'); |
insert into t1 values (1, '2 - 01'); |
insert into t1 values (2, '2 - 02'); |
|
|
insert into t2 values (1, '2 - 01'); |
insert into t2 values (1, '2 - 01'); |
insert into t2 values (2, '2 - 02'); |
insert into t2 values (2, '2 - 02'); |
insert into t2 values (3, '2 - 03'); |
|
|
# ok
|
select * from t1 having a1 > 0; |
# ok
|
select * from t2 where exists (select * from t1 where a1 > 0); |
# ER_BAD_FIELD_ERROR (1054): Unknown column 'a1' in 'having clause' |
--error ER_BAD_FIELD_ERROR
|
select * from t2 where exists (select * from t1 having a1 > 0); |
|
|
# ok
|
select * from t2 where exists (select * from t1 where a1 = b1); |
# ER_BAD_FIELD_ERROR (1054): Unknown column 'a1' in 'having clause' |
--error ER_BAD_FIELD_ERROR
|
select * from t2 where exists (select * from t1 having a1 = b1); |
# ok
|
select * from t2 where b2 in (select a2 from t1 group by a1 having a1 = b1); |
# ok
|
select * from t2 where b1 in (select a1 from t1 group by a1 having a1 = b1); |
|
|
drop table t1, t2; |