Thread: Differences in WHERE clause of SELECT
<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>
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
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
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
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
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
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
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
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
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
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
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
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
"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