google / google/googlesql

Java version: How to support Function namepath with size > 1

Open
#108 4 comments 0 reactions 0 assignees View on GitHub
Dominant language
C++
Stars
2.6k
Forks
260
PR merge metrics
No merged PRs in 30d

Description

Hi all -

We are trying to add our UDFs as functions to a zetaSQL catalog. While we are able to successfully add them, when we attempt to parse SQL that uses them, we always get "function not found". However, this seems to only be the case if the namepath size is > 1. I.e "udf.stringLength" vs "stringLength"

I have two test cases to show this:

Doesn't work:
```kotlin
@Test
fun canParseQueryWithUDF() {
val func = Function(
ImmutableList.of("proj","dataset", "test_func"),
"udf",
ZetaSQLFunctions.FunctionEnums.Mode.SCALAR,
listOf(
FunctionSignature(
FunctionArgumentType(TypeFactory.createSimpleType(ZetaSQLType.TypeKind.TYPE_INT64)),
ImmutableList.of(),
-1
)
)
)
val catalog = SimpleCatalog("test_catalog")
catalog.addFunction(func)
val testSql = "select `proj.dataset.test_func`()"
val analyzer = Analyzer(SQLAnalyzer.buildOptions(), catalog)
analyzer.analyzeStatement(testSql)
}
```
`com.google.zetasql.SqlException: Function not found: 'proj.dataset.test_func' [at 1:8]`

Doesn't work:
```kotlin
@Test
fun canParseQueryWithUDF() {
val func = Function(
ImmutableList.of("proj.dataset.test_func"),
"udf",
ZetaSQLFunctions.FunctionEnums.Mode.SCALAR,
listOf(
FunctionSignature(
FunctionArgumentType(TypeFactory.createSimpleType(ZetaSQLType.TypeKind.TYPE_INT64)),
ImmutableList.of(),
-1
)
)
)
val catalog = SimpleCatalog("test_catalog")
catalog.addFunction(func)
val testSql = "select `proj`.dataset.test_func()"
val analyzer = Analyzer(SQLAnalyzer.buildOptions(), catalog)
analyzer.analyzeStatement(testSql)
}
```

`com.google.zetasql.SqlException: Function not found: 'proj.dataset.test_func' [at 1:8]`
Works:
```kotlin
@Test
fun canParseQueryWithUDF() {
val func = Function(
ImmutableList.of("test_func"),
"udf",
ZetaSQLFunctions.FunctionEnums.Mode.SCALAR,
listOf(
FunctionSignature(
FunctionArgumentType(TypeFactory.createSimpleType(ZetaSQLType.TypeKind.TYPE_INT64)),
ImmutableList.of(),
-1
)
)
)
val catalog = SimpleCatalog("test_catalog")
catalog.addFunction(func)
val testSql = "select `test_func`()"
val analyzer = Analyzer(SQLAnalyzer.buildOptions(), catalog)
analyzer.analyzeStatement(testSql)
}
```

As a follow up, do you all do any additional processing of queries before parsing them with zetaSQL? It seems to get consistent error free parsing we need to ensure that table refs are quoted in a very specific way

\`my-proj.dataset.my_table\` vs \`my-proj\`.dataset.my_table vs dataset.my_table

For the above, only the first seems to consistently work (thus we process each query to ensure its table refs look like that)

Contributor guide

Open the contributing guide

Research direction

Start with the Kotlin test cases in the issue, focusing on SimpleCatalog.addFunction and Analyzer.analyzeStatement with qualified function names. Reproduce both namepath forms and compare them with the single-name case, then inspect the Java API's function-name resolution and identifier quoting behavior. Done means qualified UDFs parse consistently and the expected table-reference quoting behavior is documented or tested.

Written by the indexing model from the issue text.

Assessment

Tech stack
java, kotlin, sql
Domain
api, databases
Issue type
Bug
Difficulty
4/5
Estimated time
3-5 days
Activity status
Stale
Clarity
Mostly clear
Newbie friendliness
35/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.