Hi!

vac=> create table x (y int, z int);
CREATE
vac=> insert into x values (1,1);
INSERT 18168 1
vac=> insert into x values (1,2);
INSERT 18169 1
vac=> insert into x values (2,1);
INSERT 18170 1
vac=> insert into x values (2,2);
INSERT 18171 1
vac=> select * from x where y = (select max(y) from x);
ERROR:  parser: parse error at or near "select"
vac=> select * from x where y <> (select max(y) from x);
ERROR:  parser: parse error at or near "select"
vac=> select * from x where y < (select max(y) from x);
ERROR:  parser: parse error at or near "select"
vac=> select * from x where (y,z) = (select max(y), max(z) from x);
ERROR:  parser: parse error at or near "="
vac=> select * from x where (y,z) = ANY (select min(y), max(z) from x);
ERROR:  parser: parse error at or near "="
vac=> select * from x where (y,z) <> (select max(y), max(z) from x);
y|z
-+-
1|1
1|2
2|1
(3 rows)

Tom, Bruce - could you take care about this ?
(BTW, I fixed parse_expr.c broken for EXISTS...)
I'm going home now and will be here ~ 2 Feb 20:00 (PST). Hope to include 
subselect code into CVS in the next 24 hrs (from now)...

Also, could someone take care about data/queries for regression tests ?
(May be by using "big boys"...)
This would be very helpful!

TIA,
	Vadim

Mon, 02 Feb 1998 17:54:01 +0700
"Vadim B. Mikheev" <vadim@sable.krasnoyarsk.su>
subquery syntax broken
> Tom, Bruce - could you take care about this ?

Bruce? Let me know if you want me to look at it. I didn't include this
syntax originally since I thought singleton results like aggregates would
certainly not be implemented in the first cut. Forgot about Vadim's
prodigious talents :)

> (BTW, I fixed parse_expr.c broken for EXISTS...)
> I'm going home now and will be here ~ 2 Feb 20:00 (PST). Hope to include
> subselect code into CVS in the next 24 hrs (from now)...
>
> Also, could someone take care about data/queries for regression tests ?
> (May be by using "big boys"...)

Yes, we should add a "subselect.sql" regression test. Anyone taking a first
cut?

                                          - Tom


Mon, 02 Feb 1998 15:41:22 +0000
"Thomas G. Lockhart" <lockhart@alumni.caltech.edu>
Re: [HACKERS] subquery syntax broken
> vac=> select * from x where y = (select max(y) from x);
> ERROR:  parser: parse error at or near "select"
> vac=> select * from x where y <> (select max(y) from x);
> ERROR:  parser: parse error at or near "select"

Sorry I missed that most obvious option.  Here is the patch.  I will
apply it today.

---------------------------------------------------------------------------

*** ./backend/parser/gram.y.orig	Mon Feb  2 11:51:05 1998
--- ./backend/parser/gram.y	Mon Feb  2 11:59:12 1998
***************
*** 3330,3335 ****
--- 3330,3345 ----
  					n->subselect = $5;
  					$$ = (Node *)n;
  				}
+ 		| a_expr Op '(' SubSelect ')'
+ 				{
+ 					SubLink *n = makeNode(SubLink);
+ 					n->lefthand = lcons($1, NULL);
+ 					n->oper = lcons($2,NIL);
+ 					n->useor = false;
+ 					n->subLinkType = ALL_SUBLINK;
+ 					n->subselect = $4;
+ 					$$ = (Node *)n;
+ 				}
  		| a_expr AND a_expr
  				{	$$ = makeA_Expr(AND, NULL, $1, $3); }
  		| a_expr OR a_expr

-- 
Bruce Momjian
maillist@candle.pha.pa.us

Mon, 2 Feb 1998 12:00:14 -0500 (EST)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] subquery syntax broken
> 
> > Tom, Bruce - could you take care about this ?
> 
> Bruce? Let me know if you want me to look at it. I didn't include this
> syntax originally since I thought singleton results like aggregates would
> certainly not be implemented in the first cut. Forgot about Vadim's
> prodigious talents :)

It's not the aggregates, it is the whole '= (subquery)' that is missing
from gram.y.  I am adding it now.

	test=> select * from pg_user where usesysid = (select usesysid from
	pg_user);
	ERROR:  parser: syntax error at or near "select"


> 
> > (BTW, I fixed parse_expr.c broken for EXISTS...)
> > I'm going home now and will be here ~ 2 Feb 20:00 (PST). Hope to include
> > subselect code into CVS in the next 24 hrs (from now)...
> >
> > Also, could someone take care about data/queries for regression tests ?
> > (May be by using "big boys"...)
> 
> Yes, we should add a "subselect.sql" regression test. Anyone taking a first
> cut?
> 
>                                           - Tom
> 
> 


-- 
Bruce Momjian
maillist@candle.pha.pa.us

