Thread: "AS" by the syntax of table reference.(8.4 proposal)
Hi all. I thinks to the option of "AS" by the syntax of table reference. The problem on structure had restricted it until now. Then, conversion was required of this by the degree of migration from other DBMS's. It was irritated a little. I understood that it was an option in SQL2003 and SQL99. -- <SQL2003> 7.6 <table reference> (p303) <table or query name> [ [ AS ] <correlation name> -- I propose this. However, postfix operators is troublesome. Therefore, This is the inclusion with restriction. I think that it is a trifling problem. But, The past compatibility is worried very much. Then, many developers' clear eye is required for it. However, I need this patch very much. <about regression error of the after patch> This is as a result of after patch apply of CVS-HEAD regression. The cause changed the error position of a grammar interpretation. -- plpgsql ... FAILED ========================1 of 114 tests failed. ======================== *** ./expected/plpgsql.out Wed Jul 25 13:19:09 2007 --- ./results/plpgsql.out Fri Jan 25 14:29:34 2008 *************** *** 2337,2343 **** end loop; return 5; end;$$ language plpgsql; ! ERROR: syntax error at or near "fought" LINE 1: select I fought the law, the law won ^ QUERY: selectI fought the law, the law won --- 2337,2343 ---- end loop; return 5; end;$$ language plpgsql; ! ERROR: syntax error at or near "the" LINE 1: select I fought the law, the law won ^ QUERY: select I fought the law, the law won ====================================================================== <about postfix operator problem> There is syntax which is not passed. However, It is necessary to make it c_expr expression. Probably, it is avoided by cautions of a document. -- regression=# SELECT aa AS "X" FROM a_star LIMIT 1;X ---1 (1 row) regression=# SELECT aa ! AS "X" FROM a_star LIMIT 1;X ---1 (1 row) regression=# SELECT aa ! "X" FROM a_star LIMIT 1; ERROR: column "X" does not exist LINE 1: SELECT aa ! "X" FROM a_star LIMIT 1; ^ regression=# SELECT (aa !) "X" FROM a_star LIMIT 1;X ---1 (1 row) -- BTW, this is the syntax which ORACLE and SQLServer don't accept. It may be the wonderfulness of PostgreSQL. However, Is there any situation using the function? But, I can't do assertion. In conclusion, Therefore, this will be left to vote. Regards, Hiroshi Saito
"Hiroshi Saito" <z-saito@guitar.ocn.ne.jp> writes:
> *** src/backend/parser/gram.y 4 Feb 2008 06:35:46 -0000 1.1
> --- src/backend/parser/gram.y 4 Feb 2008 17:24:19 -0000 1.2
> ***************
> *** 8320,8325 ****
> --- 8320,8333 ----
> $$->val = (Node *) $1;
> $$->location = @1;
> }
> + | c_expr IDENT
> + {
> + $$ = makeNode(ResTarget);
> + $$->name = $2;
> + $$->indirection = NULL;
> + $$->val = (Node *) $1;
> + $$->location = @1;
> + }
> | a_expr
> {
> $$ = makeNode(ResTarget);
Hmm, since c_expr is so restrictive, is that really going to satisfy
anybody who expects to be able to omit AS? With the number of
unreserved keywords we have, the restriction to IDENT for the label
seems like it's going to be a constant source of gotchas as well. Both
of these limitations are contrary to the SQL spec, too --- SQL99 says
that a SELECT-list element is
<derived column> ::= <value expression> [ <as clause> ]
<as clause> ::= [ AS ] <column name>
so we really need to support at least ColId as the allowed set of
column alias names. (I tried changing the patch to do that, but
got a lot of shift/reduce conflicts, some of which are maybe fixable
but some seem hard to fix.)
I think that simply saying "it's not supported" is going to be easier to
deal with than people trying to use it and complaining that this or that
case doesn't work.
regards, tom lane
Hi. ----- Original Message ----- From: "Tom Lane" <tgl@sss.pgh.pa.us> > Hmm, since c_expr is so restrictive, is that really going to satisfy > anybody who expects to be able to omit AS? With the number of > unreserved keywords we have, the restriction to IDENT for the label > seems like it's going to be a constant source of gotchas as well. Both > of these limitations are contrary to the SQL spec, too --- SQL99 says > that a SELECT-list element is > > <derived column> ::= > <value expression> [ <as clause> ] > > <as clause> ::= [ AS ] <column name> Ahhh yes.... > > so we really need to support at least ColId as the allowed set of > column alias names. (I tried changing the patch to do that, but > got a lot of shift/reduce conflicts, some of which are maybe fixable > but some seem hard to fix.) > > I think that simply saying "it's not supported" is going to be easier to > deal with than people trying to use it and complaining that this or that > case doesn't work. Um, I think that it is supported by "AS." Then, How to use as before is allowed. I think that I do not see a complaint to it. Although what is told clearly is desired, I think that there are many people needed. Therefore, it is believed that this needs to hear many opinions. I'm violently required. I'm looking at such realization. http://winpg.jp/~saito/psqlODBC/SQLServer_linkserver_PostgreSQL2.png PostgreSQL works hard in a fit place. !! And a system will be operated in cooperation. :-) Regards, Hiroshi Saito
Oops, and, >> so we really need to support at least ColId as the allowed set of >> column alias names. (I tried changing the patch to do that, but >> got a lot of shift/reduce conflicts, some of which are maybe fixable >> but some seem hard to fix.) Since capability was insufficient, I spent several times as many time as this. It understands the very hard thing. Hardship had left traces upon this features. I want me to still inquire. Regards, Hiroshi Saito
"Hiroshi Saito" <z-saito@guitar.ocn.ne.jp> writes:
> Oops, and,
>>> so we really need to support at least ColId as the allowed set of
>>> column alias names. (I tried changing the patch to do that, but
>>> got a lot of shift/reduce conflicts, some of which are maybe fixable
>>> but some seem hard to fix.)
> Since capability was insufficient, I spent several times as many time as this.
> It understands the very hard thing. Hardship had left traces upon this features.
> I want me to still inquire.
The case that I couldn't see a good way to fix was the shift/reduce
conflicts here:
state 1414
1418 AexprConst: ConstInterval Sconst . opt_interval
DAY_P shift, and go to state 1680 HOUR_P shift, and go to state 1681 MINUTE_P shift, and go to state
1682 MONTH_P shift, and go to state 1683 SECOND_P shift, and go to state 1684 YEAR_P shift, and go to state
1685
DAY_P [reduce using rule 1149 (opt_interval)] HOUR_P [reduce using rule 1149 (opt_interval)] MINUTE_P
[reduceusing rule 1149 (opt_interval)] MONTH_P [reduce using rule 1149 (opt_interval)] SECOND_P [reduce using
rule1149 (opt_interval)] YEAR_P [reduce using rule 1149 (opt_interval)] $default reduce using rule 1149
(opt_interval)
What this is pointing out is that without AS, this statement is actually
ambiguous:
SELECT INTERVAL '1 year' YEAR;
Is "YEAR" meant to be a column alias or a qualifier for the interval
constant?
AFAICS, the only way to resolve that would be to make YEAR, as well as
the other interval qualifier words (MONTH etc), not be allowed as a
ColId ... which is per SQL spec but I confidently predict howls of
anguish from our users if we do it. I imagine there are more than
a few tables out there with columns named "month", for instance.
I guess plan B could be to rip out the special interval-constant syntax,
which we have never really implemented anyway. There isn't any
functional reason to implement it, it'd just be for spec compliance,
and you could certainly argue that supporting no-AS is more interesting
than supporting interval-constant qualifiers.
The other conflicts I saw could be resolved by making a small number
of other keywords like CHARACTER and VARYING reserved (or more reserved
than they are now anyway). These seemed like they'd be less of a
problem to reserve than the interval qualifier words.
However that still leaves us with the problem that c_expr isn't flexible
enough to make this spec-compliant. AFAICS the only way to fix that is
to give up postfix operators. IMHO, an actual loss of functionality is
too high a price to pay for being able to omit AS.
regards, tom lane
"Hiroshi Saito" <z-saito@guitar.ocn.ne.jp> writes:
> Oops, and,
>>> so we really need to support at least ColId as the allowed set of
>>> column alias names. (I tried changing the patch to do that, but
>>> got a lot of shift/reduce conflicts, some of which are maybe fixable
>>> but some seem hard to fix.)
I think supporting only IDENT wouldn't be all that much of a loss.
But yeah, c_expr isn't enough. We really need {a,b}_expr sans postfix
expressions. I tried to create something like that earlier and found my bison
grammar foo a bit rusty.
Hm. I wonder if we could do a hack where we parse the a_expr ColId as a
regular a_expr and then check for that in the target_el rule or in parse
analysis and pull the ColId out.
-- Gregory Stark EnterpriseDB http://www.enterprisedb.com Get trained by Bruce Momjian - ask me about
EnterpriseDB'sPostgreSQL training!
Gregory Stark <stark@enterprisedb.com> writes:
> But yeah, c_expr isn't enough. We really need {a,b}_expr sans postfix
> expressions.
How's that going to help? As long as postfix operators exist at all,
SELECT a + b, ...
is going to be ambiguous, and no amount of grammar magic changes that.
We could force the parser into using one interpretation or the other,
but it would still be wrong for some folks.
regards, tom lane
Sorry, I'm sleeping.
Thanks Gregory-san. and, Tom-san.
> Gregory Stark <stark@enterprisedb.com> writes:
>> But yeah, c_expr isn't enough. We really need {a,b}_expr sans postfix
>> expressions.
>
> How's that going to help? As long as postfix operators exist at all,
>
> SELECT a + b, ...
>
> is going to be ambiguous, and no amount of grammar magic changes that.
> We could force the parser into using one interpretation or the other,
> but it would still be wrong for some folks.
Ah yes, {a,b}_expr is very difficult.....Then, I am asking for compromise.
at the "c_expr", case after the patch..
postgres=# select a+b from n;?column?
---------- 5
(1 row)
postgres=# select a+b AS "FIELD" from n;FIELD
------- 5
(1 row)
postgres=# select a+b "FIELD" from n;
ERROR: syntax error at or near ""FIELD""
LINE 1: select a+b "FIELD" from n; ^
postgres=# select (a+b) "FIELD" from n;FIELD
------- 5
(1 row)
----- Original Message ----- From: "Tom Lane" <tgl@sss.pgh.pa.us> > "Hiroshi Saito" <z-saito@guitar.ocn.ne.jp> writes: >> Oops, and, >>>> so we really need to support at least ColId as the allowed set of >>>> column alias names. (I tried changing the patch to do that, but >>>> got a lot of shift/reduce conflicts, some of which are maybe fixable >>>> but some seem hard to fix.) > >> Since capability was insufficient, I spent several times as many time as this. >> It understands the very hard thing. Hardship had left traces upon this features. >> I want me to still inquire. > > The case that I couldn't see a good way to fix was the shift/reduce > conflicts here: Eh?, c_expr IDENT is no conflicts....?_? > > state 1414 > > 1418 AexprConst: ConstInterval Sconst . opt_interval > > DAY_P shift, and go to state 1680 > HOUR_P shift, and go to state 1681 > MINUTE_P shift, and go to state 1682 > MONTH_P shift, and go to state 1683 > SECOND_P shift, and go to state 1684 > YEAR_P shift, and go to state 1685 > > DAY_P [reduce using rule 1149 (opt_interval)] > HOUR_P [reduce using rule 1149 (opt_interval)] > MINUTE_P [reduce using rule 1149 (opt_interval)] > MONTH_P [reduce using rule 1149 (opt_interval)] > SECOND_P [reduce using rule 1149 (opt_interval)] > YEAR_P [reduce using rule 1149 (opt_interval)] > $default reduce using rule 1149 (opt_interval) > > What this is pointing out is that without AS, this statement is actually > ambiguous: > > SELECT INTERVAL '1 year' YEAR; > > Is "YEAR" meant to be a column alias or a qualifier for the interval > constant? postgres=# SELECT INTERVAL '1 year' YEAR;interval ----------1 year (1 row) Sorry, please let me read the following sentences later. > > AFAICS, the only way to resolve that would be to make YEAR, as well as > the other interval qualifier words (MONTH etc), not be allowed as a > ColId ... which is per SQL spec but I confidently predict howls of > anguish from our users if we do it. I imagine there are more than > a few tables out there with columns named "month", for instance. > > I guess plan B could be to rip out the special interval-constant syntax, > which we have never really implemented anyway. There isn't any > functional reason to implement it, it'd just be for spec compliance, > and you could certainly argue that supporting no-AS is more interesting > than supporting interval-constant qualifiers. > > The other conflicts I saw could be resolved by making a small number > of other keywords like CHARACTER and VARYING reserved (or more reserved > than they are now anyway). These seemed like they'd be less of a > problem to reserve than the interval qualifier words. > > However that still leaves us with the problem that c_expr isn't flexible > enough to make this spec-compliant. AFAICS the only way to fix that is > to give up postfix operators. IMHO, an actual loss of functionality is > too high a price to pay for being able to omit AS. > > regards, tom lane
Hiroshi Saito wrote:
> Sorry, I'm sleeping.
> Thanks Gregory-san. and, Tom-san.
>
>> Gregory Stark <stark@enterprisedb.com> writes:
>>> But yeah, c_expr isn't enough. We really need {a,b}_expr sans postfix
>>> expressions.
>>
>> How's that going to help? As long as postfix operators exist at all,
>>
>> SELECT a + b, ...
>>
>> is going to be ambiguous, and no amount of grammar magic changes that.
>> We could force the parser into using one interpretation or the other,
>> but it would still be wrong for some folks.
>
> Ah yes, {a,b}_expr is very difficult.....Then, I am asking for
> compromise.
> at the "c_expr", case after the patch..
> postgres=# select a+b from n;
> ?column?
> ----------
> 5
> (1 row)
>
> postgres=# select a+b AS "FIELD" from n;
> FIELD
> -------
> 5
> (1 row)
>
> postgres=# select a+b "FIELD" from n;
> ERROR: syntax error at or near ""FIELD""
> LINE 1: select a+b "FIELD" from n;
> ^
> postgres=# select (a+b) "FIELD" from n;
> FIELD
> -------
> 5
> (1 row)
>
>
How the heck are we going to document this reasonably? It strikes me as
horribly complicated and puzzling for users. The current rule might be
an annoyance in porting applications, but it has the advantage of
simplicity. If we can somehow make AS optional across the board, then
well and good, otherwise I am very dubious about making any change.
cheers
andrew
Hi Andrew-san. ----- Original Message ----- From: "Andrew Dunstan" <andrew@dunslane.net> > > How the heck are we going to document this reasonably? It strikes me as > horribly complicated and puzzling for users. The current rule might be > an annoyance in porting applications, but it has the advantage of > simplicity. If we can somehow make AS optional across the board, then > well and good, otherwise I am very dubious about making any change. Ah yes, user's confusion wants to consider a good method so that you may consider, in order to make it avoid. good proposal is desired. P.S) I go out and am no answering for a while. sorry..
"Tom Lane" <tgl@sss.pgh.pa.us> writes:
> Gregory Stark <stark@enterprisedb.com> writes:
>> But yeah, c_expr isn't enough. We really need {a,b}_expr sans postfix
>> expressions.
>
> How's that going to help? As long as postfix operators exist at all,
>
> SELECT a + b, ...
>
> is going to be ambiguous, and no amount of grammar magic changes that.
> We could force the parser into using one interpretation or the other,
> but it would still be wrong for some folks.
Sure, just like a + + b is ambiguous. We define an arbitrary choice and tell
people to put parentheses if they want the other. It's not too hard to write
SELECT (a +) b, ...
if you want an alias. Besides, nobody uses postfix expressions anyways. It
would be a pain if it worked the other way and you had to write (a + b) all
the time.
-- Gregory Stark EnterpriseDB http://www.enterprisedb.com Get trained by Bruce Momjian - ask me about
EnterpriseDB'sPostgreSQL training!
Gregory Stark <stark@enterprisedb.com> writes:
> Sure, just like a + + b is ambiguous. We define an arbitrary choice and tell
> people to put parentheses if they want the other. It's not too hard to write
> SELECT (a +) b, ...
> if you want an alias. Besides, nobody uses postfix expressions anyways. It
> would be a pain if it worked the other way and you had to write (a + b) all
> the time.
Hm, well, now that you mention it we already have provisions to
discriminate against the postfix-op case when things are ambiguous.
So really this is a precedence problem, which leads to the attached
proposal for a patch. This still has the problem of only allowing
IDENT for AS-less column labels, but at least it avoids restricting
the expression.
regards, tom lane
Index: gram.y
===================================================================
RCS file: /cvsroot/pgsql/src/backend/parser/gram.y,v
retrieving revision 2.606
diff -c -r2.606 gram.y
*** gram.y 7 Feb 2008 21:07:55 -0000 2.606
--- gram.y 9 Feb 2008 20:01:36 -0000
***************
*** 477,482 ****
--- 477,483 ---- %nonassoc BETWEEN %nonassoc IN_P %left POSTFIXOP /* dummy for postfix Op rules */
+ %nonassoc IDENT /* to support target_el without AS */ %left Op OPERATOR /*
multi-characterops and user-defined operators */ %nonassoc NOTNULL %nonassoc ISNULL
***************
*** 8705,8711 **** | target_list ',' target_el { $$ = lappend($1, $3); } ;
- /* AS is not optional because shift/red conflict with unary ops */ target_el: a_expr AS ColLabel {
$$ = makeNode(ResTarget);
--- 8706,8711 ----
***************
*** 8714,8719 ****
--- 8714,8735 ---- $$->val = (Node *)$1; $$->location = @1; }
+ /*
+ * We support omitting AS only for column labels that aren't
+ * any known keyword. There is an ambiguity against postfix
+ * operators: is "a ! b" an infix expression, or a postfix
+ * expression and a column label? We prefer to resolve this
+ * as an infix expression, which we accomplish by assigning
+ * IDENT a precedence higher than POSTFIXOP.
+ */
+ | a_expr IDENT
+ {
+ $$ = makeNode(ResTarget);
+ $$->name = $2;
+ $$->indirection = NIL;
+ $$->val = (Node *)$1;
+ $$->location = @1;
+ } | a_expr { $$ = makeNode(ResTarget);
"Tom Lane" <tgl@sss.pgh.pa.us> writes: > Gregory Stark <stark@enterprisedb.com> writes: >> Sure, just like a + + b is ambiguous. We define an arbitrary choice and tell >> people to put parentheses if they want the other. It's not too hard to write >> SELECT (a +) b, ... >> if you want an alias. Besides, nobody uses postfix expressions anyways. It >> would be a pain if it worked the other way and you had to write (a + b) all >> the time. > > Hm, well, now that you mention it we already have provisions to > discriminate against the postfix-op case when things are ambiguous. > So really this is a precedence problem, which leads to the attached > proposal for a patch. This still has the problem of only allowing > IDENT for AS-less column labels, but at least it avoids restricting > the expression. Well as I said before, I'm for including it even if it only works for IDENT. I think that's good enough to be worth including. Of course it would still be better if we could get it closer to ColId. YEAR MONTH DAY HOUR MINUTE SECOND the only problem spots? How much else had to change to work around other conflicts? -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's Slony Replication support!
Gregory Stark <stark@enterprisedb.com> writes:
> Of course it would still be better if we could get it closer to ColId. YEAR
> MONTH DAY HOUR MINUTE SECOND the only problem spots? How much else had to
> change to work around other conflicts?
No, to make it ColId we'd also have to reserve all of these:CHAR_PCHARACTERDOUBLE_PVARYINGWITHWITHOUT
and even that doesn't seem to get us quite out of the woods, as there's
still a reduce/reduce conflict here:
state 2523
1118 character: CHARACTER . opt_varying 1744 reserved_keyword: CHARACTER .
VARYING shift, and go to state 1350
')' reduce using rule 1125 (opt_varying) ')' [reduce using rule 1744 (reserved_keyword)] ','
reduceusing rule 1125 (opt_varying) ',' [reduce using rule 1744 (reserved_keyword)] $default reduce using
rule1125 (opt_varying)
opt_varying go to state 1356
which I'm not sure how to get rid of.
A possibly bigger problem is that the solution for postfix ops doesn't
scale nicely: we'd have to list not only IDENT, but *every* can-be-ColId
keyword, in the %precedence list, which (a) is a maintenance headache,
(b) causes a conflict because some are already listed there with the
wrong precedence for this purpose, and (c) is very scary from the
viewpoint of possibly silently suppressing warnings of future grammar
ambiguities. I'm not even that happy with giving IDENT a precedence;
giving precedences to 270 or so currently precedence-less tokens
just doesn't sound safe.
regards, tom lane
"Tom Lane" <tgl@sss.pgh.pa.us> writes: > A possibly bigger problem is that the solution for postfix ops doesn't > scale nicely: we'd have to list not only IDENT, but *every* can-be-ColId > keyword, in the %precedence list, which (a) is a maintenance headache, > (b) causes a conflict because some are already listed there with the > wrong precedence for this purpose, and (c) is very scary from the > viewpoint of possibly silently suppressing warnings of future grammar > ambiguities. I'm not even that happy with giving IDENT a precedence; > giving precedences to 270 or so currently precedence-less tokens > just doesn't sound safe. There are other rules that have a %prec on the rule itself, does that not work here? *off to read the fine manual now* -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Get trained by Bruce Momjian - ask me about EnterpriseDB'sPostgreSQL training!
Gregory Stark <stark@enterprisedb.com> writes:
> There are other rules that have a %prec on the rule itself, does that not work
> here? *off to read the fine manual now*
No, the decision is driven by comparing the precedence of the rule to
that of the input token, so you need precedences on both to resolve
an ambiguous situation.
regards, tom lane
Hi. From: "Tom Lane" <tgl@sss.pgh.pa.us> > A possibly bigger problem is that the solution for postfix ops doesn't > scale nicely: we'd have to list not only IDENT, but *every* can-be-ColId > keyword, in the %precedence list, which (a) is a maintenance headache, > (b) causes a conflict because some are already listed there with the > wrong precedence for this purpose, and (c) is very scary from the > viewpoint of possibly silently suppressing warnings of future grammar > ambiguities. I'm not even that happy with giving IDENT a precedence; > giving precedences to 270 or so currently precedence-less tokens > just doesn't sound safe. Yeah, when I began, I tried the method same as a_expr IDENT as you. That is because I thought that it was effective in order to avoid a conflict simply. It is worried that it may cause the problem of next operation by the reason IDENT is not the simple token. Therefore, c_expr IDENT of the method of doing with a basic rule was proposed. However, If it is allowed in the place which you consider, I don't have an objection. Regards, Hiroshi Saito