Thread: Differences in WHERE clause of SELECT

Differences in WHERE clause of SELECT

From
"Prabakaran, Vaishnavi"
Date:
<div class="WordSection1"><p class="MsoPlainText">Hi,<p class="MsoPlainText"> <p class="MsoPlainText">I am a newbie to
PostgreSQLand was wondering about the following behaviour.<p class="MsoPlainText"> <p class="MsoPlainText"> <p
class="MsoPlainText">SELECTtrue WHERE 1 = '1';     <-- Returns true <p class="MsoPlainText"> <p
class="MsoPlainText">SELECTtrue WHERE 1 BETWEEN '0' and '2';    <-- Returns true<p class="MsoPlainText"> <p
class="MsoPlainText">SELECTtrue WHERE 1 IS DISTINCT FROM '2';    <-- Returns true<p class="MsoPlainText"> <p
class="MsoPlainText">SELECTtrue WHERE 1 LIKE '1';   <-- Returns 'operator does not exist' Error<p
class="MsoPlainText"> <pclass="MsoPlainText">SELECT true WHERE '1' LIKE 1;   <-- Returns 'operator does not exist'
Error<pclass="MsoPlainText"> <p class="MsoPlainText"> <p class="MsoPlainText"> <p class="MsoPlainText">The first three
querieswork because of the implicit cast whereas the 'LIKE' is not using implicit cast. <p class="MsoPlainText"> <p
class="MsoPlainText">Canyou please help me understand why 'LIKE' does not use implicit cast ? <p class="MsoNormal"> <p
class="MsoNormal"> <pclass="MsoNormal"><span style="mso-fareast-language:EN-AU"> </span><p class="MsoNormal"><span
style="mso-fareast-language:EN-AU">BestRegards,</span><p class="MsoNormal"><span
style="mso-fareast-language:EN-AU">Vaishnavi</span><pclass="MsoNormal"> </div> 

Re: Differences in WHERE clause of SELECT

From
Josh Berkus
Date:
Prabakaran,


> I am a newbie to PostgreSQL and was wondering about the following
> behaviour.

pgsql-hackers is not the appropriate list for this kind of question.  In
the future, please post to pgsql-novice, pgsql-sql, or pgsql-general
with this kind of question.  Thanks.

> Can you please help me understand why 'LIKE' does not use implicit cast
> ? 

Like uses the operator class "text_pattern_ops" which doesn't include an
implict cast.  For one thing, the implicit cast is from text -->
integer, not the other way around, and there is no LIKE operator for
integers.

-- 
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com



Re: Differences in WHERE clause of SELECT

From
"Prabakaran, Vaishnavi"
Date:
Hi Berkus,

Thanks for your time and response.

I do understand that there is no LIKE operator support for integers and it would be great if you could help me
understandthe reason why is it not supported.
 

My intention is to know whether this is not supported because of any technical limitation or is it against any
Postgresql/SQLstandards. 
 

My use cases are like below ones :
Integer LIKE pattern [ESCAPE escape-character] 
1. List all the customers who are having negative balance:
SELECT * from Customer where balance LIKE ‘-%’

2. List all the customers whose id starts with 1:
SELECT * from Customer where cust_id LIKE ‘1%’

Thanks & Regards,
Vaishnavi


-----Original Message-----
From: pgsql-hackers-owner@postgresql.org [mailto:pgsql-hackers-owner@postgresql.org] On Behalf Of Josh Berkus
Sent: Wednesday, 10 July 2013 9:13 AM
To: pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] Differences in WHERE clause of SELECT

Prabakaran,


> I am a newbie to PostgreSQL and was wondering about the following 
> behaviour.

pgsql-hackers is not the appropriate list for this kind of question.  In the future, please post to pgsql-novice,
pgsql-sql,or pgsql-general with this kind of question.  Thanks.
 

> Can you please help me understand why 'LIKE' does not use implicit 
> cast ?

