Skip to content

Commit 39ad685

Browse files
authored
Mysql unparser cast targets (apache#26888)
## Which issue does this PR close? - Closes apache#26887. ## Rationale for this change MySQL's `CAST` does not accept `SMALLINT` or the `... UNSIGNED` integer types as target types, but `arrow_dtype_to_ast_dtype` hard-codes them for `Int16` and `UInt8`..`UInt64`. As a result, `MySqlDialect` cannot override them and the generated SQL fails with `ERROR 1064`. See https://dev.mysql.com/doc/refman/8.0/en/cast-functions.html#function_cast Checked against MySQL 8.0.46 and 8.4.13 with `SELECT CAST('1' AS <type>)`: every type in the issue table fails with `ERROR 1064`, while `SIGNED` and `UNSIGNED` succeed. ## What changes are included in this PR? - New `Dialect` hooks `int16_cast_dtype` and `uint8`/`uint16`/`uint32`/`uint64_cast_dtype`. Their defaults return the previous types. - `MySqlDialect` returns `SIGNED` for `Int16` and `UNSIGNED` for the unsigned types. This builds on apache#26883. ## What is the testing strategy for this PR? New unit tests in `datafusion/sql/src/unparser/expr.rs`: - `mysql_int16_and_unsigned_casts`: the new MySQL output - `default_dialect_int16_and_unsigned_casts`: the default output is unchanged - `custom_dialect_int16_and_unsigned_casts`: the builder setters ## Are there any user-facing changes? With `MySqlDialect`, `Int16` casts now unparse as `SIGNED` and unsigned integer casts as `UNSIGNED`.
1 parent 6a98807 commit 39ad685

2 files changed

Lines changed: 191 additions & 9 deletions

File tree

‎datafusion/sql/src/unparser/dialect.rs‎

Lines changed: 125 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -110,6 +110,36 @@ pub trait Dialect: Send + Sync {
110110
ast::DataType::Integer(None)
111111
}
112112

113+
/// The SQL type to use for Arrow Int16 unparsing
114+
/// Most dialects use SmallInt, but some, like MySQL, require SIGNED
115+
fn int16_cast_dtype(&self) -> ast::DataType {
116+
ast::DataType::SmallInt(None)
117+
}
118+
119+
/// The SQL type to use for Arrow UInt8 unparsing
120+
/// Most dialects use TinyIntUnsigned, but some, like MySQL, require UNSIGNED
121+
fn uint8_cast_dtype(&self) -> ast::DataType {
122+
ast::DataType::TinyIntUnsigned(None)
123+
}
124+
125+
/// The SQL type to use for Arrow UInt16 unparsing
126+
/// Most dialects use SmallIntUnsigned, but some, like MySQL, require UNSIGNED
127+
fn uint16_cast_dtype(&self) -> ast::DataType {
128+
ast::DataType::SmallIntUnsigned(None)
129+
}
130+
131+
/// The SQL type to use for Arrow UInt32 unparsing
132+
/// Most dialects use IntegerUnsigned, but some, like MySQL, require UNSIGNED
133+
fn uint32_cast_dtype(&self) -> ast::DataType {
134+
ast::DataType::IntegerUnsigned(None)
135+
}
136+
137+
/// The SQL type to use for Arrow UInt64 unparsing
138+
/// Most dialects use BigIntUnsigned, but some, like MySQL, require UNSIGNED
139+
fn uint64_cast_dtype(&self) -> ast::DataType {
140+
ast::DataType::BigIntUnsigned(None)
141+
}
142+
113143
/// The SQL type to use for Timestamp unparsing
114144
/// Most dialects use Timestamp, but some, like MySQL, require Datetime
115145
/// Some dialects like Dremio does not support WithTimeZone and requires always Timestamp
@@ -589,6 +619,26 @@ impl Dialect for MySqlDialect {
589619
ast::DataType::Custom(ObjectName::from(vec![Ident::new("SIGNED")]), vec![])
590620
}
591621

622+
fn int16_cast_dtype(&self) -> ast::DataType {
623+
ast::DataType::Custom(ObjectName::from(vec![Ident::new("SIGNED")]), vec![])
624+
}
625+
626+
fn uint8_cast_dtype(&self) -> ast::DataType {
627+
ast::DataType::Custom(ObjectName::from(vec![Ident::new("UNSIGNED")]), vec![])
628+
}
629+
630+
fn uint16_cast_dtype(&self) -> ast::DataType {
631+
ast::DataType::Custom(ObjectName::from(vec![Ident::new("UNSIGNED")]), vec![])
632+
}
633+
634+
fn uint32_cast_dtype(&self) -> ast::DataType {
635+
ast::DataType::Custom(ObjectName::from(vec![Ident::new("UNSIGNED")]), vec![])
636+
}
637+
638+
fn uint64_cast_dtype(&self) -> ast::DataType {
639+
ast::DataType::Custom(ObjectName::from(vec![Ident::new("UNSIGNED")]), vec![])
640+
}
641+
592642
fn timestamp_cast_dtype(
593643
&self,
594644
time_unit: &TimeUnit,
@@ -843,6 +893,11 @@ pub struct CustomDialect {
843893
int8_cast_dtype: ast::DataType,
844894
int64_cast_dtype: ast::DataType,
845895
int32_cast_dtype: ast::DataType,
896+
int16_cast_dtype: ast::DataType,
897+
uint8_cast_dtype: ast::DataType,
898+
uint16_cast_dtype: ast::DataType,
899+
uint32_cast_dtype: ast::DataType,
900+
uint64_cast_dtype: ast::DataType,
846901
timestamp_cast_dtype: ast::DataType,
847902
timestamp_tz_cast_dtype: ast::DataType,
848903
date32_cast_dtype: ast::DataType,
@@ -870,6 +925,11 @@ impl Default for CustomDialect {
870925
int8_cast_dtype: ast::DataType::TinyInt(None),
871926
int64_cast_dtype: ast::DataType::BigInt(None),
872927
int32_cast_dtype: ast::DataType::Integer(None),
928+
int16_cast_dtype: ast::DataType::SmallInt(None),
929+
uint8_cast_dtype: ast::DataType::TinyIntUnsigned(None),
930+
uint16_cast_dtype: ast::DataType::SmallIntUnsigned(None),
931+
uint32_cast_dtype: ast::DataType::IntegerUnsigned(None),
932+
uint64_cast_dtype: ast::DataType::BigIntUnsigned(None),
873933
timestamp_cast_dtype: ast::DataType::Timestamp(None, TimezoneInfo::None),
874934
timestamp_tz_cast_dtype: ast::DataType::Timestamp(
875935
None,
@@ -936,6 +996,26 @@ impl Dialect for CustomDialect {
936996
self.int32_cast_dtype.clone()
937997
}
938998

999+
fn int16_cast_dtype(&self) -> ast::DataType {
1000+
self.int16_cast_dtype.clone()
1001+
}
1002+
1003+
fn uint8_cast_dtype(&self) -> ast::DataType {
1004+
self.uint8_cast_dtype.clone()
1005+
}
1006+
1007+
fn uint16_cast_dtype(&self) -> ast::DataType {
1008+
self.uint16_cast_dtype.clone()
1009+
}
1010+
1011+
fn uint32_cast_dtype(&self) -> ast::DataType {
1012+
self.uint32_cast_dtype.clone()
1013+
}
1014+
1015+
fn uint64_cast_dtype(&self) -> ast::DataType {
1016+
self.uint64_cast_dtype.clone()
1017+
}
1018+
9391019
fn timestamp_cast_dtype(
9401020
&self,
9411021
_time_unit: &TimeUnit,
@@ -1030,6 +1110,11 @@ pub struct CustomDialectBuilder {
10301110
int8_cast_dtype: ast::DataType,
10311111
int64_cast_dtype: ast::DataType,
10321112
int32_cast_dtype: ast::DataType,
1113+
int16_cast_dtype: ast::DataType,
1114+
uint8_cast_dtype: ast::DataType,
1115+
uint16_cast_dtype: ast::DataType,
1116+
uint32_cast_dtype: ast::DataType,
1117+
uint64_cast_dtype: ast::DataType,
10331118
timestamp_cast_dtype: ast::DataType,
10341119
timestamp_tz_cast_dtype: ast::DataType,
10351120
date32_cast_dtype: ast::DataType,
@@ -1063,6 +1148,11 @@ impl CustomDialectBuilder {
10631148
int8_cast_dtype: ast::DataType::TinyInt(None),
10641149
int64_cast_dtype: ast::DataType::BigInt(None),
10651150
int32_cast_dtype: ast::DataType::Integer(None),
1151+
int16_cast_dtype: ast::DataType::SmallInt(None),
1152+
uint8_cast_dtype: ast::DataType::TinyIntUnsigned(None),
1153+
uint16_cast_dtype: ast::DataType::SmallIntUnsigned(None),
1154+
uint32_cast_dtype: ast::DataType::IntegerUnsigned(None),
1155+
uint64_cast_dtype: ast::DataType::BigIntUnsigned(None),
10661156
timestamp_cast_dtype: ast::DataType::Timestamp(None, TimezoneInfo::None),
10671157
timestamp_tz_cast_dtype: ast::DataType::Timestamp(
10681158
None,
@@ -1093,6 +1183,11 @@ impl CustomDialectBuilder {
10931183
int8_cast_dtype: self.int8_cast_dtype,
10941184
int64_cast_dtype: self.int64_cast_dtype,
10951185
int32_cast_dtype: self.int32_cast_dtype,
1186+
int16_cast_dtype: self.int16_cast_dtype,
1187+
uint8_cast_dtype: self.uint8_cast_dtype,
1188+
uint16_cast_dtype: self.uint16_cast_dtype,
1189+
uint32_cast_dtype: self.uint32_cast_dtype,
1190+
uint64_cast_dtype: self.uint64_cast_dtype,
10961191
timestamp_cast_dtype: self.timestamp_cast_dtype,
10971192
timestamp_tz_cast_dtype: self.timestamp_tz_cast_dtype,
10981193
date32_cast_dtype: self.date32_cast_dtype,
@@ -1194,6 +1289,36 @@ impl CustomDialectBuilder {
11941289
self
11951290
}
11961291

1292+
/// Customize the dialect with a specific SQL type for Int16 casting: SmallInt, SIGNED, etc.
1293+
pub fn with_int16_cast_dtype(mut self, int16_cast_dtype: ast::DataType) -> Self {
1294+
self.int16_cast_dtype = int16_cast_dtype;
1295+
self
1296+
}
1297+
1298+
/// Customize the dialect with a specific SQL type for UInt8 casting: TinyIntUnsigned, UNSIGNED, etc.
1299+
pub fn with_uint8_cast_dtype(mut self, uint8_cast_dtype: ast::DataType) -> Self {
1300+
self.uint8_cast_dtype = uint8_cast_dtype;
1301+
self
1302+
}
1303+
1304+
/// Customize the dialect with a specific SQL type for UInt16 casting: SmallIntUnsigned, UNSIGNED, etc.
1305+
pub fn with_uint16_cast_dtype(mut self, uint16_cast_dtype: ast::DataType) -> Self {
1306+
self.uint16_cast_dtype = uint16_cast_dtype;
1307+
self
1308+
}
1309+
1310+
/// Customize the dialect with a specific SQL type for UInt32 casting: IntegerUnsigned, UNSIGNED, etc.
1311+
pub fn with_uint32_cast_dtype(mut self, uint32_cast_dtype: ast::DataType) -> Self {
1312+
self.uint32_cast_dtype = uint32_cast_dtype;
1313+
self
1314+
}
1315+
1316+
/// Customize the dialect with a specific SQL type for UInt64 casting: BigIntUnsigned, UNSIGNED, etc.
1317+
pub fn with_uint64_cast_dtype(mut self, uint64_cast_dtype: ast::DataType) -> Self {
1318+
self.uint64_cast_dtype = uint64_cast_dtype;
1319+
self
1320+
}
1321+
11971322
/// Customize the dialect with a specific SQL type for Timestamp casting: Timestamp, Datetime, etc.
11981323
pub fn with_timestamp_cast_dtype(
11991324
mut self,

‎datafusion/sql/src/unparser/expr.rs‎

Lines changed: 66 additions & 9 deletions
Original file line numberDiff line numberDiff line change
@@ -1845,13 +1845,13 @@ impl Unparser<'_> {
18451845
}
18461846
DataType::Boolean => Ok(ast::DataType::Bool),
18471847
DataType::Int8 => Ok(self.dialect.int8_cast_dtype()),
1848-
DataType::Int16 => Ok(ast::DataType::SmallInt(None)),
1848+
DataType::Int16 => Ok(self.dialect.int16_cast_dtype()),
18491849
DataType::Int32 => Ok(self.dialect.int32_cast_dtype()),
18501850
DataType::Int64 => Ok(self.dialect.int64_cast_dtype()),
1851-
DataType::UInt8 => Ok(ast::DataType::TinyIntUnsigned(None)),
1852-
DataType::UInt16 => Ok(ast::DataType::SmallIntUnsigned(None)),
1853-
DataType::UInt32 => Ok(ast::DataType::IntegerUnsigned(None)),
1854-
DataType::UInt64 => Ok(ast::DataType::BigIntUnsigned(None)),
1851+
DataType::UInt8 => Ok(self.dialect.uint8_cast_dtype()),
1852+
DataType::UInt16 => Ok(self.dialect.uint16_cast_dtype()),
1853+
DataType::UInt32 => Ok(self.dialect.uint32_cast_dtype()),
1854+
DataType::UInt64 => Ok(self.dialect.uint64_cast_dtype()),
18551855
DataType::Float16 => {
18561856
not_impl_err!("Unsupported DataType: conversion: {data_type}")
18571857
}
@@ -3144,12 +3144,69 @@ mod tests {
31443144
Ok(())
31453145
}
31463146

3147+
// MySQL's `CAST` only accepts `SIGNED` and `UNSIGNED` as integer target types.
31473148
#[test]
3148-
fn mysql_int8_cast_uses_signed() -> Result<()> {
3149+
fn mysql_integer_casts() -> Result<()> {
31493150
let unparser = Unparser::new(&MySqlDialect {});
3150-
let expr = Expr::Cast(Cast::new(Box::new(col("a")), Int8));
3151-
let ast = unparser.expr_to_sql(&expr)?;
3152-
assert_eq!(format!("{ast}"), "CAST(`a` AS SIGNED)");
3151+
for (data_type, expected) in [
3152+
(Int8, "CAST(`a` AS SIGNED)"),
3153+
(DataType::Int16, "CAST(`a` AS SIGNED)"),
3154+
(DataType::Int32, "CAST(`a` AS SIGNED)"),
3155+
(DataType::Int64, "CAST(`a` AS SIGNED)"),
3156+
(DataType::UInt8, "CAST(`a` AS UNSIGNED)"),
3157+
(DataType::UInt16, "CAST(`a` AS UNSIGNED)"),
3158+
(DataType::UInt32, "CAST(`a` AS UNSIGNED)"),
3159+
(DataType::UInt64, "CAST(`a` AS UNSIGNED)"),
3160+
] {
3161+
let expr = Expr::Cast(Cast::new(Box::new(col("a")), data_type));
3162+
let ast = unparser.expr_to_sql(&expr)?;
3163+
assert_eq!(format!("{ast}"), expected);
3164+
}
3165+
Ok(())
3166+
}
3167+
3168+
#[test]
3169+
fn default_and_custom_dialect_integer_casts() -> Result<()> {
3170+
let custom_dialect = CustomDialectBuilder::new()
3171+
.with_int8_cast_dtype(ast::DataType::SmallInt(None))
3172+
.with_int16_cast_dtype(ast::DataType::Integer(None))
3173+
.with_uint8_cast_dtype(ast::DataType::SmallInt(None))
3174+
.with_uint16_cast_dtype(ast::DataType::Integer(None))
3175+
.with_uint32_cast_dtype(ast::DataType::BigInt(None))
3176+
.with_uint64_cast_dtype(ast::DataType::Numeric(ExactNumberInfo::None))
3177+
.build();
3178+
let default_unparser = Unparser::new(&DefaultDialect {});
3179+
let custom_unparser = Unparser::new(&custom_dialect);
3180+
for (data_type, default_expected, custom_expected) in [
3181+
(Int8, "CAST(a AS TINYINT)", "CAST(a AS SMALLINT)"),
3182+
(DataType::Int16, "CAST(a AS SMALLINT)", "CAST(a AS INTEGER)"),
3183+
(
3184+
DataType::UInt8,
3185+
"CAST(a AS TINYINT UNSIGNED)",
3186+
"CAST(a AS SMALLINT)",
3187+
),
3188+
(
3189+
DataType::UInt16,
3190+
"CAST(a AS SMALLINT UNSIGNED)",
3191+
"CAST(a AS INTEGER)",
3192+
),
3193+
(
3194+
DataType::UInt32,
3195+
"CAST(a AS INTEGER UNSIGNED)",
3196+
"CAST(a AS BIGINT)",
3197+
),
3198+
(
3199+
DataType::UInt64,
3200+
"CAST(a AS BIGINT UNSIGNED)",
3201+
"CAST(a AS NUMERIC)",
3202+
),
3203+
] {
3204+
let expr = Expr::Cast(Cast::new(Box::new(col("a")), data_type));
3205+
let ast = default_unparser.expr_to_sql(&expr)?;
3206+
assert_eq!(format!("{ast}"), default_expected);
3207+
let ast = custom_unparser.expr_to_sql(&expr)?;
3208+
assert_eq!(format!("{ast}"), custom_expected);
3209+
}
31533210
Ok(())
31543211
}
31553212

0 commit comments

Comments
 (0)