Looking at other databases I see
Microsoft SQL Server - string literals take on default collation of database
MySQL - string literals take on default collation of connection
Postgres - only supports single collation per database (????)
Any others? Oracle? (I couldn't find mention of collation in Oracle 10g's character set section)
One issue with the per schema approach is statement caching. Currently statement caching at a per-schema approach so there's no problem.
However for statements that don't depend on the current schema it would be good to cache them across schemas, e.g.
SELECT * FROM A.T
Especially when the default schema for a user is specific to that user.
With string literals taking information from the current schema, now a statement like:
SELECT * FROM A.T WHERE TYPE = 'CAR'
will be dependent on the current schema, thus not shareable (due to the collation for 'CAR' requring a lookup of the current schema)
In fact thinking about it, it does look strange that such a statement is dependent on the collation of the current schema, I'm not sure that's what an application developer will be expecting when they write a statement like that (principle of least surprise).
I'm not sure what's right here, just trying to expand the discussion so all angles have been looked at.
In some ways it would seem useful in TYPE = 'CAR' for 'CAR' to take on the collation of TYPE, but it's clear in the SQL standard that both have implict collation derivation (even though we can't work out what the collationtype of 'CAR' is defined to be).