Like uses the operator class "text_pattern_ops" which doesn't include an implict cast.  For one thing, the implicit
castis from text --> integer, not the other way around, and there is no LIKE operator for integers.
 

--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: Differences in WHERE clause of SELECT

From
Jaime Casanova
Date:
On Wed, Jul 10, 2013 at 8:42 PM, Prabakaran, Vaishnavi
<vaishnavip@fast.au.fujitsu.com> wrote:
> Hi Berkus,
>
> Thanks for your time and response.
>
> I do understand that there is no LIKE operator support for integers and it would be great if you could help me
understandthe reason why is it not supported. 
>
> My intention is to know whether this is not supported because of any technical limitation or is it against any
Postgresql/SQLstandards. 
>

the latter

> My use cases are like below ones :
> Integer LIKE pattern [ESCAPE escape-character]
> 1. List all the customers who are having negative balance:
> SELECT * from Customer where balance LIKE ‘-%’
>

this is not cleaner implemented this way?
SELECT * FROM customer WHERE balance < 0;

> 2. List all the customers whose id starts with 1:
> SELECT * from Customer where cust_id LIKE ‘1%’
>

there is any real use for that query? i understand if you ask
for all customers whose names begins with 'A' but that the code
begins with '1'?

--
Jaime Casanova         www.2ndQuadrant.com
Professional PostgreSQL: Soporte 24x7 y capacitación
Phone: +593 4 5107566         Cell: +593 987171157



Re: Differences in WHERE clause of SELECT

From
"Prabakaran, Vaishnavi"
Date:
On Wed, Jul 10, 2013 at 8:42 PM, Prabakaran, Vaishnavi <vaishnavip@fast.au.fujitsu.com> wrote:
> Hi Berkus,
>
> Thanks for your time and response.
>
> I do understand that there is no LIKE operator support for integers and it would be great if you could help me
understandthe reason why is it not supported. 
>
> My intention is to know whether this is not supported because of any technical limitation or is it against any
Postgresql/SQLstandards. 
>

> the latter

I see. Understood. Looking at the SQL standard it does say that the operands needs to be character or octet. But I was
hopingthat this can be overridden by implicit conversion rules which are implementation specific.   

> My use cases are like below ones :
> Integer LIKE pattern [ESCAPE escape-character] 1. List all the
> customers who are having negative balance:
> SELECT * from Customer where balance LIKE '-%'
>

this is not cleaner implemented this way?
SELECT * FROM customer WHERE balance < 0;

> 2. List all the customers whose id starts with 1:
> SELECT * from Customer where cust_id LIKE '1%'
>

> there is any real use for that query? i understand if you ask for all customers whose names begins with 'A' but that
thecode begins with '1'? 

A legacy application we are migrating does have a weird requirement like this because it was running on 'another' RDBMS
whichdoes have support for implicit casting in LIKE predicate. 

Rgds,
Vaishnavi


--
Jaime Casanova         www.2ndQuadrant.com
Professional PostgreSQL: Soporte 24x7 y capacitación
Phone: +593 4 5107566         Cell: +593 987171157





Re: Differences in WHERE clause of SELECT

From
Robert Haas
Date:
On Tue, Jul 9, 2013 at 7:12 PM, Josh Berkus <josh@agliodbs.com> wrote:
> Like uses the operator class "text_pattern_ops" which doesn't include an
> implict cast.

This is wrong.  Casts are associated with data types, not operator classes.

> For one thing, the implicit cast is from text -->
> integer, not the other way around, and there is no LIKE operator for
> integers.

This is also wrong.  There's no implicit cast between text and integer
in either direction - only assignment casts.

I think the reason why the first few examples work and the last one
fails is that, in the first few cases, there are integer literals and
unknown literals, and the operations in question are defined for
integers (and for text), so we pick the integer interpretation and
call it good.  But in the non-working case, the operation is defined
to work only on text, and an integer argument is supplied.  So we have
to cast, and there's no implicit cast, ergo we fail.

