Обсуждение: Re: 'a' == 'a ' (Was: RE: [pgsql-advocacy] [GENERAL] Oracle buysInnobase)
> -----Original Message----- > From: Terry Fielder [mailto:terry@ashtonwoodshomes.com] > Sent: Wednesday, October 19, 2005 2:05 PM > To: Dann Corbit > Cc: Tino Wildenhain; Marc G. Fournier; Richard_D_Levine@raytheon.com; > pgsql-hackers@postgresql.org; pgsql-general@postgresql.org > Subject: Re: 'a' == 'a ' (Was: RE: [pgsql-advocacy] [GENERAL] Oracle > buysInnobase) > > > > Dann Corbit wrote: > > Try this query in Oracle, SQL*Server, DB/2, Informix, etc.: > > > > connxdatasync=# select 1 where cast('a' as varchar(30)) = cast('a ' as > > varchar(30)); > > ?column? > > ---------- > > (0 rows) > > > > I see how you can interpret the SQL Standard to make the above response > > a correct one. But is it the response that you would like? > > When the compared datatypes are VARCHAR: YES What is the value of doing that? I can see plenty of harm and absolutely no return. We are talking about blank padding before comparison. Do you really want 'Danniel ' considered distinct from 'Danniel ' in a comparison? In real life, what does that buy you? And even if people think it is a good idea, it seems to be at odds with the way that I read the standard (though I am easily confused by simple enough language at times). It could well be that I am jaded from years of doing it the wrong way (I expect two character strings with all leading non-blanks in agreement to compare equal). Perhaps this is old hat to the long-timers around here and there is a good explanation as to why varchar should have non-blank padding when comparisons are performed. Can someone point me to documentation that explains it? [snip]
On Wed, 19 Oct 2005, Dann Corbit wrote: > > -----Original Message----- > > From: Terry Fielder [mailto:terry@ashtonwoodshomes.com] > > Sent: Wednesday, October 19, 2005 2:05 PM > > To: Dann Corbit > > Cc: Tino Wildenhain; Marc G. Fournier; Richard_D_Levine@raytheon.com; > > pgsql-hackers@postgresql.org; pgsql-general@postgresql.org > > Subject: Re: 'a' == 'a ' (Was: RE: [pgsql-advocacy] [GENERAL] Oracle > > buysInnobase) > > > > > > > > Dann Corbit wrote: > > > Try this query in Oracle, SQL*Server, DB/2, Informix, etc.: > > > > > > connxdatasync=# select 1 where cast('a' as varchar(30)) = cast('a ' > as > > > varchar(30)); > > > ?column? > > > ---------- > > > (0 rows) > > > > > > I see how you can interpret the SQL Standard to make the above > response > > > a correct one. But is it the response that you would like? > > > > When the compared datatypes are VARCHAR: YES > > What is the value of doing that? > > I can see plenty of harm and absolutely no return. We are talking about > blank padding before comparison. Do you really want 'Danniel ' > considered distinct from 'Danniel ' in a comparison? In real life, > what does that buy you? It buys you the ability to store things where trailing spaces are signficant (for example passwords) within the existing limitations of not having a full set of the collation behavior.
Re: 'a' == 'a ' (Was: RE: [pgsql-advocacy] [GENERAL] Oracle buysInnobase)
От
Martijn van Oosterhout
Дата:
On Wed, Oct 19, 2005 at 02:05:20PM -0700, Dann Corbit wrote: > > When the compared datatypes are VARCHAR: YES > > What is the value of doing that? > > I can see plenty of harm and absolutely no return. We are talking about > blank padding before comparison. Do you really want 'Danniel ' > considered distinct from 'Danniel ' in a comparison? In real life, > what does that buy you? Well, looking from the point of view of using indexes, indexes can only really match on things that are equal. Which means the system is going to have to trim them anyway. I'm of the opinion that strings are strings and spaces are no different from other characters. That bit of the standard quoted earlier, if you read the PAD character that is different from any other character as being the NUL character, then 'a<space><nul>' is clearly different from 'a<space><space>'. This whacky space behaviour is something I associate with the char(N) and is the main reason I never use it. > Perhaps this is old hat to the long-timers around here and there is a > good explanation as to why varchar should have non-blank padding when > comparisons are performed. Can someone point me to documentation that > explains it? The way I understood it: char(N) is blank padding varchar(N) is not If you make varchar(n) do blank padding, then what's the difference between the two types? You may as well get rid of one... Have a nice day, -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a > tool for doing 5% of the work and then sitting around waiting for someone > else to do the other 95% so you can sue them.
Вложения
Dann Corbit wrote: > > I can see plenty of harm and absolutely no return. We are talking about > blank padding before comparison. Do you really want 'Danniel ' > considered distinct from 'Danniel ' in a comparison? In real life, > what does that buy you? 100% YES! If two values are the same, then any function on those two values should return the same result. Otherwise what does "equals" mean? At the very least length() is broken by your argument. Here it's CHAR that's broken IMHO - spawn of some punch-card spawned data processing rules of the 70s. -- Richard Huxton Archonet Ltd
Richard Huxton wrote: > Dann Corbit wrote: > >> >> I can see plenty of harm and absolutely no return. We are talking about >> blank padding before comparison. Do you really want 'Danniel ' >> considered distinct from 'Danniel ' in a comparison? In real life, >> what does that buy you? > > > 100% YES! > > If two values are the same, then any function on those two values should > return the same result. Otherwise what does "equals" mean? At the very > least length() is broken by your argument. I agree completely. I would much rather be precise than intuitive. And, I have done applications where the padding length was important, especially when working with remote batch processing in the credit industries and the like. Writing queries to create and process fixed-width batch files is much easier if you can rely on these kinds of behaviors. > > Here it's CHAR that's broken IMHO - spawn of some punch-card spawned > data processing rules of the 70s. > -- > Richard Huxton > Archonet Ltd > > ---------------------------(end of broadcast)--------------------------- > TIP 2: Don't 'kill -9' the postmaster > >