Re: indentifier case
Jeff Zucker wrote: Tim Bunce wrote: On Mon, Mar 04, 2002 at 08:50:45AM -0800, Jeff Zucker wrote: Hmm, I just checked the SQL92 standard and if I'm reading it correctly the situation is ugly. Section 5.2.13 appears to say that a regular identifier and a delimited identifier are equivalent if the *upper case* version of the regular identifier compares *in a case sensitive manor* to the delimited identifier. Got a URL? It's from the PDF of SQL92 I purchased from www.ansi.org. But here's the text: 13) A regular identifier and a delimited identifier are equiva- lent if the identifier body of the regular identifier (with every letter that is a lower-case letter replaced by the equiva- lent upper-case letter or letters) and the delimited identifier body of the delimited identifier (with all occurrences of quote replaced by quote symbol and all occurrences of dou- blequote symbol replaced by double quote), considered as the repetition of a character string literal that specifies a character set specification of SQL_TEXT and an implementation- defined collation that is sensitive to case, compare equally according to the comparison rules in Subclause 8.2, comparison predicate. And SQL3 - Part 11: Information and Definition Schemas (SQL/Schemata) http://www.jtc1sc32.org/.../32N0748T.pdf has something to say about the representation of an identifier: The representation of an identifier in the base tables and views of the Information Schema is by a character string corresponding to its identifier body (in the case of a regular identifier) or its delimited identifier body (in the case of a delimited identifier). Within this character string, any lower-case letter appearing in a regular identifier is replaced by the equivalent upper-case letter, and any doublequote symbol appearing in a delimited identifier body is replaced by a double quote. Steffen
Re: indentifier case
Yeap, seems reasonable. Tim. On Wed, Mar 06, 2002 at 04:49:58PM -0800, Jeff Zucker wrote: Tim Bunce wrote: On Wed, Mar 06, 2002 at 03:04:51PM -0800, Jeff Zucker wrote: It's from the PDF of SQL92 I purchased from www.ansi.org. But here's the text: 13) A regular identifier and a delimited identifier are equiva- lent if the identifier body of the regular identifier (with every letter that is a lower-case letter replaced by the equiva- lent upper-case letter or letters) and the delimited identifier body of the delimited identifier (with all occurrences of quote replaced by quote symbol and all occurrences of dou- blequote symbol replaced by double quote), considered as the repetition of a character string literal that specifies a character set specification of SQL_TEXT and an implementation- defined collation that is sensitive to case, compare equally according to the comparison rules in Subclause 8.2, comparison predicate. Perhaps, but the paragraph above seems too daft to be worth living with :) Aha, you know what, I think I just figured out why it isn't daft and why your suggestion to use mixed case matching (BooB = BooB) can't work: Rule A - Regular identifiers are case INSENSITIVE Rule B - Delimited identifiers are case SENSITIVE Therefore, these are the same as each other: x1 boob x2 BooB x3 BOOB And these are different from each other: y1 boob y2 BooB y3 BOOB If we allow group x to be equivalent to group y on the basis of mixed case, then we'd end up either with the three identifiers in group x different from each other (a violation of rule A) or the 3 identifiers in group y as the same as each other (a violation of rule B). So we can never use mixed case to determiine the equivalence. So we have to arbitrarily say that x1,x2,x3 as a group are all equal to one and only one of group y, either the lower case or the upper case. The standards says that would be upper case to avoid confusion. So, not so daft after all? -- Jeff
Re: indentifier case
On Wed, Mar 06, 2002 at 03:04:51PM -0800, Jeff Zucker wrote: Tim Bunce wrote: On Mon, Mar 04, 2002 at 08:50:45AM -0800, Jeff Zucker wrote: Hmm, I just checked the SQL92 standard and if I'm reading it correctly the situation is ugly. Section 5.2.13 appears to say that a regular identifier and a delimited identifier are equivalent if the *upper case* version of the regular identifier compares *in a case sensitive manor* to the delimited identifier. Got a URL? It's from the PDF of SQL92 I purchased from www.ansi.org. But here's the text: 13) A regular identifier and a delimited identifier are equiva- lent if the identifier body of the regular identifier (with every letter that is a lower-case letter replaced by the equiva- lent upper-case letter or letters) and the delimited identifier body of the delimited identifier (with all occurrences of quote replaced by quote symbol and all occurrences of dou- blequote symbol replaced by double quote), considered as the repetition of a character string literal that specifies a character set specification of SQL_TEXT and an implementation- defined collation that is sensitive to case, compare equally according to the comparison rules in Subclause 8.2, comparison predicate. As I understand that, it means regular identifiers match delimited identifiers only if the delimited identifier is all upper case and the regular identifier, when uppercase is equal to it. This would be regardless of the value of SQL_IDENTIFIER_CASE. Why not go the SQL_IDENTIFIER_CASE = SQL_IC_MIXED route and say that BooG == BooG. That's fairly natural and obvious and I think it would cause least problems in the long run. And wouldn't that also suit DBD::CSV users? That makes sense but I think the paragraph above means something like this: boog boog BOOG boog boog == BOOG BOOG == BOOG Perhaps, but the paragraph above seems too daft to be worth living with :) Tim.
Re: indentifier case
Tim Bunce wrote: On Wed, Mar 06, 2002 at 03:04:51PM -0800, Jeff Zucker wrote: It's from the PDF of SQL92 I purchased from www.ansi.org. But here's the text: 13) A regular identifier and a delimited identifier are equiva- lent if the identifier body of the regular identifier (with every letter that is a lower-case letter replaced by the equiva- lent upper-case letter or letters) and the delimited identifier body of the delimited identifier (with all occurrences of quote replaced by quote symbol and all occurrences of dou- blequote symbol replaced by double quote), considered as the repetition of a character string literal that specifies a character set specification of SQL_TEXT and an implementation- defined collation that is sensitive to case, compare equally according to the comparison rules in Subclause 8.2, comparison predicate. Perhaps, but the paragraph above seems too daft to be worth living with :) Aha, you know what, I think I just figured out why it isn't daft and why your suggestion to use mixed case matching (BooB = BooB) can't work: Rule A - Regular identifiers are case INSENSITIVE Rule B - Delimited identifiers are case SENSITIVE Therefore, these are the same as each other: x1 boob x2 BooB x3 BOOB And these are different from each other: y1 boob y2 BooB y3 BOOB If we allow group x to be equivalent to group y on the basis of mixed case, then we'd end up either with the three identifiers in group x different from each other (a violation of rule A) or the 3 identifiers in group y as the same as each other (a violation of rule B). So we can never use mixed case to determiine the equivalence. So we have to arbitrarily say that x1,x2,x3 as a group are all equal to one and only one of group y, either the lower case or the upper case. The standards says that would be upper case to avoid confusion. So, not so daft after all? -- Jeff
Re: indentifier case
On Mon, Mar 04, 2002 at 08:50:45AM -0800, Jeff Zucker wrote: Tim Bunce wrote: On Sun, Mar 03, 2002 at 12:41:54PM -0800, Jeff Zucker wrote: $dbh-do(q/ CREATE TABLE x ( OOg INT, BOOg CHAR ) /); $sth=$dbh-prepare(q/ SELECT boog FROM x /); #4 NO SUCH COLUMN $sth=$dbh-prepare(q/ SELECT BOOg FROM x /); #5 NO SUCH COLUMN I think maybe #5 would work for a system where SQL_IDENTIFIER_CASE = SQL_IC_MIXED I'm not sure And you could argue that #4 would work as well Hmm, I just checked the SQL92 standard and if I'm reading it correctly the situation is ugly Section 5213 appears to say that a regular identifier and a delimited identifier are equivalent if the *upper case* version of the regular identifier compares *in a case sensitive manor* to the delimited identifier Got a URL? So this would be the way it should work according to the standard $dbh-do(q/ CREATE TABLE x ( BOOG CHAR ) /); $sth=$dbh-prepare(q/ SELECT BooG FROM x /); # OK $sth=$dbh-prepare(q/ SELECT boog FROM x /); # OK $dbh-do(q/ CREATE TABLE x ( BooG CHAR ) /); $sth=$dbh-prepare(q/ SELECT BOOG FROM x /); # NO SUCH COLUMN $sth=$dbh-prepare(q/ SELECT BooG FROM x /); # NO SUCH COLUMN $sth=$dbh-prepare(q/ SELECT boog FROM x /); # NO SUCH COLUMN That's a little too wierd for me That matches Oracle, ie SQL_IDENTIFIER_CASE = SQL_IC_UPPER It would mean that only delimited identifiers that are all upper case can be compared to regular identifiers Unless it seems a gross violation, I think I will keep the behaviour that regular identifiers are never equivalent to delimited identifiers -- if it's quoted then you must use quotes to refer to it That's a little too wierd for me :) Why not go the SQL_IDENTIFIER_CASE = SQL_IC_MIXED route and say that BooG == BooG That's fairly natural and obvious and I think it would cause least problems in the long run And wouldn't that also suit DBD::CSV users? Tim
