[REVIEWED:3-4] crash on query with outer join

[Lionel Elie Mamane]

On Thu, Feb 09, 2012 at 09:34:45AM +0000, Michael Meeks wrote:

> 	Out of interest, do databasen use 'not zero' for their true state (as
> C/C++ etc.) ? or always exactly 1 ?

In the SQL standard, the only (explicit, with a cast)s data conversion
specified for booleans are to/from the strings 'TRUE' and 'FALSE' and
'UNKNOWN' (a synonym for NULL in boolean context), which correspond to
the literals.

Anything else is database-specific and can be an error.

MySQL does not really have a boolean datatype; it is a synonym for
"tinyint" (8 bit integer). In tests (when a "truth value" is
required), anything not-zero is true (except NULL), but TRUE is the
constant one. So
 SELECT * FROM table WHERE boolColumn=TRUE
and
 SELECT * FROM table WHERE boolColumn
do not give the same results. They are, respectively:
 SELECT * FROM table WHERE boolColumn=1
and
 SELECT * FROM table WHERE boolColumn<>0

"boolColumn" can be any integer column.


PostgreSQL has a boolean datatype, and there is no automatic
conversion between integers and booleans:

=> SELECT 0=FALSE;
ERROR:  operator does not exist: integer = boolean

=> SELECT * FROM table WHERE integerColumn;
ERROR:  argument of WHERE must be type boolean, not type integer


But they can be explicitly casted and then TRUE=>1, and
non-zero=>TRUE. Interestingly, you can cast a boolean to a 32-bit
integer, but not to a 16-bit or 64-bit integer... And vice-versa: you
can cast a 32-bit integer to a boolean, but not a 16 or 64-bit integer
to a boolean.

A truth value (result of test) is basically a boolean.

-- 
Lionel