There’s already an issue logged for this: https://issues.apache.org/jira/browse/CALCITE-4362 <https://issues.apache.org/jira/browse/CALCITE-4362>. I commented on that case with a sketch of how to approach it.
As I understand it, these columns do “belong to the table” in some sense. If you are joining tables T1 and T2, they each independently have their own _PARTITIONDATE column, which you would write T1._PARTITIONDATE and T2._PARTITIONDATE. So they are more like Oracle’s ROWID than, say, the CURRENT_TIMESTAMP function which is global to the query. Let’s discuss further in the JIRA case. > On Oct 20, 2021, at 11:25 AM, Mark Grey <[email protected]> > wrote: > > Hello Calcite community, > > First of all, many thanks to the many contributors for this awesome and > very useful project! > > I had a question with regard to amending the behavior of the SqlValidator > to account for the existence in some dialects of SQL of pseudocolumns. > Examples of this sort of construct exist in BigQuery, where they are used > as a mechanism for sharding > <https://cloud.google.com/bigquery/docs/querying-wildcard-tables#filtering_selected_tables_using_table_suffix> > or partitioning > <https://cloud.google.com/bigquery/docs/querying-wildcard-tables#scanning_a_range_of_ingestion-time_partitioned_tables_using_partitiontime>. > They are essentially column identifiers that do some special operations > within the query without belonging to a specific table within the schema. > They can legally appear in projection, filtering and grouping clauses as > normal columns would, but are not members of the tables referenced within > the FROM clause. > > Is there possibly a way to extend the SqlValidator to pass these cases and > treat them as relations that exist at the schema level? Or alternatively, > to treat them as literals? Our use case is such that we'd need the > validator to pass queries that include them, but not necessarily extract > them into an underlying relational algebra expression. > > Would appreciate any pointers on classes to look at for inspiration on a > possible approach. > > Thanks! > > Mark
