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>", p3758.2 Syntax Rule #3 says:
The declared types of the corresponding fields of the two <row value predicand>s shall be comparable.
6.12 Syntax Rule #6 says that a comparison between numeric data (EN,AN) and character data (C,VC,FC,CL) is syntactically valid without restriction (Y).
There are probably more references to this matter, which may be important, but it looks to me that the standard says you can compare numbers and strings which look like them.
At 11:44 PM -0700 5/12/04, Kevin Van Vechten wrote:
Another interesting data point, note the difference between ' and ". Welcome to psql 7.4, the PostgreSQL interactive terminal. kevin=# select '500'=500; ?column? ---------- t (1 row) kevin=# select "500"=500; ERROR: column "500" does not exist
Were you surprised?
Both the SQL standard and most SQL implementations that I know of always treat '' as literal strings and "" as optional identifier delimiters (their presence makes the identifier name case-sensitive, whereas a bareword identifier is case-insensitive, and delimited identifiers can also contain nearly any character, including whitespace) such as column or table names.
For reference:
SQL-2003 Foundation, 5.3 "<literal>", p143
SQL-2003 Foundation, 5.4 "Names and identifiers", p151
SQL-2003 Foundation, 6.6 "<identifier chain>", p183Mind you, MySQL is a bit different in that it uses `` as the identifier delimiter, unless that's just an option in addition to "". Technically, I think, the SQL standard says you can use almost anything as the delimiter, though "" is still the convention.
So '' only should be a string literal. See also B'' for quoting bit strings as ones and zeros, and X'' for quoting binary strings as hexits, says the standard.
In case you want to do this research yourself, copies of the entire almost-final (indistinguishable delta) SQL-2003 spec are here in PDF form, from which I quote (and treat as gospel):
http://www.wiscorp.com/SQLStandards.html
-- Darren Duncan
--------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
