[REVIEWED:3-4] crash on query with outer join
Lionel Elie Mamane
lionel at mamane.lu
Thu Feb 9 02:38:07 PST 2012
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
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
SELECT * FROM table WHERE boolColumn
do not give the same results. They are, respectively:
SELECT * FROM table WHERE boolColumn=1
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.
More information about the LibreOffice