The point is that we use a different procedure to decide what to do
with a quoted literal ('1', or '3.14159', or 'foo') than we do to
decide whether it's OK to cast a value of a type we already know.  For
example this fails:

rhaas=# create table foo (a int, b varchar);
CREATE TABLE
rhaas=# insert into foo values ('1', '1');
INSERT 0 1
rhaas=# select * from foo where a = b;
ERROR:  operator does not exist: integer = character varying
LINE 1: select * from foo where a = b;                                 ^

This is just like the OP's first example (which worked) except that
here there's no unknown literal, so we actually need to cast, and we
refuse to do so and fail.

I continue to be of the opinion that our behavior in this area is
bone-headed.  It's reasonable to reject integer = character varying on
the basis that we don't know whether integer or character varying
comparison semantics are wanted, and the two might give different
answers (think: leading zeroes), so we'd better ask the user to
clarify.  But  '1' LIKE 1 is not ambiguous; there is only one
plausible meaning for that, and we ought to adopt it, per the patch I
proposed previously.  Rejecting SQL that other systems happily accept
is unhelpful and unfriendly and it is costing us users and mind-share.

</rant>

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company



Re: Differences in WHERE clause of SELECT

From
Tom Lane
Date:
Robert Haas <robertmhaas@gmail.com> writes:
> I continue to be of the opinion that our behavior in this area is
> bone-headed.  It's reasonable to reject integer = character varying on
> the basis that we don't know whether integer or character varying
> comparison semantics are wanted, and the two might give different
> answers (think: leading zeroes), so we'd better ask the user to
> clarify.  But  '1' LIKE 1 is not ambiguous; there is only one
> plausible meaning for that, and we ought to adopt it, per the patch I
> proposed previously.  Rejecting SQL that other systems happily accept
> is unhelpful and unfriendly and it is costing us users and mind-share.

I don't agree with this opinion.  '1' + '2' might be claimed to have
only one plausible meaning as well, but that doesn't mean that we should
accept it.  There is a very fundamental distinction between numbers and
strings, and we'd do our users no service by pretending there isn't.
What's more, your argument for this essentially rests on the assumption
that LIKE represents only one possible operator choice, which is already
false today (see \do ~~) and might be even more false in future.
        regards, tom lane



Re: Differences in WHERE clause of SELECT

From
Greg Stark
Date:
On Tue, Jul 16, 2013 at 4:40 PM, Robert Haas <robertmhaas@gmail.com> wrote:
> But  '1' LIKE 1 is not ambiguous


What about '1' LIKE 01 ?

What do people who write this actually expect LIKE to do? I gather
they're actually treating it as a synonym for =?


-- 
greg



Re: Differences in WHERE clause of SELECT

From
Robert Haas
Date:
On Tue, Jul 16, 2013 at 12:05 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> I don't agree with this opinion.

I know.

> '1' + '2' might be claimed to have
> only one plausible meaning as well, but that doesn't mean that we should
> accept it.

That example clearly has multiple sensible interpretations, because
surely we have no way of knowing whether the user wants +(int2,int2),
+(int4,int4), +(int8,int8), +(real,real), +(money,money), etc., etc.
There are 42 binary + operators, of which at least 10 or so are
plausible interpretations of that call.  I have never proposed
changing the behavior of this case - though, to be fair, if we found a
way to just decide on int4, say, I bet we'd get very few complaints.

> There is a very fundamental distinction between numbers and
> strings, and we'd do our users no service by pretending there isn't.
> What's more, your argument for this essentially rests on the assumption
> that LIKE represents only one possible operator choice, which is already
> false today (see \do ~~) and might be even more false in future.

This is a better argument, but I'm still not buying it.  Sure, there
are four candidate functions there, but they all perform the same
logical operation. It looks to me like bpcharlike and textlike both
call the same underlying function, and on a quick glance namelike
appears only trivially different.  Only bytealike is significantly
different, in that it operates on the underlying bytes rather than the
hex-escaped (or backslash-escaped) version of those bytes.  It's a
pretty good bet that the user did not want an implicit cast to bytea.

