út 25. 2. 2020 v 22:14 odesílatel Tom Lane <t...@sss.pgh.pa.us> napsal:
> Paul Jungwirth <p...@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: > > regression=# select 1 except (select 2); > ?column? > ---------- > 1 > (1 row) > > 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 > > SELECT t1.* EXCEPT (foo, bar), t2.* EXCEPT (baz) ... FROM t1, t2, ... > > 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 "*". > this proposal looks well Pavel > regards, tom lane > > >
