[I] Substrait producer emits LIKE with three arguments and an undefined ilike function [datafusion]

["namanjain24-sudo (via GitHub)" <gi...@apache.org>]

namanjain24-sudo opened a new issue, #25368:
URL: https://github.com/apache/datafusion/issues/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:
   
   ```yaml
       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`.
   
   ```rust
   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:**
   
   - #25366 and #25367 are about `output_type` on the same call, not its shape. With that fix the plan reaches Spark, which is how this surfaced.
   - #18929 added consumer support for `like_match` and `like_imatch`; this is about what the producer writes.
   - #11545 is the extension URN gap, which stops a consumer even earlier.


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org

Re: [I] Substrait producer emits LIKE with three arguments and an undefined ilike function [datafusion]

["kosiew (via GitHub)" <gi...@apache.org>]

kosiew closed issue #25368: Substrait producer emits LIKE with three arguments and an undefined ilike function
URL: https://github.com/apache/datafusion/issues/25368


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org

Re: [I] Substrait producer emits LIKE with three arguments and an undefined ilike function [datafusion]

["namanjain24-sudo (via GitHub)" <gi...@apache.org>]

namanjain24-sudo commented on issue #25368:
URL: https://github.com/apache/datafusion/issues/25368#issuecomment-5695217481

   take


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org