Re: COUNT(*) to find records which have a certain number of dependencies

Mischa Sandberg <ischamay(dot)andbergsay(at)activestateway(dot)com> writes:

> Hope you don't mind an opinion from someone who looks at this from the
> underside ...
>
> Trying to give the parser a better chance of confusing you?

Ok, I understand the basic idea that a parser muddles along too long before
reporting an error makes it harder to track down the original error. However
this isn't such a case. I can't think of any way I could accidentally
introduce an extra comma that would lead to a valid looking alias and an error
much further along. You're talking about something like:

select * from a, as x

which would still produce a syntax error directly after the erroneous comma.
Which is right where you want the error to happen.

> Having the tag only totally insignificant if you want to have a bunch of
> special validation cases, where if there is only ONE anonymous pseudotable,
> and no ambiguity is possible.

There's no additional ambiguity. There can't be since all the user would do to
make the parser happy is go and specify aliases, not necessarily use them. If
the user didn't feel the need to put aliases in initially presumably it was
because he wasn't using them because he didn't need them.

Can you really say queries like this are dangerous:

select a_id,b_id
from (select a_id from a where xyz=?),
(select b_id from b where xyz=?)

or queries like

select a_id,b_id
from (select a_id from a where xyz=?) as x,
(select b_id from b where xyz=?) as x

are any clearer or less ambiguous?

> If all it does is give you an annoying but understandable error message

The reason it's annoying is because the database is saying, "I know perfectly
well what you're doing: I parsed the subquery fine, but I'm going to refuse to
run it because you didn't say the magic word." But when I add in "as x" after
every subquery even though it's utterly meaningless to the rest of the query,
then postgres is perfectly happy to cooperate.

> BTW, the "as" is optional, but I always suggest that people use it explicitly.
> Why? Because without it, you get another silly error message or even a runtime
> error when what you did was omit a comma. For example
>
> select salary name from Employee

I understand what you mean but I don't understand how always using the "as"
helps you here. You'll still get this error even if you were always using AS
when you intended. There's no option to make AS mandatory.

Incidentally, I also always use AS in both column and table aliases (when
specifying them) but for a different reason. I could never keep straight which
of Oracle and MSSQL required AS for columns and which required it for tables.
IIRC they're exactly reversed. However as best as I recall they both allowed
subqueries without any aliases at all.

--
greg