Nulls get converted to 0 problem

On Wed, Jun 04, 2003 at 10:26:22AM -0500, Avi Schwartz wrote:

value Coldfusion is setting the integer variables to is 0 (zero) and
not "" as it should. This of course is a big problem. I checked with
other people using the combination of PostgreSQL and Coldfusion and
they cannot reproduce it. This makes me think that there may be a
problem with my database as created.

Shooting in the dark here, but what is the TRANSFORM_NULL_EQUALS
setting on your system and on that of the others?

A

-- 
----
Andrew Sullivan                         204-4141 Yonge Street
Liberty RMS                           Toronto, Ontario Canada
<andrew@libertyrms.info>                              M2P 2A8
                                         +1 416 646 3304 x110

FYI, "" does NOT equal NULL does not equal 0.

If an application wishes to store a null value, it should store a null
value, not a blank value.

Since blanks are not allowed in integers, the only legal value for CF to
store is either NULL or 0.

If you attempt to store '' or "" into an integer column, you should get an
error.

CF's lack of support for nulls is their issue, and one of the dozens of
reasons I switched development from that to PHP in my shop.

I would guess this might be a known and fixed bug in cold fusion. Have
you checked for updates on their site yet?

A database application language that doesn't understand NULLs is broken,
and needs to be fixed.

On Wed, 4 Jun 2003, Avi Schwartz wrote:

FYI, "" does NOT equal NULL does not equal 0.

Interesting, I noticed that a few days ago. I noticed that in pg sql, if I
used RTRIM on a column with all blanks. the RTRIM'ed result is not null. In
Oracle I think it would be a null. That is in Oracle "" and NULL are
equivalent.

Vincent Hikida,
Member of Technical Staff - Urbana Software, Inc.
"A Personalized Learning Experience"

www.UrbanaSoft.com

On Wednesday, Jun 4, 2003, at 13:48 America/Chicago, scott.marlowe
wrote:

FYI, "" does NOT equal NULL does not equal 0.

I know this very well, thank you.

If an application wishes to store a null value, it should store a null
value, not a blank value.

No disagreement here.

Since blanks are not allowed in integers, the only legal value for CF
to
store is either NULL or 0.

If you attempt to store '' or "" into an integer column, you should
get an
error.

I do not store a '' or "" into an integer column. This is how CF does
it when it gets a null value from the database.

CF's lack of support for nulls is their issue, and one of the dozens of
reasons I switched development from that to PHP in my shop.

Unfortunately this is not reasonable. This is an extremely big
application that will require a huge effort to move to a different
platform, a thought that we will entertain if we decide to rewrite the
application in the future.

I would guess this might be a known and fixed bug in cold fusion. Have
you checked for updates on their site yet?

Yes, I am running the latest SP. However, I am not sure yet where is
the problem since CF works as expected with other databases.

A database application language that doesn't understand NULLs is
broken,
and needs to be fixed.

Avi
--
Avi Schwartz
avi@CFFtechnologies.com

On Wed, 4 Jun 2003, Vincent Hikida wrote:

FYI, "" does NOT equal NULL does not equal 0.

Interesting, I noticed that a few days ago. I noticed that in pg sql, if I
used RTRIM on a column with all blanks. the RTRIM'ed result is not null. In
Oracle I think it would be a null. That is in Oracle "" and NULL are
equivalent.

Yeah, this is an issue we run into a lot on the lists. Basically, Oracle
got this one wrong, people wrote a lot of code expecting it, and now
Oracle can't change it to the proper behaviour without breaking folks'
applications.

There is a setting in postgresql that turns where field=NULL into where
field IS NULL, but I don't think there's much more support for "" being
equivalent to NULL.

Thanks for the detail. :)

Vincent Hikida,
Member of Technical Staff - Urbana Software, Inc.
"A Personalized Learning Experience"

www.UrbanaSoft.com

----- Original Message -----
From: "scott.marlowe" <scott.marlowe@ihs.com>
To: "Vincent Hikida" <vhikida@inreach.com>
Cc: "Avi Schwartz" <avi@CFFtechnologies.com>; <pgsql-general@postgresql.org>
Sent: Wednesday, June 04, 2003 1:28 PM
Subject: Re: [GENERAL] Nulls get converted to 0 problem

