Обсуждение: select statement fails
Any idea why the following select statement does not return rows ? This select statement is generated by npgsql2 beta 3 so I cannot change it in my application. How to fix without changing select statement ? Andrus. create temp table test ( tc char(1) ); insert into test values(' '); select * from test where tc=' '::text; Using "PostgreSQL 8.3beta4, compiled by Visual C++ build 1400"
Andrus wrote: > Any idea why the following select statement does not return rows ? > This select statement is generated by npgsql2 beta 3 so I cannot change it > in my application. > How to fix without changing select statement ? > > Andrus. > > create temp table test ( tc char(1) ); > insert into test values(' '); > select * from test where tc=' '::text; It doesn't return rows because you're using a space-padded type (char) to try and store a space. I think you probably want varchar(1) instead. -- Richard Huxton Archonet Ltd
Andrus Moor wrote: > Richard, > >> It doesn't return rows because you're using a space-padded type (char) >> to try and store a space. >> >> I think you probably want varchar(1) instead. > > thank you. > I have production database whose schema cannot changed easily. ALTER TABLE...ALTER COLUMN...TYPE will do it within one statement. It will require a lock on the table though. > I is more reasonable to force npgsql driver to generate other code if no > other solution. > npgsql driver wants to add explicit casts to parameters. > Is it reasonable to force driver to generate code > > select * from test where tc=' '::char(1); > > for char parameter type ? I'd say so - I presume it just needs to be taught about different types of text. Presumably it already knows that int8 is different from int4, so it'll be doing something similar already. Check if there is a mailing-list for the npgsql project and ask there - someone might already be working on it. -- Richard Huxton Archonet Ltd
Andrus wrote: > Any idea why the following select statement does not return rows ? > This select statement is generated by npgsql2 beta 3 so I > cannot change it in my application. > How to fix without changing select statement ? > > Andrus. > > create temp table test ( tc char(1) ); > insert into test values(' '); > select * from test where tc=' '::text; Because the arguments to the operator "=" are of different type, implicit type conversion takes place. "character(1)" will by converted to "text", during this conversion trailing blanks will be ignored, as befits the "character(n)" type. You can get what you probably want by: create temp table test ( tc varchar(1) ); Yours, Laurenz Albe
> ALTER TABLE...ALTER COLUMN...TYPE will do it within one statement. It will > require a lock on the table though. 1. This is part of composite primary key. It is discriminator column and cannot contain empty string, only single char is allowed. 2. I'm afraid that this will broke existing applications. So I'm not sure that it is reasonable to make such change. >> I is more reasonable to force npgsql driver to generate other code if no >> other solution. >> npgsql driver wants to add explicit casts to parameters. >> Is it reasonable to force driver to generate code >> >> select * from test where tc=' '::char(1); >> >> for char parameter type ? > > I'd say so - I presume it just needs to be taught about different types of > text. Presumably it already knows that int8 is different from int4, so > it'll be doing something similar already. Should it cast to char(1) or varchar(1) ? Driver cannot determine this form .NET type char. So this seems not possible without providing additional meta information to driver, i.e. standard ADO .NET interface cannot used. So I think that only solution is to create this query using string concatenation and possibly open it to sql injection attacks, parameter replacement is not possible. > Check if there is a mailing-list for the npgsql project and ask there - > someone might already be working on it. Done. Andrus.
Andrus wrote: >> ALTER TABLE...ALTER COLUMN...TYPE will do it within one statement. It will >> require a lock on the table though. > > 1. This is part of composite primary key. It is discriminator column and > cannot contain empty string, only single char is allowed. char(1) doesn't enforce that. It enforces a maximum of 1 character. ^ richardh=> CREATE TABLE chartest (c char(1) NOT NULL); CREATE TABLE richardh=> INSERT INTO chartest VALUES ('a'),(' '),(''); INSERT 0 3 What it will do is strip the space in the second value so the last two values are the same (and have length()=0). Or rather, spaces are treated as trailing the value which amounts to the same thing here. If you want a single character you'll want to add a CHECK > '' to a varchar or text column. That's because using the check on a char() column will disallow a space-character too. richardh=> ALTER TABLE chartest ADD CONSTRAINT ccheck CHECK (c > ''); ALTER TABLE richardh=> INSERT INTO chartest VALUES ('a'),(' '),(''); ERROR: new row for relation "chartest" violates check constraint "ccheck" richardh=> INSERT INTO chartest VALUES ('a'),(' '); ERROR: new row for relation "chartest" violates check constraint "ccheck" > 2. I'm afraid that this will broke existing applications. Possible, particularly since the behaviour of the column as defined is problematic anyway. It depends on how they expect a single space to behave vs an empty string. > So I'm not sure that it is reasonable to make such change. > >>> I is more reasonable to force npgsql driver to generate other code if no >>> other solution. >>> npgsql driver wants to add explicit casts to parameters. >>> Is it reasonable to force driver to generate code >>> >>> select * from test where tc=' '::char(1); >>> >>> for char parameter type ? >> I'd say so - I presume it just needs to be taught about different types of >> text. Presumably it already knows that int8 is different from int4, so >> it'll be doing something similar already. > > Should it cast to char(1) or varchar(1) ? Well in your case char(1), obviously. > Driver cannot determine this form .NET type char. I'm guessing it doesn't rely on the .NET type, but rather on the database types. Certainly there are plenty of types available to PG that probably don't have a mapping in a standard .NET install. > So this seems not possible without providing additional meta information to > driver, i.e. standard ADO .NET interface cannot used. > > So I think that only solution is to create this query using string > concatenation and possibly open it to sql injection attacks, parameter > replacement is not possible. Ask the .npgsql mailing lists. They'll be able to tell you. There must be a way of handling "non-standard" types in any case. -- Richard Huxton Archonet Ltd
> > > > > > Ask the .npgsql mailing lists. They'll be able to tell you. There must be a > way of handling "non-standard" types in any case. > Hi all! We are already working on that. The cast problem is indeed a bug in Npgsql. We already have a one line fix for it. Check it out here: http://pgfoundry.org/forum/message.php?msg_id=1003377 I hope it helps. -- Regards, Francisco Figueiredo Jr. fxjr.blogspot.com www.npgsql.org
On Wed, Apr 9, 2008 at 1:31 PM, Francisco Figueiredo Jr. <francisco@npgsql.org> wrote: > > > > > > > > > > Ask the .npgsql mailing lists. They'll be able to tell you. There must be a > > way of handling "non-standard" types in any case. > > > > Hi all! > > We are already working on that. The cast problem is indeed a bug in > Npgsql. We already have a one line fix for it. > > Check it out here: > > http://pgfoundry.org/forum/message.php?msg_id=1003377 > > I hope it helps. > Patch applied! Please, grab latest cvs code and give it a try. Note that you still will receive an string when working with char columns. But you will be able to assign char values to NpgsqlParameters without any problem. Please, let us know if you have any problems. -- Regards, Francisco Figueiredo Jr. http://fxjr.blogspot.com http://www.npgsql.org
Albe, >> select * from test where tc=' '::text; >Because the arguments to the operator "=" are of different type, >implicit type conversion takes place. >"character(1)" will by converted to "text", during this conversion >trailing blanks will be ignored, as befits the "character(n)" type. Thank you. 1. SQL assumes that CHARACTER(n) column is always padded with spaces in right. So casting to text should preserve spaces. Why PostgreSQL cast to text violates SQL ? 2. create table test ( tc char(1) ); create index tc on test(tc); select * from test where tc='x'::text; I'm afraid that if test table has large number of rows, PostgreSQL is not capable to use index for this query doe to the cast to text. Is it so ? Andrus.
On Thu, Apr 10, 2008 at 11:13:33AM +0300, Andrus wrote: > 1. SQL assumes that CHARACTER(n) column is always padded with spaces in > right. > So casting to text should preserve spaces. > Why PostgreSQL cast to text violates SQL ? It says it is padded with spaces, but it also says that these spaces are insignificant and should be ignored in certain contexts. This area of the spec is poorly worked out, see http://archives.postgresql.org/pgsql-sql/2004-02/msg00229.php for some examples of where the behaviour you want doesn't work. > 2. > > create table test ( tc char(1) ); > create index tc on test(tc); > select * from test where tc='x'::text; > > I'm afraid that if test table has large number of rows, PostgreSQL is not > capable to use index for this query doe to the cast to text. > Is it so ? PostgreSQL does have the concept of cross-type index operators, so the above may work in recent versions. On the other hand, you could just drop the cast and it will always work. Seems odd you add a cast explicitly to a type different from the column you are comparing to. It's just asking for trouble. Have a nice day, -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > Please line up in a tree and maintain the heap invariant while > boarding. Thank you for flying nlogn airlines.
Вложения
On Apr 10, 2008, at 3:13 AM, Andrus wrote: > Albe, > >>> select * from test where tc=' '::text; >> Because the arguments to the operator "=" are of different type, >> implicit type conversion takes place. >> "character(1)" will by converted to "text", during this conversion >> trailing blanks will be ignored, as befits the "character(n)" type. > > Thank you. > > 1. SQL assumes that CHARACTER(n) column is always padded with spaces > in > right. That is only for storage and display. > So casting to text should preserve spaces. > Why PostgreSQL cast to text violates SQL ? It doesn't and it is right there in the manual: "Values of type character are physically padded with spaces to the specified width n, and are stored and displayed that way. However, the padding spaces are treated as semantically insignificant. Trailing spaces are disregarded when comparing two values of type character, and they will be removed when converting a character value to one of the other string types. Note that trailing spaces are semantically significant in character varying and text values." http://www.postgresql.org/docs/current/interactive/datatype-character.html > 2. > > create table test ( tc char(1) ); > create index tc on test(tc); > select * from test where tc='x'::text; > > I'm afraid that if test table has large number of rows, PostgreSQL > is not > capable to use index for this query doe to the cast to text. > Is it so ? You have two options: 1. Just us text for the column's data type. 2. Create an index on the column cast as text: CREATE INDEX test_tc_txt_idx ON test (tc::text); Erik Jones DBA | Emma® erik@myemma.com 800.595.4401 or 615.292.5888 615.292.0777 (fax) Emma helps organizations everywhere communicate & market in style. Visit us online at http://www.myemma.com
"Andrus" <kobruleht2@hot.ee> writes: > 1. SQL assumes that CHARACTER(n) column is always padded with spaces in > right. > So casting to text should preserve spaces. No, it should not. In CHAR(n), trailing spaces are semantically insignificant; 'foo' and 'foo ' are considered equal. In TEXT they are just as significant as any other character, and those strings are definitely not equal. So 'foo ' as CHAR(4) and 'foo ' as TEXT do not actually mean the same thing at all, and similarly ' ' means two different things as CHAR(1) and as TEXT, even though they look the same. The SQL spec's definition of CHAR(n) behavior is really pretty broken in my opinion; you're almost always better off using varchar. In this particular case, where you think that a space has semantic significance, CHAR(n) is simply wrong. regards, tom lane