Re: An amusing MySQL weakness--not!

Earlier today I was working on a MySQL database (not by choice, I assure
you),
and I typed a statement like this:

Update tablexyz set field1 = '15' where field2 - 20;

The '-' was supposed to be an equal sign, but MySQL executed it anyway.
(Field2 is an integer.)

I was not amused.

PostgreSQL reports this as an error, of course.
--
Mike Nolan
nolan(at)tssi(dot)com

>>>>> "Michael" == Michael Nolan <htfoot(at)gmail(dot)com> writes:

Michael> Earlier today I was working on a MySQL database (not by choice, I assure
Michael> you),

Friends don't let friends use MySQL.

--
Randal L. Schwartz - Stonehenge Consulting Services, Inc. - +1 503 777 0095
<merlyn(at)stonehenge(dot)com> <URL:http://www.stonehenge.com/merlyn/>
Smalltalk/Perl/Unix consulting, Technical writing, Comedy, etc. etc.
See http://methodsandmessages.posterous.com/ for Smalltalk discussion

On 26/06/11 16:44, Michael Nolan wrote:
> Earlier today I was working on a MySQL database (not by choice, I
> assure you),
> and I typed a statement like this:
>
> Update tablexyz set field1 = '15' where field2 - 20;
>
> The '-' was supposed to be an equal sign, but MySQL executed it
> anyway. (Field2 is an integer.)
>
> I was not amused.
>
> PostgreSQL reports this as an error, of course.
> --
> Mike Nolan
> nolan(at)tssi(dot)com <mailto:nolan(at)tssi(dot)com>

I am guessing that '(field2 - 20)' is evaluated, and if non-zero it is
treated as true?

Michael Nolan wrote:
> Earlier today I was working on a MySQL database (not by choice, I assure
> you),
> and I typed a statement like this:
>
> Update tablexyz set field1 = '15' where field2 - 20;
>
> The '-' was supposed to be an equal sign, but MySQL executed it anyway.
> (Field2 is an integer.)
>
> I was not amused.
>
> PostgreSQL reports this as an error, of course.

I assume MySQL accepts this because it, like SQLite, doesn't have a real BOOLEAN
type and instead uses INTEGER for its truth values, I believe.

The result of any "foo = bar" is an INTEGER anyway, I believe, in such DBMSs,
same as "field2 - 20", and so if its result is nonzero, that is treated as TRUE.

Sure, that's inane, but for this one I would place a lot of blame on the SQL
standard, because it doesn't mandate BOOLEAN support, just making it optional,
and so lots of SQL DBMSs don't require BOOLEAN as the type of a WHERE expression.

Having real BOOLEAN is just one of the reasons I like Postgres the most.

-- Darren Duncan

Le dimanche 26 juin 2011 à 00:05 -0700, Darren Duncan a écrit :
> Michael Nolan wrote:

> Having real BOOLEAN is just one of the reasons I like Postgres the most.
>

Would you mind giving an example of where a boolean field would be a win
over an integer one?

I'm asking this because I frequently wonder what is best for my use; I
normally query postgres via Perl modules, which don't care about boolean
(the driver converts t/f to 0/1), but I like to tune my fields properly.

--
Vincent Veyron
http://marica.fr/
Logiciel de gestion des sinistres et des contentieux pour le service juridique

hello

2011/6/26 Vincent Veyron <vv(dot)lists(at)wanadoo(dot)fr>:
> Le dimanche 26 juin 2011 à 00:05 -0700, Darren Duncan a écrit :
>> Michael Nolan wrote:
>
>> Having real BOOLEAN is just one of the reasons I like Postgres the most.
>>
>
> Would you mind giving an example of where a boolean field would be a win
> over an integer one?

everywhere, where you require readability. For me a FALSE is more
readable than 1 <> 0 or TRUE instead 1 = 1

Regards

Pavel Stehule

>
> I'm asking this because I frequently wonder what is best for my use; I
> normally query postgres via Perl modules, which don't care about boolean
> (the driver converts t/f to 0/1), but I like to tune my fields properly.
>

sure, it depends on fact if outer environment knows or doesn't know a
boolean datatype.

>
> --
> Vincent Veyron
> http://marica.fr/
> Logiciel de gestion des sinistres et des contentieux pour le service juridique
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

* Vincent Veyron (vv(dot)lists(at)wanadoo(dot)fr) wrote:
> Would you mind giving an example of where a boolean field would be a win
> over an integer one?

Where you only ever want 2 (or perhaps 2+NULL) values allowed for the
column. It's about domain, consistency, etc, primairly. That said,
don't we implement boolean fields using a bitmap similar to NULLs? In
which case, it would likely be smaller on disk and more performant as
well.