On Wed, 4 Jun 2003, Vincent Hikida wrote:

FYI, "" does NOT equal NULL does not equal 0.

Interesting, I noticed that a few days ago. I noticed that in pg sql, if

I

used RTRIM on a column with all blanks. the RTRIM'ed result is not null.

In

On Wed, 4 Jun 2003, Avi Schwartz wrote:

On Wednesday, Jun 4, 2003, at 13:48 America/Chicago, scott.marlowe
wrote:

FYI, "" does NOT equal NULL does not equal 0.

I know this very well, thank you.

Great, now if we could just teach Cold Fusion to know the difference your
problem would be solved.

If an application wishes to store a null value, it should store a null
value, not a blank value.

No disagreement here.

Since blanks are not allowed in integers, the only legal value for CF
to
store is either NULL or 0.

If you attempt to store '' or "" into an integer column, you should
get an
error.

I do not store a '' or "" into an integer column. This is how CF does
it when it gets a null value from the database.

So, CF is letting you store NULLS? Or is it coercing them to 0 before
storage?

CF's lack of support for nulls is their issue, and one of the dozens of
reasons I switched development from that to PHP in my shop.

Unfortunately this is not reasonable. This is an extremely big
application that will require a huge effort to move to a different
platform, a thought that we will entertain if we decide to rewrite the
application in the future.

I'm not suggesting moving now. I am saying that this is a bug that
needs to be fixed.

I'm gonna guess that the real problem is that cold fusion knows JUST
enough about postgresql to be dangerous, and it is storing 0 in those
fields when you tell it to store NULL. Since it stores it as 0, it
returns it as 0. If you were to run an update statement on that table to
set the values that are 0 to NULL, it would probably work as you want.

Again, this is a guess.

I would guess this might be a known and fixed bug in cold fusion. Have
you checked for updates on their site yet?

Yes, I am running the latest SP. However, I am not sure yet where is
the problem since CF works as expected with other databases.

I repeat...

A database application language that doesn't understand NULLs is
broken, and needs to be fixed.

Just because it works with other databases doesn't mean it's not broken,
just that the other databases will let you do something you shouldn't be
allowed to do.

On Wednesday, Jun 4, 2003, at 15:55 America/Chicago, scott.marlowe
wrote:

On Wed, 4 Jun 2003, Avi Schwartz wrote:

On Wednesday, Jun 4, 2003, at 13:48 America/Chicago, scott.marlowe
wrote:

I do not store a '' or "" into an integer column. This is how CF does
it when it gets a null value from the database.

So, CF is letting you store NULLS? Or is it coercing them to 0 before
storage?

No, the value in the database IS null. When I do the select, it seems
that CF turns the null integers into the value 0.

I'm gonna guess that the real problem is that cold fusion knows JUST
enough about postgresql to be dangerous, and it is storing 0 in those
fields when you tell it to store NULL. Since it stores it as 0, it
returns it as 0. If you were to run an update statement on that table
to
set the values that are 0 to NULL, it would probably work as you want.

Again, this is a guess.

I have a feeling this has something to do with the JDBC driver, again,
just a feeling.

I posted a question also on the Macromedia forums to see if I can
verify that the problem exists only on my installation and if it works
for them to find out what JDBC driver version they are using.

Yes, I am running the latest SP. However, I am not sure yet where is
the problem since CF works as expected with other databases.

I repeat...

A database application language that doesn't understand NULLs is
broken, and needs to be fixed.

Just because it works with other databases doesn't mean it's not
broken,
just that the other databases will let you do something you shouldn't
be
allowed to do.

But that does not help me or any other company that wants to use
PostgreSQL with CF and I am sure Micromedia will tell me that
PostgreSQL is broken since they have no problem with other databases.

Avi
--
Avi Schwartz
avi@CFFtechnologies.com

On Wed, 4 Jun 2003, Avi Schwartz wrote:

I have a feeling this has something to do with the JDBC driver, again,
just a feeling.

You may be on to something there. Have you tried setting up an ODBC
connection to see how that works?

