Re: Peter Eisentraut > => select * exclude (foo) from t1, t2; > ERROR: 02000: SELECT list is empty after excluding all columns > > My paper proposes that this should be an error because foo is ambiguous.
Consider this example: create table t1(id int, data text, more_data text); create table t2(id int, other_data text, different_data text); Now if you wanted just the data without the surrogate keys, you would want to say: select * exclude (id) from t1 join t2 on t1.id = t2.id; Having to specify (t1.id, t2.id) would make it cumbersome to use, especially considering "exclude" would mostly be useful for interactive use. > You also need to be careful with column privileges. For example: > > create table t5 (a int, b int, c int); > grant select (a) on table t5 to user2; > -- as user2 > select * exclude (b, c) from t5; > > At least under the SQL standard security model, this should be an error, > because you need to check the privileges of b and c. This is because you > shouldn't be able to use this feature to probe for the existence of columns > that you otherwise don't have access to. I would actually argue the exact other way round. If you have access to a table except some column (like everything in a users table except for the password), you would want to be able to write select * exclude (password) from users; This is a very natural way to use the feature. If referencing "password" was forbidden, it would exactly defeat the reason for using EXCLUDE here. Christoph
