Thread: 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@tssi.com
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@tssi.com
>>>>> "Michael" == Michael Nolan <htfoot@gmail.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@stonehenge.com> <URL:http://www.stonehenge.com/merlyn/> Smalltalk/Perl/Unix consulting, Technical writing, Comedy, etc. etc. See http://methodsandmessages.posterous.com/ for Smalltalk discussion
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.lists@wanadoo.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@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general >
* Vincent Veyron (vv.lists@wanadoo.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
Attachment
This message has been digitally signed by the sender.
Attachment
On 26/06/11 16:44, Michael Nolan wrote:
I am guessing that '(field2 - 20)' is evaluated, and if non-zero it is treated as true?
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@tssi.com
I am guessing that '(field2 - 20)' is evaluated, and if non-zero it is treated as true?
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