all_any_in.err

application/octet-stream
Filename: all_any_in.err
Type: application/octet-stream
Part: 0
Message: Re[2]: [HACKERS] BUG ON HAVING CLAUSE
I'm trying ANY, ALL and IN on informix and I have different results
who are right ?

select * from emp;
------+-----+----------+----------+---------+----+------+-----+----
ename |empno|job       |  hiredate|sal      |comm|deptno|level| mgr
------+-----+----------+----------+---------+----+------+-----+----
ALLEN | 7499|SALESMAN  |1981-02-20|$1,600.00| 300|    20|    4|7782
BLAKE | 7698|MANAGER   |1981-05-01|$2,850.00|    |    30|    3|7782
JONES | 7900|CLERK     |1981-12-03|$950.00  |    |    30|    2|7782
MILLER| 7654|SALESMAN  |1981-09-28|$1,250.00| 400|    30|    3|7839
CLARK | 7844|SALESMAN  |1981-09-08|$1,500.00|    |    10|    2|7839
JONES | 7521|SALESMAN  |1981-02-22|$1,250.00| 500|    10|    1|7782
(6 rows)

--PostgreSQL:
select * from emp where ename NOT IN (
     select ename from emp group by ename having 1 < count(ename)
     );
-----+-----+---+--------+---+----+------+-----+---
ename|empno|job|hiredate|sal|comm|deptno|level|mgr
-----+-----+---+--------+---+----+------+-----+---
(0 rows)

--Informix:     
------+-----+----------+----------+---------+----+------+-----+----
ename |empno|job       |  hiredate|sal      |comm|deptno|level| mgr
------+-----+----------+----------+---------+----+------+-----+----
ALLEN | 7499|SALESMAN  |1981-02-20|$1,600.00| 300|    20|    4|7782
BLAKE | 7698|MANAGER   |1981-05-01|$2,850.00|    |    30|    3|7782
MILLER| 7654|SALESMAN  |1981-09-28|$1,250.00| 400|    30|    3|7839
CLARK | 7844|SALESMAN  |1981-09-08|$1,500.00|    |    10|    2|7839
(4 rows)


--PostgreSQL:
select * from emp where ename IN (
     select ename from emp group by ename having 1 < count(ename)
     );
------+-----+----------+----------+---------+----+------+-----+----
ename |empno|job       |  hiredate|sal      |comm|deptno|level| mgr
------+-----+----------+----------+---------+----+------+-----+----
ALLEN | 7499|SALESMAN  |1981-02-20|$1,600.00| 300|    20|    4|7782
BLAKE | 7698|MANAGER   |1981-05-01|$2,850.00|    |    30|    3|7782
JONES | 7900|CLERK     |1981-12-03|$950.00  |    |    30|    2|7782
MILLER| 7654|SALESMAN  |1981-09-28|$1,250.00| 400|    30|    3|7839
CLARK | 7844|SALESMAN  |1981-09-08|$1,500.00|    |    10|    2|7839
JONES | 7521|SALESMAN  |1981-02-22|$1,250.00| 500|    10|    1|7782
(6 rows)

--Informix:     
------+-----+----------+----------+---------+----+------+-----+----
ename |empno|job       |  hiredate|sal      |comm|deptno|level| mgr
------+-----+----------+----------+---------+----+------+-----+----
JONES | 7900|CLERK     |1981-12-03|$950.00  |    |    30|    2|7782
JONES | 7521|SALESMAN  |1981-02-22|$1,250.00| 500|    10|    1|7782
(6 rows)

--PostgreSQL:
select * from emp where ename =ANY (
     select ename from emp group by ename having 1 < count(ename)
     );
ename |empno|job       |  hiredate|sal      |comm|deptno|level| mgr
------+-----+----------+----------+---------+----+------+-----+----
ALLEN | 7499|SALESMAN  |1981-02-20|$1,600.00| 300|    20|    4|7782
BLAKE | 7698|MANAGER   |1981-05-01|$2,850.00|    |    30|    3|7782
JONES | 7900|CLERK     |1981-12-03|$950.00  |    |    30|    2|7782
MILLER| 7654|SALESMAN  |1981-09-28|$1,250.00| 400|    30|    3|7839
CLARK | 7844|SALESMAN  |1981-09-08|$1,500.00|    |    10|    2|7839
JONES | 7521|SALESMAN  |1981-02-22|$1,250.00| 500|    10|    1|7782
(6 rows)

--Informix:     
-----+-----+----------+----------+---------+----+------+-----+----
ename|empno|job       |  hiredate|sal      |comm|deptno|level| mgr
-----+-----+----------+----------+---------+----+------+-----+----
JONES| 7900|CLERK     |1981-12-03|$950.00  |    |    30|    2|7782
JONES| 7521|SALESMAN  |1981-02-22|$1,250.00| 500|    10|    1|7782
(2 rows)

--PostgreSQL:
select * from emp where ename <>ANY (
     select ename from emp group by ename having 1 < count(ename)
     );
------+-----+----------+----------+---------+----+------+-----+----
ename |empno|job       |  hiredate|sal      |comm|deptno|level| mgr
------+-----+----------+----------+---------+----+------+-----+----
ALLEN | 7499|SALESMAN  |1981-02-20|$1,600.00| 300|    20|    4|7782
BLAKE | 7698|MANAGER   |1981-05-01|$2,850.00|    |    30|    3|7782
JONES | 7900|CLERK     |1981-12-03|$950.00  |    |    30|    2|7782
MILLER| 7654|SALESMAN  |1981-09-28|$1,250.00| 400|    30|    3|7839
CLARK | 7844|SALESMAN  |1981-09-08|$1,500.00|    |    10|    2|7839
JONES | 7521|SALESMAN  |1981-02-22|$1,250.00| 500|    10|    1|7782
(6 rows)

--Informix:     
------+-----+----------+----------+---------+----+------+-----+----
ename |empno|job       |  hiredate|sal      |comm|deptno|level| mgr
------+-----+----------+----------+---------+----+------+-----+----
ALLEN | 7499|SALESMAN  |1981-02-20|$1,600.00| 300|    20|    4|7782
BLAKE | 7698|MANAGER   |1981-05-01|$2,850.00|    |    30|    3|7782
MILLER| 7654|SALESMAN  |1981-09-28|$1,250.00| 400|    30|    3|7839
CLARK | 7844|SALESMAN  |1981-09-08|$1,500.00|    |    10|    2|7839
(4 rows)


--PostgreSQL:
select * from emp where ename <ANY (
     select ename from emp group by ename having 1 < count(ename)
     );
-----+-----+----------+----------+---------+----+------+-----+----
ename|empno|job       |  hiredate|sal      |comm|deptno|level| mgr
-----+-----+----------+----------+---------+----+------+-----+----
ALLEN| 7499|SALESMAN  |1981-02-20|$1,600.00| 300|    20|    4|7782
BLAKE| 7698|MANAGER   |1981-05-01|$2,850.00|    |    30|    3|7782
JONES| 7900|CLERK     |1981-12-03|$950.00  |    |    30|    2|7782
CLARK| 7844|SALESMAN  |1981-09-08|$1,500.00|    |    10|    2|7839
JONES| 7521|SALESMAN  |1981-02-22|$1,250.00| 500|    10|    1|7782
(5 rows)

--Informix:     
-----+-----+----------+----------+---------+----+------+-----+----
ename|empno|job       |  hiredate|sal      |comm|deptno|level| mgr
-----+-----+----------+----------+---------+----+------+-----+----
ALLEN| 7499|SALESMAN  |1981-02-20|$1,600.00| 300|    20|    4|7782
BLAKE| 7698|MANAGER   |1981-05-01|$2,850.00|    |    30|    3|7782
CLARK| 7844|SALESMAN  |1981-09-08|$1,500.00|    |    10|    2|7839
(3 rows)


--PostgreSQL:
select * from emp where ename >ANY (
     select ename from emp group by ename having 1 < count(ename)
     );
