Hello Hongyu Guo,
Interesting! I’ll definitely explore this as an option. I’m curious though, do
the convertlet tables in RelToSqlConverter have access to enough information to
be able to look at a call to HASH(*) and derive the table names (+ their column
names) corresponding the scope? My understanding is that the context objects
available have access to the validator, but then we’re right back where we
started with the problem of how to access the table/column names within the
validator.
On 2023/08/16 13:27:06 Hongyu Guo wrote:
> Hi Kian Nassre,
> I try to create a HASH function instance to solve this problem
> ```
> public static final SqlFunction HASH =
> new SqlFunction("HASH",
> SqlKind.OTHER_FUNCTION,
> ReturnTypes.BIGINT,
> null,
> OperandTypes.ONE_OR_MORE,
> SqlFunctionCategory.NUMERIC) {
> @Override public SqlSyntax getSyntax() {
> return SqlSyntax.FUNCTION_STAR;
> }
>
> @Override public RelDataType deriveType(
> SqlValidator validator, SqlValidatorScope scope, SqlCall call) {
> return validator.getTypeFactory().createSqlType(
> SqlTypeName.BIGINT);
> }
> };
> ```
> It can pass validation, but it will throw an exception in the
> `RelToSqlConverter`
> stage. I notice `count(*)` is a special case similar to `hash(*)`, so I
> have looked into the
> code for COUNT function and AggConverter, where a trick is used to remove
> the `*` identifier in `count(*)`
> (org/apache/calcite/sql2rel/AggConverter.java:447),
> we cannot use similar implementations in `hash(*)`.
>
> So i think one possible solution could be expanding `*` in `hash(*)` during
> the
> `SqlToRelConverter` stage (a litter complex, you should derive the type of
> `*`
> or `tbl1.*`), or check why `hash(*)` throws an exception in the
> `RelToSqlConverter`
> stage.
>
> I hope this helps :)
>
> Best
> Hongyu Guo
>
> On 2023/08/15 04:31:41 Kian Nassre wrote:
> > Hello,
> >
> > I’m working on a project that utilizes Calcite and I have interest in
> > supporting functions that allow using * to specify all of the existing
> > columns in a function call. For example, one function that I am looking at
> > is the variadic Snowflake HASH function which can accept all columns with
> > HASH(*). Trying to add this function as I would any other function I
> > encounter issues in the validator because it does not recognize * as a
> > valid identifier.
> >
> > My understanding is that the * is essentially syntactic sugar which
> should
> > be expanded in the same way that SELECT * FROM t is expanded early by the
> > validator.
> >
> > I’m wondering if anyone has any advice on the best way to add support for
> > this functionality. I have done some experimentation with replicating the
> > approach that the validator uses for expanding SELECT * FROM T into
> > multiple columns. I do this using a variant of the private method
> > expandStar.
> >
> > The first problem comes when I try to write my variant of expandStar
> > because it seems I need to access the package private method
> getChildNames,
> > or to the .names attribute of the package private class ScopeChild, so I’m
> > not understanding if that’s what I should be doing or if there’s an
> > alternate method. I’m using it because I need some way to access the table
> > names from a scope.
> >
> > The other problem is that to get my version of expandStar called in all
> the
> > places I need it to be called (e.g. inside of function calls, or inside of
> > ORDER BY terms) I think I need to add logic that calls it to the Expander
> > classes or its subclasses. My initial idea was to subclass all of the
> > expander classes and have my subclasses call my expandStar method, then
> > have all methods that use the expanders instead use my subclasses. This
> > would require changing the classes from private/package-private to
> > protected, which makes me wonder if I'm barking up the wrong tree.
> >
> > The approach seems promising, but I’m not sure if I’m headed in the right
> > direction?
> >
> > Sincerely,
> > - Kian Nassre
> >
>
