1

Today I learned that SELECT f1,EXISTS(SELECT ...),f2 is valid in MySQL and, reading from StackOverflow, apparently in PostgreSQL as well and the EXISTS part is 0 when the subquery result is empty and 1 when the result is not. Then I began to think and tested a few queries looking like SELECT * WHERE column1 - column2 and even this worked and it's the same as column1 <> column2. And I even tried WHERE (column1 > 0) - column2 and even this worked. Is this SQL standard or just MySQL is trigger happy about casting booleans and integers with wild abandon? Or perhaps booleans do not even exist in SQL and 0 / 1 represents them?

2

Or perhaps booleans do not even exist in SQL and 0 / 1 represents them

boolean do exists in the SQL standard but as "real" booleans.

So valid boolean literals are false or true.

Is this SQL standard or just MySQL is trigger happy about casting booleans and integers with wild abandon

MySQL does not actually support boolean values. It simply treats anything that can't be converted to a non-zero number as "false".

E.g.

select *
from some_table
where 'xxxx';

is accepted as valid (even though it is invalid SQL) and is equivalent to where false because xxxx can't be converted to a number and thus is assumed to be 0 which is treated as false.

On the other hand

select *
from some_table
where '42xxxx';

is treated as where true because 42xxx will be (implicitly) converted to 42 which in turn is treated as "true".


The query:

SELECT * 
WHERE column1 - column2

is invalid standard SQL because it yields a number (assuming column1 and column2 are numbers) and a number is not a boolean expression.


You can see this behavior when doing:

select 1 > 0;

MySQL returns the number 1 whereas a database supporting real booleans (e.g. Postgres) will return true. Oracle does not support real booleans so select 1 > 0 from dual is actually invalid there.

  • 42xxx will be (implicitly) converted to 42 -- gosh, here I thought PHP is alone in doing that! So then what about PostgreSQL supporting SELECT EXISTS() being 0 or 1? – chx Nov 4 '16 at 9:09
  • 1
    select exists (select * from t); gives TRUE or FALSE in Postgres, not 0/1. – ypercubeᵀᴹ Nov 4 '16 at 9:16
  • @chx: as Postgres supports real booleans, exists() also returns a proper boolean value. – a_horse_with_no_name Nov 4 '16 at 9:17
  • What a_horse says. You'll have to do an explicit conversion in Postgres to convert that TRUE/FALSE to 1/0: select (exists (select * from t))::int ; MySQL does that "automatically" because BOOLEAN in MySQL is just a synonym of TINYINT, it doesn't have a proper boolean type (but in works and looks like a boolean in most cases). – ypercubeᵀᴹ Nov 4 '16 at 9:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.