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
>
