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                };
298                CTE {
299                    name: cte.name,
300                    column_list: cte.column_list,
301                    cte_type,
302                }
303            })
304            .collect()
305    }
306
307    /// Transform a complete statement
308    fn transform_statement(&self, mut stmt: SelectStatement) -> SelectStatement {
309        // Transform CTEs first
310        if !stmt.ctes.is_empty() {
311            stmt.ctes = self.transform_ctes(stmt.ctes);
312        }
313
314        // Transform SELECT clause
315        stmt.select_items = self.transform_select_items(stmt.select_items);
316
317        // Transform WHERE clause
318        if let Some(where_clause) = stmt.where_clause {
319            stmt.where_clause = Some(self.transform_where_clause(where_clause));
320        }
321
322        // Transform HAVING clause
323        if let Some(having) = stmt.having {
324            stmt.having = Some(self.transform_expression(having));
325        }
326
327        // Transform ORDER BY clause
328        if let Some(order_by) = stmt.order_by {
329            stmt.order_by = Some(self.transform_order_by(order_by));
330        }
331
332        // Transform GROUP BY clause
333        if let Some(group_by) = stmt.group_by {
334            stmt.group_by = Some(self.transform_group_by(group_by));
335        }
336
337        // Transform QUALIFY clause
338        if let Some(qualify) = stmt.qualify {
339            stmt.qualify = Some(self.transform_expression(qualify));
340        }
341
342        stmt
343    }
344}
345
346impl Default for ILikeToLikeTransformer {
347    fn default() -> Self {
348        Self::new()
349    }
350}
351
352impl ASTTransformer for ILikeToLikeTransformer {
353    fn name(&self) -> &str {
354        "ILikeToLikeTransformer"
355    }
356
357    fn description(&self) -> &str {
358        "Converts ILIKE (case-insensitive LIKE) to UPPER() LIKE UPPER() pattern"
359    }
360
361    fn transform(&mut self, stmt: SelectStatement) -> Result<SelectStatement> {
362        Ok(self.transform_statement(stmt))
363    }
364}
365
366#[cfg(test)]
367mod tests {
368    use super::*;
369    use crate::sql::parser::ast::{ColumnRef, QuoteStyle};
370
371    #[test]
372    fn test_ilike_simple() {
373        let expr = SqlExpression::BinaryOp {
374            left: Box::new(SqlExpression::Column(ColumnRef::unquoted(
375                "email".to_string(),
376            ))),
377            op: "ILIKE".to_string(),
378            right: Box::new(SqlExpression::StringLiteral("%@gmail.com".to_string())),
379        };
380
381        let transformer = ILikeToLikeTransformer::new();
382        let result = transformer.transform_expression(expr);
383
384        // Should be UPPER(email) LIKE UPPER('%@gmail.com')
385        match result {
386            SqlExpression::BinaryOp { left, op, right } => {
387                assert_eq!(op, "LIKE");
388
389                // Check left is UPPER(email)
390                match *left {
391                    SqlExpression::FunctionCall { ref name, .. } => {
392                        assert_eq!(name, "UPPER");
393                    }
394                    _ => panic!("Expected FunctionCall on left"),
395                }
396
397                // Check right is UPPER('%@gmail.com')
398                match *right {
399                    SqlExpression::FunctionCall { ref name, .. } => {
400                        assert_eq!(name, "UPPER");
401                    }
402                    _ => panic!("Expected FunctionCall on right"),
403                }
404            }
405            _ => panic!("Expected BinaryOp"),
406        }
407    }
408
409    #[test]
410    fn test_ilike_in_where_clause() {
411        let mut stmt = SelectStatement::default();
412
413        stmt.where_clause = Some(WhereClause {
414            conditions: vec![Condition {
415                expr: SqlExpression::BinaryOp {
416                    left: Box::new(SqlExpression::Column(ColumnRef::unquoted(
417                        "name".to_string(),
418                    ))),
419                    op: "ILIKE".to_string(),
420                    right: Box::new(SqlExpression::StringLiteral("%john%".to_string())),
421                },
422                connector: None,
423            }],
424        });
425
426        let mut transformer = ILikeToLikeTransformer::new();
427        let result = transformer.transform(stmt).unwrap();
428
429        let where_clause = result.where_clause.unwrap();
430        let condition = &where_clause.conditions[0];
431
432        match &condition.expr {
433            SqlExpression::BinaryOp { op, .. } => {
434                assert_eq!(op, "LIKE");
435            }
436            _ => panic!("Expected BinaryOp"),
437        }
438    }
439
440    #[test]
441    fn test_like_unchanged() {
442        let expr = SqlExpression::BinaryOp {
443            left: Box::new(SqlExpression::Column(ColumnRef::unquoted(
444                "email".to_string(),
445            ))),
446            op: "LIKE".to_string(),
447            right: Box::new(SqlExpression::StringLiteral("%@gmail.com".to_string())),
448        };
449
450        let transformer = ILikeToLikeTransformer::new();
451        let result = transformer.transform_expression(expr.clone());
452
453        // LIKE should remain unchanged
454        match result {
455            SqlExpression::BinaryOp { op, .. } => {
456                assert_eq!(op, "LIKE");
457                // Should NOT be wrapped in UPPER()
458            }
459            _ => panic!("Expected BinaryOp"),
460        }
461    }
462}
sql_cli/query_plan/ilike_to_like_transformer.rs

sql_cli/query_plan/
ilike_to_like_transformer.rs