Mon, 2 Feb 1998 12:12:01 -0500 (EST)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] subquery syntax broken
> It's not the aggregates, it is the whole '= (subquery)' that is missing
> from gram.y.  I am adding it now.

Right, and aggregates are the only way in general to get a singleton result from
a subselect. OK, I forgot about "where y = (select 1)". Well, only _useful_
way?? I'll bet I'm forgetting another one too...

                                - Tom


Mon, 02 Feb 1998 17:53:35 +0000
"Thomas G. Lockhart" <lockhart@alumni.caltech.edu>
Re: [HACKERS] subquery syntax broken
> 
> > It's not the aggregates, it is the whole '= (subquery)' that is missing
> > from gram.y.  I am adding it now.
> 
> Right, and aggregates are the only way in general to get a singleton result from
> a subselect. OK, I forgot about "where y = (select 1)". Well, only _useful_
> way?? I'll bet I'm forgetting another one too...
> 

Sometimes you have = (subselect) with one row, often with correlated
subqueries, but most often with aggregates.


-- 
Bruce Momjian
maillist@candle.pha.pa.us

Mon, 2 Feb 1998 13:29:33 -0500 (EST)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] subquery syntax broken
Thomas G. Lockhart wrote:
> 
> > It's not the aggregates, it is the whole '= (subquery)' that is missing
> > from gram.y.  I am adding it now.
> 
> Right, and aggregates are the only way in general to get a singleton result from
> a subselect. OK, I forgot about "where y = (select 1)". Well, only _useful_
> way?? I'll bet I'm forgetting another one too...

How about "where y = (select distinct foo from bar where n=5)" ?

Ocie

Mon, 2 Feb 1998 10:52:10 -0800 (PST)
ocie@paracel.com
Re: [HACKERS] subquery syntax broken
Bruce Momjian wrote:
> 
> > vac=> select * from x where y = (select max(y) from x);
> > ERROR:  parser: parse error at or near "select"
> > vac=> select * from x where y <> (select max(y) from x);
> > ERROR:  parser: parse error at or near "select"
> 
> Sorry I missed that most obvious option.  Here is the patch.  I will
> apply it today.

It doesn't work for =, >, etc:

vac=> select * from x where y = (select max(y) from x);
ERROR:  parser: parse error at or near "select"

but work for others:

vac=> select * from x where y @ (select max(y) from x);
ERROR:  There is no operator '@' for types 'int4' and 'int4'
        You will either have to retype this query using an explicit cast,
        or you will have to define the operator using CREATE OPERATOR

Also:

> +               | a_expr Op '(' SubSelect ')'
> +                               {
> +                                       SubLink *n = makeNode(SubLink);
> +                                       n->lefthand = lcons($1, NULL);
> +                                       n->oper = lcons($2,NIL);
> +                                       n->useor = false;
> +                                       n->subLinkType = ALL_SUBLINK;
                                                           ^^^^^^^^^^^
                   should be EXPR_SUBLINK
> +                                       n->subselect = $4;
> +                                       $$ = (Node *)n;
> +                               }

Vadim

Tue, 03 Feb 1998 09:50:17 +0700
"Vadim B. Mikheev" <vadim@sable.krasnoyarsk.su>
Re: [HACKERS] subquery syntax broken

I believe it has to do with the fact that '=' has right precedence. 
Thomas, can you comment.  Maybe we need to %right 'Op' at that point so
it doesn't shift too early?

> 
> Bruce Momjian wrote:
> > 
> > > vac=> select * from x where y = (select max(y) from x);
> > > ERROR:  parser: parse error at or near "select"
> > > vac=> select * from x where y <> (select max(y) from x);
> > > ERROR:  parser: parse error at or near "select"
> > 
> > Sorry I missed that most obvious option.  Here is the patch.  I will
> > apply it today.
> 
> It doesn't work for =, >, etc:
> 
> vac=> select * from x where y = (select max(y) from x);
> ERROR:  parser: parse error at or near "select"
> 
> but work for others:
> 
> vac=> select * from x where y @ (select max(y) from x);
> ERROR:  There is no operator '@' for types 'int4' and 'int4'
>         You will either have to retype this query using an explicit cast,
>         or you will have to define the operator using CREATE OPERATOR
> 
> Also:
> 
> > +               | a_expr Op '(' SubSelect ')'
> > +                               {
> > +                                       SubLink *n = makeNode(SubLink);
> > +                                       n->lefthand = lcons($1, NULL);
> > +                                       n->oper = lcons($2,NIL);
> > +                                       n->useor = false;
> > +                                       n->subLinkType = ALL_SUBLINK;
>                                                            ^^^^^^^^^^^
>                    should be EXPR_SUBLINK
> > +                                       n->subselect = $4;
> > +                                       $$ = (Node *)n;
> > +                               }
> 
> Vadim
> 


