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@illuminatedcomputing.com