You are viewing a plain text version of this content. The canonical link for it is here.
Posted to github@datafusion.apache.org by "ewgenius (via GitHub)" <gi...@apache.org> on 2025/04/23 04:01:36 UTC

[PR] Fix ILIKE expression support in SQL unparser [datafusion]

ewgenius opened a new pull request, #15820:
URL: https://github.com/apache/datafusion/pull/15820

   ## Which issue does this PR close?
   
   SQL Unparser incorrectly handles `ILIKE` expressions, handling all as `LIKE`, ignoring `case_insensitive` flag.
   
   ## Rationale for this change
   
   Case sensitivity shouldn't be ignored in `ILIKE` expressions, as it's a part of the SQL standard.
   
   ## What changes are included in this PR?
   
   Added correct handling for `case_insensitive` flag in `ILIKE` expressions in SQL unparser
   
   ## Are these changes tested?
   
   Yes, covered by additional SQL roundtrip test in plan_to_sql, fixed and extended expressions tests for LIKE/ILIKE.
   
   ## Are there any user-facing changes?
   
   No
   


-- 
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: [PR] Fix `ILIKE` expression support in SQL unparser [datafusion]

Posted by "phillipleblanc (via GitHub)" <gi...@apache.org>.
phillipleblanc commented on PR #15820:
URL: https://github.com/apache/datafusion/pull/15820#issuecomment-2826473113

   Correct, DataFusion was correctly handling Like and ILike - but DataFusion stores that as a single `Expr::Like ` with a boolean for whether its case insensitive.
   
   When translating that expression back into the SQL AST, we need to use the correct AST expression for `Expr::Like` with `case_insensitive` equal to `true` (i.e. `ILIKE`) or `Expr::Like` with `case_insensitive` equal to false (i.e. `LIKE`)


-- 
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: [PR] Fix `ILIKE` expression support in SQL unparser [datafusion]

Posted by "comphead (via GitHub)" <gi...@apache.org>.
comphead commented on PR #15820:
URL: https://github.com/apache/datafusion/pull/15820#issuecomment-2827893961

   > Correct, DataFusion was correctly handling Like and ILike - but DataFusion stores that as a single `Expr::Like ` with a boolean for whether its case insensitive.
   > 
   > When translating that expression back into the SQL AST, we need to use the correct AST expression for `Expr::Like` with `case_insensitive` equal to `true` (i.e. `ILIKE`) or `Expr::Like` with `case_insensitive` equal to false (i.e. `LIKE`)
   
   I see, thanks for letting me know. 


-- 
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: [PR] Fix `ILIKE` expression support in SQL unparser [datafusion]

Posted by "ewgenius (via GitHub)" <gi...@apache.org>.
ewgenius commented on PR #15820:
URL: https://github.com/apache/datafusion/pull/15820#issuecomment-2826267482

   @comphead thanks for the response.
   
   The problem we faced at Spice is that `ILIKE` expression pushed down as `LIKE` to the data source:
   - https://github.com/spiceai/spiceai/issues/5486
   
   I'm not entirely sure, but this particular query migh not go through SQL unparser so it is not affected by the bug?
   cc @phillipleblanc 
   
   ```
   ➜ datafusion-cli
   DataFusion CLI v47.0.0
   > select * from (select 'a' a) t where t.a like ('%A%');
   +---+
   | a |
   +---+
   +---+
   0 row(s) fetched.
   Elapsed 0.023 seconds.
   
   > select * from (select 'a' a) t where t.a ilike ('%A%');
   +---+
   | a |
   +---+
   | a |
   +---+
   1 row(s) fetched.
   Elapsed 0.004 seconds.
   
   > explain select * from (select 'a' a) t where t.a ilike ('%A%');
   +---------------+-------------------------------+
   | plan_type     | plan                          |
   +---------------+-------------------------------+
   | physical_plan | ┌───────────────────────────┐ |
   |               | │       ProjectionExec      │ |
   |               | │    --------------------   │ |
   |               | │          expr0: a         │ |
   |               | └─────────────┬─────────────┘ |
   |               | ┌─────────────┴─────────────┐ |
   |               | │     PlaceholderRowExec    │ |
   |               | └───────────────────────────┘ |
   |               |                               |
   +---------------+-------------------------------+
   1 row(s) fetched.
   Elapsed 0.003 seconds.
   
   > explain select * from (select 'a' a) t where t.a like ('%A%');
   +---------------+-------------------------------+
   | plan_type     | plan                          |
   +---------------+-------------------------------+
   | physical_plan | ┌───────────────────────────┐ |
   |               | │         EmptyExec         │ |
   |               | └───────────────────────────┘ |
   |               |                               |
   +---------------+-------------------------------+
   1 row(s) fetched.
   Elapsed 0.000 seconds.
   ```
   
   


-- 
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: [PR] Fix `ILIKE` expression support in SQL unparser [datafusion]

Posted by "comphead (via GitHub)" <gi...@apache.org>.
comphead merged PR #15820:
URL: https://github.com/apache/datafusion/pull/15820


-- 
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