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 OPERATORAlso:
+ | 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
Show quoted text
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 OPERATORAlso:
+ | 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 OPERATORAlso:
+ | 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