Once #1390 lands, isthmus maps each covered string function to one Calcite operator and rejects any option preference that operator does not implement. That is spec-correct, but Calcite already has operators for two of the rejected behaviors:
concat with null_handling: [IGNORE_NULLS]: SqlLibraryOperators.CONCAT_FUNCTION_WITH_NULL treats a null argument as the empty string, which is what the IGNORE_NULLS cases in the spec's tests/cases/string/concat.test expect, including concat(null, null) = ''.
like with case_sensitivity: [CASE_INSENSITIVE]: SqlLibraryOperators.ILIKE.
scalar_function concat:str(string 'a', string? null) options { null_handling: [IGNORE_NULLS] }
// after #1390: UnsupportedOperationException ("Calcite concat requires null_handling ACCEPT_NULLS")
Proposal: let the option policy pick the operator from the preference list on import, and emit IGNORE_NULLS / CASE_INSENSITIVE when exporting those operators. That would make the policy a choice between operators, the way the distribution enum argument already chooses between STDDEV_POP and STDDEV_SAMP.
Things to check before binding library operators:
- Nullability: Calcite types
CONCAT_FUNCTION_WITH_NULL's result NOT NULL, whereas the spec's concat test types the result str?.
- Known Calcite traps: binding a
SqlLibraryOperators constant directly has previously broken ORDER BY, emitted a widened output_type, and failed in the native image. A local operator registered in SUBSTRAIT_SCALAR_OPERATOR_TABLE may be needed instead.
Follow-up from the #1390 review; related to #1173 and #1012.
Once #1390 lands, isthmus maps each covered string function to one Calcite operator and rejects any option preference that operator does not implement. That is spec-correct, but Calcite already has operators for two of the rejected behaviors:
concatwithnull_handling: [IGNORE_NULLS]:SqlLibraryOperators.CONCAT_FUNCTION_WITH_NULLtreats a null argument as the empty string, which is what theIGNORE_NULLScases in the spec'stests/cases/string/concat.testexpect, includingconcat(null, null) = ''.likewithcase_sensitivity: [CASE_INSENSITIVE]:SqlLibraryOperators.ILIKE.Proposal: let the option policy pick the operator from the preference list on import, and emit
IGNORE_NULLS/CASE_INSENSITIVEwhen exporting those operators. That would make the policy a choice between operators, the way thedistributionenum argument already chooses betweenSTDDEV_POPandSTDDEV_SAMP.Things to check before binding library operators:
CONCAT_FUNCTION_WITH_NULL's resultNOT NULL, whereas the spec's concat test types the resultstr?.SqlLibraryOperatorsconstant directly has previously broken ORDER BY, emitted a widenedoutput_type, and failed in the native image. A local operator registered inSUBSTRAIT_SCALAR_OPERATOR_TABLEmay be needed instead.Follow-up from the #1390 review; related to #1173 and #1012.