IS NULL OR IS NOT NULL

Bonjour

j'ai une table avec des record dedans

imaginons 2 colonnes
ID     EMAIL
1000  null
quand je fais

Code:

select * from matable where id = 1000

j'ai bien ma ligne

et quand je fais

Code:

select * from matable where email is null and id = 1000

je n'ai rien

alors je teste

Code:

select * from matable where email is not null and id = 1000

mais je n'ai toujours rien

je teste alors un group by sur email mais la valeur

Citation:

null

ne ressort pas non plus

je ne comprends pas vraiment.

avez vous une idée

Faite un
Code:

1 2 Select dump(email) from table where id = 1000
pour voir ce qu'il y a dans la base.

Merci,

je vais ouvrir un ticket chez oracle car c'est vraiment bizzare.

quand je fais where email = 'azerty' la ligne avec l'id 1000 ressort alors que l'email ne vaut pas azerty.

je viens de trouver le probleme, j'ai desactiver la compression de la table sur le onload.

une fois que j'ai trouvé le probleme j'ai put affiner mes recherches dans google et j'ai trouvé ce post

Citation:

Regarding critical bugs and table compression, take a look at the following note from MetaLink. What I like most is the recommended "workaround":
Bug 7123643 Wrong results (NULL) from select on compressed table

It is believed to be a regression in default behaviour thus:

Regression introduced in 10.2.0.4

Regression introduced in 11.1.0.6

Symptoms:

* Wrong Results

Related To:

* Compressed Data Storage

Description

This problem is introduced in 10.2.0.4 and 11.1.0.6 by the

fix for bug 5912350.

SELECT against a compressed table can return NULL data

for columns which contain data in certain scenarios.

This problem only affects compressed tables where the data has

been loaded using bulk load (CTAS, import, Insert as select etc...).

There is no persistent corruption.

Workaround:

Do not use compressed tables
By the way, as mentioned in the description, this bug was introduced by the fix for the bug
5912350: Dump (kdr9ir2f0rst4srp0) on select of compressed table having > 255 columns

Symptoms:

* Process May Dump (ORA-7445) / Abend / Abort

* Dump in or under kdr9ir2F0rst4srp0

Related To:

* Compressed Data Storage

Description

A dump can occur when selecting from a COMPRESSED

table with > 255 columns.

Workaround:

Do not use compressed tables with more than 255 columns.

Note:

This fix introduces the problem described in bug 7123643.

One off fixes for this bug (5912350) should be deinstalled and

replaced with the fix for bug 7123643.
So it looks like using table compression is still a bit risky. I hoped that it is more mature now that this feature has been there for quite a long time.

source : https://community.oracle.com/message/2691128