> I'm asking this because I frequently wonder what is best for my use; I
> normally query postgres via Perl modules, which don't care about boolean
> (the driver converts t/f to 0/1), but I like to tune my fields properly.

Yes, which is pretty horrible of it, imo.

Thanks,

Stephen

Le dimanche 26 juin 2011 à 12:05 -0400, Stephen Frost a écrit :
> > (the driver converts t/f to 0/1), but I like to tune my fields properly.
>
> Yes, which is pretty horrible of it, imo.
>

There is an option to turn it off and get the characters t/f returned
(pg_bool_tf)

--
Vincent Veyron
http://marica.fr/
Logiciel de gestion des sinistres et des contentieux pour le service juridique

Le dimanche 26 juin 2011 à 16:41 +0200, Pavel Stehule a écrit :

> everywhere, where you require readability. For me a FALSE is more
> readable than 1 <> 0 or TRUE instead 1 = 1
>

Actually, in Perl it's just 0 for false and 1 for true, so it's very
readable if you're used to it.

> >
> > I'm asking this because I frequently wonder what is best for my use; I
> > normally query postgres via Perl modules, which don't care about boolean
> > (the driver converts t/f to 0/1), but I like to tune my fields properly.
> >
>
> sure, it depends on fact if outer environment knows or doesn't know a
> boolean datatype.
>

In that case, I was really wondering from a data modeling standpoint.
Stephen's point seems good (only two values + null).

--
Vincent Veyron
http://marica.fr/
Logiciel de gestion des sinistres et des contentieux pour le service juridique

Στις Sunday 26 June 2011 17:11:06 ο/η Vincent Veyron έγραψε:
> Le dimanche 26 juin 2011 00:05 -0700, Darren Duncan a crit :
> > Michael Nolan wrote:
>
> > Having real BOOLEAN is just one of the reasons I like Postgres the most.
> >
>
> Would you mind giving an example of where a boolean field would be a win
> over an integer one?
>
> I'm asking this because I frequently wonder what is best for my use; I
> normally query postgres via Perl modules, which don't care about boolean
> (the driver converts t/f to 0/1), but I like to tune my fields properly.

Alright, but in other enterprise-level platforms, like java, there is a boolean data type and a Boolean class wrapper.

>
>
> --
> Vincent Veyron
> http://marica.fr/
> Logiciel de gestion des sinistres et des contentieux pour le service juridique
>
>

--
Achilleas Mantzios

On Sunday 26. June 2011 16.11.06 Vincent Veyron wrote:
> Le dimanche 26 juin 2011 à 00:05 -0700, Darren Duncan a écrit :
> > Michael Nolan wrote:
> >
> > Having real BOOLEAN is just one of the reasons I like Postgres the most.
>
> Would you mind giving an example of where a boolean field would be a win
> over an integer one?
>
> I'm asking this because I frequently wonder what is best for my use; I
> normally query postgres via Perl modules, which don't care about boolean
> (the driver converts t/f to 0/1), but I like to tune my fields properly.

PHP has its own Boolean values TRUE/FALSE, but reads Postgresql Booleans as
't'/'f'. You always have to rely on kludgy konstructs like

if ($pg_bool == 't') then
$my_bool = TRUE;
elseif ($pg_bool == 'f') then
$my_bool = FALSE;
else
$my_bool = NULL;

It's of course much easier to use integer values, but it sucks not to use a
bool when you want a variable that can't be anything but TRUE, FALSE or NULL.

It obfuscates the code to use a var that evaluates to an integer, but in
reality is a Boolean in disguise.

regards, Leif

Le lundi 27 juin 2011 à 12:11 +0200, Leif Biberg Kristensen a écrit :
> PHP has its own Boolean values TRUE/FALSE, but reads Postgresql
> Booleans as
> 't'/'f'. You always have to rely on kludgy konstructs like
>
> if ($pg_bool == 't') then
> $my_bool = TRUE;
> elseif ($pg_bool == 'f') then
> $my_bool = FALSE;
> else
> $my_bool = NULL;
>
> It's of course much easier to use integer values, but it sucks not to
> use a
> bool when you want a variable that can't be anything but TRUE, FALSE
> or NULL.
>

Same thing with Perl.

But I am starting to think that the benefits of data integrity
mentionned by Stephen are worth the extra effort : you save on
validation needs anyway, which is probably another couple of lines, if
not more, and less reliable.

--
Vincent Veyron
http://marica.fr/
Logiciel de gestion des sinistres et des contentieux pour le service juridique