Given this part of that same rule applied to the strings: "b) If the length in characters of X is not equal to the length in characters of Y, then the shorter string is effectively replaced, for the purposes of comparison, with a copy of itself that has been extended to the length of the longer string by concatenation on the right of one or more pad characters, where the pad character is chosen based on CS. If CS has the NO PAD characteristic, then the pad character is an implementation-dependent character different from any character in the character set of X and Y that collates less than any string under CS. Otherwise, the pad character is a <space>." I do not see how two strings which are otherwise equal (except for length or blank padding) can possibly compare unequal unless the NO PAD property is applied and the implementation defined pad character is also something other than a <space>. Is that the case for PostgreSQL? Even if it is, is seems truly bizarre that the NO PAD attribute would be applied to string constants. > -----Original Message----- > From: Marc G. Fournier [mailto:scrappy@xxxxxxxxxxxxxx] > Sent: Wednesday, October 19, 2005 12:53 PM > To: Dann Corbit > Cc: Stephan Szabo; Marc G. Fournier; Richard_D_Levine@xxxxxxxxxxxx; pgsql- > hackers@xxxxxxxxxxxxxx; pgsql-general@xxxxxxxxxxxxxx > Subject: RE: [pgsql-advocacy] Oracle buys Innobase > > On Wed, 19 Oct 2005, Dann Corbit wrote: > > >> -----Original Message----- > >> From: Stephan Szabo [mailto:sszabo@xxxxxxxxxxxxxxxxxxxxx] > >> Sent: Wednesday, October 19, 2005 12:39 PM > >> To: Dann Corbit > >> Cc: Marc G. Fournier; Richard_D_Levine@xxxxxxxxxxxx; pgsql- > >> general@xxxxxxxxxxxxxx > >> Subject: Re: [pgsql-advocacy] Oracle buys Innobase > >> > >> On Wed, 19 Oct 2005, Dann Corbit wrote: > >> > >>> Yes, clearly that is the wrong result according to the SQL standard. > >>> > >>> Here is a SQL*Server query: > >>> select 1 where 'a' = 'a ' AND 'a' = 'a ' AND 'a ' = 'a ' > >>> > >>> It returns (correctly): 1 > >> > >> Doesn't that depend on the collating sequence in use, or is a NO PAD > >> collating sequence not allowed here? > > > > If the implementation defines constants as NO PAD and the implementation > > defined pad character is something other than space, then they could > > compare unequal. > > > > I would find that implementation disturbing. But I am easily bent out > > of shape. > > > > The attached HTML file in my earlier post is the official quote from the > > SQL 99 standard. That is the formal and correct definition, far > > superior to my off the cuff approximations. > > 'k, if I'm reading the right section (you say its bolded, but I'm using > pine which doesn't seem to do a good job of reading HTML): > > =========== > d) Depending on the collating sequence, two strings may compare as > equal even if they are of different lengths or contain different > sequences of characters. When any of the operations MAX, MIN, and > DISTINCT reference a grouping column, and the UNION, EXCEPT, and > INTERSECT operators refer to character strings, the specific value > selected by these operations from a set of such equal values is > implementation-dependent. > =========== > > I think the key part of that 'clause' is "two strings *may* compare as > equal" ... sounds implementation dependent to me, depending on how the > implementor interprets it ... or am I reading the wrong section? > > ---- > Marc G. Fournier Hub.Org Networking Services > (http://www.hub.org) > Email: scrappy@xxxxxxx Yahoo!: yscrappy ICQ: > 7615664 ---------------------------(end of broadcast)--------------------------- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
