Thread: Differentiate Between Zero-Length String and NULL Column Values
Hi All, Small problem with 8.2.1, I have a temp table of basic stock details:CREATE TEMP TABLE tmpstk( code varchar(6), descriptionvarchar(38), grp varchar(4), brand text, style text, supplier varchar(6), supplier_code text, wholesalenumeric(10,2), retail numeric(10,2), ean varchar(13))WITHOUT OIDS; This table is populated using a COPY query works OK. If I try the following query:SELECT * FROM tmpstk I get what I expect, almost 8000 rows including rows similar to the following: "401514","EQUINADE_SHOWSILK_SHAMPOO_5L","3209","EQUINADE","SHAMPOO","EQUEST" ,"401514","0.00","0.00","0.00","0.00","10.00","9329028056594" "401600","A/DRESS_SHAMPOO_TEA_TREE_OIL_500ML","3208","AUSTRALIAN_DRESSAGE"," SHAMPOO","EQUEST","401600","0.00","0.00","0.00","0.00","10.00","" The second row (401600) is what Im interested in for this particular problem. The problem is when I try and add a WHERE clause:SELECT * FROM tmpstk WHERE ean = '';SELECT * FROM tmpstk WHERETRIM(ean) = '';SELECT * FROM tmpstk WHERE ean = NULL; None of the above queries return any rows. What am I doing wrong? It should return the 401600 row above, along with several hundred other rows. I have attached the bash script that actually does this processing. Help is much appreciated. Cheers, -p Phillip Smith IT Coordinator Weatherbeeta P/L AUSTRALIA E. phillip.smith@NO-SPAM.weatherbeeta.com.au *******************Confidentiality and Privilege Notice******************* The material contained in this message is privileged and confidential to the addressee. If you are not the addressee indicated in this message or responsible for delivery of the message to such person, you may not copy or deliver this message to anyone, and you should destroy it and kindly notify the sender by reply email. Information in this message that does not relate to the official business of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta. Weatherbeeta, its employees, contractors or associates shall not be liable for direct, indirect or consequential loss arising from transmission of this message or any attachments
On Tue, Jan 30, 2007 at 04:32:22PM +1100, Phillip Smith wrote: > The second row (401600) is what I'm interested in for this particular > problem. The problem is when I try and add a WHERE clause: > SELECT * FROM tmpstk WHERE ean = ''; > SELECT * FROM tmpstk WHERE TRIM(ean) = ''; > SELECT * FROM tmpstk WHERE ean = NULL; > None of the above queries return any rows. Checking for equality against NULL won't work unless you have transform_null_equals set, which you shouldn't. Use IS NULL instead: SELECT * FROM tmpstk WHERE ean IS NULL; -- Michael Fuhr
On Tue, Jan 30, 2007 at 04:32:22PM +1100, Phillip Smith wrote:
> SHAMPOO","EQUEST","401600","0.00","0.00","0.00","0.00","10.00",""
^^
> SELECT * FROM tmpstk WHERE ean = NULL; ^^^^
Along with what Michael Fuhr said in his post about equality and
NULL, Postgres doesn't treat the empty string and NULL as equivalent
(because they're not). Only Oracle has that dodgy interpretation of
SQL, as far as I know. If you want to use the empty string, you need
WHERE ean = ''
If you want instead ean to be NULL, use the traditional \N to signify
NULL on your way in, or define null some other way.
A
--
Andrew Sullivan | ajs@crankycanuck.ca
Users never remark, "Wow, this software may be buggy and hard
to use, but at least there is a lot of code underneath." --Damien Katz
Andrew, I think you're wrong stating that Oracle would interpret NULL and empty string as equal.
The Oracle databases I use (8, 9 and 10) certainly make a distiction between both values.
Maybe earlier versions did so, that I don't know.
>>> Andrew Sullivan <ajs@crankycanuck.ca> 2007-01-30 14:13 >>>
On Tue, Jan 30, 2007 at 04:32:22PM +1100, Phillip Smith wrote:
> SHAMPOO","EQUEST","401600","0.00","0.00","0.00","0.00","10.00",""
^^
> SELECT * FROM tmpstk WHERE ean = NULL;
^^^^
Along with what Michael Fuhr said in his post about equality and
NULL, Postgres doesn't treat the empty string and NULL as equivalent
(because they're not). Only Oracle has that dodgy interpretation of
SQL, as far as I know. If you want to use the empty string, you need
WHERE ean = ''
If you want instead ean to be NULL, use the traditional \N to signify
NULL on your way in, or define null some other way.
A
--
Andrew Sullivan | ajs@crankycanuck.ca
Users never remark, "Wow, this software may be buggy and hard
to use, but at least there is a lot of code underneath."
--Damien Katz
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
On Tue, Jan 30, 2007 at 02:38:07PM +0100, Bart Degryse wrote: > Andrew, I think you're wrong stating that Oracle would interpret > NULL and empty string as equal. The Oracle databases I use (8, 9 > and 10) certainly make a distiction between both values. Maybe > earlier versions did so, that I don't know. Hmm. Well, I'm not an Oracle guy, so I don't really know. All I know is that we occasionally get people coming from Oracle who are surprised by this difference. What I've been _told_ is that '' and NULL are under some circumstances (maybe integers?) the same thing, whereas of course ' ' and NULL are not. But since I'm not an Oracle user, people should feel free to ignore me :) A -- Andrew Sullivan | ajs@crankycanuck.ca Everything that happens in the world happens at some place. --Jane Jacobs
Andrew Sullivan Wrote:
> On Tue, Jan 30, 2007 at 02:38:07PM +0100, Bart Degryse wrote:
> > Andrew, I think you're wrong stating that Oracle would interpret
> > NULL and empty string as equal. The Oracle databases I use (8, 9
> > and 10) certainly make a distiction between both values. Maybe
> > earlier versions did so, that I don't know.
>
> Hmm. Well, I'm not an Oracle guy, so I don't really know. All I
> know is that we occasionally get people coming from Oracle who are
> surprised by this difference. What I've been _told_ is that '' and
> NULL are under some circumstances (maybe integers?) the same thing,
> whereas of course ' ' and NULL are not. But since I'm not an Oracle
> user, people should feel free to ignore me :)
Sybase does something like that... In sybase, null and empty string are the
same. However, to avoid the equality ''=NULL, they actually interpret '' as a
single space. So if you do something like SELECT 'A'+''+'C' (concatenation is
+ in sybase), it results in 'A C'. Null is a "real" empty string in that its
length is zero, and if you insert a trim('') into a column, it will treat it
as NULL.
Herouth
On Tue, 30 Jan 2007 09:23:32 -0500 Andrew Sullivan <ajs@crankycanuck.ca> wrote: > Hmm. Well, I'm not an Oracle guy, so I don't really know. All I > know is that we occasionally get people coming from Oracle who are > surprised by this difference. What I've been _told_ is that '' and > NULL are under some circumstances (maybe integers?) the same thing, > whereas of course ' ' and NULL are not. But since I'm not an Oracle > user, people should feel free to ignore me :) I don't have an Oracle installation here and I haven't used it much but I wonder if they treat the following two statements differently. SELECT * FROM table WHERE column IS NULL; SELECT * FROM table WHERE column = NULL; The latter violates the SQL spec and is not allowed by PostgreSQL without setting a special flag. Is it possible that Oracle accepts "=" against NULL and also treats it slightly differently? -- D'Arcy J.M. Cain <darcy@druid.net> | Democracy is three wolves http://www.druid.net/darcy/ | and a sheep voting on +1 416 425 1212 (DoD#0082) (eNTP) | what's for dinner.
On Tue, Jan 30, 2007 at 11:38:34AM -0500, D'Arcy J.M. Cain wrote: > I don't have an Oracle installation here and I haven't used it much but > I wonder if they treat the following two statements differently. > > SELECT * FROM table WHERE column IS NULL; > SELECT * FROM table WHERE column = NULL; AFAIK they don't accept the latter any more than we do. But again, I'm an Oracle ignoramous. I _do_ know that people of my acquaintance who have historically only developed against Oracle have given me queries with things like value = '' in it, and been surprised. Or at least, I think it's like that. I do recall hearing a lot about how stupid Postgres was because it didn't like something that worked "perfectly well" on Oracle, which I was assured was the most SQL-compliant system on the planet. Happily, I no longer work with any of those people :) A -- Andrew Sullivan | ajs@crankycanuck.ca "The year's penultimate month" is not in truth a good way of saying November. --H.W. Fowler
Andrew Sullivan wrote: > AFAIK they don't accept the latter any more than we do. But again, > I'm an Oracle ignoramous. I _do_ know that people of my acquaintance > who have historically only developed against Oracle have given me > queries with things like > > value = '' > > in it, and been surprised. Or at least, I think it's like that. I > do recall hearing a lot about how stupid Postgres was because it > didn't like something that worked "perfectly well" on Oracle, which I > was assured was the most SQL-compliant system on the planet. > Happily, I no longer work with any of those people :) At my last job I used Oracle 8i and 9i (standard editions), and I very definitely remember the pain of it interpreting the empty string as a NULL (especially relevant when input needed trimming). - Geoff
D'Arcy J.M. Cain wrote: > SELECT * FROM table WHERE column IS NULL; > SELECT * FROM table WHERE column = NULL; > > The latter violates the SQL spec and is not allowed by PostgreSQL > without setting a special flag. It doesn't violate any spec and it's certainly allowed by PostgreSQL without any flags. It's just that the result is not what some people expect. -- Peter Eisentraut http://developer.postgresql.org/~petere/
> On Tue, Jan 30, 2007 at 02:38:07PM +0100, Bart Degryse wrote: > >> Andrew, I think you're wrong stating that Oracle would interpret >> NULL and empty string as equal. The Oracle databases I use (8, 9 >> and 10) certainly make a distiction between both values. Maybe >> earlier versions did so, that I don't know. > > Hmm. Well, I'm not an Oracle guy, so I don't really know. All I > know is that we occasionally get people coming from Oracle who are > surprised by this difference. What I've been _told_ is that '' and > NULL are under some circumstances (maybe integers?) the same thing, > whereas of course ' ' and NULL are not. But since I'm not an Oracle > user, people should feel free to ignore me :) I've recently read some books on Oracle, so probably the best thing I can do is to quote a paragraph on this from "Oracle PL/SQL programming" from O'Reilly: In Oracle SQL and PL/SQL, a null string is 'usually' indistiguishable from a literal of zero characters, represented literally as ''. For example the following expression will evaluate to TRUE both in SQL and PL/SQL: '' IS NULL Assigning a zero-length string to a VARCHAR2(n) variable in PL/SQL also yields a NULL result: DECLARE str VARCHAR2(1) := ''; BEGIN IF str IS NULL -- will be TRUE This behavior is consistent with Oracle's treatment of VARCHAR2 table columns. ... These examples illustrate Oracle's partial adherence to the 92 and 99 versions of the ANSI SQL standard, which mandates a difference between a zero-length string and a NULL string. Oracle admits the difference, and says they may fully adopt the standard in the future. They've been issuing that warning for about 10 years, though, and it hasn't happened yet. ... Note: This does not apply to the CHAR(n) columns - these are blank-padded. Tomas
On Wed, Jan 31, 2007 at 12:18:03PM -0800, BillR wrote: > Peter Eisentraut wrote: > >It doesn't violate any spec and it's certainly allowed by PostgreSQL > >without any flags. It's just that the result is not what some people > >expect. > "= NULL" violates the SQL-92 Specification. I don't want to put words in his mouth, but I think you missed the bit where Peter said "the result is not what some people expect". Hint: 'somevalue = NULL' is not a violation of SQL in that you don't get an ERROR. A -- Andrew Sullivan | ajs@crankycanuck.ca "The year's penultimate month" is not in truth a good way of saying November. --H.W. Fowler
Just a datapoint: SQL*Plus: Release 10.1.0.3.0 - Production on Tue Jan 30 15:15:49 2007 Copyright (c) 1982, 2004, Oracle. All rights reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.3.0 - Production With the Partitioning, OLAP and Data Mining options SQL> select * from dual where '' IS NULL; D - X SQL> select * from dual where '' = NULL; no rows selected -j
Peter Eisentraut wrote:
> D'Arcy J.M. Cain wrote:
>
>> SELECT * FROM table WHERE column IS NULL;
>> SELECT * FROM table WHERE column = NULL;
>>
>> The latter violates the SQL spec and is not allowed by PostgreSQL
>> without setting a special flag.
>>
>
> It doesn't violate any spec and it's certainly allowed by PostgreSQL
> without any flags. It's just that the result is not what some people
> expect.
>
>
"= NULL" violates the SQL-92 Specification. Relevant section posted below.
Additionally *none of the example code* in the SQL-92 specification
document uses the expression "WHERE #value# = NULL"
*All* the example code in the specification use the expression as:
"WHERE #value# IS NULL"
8.6 <null predicate>
Function
Specify a test for a null value.
Format
<null predicate> ::= <row value constructor> IS [ NOT ] NULL
Syntax Rules
None.
Access Rules
None.
General Rules
1) Let R be the value of the <row value constructor>.
2) If all the values in R are the null value, then "R IS NULL" is true; otherwise, it is false.
3) If none of the values in R are the null value, then "R IS NOT NULL" is true; otherwise, it is
false.
Note: For all R, "R IS NOT NULL" has the same result as "NOT R IS NULL" if and only if R is of
degree1. Table 12, "<null predicate> semantics", specifies this behavior.
________________Table_12-<null_predicate>_semantics________________
R IS R IS NOT NOT R IS NOT R IS NOT
_Expression_______NULL____NULL__________NULL__________NULL_________
| degree 1: null | true | false | false | true | | | |
| | | | degree 1: not | false | true | true | false | null
| degree > 1: | true | false | false | true | | all null | |
| | | | | | | | | | degree
>1: | false | false | true | true | | some null | | | |
| | | | | | | | degree > 1: | false
|true | true | false | |_none_null______|_______|_____________|____________|______________|
| | | | | | |Leveling Rules | | |
| | | | | | | | 218
DatabaseLanguage SQL
On 1/30/2007 3:17 PM, Jamie A Lawrence wrote: > Just a datapoint: > > SQL*Plus: Release 10.1.0.3.0 - Production on Tue Jan 30 15:15:49 2007 > > Copyright (c) 1982, 2004, Oracle. All rights reserved. > > > Connected to: > Oracle Database 10g Enterprise Edition Release 10.1.0.3.0 - Production > With the Partitioning, OLAP and Data Mining options It is well known that Oracle's handling of zero length strings violates all ANSI SQL Standards, so what exactly is your point? Jan > > SQL> select * from dual where '' IS NULL; > > D > - > X > > SQL> select * from dual where '' = NULL; > > no rows selected > > > -j > > ---------------------------(end of broadcast)--------------------------- > TIP 1: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly -- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #================================================== JanWieck@Yahoo.com #