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, SimpleWhenBranch, SqlExpression,
36    WhenBranch, WhereClause, CTE,
37};
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            // Recursively transform nested expressions
72            SqlExpression::BinaryOp { left, op, right } => SqlExpression::BinaryOp {
73                left: Box::new(self.transform_expression(*left)),
74                op,
75                right: Box::new(self.transform_expression(*right)),
76            },
77
78            SqlExpression::FunctionCall {
79                name,
80                args,
81                distinct,
82            } => SqlExpression::FunctionCall {
83                name,
84                args: args
85                    .into_iter()
86                    .map(|arg| self.transform_expression(arg))
87                    .collect(),
88                distinct,
89            },
90
91            SqlExpression::CaseExpression {
92                when_branches,
93                else_branch,
94            } => SqlExpression::CaseExpression {
95                when_branches: when_branches
96                    .into_iter()
97                    .map(|branch| WhenBranch {
98                        condition: Box::new(self.transform_expression(*branch.condition)),
99                        result: Box::new(self.transform_expression(*branch.result)),
100                    })
101                    .collect(),
102                else_branch: else_branch.map(|e| Box::new(self.transform_expression(*e))),
103            },
104
105            SqlExpression::SimpleCaseExpression {
106                expr,
107                when_branches,
108                else_branch,
109            } => SqlExpression::SimpleCaseExpression {
110                expr: Box::new(self.transform_expression(*expr)),
111                when_branches: when_branches
112                    .into_iter()
113                    .map(|branch| SimpleWhenBranch {
114                        value: Box::new(self.transform_expression(*branch.value)),
115                        result: Box::new(self.transform_expression(*branch.result)),
116                    })
117                    .collect(),
118                else_branch: else_branch.map(|e| Box::new(self.transform_expression(*e))),
119            },
120
121            SqlExpression::Between { expr, lower, upper } => SqlExpression::Between {
122                expr: Box::new(self.transform_expression(*expr)),
123                lower: Box::new(self.transform_expression(*lower)),
124                upper: Box::new(self.transform_expression(*upper)),
125            },
126
127            SqlExpression::InList { expr, values } => SqlExpression::InList {
128                expr: Box::new(self.transform_expression(*expr)),
129                values: values
130                    .into_iter()
131                    .map(|v| self.transform_expression(v))
132                    .collect(),
133            },
134
135            SqlExpression::InSubquery { expr, subquery } => SqlExpression::InSubquery {
136                expr: Box::new(self.transform_expression(*expr)),
137                subquery: Box::new(self.transform_statement(*subquery)),
138            },
139
140            SqlExpression::NotInList { expr, values } => SqlExpression::NotInList {
141                expr: Box::new(self.transform_expression(*expr)),
142                values: values
143                    .into_iter()
144                    .map(|v| self.transform_expression(v))
145                    .collect(),
146            },
147
148            SqlExpression::MethodCall {
149                object,
150                method,
151                args,
152            } => SqlExpression::MethodCall {
153                object,
154                method,
155                args: args
156                    .into_iter()
157                    .map(|arg| self.transform_expression(arg))
158                    .collect(),
159            },
160
161            SqlExpression::ChainedMethodCall { base, method, args } => {
162                SqlExpression::ChainedMethodCall {
163                    base: Box::new(self.transform_expression(*base)),
164                    method,
165                    args: args
166                        .into_iter()
167                        .map(|arg| self.transform_expression(arg))
168                        .collect(),
169                }
170            }
171
172            SqlExpression::Not { expr } => SqlExpression::Not {
173                expr: Box::new(self.transform_expression(*expr)),
174            },
175
176            SqlExpression::ScalarSubquery { query } => SqlExpression::ScalarSubquery {
177                query: Box::new(self.transform_statement(*query)),
178            },
179
180            SqlExpression::NotInSubquery { expr, subquery } => SqlExpression::NotInSubquery {
181                expr: Box::new(self.transform_expression(*expr)),
182                subquery: Box::new(self.transform_statement(*subquery)),
183            },
184
185            SqlExpression::WindowFunction {
186                name,
187                args,
188                window_spec,
189            } => SqlExpression::WindowFunction {
190                name,
191                args: args
192                    .into_iter()
193                    .map(|arg| self.transform_expression(arg))
194                    .collect(),
195                window_spec,
196            },
197
198            SqlExpression::Unnest { column, delimiter } => SqlExpression::Unnest {
199                column: Box::new(self.transform_expression(*column)),
200                delimiter,
201            },
202
203            // Literals and simple expressions don't need transformation
204            _ => expr,
205        }
206    }
207
208    /// Transform WHERE clause
209    fn transform_where_clause(&self, where_clause: WhereClause) -> WhereClause {
210        WhereClause {
211            conditions: where_clause
212                .conditions
213                .into_iter()
214                .map(|condition| Condition {
215                    expr: self.transform_expression(condition.expr),
216                    connector: condition.connector,
217                })
218                .collect(),
219        }
220    }
221
222    /// Transform SELECT items
223    fn transform_select_items(&self, items: Vec<SelectItem>) -> Vec<SelectItem> {
224        items
225            .into_iter()
226            .map(|item| match item {
227                SelectItem::Expression {
228                    expr,
229                    alias,
230                    leading_comments,
231                    trailing_comment,
232                } => SelectItem::Expression {
233                    expr: self.transform_expression(expr),
234                    alias,
235                    leading_comments,
236                    trailing_comment,
237                },
238                SelectItem::Column {
239                    column,
240                    leading_comments,
241                    trailing_comment,
242                } => SelectItem::Column {
243                    column,
244                    leading_comments,
245                    trailing_comment,
246                },
247                SelectItem::Star {
248                    table_prefix,
249                    leading_comments,
250                    trailing_comment,
251                } => SelectItem::Star {
252                    table_prefix,
253                    leading_comments,
254                    trailing_comment,
255                },
256                SelectItem::StarExclude {
257                    table_prefix,
258                    excluded_columns,
259                    leading_comments,
260                    trailing_comment,
261                } => SelectItem::StarExclude {
262                    table_prefix,
263                    excluded_columns,
264                    leading_comments,
265                    trailing_comment,
266                },
267            })
268            .collect()
269    }
270
271    /// Transform ORDER BY items
272    fn transform_order_by(&self, items: Vec<OrderByItem>) -> Vec<OrderByItem> {
273        items
274            .into_iter()
275            .map(|item| OrderByItem {
276                expr: self.transform_expression(item.expr),
277                direction: item.direction,
278            })
279            .collect()
280    }
281
282    /// Transform GROUP BY expressions
283    fn transform_group_by(&self, exprs: Vec<SqlExpression>) -> Vec<SqlExpression> {
284        exprs
285            .into_iter()
286            .map(|e| self.transform_expression(e))
287            .collect()
288    }
289
290    /// Transform CTEs
291    fn transform_ctes(&self, ctes: Vec<CTE>) -> Vec<CTE> {
292        ctes.into_iter()
293            .map(|cte| {
294                let cte_type = match cte.cte_type {
295                    CTEType::Standard(stmt) => CTEType::Standard(self.transform_statement(stmt)),
296                    CTEType::Web(web_spec) => CTEType::Web(web_spec), // Don't transform WEB CTEs
297                    CTEType::File(file_spec) => CTEType::File(file_spec), // Don't transform FILE CTEs
298                };
299                CTE {
300                    name: cte.name,
301                    column_list: cte.column_list,
302                    cte_type,
303                }
304            })
305            .collect()
306    }
307
308    /// Transform a complete statement
309    fn transform_statement(&self, mut stmt: SelectStatement) -> SelectStatement {
310        // Transform CTEs first
311        if !stmt.ctes.is_empty() {
312            stmt.ctes = self.transform_ctes(stmt.ctes);
313        }
314
315        // Transform SELECT clause
316        stmt.select_items = self.transform_select_items(stmt.select_items);
317
318        // Transform WHERE clause
319        if let Some(where_clause) = stmt.where_clause {
320            stmt.where_clause = Some(self.transform_where_clause(where_clause));
321        }
322
323        // Transform HAVING clause
324        if let Some(having) = stmt.having {
325            stmt.having = Some(self.transform_expression(having));
326        }
327
328        // Transform ORDER BY clause
329        if let Some(order_by) = stmt.order_by {
330            stmt.order_by = Some(self.transform_order_by(order_by));
331        }
332
333        // Transform GROUP BY clause
334        if let Some(group_by) = stmt.group_by {
335            stmt.group_by = Some(self.transform_group_by(group_by));
336        }
337
338        // Transform QUALIFY clause
339        if let Some(qualify) = stmt.qualify {
340            stmt.qualify = Some(self.transform_expression(qualify));
341        }
342
343        stmt
344    }
345}
346
347impl Default for ILikeToLikeTransformer {
348    fn default() -> Self {
349        Self::new()
350    }
351}
352
353impl ASTTransformer for ILikeToLikeTransformer {
354    fn name(&self) -> &str {
355        "ILikeToLikeTransformer"
356    }
357
358    fn description(&self) -> &str {
359        "Converts ILIKE (case-insensitive LIKE) to UPPER() LIKE UPPER() pattern"
360    }
361
362    fn transform(&mut self, stmt: SelectStatement) -> Result<SelectStatement> {
363        Ok(self.transform_statement(stmt))
364    }
365}
366
367#[cfg(test)]
368mod tests {
369    use super::*;
370    use crate::sql::parser::ast::{ColumnRef, QuoteStyle};
371
372    #[test]
373    fn test_ilike_simple() {
374        let expr = SqlExpression::BinaryOp {
375            left: Box::new(SqlExpression::Column(ColumnRef::unquoted(
376                "email".to_string(),
377            ))),
378            op: "ILIKE".to_string(),
379            right: Box::new(SqlExpression::StringLiteral("%@gmail.com".to_string())),
380        };
381
382        let transformer = ILikeToLikeTransformer::new();
383        let result = transformer.transform_expression(expr);
384
385        // Should be UPPER(email) LIKE UPPER('%@gmail.com')
386        match result {
387            SqlExpression::BinaryOp { left, op, right } => {
388                assert_eq!(op, "LIKE");
389
390                // Check left is UPPER(email)
391                match *left {
392                    SqlExpression::FunctionCall { ref name, .. } => {
393                        assert_eq!(name, "UPPER");
394                    }
395                    _ => panic!("Expected FunctionCall on left"),
396                }
397
398                // Check right is UPPER('%@gmail.com')
399                match *right {
400                    SqlExpression::FunctionCall { ref name, .. } => {
401                        assert_eq!(name, "UPPER");
402                    }
403                    _ => panic!("Expected FunctionCall on right"),
404                }
405            }
406            _ => panic!("Expected BinaryOp"),
407        }
408    }
409
410    #[test]
411    fn test_ilike_in_where_clause() {
412        let mut stmt = SelectStatement::default();
413
414        stmt.where_clause = Some(WhereClause {
415            conditions: vec![Condition {
416                expr: SqlExpression::BinaryOp {
417                    left: Box::new(SqlExpression::Column(ColumnRef::unquoted(
418                        "name".to_string(),
419                    ))),
420                    op: "ILIKE".to_string(),
421                    right: Box::new(SqlExpression::StringLiteral("%john%".to_string())),
422                },
423                connector: None,
424            }],
425        });
426
427        let mut transformer = ILikeToLikeTransformer::new();
428        let result = transformer.transform(stmt).unwrap();
429
430        let where_clause = result.where_clause.unwrap();
431        let condition = &where_clause.conditions[0];
432
433        match &condition.expr {
434            SqlExpression::BinaryOp { op, .. } => {
435                assert_eq!(op, "LIKE");
436            }
437            _ => panic!("Expected BinaryOp"),
438        }
439    }
440
441    #[test]
442    fn test_like_unchanged() {
443        let expr = SqlExpression::BinaryOp {
444            left: Box::new(SqlExpression::Column(ColumnRef::unquoted(
445                "email".to_string(),
446            ))),
447            op: "LIKE".to_string(),
448            right: Box::new(SqlExpression::StringLiteral("%@gmail.com".to_string())),
449        };
450
451        let transformer = ILikeToLikeTransformer::new();
452        let result = transformer.transform_expression(expr.clone());
453
454        // LIKE should remain unchanged
455        match result {
456            SqlExpression::BinaryOp { op, .. } => {
457                assert_eq!(op, "LIKE");
458                // Should NOT be wrapped in UPPER()
459            }
460            _ => panic!("Expected BinaryOp"),
461        }
462    }
463}
sql_cli/query_plan/ilike_to_like_transformer.rs

sql_cli/query_plan/
ilike_to_like_transformer.rs