PostgreSQL requires more casts that any other mainstream database
system on the planet, and it's one of the biggest barriers to
migrating from other database systems to PostgreSQL.  I understand
that there is a consensus in this forum to pretend that our
intolerance is a virtue, but I don't agree with that consensus. A
request for =(text,int) is ambiguous; we really do not know what is
meant.  A request for ~~(int,text) is not ambiguous; we reject it out
of pedantry, not necessity.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company



Re: Differences in WHERE clause of SELECT

From
Merlin Moncure
Date:
On Tue, Jul 16, 2013 at 12:58 PM, Robert Haas <robertmhaas@gmail.com> wrote:
> PostgreSQL requires more casts that any other mainstream database
> system on the planet, and it's one of the biggest barriers to
> migrating from other database systems to PostgreSQL.  I understand
> that there is a consensus in this forum to pretend that our
> intolerance is a virtue, but I don't agree with that consensus. A
> request for =(text,int) is ambiguous; we really do not know what is
> meant.  A request for ~~(int,text) is not ambiguous; we reject it out
> of pedantry, not necessity.

You make some good points but I still wonder (I'm recalling your lpad
discussion from a while back) if we're better off providing specific
overloads and casts for portability pain points instead of changing
casting mechanics.

merlin



Re: Differences in WHERE clause of SELECT

From
Robert Haas
Date:
On Tue, Jul 16, 2013 at 2:58 PM, Merlin Moncure <mmoncure@gmail.com> wrote:
> On Tue, Jul 16, 2013 at 12:58 PM, Robert Haas <robertmhaas@gmail.com> wrote:
>> PostgreSQL requires more casts that any other mainstream database
>> system on the planet, and it's one of the biggest barriers to
>> migrating from other database systems to PostgreSQL.  I understand
>> that there is a consensus in this forum to pretend that our
>> intolerance is a virtue, but I don't agree with that consensus. A
>> request for =(text,int) is ambiguous; we really do not know what is
>> meant.  A request for ~~(int,text) is not ambiguous; we reject it out
>> of pedantry, not necessity.
>
> You make some good points but I still wonder (I'm recalling your lpad
> discussion from a while back) if we're better off providing specific
> overloads and casts for portability pain points instead of changing
> casting mechanics.

TBH, I think we've gone too far in that direction already.  We've got
numerous cases where there are multiple function or operator
declarations that exist only to work around shortcomings of the
typecasting system.  For example, we've got pg_size_pretty(bigint) and
pg_size_pretty(numeric); if we adopted the patch that I proposed
previously, one of those would be enough, and then it would work for
integers, too.  We could instead add a third version for integers, but
where's the joy in that?

We've currently got 10 different functions for || that include
||(anynonarray,text), ||(text,anynonarray), and ||(text,text).  The
first two of those are workarounds for the fact that the third one
does not do what you want.   And then there's concat(variadic "any"),
which is yet a third way of hacking around the same general category
of problem.  The patch I proposed previously would allow concat to be
declared as variadic text if we so desired, but it wouldn't help with
the || case.  I think that's OK; I never pretended that patch would
fix everything, but I still think it's an improvement over what we
have now.

We can certainly continue to play whack-a-mole and dream up a new
solution every time a really intolerable variant of this problem comes
up.  But that doesn't seem good to me.  It means that every case
behaves a little different from every other case, and the whole thing
is kinda arcane and hard to understand, even for hackers.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company



Re: Differences in WHERE clause of SELECT

From
Kevin Grittner
Date:
Robert Haas <robertmhaas@gmail.com> wrote:

> We can certainly continue to play whack-a-mole and dream up a new
> solution every time a really intolerable variant of this problem comes
> up.  But that doesn't seem good to me.  It means that every case
> behaves a little different from every other case, and the whole thing
> is kinda arcane and hard to understand, even for hackers.

