Wildcards and NULL fields

[DPlank]

This is probably a very quick one.

I have a test client (a .jsp) which runs a search service against an Oracle 10g DB.

When I specify certain data, I find I get unexpected results.

If I specify Customer Country and Customer Number I get the expected data returned.

If I specify the above data, and add a * to another field (which is permitted) I get zero results from the search. I've checked the DB and the field in question is NULL for the data. Should this be returned by the wildcard or is Oracle finding no match because the wildcard tells it to expect to find something in the field?

Cheers,
Dave

Probably the only Test Analyst Manager on Tek-Tips...therefore whatever it was that went wrong, I'm to blame...

animadverto vos in Abyssus!

Take a look at Forum1393!

 

[SantaMufasa]

Dave,

Since "*" is not a valid Oracle SQL wildcard-comparison token, I'd have to see your actual code before I offer a response. Additionally, the only "wildcard" that compares favourably to NULL is "IS NULL" or "IS NOT NULL".

So, could you please post your actual code (if reasonably sized), some sample data, and the results you expect from your code?

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[DPlank]

Sorry Dave,

I'm using a client jsp to submit the search to the db. I don't have access to see the SQL that gets generated.

Essentially, I was looking to find out if an Oracle db would return NULL fields when the search criteria was a wildcard. I believe the search system I'm using replaces my asterisl with a % symbol instead.

Sample Data :
Country Customer No.
ES 1785880

Expected result - Customer record shown.

Sample Data :
Country Customer No. City
ES 1785880 *

Expected Result - I had expected the search to treat the wildcard as "check all possible states of this column" i.e. I thought it would be true for all variations of all characters and also NULL as NULL is a valid state for the column. Therefore I expected the same result as the first search. What I got was zero search results.

I've spoken with our DB Designer and he's experimented with SQL using LIKE '%' in his WHERE clause. He reckons it only returns rows where there is a value, and ignores any rows where that column has a NULL value.

Cheers,
Dave

Probably the only Test Analyst Manager on Tek-Tips...therefore whatever it was that went wrong, I'm to blame...

animadverto vos in Abyssus!

Take a look at Forum1393!

 

[SantaMufasa]

Your designer is correct. If you say "WHERE <expression> LIKE '%'" it means "Display the current row if <expression> has any character...", and NULL is the absence of any characters. The following illustrates this point:

SQL> select count(*) from s_emp;

  COUNT(*)
----------
        25

select commission_pct from s_emp where commission_pct like '%';

COMMISSION_PCT
--------------
            10
          12.5
            10
            15
          17.5

To be able to see the rows that contain NULLs in a particular expression, you must force your front-end software to also ask "...OR IS NULL".

Let us know if this resolves your inquiry.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[PCLewis]

The way Oracle handles NULL is (in my opinion) poor and yet, if I had "grown up with it" I would probably see it the other way around.

As the previous poster has indicated, only the IS NULL and IS NOT NULL will have any success in a WHERE clause involving a field that might be NULL. Adding the "OR IS NULL" to your clauses is the only way to go.

Why I think Oracle are weird is because of the following:

In some DB's NULL (in a string field) is the absence of data whereas in Oracle, it is the Absence of characters. In some DB's an empty string is inded considered as data.

In Oracle if you set a string field (VARCHAR2 for example) to '' then immediately query that table for all rows with that field = '' you get no hits. You have to query "IS NULL" to get it.

In my view an empty string is not the same as NULL. And if it is the same as NULL, then you should be able to query for it with ='' and also it should be selected when selecting that field "LIKE '%'.

 

[SantaMufasa]

PC,

Yes, If you had "grown up" with Oracle's perception of NULL, then you certainly would be more comfortable.

If you consider that the "=" operator means, "Do the bits to the left of the '=' operator match the bits to the right of the operator?", then if there are not bits on one side or the other, then there is no match, by definition.

It also makes life more livable if you also consider that in the Oracle World, NULL logically means "an unknown value". Is "an unknown value" equal to "an unknown value"?...Not in the Oracle World. All you can determine, with surety, about "an unknown value" is that it is "an unknown value"...i.e., IS NULL or IS NOT NULL.

If this does not give you a warm, fuzzy feeling, then all we can say with surety is that you do not have a warm, fuzzy feeling...because Oracle will always work this way unless customers vote otherwise with their checkbooks.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.