Обсуждение: WHERE parent IN (0,-1)

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

> 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,
Pennsylvania19026
 

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

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

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

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