Re: a proposal for a new functionality: "SELECT * [EXCEPT col1 [,col2]]

On 2/25/20 11:46 AM, Stanislav Motycka wrote:
>
>
> Dňa 25. 2. 2020 o 20:28 Paul A Jungwirth napísal(a):
>> I take the proposal to mean this:
>>
>> SELECT listOfColumns [EXCEPT listOfColumns] FROM ...
> Exactly, simply exclude unneeded columns from the base clause "SELECT",
> nothing more ..

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;

Believe it or not these are valid SQL:

SELECT;
SELECT EXCEPT SELECT;

This fails today but only because of the different number of columns:

SELECT 1 AS SELECT EXCEPT SELECT;

So the parser understands it as selectQuery EXCEPT selectQuery, but you
can see how it could also be parsable as this new structure. So the
parser would have to decide which is meant (if that's even possible at
that early stage).

I guess as soon as you exclude two columns it is unambiguous though
because of this comma: SELECT ... EXCEPT SELECT, .... And anyway I think
for such a pathological case you could just tell people to add double
quotes.

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. Also a few other notes (after very brief testing):

SELECT * EXCEPT (foo) FROM t; -- works
SELECT * EXCEPT (foo, bar) FROM t; -- works
SELECT t.* EXCEPT (foo) FROM t; -- works
SELECT * EXCEPT foo FROM t; -- fails
SELECT foo, bar EXCEPT (foo) FROM t; -- fails
SELECT t1.foo, t2.* EXCEPT (foo) FROM t1 JOIN t2 ON ...; -- works
SELECT t2.*, t1.foo EXCEPT (foo) FROM t1 JOIN t2 ON ...; -- fails!

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).

Regards,

--
Paul ~{:-)
pj(at)illuminatedcomputing(dot)com