1use datafusion_expr::expr::{AggregateFunctionParams, Unnest, WindowFunctionParams};
19use sqlparser::ast::Value::SingleQuotedString;
20use sqlparser::ast::{
21 self, Array, BinaryOperator, CaseWhen, Expr as AstExpr, Function, Ident, Interval,
22 ObjectName, OrderByOptions, Subscript, TimezoneInfo, UnaryOperator, ValueWithSpan,
23};
24use std::sync::Arc;
25use std::vec;
26
27use super::dialect::IntervalStyle;
28use super::Unparser;
29use arrow::array::{
30 types::{
31 ArrowTemporalType, Time32MillisecondType, Time32SecondType,
32 Time64MicrosecondType, Time64NanosecondType, TimestampMicrosecondType,
33 TimestampMillisecondType, TimestampNanosecondType, TimestampSecondType,
34 },
35 ArrayRef, Date32Array, Date64Array, PrimitiveArray,
36};
37use arrow::datatypes::{DataType, Decimal128Type, Decimal256Type, DecimalType};
38use arrow::util::display::array_value_to_string;
39use datafusion_common::{
40 internal_datafusion_err, internal_err, not_impl_err, plan_err, Column, Result,
41 ScalarValue,
42};
43use datafusion_expr::{
44 expr::{Alias, Exists, InList, ScalarFunction, Sort, WindowFunction},
45 Between, BinaryExpr, Case, Cast, Expr, GroupingSet, Like, Operator, TryCast,
46};
47use sqlparser::ast::helpers::attached_token::AttachedToken;
48use sqlparser::tokenizer::Span;
49
50pub fn expr_to_sql(expr: &Expr) -> Result<ast::Expr> {
79 let unparser = Unparser::default();
80 unparser.expr_to_sql(expr)
81}
82
83const LOWEST: &BinaryOperator = &BinaryOperator::Or;
84const IS: &BinaryOperator = &BinaryOperator::BitwiseAnd;
87
88impl Unparser<'_> {
89 pub fn expr_to_sql(&self, expr: &Expr) -> Result<ast::Expr> {
90 let mut root_expr = self.expr_to_sql_inner(expr)?;
91 if self.pretty {
92 root_expr = self.remove_unnecessary_nesting(root_expr, LOWEST, LOWEST);
93 }
94 Ok(root_expr)
95 }
96
97 #[cfg_attr(feature = "recursive_protection", recursive::recursive)]
98 fn expr_to_sql_inner(&self, expr: &Expr) -> Result<ast::Expr> {
99 match expr {
100 Expr::InList(InList {
101 expr,
102 list,
103 negated,
104 }) => {
105 let list_expr = list
106 .iter()
107 .map(|e| self.expr_to_sql_inner(e))
108 .collect::<Result<Vec<_>>>()?;
109 Ok(ast::Expr::InList {
110 expr: Box::new(self.expr_to_sql_inner(expr)?),
111 list: list_expr,
112 negated: *negated,
113 })
114 }
115 Expr::ScalarFunction(ScalarFunction { func, args }) => {
116 let func_name = func.name();
117
118 if let Some(expr) = self
119 .dialect
120 .scalar_function_to_sql_overrides(self, func_name, args)?
121 {
122 return Ok(expr);
123 }
124
125 self.scalar_function_to_sql(func_name, args)
126 }
127 Expr::Between(Between {
128 expr,
129 negated,
130 low,
131 high,
132 }) => {
133 let sql_parser_expr = self.expr_to_sql_inner(expr)?;
134 let sql_low = self.expr_to_sql_inner(low)?;
135 let sql_high = self.expr_to_sql_inner(high)?;
136 Ok(ast::Expr::Nested(Box::new(self.between_op_to_sql(
137 sql_parser_expr,
138 *negated,
139 sql_low,
140 sql_high,
141 ))))
142 }
143 Expr::Column(col) => self.col_to_sql(col),
144 Expr::BinaryExpr(BinaryExpr { left, op, right }) => {
145 let l = self.expr_to_sql_inner(left.as_ref())?;
146 let r = self.expr_to_sql_inner(right.as_ref())?;
147 let op = self.op_to_sql(op)?;
148
149 Ok(ast::Expr::Nested(Box::new(self.binary_op_to_sql(l, r, op))))
150 }
151 Expr::Case(Case {
152 expr,
153 when_then_expr,
154 else_expr,
155 }) => {
156 let conditions = when_then_expr
157 .iter()
158 .map(|(cond, result)| {
159 Ok(CaseWhen {
160 condition: self.expr_to_sql_inner(cond)?,
161 result: self.expr_to_sql_inner(result)?,
162 })
163 })
164 .collect::<Result<Vec<CaseWhen>>>()?;
165
166 let operand = match expr.as_ref() {
167 Some(e) => match self.expr_to_sql_inner(e) {
168 Ok(sql_expr) => Some(Box::new(sql_expr)),
169 Err(_) => None,
170 },
171 None => None,
172 };
173 let else_result = match else_expr.as_ref() {
174 Some(e) => match self.expr_to_sql_inner(e) {
175 Ok(sql_expr) => Some(Box::new(sql_expr)),
176 Err(_) => None,
177 },
178 None => None,
179 };
180
181 Ok(ast::Expr::Case {
182 operand,
183 conditions,
184 else_result,
185 })
186 }
187 Expr::Cast(Cast { expr, data_type }) => {
188 Ok(self.cast_to_sql(expr, data_type)?)
189 }
190 Expr::Literal(value) => Ok(self.scalar_to_sql(value)?),
191 Expr::Alias(Alias { expr, name: _, .. }) => self.expr_to_sql_inner(expr),
192 Expr::WindowFunction(WindowFunction {
193 fun,
194 params:
195 WindowFunctionParams {
196 args,
197 partition_by,
198 order_by,
199 window_frame,
200 ..
201 },
202 }) => {
203 let func_name = fun.name();
204
205 let args = self.function_args_to_sql(args)?;
206
207 let units = match window_frame.units {
208 datafusion_expr::window_frame::WindowFrameUnits::Rows => {
209 ast::WindowFrameUnits::Rows
210 }
211 datafusion_expr::window_frame::WindowFrameUnits::Range => {
212 ast::WindowFrameUnits::Range
213 }
214 datafusion_expr::window_frame::WindowFrameUnits::Groups => {
215 ast::WindowFrameUnits::Groups
216 }
217 };
218
219 let order_by = order_by
220 .iter()
221 .map(|sort_expr| self.sort_to_sql(sort_expr))
222 .collect::<Result<Vec<_>>>()?;
223
224 let start_bound = self.convert_bound(&window_frame.start_bound)?;
225 let end_bound = self.convert_bound(&window_frame.end_bound)?;
226
227 let window_frame = if self.dialect.window_func_support_window_frame(
228 func_name,
229 &start_bound,
230 &end_bound,
231 ) {
232 Some(ast::WindowFrame {
233 units,
234 start_bound,
235 end_bound: Some(end_bound),
236 })
237 } else {
238 None
239 };
240
241 let over = Some(ast::WindowType::WindowSpec(ast::WindowSpec {
242 window_name: None,
243 partition_by: partition_by
244 .iter()
245 .map(|e| self.expr_to_sql_inner(e))
246 .collect::<Result<Vec<_>>>()?,
247 order_by,
248 window_frame,
249 }));
250
251 Ok(ast::Expr::Function(Function {
252 name: ObjectName::from(vec![Ident {
253 value: func_name.to_string(),
254 quote_style: None,
255 span: Span::empty(),
256 }]),
257 args: ast::FunctionArguments::List(ast::FunctionArgumentList {
258 duplicate_treatment: None,
259 args,
260 clauses: vec![],
261 }),
262 filter: None,
263 null_treatment: None,
264 over,
265 within_group: vec![],
266 parameters: ast::FunctionArguments::None,
267 uses_odbc_syntax: false,
268 }))
269 }
270 Expr::SimilarTo(Like {
271 negated,
272 expr,
273 pattern,
274 escape_char,
275 case_insensitive: _,
276 })
277 | Expr::Like(Like {
278 negated,
279 expr,
280 pattern,
281 escape_char,
282 case_insensitive: _,
283 }) => Ok(ast::Expr::Like {
284 negated: *negated,
285 expr: Box::new(self.expr_to_sql_inner(expr)?),
286 pattern: Box::new(self.expr_to_sql_inner(pattern)?),
287 escape_char: escape_char.map(|c| c.to_string()),
288 any: false,
289 }),
290 Expr::AggregateFunction(agg) => {
291 let func_name = agg.func.name();
292 let AggregateFunctionParams {
293 distinct,
294 args,
295 filter,
296 ..
297 } = &agg.params;
298
299 let args = self.function_args_to_sql(args)?;
300 let filter = match filter {
301 Some(filter) => Some(Box::new(self.expr_to_sql_inner(filter)?)),
302 None => None,
303 };
304 Ok(ast::Expr::Function(Function {
305 name: ObjectName::from(vec![Ident {
306 value: func_name.to_string(),
307 quote_style: None,
308 span: Span::empty(),
309 }]),
310 args: ast::FunctionArguments::List(ast::FunctionArgumentList {
311 duplicate_treatment: distinct
312 .then_some(ast::DuplicateTreatment::Distinct),
313 args,
314 clauses: vec![],
315 }),
316 filter,
317 null_treatment: None,
318 over: None,
319 within_group: vec![],
320 parameters: ast::FunctionArguments::None,
321 uses_odbc_syntax: false,
322 }))
323 }
324 Expr::ScalarSubquery(subq) => {
325 let sub_statement = self.plan_to_sql(subq.subquery.as_ref())?;
326 let sub_query = if let ast::Statement::Query(inner_query) = sub_statement
327 {
328 inner_query
329 } else {
330 return plan_err!(
331 "Subquery must be a Query, but found {sub_statement:?}"
332 );
333 };
334 Ok(ast::Expr::Subquery(sub_query))
335 }
336 Expr::InSubquery(insubq) => {
337 let inexpr = Box::new(self.expr_to_sql_inner(insubq.expr.as_ref())?);
338 let sub_statement =
339 self.plan_to_sql(insubq.subquery.subquery.as_ref())?;
340 let sub_query = if let ast::Statement::Query(inner_query) = sub_statement
341 {
342 inner_query
343 } else {
344 return plan_err!(
345 "Subquery must be a Query, but found {sub_statement:?}"
346 );
347 };
348 Ok(ast::Expr::InSubquery {
349 expr: inexpr,
350 subquery: sub_query,
351 negated: insubq.negated,
352 })
353 }
354 Expr::Exists(Exists { subquery, negated }) => {
355 let sub_statement = self.plan_to_sql(subquery.subquery.as_ref())?;
356 let sub_query = if let ast::Statement::Query(inner_query) = sub_statement
357 {
358 inner_query
359 } else {
360 return plan_err!(
361 "Subquery must be a Query, but found {sub_statement:?}"
362 );
363 };
364 Ok(ast::Expr::Exists {
365 subquery: sub_query,
366 negated: *negated,
367 })
368 }
369 Expr::IsNull(expr) => {
370 Ok(ast::Expr::IsNull(Box::new(self.expr_to_sql_inner(expr)?)))
371 }
372 Expr::IsNotNull(expr) => Ok(ast::Expr::IsNotNull(Box::new(
373 self.expr_to_sql_inner(expr)?,
374 ))),
375 Expr::IsTrue(expr) => {
376 Ok(ast::Expr::IsTrue(Box::new(self.expr_to_sql_inner(expr)?)))
377 }
378 Expr::IsNotTrue(expr) => Ok(ast::Expr::IsNotTrue(Box::new(
379 self.expr_to_sql_inner(expr)?,
380 ))),
381 Expr::IsFalse(expr) => {
382 Ok(ast::Expr::IsFalse(Box::new(self.expr_to_sql_inner(expr)?)))
383 }
384 Expr::IsNotFalse(expr) => Ok(ast::Expr::IsNotFalse(Box::new(
385 self.expr_to_sql_inner(expr)?,
386 ))),
387 Expr::IsUnknown(expr) => Ok(ast::Expr::IsUnknown(Box::new(
388 self.expr_to_sql_inner(expr)?,
389 ))),
390 Expr::IsNotUnknown(expr) => Ok(ast::Expr::IsNotUnknown(Box::new(
391 self.expr_to_sql_inner(expr)?,
392 ))),
393 Expr::Not(expr) => {
394 let sql_parser_expr = self.expr_to_sql_inner(expr)?;
395 Ok(AstExpr::UnaryOp {
396 op: UnaryOperator::Not,
397 expr: Box::new(sql_parser_expr),
398 })
399 }
400 Expr::Negative(expr) => {
401 let sql_parser_expr = self.expr_to_sql_inner(expr)?;
402 Ok(AstExpr::UnaryOp {
403 op: UnaryOperator::Minus,
404 expr: Box::new(sql_parser_expr),
405 })
406 }
407 Expr::ScalarVariable(_, ids) => {
408 if ids.is_empty() {
409 return internal_err!("Not a valid ScalarVariable");
410 }
411
412 Ok(if ids.len() == 1 {
413 ast::Expr::Identifier(
414 self.new_ident_without_quote_style(ids[0].to_string()),
415 )
416 } else {
417 ast::Expr::CompoundIdentifier(
418 ids.iter()
419 .map(|i| self.new_ident_without_quote_style(i.to_string()))
420 .collect(),
421 )
422 })
423 }
424 Expr::TryCast(TryCast { expr, data_type }) => {
425 let inner_expr = self.expr_to_sql_inner(expr)?;
426 Ok(ast::Expr::Cast {
427 kind: ast::CastKind::TryCast,
428 expr: Box::new(inner_expr),
429 data_type: self.arrow_dtype_to_ast_dtype(data_type)?,
430 format: None,
431 })
432 }
433 #[expect(deprecated)]
435 Expr::Wildcard { qualifier, .. } => {
436 let attached_token = AttachedToken::empty();
437 if let Some(qualifier) = qualifier {
438 let idents: Vec<Ident> =
439 qualifier.to_vec().into_iter().map(Ident::new).collect();
440 Ok(ast::Expr::QualifiedWildcard(
441 ObjectName::from(idents),
442 attached_token,
443 ))
444 } else {
445 Ok(ast::Expr::Wildcard(attached_token))
446 }
447 }
448 Expr::GroupingSet(grouping_set) => match grouping_set {
449 GroupingSet::GroupingSets(grouping_sets) => {
450 let expr_ast_sets = grouping_sets
451 .iter()
452 .map(|set| {
453 set.iter()
454 .map(|e| self.expr_to_sql_inner(e))
455 .collect::<Result<Vec<_>>>()
456 })
457 .collect::<Result<Vec<_>>>()?;
458
459 Ok(ast::Expr::GroupingSets(expr_ast_sets))
460 }
461 GroupingSet::Cube(cube) => {
462 let expr_ast_sets = cube
463 .iter()
464 .map(|e| {
465 let sql = self.expr_to_sql_inner(e)?;
466 Ok(vec![sql])
467 })
468 .collect::<Result<Vec<_>>>()?;
469 Ok(ast::Expr::Cube(expr_ast_sets))
470 }
471 GroupingSet::Rollup(rollup) => {
472 let expr_ast_sets: Vec<Vec<AstExpr>> = rollup
473 .iter()
474 .map(|e| {
475 let sql = self.expr_to_sql_inner(e)?;
476 Ok(vec![sql])
477 })
478 .collect::<Result<Vec<_>>>()?;
479 Ok(ast::Expr::Rollup(expr_ast_sets))
480 }
481 },
482 Expr::Placeholder(p) => {
483 Ok(ast::Expr::value(ast::Value::Placeholder(p.id.to_string())))
484 }
485 Expr::OuterReferenceColumn(_, col) => self.col_to_sql(col),
486 Expr::Unnest(unnest) => self.unnest_to_sql(unnest),
487 }
488 }
489
490 pub fn scalar_function_to_sql(
491 &self,
492 func_name: &str,
493 args: &[Expr],
494 ) -> Result<ast::Expr> {
495 match func_name {
496 "make_array" => self.make_array_to_sql(args),
497 "array_element" => self.array_element_to_sql(args),
498 "named_struct" => self.named_struct_to_sql(args),
499 "get_field" => self.get_field_to_sql(args),
500 "map" => self.map_to_sql(args),
501 _ => self.scalar_function_to_sql_internal(func_name, args),
503 }
504 }
505
506 fn scalar_function_to_sql_internal(
507 &self,
508 func_name: &str,
509 args: &[Expr],
510 ) -> Result<ast::Expr> {
511 let args = self.function_args_to_sql(args)?;
512 Ok(ast::Expr::Function(Function {
513 name: ObjectName::from(vec![Ident {
514 value: func_name.to_string(),
515 quote_style: None,
516 span: Span::empty(),
517 }]),
518 args: ast::FunctionArguments::List(ast::FunctionArgumentList {
519 duplicate_treatment: None,
520 args,
521 clauses: vec![],
522 }),
523 filter: None,
524 null_treatment: None,
525 over: None,
526 within_group: vec![],
527 parameters: ast::FunctionArguments::None,
528 uses_odbc_syntax: false,
529 }))
530 }
531
532 fn make_array_to_sql(&self, args: &[Expr]) -> Result<ast::Expr> {
533 let args = args
534 .iter()
535 .map(|e| self.expr_to_sql(e))
536 .collect::<Result<Vec<_>>>()?;
537 Ok(ast::Expr::Array(Array {
538 elem: args,
539 named: false,
540 }))
541 }
542
543 fn scalar_value_list_to_sql(&self, array: &ArrayRef) -> Result<ast::Expr> {
544 let mut elem = Vec::new();
545 for i in 0..array.len() {
546 let value = ScalarValue::try_from_array(&array, i)?;
547 elem.push(self.scalar_to_sql(&value)?);
548 }
549
550 Ok(ast::Expr::Array(Array { elem, named: false }))
551 }
552
553 fn array_element_to_sql(&self, args: &[Expr]) -> Result<ast::Expr> {
554 if args.len() != 2 {
555 return internal_err!("array_element must have exactly 2 arguments");
556 }
557 let array = self.expr_to_sql(&args[0])?;
558 let index = self.expr_to_sql(&args[1])?;
559 Ok(ast::Expr::CompoundFieldAccess {
560 root: Box::new(array),
561 access_chain: vec![ast::AccessExpr::Subscript(Subscript::Index { index })],
562 })
563 }
564
565 fn named_struct_to_sql(&self, args: &[Expr]) -> Result<ast::Expr> {
566 if args.len() % 2 != 0 {
567 return internal_err!("named_struct must have an even number of arguments");
568 }
569
570 let args = args
571 .chunks_exact(2)
572 .map(|chunk| {
573 let key = match &chunk[0] {
574 Expr::Literal(ScalarValue::Utf8(Some(s))) => self.new_ident_quoted_if_needs(s.to_string()),
575 _ => return internal_err!("named_struct expects even arguments to be strings, but received: {:?}", &chunk[0])
576 };
577
578 Ok(ast::DictionaryField {
579 key,
580 value: Box::new(self.expr_to_sql(&chunk[1])?),
581 })
582 })
583 .collect::<Result<Vec<_>>>()?;
584
585 Ok(ast::Expr::Dictionary(args))
586 }
587
588 fn get_field_to_sql(&self, args: &[Expr]) -> Result<ast::Expr> {
589 if args.len() != 2 {
590 return internal_err!("get_field must have exactly 2 arguments");
591 }
592
593 let mut id = match &args[0] {
594 Expr::Column(col) => match self.col_to_sql(col)? {
595 ast::Expr::Identifier(ident) => vec![ident],
596 ast::Expr::CompoundIdentifier(idents) => idents,
597 other => return internal_err!("expected col_to_sql to return an Identifier or CompoundIdentifier, but received: {:?}", other),
598 },
599 _ => return internal_err!("get_field expects first argument to be column, but received: {:?}", &args[0]),
600 };
601
602 let field = match &args[1] {
603 Expr::Literal(lit) => self.new_ident_quoted_if_needs(lit.to_string()),
604 _ => {
605 return internal_err!(
606 "get_field expects second argument to be a string, but received: {:?}",
607 &args[0]
608 )
609 }
610 };
611 id.push(field);
612
613 Ok(ast::Expr::CompoundIdentifier(id))
614 }
615
616 fn map_to_sql(&self, args: &[Expr]) -> Result<ast::Expr> {
617 if args.len() != 2 {
618 return internal_err!("map must have exactly 2 arguments");
619 }
620
621 let ast::Expr::Array(Array { elem: keys, .. }) = self.expr_to_sql(&args[0])?
622 else {
623 return internal_err!(
624 "map expects first argument to be an array, but received: {:?}",
625 &args[0]
626 );
627 };
628
629 let ast::Expr::Array(Array { elem: values, .. }) = self.expr_to_sql(&args[1])?
630 else {
631 return internal_err!(
632 "map expects second argument to be an array, but received: {:?}",
633 &args[1]
634 );
635 };
636
637 let entries = keys
638 .into_iter()
639 .zip(values)
640 .map(|(key, value)| ast::MapEntry {
641 key: Box::new(key),
642 value: Box::new(value),
643 })
644 .collect();
645
646 Ok(ast::Expr::Map(ast::Map { entries }))
647 }
648
649 pub fn sort_to_sql(&self, sort: &Sort) -> Result<ast::OrderByExpr> {
650 let Sort {
651 expr,
652 asc,
653 nulls_first,
654 } = sort;
655 let sql_parser_expr = self.expr_to_sql(expr)?;
656
657 let nulls_first = if self.dialect.supports_nulls_first_in_sort() {
658 Some(*nulls_first)
659 } else {
660 None
661 };
662
663 Ok(ast::OrderByExpr {
664 expr: sql_parser_expr,
665 options: OrderByOptions {
666 asc: Some(*asc),
667 nulls_first,
668 },
669 with_fill: None,
670 })
671 }
672
673 fn ast_type_for_date64_in_cast(&self) -> ast::DataType {
674 if self.dialect.use_timestamp_for_date64() {
675 ast::DataType::Timestamp(None, TimezoneInfo::None)
676 } else {
677 ast::DataType::Datetime(None)
678 }
679 }
680
681 pub fn col_to_sql(&self, col: &Column) -> Result<ast::Expr> {
682 if let Some(table_ref) = &col.relation {
683 let mut id = if self.dialect.full_qualified_col() {
684 table_ref.to_vec()
685 } else {
686 vec![table_ref.table().to_string()]
687 };
688 id.push(col.name.to_string());
689 return Ok(ast::Expr::CompoundIdentifier(
690 id.iter()
691 .map(|i| self.new_ident_quoted_if_needs(i.to_string()))
692 .collect(),
693 ));
694 }
695 Ok(ast::Expr::Identifier(
696 self.new_ident_quoted_if_needs(col.name.to_string()),
697 ))
698 }
699
700 fn convert_bound(
701 &self,
702 bound: &datafusion_expr::window_frame::WindowFrameBound,
703 ) -> Result<ast::WindowFrameBound> {
704 match bound {
705 datafusion_expr::window_frame::WindowFrameBound::Preceding(val) => {
706 Ok(ast::WindowFrameBound::Preceding({
707 let val = self.scalar_to_sql(val)?;
708 if let ast::Expr::Value(ValueWithSpan {
709 value: ast::Value::Null,
710 span: _,
711 }) = &val
712 {
713 None
714 } else {
715 Some(Box::new(val))
716 }
717 }))
718 }
719 datafusion_expr::window_frame::WindowFrameBound::Following(val) => {
720 Ok(ast::WindowFrameBound::Following({
721 let val = self.scalar_to_sql(val)?;
722 if let ast::Expr::Value(ValueWithSpan {
723 value: ast::Value::Null,
724 span: _,
725 }) = &val
726 {
727 None
728 } else {
729 Some(Box::new(val))
730 }
731 }))
732 }
733 datafusion_expr::window_frame::WindowFrameBound::CurrentRow => {
734 Ok(ast::WindowFrameBound::CurrentRow)
735 }
736 }
737 }
738
739 pub(crate) fn function_args_to_sql(
740 &self,
741 args: &[Expr],
742 ) -> Result<Vec<ast::FunctionArg>> {
743 args.iter()
744 .map(|e| {
745 #[expect(deprecated)]
746 if matches!(
747 e,
748 Expr::Wildcard {
749 qualifier: None,
750 ..
751 }
752 ) {
753 Ok(ast::FunctionArg::Unnamed(ast::FunctionArgExpr::Wildcard))
754 } else {
755 self.expr_to_sql(e)
756 .map(|e| ast::FunctionArg::Unnamed(ast::FunctionArgExpr::Expr(e)))
757 }
758 })
759 .collect::<Result<Vec<_>>>()
760 }
761
762 pub(super) fn new_ident_quoted_if_needs(&self, ident: String) -> Ident {
764 let quote_style = self.dialect.identifier_quote_style(&ident);
765 Ident {
766 value: ident,
767 quote_style,
768 span: Span::empty(),
769 }
770 }
771
772 pub(super) fn new_ident_without_quote_style(&self, str: String) -> Ident {
773 Ident {
774 value: str,
775 quote_style: None,
776 span: Span::empty(),
777 }
778 }
779
780 pub(super) fn binary_op_to_sql(
781 &self,
782 lhs: ast::Expr,
783 rhs: ast::Expr,
784 op: BinaryOperator,
785 ) -> ast::Expr {
786 ast::Expr::BinaryOp {
787 left: Box::new(lhs),
788 op,
789 right: Box::new(rhs),
790 }
791 }
792
793 fn remove_unnecessary_nesting(
801 &self,
802 expr: ast::Expr,
803 left_op: &BinaryOperator,
804 right_op: &BinaryOperator,
805 ) -> ast::Expr {
806 match expr {
807 ast::Expr::Nested(nested) => {
808 let surrounding_precedence = self
809 .sql_op_precedence(left_op)
810 .max(self.sql_op_precedence(right_op));
811
812 let inner_precedence = self.inner_precedence(&nested);
813
814 let not_associative =
815 matches!(left_op, BinaryOperator::Minus | BinaryOperator::Divide);
816
817 if inner_precedence == surrounding_precedence && not_associative {
818 ast::Expr::Nested(Box::new(
819 self.remove_unnecessary_nesting(*nested, LOWEST, LOWEST),
820 ))
821 } else if inner_precedence >= surrounding_precedence {
822 self.remove_unnecessary_nesting(*nested, left_op, right_op)
823 } else {
824 ast::Expr::Nested(Box::new(
825 self.remove_unnecessary_nesting(*nested, LOWEST, LOWEST),
826 ))
827 }
828 }
829 ast::Expr::BinaryOp { left, op, right } => ast::Expr::BinaryOp {
830 left: Box::new(self.remove_unnecessary_nesting(*left, left_op, &op)),
831 right: Box::new(self.remove_unnecessary_nesting(*right, &op, right_op)),
832 op,
833 },
834 ast::Expr::IsTrue(expr) => ast::Expr::IsTrue(Box::new(
835 self.remove_unnecessary_nesting(*expr, left_op, IS),
836 )),
837 ast::Expr::IsNotTrue(expr) => ast::Expr::IsNotTrue(Box::new(
838 self.remove_unnecessary_nesting(*expr, left_op, IS),
839 )),
840 ast::Expr::IsFalse(expr) => ast::Expr::IsFalse(Box::new(
841 self.remove_unnecessary_nesting(*expr, left_op, IS),
842 )),
843 ast::Expr::IsNotFalse(expr) => ast::Expr::IsNotFalse(Box::new(
844 self.remove_unnecessary_nesting(*expr, left_op, IS),
845 )),
846 ast::Expr::IsNull(expr) => ast::Expr::IsNull(Box::new(
847 self.remove_unnecessary_nesting(*expr, left_op, IS),
848 )),
849 ast::Expr::IsNotNull(expr) => ast::Expr::IsNotNull(Box::new(
850 self.remove_unnecessary_nesting(*expr, left_op, IS),
851 )),
852 ast::Expr::IsUnknown(expr) => ast::Expr::IsUnknown(Box::new(
853 self.remove_unnecessary_nesting(*expr, left_op, IS),
854 )),
855 ast::Expr::IsNotUnknown(expr) => ast::Expr::IsNotUnknown(Box::new(
856 self.remove_unnecessary_nesting(*expr, left_op, IS),
857 )),
858 _ => expr,
859 }
860 }
861
862 fn inner_precedence(&self, expr: &ast::Expr) -> u8 {
863 match expr {
864 ast::Expr::Nested(_) | ast::Expr::Identifier(_) | ast::Expr::Value(_) => 100,
865 ast::Expr::BinaryOp { op, .. } => self.sql_op_precedence(op),
866 ast::Expr::Between { .. } => {
869 self.sql_op_precedence(&BinaryOperator::PGLikeMatch)
870 }
871 _ => 0,
872 }
873 }
874
875 pub(super) fn between_op_to_sql(
876 &self,
877 expr: ast::Expr,
878 negated: bool,
879 low: ast::Expr,
880 high: ast::Expr,
881 ) -> ast::Expr {
882 ast::Expr::Between {
883 expr: Box::new(expr),
884 negated,
885 low: Box::new(low),
886 high: Box::new(high),
887 }
888 }
889
890 fn sql_op_precedence(&self, op: &BinaryOperator) -> u8 {
891 match self.sql_to_op(op) {
892 Ok(op) => op.precedence(),
893 Err(_) => 0,
894 }
895 }
896
897 fn sql_to_op(&self, op: &BinaryOperator) -> Result<Operator> {
898 match op {
899 BinaryOperator::Eq => Ok(Operator::Eq),
900 BinaryOperator::NotEq => Ok(Operator::NotEq),
901 BinaryOperator::Lt => Ok(Operator::Lt),
902 BinaryOperator::LtEq => Ok(Operator::LtEq),
903 BinaryOperator::Gt => Ok(Operator::Gt),
904 BinaryOperator::GtEq => Ok(Operator::GtEq),
905 BinaryOperator::Plus => Ok(Operator::Plus),
906 BinaryOperator::Minus => Ok(Operator::Minus),
907 BinaryOperator::Multiply => Ok(Operator::Multiply),
908 BinaryOperator::Divide => Ok(Operator::Divide),
909 BinaryOperator::Modulo => Ok(Operator::Modulo),
910 BinaryOperator::And => Ok(Operator::And),
911 BinaryOperator::Or => Ok(Operator::Or),
912 BinaryOperator::PGRegexMatch => Ok(Operator::RegexMatch),
913 BinaryOperator::PGRegexIMatch => Ok(Operator::RegexIMatch),
914 BinaryOperator::PGRegexNotMatch => Ok(Operator::RegexNotMatch),
915 BinaryOperator::PGRegexNotIMatch => Ok(Operator::RegexNotIMatch),
916 BinaryOperator::PGILikeMatch => Ok(Operator::ILikeMatch),
917 BinaryOperator::PGNotLikeMatch => Ok(Operator::NotLikeMatch),
918 BinaryOperator::PGLikeMatch => Ok(Operator::LikeMatch),
919 BinaryOperator::PGNotILikeMatch => Ok(Operator::NotILikeMatch),
920 BinaryOperator::BitwiseAnd => Ok(Operator::BitwiseAnd),
921 BinaryOperator::BitwiseOr => Ok(Operator::BitwiseOr),
922 BinaryOperator::BitwiseXor => Ok(Operator::BitwiseXor),
923 BinaryOperator::PGBitwiseShiftRight => Ok(Operator::BitwiseShiftRight),
924 BinaryOperator::PGBitwiseShiftLeft => Ok(Operator::BitwiseShiftLeft),
925 BinaryOperator::StringConcat => Ok(Operator::StringConcat),
926 BinaryOperator::AtArrow => Ok(Operator::AtArrow),
927 BinaryOperator::ArrowAt => Ok(Operator::ArrowAt),
928 BinaryOperator::Arrow => Ok(Operator::Arrow),
929 BinaryOperator::LongArrow => Ok(Operator::LongArrow),
930 BinaryOperator::HashArrow => Ok(Operator::HashArrow),
931 BinaryOperator::HashLongArrow => Ok(Operator::HashLongArrow),
932 BinaryOperator::AtAt => Ok(Operator::AtAt),
933 BinaryOperator::DuckIntegerDivide | BinaryOperator::MyIntegerDivide => {
934 Ok(Operator::IntegerDivide)
935 }
936 BinaryOperator::HashMinus => Ok(Operator::HashMinus),
937 BinaryOperator::AtQuestion => Ok(Operator::AtQuestion),
938 BinaryOperator::Question => Ok(Operator::Question),
939 BinaryOperator::QuestionAnd => Ok(Operator::QuestionAnd),
940 BinaryOperator::QuestionPipe => Ok(Operator::QuestionPipe),
941 _ => not_impl_err!("unsupported operation: {op:?}"),
942 }
943 }
944
945 fn op_to_sql(&self, op: &Operator) -> Result<BinaryOperator> {
946 match op {
947 Operator::Eq => Ok(BinaryOperator::Eq),
948 Operator::NotEq => Ok(BinaryOperator::NotEq),
949 Operator::Lt => Ok(BinaryOperator::Lt),
950 Operator::LtEq => Ok(BinaryOperator::LtEq),
951 Operator::Gt => Ok(BinaryOperator::Gt),
952 Operator::GtEq => Ok(BinaryOperator::GtEq),
953 Operator::Plus => Ok(BinaryOperator::Plus),
954 Operator::Minus => Ok(BinaryOperator::Minus),
955 Operator::Multiply => Ok(BinaryOperator::Multiply),
956 Operator::Divide => Ok(self.dialect.division_operator()),
957 Operator::Modulo => Ok(BinaryOperator::Modulo),
958 Operator::And => Ok(BinaryOperator::And),
959 Operator::Or => Ok(BinaryOperator::Or),
960 Operator::IsDistinctFrom => not_impl_err!("unsupported operation: {op:?}"),
961 Operator::IsNotDistinctFrom => not_impl_err!("unsupported operation: {op:?}"),
962 Operator::RegexMatch => Ok(BinaryOperator::PGRegexMatch),
963 Operator::RegexIMatch => Ok(BinaryOperator::PGRegexIMatch),
964 Operator::RegexNotMatch => Ok(BinaryOperator::PGRegexNotMatch),
965 Operator::RegexNotIMatch => Ok(BinaryOperator::PGRegexNotIMatch),
966 Operator::ILikeMatch => Ok(BinaryOperator::PGILikeMatch),
967 Operator::NotLikeMatch => Ok(BinaryOperator::PGNotLikeMatch),
968 Operator::LikeMatch => Ok(BinaryOperator::PGLikeMatch),
969 Operator::NotILikeMatch => Ok(BinaryOperator::PGNotILikeMatch),
970 Operator::BitwiseAnd => Ok(BinaryOperator::BitwiseAnd),
971 Operator::BitwiseOr => Ok(BinaryOperator::BitwiseOr),
972 Operator::BitwiseXor => Ok(BinaryOperator::BitwiseXor),
973 Operator::BitwiseShiftRight => Ok(BinaryOperator::PGBitwiseShiftRight),
974 Operator::BitwiseShiftLeft => Ok(BinaryOperator::PGBitwiseShiftLeft),
975 Operator::StringConcat => Ok(BinaryOperator::StringConcat),
976 Operator::AtArrow => Ok(BinaryOperator::AtArrow),
977 Operator::ArrowAt => Ok(BinaryOperator::ArrowAt),
978 Operator::Arrow => Ok(BinaryOperator::Arrow),
979 Operator::LongArrow => Ok(BinaryOperator::LongArrow),
980 Operator::HashArrow => Ok(BinaryOperator::HashArrow),
981 Operator::HashLongArrow => Ok(BinaryOperator::HashLongArrow),
982 Operator::AtAt => Ok(BinaryOperator::AtAt),
983 Operator::IntegerDivide => Ok(BinaryOperator::DuckIntegerDivide),
984 Operator::HashMinus => Ok(BinaryOperator::HashMinus),
985 Operator::AtQuestion => Ok(BinaryOperator::AtQuestion),
986 Operator::Question => Ok(BinaryOperator::Question),
987 Operator::QuestionAnd => Ok(BinaryOperator::QuestionAnd),
988 Operator::QuestionPipe => Ok(BinaryOperator::QuestionPipe),
989 }
990 }
991
992 fn handle_timestamp<T: ArrowTemporalType>(
993 &self,
994 v: &ScalarValue,
995 tz: &Option<Arc<str>>,
996 ) -> Result<ast::Expr>
997 where
998 i64: From<T::Native>,
999 {
1000 let ts = if let Some(tz) = tz {
1001 v.to_array()?
1002 .as_any()
1003 .downcast_ref::<PrimitiveArray<T>>()
1004 .ok_or(internal_datafusion_err!(
1005 "Failed to downcast type {v:?} to arrow array"
1006 ))?
1007 .value_as_datetime_with_tz(0, tz.parse()?)
1008 .ok_or(internal_datafusion_err!(
1009 "Unable to convert {v:?} to DateTime"
1010 ))?
1011 .to_string()
1012 } else {
1013 v.to_array()?
1014 .as_any()
1015 .downcast_ref::<PrimitiveArray<T>>()
1016 .ok_or(internal_datafusion_err!(
1017 "Failed to downcast type {v:?} to arrow array"
1018 ))?
1019 .value_as_datetime(0)
1020 .ok_or(internal_datafusion_err!(
1021 "Unable to convert {v:?} to DateTime"
1022 ))?
1023 .to_string()
1024 };
1025
1026 let time_unit = match T::DATA_TYPE {
1027 DataType::Timestamp(unit, _) => unit,
1028 _ => {
1029 return Err(internal_datafusion_err!(
1030 "Expected Timestamp, got {:?}",
1031 T::DATA_TYPE
1032 ))
1033 }
1034 };
1035
1036 Ok(ast::Expr::Cast {
1037 kind: ast::CastKind::Cast,
1038 expr: Box::new(ast::Expr::value(SingleQuotedString(ts))),
1039 data_type: self.dialect.timestamp_cast_dtype(&time_unit, &None),
1040 format: None,
1041 })
1042 }
1043
1044 fn handle_time<T: ArrowTemporalType>(&self, v: &ScalarValue) -> Result<ast::Expr>
1045 where
1046 i64: From<T::Native>,
1047 {
1048 let time = v
1049 .to_array()?
1050 .as_any()
1051 .downcast_ref::<PrimitiveArray<T>>()
1052 .ok_or(internal_datafusion_err!(
1053 "Failed to downcast type {v:?} to arrow array"
1054 ))?
1055 .value_as_time(0)
1056 .ok_or(internal_datafusion_err!("Unable to convert {v:?} to Time"))?
1057 .to_string();
1058 Ok(ast::Expr::Cast {
1059 kind: ast::CastKind::Cast,
1060 expr: Box::new(ast::Expr::value(SingleQuotedString(time))),
1061 data_type: ast::DataType::Time(None, TimezoneInfo::None),
1062 format: None,
1063 })
1064 }
1065
1066 fn cast_to_sql(&self, expr: &Expr, data_type: &DataType) -> Result<ast::Expr> {
1069 let inner_expr = self.expr_to_sql_inner(expr)?;
1070 match inner_expr {
1071 ast::Expr::Value(_) => match data_type {
1072 DataType::Dictionary(_, _) | DataType::Binary | DataType::BinaryView => {
1073 Ok(inner_expr)
1074 }
1075 _ => Ok(ast::Expr::Cast {
1076 kind: ast::CastKind::Cast,
1077 expr: Box::new(inner_expr),
1078 data_type: self.arrow_dtype_to_ast_dtype(data_type)?,
1079 format: None,
1080 }),
1081 },
1082 _ => Ok(ast::Expr::Cast {
1083 kind: ast::CastKind::Cast,
1084 expr: Box::new(inner_expr),
1085 data_type: self.arrow_dtype_to_ast_dtype(data_type)?,
1086 format: None,
1087 }),
1088 }
1089 }
1090
1091 fn scalar_to_sql(&self, v: &ScalarValue) -> Result<ast::Expr> {
1094 match v {
1095 ScalarValue::Null => Ok(ast::Expr::value(ast::Value::Null)),
1096 ScalarValue::Boolean(Some(b)) => {
1097 Ok(ast::Expr::value(ast::Value::Boolean(b.to_owned())))
1098 }
1099 ScalarValue::Boolean(None) => Ok(ast::Expr::value(ast::Value::Null)),
1100 ScalarValue::Float16(Some(f)) => {
1101 Ok(ast::Expr::value(ast::Value::Number(f.to_string(), false)))
1102 }
1103 ScalarValue::Float16(None) => Ok(ast::Expr::value(ast::Value::Null)),
1104 ScalarValue::Float32(Some(f)) => {
1105 let f_val = match f.fract() {
1106 0.0 => format!("{:.1}", f),
1107 _ => format!("{}", f),
1108 };
1109 Ok(ast::Expr::value(ast::Value::Number(f_val, false)))
1110 }
1111 ScalarValue::Float32(None) => Ok(ast::Expr::value(ast::Value::Null)),
1112 ScalarValue::Float64(Some(f)) => {
1113 let f_val = match f.fract() {
1114 0.0 => format!("{:.1}", f),
1115 _ => format!("{}", f),
1116 };
1117 Ok(ast::Expr::value(ast::Value::Number(f_val, false)))
1118 }
1119 ScalarValue::Float64(None) => Ok(ast::Expr::value(ast::Value::Null)),
1120 ScalarValue::Decimal128(Some(value), precision, scale) => {
1121 Ok(ast::Expr::value(ast::Value::Number(
1122 Decimal128Type::format_decimal(*value, *precision, *scale),
1123 false,
1124 )))
1125 }
1126 ScalarValue::Decimal128(None, ..) => Ok(ast::Expr::value(ast::Value::Null)),
1127 ScalarValue::Decimal256(Some(value), precision, scale) => {
1128 Ok(ast::Expr::value(ast::Value::Number(
1129 Decimal256Type::format_decimal(*value, *precision, *scale),
1130 false,
1131 )))
1132 }
1133 ScalarValue::Decimal256(None, ..) => Ok(ast::Expr::value(ast::Value::Null)),
1134 ScalarValue::Int8(Some(i)) => {
1135 Ok(ast::Expr::value(ast::Value::Number(i.to_string(), false)))
1136 }
1137 ScalarValue::Int8(None) => Ok(ast::Expr::value(ast::Value::Null)),
1138 ScalarValue::Int16(Some(i)) => {
1139 Ok(ast::Expr::value(ast::Value::Number(i.to_string(), false)))
1140 }
1141 ScalarValue::Int16(None) => Ok(ast::Expr::value(ast::Value::Null)),
1142 ScalarValue::Int32(Some(i)) => {
1143 Ok(ast::Expr::value(ast::Value::Number(i.to_string(), false)))
1144 }
1145 ScalarValue::Int32(None) => Ok(ast::Expr::value(ast::Value::Null)),
1146 ScalarValue::Int64(Some(i)) => {
1147 Ok(ast::Expr::value(ast::Value::Number(i.to_string(), false)))
1148 }
1149 ScalarValue::Int64(None) => Ok(ast::Expr::value(ast::Value::Null)),
1150 ScalarValue::UInt8(Some(ui)) => {
1151 Ok(ast::Expr::value(ast::Value::Number(ui.to_string(), false)))
1152 }
1153 ScalarValue::UInt8(None) => Ok(ast::Expr::value(ast::Value::Null)),
1154 ScalarValue::UInt16(Some(ui)) => {
1155 Ok(ast::Expr::value(ast::Value::Number(ui.to_string(), false)))
1156 }
1157 ScalarValue::UInt16(None) => Ok(ast::Expr::value(ast::Value::Null)),
1158 ScalarValue::UInt32(Some(ui)) => {
1159 Ok(ast::Expr::value(ast::Value::Number(ui.to_string(), false)))
1160 }
1161 ScalarValue::UInt32(None) => Ok(ast::Expr::value(ast::Value::Null)),
1162 ScalarValue::UInt64(Some(ui)) => {
1163 Ok(ast::Expr::value(ast::Value::Number(ui.to_string(), false)))
1164 }
1165 ScalarValue::UInt64(None) => Ok(ast::Expr::value(ast::Value::Null)),
1166 ScalarValue::Utf8(Some(str)) => {
1167 Ok(ast::Expr::value(SingleQuotedString(str.to_string())))
1168 }
1169 ScalarValue::Utf8(None) => Ok(ast::Expr::value(ast::Value::Null)),
1170 ScalarValue::Utf8View(Some(str)) => {
1171 Ok(ast::Expr::value(SingleQuotedString(str.to_string())))
1172 }
1173 ScalarValue::Utf8View(None) => Ok(ast::Expr::value(ast::Value::Null)),
1174 ScalarValue::LargeUtf8(Some(str)) => {
1175 Ok(ast::Expr::value(SingleQuotedString(str.to_string())))
1176 }
1177 ScalarValue::LargeUtf8(None) => Ok(ast::Expr::value(ast::Value::Null)),
1178 ScalarValue::Binary(Some(_)) => not_impl_err!("Unsupported scalar: {v:?}"),
1179 ScalarValue::Binary(None) => Ok(ast::Expr::value(ast::Value::Null)),
1180 ScalarValue::BinaryView(Some(_)) => {
1181 not_impl_err!("Unsupported scalar: {v:?}")
1182 }
1183 ScalarValue::BinaryView(None) => Ok(ast::Expr::value(ast::Value::Null)),
1184 ScalarValue::FixedSizeBinary(..) => {
1185 not_impl_err!("Unsupported scalar: {v:?}")
1186 }
1187 ScalarValue::LargeBinary(Some(_)) => {
1188 not_impl_err!("Unsupported scalar: {v:?}")
1189 }
1190 ScalarValue::LargeBinary(None) => Ok(ast::Expr::value(ast::Value::Null)),
1191 ScalarValue::FixedSizeList(a) => self.scalar_value_list_to_sql(a.values()),
1192 ScalarValue::List(a) => self.scalar_value_list_to_sql(a.values()),
1193 ScalarValue::LargeList(a) => self.scalar_value_list_to_sql(a.values()),
1194 ScalarValue::Date32(Some(_)) => {
1195 let date = v
1196 .to_array()?
1197 .as_any()
1198 .downcast_ref::<Date32Array>()
1199 .ok_or(internal_datafusion_err!(
1200 "Unable to downcast to Date32 from Date32 scalar"
1201 ))?
1202 .value_as_date(0)
1203 .ok_or(internal_datafusion_err!(
1204 "Unable to convert Date32 to NaiveDate"
1205 ))?;
1206
1207 Ok(ast::Expr::Cast {
1208 kind: ast::CastKind::Cast,
1209 expr: Box::new(ast::Expr::value(SingleQuotedString(
1210 date.to_string(),
1211 ))),
1212 data_type: ast::DataType::Date,
1213 format: None,
1214 })
1215 }
1216 ScalarValue::Date32(None) => Ok(ast::Expr::value(ast::Value::Null)),
1217 ScalarValue::Date64(Some(_)) => {
1218 let datetime = v
1219 .to_array()?
1220 .as_any()
1221 .downcast_ref::<Date64Array>()
1222 .ok_or(internal_datafusion_err!(
1223 "Unable to downcast to Date64 from Date64 scalar"
1224 ))?
1225 .value_as_datetime(0)
1226 .ok_or(internal_datafusion_err!(
1227 "Unable to convert Date64 to NaiveDateTime"
1228 ))?;
1229
1230 Ok(ast::Expr::Cast {
1231 kind: ast::CastKind::Cast,
1232 expr: Box::new(ast::Expr::value(SingleQuotedString(
1233 datetime.to_string(),
1234 ))),
1235 data_type: self.ast_type_for_date64_in_cast(),
1236 format: None,
1237 })
1238 }
1239 ScalarValue::Date64(None) => Ok(ast::Expr::value(ast::Value::Null)),
1240 ScalarValue::Time32Second(Some(_t)) => {
1241 self.handle_time::<Time32SecondType>(v)
1242 }
1243 ScalarValue::Time32Second(None) => Ok(ast::Expr::value(ast::Value::Null)),
1244 ScalarValue::Time32Millisecond(Some(_t)) => {
1245 self.handle_time::<Time32MillisecondType>(v)
1246 }
1247 ScalarValue::Time32Millisecond(None) => {
1248 Ok(ast::Expr::value(ast::Value::Null))
1249 }
1250 ScalarValue::Time64Microsecond(Some(_t)) => {
1251 self.handle_time::<Time64MicrosecondType>(v)
1252 }
1253 ScalarValue::Time64Microsecond(None) => {
1254 Ok(ast::Expr::value(ast::Value::Null))
1255 }
1256 ScalarValue::Time64Nanosecond(Some(_t)) => {
1257 self.handle_time::<Time64NanosecondType>(v)
1258 }
1259 ScalarValue::Time64Nanosecond(None) => Ok(ast::Expr::value(ast::Value::Null)),
1260 ScalarValue::TimestampSecond(Some(_ts), tz) => {
1261 self.handle_timestamp::<TimestampSecondType>(v, tz)
1262 }
1263 ScalarValue::TimestampSecond(None, _) => {
1264 Ok(ast::Expr::value(ast::Value::Null))
1265 }
1266 ScalarValue::TimestampMillisecond(Some(_ts), tz) => {
1267 self.handle_timestamp::<TimestampMillisecondType>(v, tz)
1268 }
1269 ScalarValue::TimestampMillisecond(None, _) => {
1270 Ok(ast::Expr::value(ast::Value::Null))
1271 }
1272 ScalarValue::TimestampMicrosecond(Some(_ts), tz) => {
1273 self.handle_timestamp::<TimestampMicrosecondType>(v, tz)
1274 }
1275 ScalarValue::TimestampMicrosecond(None, _) => {
1276 Ok(ast::Expr::value(ast::Value::Null))
1277 }
1278 ScalarValue::TimestampNanosecond(Some(_ts), tz) => {
1279 self.handle_timestamp::<TimestampNanosecondType>(v, tz)
1280 }
1281 ScalarValue::TimestampNanosecond(None, _) => {
1282 Ok(ast::Expr::value(ast::Value::Null))
1283 }
1284 ScalarValue::IntervalYearMonth(Some(_))
1285 | ScalarValue::IntervalDayTime(Some(_))
1286 | ScalarValue::IntervalMonthDayNano(Some(_)) => {
1287 self.interval_scalar_to_sql(v)
1288 }
1289 ScalarValue::IntervalYearMonth(None) => {
1290 Ok(ast::Expr::value(ast::Value::Null))
1291 }
1292 ScalarValue::IntervalDayTime(None) => Ok(ast::Expr::value(ast::Value::Null)),
1293 ScalarValue::IntervalMonthDayNano(None) => {
1294 Ok(ast::Expr::value(ast::Value::Null))
1295 }
1296 ScalarValue::DurationSecond(Some(_d)) => {
1297 not_impl_err!("Unsupported scalar: {v:?}")
1298 }
1299 ScalarValue::DurationSecond(None) => Ok(ast::Expr::value(ast::Value::Null)),
1300 ScalarValue::DurationMillisecond(Some(_d)) => {
1301 not_impl_err!("Unsupported scalar: {v:?}")
1302 }
1303 ScalarValue::DurationMillisecond(None) => {
1304 Ok(ast::Expr::value(ast::Value::Null))
1305 }
1306 ScalarValue::DurationMicrosecond(Some(_d)) => {
1307 not_impl_err!("Unsupported scalar: {v:?}")
1308 }
1309 ScalarValue::DurationMicrosecond(None) => {
1310 Ok(ast::Expr::value(ast::Value::Null))
1311 }
1312 ScalarValue::DurationNanosecond(Some(_d)) => {
1313 not_impl_err!("Unsupported scalar: {v:?}")
1314 }
1315 ScalarValue::DurationNanosecond(None) => {
1316 Ok(ast::Expr::value(ast::Value::Null))
1317 }
1318 ScalarValue::Struct(_) => not_impl_err!("Unsupported scalar: {v:?}"),
1319 ScalarValue::Map(_) => not_impl_err!("Unsupported scalar: {v:?}"),
1320 ScalarValue::Union(..) => not_impl_err!("Unsupported scalar: {v:?}"),
1321 ScalarValue::Dictionary(_k, v) => self.scalar_to_sql(v),
1322 }
1323 }
1324
1325 fn interval_to_mysql_expr(
1332 &self,
1333 months: i32,
1334 days: i32,
1335 microseconds: i64,
1336 ) -> Result<ast::Expr> {
1337 if months != 0 && days == 0 && microseconds == 0 {
1339 let interval = Interval {
1340 value: Box::new(ast::Expr::value(ast::Value::Number(
1341 months.to_string(),
1342 false,
1343 ))),
1344 leading_field: Some(ast::DateTimeField::Month),
1345 leading_precision: None,
1346 last_field: None,
1347 fractional_seconds_precision: None,
1348 };
1349 return Ok(ast::Expr::Interval(interval));
1350 } else if months != 0 {
1351 return not_impl_err!("Unsupported Interval scalar with both Month and DayTime for IntervalStyle::MySQL");
1352 }
1353
1354 if microseconds == 0 {
1356 let interval = Interval {
1357 value: Box::new(ast::Expr::value(ast::Value::Number(
1358 days.to_string(),
1359 false,
1360 ))),
1361 leading_field: Some(ast::DateTimeField::Day),
1362 leading_precision: None,
1363 last_field: None,
1364 fractional_seconds_precision: None,
1365 };
1366 return Ok(ast::Expr::Interval(interval));
1367 }
1368
1369 let microseconds = microseconds + (days as i64 * 24 * 60 * 60 * 1_000_000);
1372
1373 if microseconds % 1_000_000 != 0 {
1374 let interval = Interval {
1375 value: Box::new(ast::Expr::value(ast::Value::Number(
1376 microseconds.to_string(),
1377 false,
1378 ))),
1379 leading_field: Some(ast::DateTimeField::Microsecond),
1380 leading_precision: None,
1381 last_field: None,
1382 fractional_seconds_precision: None,
1383 };
1384 return Ok(ast::Expr::Interval(interval));
1385 }
1386
1387 let secs = microseconds / 1_000_000;
1388
1389 if secs % 60 != 0 {
1390 let interval = Interval {
1391 value: Box::new(ast::Expr::value(ast::Value::Number(
1392 secs.to_string(),
1393 false,
1394 ))),
1395 leading_field: Some(ast::DateTimeField::Second),
1396 leading_precision: None,
1397 last_field: None,
1398 fractional_seconds_precision: None,
1399 };
1400 return Ok(ast::Expr::Interval(interval));
1401 }
1402
1403 let mins = secs / 60;
1404
1405 if mins % 60 != 0 {
1406 let interval = Interval {
1407 value: Box::new(ast::Expr::value(ast::Value::Number(
1408 mins.to_string(),
1409 false,
1410 ))),
1411 leading_field: Some(ast::DateTimeField::Minute),
1412 leading_precision: None,
1413 last_field: None,
1414 fractional_seconds_precision: None,
1415 };
1416 return Ok(ast::Expr::Interval(interval));
1417 }
1418
1419 let hours = mins / 60;
1420
1421 if hours % 24 != 0 {
1422 let interval = Interval {
1423 value: Box::new(ast::Expr::value(ast::Value::Number(
1424 hours.to_string(),
1425 false,
1426 ))),
1427 leading_field: Some(ast::DateTimeField::Hour),
1428 leading_precision: None,
1429 last_field: None,
1430 fractional_seconds_precision: None,
1431 };
1432 return Ok(ast::Expr::Interval(interval));
1433 }
1434
1435 let days = hours / 24;
1436
1437 let interval = Interval {
1438 value: Box::new(ast::Expr::value(ast::Value::Number(
1439 days.to_string(),
1440 false,
1441 ))),
1442 leading_field: Some(ast::DateTimeField::Day),
1443 leading_precision: None,
1444 last_field: None,
1445 fractional_seconds_precision: None,
1446 };
1447 Ok(ast::Expr::Interval(interval))
1448 }
1449
1450 fn interval_scalar_to_sql(&self, v: &ScalarValue) -> Result<ast::Expr> {
1451 match self.dialect.interval_style() {
1452 IntervalStyle::PostgresVerbose => {
1453 let wrap_array = v.to_array()?;
1454 let Some(result) = array_value_to_string(&wrap_array, 0).ok() else {
1455 return internal_err!(
1456 "Unable to convert interval scalar value to string"
1457 );
1458 };
1459 let interval = Interval {
1460 value: Box::new(ast::Expr::value(SingleQuotedString(
1461 result.to_uppercase(),
1462 ))),
1463 leading_field: None,
1464 leading_precision: None,
1465 last_field: None,
1466 fractional_seconds_precision: None,
1467 };
1468 Ok(ast::Expr::Interval(interval))
1469 }
1470 IntervalStyle::SQLStandard => match v {
1472 ScalarValue::IntervalYearMonth(Some(v)) => {
1473 let interval = Interval {
1474 value: Box::new(ast::Expr::value(SingleQuotedString(
1475 v.to_string(),
1476 ))),
1477 leading_field: Some(ast::DateTimeField::Month),
1478 leading_precision: None,
1479 last_field: None,
1480 fractional_seconds_precision: None,
1481 };
1482 Ok(ast::Expr::Interval(interval))
1483 }
1484 ScalarValue::IntervalDayTime(Some(v)) => {
1485 let days = v.days;
1486 let secs = v.milliseconds / 1_000;
1487 let mins = secs / 60;
1488 let hours = mins / 60;
1489
1490 let secs = secs - (mins * 60);
1491 let mins = mins - (hours * 60);
1492
1493 let millis = v.milliseconds % 1_000;
1494 let interval = Interval {
1495 value: Box::new(ast::Expr::value(SingleQuotedString(format!(
1496 "{days} {hours}:{mins}:{secs}.{millis:3}"
1497 )))),
1498 leading_field: Some(ast::DateTimeField::Day),
1499 leading_precision: None,
1500 last_field: Some(ast::DateTimeField::Second),
1501 fractional_seconds_precision: None,
1502 };
1503 Ok(ast::Expr::Interval(interval))
1504 }
1505 ScalarValue::IntervalMonthDayNano(Some(v)) => {
1506 if v.months >= 0 && v.days == 0 && v.nanoseconds == 0 {
1507 let interval = Interval {
1508 value: Box::new(ast::Expr::value(SingleQuotedString(
1509 v.months.to_string(),
1510 ))),
1511 leading_field: Some(ast::DateTimeField::Month),
1512 leading_precision: None,
1513 last_field: None,
1514 fractional_seconds_precision: None,
1515 };
1516 Ok(ast::Expr::Interval(interval))
1517 } else if v.months == 0 && v.nanoseconds % 1_000_000 == 0 {
1518 let days = v.days;
1519 let secs = v.nanoseconds / 1_000_000_000;
1520 let mins = secs / 60;
1521 let hours = mins / 60;
1522
1523 let secs = secs - (mins * 60);
1524 let mins = mins - (hours * 60);
1525
1526 let millis = (v.nanoseconds % 1_000_000_000) / 1_000_000;
1527
1528 let interval = Interval {
1529 value: Box::new(ast::Expr::value(SingleQuotedString(
1530 format!("{days} {hours}:{mins}:{secs}.{millis:03}"),
1531 ))),
1532 leading_field: Some(ast::DateTimeField::Day),
1533 leading_precision: None,
1534 last_field: Some(ast::DateTimeField::Second),
1535 fractional_seconds_precision: None,
1536 };
1537 Ok(ast::Expr::Interval(interval))
1538 } else {
1539 not_impl_err!("Unsupported IntervalMonthDayNano scalar with both Month and DayTime for IntervalStyle::SQLStandard")
1540 }
1541 }
1542 _ => not_impl_err!(
1543 "Unsupported ScalarValue for Interval conversion: {v:?}"
1544 ),
1545 },
1546 IntervalStyle::MySQL => match v {
1547 ScalarValue::IntervalYearMonth(Some(v)) => {
1548 self.interval_to_mysql_expr(*v, 0, 0)
1549 }
1550 ScalarValue::IntervalDayTime(Some(v)) => {
1551 self.interval_to_mysql_expr(0, v.days, v.milliseconds as i64 * 1_000)
1552 }
1553 ScalarValue::IntervalMonthDayNano(Some(v)) => {
1554 if v.nanoseconds % 1_000 != 0 {
1555 return not_impl_err!(
1556 "Unsupported IntervalMonthDayNano scalar with nanoseconds precision for IntervalStyle::MySQL"
1557 );
1558 }
1559 self.interval_to_mysql_expr(v.months, v.days, v.nanoseconds / 1_000)
1560 }
1561 _ => not_impl_err!(
1562 "Unsupported ScalarValue for Interval conversion: {v:?}"
1563 ),
1564 },
1565 }
1566 }
1567
1568 fn unnest_to_sql(&self, unnest: &Unnest) -> Result<ast::Expr> {
1571 let args = self.function_args_to_sql(std::slice::from_ref(&unnest.expr))?;
1572
1573 Ok(ast::Expr::Function(Function {
1574 name: ObjectName::from(vec![Ident {
1575 value: "UNNEST".to_string(),
1576 quote_style: None,
1577 span: Span::empty(),
1578 }]),
1579 args: ast::FunctionArguments::List(ast::FunctionArgumentList {
1580 duplicate_treatment: None,
1581 args,
1582 clauses: vec![],
1583 }),
1584 filter: None,
1585 null_treatment: None,
1586 over: None,
1587 within_group: vec![],
1588 parameters: ast::FunctionArguments::None,
1589 uses_odbc_syntax: false,
1590 }))
1591 }
1592
1593 fn arrow_dtype_to_ast_dtype(&self, data_type: &DataType) -> Result<ast::DataType> {
1594 match data_type {
1595 DataType::Null => {
1596 not_impl_err!("Unsupported DataType: conversion: {data_type:?}")
1597 }
1598 DataType::Boolean => Ok(ast::DataType::Bool),
1599 DataType::Int8 => Ok(ast::DataType::TinyInt(None)),
1600 DataType::Int16 => Ok(ast::DataType::SmallInt(None)),
1601 DataType::Int32 => Ok(self.dialect.int32_cast_dtype()),
1602 DataType::Int64 => Ok(self.dialect.int64_cast_dtype()),
1603 DataType::UInt8 => Ok(ast::DataType::TinyIntUnsigned(None)),
1604 DataType::UInt16 => Ok(ast::DataType::SmallIntUnsigned(None)),
1605 DataType::UInt32 => Ok(ast::DataType::IntegerUnsigned(None)),
1606 DataType::UInt64 => Ok(ast::DataType::BigIntUnsigned(None)),
1607 DataType::Float16 => {
1608 not_impl_err!("Unsupported DataType: conversion: {data_type:?}")
1609 }
1610 DataType::Float32 => Ok(ast::DataType::Float(None)),
1611 DataType::Float64 => Ok(self.dialect.float64_ast_dtype()),
1612 DataType::Timestamp(time_unit, tz) => {
1613 Ok(self.dialect.timestamp_cast_dtype(time_unit, tz))
1614 }
1615 DataType::Date32 => Ok(self.dialect.date32_cast_dtype()),
1616 DataType::Date64 => Ok(self.ast_type_for_date64_in_cast()),
1617 DataType::Time32(_) => {
1618 not_impl_err!("Unsupported DataType: conversion: {data_type:?}")
1619 }
1620 DataType::Time64(_) => {
1621 not_impl_err!("Unsupported DataType: conversion: {data_type:?}")
1622 }
1623 DataType::Duration(_) => {
1624 not_impl_err!("Unsupported DataType: conversion: {data_type:?}")
1625 }
1626 DataType::Interval(_) => Ok(ast::DataType::Interval),
1627 DataType::Binary => {
1628 not_impl_err!("Unsupported DataType: conversion: {data_type:?}")
1629 }
1630 DataType::FixedSizeBinary(_) => {
1631 not_impl_err!("Unsupported DataType: conversion: {data_type:?}")
1632 }
1633 DataType::LargeBinary => {
1634 not_impl_err!("Unsupported DataType: conversion: {data_type:?}")
1635 }
1636 DataType::BinaryView => {
1637 not_impl_err!("Unsupported DataType: conversion: {data_type:?}")
1638 }
1639 DataType::Utf8 => Ok(self.dialect.utf8_cast_dtype()),
1640 DataType::LargeUtf8 => Ok(self.dialect.large_utf8_cast_dtype()),
1641 DataType::Utf8View => Ok(self.dialect.utf8_cast_dtype()),
1642 DataType::List(_) => {
1643 not_impl_err!("Unsupported DataType: conversion: {data_type:?}")
1644 }
1645 DataType::FixedSizeList(_, _) => {
1646 not_impl_err!("Unsupported DataType: conversion: {data_type:?}")
1647 }
1648 DataType::LargeList(_) => {
1649 not_impl_err!("Unsupported DataType: conversion: {data_type:?}")
1650 }
1651 DataType::ListView(_) => {
1652 not_impl_err!("Unsupported DataType: conversion: {data_type:?}")
1653 }
1654 DataType::LargeListView(_) => {
1655 not_impl_err!("Unsupported DataType: conversion: {data_type:?}")
1656 }
1657 DataType::Struct(_) => {
1658 not_impl_err!("Unsupported DataType: conversion: {data_type:?}")
1659 }
1660 DataType::Union(_, _) => {
1661 not_impl_err!("Unsupported DataType: conversion: {data_type:?}")
1662 }
1663 DataType::Dictionary(_, val) => self.arrow_dtype_to_ast_dtype(val),
1664 DataType::Decimal128(precision, scale)
1665 | DataType::Decimal256(precision, scale) => {
1666 let mut new_precision = *precision as u64;
1667 let mut new_scale = *scale as u64;
1668 if *scale < 0 {
1669 new_precision = (*precision as i16 - *scale as i16) as u64;
1670 new_scale = 0
1671 }
1672
1673 Ok(ast::DataType::Decimal(
1674 ast::ExactNumberInfo::PrecisionAndScale(new_precision, new_scale),
1675 ))
1676 }
1677 DataType::Map(_, _) => {
1678 not_impl_err!("Unsupported DataType: conversion: {data_type:?}")
1679 }
1680 DataType::RunEndEncoded(_, _) => {
1681 not_impl_err!("Unsupported DataType: conversion: {data_type:?}")
1682 }
1683 }
1684 }
1685}
1686
1687#[cfg(test)]
1688mod tests {
1689 use std::ops::{Add, Sub};
1690 use std::{any::Any, sync::Arc, vec};
1691
1692 use crate::unparser::dialect::SqliteDialect;
1693 use arrow::array::{LargeListArray, ListArray};
1694 use arrow::datatypes::{DataType::Int8, Field, Int32Type, Schema, TimeUnit};
1695 use ast::ObjectName;
1696 use datafusion_common::{Spans, TableReference};
1697 use datafusion_expr::expr::WildcardOptions;
1698 use datafusion_expr::{
1699 case, cast, col, cube, exists, grouping_set, interval_datetime_lit,
1700 interval_year_month_lit, lit, not, not_exists, out_ref_col, placeholder, rollup,
1701 table_scan, try_cast, when, ColumnarValue, ScalarFunctionArgs, ScalarUDF,
1702 ScalarUDFImpl, Signature, Volatility, WindowFrame, WindowFunctionDefinition,
1703 };
1704 use datafusion_expr::{interval_month_day_nano_lit, ExprFunctionExt};
1705 use datafusion_functions::datetime::from_unixtime::FromUnixtimeFunc;
1706 use datafusion_functions::expr_fn::{get_field, named_struct};
1707 use datafusion_functions_aggregate::count::count_udaf;
1708 use datafusion_functions_aggregate::expr_fn::sum;
1709 use datafusion_functions_nested::expr_fn::{array_element, make_array};
1710 use datafusion_functions_nested::map::map;
1711 use datafusion_functions_window::rank::rank_udwf;
1712 use datafusion_functions_window::row_number::row_number_udwf;
1713 use sqlparser::ast::ExactNumberInfo;
1714
1715 use crate::unparser::dialect::{
1716 CharacterLengthStyle, CustomDialect, CustomDialectBuilder, DateFieldExtractStyle,
1717 DefaultDialect, Dialect, DuckDBDialect, PostgreSqlDialect, ScalarFnToSqlHandler,
1718 };
1719
1720 use super::*;
1721
1722 #[derive(Debug)]
1724 struct DummyUDF {
1725 signature: Signature,
1726 }
1727
1728 impl DummyUDF {
1729 fn new() -> Self {
1730 Self {
1731 signature: Signature::variadic_any(Volatility::Immutable),
1732 }
1733 }
1734 }
1735
1736 impl ScalarUDFImpl for DummyUDF {
1737 fn as_any(&self) -> &dyn Any {
1738 self
1739 }
1740
1741 fn name(&self) -> &str {
1742 "dummy_udf"
1743 }
1744
1745 fn signature(&self) -> &Signature {
1746 &self.signature
1747 }
1748
1749 fn return_type(&self, _arg_types: &[DataType]) -> Result<DataType> {
1750 Ok(DataType::Int32)
1751 }
1752
1753 fn invoke_with_args(&self, _args: ScalarFunctionArgs) -> Result<ColumnarValue> {
1754 panic!("dummy - not implemented")
1755 }
1756 }
1757 #[test]
1760 fn expr_to_sql_ok() -> Result<()> {
1761 let dummy_schema = Schema::new(vec![Field::new("a", DataType::Int32, false)]);
1762 #[expect(deprecated)]
1763 let dummy_logical_plan = table_scan(Some("t"), &dummy_schema, None)?
1764 .project(vec![Expr::Wildcard {
1765 qualifier: None,
1766 options: Box::new(WildcardOptions::default()),
1767 }])?
1768 .filter(col("a").eq(lit(1)))?
1769 .build()?;
1770
1771 let tests: Vec<(Expr, &str)> = vec![
1772 ((col("a") + col("b")).gt(lit(4)), r#"((a + b) > 4)"#),
1773 (
1774 Expr::Column(Column {
1775 relation: Some(TableReference::partial("a", "b")),
1776 name: "c".to_string(),
1777 spans: Spans::new(),
1778 })
1779 .gt(lit(4)),
1780 r#"(b.c > 4)"#,
1781 ),
1782 (
1783 case(col("a"))
1784 .when(lit(1), lit(true))
1785 .when(lit(0), lit(false))
1786 .otherwise(lit(ScalarValue::Null))?,
1787 r#"CASE a WHEN 1 THEN true WHEN 0 THEN false ELSE NULL END"#,
1788 ),
1789 (
1790 when(col("a").is_null(), lit(true)).otherwise(lit(false))?,
1791 r#"CASE WHEN a IS NULL THEN true ELSE false END"#,
1792 ),
1793 (
1794 when(col("a").is_not_null(), lit(true)).otherwise(lit(false))?,
1795 r#"CASE WHEN a IS NOT NULL THEN true ELSE false END"#,
1796 ),
1797 (
1798 Expr::Cast(Cast {
1799 expr: Box::new(col("a")),
1800 data_type: DataType::Date64,
1801 }),
1802 r#"CAST(a AS DATETIME)"#,
1803 ),
1804 (
1805 Expr::Cast(Cast {
1806 expr: Box::new(col("a")),
1807 data_type: DataType::Timestamp(
1808 TimeUnit::Nanosecond,
1809 Some("+08:00".into()),
1810 ),
1811 }),
1812 r#"CAST(a AS TIMESTAMP WITH TIME ZONE)"#,
1813 ),
1814 (
1815 Expr::Cast(Cast {
1816 expr: Box::new(col("a")),
1817 data_type: DataType::Timestamp(TimeUnit::Millisecond, None),
1818 }),
1819 r#"CAST(a AS TIMESTAMP)"#,
1820 ),
1821 (
1822 Expr::Cast(Cast {
1823 expr: Box::new(col("a")),
1824 data_type: DataType::UInt32,
1825 }),
1826 r#"CAST(a AS INTEGER UNSIGNED)"#,
1827 ),
1828 (
1829 col("a").in_list(vec![lit(1), lit(2), lit(3)], false),
1830 r#"a IN (1, 2, 3)"#,
1831 ),
1832 (
1833 col("a").in_list(vec![lit(1), lit(2), lit(3)], true),
1834 r#"a NOT IN (1, 2, 3)"#,
1835 ),
1836 (
1837 ScalarUDF::new_from_impl(DummyUDF::new()).call(vec![col("a"), col("b")]),
1838 r#"dummy_udf(a, b)"#,
1839 ),
1840 (
1841 ScalarUDF::new_from_impl(DummyUDF::new())
1842 .call(vec![col("a"), col("b")])
1843 .is_null(),
1844 r#"dummy_udf(a, b) IS NULL"#,
1845 ),
1846 (
1847 ScalarUDF::new_from_impl(DummyUDF::new())
1848 .call(vec![col("a"), col("b")])
1849 .is_not_null(),
1850 r#"dummy_udf(a, b) IS NOT NULL"#,
1851 ),
1852 (
1853 Expr::Like(Like {
1854 negated: true,
1855 expr: Box::new(col("a")),
1856 pattern: Box::new(lit("foo")),
1857 escape_char: Some('o'),
1858 case_insensitive: true,
1859 }),
1860 r#"a NOT LIKE 'foo' ESCAPE 'o'"#,
1861 ),
1862 (
1863 Expr::SimilarTo(Like {
1864 negated: false,
1865 expr: Box::new(col("a")),
1866 pattern: Box::new(lit("foo")),
1867 escape_char: Some('o'),
1868 case_insensitive: true,
1869 }),
1870 r#"a LIKE 'foo' ESCAPE 'o'"#,
1871 ),
1872 (
1873 Expr::Literal(ScalarValue::Date64(Some(0))),
1874 r#"CAST('1970-01-01 00:00:00' AS DATETIME)"#,
1875 ),
1876 (
1877 Expr::Literal(ScalarValue::Date64(Some(10000))),
1878 r#"CAST('1970-01-01 00:00:10' AS DATETIME)"#,
1879 ),
1880 (
1881 Expr::Literal(ScalarValue::Date64(Some(-10000))),
1882 r#"CAST('1969-12-31 23:59:50' AS DATETIME)"#,
1883 ),
1884 (
1885 Expr::Literal(ScalarValue::Date32(Some(0))),
1886 r#"CAST('1970-01-01' AS DATE)"#,
1887 ),
1888 (
1889 Expr::Literal(ScalarValue::Date32(Some(10))),
1890 r#"CAST('1970-01-11' AS DATE)"#,
1891 ),
1892 (
1893 Expr::Literal(ScalarValue::Date32(Some(-1))),
1894 r#"CAST('1969-12-31' AS DATE)"#,
1895 ),
1896 (
1897 Expr::Literal(ScalarValue::TimestampSecond(Some(10001), None)),
1898 r#"CAST('1970-01-01 02:46:41' AS TIMESTAMP)"#,
1899 ),
1900 (
1901 Expr::Literal(ScalarValue::TimestampSecond(
1902 Some(10001),
1903 Some("+08:00".into()),
1904 )),
1905 r#"CAST('1970-01-01 10:46:41 +08:00' AS TIMESTAMP)"#,
1906 ),
1907 (
1908 Expr::Literal(ScalarValue::TimestampMillisecond(Some(10001), None)),
1909 r#"CAST('1970-01-01 00:00:10.001' AS TIMESTAMP)"#,
1910 ),
1911 (
1912 Expr::Literal(ScalarValue::TimestampMillisecond(
1913 Some(10001),
1914 Some("+08:00".into()),
1915 )),
1916 r#"CAST('1970-01-01 08:00:10.001 +08:00' AS TIMESTAMP)"#,
1917 ),
1918 (
1919 Expr::Literal(ScalarValue::TimestampMicrosecond(Some(10001), None)),
1920 r#"CAST('1970-01-01 00:00:00.010001' AS TIMESTAMP)"#,
1921 ),
1922 (
1923 Expr::Literal(ScalarValue::TimestampMicrosecond(
1924 Some(10001),
1925 Some("+08:00".into()),
1926 )),
1927 r#"CAST('1970-01-01 08:00:00.010001 +08:00' AS TIMESTAMP)"#,
1928 ),
1929 (
1930 Expr::Literal(ScalarValue::TimestampNanosecond(Some(10001), None)),
1931 r#"CAST('1970-01-01 00:00:00.000010001' AS TIMESTAMP)"#,
1932 ),
1933 (
1934 Expr::Literal(ScalarValue::TimestampNanosecond(
1935 Some(10001),
1936 Some("+08:00".into()),
1937 )),
1938 r#"CAST('1970-01-01 08:00:00.000010001 +08:00' AS TIMESTAMP)"#,
1939 ),
1940 (
1941 Expr::Literal(ScalarValue::Time32Second(Some(10001))),
1942 r#"CAST('02:46:41' AS TIME)"#,
1943 ),
1944 (
1945 Expr::Literal(ScalarValue::Time32Millisecond(Some(10001))),
1946 r#"CAST('00:00:10.001' AS TIME)"#,
1947 ),
1948 (
1949 Expr::Literal(ScalarValue::Time64Microsecond(Some(10001))),
1950 r#"CAST('00:00:00.010001' AS TIME)"#,
1951 ),
1952 (
1953 Expr::Literal(ScalarValue::Time64Nanosecond(Some(10001))),
1954 r#"CAST('00:00:00.000010001' AS TIME)"#,
1955 ),
1956 (sum(col("a")), r#"sum(a)"#),
1957 (
1958 #[expect(deprecated)]
1959 count_udaf()
1960 .call(vec![Expr::Wildcard {
1961 qualifier: None,
1962 options: Box::new(WildcardOptions::default()),
1963 }])
1964 .distinct()
1965 .build()
1966 .unwrap(),
1967 "count(DISTINCT *)",
1968 ),
1969 (
1970 #[expect(deprecated)]
1971 count_udaf()
1972 .call(vec![Expr::Wildcard {
1973 qualifier: None,
1974 options: Box::new(WildcardOptions::default()),
1975 }])
1976 .filter(lit(true))
1977 .build()
1978 .unwrap(),
1979 "count(*) FILTER (WHERE true)",
1980 ),
1981 (
1982 Expr::WindowFunction(WindowFunction {
1983 fun: WindowFunctionDefinition::WindowUDF(row_number_udwf()),
1984 params: WindowFunctionParams {
1985 args: vec![col("col")],
1986 partition_by: vec![],
1987 order_by: vec![],
1988 window_frame: WindowFrame::new(None),
1989 null_treatment: None,
1990 },
1991 }),
1992 r#"row_number(col) OVER (ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)"#,
1993 ),
1994 (
1995 #[expect(deprecated)]
1996 Expr::WindowFunction(WindowFunction {
1997 fun: WindowFunctionDefinition::AggregateUDF(count_udaf()),
1998 params: WindowFunctionParams {
1999 args: vec![Expr::Wildcard {
2000 qualifier: None,
2001 options: Box::new(WildcardOptions::default()),
2002 }],
2003 partition_by: vec![],
2004 order_by: vec![Sort::new(col("a"), false, true)],
2005 window_frame: WindowFrame::new_bounds(
2006 datafusion_expr::WindowFrameUnits::Range,
2007 datafusion_expr::WindowFrameBound::Preceding(
2008 ScalarValue::UInt32(Some(6)),
2009 ),
2010 datafusion_expr::WindowFrameBound::Following(
2011 ScalarValue::UInt32(Some(2)),
2012 ),
2013 ),
2014 null_treatment: None,
2015 },
2016 }),
2017 r#"count(*) OVER (ORDER BY a DESC NULLS FIRST RANGE BETWEEN 6 PRECEDING AND 2 FOLLOWING)"#,
2018 ),
2019 (col("a").is_not_null(), r#"a IS NOT NULL"#),
2020 (col("a").is_null(), r#"a IS NULL"#),
2021 (
2022 (col("a") + col("b")).gt(lit(4)).is_true(),
2023 r#"((a + b) > 4) IS TRUE"#,
2024 ),
2025 (
2026 (col("a") + col("b")).gt(lit(4)).is_not_true(),
2027 r#"((a + b) > 4) IS NOT TRUE"#,
2028 ),
2029 (
2030 (col("a") + col("b")).gt(lit(4)).is_false(),
2031 r#"((a + b) > 4) IS FALSE"#,
2032 ),
2033 (
2034 (col("a") + col("b")).gt(lit(4)).is_not_false(),
2035 r#"((a + b) > 4) IS NOT FALSE"#,
2036 ),
2037 (
2038 (col("a") + col("b")).gt(lit(4)).is_unknown(),
2039 r#"((a + b) > 4) IS UNKNOWN"#,
2040 ),
2041 (
2042 (col("a") + col("b")).gt(lit(4)).is_not_unknown(),
2043 r#"((a + b) > 4) IS NOT UNKNOWN"#,
2044 ),
2045 (not(col("a")), r#"NOT a"#),
2046 (
2047 Expr::between(col("a"), lit(1), lit(7)),
2048 r#"(a BETWEEN 1 AND 7)"#,
2049 ),
2050 (Expr::Negative(Box::new(col("a"))), r#"-a"#),
2051 (
2052 exists(Arc::new(dummy_logical_plan.clone())),
2053 r#"EXISTS (SELECT * FROM t WHERE (t.a = 1))"#,
2054 ),
2055 (
2056 not_exists(Arc::new(dummy_logical_plan)),
2057 r#"NOT EXISTS (SELECT * FROM t WHERE (t.a = 1))"#,
2058 ),
2059 (
2060 try_cast(col("a"), DataType::Date64),
2061 r#"TRY_CAST(a AS DATETIME)"#,
2062 ),
2063 (
2064 try_cast(col("a"), DataType::UInt32),
2065 r#"TRY_CAST(a AS INTEGER UNSIGNED)"#,
2066 ),
2067 (
2068 Expr::ScalarVariable(Int8, vec![String::from("@a")]),
2069 r#"@a"#,
2070 ),
2071 (
2072 Expr::ScalarVariable(
2073 Int8,
2074 vec![String::from("@root"), String::from("foo")],
2075 ),
2076 r#"@root.foo"#,
2077 ),
2078 (col("x").eq(placeholder("$1")), r#"(x = $1)"#),
2079 (
2080 out_ref_col(DataType::Int32, "t.a").gt(lit(1)),
2081 r#"(t.a > 1)"#,
2082 ),
2083 (
2084 grouping_set(vec![vec![col("a"), col("b")], vec![col("a")]]),
2085 r#"GROUPING SETS ((a, b), (a))"#,
2086 ),
2087 (cube(vec![col("a"), col("b")]), r#"CUBE (a, b)"#),
2088 (rollup(vec![col("a"), col("b")]), r#"ROLLUP (a, b)"#),
2089 (col("table").eq(lit(1)), r#"("table" = 1)"#),
2090 (
2091 col("123_need_quoted").eq(lit(1)),
2092 r#"("123_need_quoted" = 1)"#,
2093 ),
2094 (col("need-quoted").eq(lit(1)), r#"("need-quoted" = 1)"#),
2095 (col("need quoted").eq(lit(1)), r#"("need quoted" = 1)"#),
2096 (
2098 (col("a") + col("b")).gt(Expr::Literal(ScalarValue::Decimal128(
2099 Some(100123),
2100 28,
2101 3,
2102 ))),
2103 r#"((a + b) > 100.123)"#,
2104 ),
2105 (
2106 (col("a") + col("b")).gt(Expr::Literal(ScalarValue::Decimal256(
2107 Some(100123.into()),
2108 28,
2109 3,
2110 ))),
2111 r#"((a + b) > 100.123)"#,
2112 ),
2113 (
2114 Expr::Cast(Cast {
2115 expr: Box::new(col("a")),
2116 data_type: DataType::Decimal128(10, -2),
2117 }),
2118 r#"CAST(a AS DECIMAL(12,0))"#,
2119 ),
2120 (
2121 Expr::Unnest(Unnest {
2122 expr: Box::new(Expr::Column(Column {
2123 relation: Some(TableReference::partial("schema", "table")),
2124 name: "array_col".to_string(),
2125 spans: Spans::new(),
2126 })),
2127 }),
2128 r#"UNNEST("table".array_col)"#,
2129 ),
2130 (make_array(vec![lit(1), lit(2), lit(3)]), "[1, 2, 3]"),
2131 (array_element(col("array_col"), lit(1)), "array_col[1]"),
2132 (
2133 array_element(make_array(vec![lit(1), lit(2), lit(3)]), lit(1)),
2134 "[1, 2, 3][1]",
2135 ),
2136 (
2137 named_struct(vec![lit("a"), lit("1"), lit("b"), lit(2)]),
2138 "{a: '1', b: 2}",
2139 ),
2140 (get_field(col("a.b"), "c"), "a.b.c"),
2141 (
2142 map(vec![lit("a"), lit("b")], vec![lit(1), lit(2)]),
2143 "MAP {'a': 1, 'b': 2}",
2144 ),
2145 (
2146 Expr::Literal(ScalarValue::Dictionary(
2147 Box::new(DataType::Int32),
2148 Box::new(ScalarValue::Utf8(Some("foo".into()))),
2149 )),
2150 "'foo'",
2151 ),
2152 (
2153 Expr::Literal(ScalarValue::List(Arc::new(
2154 ListArray::from_iter_primitive::<Int32Type, _, _>(vec![Some(vec![
2155 Some(1),
2156 Some(2),
2157 Some(3),
2158 ])]),
2159 ))),
2160 "[1, 2, 3]",
2161 ),
2162 (
2163 Expr::Literal(ScalarValue::LargeList(Arc::new(
2164 LargeListArray::from_iter_primitive::<Int32Type, _, _>(vec![Some(
2165 vec![Some(1), Some(2), Some(3)],
2166 )]),
2167 ))),
2168 "[1, 2, 3]",
2169 ),
2170 (
2171 Expr::BinaryExpr(BinaryExpr {
2172 left: Box::new(col("a")),
2173 op: Operator::ArrowAt,
2174 right: Box::new(col("b")),
2175 }),
2176 "(a <@ b)",
2177 ),
2178 (
2179 Expr::BinaryExpr(BinaryExpr {
2180 left: Box::new(col("a")),
2181 op: Operator::AtArrow,
2182 right: Box::new(col("b")),
2183 }),
2184 "(a @> b)",
2185 ),
2186 ];
2187
2188 for (expr, expected) in tests {
2189 let ast = expr_to_sql(&expr)?;
2190
2191 let actual = format!("{}", ast);
2192
2193 assert_eq!(actual, expected);
2194 }
2195
2196 Ok(())
2197 }
2198
2199 #[test]
2200 fn custom_dialect_with_identifier_quote_style() -> Result<()> {
2201 let dialect = CustomDialectBuilder::new()
2202 .with_identifier_quote_style('\'')
2203 .build();
2204 let unparser = Unparser::new(&dialect);
2205
2206 let expr = col("a").gt(lit(4));
2207 let ast = unparser.expr_to_sql(&expr)?;
2208
2209 let actual = format!("{}", ast);
2210
2211 let expected = r#"('a' > 4)"#;
2212 assert_eq!(actual, expected);
2213
2214 Ok(())
2215 }
2216
2217 #[test]
2218 fn custom_dialect_without_identifier_quote_style() -> Result<()> {
2219 let dialect = CustomDialect::default();
2220 let unparser = Unparser::new(&dialect);
2221
2222 let expr = col("a").gt(lit(4));
2223 let ast = unparser.expr_to_sql(&expr)?;
2224
2225 let actual = format!("{}", ast);
2226
2227 let expected = r#"(a > 4)"#;
2228 assert_eq!(actual, expected);
2229
2230 Ok(())
2231 }
2232
2233 #[test]
2234 fn custom_dialect_use_timestamp_for_date64() -> Result<()> {
2235 for (use_timestamp_for_date64, identifier) in
2236 [(false, "DATETIME"), (true, "TIMESTAMP")]
2237 {
2238 let dialect = CustomDialectBuilder::new()
2239 .with_use_timestamp_for_date64(use_timestamp_for_date64)
2240 .build();
2241 let unparser = Unparser::new(&dialect);
2242
2243 let expr = Expr::Cast(Cast {
2244 expr: Box::new(col("a")),
2245 data_type: DataType::Date64,
2246 });
2247 let ast = unparser.expr_to_sql(&expr)?;
2248
2249 let actual = format!("{}", ast);
2250
2251 let expected = format!(r#"CAST(a AS {identifier})"#);
2252 assert_eq!(actual, expected);
2253 }
2254 Ok(())
2255 }
2256
2257 #[test]
2258 fn custom_dialect_float64_ast_dtype() -> Result<()> {
2259 for (float64_ast_dtype, identifier) in [
2260 (ast::DataType::Double(ExactNumberInfo::None), "DOUBLE"),
2261 (ast::DataType::DoublePrecision, "DOUBLE PRECISION"),
2262 ] {
2263 let dialect = CustomDialectBuilder::new()
2264 .with_float64_ast_dtype(float64_ast_dtype)
2265 .build();
2266 let unparser = Unparser::new(&dialect);
2267
2268 let expr = Expr::Cast(Cast {
2269 expr: Box::new(col("a")),
2270 data_type: DataType::Float64,
2271 });
2272 let ast = unparser.expr_to_sql(&expr)?;
2273
2274 let actual = format!("{}", ast);
2275
2276 let expected = format!(r#"CAST(a AS {identifier})"#);
2277 assert_eq!(actual, expected);
2278 }
2279 Ok(())
2280 }
2281
2282 #[test]
2283 fn customer_dialect_support_nulls_first_in_ort() -> Result<()> {
2284 let tests: Vec<(Sort, &str, bool)> = vec![
2285 (col("a").sort(true, true), r#"a ASC NULLS FIRST"#, true),
2286 (col("a").sort(true, true), r#"a ASC"#, false),
2287 ];
2288
2289 for (expr, expected, supports_nulls_first_in_sort) in tests {
2290 let dialect = CustomDialectBuilder::new()
2291 .with_supports_nulls_first_in_sort(supports_nulls_first_in_sort)
2292 .build();
2293 let unparser = Unparser::new(&dialect);
2294 let ast = unparser.sort_to_sql(&expr)?;
2295
2296 let actual = format!("{}", ast);
2297
2298 assert_eq!(actual, expected);
2299 }
2300
2301 Ok(())
2302 }
2303
2304 #[test]
2305 fn test_character_length_scalar_to_expr() {
2306 let tests = [
2307 (CharacterLengthStyle::Length, "length(x)"),
2308 (CharacterLengthStyle::CharacterLength, "character_length(x)"),
2309 ];
2310
2311 for (style, expected) in tests {
2312 let dialect = CustomDialectBuilder::new()
2313 .with_character_length_style(style)
2314 .build();
2315 let unparser = Unparser::new(&dialect);
2316
2317 let expr = ScalarUDF::new_from_impl(
2318 datafusion_functions::unicode::character_length::CharacterLengthFunc::new(
2319 ),
2320 )
2321 .call(vec![col("x")]);
2322
2323 let ast = unparser.expr_to_sql(&expr).expect("to be unparsed");
2324
2325 let actual = format!("{ast}");
2326
2327 assert_eq!(actual, expected);
2328 }
2329 }
2330
2331 #[test]
2332 fn test_interval_scalar_to_expr() {
2333 let tests = [
2334 (
2335 interval_month_day_nano_lit("1 MONTH"),
2336 IntervalStyle::SQLStandard,
2337 "INTERVAL '1' MONTH",
2338 ),
2339 (
2340 interval_month_day_nano_lit("1.5 DAY"),
2341 IntervalStyle::SQLStandard,
2342 "INTERVAL '1 12:0:0.000' DAY TO SECOND",
2343 ),
2344 (
2345 interval_month_day_nano_lit("-1.5 DAY"),
2346 IntervalStyle::SQLStandard,
2347 "INTERVAL '-1 -12:0:0.000' DAY TO SECOND",
2348 ),
2349 (
2350 interval_month_day_nano_lit("1.51234 DAY"),
2351 IntervalStyle::SQLStandard,
2352 "INTERVAL '1 12:17:46.176' DAY TO SECOND",
2353 ),
2354 (
2355 interval_datetime_lit("1.51234 DAY"),
2356 IntervalStyle::SQLStandard,
2357 "INTERVAL '1 12:17:46.176' DAY TO SECOND",
2358 ),
2359 (
2360 interval_year_month_lit("1 YEAR"),
2361 IntervalStyle::SQLStandard,
2362 "INTERVAL '12' MONTH",
2363 ),
2364 (
2365 interval_month_day_nano_lit(
2366 "1 YEAR 1 MONTH 1 DAY 3 HOUR 10 MINUTE 20 SECOND",
2367 ),
2368 IntervalStyle::PostgresVerbose,
2369 r#"INTERVAL '13 MONS 1 DAYS 3 HOURS 10 MINS 20.000000000 SECS'"#,
2370 ),
2371 (
2372 interval_month_day_nano_lit("1.5 MONTH"),
2373 IntervalStyle::PostgresVerbose,
2374 r#"INTERVAL '1 MONS 15 DAYS'"#,
2375 ),
2376 (
2377 interval_month_day_nano_lit("-3 MONTH"),
2378 IntervalStyle::PostgresVerbose,
2379 r#"INTERVAL '-3 MONS'"#,
2380 ),
2381 (
2382 interval_month_day_nano_lit("1 MONTH")
2383 .add(interval_month_day_nano_lit("1 DAY")),
2384 IntervalStyle::PostgresVerbose,
2385 r#"(INTERVAL '1 MONS' + INTERVAL '1 DAYS')"#,
2386 ),
2387 (
2388 interval_month_day_nano_lit("1 MONTH")
2389 .sub(interval_month_day_nano_lit("1 DAY")),
2390 IntervalStyle::PostgresVerbose,
2391 r#"(INTERVAL '1 MONS' - INTERVAL '1 DAYS')"#,
2392 ),
2393 (
2394 interval_datetime_lit("10 DAY 1 HOUR 10 MINUTE 20 SECOND"),
2395 IntervalStyle::PostgresVerbose,
2396 r#"INTERVAL '10 DAYS 1 HOURS 10 MINS 20.000 SECS'"#,
2397 ),
2398 (
2399 interval_datetime_lit("10 DAY 1.5 HOUR 10 MINUTE 20 SECOND"),
2400 IntervalStyle::PostgresVerbose,
2401 r#"INTERVAL '10 DAYS 1 HOURS 40 MINS 20.000 SECS'"#,
2402 ),
2403 (
2404 interval_year_month_lit("1 YEAR 1 MONTH"),
2405 IntervalStyle::PostgresVerbose,
2406 r#"INTERVAL '1 YEARS 1 MONS'"#,
2407 ),
2408 (
2409 interval_year_month_lit("1.5 YEAR 1 MONTH"),
2410 IntervalStyle::PostgresVerbose,
2411 r#"INTERVAL '1 YEARS 7 MONS'"#,
2412 ),
2413 (
2414 interval_year_month_lit("1 YEAR 1 MONTH"),
2415 IntervalStyle::MySQL,
2416 r#"INTERVAL 13 MONTH"#,
2417 ),
2418 (
2419 interval_month_day_nano_lit("1 YEAR -1 MONTH"),
2420 IntervalStyle::MySQL,
2421 r#"INTERVAL 11 MONTH"#,
2422 ),
2423 (
2424 interval_month_day_nano_lit("15 DAY"),
2425 IntervalStyle::MySQL,
2426 r#"INTERVAL 15 DAY"#,
2427 ),
2428 (
2429 interval_month_day_nano_lit("-40 HOURS"),
2430 IntervalStyle::MySQL,
2431 r#"INTERVAL -40 HOUR"#,
2432 ),
2433 (
2434 interval_datetime_lit("-1.5 DAY 1 HOUR"),
2435 IntervalStyle::MySQL,
2436 "INTERVAL -35 HOUR",
2437 ),
2438 (
2439 interval_datetime_lit("1000000 DAY 1.5 HOUR 10 MINUTE 20 SECOND"),
2440 IntervalStyle::MySQL,
2441 r#"INTERVAL 86400006020 SECOND"#,
2442 ),
2443 (
2444 interval_year_month_lit("0 DAY 0 HOUR"),
2445 IntervalStyle::MySQL,
2446 r#"INTERVAL 0 DAY"#,
2447 ),
2448 (
2449 interval_month_day_nano_lit("-1296000000 SECOND"),
2450 IntervalStyle::MySQL,
2451 r#"INTERVAL -15000 DAY"#,
2452 ),
2453 ];
2454
2455 for (value, style, expected) in tests {
2456 let dialect = CustomDialectBuilder::new()
2457 .with_interval_style(style)
2458 .build();
2459 let unparser = Unparser::new(&dialect);
2460
2461 let ast = unparser.expr_to_sql(&value).expect("to be unparsed");
2462
2463 let actual = format!("{ast}");
2464
2465 assert_eq!(actual, expected);
2466 }
2467 }
2468
2469 #[test]
2470 fn test_float_scalar_to_expr() {
2471 let tests = [
2472 (Expr::Literal(ScalarValue::Float64(Some(3f64))), "3.0"),
2473 (Expr::Literal(ScalarValue::Float64(Some(3.1f64))), "3.1"),
2474 (Expr::Literal(ScalarValue::Float32(Some(-2f32))), "-2.0"),
2475 (
2476 Expr::Literal(ScalarValue::Float32(Some(-2.989f32))),
2477 "-2.989",
2478 ),
2479 ];
2480 for (value, expected) in tests {
2481 let dialect = CustomDialectBuilder::new().build();
2482 let unparser = Unparser::new(&dialect);
2483
2484 let ast = unparser.expr_to_sql(&value).expect("to be unparsed");
2485 let actual = format!("{ast}");
2486
2487 assert_eq!(actual, expected);
2488 }
2489 }
2490
2491 #[test]
2492 fn test_cast_value_to_binary_expr() {
2493 let tests = [
2494 (
2495 Expr::Cast(Cast {
2496 expr: Box::new(Expr::Literal(ScalarValue::Utf8(Some(
2497 "blah".to_string(),
2498 )))),
2499 data_type: DataType::Binary,
2500 }),
2501 "'blah'",
2502 ),
2503 (
2504 Expr::Cast(Cast {
2505 expr: Box::new(Expr::Literal(ScalarValue::Utf8(Some(
2506 "blah".to_string(),
2507 )))),
2508 data_type: DataType::BinaryView,
2509 }),
2510 "'blah'",
2511 ),
2512 ];
2513 for (value, expected) in tests {
2514 let dialect = CustomDialectBuilder::new().build();
2515 let unparser = Unparser::new(&dialect);
2516
2517 let ast = unparser.expr_to_sql(&value).expect("to be unparsed");
2518 let actual = format!("{ast}");
2519
2520 assert_eq!(actual, expected);
2521 }
2522 }
2523
2524 #[test]
2525 fn custom_dialect_use_char_for_utf8_cast() -> Result<()> {
2526 let default_dialect = CustomDialectBuilder::default().build();
2527 let mysql_custom_dialect = CustomDialectBuilder::new()
2528 .with_utf8_cast_dtype(ast::DataType::Char(None))
2529 .with_large_utf8_cast_dtype(ast::DataType::Char(None))
2530 .build();
2531
2532 for (dialect, data_type, identifier) in [
2533 (&default_dialect, DataType::Utf8, "VARCHAR"),
2534 (&default_dialect, DataType::LargeUtf8, "TEXT"),
2535 (&mysql_custom_dialect, DataType::Utf8, "CHAR"),
2536 (&mysql_custom_dialect, DataType::LargeUtf8, "CHAR"),
2537 ] {
2538 let unparser = Unparser::new(dialect);
2539
2540 let expr = Expr::Cast(Cast {
2541 expr: Box::new(col("a")),
2542 data_type,
2543 });
2544 let ast = unparser.expr_to_sql(&expr)?;
2545
2546 let actual = format!("{}", ast);
2547 let expected = format!(r#"CAST(a AS {identifier})"#);
2548
2549 assert_eq!(actual, expected);
2550 }
2551 Ok(())
2552 }
2553
2554 #[test]
2555 fn custom_dialect_with_date_field_extract_style() -> Result<()> {
2556 for (extract_style, unit, expected) in [
2557 (
2558 DateFieldExtractStyle::DatePart,
2559 "YEAR",
2560 "date_part('YEAR', x)",
2561 ),
2562 (
2563 DateFieldExtractStyle::Extract,
2564 "YEAR",
2565 "EXTRACT(YEAR FROM x)",
2566 ),
2567 (DateFieldExtractStyle::Strftime, "YEAR", "strftime('%Y', x)"),
2568 (
2569 DateFieldExtractStyle::DatePart,
2570 "MONTH",
2571 "date_part('MONTH', x)",
2572 ),
2573 (
2574 DateFieldExtractStyle::Extract,
2575 "MONTH",
2576 "EXTRACT(MONTH FROM x)",
2577 ),
2578 (
2579 DateFieldExtractStyle::Strftime,
2580 "MONTH",
2581 "strftime('%m', x)",
2582 ),
2583 (
2584 DateFieldExtractStyle::DatePart,
2585 "DAY",
2586 "date_part('DAY', x)",
2587 ),
2588 (DateFieldExtractStyle::Strftime, "DAY", "strftime('%d', x)"),
2589 (DateFieldExtractStyle::Extract, "DAY", "EXTRACT(DAY FROM x)"),
2590 ] {
2591 let dialect = CustomDialectBuilder::new()
2592 .with_date_field_extract_style(extract_style)
2593 .build();
2594
2595 let unparser = Unparser::new(&dialect);
2596 let expr = ScalarUDF::new_from_impl(
2597 datafusion_functions::datetime::date_part::DatePartFunc::new(),
2598 )
2599 .call(vec![Expr::Literal(ScalarValue::new_utf8(unit)), col("x")]);
2600
2601 let ast = unparser.expr_to_sql(&expr)?;
2602 let actual = format!("{}", ast);
2603
2604 assert_eq!(actual, expected);
2605 }
2606 Ok(())
2607 }
2608
2609 #[test]
2610 fn custom_dialect_with_int64_cast_dtype() -> Result<()> {
2611 let default_dialect = CustomDialectBuilder::new().build();
2612 let mysql_dialect = CustomDialectBuilder::new()
2613 .with_int64_cast_dtype(ast::DataType::Custom(
2614 ObjectName::from(vec![Ident::new("SIGNED")]),
2615 vec![],
2616 ))
2617 .build();
2618
2619 for (dialect, identifier) in
2620 [(default_dialect, "BIGINT"), (mysql_dialect, "SIGNED")]
2621 {
2622 let unparser = Unparser::new(&dialect);
2623 let expr = Expr::Cast(Cast {
2624 expr: Box::new(col("a")),
2625 data_type: DataType::Int64,
2626 });
2627 let ast = unparser.expr_to_sql(&expr)?;
2628
2629 let actual = format!("{}", ast);
2630 let expected = format!(r#"CAST(a AS {identifier})"#);
2631
2632 assert_eq!(actual, expected);
2633 }
2634 Ok(())
2635 }
2636
2637 #[test]
2638 fn custom_dialect_with_int32_cast_dtype() -> Result<()> {
2639 let default_dialect = CustomDialectBuilder::new().build();
2640 let mysql_dialect = CustomDialectBuilder::new()
2641 .with_int32_cast_dtype(ast::DataType::Custom(
2642 ObjectName::from(vec![Ident::new("SIGNED")]),
2643 vec![],
2644 ))
2645 .build();
2646
2647 for (dialect, identifier) in
2648 [(default_dialect, "INTEGER"), (mysql_dialect, "SIGNED")]
2649 {
2650 let unparser = Unparser::new(&dialect);
2651 let expr = Expr::Cast(Cast {
2652 expr: Box::new(col("a")),
2653 data_type: DataType::Int32,
2654 });
2655 let ast = unparser.expr_to_sql(&expr)?;
2656
2657 let actual = format!("{}", ast);
2658 let expected = format!(r#"CAST(a AS {identifier})"#);
2659
2660 assert_eq!(actual, expected);
2661 }
2662 Ok(())
2663 }
2664
2665 #[test]
2666 fn custom_dialect_with_timestamp_cast_dtype() -> Result<()> {
2667 let default_dialect = CustomDialectBuilder::new().build();
2668 let mysql_dialect = CustomDialectBuilder::new()
2669 .with_timestamp_cast_dtype(
2670 ast::DataType::Datetime(None),
2671 ast::DataType::Datetime(None),
2672 )
2673 .build();
2674
2675 let timestamp = DataType::Timestamp(TimeUnit::Nanosecond, None);
2676 let timestamp_with_tz =
2677 DataType::Timestamp(TimeUnit::Nanosecond, Some("+08:00".into()));
2678
2679 for (dialect, data_type, identifier) in [
2680 (&default_dialect, ×tamp, "TIMESTAMP"),
2681 (
2682 &default_dialect,
2683 ×tamp_with_tz,
2684 "TIMESTAMP WITH TIME ZONE",
2685 ),
2686 (&mysql_dialect, ×tamp, "DATETIME"),
2687 (&mysql_dialect, ×tamp_with_tz, "DATETIME"),
2688 ] {
2689 let unparser = Unparser::new(dialect);
2690 let expr = Expr::Cast(Cast {
2691 expr: Box::new(col("a")),
2692 data_type: data_type.clone(),
2693 });
2694 let ast = unparser.expr_to_sql(&expr)?;
2695
2696 let actual = format!("{}", ast);
2697 let expected = format!(r#"CAST(a AS {identifier})"#);
2698
2699 assert_eq!(actual, expected);
2700 }
2701 Ok(())
2702 }
2703
2704 #[test]
2705 fn custom_dialect_with_timestamp_cast_dtype_scalar_expr() -> Result<()> {
2706 let default_dialect = CustomDialectBuilder::new().build();
2707 let mysql_dialect = CustomDialectBuilder::new()
2708 .with_timestamp_cast_dtype(
2709 ast::DataType::Datetime(None),
2710 ast::DataType::Datetime(None),
2711 )
2712 .build();
2713
2714 for (dialect, identifier) in [
2715 (&default_dialect, "TIMESTAMP"),
2716 (&mysql_dialect, "DATETIME"),
2717 ] {
2718 let unparser = Unparser::new(dialect);
2719 let expr = Expr::Literal(ScalarValue::TimestampMillisecond(
2720 Some(1738285549123),
2721 None,
2722 ));
2723 let ast = unparser.expr_to_sql(&expr)?;
2724
2725 let actual = format!("{}", ast);
2726 let expected = format!(r#"CAST('2025-01-31 01:05:49.123' AS {identifier})"#);
2727
2728 assert_eq!(actual, expected);
2729 }
2730 Ok(())
2731 }
2732
2733 #[test]
2734 fn custom_dialect_date32_ast_dtype() -> Result<()> {
2735 let default_dialect = CustomDialectBuilder::default().build();
2736 let sqlite_custom_dialect = CustomDialectBuilder::new()
2737 .with_date32_cast_dtype(ast::DataType::Text)
2738 .build();
2739
2740 for (dialect, data_type, identifier) in [
2741 (&default_dialect, DataType::Date32, "DATE"),
2742 (&sqlite_custom_dialect, DataType::Date32, "TEXT"),
2743 ] {
2744 let unparser = Unparser::new(dialect);
2745
2746 let expr = Expr::Cast(Cast {
2747 expr: Box::new(col("a")),
2748 data_type,
2749 });
2750 let ast = unparser.expr_to_sql(&expr)?;
2751
2752 let actual = format!("{}", ast);
2753 let expected = format!(r#"CAST(a AS {identifier})"#);
2754
2755 assert_eq!(actual, expected);
2756 }
2757 Ok(())
2758 }
2759
2760 #[test]
2761 fn custom_dialect_division_operator() -> Result<()> {
2762 let default_dialect = CustomDialectBuilder::new().build();
2763 let duckdb_dialect = CustomDialectBuilder::new()
2764 .with_division_operator(BinaryOperator::DuckIntegerDivide)
2765 .build();
2766
2767 for (dialect, expected) in
2768 [(default_dialect, "(a / b)"), (duckdb_dialect, "(a // b)")]
2769 {
2770 let unparser = Unparser::new(&dialect);
2771 let expr = Expr::BinaryExpr(BinaryExpr {
2772 left: Box::new(col("a")),
2773 op: Operator::Divide,
2774 right: Box::new(col("b")),
2775 });
2776 let ast = unparser.expr_to_sql(&expr)?;
2777
2778 let actual = format!("{}", ast);
2779 let expected = expected.to_string();
2780
2781 assert_eq!(actual, expected);
2782 }
2783 Ok(())
2784 }
2785
2786 #[test]
2787 fn test_cast_value_to_dict_expr() {
2788 let tests = [(
2789 Expr::Cast(Cast {
2790 expr: Box::new(Expr::Literal(ScalarValue::Utf8(Some(
2791 "variation".to_string(),
2792 )))),
2793 data_type: DataType::Dictionary(Box::new(Int8), Box::new(DataType::Utf8)),
2794 }),
2795 "'variation'",
2796 )];
2797 for (value, expected) in tests {
2798 let dialect = CustomDialectBuilder::new().build();
2799 let unparser = Unparser::new(&dialect);
2800
2801 let ast = unparser.expr_to_sql(&value).expect("to be unparsed");
2802 let actual = format!("{ast}");
2803
2804 assert_eq!(actual, expected);
2805 }
2806 }
2807
2808 #[test]
2809 fn test_round_scalar_fn_to_expr() -> Result<()> {
2810 let default_dialect: Arc<dyn Dialect> = Arc::new(
2811 CustomDialectBuilder::new()
2812 .with_identifier_quote_style('"')
2813 .build(),
2814 );
2815 let postgres_dialect: Arc<dyn Dialect> = Arc::new(PostgreSqlDialect {});
2816
2817 for (dialect, identifier) in
2818 [(default_dialect, "DOUBLE"), (postgres_dialect, "NUMERIC")]
2819 {
2820 let unparser = Unparser::new(dialect.as_ref());
2821 let expr = Expr::ScalarFunction(ScalarFunction {
2822 func: Arc::new(ScalarUDF::from(
2823 datafusion_functions::math::round::RoundFunc::new(),
2824 )),
2825 args: vec![
2826 Expr::Cast(Cast {
2827 expr: Box::new(col("a")),
2828 data_type: DataType::Float64,
2829 }),
2830 Expr::Literal(ScalarValue::Int64(Some(2))),
2831 ],
2832 });
2833 let ast = unparser.expr_to_sql(&expr)?;
2834
2835 let actual = format!("{}", ast);
2836 let expected = format!(r#"round(CAST("a" AS {identifier}), 2)"#);
2837
2838 assert_eq!(actual, expected);
2839 }
2840 Ok(())
2841 }
2842
2843 #[test]
2844 fn test_window_func_support_window_frame() -> Result<()> {
2845 let default_dialect: Arc<dyn Dialect> =
2846 Arc::new(CustomDialectBuilder::new().build());
2847
2848 let test_dialect: Arc<dyn Dialect> = Arc::new(
2849 CustomDialectBuilder::new()
2850 .with_window_func_support_window_frame(false)
2851 .build(),
2852 );
2853
2854 for (dialect, expected) in [
2855 (
2856 default_dialect,
2857 "rank() OVER (ORDER BY a ASC NULLS FIRST ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)",
2858 ),
2859 (test_dialect, "rank() OVER (ORDER BY a ASC NULLS FIRST)"),
2860 ] {
2861 let unparser = Unparser::new(dialect.as_ref());
2862 let func = WindowFunctionDefinition::WindowUDF(rank_udwf());
2863 let mut window_func = WindowFunction::new(func, vec![]);
2864 window_func.params.order_by = vec![Sort::new(col("a"), true, true)];
2865 let expr = Expr::WindowFunction(window_func);
2866 let ast = unparser.expr_to_sql(&expr)?;
2867
2868 let actual = ast.to_string();
2869 let expected = expected.to_string();
2870
2871 assert_eq!(actual, expected);
2872 }
2873 Ok(())
2874 }
2875
2876 #[test]
2877 fn test_from_unixtime() -> Result<()> {
2878 let default_dialect: Arc<dyn Dialect> = Arc::new(DefaultDialect {});
2879 let sqlite_dialect: Arc<dyn Dialect> = Arc::new(SqliteDialect {});
2880
2881 for (dialect, expected) in [
2882 (default_dialect, "from_unixtime(date_col)"),
2883 (sqlite_dialect, "datetime(`date_col`, 'unixepoch')"),
2884 ] {
2885 let unparser = Unparser::new(dialect.as_ref());
2886 let expr = Expr::ScalarFunction(ScalarFunction {
2887 func: Arc::new(ScalarUDF::from(FromUnixtimeFunc::new())),
2888 args: vec![col("date_col")],
2889 });
2890
2891 let ast = unparser.expr_to_sql(&expr)?;
2892
2893 let actual = ast.to_string();
2894 let expected = expected.to_string();
2895
2896 assert_eq!(actual, expected);
2897 }
2898 Ok(())
2899 }
2900
2901 #[test]
2902 fn test_date_trunc() -> Result<()> {
2903 let default_dialect: Arc<dyn Dialect> = Arc::new(DefaultDialect {});
2904 let sqlite_dialect: Arc<dyn Dialect> = Arc::new(SqliteDialect {});
2905
2906 for (dialect, precision, expected) in [
2907 (
2908 Arc::clone(&default_dialect),
2909 "YEAR",
2910 "date_trunc('YEAR', date_col)",
2911 ),
2912 (
2913 Arc::clone(&sqlite_dialect),
2914 "YEAR",
2915 "strftime('%Y', `date_col`)",
2916 ),
2917 (
2918 Arc::clone(&default_dialect),
2919 "MONTH",
2920 "date_trunc('MONTH', date_col)",
2921 ),
2922 (
2923 Arc::clone(&sqlite_dialect),
2924 "MONTH",
2925 "strftime('%Y-%m', `date_col`)",
2926 ),
2927 (
2928 Arc::clone(&default_dialect),
2929 "DAY",
2930 "date_trunc('DAY', date_col)",
2931 ),
2932 (
2933 Arc::clone(&sqlite_dialect),
2934 "DAY",
2935 "strftime('%Y-%m-%d', `date_col`)",
2936 ),
2937 (
2938 Arc::clone(&default_dialect),
2939 "HOUR",
2940 "date_trunc('HOUR', date_col)",
2941 ),
2942 (
2943 Arc::clone(&sqlite_dialect),
2944 "HOUR",
2945 "strftime('%Y-%m-%d %H', `date_col`)",
2946 ),
2947 (
2948 Arc::clone(&default_dialect),
2949 "MINUTE",
2950 "date_trunc('MINUTE', date_col)",
2951 ),
2952 (
2953 Arc::clone(&sqlite_dialect),
2954 "MINUTE",
2955 "strftime('%Y-%m-%d %H:%M', `date_col`)",
2956 ),
2957 (default_dialect, "SECOND", "date_trunc('SECOND', date_col)"),
2958 (
2959 sqlite_dialect,
2960 "SECOND",
2961 "strftime('%Y-%m-%d %H:%M:%S', `date_col`)",
2962 ),
2963 ] {
2964 let unparser = Unparser::new(dialect.as_ref());
2965 let expr = Expr::ScalarFunction(ScalarFunction {
2966 func: Arc::new(ScalarUDF::from(
2967 datafusion_functions::datetime::date_trunc::DateTruncFunc::new(),
2968 )),
2969 args: vec![
2970 Expr::Literal(ScalarValue::Utf8(Some(precision.to_string()))),
2971 col("date_col"),
2972 ],
2973 });
2974
2975 let ast = unparser.expr_to_sql(&expr)?;
2976
2977 let actual = ast.to_string();
2978 let expected = expected.to_string();
2979
2980 assert_eq!(actual, expected);
2981 }
2982 Ok(())
2983 }
2984
2985 #[test]
2986 fn test_dictionary_to_sql() -> Result<()> {
2987 let dialect = CustomDialectBuilder::new().build();
2988
2989 let unparser = Unparser::new(&dialect);
2990
2991 let ast_dtype = unparser.arrow_dtype_to_ast_dtype(&DataType::Dictionary(
2992 Box::new(DataType::Int32),
2993 Box::new(DataType::Utf8),
2994 ))?;
2995
2996 assert_eq!(ast_dtype, ast::DataType::Varchar(None));
2997
2998 Ok(())
2999 }
3000
3001 #[test]
3002 fn test_utf8_view_to_sql() -> Result<()> {
3003 let dialect = CustomDialectBuilder::new()
3004 .with_utf8_cast_dtype(ast::DataType::Char(None))
3005 .build();
3006 let unparser = Unparser::new(&dialect);
3007
3008 let ast_dtype = unparser.arrow_dtype_to_ast_dtype(&DataType::Utf8View)?;
3009
3010 assert_eq!(ast_dtype, ast::DataType::Char(None));
3011
3012 let expr = cast(col("a"), DataType::Utf8View);
3013 let ast = unparser.expr_to_sql(&expr)?;
3014
3015 let actual = format!("{}", ast);
3016 let expected = r#"CAST(a AS CHAR)"#.to_string();
3017
3018 assert_eq!(actual, expected);
3019
3020 let expr = col("a").eq(lit(ScalarValue::Utf8View(Some("hello".to_string()))));
3021 let ast = unparser.expr_to_sql(&expr)?;
3022
3023 let actual = format!("{}", ast);
3024 let expected = r#"(a = 'hello')"#.to_string();
3025
3026 assert_eq!(actual, expected);
3027
3028 let expr = col("a").is_not_null();
3029
3030 let ast = unparser.expr_to_sql(&expr)?;
3031 let actual = format!("{}", ast);
3032 let expected = r#"a IS NOT NULL"#.to_string();
3033
3034 assert_eq!(actual, expected);
3035
3036 let expr = col("a").is_null();
3037
3038 let ast = unparser.expr_to_sql(&expr)?;
3039 let actual = format!("{}", ast);
3040 let expected = r#"a IS NULL"#.to_string();
3041
3042 assert_eq!(actual, expected);
3043
3044 Ok(())
3045 }
3046
3047 #[test]
3048 fn test_custom_scalar_overrides_duckdb() -> Result<()> {
3049 let duckdb_default = DuckDBDialect::new();
3050 let duckdb_extended = DuckDBDialect::new().with_custom_scalar_overrides(vec![(
3051 "dummy_udf",
3052 Box::new(|unparser: &Unparser, args: &[Expr]| {
3053 unparser.scalar_function_to_sql("smart_udf", args).map(Some)
3054 }) as ScalarFnToSqlHandler,
3055 )]);
3056
3057 for (dialect, expected) in [
3058 (duckdb_default, r#"dummy_udf("a", "b")"#),
3059 (duckdb_extended, r#"smart_udf("a", "b")"#),
3060 ] {
3061 let unparser = Unparser::new(&dialect);
3062 let expr =
3063 ScalarUDF::new_from_impl(DummyUDF::new()).call(vec![col("a"), col("b")]);
3064 let actual = format!("{}", unparser.expr_to_sql(&expr)?);
3065 assert_eq!(actual, expected);
3066 }
3067
3068 Ok(())
3069 }
3070}