sql_cli/query_plan/
ilike_to_like_transformer.rs

1//! ILIKE to LIKE transformer
2//!
3//! This transformer converts ILIKE (case-insensitive LIKE) operators to
4//! standard LIKE operators by wrapping both sides in UPPER() function calls.
5//!
6//! # Problem
7//!
8//! PostgreSQL-style ILIKE is convenient for case-insensitive pattern matching:
9//! ```sql
10//! SELECT * FROM users WHERE email ILIKE '%@GMAIL.COM'
11//! ```
12//!
13//! But not all SQL engines support ILIKE natively.
14//!
15//! # Solution
16//!
17//! Transform ILIKE to UPPER(col) LIKE UPPER(pattern):
18//! ```sql
19//! -- Input
20//! WHERE email ILIKE '%@gmail.com'
21//!
22//! -- Output
23//! WHERE UPPER(email) LIKE UPPER('%@gmail.com')
24//! ```
25//!
26//! # Algorithm
27//!
28//! 1. Traverse the entire AST
29//! 2. Find all BinaryOp expressions with "ILIKE" operator
30//! 3. Replace with LIKE operator and wrap both sides in UPPER()
31//! 4. Recursively handle all clauses (WHERE, SELECT, HAVING, etc.)
32
33use crate::query_plan::pipeline::ASTTransformer;
34use crate::sql::parser::ast::{
35    CTEType, Condition, OrderByItem, SelectItem, SelectStatement, SqlExpression, WhereClause, CTE,
36};
37use crate::sql::parser::walk;
38use anyhow::Result;
39use tracing::debug;
40
41/// Transformer that converts ILIKE to UPPER() LIKE UPPER()
42pub struct ILikeToLikeTransformer;
43
44impl ILikeToLikeTransformer {
45    pub fn new() -> Self {
46        Self
47    }
48
49    /// Transform an expression, converting ILIKE to LIKE with UPPER()
50    fn transform_expression(&self, expr: SqlExpression) -> SqlExpression {
51        match expr {
52            // Core transformation: ILIKE -> UPPER() LIKE UPPER()
53            SqlExpression::BinaryOp { left, op, right } if op == "ILIKE" => {
54                debug!("Transforming ILIKE to UPPER() LIKE UPPER()");
55
56                SqlExpression::BinaryOp {
57                    left: Box::new(SqlExpression::FunctionCall {
58                        name: "UPPER".to_string(),
59                        args: vec![self.transform_expression(*left)],
60                        distinct: false,
61                    }),
62                    op: "LIKE".to_string(),
63                    right: Box::new(SqlExpression::FunctionCall {
64                        name: "UPPER".to_string(),
65                        args: vec![self.transform_expression(*right)],
66                        distinct: false,
67                    }),
68                }
69            }
70
71            // Everything else is plain traversal. ILIKE -> LIKE is
72            // scope-independent, so we cross the subquery boundary: the
73            // `crossing` form rewrites nested statements too, without this
74            // file having to name the subquery-bearing variants itself.
75            other => walk::map_children_crossing(
76                other,
77                &mut (),
78                |_, e| self.transform_expression(e),
79                |_, stmt| Box::new(self.transform_statement(*stmt)),
80            ),
81        }
82    }
83
84    /// Transform WHERE clause
85    fn transform_where_clause(&self, where_clause: WhereClause) -> WhereClause {
86        WhereClause {
87            conditions: where_clause
88                .conditions
89                .into_iter()
90                .map(|condition| Condition {
91                    expr: self.transform_expression(condition.expr),
92                    connector: condition.connector,
93                })
94                .collect(),
95        }
96    }
97
98    /// Transform SELECT items
99    fn transform_select_items(&self, items: Vec<SelectItem>) -> Vec<SelectItem> {
100        items
101            .into_iter()
102            .map(|item| match item {
103                SelectItem::Expression {
104                    expr,
105                    alias,
106                    leading_comments,
107                    trailing_comment,
108                } => SelectItem::Expression {
109                    expr: self.transform_expression(expr),
110                    alias,
111                    leading_comments,
112                    trailing_comment,
113                },
114                SelectItem::Column {
115                    column,
116                    leading_comments,
117                    trailing_comment,
118                } => SelectItem::Column {
119                    column,
120                    leading_comments,
121                    trailing_comment,
122                },
123                SelectItem::Star {
124                    table_prefix,
125                    leading_comments,
126                    trailing_comment,
127                } => SelectItem::Star {
128                    table_prefix,
129                    leading_comments,
130                    trailing_comment,
131                },
132                SelectItem::StarExclude {
133                    table_prefix,
134                    excluded_columns,
135                    leading_comments,
136                    trailing_comment,
137                } => SelectItem::StarExclude {
138                    table_prefix,
139                    excluded_columns,
140                    leading_comments,
141                    trailing_comment,
142                },
143            })
144            .collect()
145    }
146
147    /// Transform ORDER BY items
148    fn transform_order_by(&self, items: Vec<OrderByItem>) -> Vec<OrderByItem> {
149        items
150            .into_iter()
151            .map(|item| OrderByItem {
152                expr: self.transform_expression(item.expr),
153                direction: item.direction,
154            })
155            .collect()
156    }
157
158    /// Transform GROUP BY expressions
159    fn transform_group_by(&self, exprs: Vec<SqlExpression>) -> Vec<SqlExpression> {
160        exprs
161            .into_iter()
162            .map(|e| self.transform_expression(e))
163            .collect()
164    }
165
166    /// Transform CTEs
167    fn transform_ctes(&self, ctes: Vec<CTE>) -> Vec<CTE> {
168        ctes.into_iter()
169            .map(|cte| {
170                let cte_type = match cte.cte_type {
171                    CTEType::Standard(stmt) => CTEType::Standard(self.transform_statement(stmt)),
172                    CTEType::Web(web_spec) => CTEType::Web(web_spec), // Don't transform WEB CTEs
173                    CTEType::File(file_spec) => CTEType::File(file_spec), // Don't transform FILE CTEs
174                };
175                CTE {
176                    name: cte.name,
177                    column_list: cte.column_list,
178                    cte_type,
179                }
180            })
181            .collect()
182    }
183
184    /// Transform a complete statement
185    fn transform_statement(&self, mut stmt: SelectStatement) -> SelectStatement {
186        // Transform CTEs first
187        if !stmt.ctes.is_empty() {
188            stmt.ctes = self.transform_ctes(stmt.ctes);
189        }
190
191        // Transform SELECT clause
192        stmt.select_items = self.transform_select_items(stmt.select_items);
193
194        // Transform WHERE clause
195        if let Some(where_clause) = stmt.where_clause {
196            stmt.where_clause = Some(self.transform_where_clause(where_clause));
197        }
198
199        // Transform HAVING clause
200        if let Some(having) = stmt.having {
201            stmt.having = Some(self.transform_expression(having));
202        }
203
204        // Transform ORDER BY clause
205        if let Some(order_by) = stmt.order_by {
206            stmt.order_by = Some(self.transform_order_by(order_by));
207        }
208
209        // Transform GROUP BY clause
210        if let Some(group_by) = stmt.group_by {
211            stmt.group_by = Some(self.transform_group_by(group_by));
212        }
213
214        // Transform QUALIFY clause
215        if let Some(qualify) = stmt.qualify {
216            stmt.qualify = Some(self.transform_expression(qualify));
217        }
218
219        stmt
220    }
221}
222
223impl Default for ILikeToLikeTransformer {
224    fn default() -> Self {
225        Self::new()
226    }
227}
228
229impl ASTTransformer for ILikeToLikeTransformer {
230    fn name(&self) -> &str {
231        "ILikeToLikeTransformer"
232    }
233
234    fn description(&self) -> &str {
235        "Converts ILIKE (case-insensitive LIKE) to UPPER() LIKE UPPER() pattern"
236    }
237
238    fn transform(&mut self, stmt: SelectStatement) -> Result<SelectStatement> {
239        Ok(self.transform_statement(stmt))
240    }
241}
242
243#[cfg(test)]
244mod tests {
245    use super::*;
246    use crate::sql::parser::ast::{ColumnRef, QuoteStyle};
247    use crate::sql::recursive_parser::Parser;
248
249    /// Render just enough of an expression to assert on operators, so these
250    /// tests don't depend on the exact AST shape.
251    fn ops_in(expr: &SqlExpression) -> Vec<String> {
252        let mut ops = Vec::new();
253        crate::sql::parser::walk::visit_all(expr, &mut |e| {
254            if let SqlExpression::BinaryOp { op, .. } = e {
255                ops.push(op.clone());
256            }
257        });
258        ops
259    }
260
261    /// Regression for the walk.rs migration.
262    ///
263    /// `WindowSpec::order_by` holds real expressions, and the old hand-rolled
264    /// walker passed `window_spec` through untouched — so an ILIKE inside
265    /// `OVER (ORDER BY ...)` was silently left as ILIKE and would reach the
266    /// executor as an unknown operator.
267    #[test]
268    fn transforms_ilike_inside_window_order_by() {
269        let stmt = Parser::new(
270            "SELECT ROW_NUMBER() OVER (ORDER BY CASE WHEN name ILIKE '%a%' THEN 1 ELSE 0 END) AS rn FROM t",
271        )
272        .parse()
273        .expect("query should parse");
274
275        let result = ILikeToLikeTransformer::new().transform_statement(stmt);
276
277        let expr = result
278            .select_items
279            .iter()
280            .find_map(|i| match i {
281                SelectItem::Expression { expr, .. } => Some(expr),
282                _ => None,
283            })
284            .expect("expected a projected expression");
285
286        let ops = ops_in(expr);
287        assert!(
288            !ops.iter().any(|o| o == "ILIKE"),
289            "ILIKE inside a window ORDER BY must be rewritten, found ops: {ops:?}"
290        );
291        assert!(
292            ops.iter().any(|o| o == "LIKE"),
293            "expected a LIKE after rewriting, found ops: {ops:?}"
294        );
295    }
296
297    /// The tuple subquery forms fell into the old catch-all, so neither the
298    /// LHS operands nor the subquery body were transformed.
299    #[test]
300    fn transforms_ilike_inside_tuple_subquery() {
301        let stmt = Parser::new(
302            "SELECT a FROM t WHERE (a, b) IN (SELECT x, y FROM u WHERE note ILIKE '%z%')",
303        )
304        .parse()
305        .expect("query should parse");
306
307        let result = ILikeToLikeTransformer::new().transform_statement(stmt);
308
309        let cond = &result.where_clause.expect("where clause").conditions[0].expr;
310        let inner = match cond {
311            SqlExpression::InSubqueryTuple { subquery, .. } => subquery,
312            other => panic!("expected a tuple IN subquery, got {other:?}"),
313        };
314        let inner_cond = &inner
315            .where_clause
316            .as_ref()
317            .expect("inner where clause")
318            .conditions[0]
319            .expr;
320
321        let ops = ops_in(inner_cond);
322        assert!(
323            !ops.iter().any(|o| o == "ILIKE"),
324            "ILIKE inside a tuple subquery must be rewritten, found ops: {ops:?}"
325        );
326    }
327
328    #[test]
329    fn test_ilike_simple() {
330        let expr = SqlExpression::BinaryOp {
331            left: Box::new(SqlExpression::Column(ColumnRef::unquoted(
332                "email".to_string(),
333            ))),
334            op: "ILIKE".to_string(),
335            right: Box::new(SqlExpression::StringLiteral("%@gmail.com".to_string())),
336        };
337
338        let transformer = ILikeToLikeTransformer::new();
339        let result = transformer.transform_expression(expr);
340
341        // Should be UPPER(email) LIKE UPPER('%@gmail.com')
342        match result {
343            SqlExpression::BinaryOp { left, op, right } => {
344                assert_eq!(op, "LIKE");
345
346                // Check left is UPPER(email)
347                match *left {
348                    SqlExpression::FunctionCall { ref name, .. } => {
349                        assert_eq!(name, "UPPER");
350                    }
351                    _ => panic!("Expected FunctionCall on left"),
352                }
353
354                // Check right is UPPER('%@gmail.com')
355                match *right {
356                    SqlExpression::FunctionCall { ref name, .. } => {
357                        assert_eq!(name, "UPPER");
358                    }
359                    _ => panic!("Expected FunctionCall on right"),
360                }
361            }
362            _ => panic!("Expected BinaryOp"),
363        }
364    }
365
366    #[test]
367    fn test_ilike_in_where_clause() {
368        let mut stmt = SelectStatement::default();
369
370        stmt.where_clause = Some(WhereClause {
371            conditions: vec![Condition {
372                expr: SqlExpression::BinaryOp {
373                    left: Box::new(SqlExpression::Column(ColumnRef::unquoted(
374                        "name".to_string(),
375                    ))),
376                    op: "ILIKE".to_string(),
377                    right: Box::new(SqlExpression::StringLiteral("%john%".to_string())),
378                },
379                connector: None,
380            }],
381        });
382
383        let mut transformer = ILikeToLikeTransformer::new();
384        let result = transformer.transform(stmt).unwrap();
385
386        let where_clause = result.where_clause.unwrap();
387        let condition = &where_clause.conditions[0];
388
389        match &condition.expr {
390            SqlExpression::BinaryOp { op, .. } => {
391                assert_eq!(op, "LIKE");
392            }
393            _ => panic!("Expected BinaryOp"),
394        }
395    }
396
397    #[test]
398    fn test_like_unchanged() {
399        let expr = SqlExpression::BinaryOp {
400            left: Box::new(SqlExpression::Column(ColumnRef::unquoted(
401                "email".to_string(),
402            ))),
403            op: "LIKE".to_string(),
404            right: Box::new(SqlExpression::StringLiteral("%@gmail.com".to_string())),
405        };
406
407        let transformer = ILikeToLikeTransformer::new();
408        let result = transformer.transform_expression(expr.clone());
409
410        // LIKE should remain unchanged
411        match result {
412            SqlExpression::BinaryOp { op, .. } => {
413                assert_eq!(op, "LIKE");
414                // Should NOT be wrapped in UPPER()
415            }
416            _ => panic!("Expected BinaryOp"),
417        }
418    }
419}
sql_cli/query_plan/ilike_to_like_transformer.rs

sql_cli/query_plan/
ilike_to_like_transformer.rs