If you're building up a list of things that generate errors in
PostgreSQL but not other DBMS products, make sure you have this:

test=# create table t(d date);
CREATE TABLE
test=# insert into t values (NULL);
INSERT 0 1
test=# insert into t values (COALESCE(NULL, NULL));
ERROR:  column "d" is of type date but expression is of type text
LINE 1: insert into t values (COALESCE(NULL, NULL));
                              ^
HINT:  You will need to rewrite or cast the expression.

From a user perspective, it's hard to explain why COALESCE(NULL,
NULL) fails in a location that a bare NULL works.  From the
perspective of those working on the code, and looking at the
problem from the inside out, it seems sane; but that's the only
perspective from which it does.

--
Kevin Grittner
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company



Re: Differences in WHERE clause of SELECT

From
"Prabakaran, Vaishnavi"
Date:
Hi,

Thanks for your responses.

The specific use case which I am interested in is

" Numeric LIKE Pattern_string ".

I'm willing to attempt a patch to support the specific use case above by adding implicit casts, without modifying the
entirecasting rules. 

Is this something that is likely to be included in the code ?

Thanks & Regards,
Vaishnavi

-----Original Message-----
From: pgsql-hackers-owner@postgresql.org [mailto:pgsql-hackers-owner@postgresql.org] On Behalf Of Kevin Grittner
Sent: Wednesday, 17 July 2013 6:23 AM
To: Robert Haas; Merlin Moncure
Cc: Tom Lane; Josh Berkus; pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] Differences in WHERE clause of SELECT

Robert Haas <robertmhaas@gmail.com> wrote:

> We can certainly continue to play whack-a-mole and dream up a new
> solution every time a really intolerable variant of this problem comes
> up.  But that doesn't seem good to me.  It means that every case
> behaves a little different from every other case, and the whole thing
> is kinda arcane and hard to understand, even for hackers.

If you're building up a list of things that generate errors in PostgreSQL but not other DBMS products, make sure you
havethis: 

test=# create table t(d date);
CREATE TABLE
test=# insert into t values (NULL);
INSERT 0 1
test=# insert into t values (COALESCE(NULL, NULL));
ERROR:  column "d" is of type date but expression is of type text LINE 1: insert into t values (COALESCE(NULL, NULL));
                              ^
HINT:  You will need to rewrite or cast the expression.

From a user perspective, it's hard to explain why COALESCE(NULL,
NULL) fails in a location that a bare NULL works.  From the perspective of those working on the code, and looking at
theproblem from the inside out, it seems sane; but that's the only perspective from which it does. 

--
Kevin Grittner
EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers





Re: Differences in WHERE clause of SELECT

From
Tom Lane
Date:
"Prabakaran, Vaishnavi" <vaishnavip@fast.au.fujitsu.com> writes:
> The specific use case which I am interested in is 

> " Numeric LIKE Pattern_string ".

> I'm willing to attempt a patch to support the specific use case above by adding implicit casts, without modifying the
entirecasting rules.
 

> Is this something that is likely to be included in the code ? 

No, especially not if you do it by adding implicit casts.  That would
have unfortunate side-effects in all sorts of contexts.

If you're dead set on having this sort of behavior, you can do it today
with a custom operator, for instance:

regression=# select 1.4 like 'foo';
ERROR:  operator does not exist: numeric ~~ unknown
LINE 1: select 1.4 like 'foo';                  ^
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.
regression=# create function numericlike(numeric, text) returns bool as
regression-# 'select $1::text like $2' language sql;
CREATE FUNCTION
regression=# create operator ~~ (leftarg = numeric, rightarg = text,
regression(# procedure = numericlike);
CREATE OPERATOR
regression=# select 1.4 like 'foo';?column? 
----------f
(1 row)

I'd suggest doing that rather than making changes that are likely to
have side-effects on behavior entirely unrelated to LIKE.  In addition,
you won't have to sell the community on whether this behavior is
actually a good idea.
        regards, tom lane