Substrait producer emits LIKE with three arguments and an undefined ilike function #25368

Describe the bug

The Substrait producer emits LIKE and ILIKE in a shape the Substrait function definition does not describe:

• it passes three arguments, the third being the escape character, while like is defined with two;
• it registers the function name ilike, which no Substrait extension defines;
• it never sets the case_sensitivity option, which is how the specification expresses the case insensitive form.

functions_string.yaml defines exactly one function here, with two arguments and an option:

    name: like
    description: >-
      Are two strings like each other.

      The `case_sensitivity` option applies to the `match` argument.
    impls:
      - args:
          - value: "varchar<L1>"
            name: "input"
            description: The input string.
          - value: "varchar<L2>"
            name: "match"
            description: The string to match against the input string.
        options:
          case_sensitivity:
            values: [ CASE_SENSITIVE, CASE_INSENSITIVE, CASE_INSENSITIVE_ASCII ]
        return: "boolean"

(the second impls entry is the same with string in place of varchar)

Neither ilike nor an escape argument appears in any extension file, in the substrait 0.63.0 crate this repo pins or in the 16 extension yamls on substrait-io/substrait main.

Reproduced on main at 86ba5e0.

To Reproduce

Add this as an example under datafusion/substrait/examples/ and run
cargo run --locked -p datafusion-substrait --example like_probe.

use datafusion::arrow::datatypes::{DataType, Field, Schema};
use datafusion::common::Result;
use datafusion::datasource::empty::EmptyTable;
use datafusion::prelude::SessionContext;
use datafusion_substrait::logical_plan::producer::to_substrait_plan;
use datafusion_substrait::substrait::proto::expression::RexType;
use datafusion_substrait::substrait::proto::extensions::simple_extension_declaration::MappingType;
use datafusion_substrait::substrait::proto::rel::RelType;
use datafusion_substrait::substrait::proto::{plan_rel, Expression, Rel};
use std::sync::Arc;

fn condition(rel: &Rel) -> Option<&Expression> {
    match rel.rel_type.as_ref()? {
        RelType::Filter(f) => f.condition.as_ref().map(|c| c.as_ref()),
        RelType::Project(p) => condition(p.input.as_ref()?),
        _ => None,
    }
}

#[tokio::main(flavor = "current_thread")]
async fn main() -> Result<()> {
    let ctx = SessionContext::new();
    ctx.register_table(
        "t",
        Arc::new(EmptyTable::new(Arc::new(Schema::new(vec![Field::new(
            "s",
            DataType::Utf8,
            true,
        )])))),
    )?;

    for sql in [
        "SELECT s FROM t WHERE s LIKE 'a%'",
        "SELECT s FROM t WHERE s ILIKE 'a%'",
        "SELECT s FROM t WHERE s LIKE 'a!%' ESCAPE '!'",
    ] {
        let plan = ctx.sql(sql).await?.into_optimized_plan()?;
        let proto = to_substrait_plan(&plan, &ctx.state())?;
        let names: Vec<String> = proto
            .extensions
            .iter()
            .filter_map(|e| match e.mapping_type.as_ref()? {
                MappingType::ExtensionFunction(f) => Some(f.name.clone()),
                _ => None,
            })
            .collect();
        let mut arity = 0;
        let mut options = 0;
        for r in &proto.relations {
            if let Some(plan_rel::RelType::Root(root)) = &r.rel_type {
                if let Some(expr) = root.input.as_ref().and_then(|i| condition(i)) {
                    if let Some(RexType::ScalarFunction(f)) = expr.rex_type.as_ref() {
                        arity = f.arguments.len();
                        options = f.options.len();
                    }
                }
            }
        }
        println!("{sql:<48} registered {names:?} arguments {arity} options {options}");
    }
    Ok(())
}

Output:

SELECT s FROM t WHERE s LIKE 'a%'                registered ["like"] arguments 3 options 0
SELECT s FROM t WHERE s ILIKE 'a%'               registered ["ilike"] arguments 3 options 0
SELECT s FROM t WHERE s LIKE 'a!%' ESCAPE '!'    registered ["like"] arguments 3 options 0

Expected behavior

A LIKE should be emitted as the two argument like the extension defines, with case_sensitivity set to CASE_INSENSITIVE for ILIKE rather than a separate ilike function.

The escape character has no representation in that definition. Emitting it as a third argument is not one, since a consumer binds arguments by the declaration. Rejecting LIKE ... ESCAPE with not_impl_err! would at least be visible, instead of producing a plan that other engines misread.

Additional context

What breaks. substrait-java 0.103.0 converts function arguments by index and does not check the count against the declaration (FunctionArg.ProtoFrom.convert only consults the definition for enum arguments), so the plan is accepted and the mismatch surfaces later. Running a DataFusion produced LIKE plan through substrait-java and substrait-spark on Spark 3.5.4, with the output_type fix from #25367 applied so that it gets past the missing type, gives:

AnalysisException: [WRONG_NUM_ARGS.WITHOUT_SUGGESTION] The `like` requires 2 parameters but the actual number is 3.

An ILIKE plan does not get that far. Pointing the declaration at the string extension so that #11545 does not stop it first, substrait-java answers:

IllegalArgumentException: Unexpected scalar function with key ilike:str_str. The URN extension:io.substrait:functions_string is loaded but no scalar function with this key found.

at SimpleExtension$ExtensionCollection.getScalarFunction:1325, because no extension declares that name.

Why no test catches it. The DataFusion consumer accepts both shapes: build_like_expr takes two or three arguments and reads the escape character from the third (consumer/expr/scalar_function.rs:257-287), and it maps the ilike name back to a case insensitive LIKE. Producer and consumer therefore agree on a private convention and every DataFusion to DataFusion round trip passes.

Related, but distinct:

• Substrait producer omits output_type on window functions and LIKE expressions #25366 and fix: Set Substrait output_type on window functions and LIKE #25367 are about output_type on the same call, not its shape. With that fix the plan reaches Spark, which is how this surfaced.
• fix: Implement Substrait consumer support for like_match, like_imatch, and negated variants #18929 added consumer support for like_match and like_imatch; this is about what the producer writes.
• Provide valid extensionUris and extensionUriReferences in Substrait #11545 is the extension URN gap, which stops a consumer even earlier.