pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
WHERE 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>
  1999-07-28 00:09 ` Re: [SQL] WHERE parent IN (0,-1) Bruce Momjian <maillist@candle.pha.pa.us>
  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-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   ` Re: [SQL] WHERE parent IN (0,-1) Tom Lane <tgl@sss.pgh.pa.us>
  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-27 23:51 WHERE parent IN (0,-1) Drew Whittle <drew@albatross.co.nz>
  1999-07-28 00:09 ` Re: [SQL] WHERE parent IN (0,-1) Bruce Momjian <maillist@candle.pha.pa.us>
@ 1999-07-28 13:47   ` Tom Lane <tgl@sss.pgh.pa.us>
  1999-07-28 14:47     ` Re: [SQL] WHERE parent IN (0,-1) Thomas Lockhart <lockhart@alumni.caltech.edu>
  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-27 23:51 WHERE parent IN (0,-1) Drew Whittle <drew@albatross.co.nz>
  1999-07-28 00:09 ` Re: [SQL] WHERE parent IN (0,-1) Bruce Momjian <maillist@candle.pha.pa.us>
  1999-07-28 13:47   ` Re: [SQL] WHERE parent IN (0,-1) Tom Lane <tgl@sss.pgh.pa.us>
@ 1999-07-28 14:47     ` Thomas Lockhart <lockhart@alumni.caltech.edu>
  1999-07-28 17:47       ` Re: [SQL] WHERE parent IN (0,-1) 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-27 23:51 WHERE parent IN (0,-1) Drew Whittle <drew@albatross.co.nz>
  1999-07-28 00:09 ` Re: [SQL] WHERE parent IN (0,-1) Bruce Momjian <maillist@candle.pha.pa.us>
  1999-07-28 13:47   ` Re: [SQL] WHERE parent IN (0,-1) Tom Lane <tgl@sss.pgh.pa.us>
  1999-07-28 14:47     ` Re: [SQL] WHERE parent IN (0,-1) Thomas Lockhart <lockhart@alumni.caltech.edu>
@ 1999-07-28 17:47       ` Tom Lane <tgl@sss.pgh.pa.us>
  1999-07-29 03:27         ` Re: [SQL] WHERE parent IN (0,-1) 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-27 23:51 WHERE parent IN (0,-1) Drew Whittle <drew@albatross.co.nz>
  1999-07-28 00:09 ` Re: [SQL] WHERE parent IN (0,-1) Bruce Momjian <maillist@candle.pha.pa.us>
  1999-07-28 13:47   ` Re: [SQL] WHERE parent IN (0,-1) Tom Lane <tgl@sss.pgh.pa.us>
  1999-07-28 14:47     ` Re: [SQL] WHERE parent IN (0,-1) Thomas Lockhart <lockhart@alumni.caltech.edu>
  1999-07-28 17:47       ` Re: [SQL] WHERE parent IN (0,-1) Tom Lane <tgl@sss.pgh.pa.us>
@ 1999-07-29 03:27         ` Thomas Lockhart <lockhart@alumni.caltech.edu>
  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