Thread

IN bug

jose' soares <sferac@bo.nettuno.it> — 1998-12-03T14:35:34Z

Hi all,
Seems there is a bug on IN (subselect)

I'm looking for duplicate rows and I found 2:

select ename from emp group by ename having 1 < count(ename);
ename
-----
ALLEN
BLAKE
(2 rows)

Using IN to display all columns of duplicate rows I have the
following:


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
KING  | 7521|SALESMAN  |1981-02-22|$1,250.00| 500|    10|    1|7782
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
(8 rows)


but I expect the following instead:

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
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
(4 rows)

-Jose'-