Обсуждение: subquery syntax broken
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
> 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
> 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
>
> > 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
> 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
> > > 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
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
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
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
> 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
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
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