I posted a question also on the Macromedia forums to see if I can
verify that the problem exists only on my installation and if it works
for them to find out what JDBC driver version they are using.

Yes, I am running the latest SP. However, I am not sure yet where is
the problem since CF works as expected with other databases.

I repeat...

A database application language that doesn't understand NULLs is
broken, and needs to be fixed.

Just because it works with other databases doesn't mean it's not
broken,
just that the other databases will let you do something you shouldn't
be
allowed to do.

But that does not help me or any other company that wants to use
PostgreSQL with CF and I am sure Micromedia will tell me that
PostgreSQL is broken since they have no problem with other databases.

That's not always true. Where I work we went round and round with the
folks at Crystal Reports. Their box said it worked with "LDAP V3
compliant" servers. It didn't work with OpenLDAP, and we figured it out
together, and they're now working on making it work with OpenLDAP.

Sure, they complained about "only supporting brand X" LDAP servers, but
then I pointed out that for their box to be correct it should say that,
not claim full LDAP V3 which they obviously didn't really have quite right
yet.

It couldn't be that hard to add a few is_null type checks to cold fusion,
and it certainly wouldn't hurt their market penetration to be able to
handle NULLs properly. It's usually harder to find someone to "pitch" to
in a closed source shop, but they're often very receptive of ideas once
they get in.

On Wednesday, Jun 4, 2003, at 16:53 America/Chicago, scott.marlowe
wrote:

On Wed, 4 Jun 2003, Avi Schwartz wrote:

I have a feeling this has something to do with the JDBC driver, again,
just a feeling.

You may be on to something there. Have you tried setting up an ODBC
connection to see how that works?

Unlike older versions, Coldfusion MX is Java based and does not support
ODBC connections anymore.

Just because it works with other databases doesn't mean it's not
broken,
just that the other databases will let you do something you shouldn't
be
allowed to do.

But that does not help me or any other company that wants to use
PostgreSQL with CF and I am sure Micromedia will tell me that
PostgreSQL is broken since they have no problem with other databases.

That's not always true. Where I work we went round and round with the
folks at Crystal Reports. Their box said it worked with "LDAP V3
compliant" servers. It didn't work with OpenLDAP, and we figured it
out
together, and they're now working on making it work with OpenLDAP.

I just posted a general request for MM to improve their support of
PostgreSQL. We'll see what happens.

Avi
--
Avi Schwartz
avi@CFFtechnologies.com

Oracle *incorrectly* interprets blank (empty) strings as NULL. They are NOT
the same. A string of zero characters is a string nonetheless. A NULL is
"the absence of value", which equals nothing (theoretically not even another
NULL).

Terry Fielder
Manager Software Development and Deployment
Great Gulf Homes / Ashton Woods Homes
terry@greatgulfhomes.com
Fax: (416) 441-9085

On Wed, Jun 04, 2003 at 14:39:05 -0500,
Avi Schwartz <avi@CFFtechnologies.com> wrote:

I do not store a '' or "" into an integer column. This is how CF does
it when it gets a null value from the database.

Can you use coalesce to work around this problem?

On Wed, 2003-06-04 at 15:55, scott.marlowe wrote:

On Wed, 4 Jun 2003, Avi Schwartz wrote:

On Wednesday, Jun 4, 2003, at 13:48 America/Chicago, scott.marlowe
wrote:

[snip]

I repeat...

A database application language that doesn't understand NULLs is
broken, and needs to be fixed.

Just because it works with other databases doesn't mean it's not broken,
just that the other databases will let you do something you shouldn't be
allowed to do.

I guess that's how de facto standards get created. <Sigh>

-- 
+-----------------------------------------------------------+
| Ron Johnson, Jr.     Home: ron.l.johnson@cox.net          |
| Jefferson, LA  USA   http://members.cox.net/ron.l.johnson |
|                                                           |
| Regarding war zones: "There's nothing sacrosanct about a  |
| hotel with a bunch of journalists in it."                 |
|     Marine Lt. Gen. Bernard E. Trainor (Retired)          |
+-----------------------------------------------------------+

On Wed, Jun 04, 2003 at 10:26:22AM -0500, Avi Schwartz wrote:

select * from item_catalog where item_category is null

With SQl Server 7, this works as expected, but with PostgreSQL, the
value Coldfusion is setting the integer variables to is 0 (zero) and
not "" as it should.

You probably can do

select coalesce(column1::text, ''), coalesce(column2::text, ''), ...
from item_catalog where item_category is null;

(Note that everything is going to come back as TEXT rather than numbers,
though)

--
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"Las cosas son buenas o malas segun las hace nuestra opinion" (Lisias)

On Wed, 4 Jun 2003 terry@ashtonwoodshomes.com wrote:

Oracle *incorrectly* interprets blank (empty) strings as NULL. They are NOT
the same. A string of zero characters is a string nonetheless. A NULL is
"the absence of value", which equals nothing (theoretically not even another
NULL).

If you're testing a value, you're testing to see if there's something in
there or not - what difference does it make if the variable contains 0, ""
or NULL?

Why not adhere to the practices inherent (and thus anticipated by
developers) in other languages (C comes to mind) where 0, NULL and "" are
equivalent?

Cheers!

--
Jon Earle

SAVE FARSCAPE http://www.savefarscape.com/

On Thu, 5 Jun 2003, Alvaro Herrera wrote:

On Wed, Jun 04, 2003 at 10:26:22AM -0500, Avi Schwartz wrote:

select * from item_catalog where item_category is null

With SQl Server 7, this works as expected, but with PostgreSQL, the
value Coldfusion is setting the integer variables to is 0 (zero) and
not "" as it should.

You probably can do

select coalesce(column1::text, ''), coalesce(column2::text, ''), ...
from item_catalog where item_category is null;

(Note that everything is going to come back as TEXT rather than numbers,
though)

Adding to this, if they DON'T want to have pgsql specific code in their
application, they could create views with update triggers to handle the
tables underneath.

Just wanted to let everyone know that I just downloaded the latest JDBC
driver and all my problems are solved now. It was a driver issue after
all.

Avi

On Wednesday, Jun 4, 2003, at 17:22 America/Chicago, Avi Schwartz wrote:

On Wednesday, Jun 4, 2003, at 16:53 America/Chicago, scott.marlowe
wrote:

On Wed, 4 Jun 2003, Avi Schwartz wrote:

I have a feeling this has something to do with the JDBC driver,
again,
just a feeling.

You may be on to something there. Have you tried setting up an ODBC
connection to see how that works?

Unlike older versions, Coldfusion MX is Java based and does not
support ODBC connections anymore.

--
Avi Schwartz
avi@CFFtechnologies.com

One good reason not interpret the empty string as NULL is because the empty
string could violate a foreign key constraint, whereas a NULL says "don't
evaluate the fkey constraint, there is no value here for this row"

Terry Fielder
Manager Software Development and Deployment
Great Gulf Homes / Ashton Woods Homes
terry@greatgulfhomes.com
Fax: (416) 441-9085

On Thu, 2003-06-05 at 09:39, Jon Earle wrote:

On Wed, 4 Jun 2003 terry@ashtonwoodshomes.com wrote:

Oracle *incorrectly* interprets blank (empty) strings as NULL. They are NOT
the same. A string of zero characters is a string nonetheless. A NULL is
"the absence of value", which equals nothing (theoretically not even another
NULL).

If you're testing a value, you're testing to see if there's something in
there or not - what difference does it make if the variable contains 0, ""
or NULL?

Why not adhere to the practices inherent (and thus anticipated by
developers) in other languages (C comes to mind) where 0, NULL and "" are
equivalent?

Perhaps because the SQL Spec says they are different?

For that matter, a zero length string in C is not the same as NULL.
Believing otherwise may be convenient, but leads to segfaults

i.e., this code will cause a segfault

main(...) {
char *str;

if (str == NULL)
printf ("This test is safe\n");

if (str == "")
printf ("This comparison above can segfault on some systems\n");

printf ("printing a NULL string like %s can also segfault\n", str);

}

I believe in C the following is true as well:

main() {
char *str="";

if (str)
printf ("An empty string evaluates as true");

}

--
Karl DeBisschop <kdebisschop@alert.infoplease.com>