pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedWHERE parent IN (0,-1)
6+ messages / 4 participants
[nested] [flat]
* WHERE parent IN (0,-1)
@ 1999-07-27 23:51 Drew Whittle <drew@albatross.co.nz>
0 siblings, 1 reply; 6+ messages in thread
From: Drew Whittle @ 1999-07-27 23:51 UTC (permalink / raw)
To: pgsql-sql
Hi,
I'm pretty new to Postgres, so please excuse my ignorance.
I have a sql script that uses WHERE parent IN (0,-1), this works fine with
MySQL, Access, & MS-SQL Server, unfortunately Postgres does not like this
statement, I can get around the problem (I think) by using WHERE parent IN
(0,'-1').
Is this valid?
Is there another way to do this that works with Postgres and the others?
(They dont like the ' around -1)
Thanks,
Drew
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: [SQL] WHERE parent IN (0,-1)
@ 1999-07-28 00:09 Bruce Momjian <maillist@candle.pha.pa.us>
parent: Drew Whittle <drew@albatross.co.nz>
0 siblings, 1 reply; 6+ messages in thread
From: Bruce Momjian @ 1999-07-28 00:09 UTC (permalink / raw)
To: Drew Whittle <drew@albatross.co.nz>; +Cc: pgsql-sql
> Hi,
>
> I'm pretty new to Postgres, so please excuse my ignorance.
>
> I have a sql script that uses WHERE parent IN (0,-1), this works fine with
> MySQL, Access, & MS-SQL Server, unfortunately Postgres does not like this
> statement, I can get around the problem (I think) by using WHERE parent IN
> (0,'-1').
>
> Is this valid?
>
> Is there another way to do this that works with Postgres and the others?
> (They dont like the ' around -1)
>
> Thanks,
>
> Drew
>
>
>
yes, that's a good workaround. Added to TODO list:
* select * from pg_class where oid in (0,-1);
--
Bruce Momjian | http://www.op.net/~candle
maillist@candle.pha.pa.us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: [SQL] WHERE parent IN (0,-1)
@ 1999-07-28 13:47 Tom Lane <tgl@sss.pgh.pa.us>
parent: Bruce Momjian <maillist@candle.pha.pa.us>
0 siblings, 1 reply; 6+ messages in thread
From: Tom Lane @ 1999-07-28 13:47 UTC (permalink / raw)
To: Thomas Lockhart <lockhart@alumni.caltech.edu>; +Cc: Drew Whittle <drew@albatross.co.nz>; pgsql-sql
Bruce Momjian <maillist@candle.pha.pa.us> writes:
>> I can get around the problem (I think) by using WHERE parent IN
>> (0,'-1').
> yes, that's a good workaround. Added to TODO list:
> * select * from pg_class where oid in (0,-1);
It's a grammar problem. in_expr_nodes and not_in_expr_nodes expect the
elements of the IN-list to be AexprConst ... I wonder why not a_expr
instead? Might be a reduce conflict, but I bet we could at least use
b_expr. Thomas, any comments?
regards, tom lane
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: [SQL] WHERE parent IN (0,-1)
@ 1999-07-28 14:47 Thomas Lockhart <lockhart@alumni.caltech.edu>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 6+ messages in thread
From: Thomas Lockhart @ 1999-07-28 14:47 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Drew Whittle <drew@albatross.co.nz>; pgsql-sql
> >> I can get around the problem (I think) by using WHERE parent IN
> >> (0,'-1').
> It's a grammar problem. in_expr_nodes and not_in_expr_nodes expect
> the elements of the IN-list to be AexprConst ... I wonder why not
> a_expr instead? Might be a reduce conflict, but I bet we could at
> least use b_expr. Thomas, any comments?
The immediate problem is with handling the minus sign; I should be
able to fix that (I had changed the handling of minus signs to get
better behavior with cases like "- 1" and "2-1" in other contexts). My
recollection is that SQL92 allows only lists of constants, and there
may have been shift/reduce problems with doing more than that. Will
poke at it in between getting Bruce's man pages going ;)
- Thomas
--
Thomas Lockhart lockhart@alumni.caltech.edu
South Pasadena, California
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: [SQL] WHERE parent IN (0,-1)
@ 1999-07-28 17:47 Tom Lane <tgl@sss.pgh.pa.us>
parent: Thomas Lockhart <lockhart@alumni.caltech.edu>
0 siblings, 1 reply; 6+ messages in thread
From: Tom Lane @ 1999-07-28 17:47 UTC (permalink / raw)
To: Thomas Lockhart <lockhart@alumni.caltech.edu>; +Cc: Drew Whittle <drew@albatross.co.nz>; pgsql-sql
>> It's a grammar problem. in_expr_nodes and not_in_expr_nodes expect
>> the elements of the IN-list to be AexprConst ... I wonder why not
>> a_expr instead? Might be a reduce conflict, but I bet we could at
>> least use b_expr. Thomas, any comments?
Indeed, there is no reduce conflict created by using a_expr, so I went
ahead and committed it. Also tidied the list-generating code a bit.
Stuff like
select * from int4_tbl where f1 not in (0,123455+1);
seems to work fine now.
Drew, if you don't want to wait around for 6.6 to fix this, you
should be able to just change the occurrences of AexprConst to a_expr
in the productions for in_expr_nodes: and not_in_expr_nodes: in
src/backend/parser/gram.y. I wouldn't advise trying to copy the current
gram.y into 6.5, since there are a bunch of other changes in it already...
regards, tom lane
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: [SQL] WHERE parent IN (0,-1)
@ 1999-07-29 03:27 Thomas Lockhart <lockhart@alumni.caltech.edu>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 6+ messages in thread
From: Thomas Lockhart @ 1999-07-29 03:27 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Drew Whittle <drew@albatross.co.nz>; pgsql-sql
> >> It's a grammar problem. in_expr_nodes and not_in_expr_nodes expect
> >> the elements of the IN-list to be AexprConst ... I wonder why not
> >> a_expr instead? Might be a reduce conflict, but I bet we could at
> >> least use b_expr. Thomas, any comments?
> Indeed, there is no reduce conflict created by using a_expr, so I went
> ahead and committed it. Also tidied the list-generating code a bit.
Great. Did you fix all the places where IN (values) is allowed? I was
doing that, but will merge your fixes...
- Thomas
--
Thomas Lockhart lockhart@alumni.caltech.edu
South Pasadena, California
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~1999-07-29 03:27 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1999-07-27 23:51 WHERE parent IN (0,-1) Drew Whittle <drew@albatross.co.nz>
1999-07-28 00:09 ` Bruce Momjian <maillist@candle.pha.pa.us>
1999-07-28 13:47 ` Tom Lane <tgl@sss.pgh.pa.us>
1999-07-28 14:47 ` Thomas Lockhart <lockhart@alumni.caltech.edu>
1999-07-28 17:47 ` Tom Lane <tgl@sss.pgh.pa.us>
1999-07-29 03:27 ` Thomas Lockhart <lockhart@alumni.caltech.edu>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox