I seem to remember many, many moons ago when studying SQL for the first time, learning that SQL syntax treats everything as character data no matter what the DB column data type is.
Also, I think this was to make the SQL syntax more "natural English language" like. I believe the original intent of SQL syntax was to be simple enough even management could understand it. Naturally that didn't work :-) Fred > -----Original Message----- > From: Darren Duncan [mailto:[EMAIL PROTECTED] > Sent: Thursday, May 13, 2004 3:39 AM > To: [EMAIL PROTECTED] > Subject: Re: [sqlite] SQLite version 3 design question: '500'=500? > > > At 8:19 PM -0400 5/12/04, D. Richard Hipp wrote: > >The development team is making progress on SQLite version 3.0. > >But we've run across an interesting puzzle. What should be > >returned by this: > > > > SELECT '500'=500; > > > >Is the result "0" or "1"? In other words, what happens when > >you compare a number to a string that looks like that number. > >Are they equal or not? > > In my experience, strings are implicitely cast as numbers when used > in a numerical context, which includes comparisons with either > literal numbers or numerical columns. So I vote for the above two > items having the result "TRUE". > > Take this for example, pretending that "bar" is a numerical column > that does not have a uniqueness constraint. > > INSERT INTO foo (bar) VALUES (5); > INSERT INTO foo (bar) VALUES ('5'); > > SELECT bar FROM foo WHERE bar = 5; > SELECT bar FROM foo WHERE bar = '5'; > > With most database engines that I'm aware of, both of the insert > statements will work whether the column is a string or a number, and > both select statements will return 2 rows (assuming table empty > before we started). That just seems normal. > > Also, such behaviour will benefit lazy programmers that generate SQL > from code because they can just put '' around all literal values > regardless of the data type, and it will just work. > > As for what the SQL standard says ... > > For reference: > SQL-2003 Foundation, 6.12 "<cast specification>", p201 > SQL-2003 Foundation, 8.2 "<comparison predicate>", p375 > ... --------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
