I’d have to re-read the SQL standard to figure out whether it is valid or not, but the currently behavior seems harmless so let’s allow it.
My main point is that “select schema.table.column from …” is never a good idea and is often a bad idea. Julian > On Jul 12, 2017, at 3:26 PM, Zain Humayun <[email protected]> > wrote: > > Sorry there was a type in my previous email: > > SqlValidator sees > > select foodmart.store_state from foodmart.foodmart as foodmart > > On Wednesday, July 12, 2017, 3:22:09 PM PDT, Zain Humayun > <[email protected]> wrote: > > Thanks for the clarification. You mentioned that the validator would tell me > my query was invalid, but running the concrete example in calcite: > > select foodmart.foodmart.store_state from foodmart > > does not produce an error. Further investigation reveals that at some point > calcite transformed it into the query: > > select foodmart.foodmart.store_state from foodmart.foodmart - (this is what > SqlValidatorImpl sees). > > Is this a bug or intended behaviour? Perhaps it's because foodmart is > declared as the default schema in the model definition. I tested another > query without a model definition: > > > select adhoc.table.column from table > > The same thing happens, at some point "from table" becomes "from > adhoc.table". Also, i'm still wondering why the column name would end up in > the prefix list rather than the suffix list (from my previous email). I'm > using the scope returned from getSelectScope(...), if that matters. > > Zain. > > > On Wednesday, July 12, 2017, 2:38:59 PM PDT, Julian Hyde <[email protected]> > wrote: > > Almost always, the “prefix” part is almost always the table alias, and > therefore has length 1. > > The exception is that if you use a qualified name schema.table or > catalog.schema.table in the FROM clause you are allowed to use schema.table > or catalog.schema.table as the qualifier, and therefore the prefix will have > length 2 or 3. > > In your case, > > select "schema"."table"."column" from “table" > > you are not allowed to qualify with “schema”.”table” because you only wrote > “table”, not “schema”.’table” in the FROM clause. So, the validator is just > about to tell you that your query is invalid. Which it is. > > Frankly I think it’s a dumb idea to qualify column names with with > schema.table or catalog.schema.table and I wish people (and tools) didn’t do > it. We have to support it because it is standard. > > Julian > > > >> On Jul 12, 2017, at 12:08 PM, Zain Humayun <[email protected]> >> wrote: >> >> Hi, >> >> I need find the fully qualified name of a SqlIdentifier in SqlValidatorImpl, >> and I was wondering why in a query of the form >> >> select "schema"."table"."column" from "table" >> >> calling scope.fullyQualify() on the SqlIdentifier "schema"."table"."column" >> would produce a prefix list of ["table", "column"] and a suffix list of [ ]. >> In contrast calling fullyQualify in the query >> >> select "table"."column" from "table" >> >> will produce a prefix list of ["table"] and a suffix list of ["column"]. I'm >> wondering what the prefix and suffix list actually stand for, and why fully >> qualifying with the schema in the identifier causes the above behavior. >> >> Thanks,Zain.
