Thread: 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