Hi, Don't you think you should use
select * from test WHERE a=0 ; not select * from test where 0; Isn't the correct usage of WHERE >-----Original Message----- >From: Svensson, B.A.T. (HKG) [mailto:[EMAIL PROTECTED]] >Sent: Tuesday, April 16, 2002 2:00 PM >To: Mysql List; Christopher Thompson >Subject: RE: WHERE codition test > > >> mysql> select x.a from test x where 1; >> >> 1 evaluates to be true, so this should select all rows. And it does. >> >> 5 rows in set (0.00 sec) >> >> mysql> select * from test where 0; >> >> This evaluates to false, so this should not select any rows. And it does. >> >> Empty set (0.00 sec) >> >> mysql> select x.a from test x where x.a; >> >> This should select all rows where the a column's value evaluates to true. > >> And it does. >> >> 3 rows in set (0.00 sec) > >The tree cases above is a typical C/C++ behavior, so nothing is wired here. >But the next one does not. (Rather it behave like a typical C/C++ fault in >handling/checking integrity/consistency of pointers somewhere.) > >> mysql> select x.a from test x where 'a'; >> Empty set (0.00 sec) >> >> I'm not quite sure why this doesn't return anything, to tell the truth. > >Neither I, it seams strange. This value has to be stored in some kind >of pointer to an object or char * in the program it self. I would first >assume that the content of the pointer is tested, but that cant be true >since the content in this case is the number 65. > >So then you would suspect that they test a pointer instead. But that >does not make sence either. Thre pointer should exists, right? > >But maybe there is something really strange going on with the >interpretation of the SQL query insteed? > >I kind of recognize this situation with the BASIC interpreted built >in the system I did work with before. One could build really funny >(working) BASIC statement due to that the interpreter went confused >with the sysntax sometimes. > >I don't know how MySQL tokenizes the SQL query, but if it breaks up >the token in a certain way, then it is possible that the interpreter >finds the string 'a', and then assumes it MUST have an operator, but >the operator might then point to NULL, an dthen nobody know what >happens after that, except that the end results evaluates to zero. > >Hmmm... this is indeed interesting to know what really happens, maybe >I should download the source code and stepdebug just top see what actually >happens. But that has to be a later project... > > >>That >> said, I cannot understand why you'd ever set up a query like these. > >I did actually try to provoke a 'syntax error' message will all test >cases, but they failed. However I learnt something important from this: > >If an applications programmer does something like this: > >SELECT <whatever> >FROm <a bunch of tables> >WHERE <lots of primary key lookup between tables> > <and a bunch of nested SELECT statements> > AND Attribute >/* and the app programmer took a coffee break here, and >** when returned forgot to add "= <some hardcoded value>" >*/ > <lots of other junk> > >Since the query compiler does not generates a syntax error here, this might >be a source of introducing faults in the system. That's why it is important >to know exactly how the system behaves. No I know this, but is there other >surprises that I would like to know about? > >> On Tuesday 16 April 2002 11:21 am, Svensson, B.A.T. (HKG) wrote: >> > Basically I am just curious, and I did perform some few small test. >> > But, I was suspecting to get all rows when I did something like >> > "WHERE <valid string>", but had to write something like "WHERE NOT >> > <valid string>" which feels a little bit upside down for me considering >> > that integers apart from zero evaluates to true. >> > >> > Also it seams like this is not ISO SQL, so it could be a bit tricky >> > to predict the outcome without actually having some specification of >> > the behavior. >> > >> > //Anders >> > >> > > -----Original Message----- >> > > From: Christopher Thompson >> > > Sent: Tuesday, April 16, 2002 6:24 PM >> > > To: Svensson, B.A.T. (HKG); Mysql List >> > > Subject: Re: WHERE codition test >> > > >> > > >> > > I'm not sure what you are asking. Everything is working correctly as >far >> > > as I can see. >> > See Dave Matthews Band live or win a signed guitar http://r.lycos.com/r/bmgfly_mail_dmb/http://win.ipromotions.com/lycos_020201/splash.asp --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php