-- 
Bruce Momjian
maillist@candle.pha.pa.us

Mon, 2 Feb 1998 22:17:41 -0500 (EST)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] subquery syntax broken
> I believe it has to do with the fact that '=' has right precedence.
> Thomas, can you comment.  Maybe we need to %right 'Op' at that point so
> it doesn't shift too early?

No, the single-character operators each need their own code in the parser.
Check near line 2980 in the parser for examples from the "a_expr" syntax. You
just need to replicate the subselect "Op" definition blocks and substitute
each of '=', '<', and '>' in the copies. The existing "Op" code handles the
multi-character operators such as '<=' and '>='...

                                              - Tom

> > It doesn't work for =, >, etc:
> >
> > vac=> select * from x where y = (select max(y) from x);
> > ERROR:  parser: parse error at or near "select"
> >
> > but work for others:
> >
> > vac=> select * from x where y @ (select max(y) from x);
> > ERROR:  There is no operator '@' for types 'int4' and 'int4'
> >         You will either have to retype this query using an explicit cast,
> >         or you will have to define the operator using CREATE OPERATOR
> >
> > Also:
> >
> > > +               | a_expr Op '(' SubSelect ')'
> > > +                               {
> > > +                                       SubLink *n = makeNode(SubLink);
> > > +                                       n->lefthand = lcons($1, NULL);
> > > +                                       n->oper = lcons($2,NIL);
> > > +                                       n->useor = false;
> > > +                                       n->subLinkType = ALL_SUBLINK;
> >                                                            ^^^^^^^^^^^
> >                    should be EXPR_SUBLINK
> > > +                                       n->subselect = $4;
> > > +                                       $$ = (Node *)n;
> > > +                               }
> >
> > Vadim
> >
>
> --
> Bruce Momjian
> maillist@candle.pha.pa.us




Tue, 03 Feb 1998 08:18:37 +0000
"Thomas G. Lockhart" <lockhart@alumni.caltech.edu>
Re: [HACKERS] subquery syntax broken
Thomas G. Lockhart wrote:
> 
> > I believe it has to do with the fact that '=' has right precedence.
> > Thomas, can you comment.  Maybe we need to %right 'Op' at that point so
> > it doesn't shift too early?
> 
> No, the single-character operators each need their own code in the parser.
> Check near line 2980 in the parser for examples from the "a_expr" syntax. You
> just need to replicate the subselect "Op" definition blocks and substitute
> each of '=', '<', and '>' in the copies. The existing "Op" code handles the

Don't forget about ALL/ANY modifiers, too...

> multi-character operators such as '<=' and '>='...

Vadim

Tue, 03 Feb 1998 15:34:58 +0700
"Vadim B. Mikheev" <vadim@sable.krasnoyarsk.su>
Re: [HACKERS] subquery syntax broken
Done.  Patch applied.  Makes the grammar bigger, though.

> 
> > I believe it has to do with the fact that '=' has right precedence.
> > Thomas, can you comment.  Maybe we need to %right 'Op' at that point so
> > it doesn't shift too early?
> 
> No, the single-character operators each need their own code in the parser.
> Check near line 2980 in the parser for examples from the "a_expr" syntax. You
> just need to replicate the subselect "Op" definition blocks and substitute
> each of '=', '<', and '>' in the copies. The existing "Op" code handles the
> multi-character operators such as '<=' and '>='...
> 
>                                               - Tom
> 
> > > It doesn't work for =, >, etc:
> > >
> > > vac=> select * from x where y = (select max(y) from x);
> > > ERROR:  parser: parse error at or near "select"
> > >
> > > but work for others:
> > >
> > > vac=> select * from x where y @ (select max(y) from x);
> > > ERROR:  There is no operator '@' for types 'int4' and 'int4'
> > >         You will either have to retype this query using an explicit cast,
> > >         or you will have to define the operator using CREATE OPERATOR
> > >
> > > Also:
> > >
> > > > +               | a_expr Op '(' SubSelect ')'
> > > > +                               {
> > > > +                                       SubLink *n = makeNode(SubLink);
> > > > +                                       n->lefthand = lcons($1, NULL);
> > > > +                                       n->oper = lcons($2,NIL);
> > > > +                                       n->useor = false;
> > > > +                                       n->subLinkType = ALL_SUBLINK;
> > >                                                            ^^^^^^^^^^^
> > >                    should be EXPR_SUBLINK
> > > > +                                       n->subselect = $4;
> > > > +                                       $$ = (Node *)n;
> > > > +                               }
> > >
> > > Vadim
> > >
> >
> > --
> > Bruce Momjian
> > maillist@candle.pha.pa.us
> 
> 
> 
> 


-- 
Bruce Momjian
maillist@candle.pha.pa.us

Tue, 3 Feb 1998 14:26:40 -0500 (EST)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] subquery syntax broken