Misterious query result (Is it a bug ??)

[Turin]

Hi friends, I don't know what is happening, this really is a great mistery for me. I don't know if this is an Oracle bug, but a query is failing! I'm using Oracle 9i on Windows 2000.

This is the problem, I want to know what records exist in the table "documentos" that have a value in the column "ficha" which is not in table "personal_rem" in order to delete them.

First I apply this query to know the situation:

SQL> select ficha from documentos where ficha not in ( select ficha from personal_rem );

no rows selected

But, when I'm check the records of the table "documentos" I found some values of the column "ficha" that doesn't exist in the table "personal_rem".

These are the queries:

SQL> select ficha from documentos where ficha = 'F00081';

FICHA
----------
F00081
F00081
F00081

SQL> select ficha from personal_rem where ficha = 'F00081';

no rows selected


The record with the value of "F00081" doesn't appear in my first query. Why ??? I don't know.
What is happening ??

So I apply another query, that gave me the result that I was expecting. The query is:


SQL> select ficha from
2 documentos
3 where ficha not in
4 (
5 select distinct ficha from documentos where ficha
6 in (select ficha from personal_rem)
7* )

FICHA
----------
F00036
F00081
F00036
F00085
F00047
F00096
F00024
F00040
F00081
F00081

I think my first query must give the same result that the last one, but this is not happening. If some of you know how to solve this mistery please, let me know.

Thanks in advance

Regards

[abhaysk]

Originally posted by Turin

SQL> select ficha from documentos where ficha not in ( select ficha from personal_rem );

no rows selected

SQL> select ficha from documentos where ficha = 'F00081';

FICHA
----------
F00081
F00081
F00081

SQL> select ficha from personal_rem where ficha = 'F00081';

no rows selected

SQL> select ficha from
2 documentos
3 where ficha not in
4 (
5 select distinct ficha from documentos where ficha
6 in (select ficha from personal_rem)
7* )

FICHA
----------
F00036
F00081
F00036
F00085
F00047
F00096
F00024
F00040
F00081
F00081

Again oracle is behaving stupidly here..... as was in the case
http://www.dbasupport.com/forums/sho...426#post151426


Abhay.

[marist89]

Originally posted by abhaysk
Again oracle is behaving stupidly here..... as was in the case
http://www.dbasupport.com/forums/sho...426#post151426


Abhay.

It's more likely, IMHO, that with a character based key there is an inconsistency in the data. I am betting trailing space, zero for oh substitution, etc.

[Turin]

I solved this thanks to metalink.
Thanks anyway

This was the solution:

"
Some row in the table personal_rem(ficha field) might be null

Try this query

select ficha from documentos where ficha not in ( select ficha from personal_rem where ficha is not null ); "

I found some null values in the field "ficha" of the table "personal_rem", so this was the reason of the problem.

Regards

[abhaysk]

Originally posted by marist89
I am betting trailing space, zero for oh substitution, etc.

this would not effect the query result coz space or 0 or so has ascii value and comparion with it would result either true or false.

take for eg u had empname as 'Abhay ' in one tab and other had 'Abhay'

then the query

Select * from emp where emp_name not in ( Select emp_name from emp1 );

would give result as 'Abhay '

PS

SQL> insert into emp(id,emp_name) values(1,'Abhay ');

1 row created.

Commit complete.
SQL> insert into emp1(id,emp_name) values(1,'Abhay');

1 row created.

SQL> select * from emp where emp_name not in ( select emp_name from emp1 );


                  ID EMP_NAME    
-------------------- ----------- 
                   1 Abhay

[abhaysk]

Originally posted by Turin
I solved this thanks to metalink.
Thanks anyway

Metlink doc id?
or get me the link?

Thanks
Abhay.

[Turin]

Subject:

Possible bug: SELECT ... from T1 where c NOT IN (SELECT c from T2) is not working!!

Forum: Oracle PL/SQL Technical Forum

Doc ID: 352073.996

Regards