út 25. 2. 2020 v 22:14 odesílatel Tom Lane <tgl@sss.pgh.pa.us> napsal:
Paul Jungwirth <pj@illuminatedcomputing.com> writes: > Not that this is necessarily fatal, but you'd need to avoid parsing > trouble with the other EXCEPT, e.g. > SELECT 1 EXCEPT SELECT 1;
Yeah, it doesn't sound like much consideration has been given to that ambiguity, but it's a big problem if you want to use a syntax like this.
> Google Big Query was mentioned upthread. I see they require parens, e.g. > SELECT ... EXCEPT (...). I don't think that actually fixes the ambiguity > though.
Indeed it doesn't, because you can parenthesize an EXCEPT's sub-queries:
In principle, once you got to the SELECT keyword you could tell things apart, but I'm afraid that might be too late for a Bison-based parser.
> So it seems they require at least one `*` in the SELECT target list. In > fact the `*` must be the very last thing. Personally I think it should > be as general as possible and work even without a `*` (let alone caring > about its position).
I wonder if they aren't thinking of the EXCEPT as annotating the '*' rather than the whole SELECT list. That seems potentially more flexible, not less so. Consider
This doesn't have any problem with ambiguity if t2 has a "foo" column, or if t1 has a "baz" column; which indeed would be cases where this sort of ability would be pretty useful, since otherwise you end up with painful-to-rename duplicate output column names. And certainly there is no particular need for this construct if you didn't write a "*".