Re: Nulls get converted to 0 problem - Mailing list pgsql-general

Hi Jon!

>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?

sorry, I cannot resist: well, it makes a huge difference.
Apples and pears are uncomparable, so are numbers and strings.
In strongly typed languages, these values are of different
types and therefore not comparable by simple means. And IIRC also
the SQL standards mentions these to be handled differently. IMO,
only because some vendor once upon a time decided to break with
the standard, other developers are not obliged to follow them
like lemmings. Existing code written in the manner of sytem X
might require some work if you port to system Y, when X and Y
do not agree on standard compliance and the coders were not
aware of that fact.

>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?

Even in C an empty string is not equivalent to 0 or
NULL, AFAIR. And 0 and NULL being equivalent is more of a
convention, than a definition (at least nowadays). Any good
compiler should give you a warning on assigning with these mixed
types, unless you switch those warnings off.

And to use a counterexample, think of Java.

Bad practices tend to result in bad code.
<sarcasm>if your code was well written, you should have no trouble
to find and convert all the places where these assumptions about
equality was made</sarcasm>. I don't know if such lazily comparing
systems claim to be standard compliant. If you want to be, and I
understood postgres wants to, you have to do it the standards way.

Btw, I like postgres' style of return false on a comparison
of two NULLs. If something is undefined, how could I compare it
to anything else. I can't even figure out its features, so I should
not be able to match it successfully to anything else.

If you really need/want to have this behaviour, I suggest
writing special comparison function(s), which handles the input
as you like it. This way you only have to port the function
between two database systems and you are all done. I would still
recommend to fix the calling code. Try to imagine yourself
revisiting a non-trivial piece of code in half a year...

Sidenote: In principle I dislike all such types of implicit
conversions done by computer systems. IMO, as a programmer you
get used to imprecise thinking, when computer systems handle
your wrong input silently. In the long run, mental concepts
of the language elements will become all blurry, which in turn
leads to programming errors.

Ciao
  Jan
P.S.: as you certainly figured out already, i have a background
in programming languages and compilers :-)