Thread
-
subquery syntax broken
Vadim B. Mikheev <vadim@sable.krasnoyarsk.su> — 1998-02-02T10:54:01Z
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
-
Re: [HACKERS] subquery syntax broken
Thomas Lockhart <lockhart@alumni.caltech.edu> — 1998-02-02T15:41:22Z
> 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 -
Re: [HACKERS] subquery syntax broken
Bruce Momjian <maillist@candle.pha.pa.us> — 1998-02-02T17:00:14Z
> 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 -
Re: [HACKERS] subquery syntax broken
Bruce Momjian <maillist@candle.pha.pa.us> — 1998-02-02T17:12:01Z
> > > 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
-
Re: [HACKERS] subquery syntax broken
Thomas Lockhart <lockhart@alumni.caltech.edu> — 1998-02-02T17:53:35Z
> 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 -
Re: [HACKERS] subquery syntax broken
Bruce Momjian <maillist@candle.pha.pa.us> — 1998-02-02T18:29:33Z
> > > 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
-
Re: [HACKERS] subquery syntax broken
ocie@paracel.com — 1998-02-02T18:52:10Z
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
-
Re: [HACKERS] subquery syntax broken
Vadim B. Mikheev <vadim@sable.krasnoyarsk.su> — 1998-02-03T02:50:17Z
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 -
Re: [HACKERS] subquery syntax broken
Bruce Momjian <maillist@candle.pha.pa.us> — 1998-02-03T03:17:41Z
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 -
Re: [HACKERS] subquery syntax broken
Thomas Lockhart <lockhart@alumni.caltech.edu> — 1998-02-03T08:18:37Z
> 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 -
Re: [HACKERS] subquery syntax broken
Vadim B. Mikheev <vadim@sable.krasnoyarsk.su> — 1998-02-03T08:34:58Z
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
-
Re: [HACKERS] subquery syntax broken
Bruce Momjian <maillist@candle.pha.pa.us> — 1998-02-03T19:26:40Z
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