------+-----+----------+----------+---------+----+------+-----+----
ename |empno|job       |  hiredate|sal      |comm|deptno|level| mgr
------+-----+----------+----------+---------+----+------+-----+----
BLAKE | 7698|MANAGER   |1981-05-01|$2,850.00|    |    30|    3|7782
JONES | 7900|CLERK     |1981-12-03|$950.00  |    |    30|    2|7782
MILLER| 7654|SALESMAN  |1981-09-28|$1,250.00| 400|    30|    3|7839
CLARK | 7844|SALESMAN  |1981-09-08|$1,500.00|    |    10|    2|7839
JONES | 7521|SALESMAN  |1981-02-22|$1,250.00| 500|    10|    1|7782
(5 rows)

--Informix:     
------+-----+----------+----------+---------+----+------+-----+----
ename |empno|job       |  hiredate|sal      |comm|deptno|level| mgr
------+-----+----------+----------+---------+----+------+-----+----
MILLER| 7654|SALESMAN  |1981-09-28|$1,250.00| 400|    30|    3|7839
(1 rows)

--PostgreSQL:
select * from emp where ename =ALL (
     select ename from emp group by ename having 1 < count(ename)
     );
ename|empno|job|hiredate|sal|comm|deptno|level|mgr
-----+-----+---+--------+---+----+------+-----+---
(0 rows)

--Informix:     
-----+-----+----------+----------+---------+----+------+-----+----
ename|empno|job       |  hiredate|sal      |comm|deptno|level| mgr
-----+-----+----------+----------+---------+----+------+-----+----
JONES| 7900|CLERK     |1981-12-03|$950.00  |    |    30|    2|7782
JONES| 7521|SALESMAN  |1981-02-22|$1,250.00| 500|    10|    1|7782
(2 rows)

--PostgreSQL:
select * from emp where ename >ALL (
     select ename from emp group by ename having 1 < count(ename)
     );
ename|empno|job|hiredate|sal|comm|deptno|level|mgr
-----+-----+---+--------+---+----+------+-----+---
(0 rows)

--Informix:     
------+-----+----------+----------+---------+----+------+-----+----
ename |empno|job       |  hiredate|sal      |comm|deptno|level| mgr
------+-----+----------+----------+---------+----+------+-----+----
MILLER| 7654|SALESMAN  |1981-09-28|$1,250.00| 400|    30|    3|7839
(1 rows)

--PostgreSQL:
select * from emp where ename <ALL (
     select ename from emp group by ename having 1 < count(ename)
     );
ename|empno|job|hiredate|sal|comm|deptno|level|mgr
-----+-----+---+--------+---+----+------+-----+---
(0 rows)

--Informix:     
-----+-----+----------+----------+---------+----+------+-----+----
ename|empno|job       |  hiredate|sal      |comm|deptno|level| mgr
-----+-----+----------+----------+---------+----+------+-----+----
ALLEN| 7499|SALESMAN  |1981-02-20|$1,600.00| 300|    20|    4|7782
BLAKE| 7698|MANAGER   |1981-05-01|$2,850.00|    |    30|    3|7782
CLARK| 7844|SALESMAN  |1981-09-08|$1,500.00|    |    10|    2|7839
(3 rows)

--PostgreSQL:
select * from emp where ename <>ALL (
     select ename from emp group by ename having 1 < count(ename)
     );
ename|empno|job|hiredate|sal|comm|deptno|level|mgr
-----+-----+---+--------+---+----+------+-----+---
(0 rows)

--Informix:     
------+-----+----------+----------+---------+----+------+-----+----
ename |empno|job       |  hiredate|sal      |comm|deptno|level| mgr
------+-----+----------+----------+---------+----+------+-----+----
ALLEN | 7499|SALESMAN  |1981-02-20|$1,600.00| 300|    20|    4|7782
BLAKE | 7698|MANAGER   |1981-05-01|$2,850.00|    |    30|    3|7782
MILLER| 7654|SALESMAN  |1981-09-28|$1,250.00| 400|    30|    3|7839
CLARK | 7844|SALESMAN  |1981-09-08|$1,500.00|    |    10|    2|7839
(4 rows)