This is an automated email from the ASF dual-hosted git repository.
gengliang pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/spark.git
The following commit(s) were added to refs/heads/master by this push:
new 49fa2e0720d [SPARK-38908][SQL] Provide query context in runtime error
of Casting from String to Number/Date/Timestamp/Boolean
49fa2e0720d is described below
commit 49fa2e0720d3ca681d817981cbc2c7b811de2706
Author: Gengliang Wang <[email protected]>
AuthorDate: Fri Apr 15 19:20:31 2022 +0800
[SPARK-38908][SQL] Provide query context in runtime error of Casting from
String to Number/Date/Timestamp/Boolean
### What changes were proposed in this pull request?
Provide query context in runtime error of Casting from String to
Number/Date/Timestamp/Boolean.
Casting Double/Float to Timestamp shares the same error method as casting
String to Timestamp, so this PR also provides query context in its error.
### Why are the changes needed?
Provide SQL query context of runtime errors to users, so that they can
understand it better.
### Does this PR introduce _any_ user-facing change?
Yes, improve the runtime error message of Casting from String to
Number/Date/Timestamp/Boolean
### How was this patch tested?
UT
Closes #36206 from gengliangwang/castStringContext.
Authored-by: Gengliang Wang <[email protected]>
Signed-off-by: Gengliang Wang <[email protected]>
---
core/src/main/resources/error/error-classes.json | 2 +-
project/MimaExcludes.scala | 6 +-
.../spark/sql/catalyst/expressions/Cast.scala | 74 ++++++----
.../spark/sql/catalyst/util/DateTimeUtils.scala | 16 +-
.../spark/sql/catalyst/util/UTF8StringUtils.scala | 16 +-
.../spark/sql/errors/QueryExecutionErrors.scala | 22 ++-
.../scala/org/apache/spark/sql/types/Decimal.scala | 4 +-
.../src/test/resources/sql-tests/inputs/cast.sql | 11 ++
.../resources/sql-tests/results/ansi/cast.sql.out | 164 ++++++++++++++++++++-
.../resources/sql-tests/results/ansi/date.sql.out | 9 ++
.../results/ansi/datetime-parsing-invalid.sql.out | 6 +
.../sql-tests/results/ansi/interval.sql.out | 30 ++++
.../results/ansi/string-functions.sql.out | 14 +-
.../test/resources/sql-tests/results/cast.sql.out | 74 +++++++++-
.../sql-tests/results/postgreSQL/boolean.sql.out | 51 ++++++-
.../sql-tests/results/postgreSQL/float4.sql.out | 12 ++
.../sql-tests/results/postgreSQL/float8.sql.out | 12 ++
.../sql-tests/results/postgreSQL/text.sql.out | 6 +
.../results/postgreSQL/window_part2.sql.out | 5 +
.../results/postgreSQL/window_part3.sql.out | 6 +-
.../results/postgreSQL/window_part4.sql.out | 6 +-
21 files changed, 484 insertions(+), 62 deletions(-)
diff --git a/core/src/main/resources/error/error-classes.json
b/core/src/main/resources/error/error-classes.json
index f8c7084e0a1..397af26302b 100644
--- a/core/src/main/resources/error/error-classes.json
+++ b/core/src/main/resources/error/error-classes.json
@@ -103,7 +103,7 @@
"sqlState" : "22023"
},
"INVALID_INPUT_SYNTAX_FOR_NUMERIC_TYPE" : {
- "message" : [ "invalid input syntax for type numeric: %s. To return NULL
instead, use 'try_cast'. If necessary set %s to false to bypass this error." ],
+ "message" : [ "invalid input syntax for type numeric: %s. To return NULL
instead, use 'try_cast'. If necessary set %s to false to bypass this error.%s"
],
"sqlState" : "42000"
},
"INVALID_JSON_SCHEMA_MAPTYPE" : {
diff --git a/project/MimaExcludes.scala b/project/MimaExcludes.scala
index b7376286826..f0010dfcd83 100644
--- a/project/MimaExcludes.scala
+++ b/project/MimaExcludes.scala
@@ -70,7 +70,11 @@ object MimaExcludes {
ProblemFilters.exclude[MissingClassProblem]("org.apache.spark.sql.connector.read.partitioning.ClusteredDistribution"),
ProblemFilters.exclude[MissingClassProblem]("org.apache.spark.sql.connector.read.partitioning.Distribution"),
ProblemFilters.exclude[DirectMissingMethodProblem]("org.apache.spark.sql.connector.read.partitioning.Partitioning.*"),
-
ProblemFilters.exclude[ReversedMissingMethodProblem]("org.apache.spark.sql.connector.read.partitioning.Partitioning.*")
+
ProblemFilters.exclude[ReversedMissingMethodProblem]("org.apache.spark.sql.connector.read.partitioning.Partitioning.*"),
+
+ // [SPARK-38908][SQL] Provide query context in runtime error of Casting
from String to
+ // Number/Date/Timestamp/Boolean
+
ProblemFilters.exclude[DirectMissingMethodProblem]("org.apache.spark.sql.types.Decimal.fromStringANSI")
)
// Exclude rules for 3.2.x from 3.1.1
diff --git
a/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/expressions/Cast.scala
b/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/expressions/Cast.scala
index 03ecaecca06..e522c211cb2 100644
---
a/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/expressions/Cast.scala
+++
b/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/expressions/Cast.scala
@@ -467,7 +467,7 @@ abstract class CastBase extends UnaryExpression with
TimeZoneAwareExpression wit
false
} else {
if (ansiEnabled) {
- throw QueryExecutionErrors.invalidInputSyntaxForBooleanError(s)
+ throw QueryExecutionErrors.invalidInputSyntaxForBooleanError(s,
origin.context)
} else {
null
}
@@ -499,7 +499,7 @@ abstract class CastBase extends UnaryExpression with
TimeZoneAwareExpression wit
case StringType =>
buildCast[UTF8String](_, utfs => {
if (ansiEnabled) {
- DateTimeUtils.stringToTimestampAnsi(utfs, zoneId)
+ DateTimeUtils.stringToTimestampAnsi(utfs, zoneId, origin.context)
} else {
DateTimeUtils.stringToTimestamp(utfs, zoneId).orNull
}
@@ -524,14 +524,14 @@ abstract class CastBase extends UnaryExpression with
TimeZoneAwareExpression wit
// TimestampWritable.doubleToTimestamp
case DoubleType =>
if (ansiEnabled) {
- buildCast[Double](_, d => doubleToTimestampAnsi(d))
+ buildCast[Double](_, d => doubleToTimestampAnsi(d, origin.context))
} else {
buildCast[Double](_, d => doubleToTimestamp(d))
}
// TimestampWritable.floatToTimestamp
case FloatType =>
if (ansiEnabled) {
- buildCast[Float](_, f => doubleToTimestampAnsi(f.toDouble))
+ buildCast[Float](_, f => doubleToTimestampAnsi(f.toDouble,
origin.context))
} else {
buildCast[Float](_, f => doubleToTimestamp(f.toDouble))
}
@@ -541,7 +541,7 @@ abstract class CastBase extends UnaryExpression with
TimeZoneAwareExpression wit
case StringType =>
buildCast[UTF8String](_, utfs => {
if (ansiEnabled) {
- DateTimeUtils.stringToTimestampWithoutTimeZoneAnsi(utfs)
+ DateTimeUtils.stringToTimestampWithoutTimeZoneAnsi(utfs,
origin.context)
} else {
DateTimeUtils.stringToTimestampWithoutTimeZone(utfs).orNull
}
@@ -574,7 +574,7 @@ abstract class CastBase extends UnaryExpression with
TimeZoneAwareExpression wit
private[this] def castToDate(from: DataType): Any => Any = from match {
case StringType =>
if (ansiEnabled) {
- buildCast[UTF8String](_, s => DateTimeUtils.stringToDateAnsi(s))
+ buildCast[UTF8String](_, s => DateTimeUtils.stringToDateAnsi(s,
origin.context))
} else {
buildCast[UTF8String](_, s => DateTimeUtils.stringToDate(s).orNull)
}
@@ -631,7 +631,7 @@ abstract class CastBase extends UnaryExpression with
TimeZoneAwareExpression wit
// LongConverter
private[this] def castToLong(from: DataType): Any => Any = from match {
case StringType if ansiEnabled =>
- buildCast[UTF8String](_, UTF8StringUtils.toLongExact)
+ buildCast[UTF8String](_, v => UTF8StringUtils.toLongExact(v,
origin.context))
case StringType =>
val result = new LongWrapper()
buildCast[UTF8String](_, s => if (s.toLong(result)) result.value else
null)
@@ -654,7 +654,7 @@ abstract class CastBase extends UnaryExpression with
TimeZoneAwareExpression wit
// IntConverter
private[this] def castToInt(from: DataType): Any => Any = from match {
case StringType if ansiEnabled =>
- buildCast[UTF8String](_, UTF8StringUtils.toIntExact)
+ buildCast[UTF8String](_, v => UTF8StringUtils.toIntExact(v,
origin.context))
case StringType =>
val result = new IntWrapper()
buildCast[UTF8String](_, s => if (s.toInt(result)) result.value else
null)
@@ -686,7 +686,7 @@ abstract class CastBase extends UnaryExpression with
TimeZoneAwareExpression wit
// ShortConverter
private[this] def castToShort(from: DataType): Any => Any = from match {
case StringType if ansiEnabled =>
- buildCast[UTF8String](_, UTF8StringUtils.toShortExact)
+ buildCast[UTF8String](_, v => UTF8StringUtils.toShortExact(v,
origin.context))
case StringType =>
val result = new IntWrapper()
buildCast[UTF8String](_, s => if (s.toShort(result)) {
@@ -733,7 +733,7 @@ abstract class CastBase extends UnaryExpression with
TimeZoneAwareExpression wit
// ByteConverter
private[this] def castToByte(from: DataType): Any => Any = from match {
case StringType if ansiEnabled =>
- buildCast[UTF8String](_, UTF8StringUtils.toByteExact)
+ buildCast[UTF8String](_, v => UTF8StringUtils.toByteExact(v,
origin.context))
case StringType =>
val result = new IntWrapper()
buildCast[UTF8String](_, s => if (s.toByte(result)) {
@@ -815,7 +815,8 @@ abstract class CastBase extends UnaryExpression with
TimeZoneAwareExpression wit
if (d == null) null else changePrecision(d, target)
})
case StringType if ansiEnabled =>
- buildCast[UTF8String](_, s => changePrecision(Decimal.fromStringANSI(s),
target))
+ buildCast[UTF8String](_,
+ s => changePrecision(Decimal.fromStringANSI(s, origin.context),
target))
case BooleanType =>
buildCast[Boolean](_, b => toPrecision(if (b) Decimal.ONE else
Decimal.ZERO, target))
case DateType =>
@@ -844,7 +845,7 @@ abstract class CastBase extends UnaryExpression with
TimeZoneAwareExpression wit
case _: NumberFormatException =>
val d = Cast.processFloatingPointSpecialLiterals(doubleStr, false)
if(ansiEnabled && d == null) {
- throw QueryExecutionErrors.invalidInputSyntaxForNumericError(s)
+ throw QueryExecutionErrors.invalidInputSyntaxForNumericError(s,
origin.context)
} else {
d
}
@@ -869,7 +870,7 @@ abstract class CastBase extends UnaryExpression with
TimeZoneAwareExpression wit
case _: NumberFormatException =>
val f = Cast.processFloatingPointSpecialLiterals(floatStr, true)
if (ansiEnabled && f == null) {
- throw QueryExecutionErrors.invalidInputSyntaxForNumericError(s)
+ throw QueryExecutionErrors.invalidInputSyntaxForNumericError(s,
origin.context)
} else {
f
}
@@ -1016,7 +1017,7 @@ abstract class CastBase extends UnaryExpression with
TimeZoneAwareExpression wit
case CalendarIntervalType => castToIntervalCode(from)
case it: DayTimeIntervalType => castToDayTimeIntervalCode(from, it)
case it: YearMonthIntervalType => castToYearMonthIntervalCode(from, it)
- case BooleanType => castToBooleanCode(from)
+ case BooleanType => castToBooleanCode(from, ctx)
case ByteType => castToByteCode(from, ctx)
case ShortType => castToShortCode(from, ctx)
case IntegerType => castToIntCode(from, ctx)
@@ -1295,8 +1296,9 @@ abstract class CastBase extends UnaryExpression with
TimeZoneAwareExpression wit
val intOpt = ctx.freshVariable("intOpt", classOf[Option[Integer]])
(c, evPrim, evNull) =>
if (ansiEnabled) {
+ val errorContext = ctx.addReferenceObj("errCtx", origin.context)
code"""
- $evPrim =
org.apache.spark.sql.catalyst.util.DateTimeUtils.stringToDateAnsi($c);
+ $evPrim = $dateTimeUtilsCls.stringToDateAnsi($c, $errorContext);
"""
} else {
code"""
@@ -1373,9 +1375,10 @@ abstract class CastBase extends UnaryExpression with
TimeZoneAwareExpression wit
}
"""
case StringType if ansiEnabled =>
+ val errorContext = ctx.addReferenceObj("errCtx", origin.context)
(c, evPrim, evNull) =>
code"""
- Decimal $tmp = Decimal.fromStringANSI($c);
+ Decimal $tmp = Decimal.fromStringANSI($c, $errorContext);
${changePrecision(tmp, target, evPrim, evNull, canNullSafeCast,
ctx)}
"""
case BooleanType =>
@@ -1432,9 +1435,9 @@ abstract class CastBase extends UnaryExpression with
TimeZoneAwareExpression wit
val longOpt = ctx.freshVariable("longOpt", classOf[Option[Long]])
(c, evPrim, evNull) =>
if (ansiEnabled) {
+ val errorContext = ctx.addReferenceObj("errCtx", origin.context)
code"""
- $evPrim =
-
org.apache.spark.sql.catalyst.util.DateTimeUtils.stringToTimestampAnsi($c,
$zid);
+ $evPrim = $dateTimeUtilsCls.stringToTimestampAnsi($c, $zid,
$errorContext);
"""
} else {
code"""
@@ -1471,7 +1474,8 @@ abstract class CastBase extends UnaryExpression with
TimeZoneAwareExpression wit
case DoubleType =>
(c, evPrim, evNull) =>
if (ansiEnabled) {
- code"$evPrim = $dateTimeUtilsCls.doubleToTimestampAnsi($c);"
+ val errorContext = ctx.addReferenceObj("errCtx", origin.context)
+ code"$evPrim = $dateTimeUtilsCls.doubleToTimestampAnsi($c,
$errorContext);"
} else {
code"""
if (Double.isNaN($c) || Double.isInfinite($c)) {
@@ -1484,7 +1488,8 @@ abstract class CastBase extends UnaryExpression with
TimeZoneAwareExpression wit
case FloatType =>
(c, evPrim, evNull) =>
if (ansiEnabled) {
- code"$evPrim = $dateTimeUtilsCls.doubleToTimestampAnsi((double)$c);"
+ val errorContext = ctx.addReferenceObj("errCtx", origin.context)
+ code"$evPrim = $dateTimeUtilsCls.doubleToTimestampAnsi((double)$c,
$errorContext);"
} else {
code"""
if (Float.isNaN($c) || Float.isInfinite($c)) {
@@ -1503,9 +1508,9 @@ abstract class CastBase extends UnaryExpression with
TimeZoneAwareExpression wit
val longOpt = ctx.freshVariable("longOpt", classOf[Option[Long]])
(c, evPrim, evNull) =>
if (ansiEnabled) {
+ val errorContext = ctx.addReferenceObj("errCtx", origin.context)
code"""
- $evPrim =
- $dateTimeUtilsCls.stringToTimestampWithoutTimeZoneAnsi($c);
+ $evPrim =
$dateTimeUtilsCls.stringToTimestampWithoutTimeZoneAnsi($c, $errorContext);
"""
} else {
code"""
@@ -1613,12 +1618,15 @@ abstract class CastBase extends UnaryExpression with
TimeZoneAwareExpression wit
private[this] def timestampToDoubleCode(ts: ExprValue): Block =
code"$ts / (double)$MICROS_PER_SECOND"
- private[this] def castToBooleanCode(from: DataType): CastFunction = from
match {
+ private[this] def castToBooleanCode(
+ from: DataType,
+ ctx: CodegenContext): CastFunction = from match {
case StringType =>
val stringUtils =
inline"${StringUtils.getClass.getName.stripSuffix("$")}"
(c, evPrim, evNull) =>
val castFailureCode = if (ansiEnabled) {
- s"throw QueryExecutionErrors.invalidInputSyntaxForBooleanError($c);"
+ val errorContext = ctx.addReferenceObj("errCtx", origin.context)
+ s"throw QueryExecutionErrors.invalidInputSyntaxForBooleanError($c,
$errorContext);"
} else {
s"$evNull = true;"
}
@@ -1746,7 +1754,8 @@ abstract class CastBase extends UnaryExpression with
TimeZoneAwareExpression wit
private[this] def castToByteCode(from: DataType, ctx: CodegenContext):
CastFunction = from match {
case StringType if ansiEnabled =>
val stringUtils =
UTF8StringUtils.getClass.getCanonicalName.stripSuffix("$")
- (c, evPrim, evNull) => code"$evPrim = $stringUtils.toByteExact($c);"
+ val errorContext = ctx.addReferenceObj("errCtx", origin.context)
+ (c, evPrim, evNull) => code"$evPrim = $stringUtils.toByteExact($c,
$errorContext);"
case StringType =>
val wrapper = ctx.freshVariable("intWrapper",
classOf[UTF8String.IntWrapper])
(c, evPrim, evNull) =>
@@ -1782,7 +1791,8 @@ abstract class CastBase extends UnaryExpression with
TimeZoneAwareExpression wit
ctx: CodegenContext): CastFunction = from match {
case StringType if ansiEnabled =>
val stringUtils =
UTF8StringUtils.getClass.getCanonicalName.stripSuffix("$")
- (c, evPrim, evNull) => code"$evPrim = $stringUtils.toShortExact($c);"
+ val errorContext = ctx.addReferenceObj("errCtx", origin.context)
+ (c, evPrim, evNull) => code"$evPrim = $stringUtils.toShortExact($c,
$errorContext);"
case StringType =>
val wrapper = ctx.freshVariable("intWrapper",
classOf[UTF8String.IntWrapper])
(c, evPrim, evNull) =>
@@ -1816,7 +1826,8 @@ abstract class CastBase extends UnaryExpression with
TimeZoneAwareExpression wit
private[this] def castToIntCode(from: DataType, ctx: CodegenContext):
CastFunction = from match {
case StringType if ansiEnabled =>
val stringUtils =
UTF8StringUtils.getClass.getCanonicalName.stripSuffix("$")
- (c, evPrim, evNull) => code"$evPrim = $stringUtils.toIntExact($c);"
+ val errorContext = ctx.addReferenceObj("errCtx", origin.context)
+ (c, evPrim, evNull) => code"$evPrim = $stringUtils.toIntExact($c,
$errorContext);"
case StringType =>
val wrapper = ctx.freshVariable("intWrapper",
classOf[UTF8String.IntWrapper])
(c, evPrim, evNull) =>
@@ -1850,7 +1861,8 @@ abstract class CastBase extends UnaryExpression with
TimeZoneAwareExpression wit
private[this] def castToLongCode(from: DataType, ctx: CodegenContext):
CastFunction = from match {
case StringType if ansiEnabled =>
val stringUtils =
UTF8StringUtils.getClass.getCanonicalName.stripSuffix("$")
- (c, evPrim, evNull) => code"$evPrim = $stringUtils.toLongExact($c);"
+ val errorContext = ctx.addReferenceObj("errCtx", origin.context)
+ (c, evPrim, evNull) => code"$evPrim = $stringUtils.toLongExact($c,
$errorContext);"
case StringType =>
val wrapper = ctx.freshVariable("longWrapper",
classOf[UTF8String.LongWrapper])
(c, evPrim, evNull) =>
@@ -1886,7 +1898,8 @@ abstract class CastBase extends UnaryExpression with
TimeZoneAwareExpression wit
val floatStr = ctx.freshVariable("floatStr", StringType)
(c, evPrim, evNull) =>
val handleNull = if (ansiEnabled) {
- s"throw
QueryExecutionErrors.invalidInputSyntaxForNumericError($c);"
+ val errorContext = ctx.addReferenceObj("errCtx", origin.context)
+ s"throw QueryExecutionErrors.invalidInputSyntaxForNumericError($c,
$errorContext);"
} else {
s"$evNull = true;"
}
@@ -1922,7 +1935,8 @@ abstract class CastBase extends UnaryExpression with
TimeZoneAwareExpression wit
val doubleStr = ctx.freshVariable("doubleStr", StringType)
(c, evPrim, evNull) =>
val handleNull = if (ansiEnabled) {
- s"throw
QueryExecutionErrors.invalidInputSyntaxForNumericError($c);"
+ val errorContext = ctx.addReferenceObj("errCtx", origin.context)
+ s"throw QueryExecutionErrors.invalidInputSyntaxForNumericError($c,
$errorContext);"
} else {
s"$evNull = true;"
}
diff --git
a/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/util/DateTimeUtils.scala
b/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/util/DateTimeUtils.scala
index 65da5e9cb42..97ad3e3c10a 100644
---
a/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/util/DateTimeUtils.scala
+++
b/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/util/DateTimeUtils.scala
@@ -448,15 +448,15 @@ object DateTimeUtils {
}
}
- def stringToTimestampAnsi(s: UTF8String, timeZoneId: ZoneId): Long = {
+ def stringToTimestampAnsi(s: UTF8String, timeZoneId: ZoneId, errorContext:
String = ""): Long = {
stringToTimestamp(s, timeZoneId).getOrElse {
- throw QueryExecutionErrors.cannotCastToDateTimeError(s, TimestampType)
+ throw QueryExecutionErrors.cannotCastToDateTimeError(s, TimestampType,
errorContext)
}
}
- def doubleToTimestampAnsi(d: Double): Long = {
+ def doubleToTimestampAnsi(d: Double, errorContext: String): Long = {
if (d.isNaN || d.isInfinite) {
- throw QueryExecutionErrors.cannotCastToDateTimeError(d, TimestampType)
+ throw QueryExecutionErrors.cannotCastToDateTimeError(d, TimestampType,
errorContext)
} else {
DoubleExactNumeric.toLong(d * MICROS_PER_SECOND)
}
@@ -503,9 +503,9 @@ object DateTimeUtils {
stringToTimestampWithoutTimeZone(s, true)
}
- def stringToTimestampWithoutTimeZoneAnsi(s: UTF8String): Long = {
+ def stringToTimestampWithoutTimeZoneAnsi(s: UTF8String, errorContext:
String): Long = {
stringToTimestampWithoutTimeZone(s, true).getOrElse {
- throw QueryExecutionErrors.cannotCastToDateTimeError(s, TimestampNTZType)
+ throw QueryExecutionErrors.cannotCastToDateTimeError(s,
TimestampNTZType, errorContext)
}
}
@@ -621,9 +621,9 @@ object DateTimeUtils {
}
}
- def stringToDateAnsi(s: UTF8String): Int = {
+ def stringToDateAnsi(s: UTF8String, errorContext: String = ""): Int = {
stringToDate(s).getOrElse {
- throw QueryExecutionErrors.cannotCastToDateTimeError(s, DateType)
+ throw QueryExecutionErrors.cannotCastToDateTimeError(s, DateType,
errorContext)
}
}
diff --git
a/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/util/UTF8StringUtils.scala
b/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/util/UTF8StringUtils.scala
index 7fb564d1bd3..9589cf3774e 100644
---
a/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/util/UTF8StringUtils.scala
+++
b/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/util/UTF8StringUtils.scala
@@ -25,20 +25,24 @@ import org.apache.spark.unsafe.types.UTF8String
*/
object UTF8StringUtils {
- def toLongExact(s: UTF8String): Long = withException(s.toLongExact)
+ def toLongExact(s: UTF8String, errorContext: String): Long =
+ withException(s.toLongExact, errorContext)
- def toIntExact(s: UTF8String): Int = withException(s.toIntExact)
+ def toIntExact(s: UTF8String, errorContext: String): Int =
+ withException(s.toIntExact, errorContext)
- def toShortExact(s: UTF8String): Short = withException(s.toShortExact)
+ def toShortExact(s: UTF8String, errorContext: String): Short =
+ withException(s.toShortExact, errorContext)
- def toByteExact(s: UTF8String): Byte = withException(s.toByteExact)
+ def toByteExact(s: UTF8String, errorContext: String): Byte =
+ withException(s.toByteExact, errorContext)
- private def withException[A](f: => A): A = {
+ private def withException[A](f: => A, errorContext: String): A = {
try {
f
} catch {
case e: NumberFormatException =>
- throw QueryExecutionErrors.invalidInputSyntaxForNumericError(e)
+ throw QueryExecutionErrors.invalidInputSyntaxForNumericError(e,
errorContext)
}
}
}
diff --git
a/sql/catalyst/src/main/scala/org/apache/spark/sql/errors/QueryExecutionErrors.scala
b/sql/catalyst/src/main/scala/org/apache/spark/sql/errors/QueryExecutionErrors.scala
index 1dcbe82126d..e259103382e 100644
---
a/sql/catalyst/src/main/scala/org/apache/spark/sql/errors/QueryExecutionErrors.scala
+++
b/sql/catalyst/src/main/scala/org/apache/spark/sql/errors/QueryExecutionErrors.scala
@@ -104,14 +104,18 @@ object QueryExecutionErrors extends QueryErrorsBase {
decimalPrecision.toString, decimalScale.toString,
SQLConf.ANSI_ENABLED.key, context))
}
- def invalidInputSyntaxForNumericError(e: NumberFormatException):
NumberFormatException = {
+ def invalidInputSyntaxForNumericError(
+ e: NumberFormatException,
+ errorContext: String): NumberFormatException = {
new NumberFormatException(s"${e.getMessage}. To return NULL instead, use
'try_cast'. " +
- s"If necessary set ${SQLConf.ANSI_ENABLED.key} to false to bypass this
error.")
+ s"If necessary set ${SQLConf.ANSI_ENABLED.key} to false to bypass this
error." + errorContext)
}
- def invalidInputSyntaxForNumericError(s: UTF8String): NumberFormatException
= {
+ def invalidInputSyntaxForNumericError(
+ s: UTF8String,
+ errorContext: String): NumberFormatException = {
new SparkNumberFormatException(errorClass =
"INVALID_INPUT_SYNTAX_FOR_NUMERIC_TYPE",
- messageParameters = Array(toSQLValue(s, StringType),
SQLConf.ANSI_ENABLED.key))
+ messageParameters = Array(toSQLValue(s, StringType),
SQLConf.ANSI_ENABLED.key, errorContext))
}
def cannotCastFromNullTypeError(to: DataType): Throwable = {
@@ -1007,9 +1011,9 @@ object QueryExecutionErrors extends QueryErrorsBase {
e)
}
- def cannotCastToDateTimeError(value: Any, to: DataType): Throwable = {
+ def cannotCastToDateTimeError(value: Any, to: DataType, errorContext:
String): Throwable = {
new DateTimeException(s"Cannot cast $value to $to. To return NULL instead,
use 'try_cast'. " +
- s"If necessary set ${SQLConf.ANSI_ENABLED.key} to false to bypass this
error.")
+ s"If necessary set ${SQLConf.ANSI_ENABLED.key} to false to bypass this
error." + errorContext)
}
def registeringStreamingQueryListenerError(e: Exception): Throwable = {
@@ -1143,10 +1147,12 @@ object QueryExecutionErrors extends QueryErrorsBase {
"SQLUserDefinedType nor registered with UDTRegistration.}")
}
- def invalidInputSyntaxForBooleanError(s: UTF8String):
UnsupportedOperationException = {
+ def invalidInputSyntaxForBooleanError(
+ s: UTF8String,
+ errorContext: String): UnsupportedOperationException = {
new UnsupportedOperationException(s"invalid input syntax for type boolean:
$s. " +
s"To return NULL instead, use 'try_cast'. If necessary set
${SQLConf.ANSI_ENABLED.key} " +
- "to false to bypass this error.")
+ "to false to bypass this error." + errorContext)
}
def unsupportedOperandTypeForSizeFunctionError(dataType: DataType):
Throwable = {
diff --git
a/sql/catalyst/src/main/scala/org/apache/spark/sql/types/Decimal.scala
b/sql/catalyst/src/main/scala/org/apache/spark/sql/types/Decimal.scala
index 39c7e6ba580..ac6ac33451c 100644
--- a/sql/catalyst/src/main/scala/org/apache/spark/sql/types/Decimal.scala
+++ b/sql/catalyst/src/main/scala/org/apache/spark/sql/types/Decimal.scala
@@ -613,7 +613,7 @@ object Decimal {
}
}
- def fromStringANSI(str: UTF8String): Decimal = {
+ def fromStringANSI(str: UTF8String, errorContext: String = ""): Decimal = {
try {
val bigDecimal = stringToJavaBigDecimal(str)
// We fast fail because constructing a very large JavaBigDecimal to
Decimal is very slow.
@@ -626,7 +626,7 @@ object Decimal {
}
} catch {
case _: NumberFormatException =>
- throw QueryExecutionErrors.invalidInputSyntaxForNumericError(str)
+ throw QueryExecutionErrors.invalidInputSyntaxForNumericError(str,
errorContext)
}
}
diff --git a/sql/core/src/test/resources/sql-tests/inputs/cast.sql
b/sql/core/src/test/resources/sql-tests/inputs/cast.sql
index 39095cb8ce0..e391c31690f 100644
--- a/sql/core/src/test/resources/sql-tests/inputs/cast.sql
+++ b/sql/core/src/test/resources/sql-tests/inputs/cast.sql
@@ -87,3 +87,14 @@ select cast('\t\n xyz \t\r' as boolean);
select cast('23.45' as decimal(4, 2));
select cast('123.45' as decimal(4, 2));
+select cast('xyz' as decimal(4, 2));
+
+select cast('2022-01-01' as date);
+select cast('a' as date);
+select cast('2022-01-01 00:00:00' as timestamp);
+select cast('a' as timestamp);
+select cast('2022-01-01 00:00:00' as timestamp_ntz);
+select cast('a' as timestamp_ntz);
+
+select cast(cast('inf' as double) as timestamp);
+select cast(cast('inf' as float) as timestamp);
diff --git a/sql/core/src/test/resources/sql-tests/results/ansi/cast.sql.out
b/sql/core/src/test/resources/sql-tests/results/ansi/cast.sql.out
index 6b705274dc8..3de9c1f743d 100644
--- a/sql/core/src/test/resources/sql-tests/results/ansi/cast.sql.out
+++ b/sql/core/src/test/resources/sql-tests/results/ansi/cast.sql.out
@@ -1,5 +1,5 @@
-- Automatically generated by SQLQueryTestSuite
--- Number of queries: 57
+-- Number of queries: 66
-- !query
@@ -9,6 +9,9 @@ struct<>
-- !query output
java.lang.NumberFormatException
invalid input syntax for type numeric: '1.23'. To return NULL instead, use
'try_cast'. If necessary set spark.sql.ansi.enabled to false to bypass this
error.
+== SQL(line 1, position 7) ==
+SELECT CAST('1.23' AS int)
+ ^^^^^^^^^^^^^^^^^^^
-- !query
@@ -18,6 +21,9 @@ struct<>
-- !query output
java.lang.NumberFormatException
invalid input syntax for type numeric: '1.23'. To return NULL instead, use
'try_cast'. If necessary set spark.sql.ansi.enabled to false to bypass this
error.
+== SQL(line 1, position 7) ==
+SELECT CAST('1.23' AS long)
+ ^^^^^^^^^^^^^^^^^^^^
-- !query
@@ -27,6 +33,9 @@ struct<>
-- !query output
java.lang.NumberFormatException
invalid input syntax for type numeric: '-4.56'. To return NULL instead, use
'try_cast'. If necessary set spark.sql.ansi.enabled to false to bypass this
error.
+== SQL(line 1, position 7) ==
+SELECT CAST('-4.56' AS int)
+ ^^^^^^^^^^^^^^^^^^^^
-- !query
@@ -36,6 +45,9 @@ struct<>
-- !query output
java.lang.NumberFormatException
invalid input syntax for type numeric: '-4.56'. To return NULL instead, use
'try_cast'. If necessary set spark.sql.ansi.enabled to false to bypass this
error.
+== SQL(line 1, position 7) ==
+SELECT CAST('-4.56' AS long)
+ ^^^^^^^^^^^^^^^^^^^^^
-- !query
@@ -45,6 +57,9 @@ struct<>
-- !query output
java.lang.NumberFormatException
invalid input syntax for type numeric: 'abc'. To return NULL instead, use
'try_cast'. If necessary set spark.sql.ansi.enabled to false to bypass this
error.
+== SQL(line 1, position 7) ==
+SELECT CAST('abc' AS int)
+ ^^^^^^^^^^^^^^^^^^
-- !query
@@ -54,6 +69,9 @@ struct<>
-- !query output
java.lang.NumberFormatException
invalid input syntax for type numeric: 'abc'. To return NULL instead, use
'try_cast'. If necessary set spark.sql.ansi.enabled to false to bypass this
error.
+== SQL(line 1, position 7) ==
+SELECT CAST('abc' AS long)
+ ^^^^^^^^^^^^^^^^^^^
-- !query
@@ -63,6 +81,9 @@ struct<>
-- !query output
java.lang.NumberFormatException
invalid input syntax for type numeric: '1234567890123'. To return NULL
instead, use 'try_cast'. If necessary set spark.sql.ansi.enabled to false to
bypass this error.
+== SQL(line 1, position 7) ==
+SELECT CAST('1234567890123' AS int)
+ ^^^^^^^^^^^^^^^^^^^^^^^^^^^^
-- !query
@@ -72,6 +93,9 @@ struct<>
-- !query output
java.lang.NumberFormatException
invalid input syntax for type numeric: '12345678901234567890123'. To return
NULL instead, use 'try_cast'. If necessary set spark.sql.ansi.enabled to false
to bypass this error.
+== SQL(line 1, position 7) ==
+SELECT CAST('12345678901234567890123' AS long)
+ ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
-- !query
@@ -81,6 +105,9 @@ struct<>
-- !query output
java.lang.NumberFormatException
invalid input syntax for type numeric: ''. To return NULL instead, use
'try_cast'. If necessary set spark.sql.ansi.enabled to false to bypass this
error.
+== SQL(line 1, position 7) ==
+SELECT CAST('' AS int)
+ ^^^^^^^^^^^^^^^
-- !query
@@ -90,6 +117,9 @@ struct<>
-- !query output
java.lang.NumberFormatException
invalid input syntax for type numeric: ''. To return NULL instead, use
'try_cast'. If necessary set spark.sql.ansi.enabled to false to bypass this
error.
+== SQL(line 1, position 7) ==
+SELECT CAST('' AS long)
+ ^^^^^^^^^^^^^^^^
-- !query
@@ -115,6 +145,9 @@ struct<>
-- !query output
java.lang.NumberFormatException
invalid input syntax for type numeric: '123.a'. To return NULL instead, use
'try_cast'. If necessary set spark.sql.ansi.enabled to false to bypass this
error.
+== SQL(line 1, position 7) ==
+SELECT CAST('123.a' AS int)
+ ^^^^^^^^^^^^^^^^^^^^
-- !query
@@ -124,6 +157,9 @@ struct<>
-- !query output
java.lang.NumberFormatException
invalid input syntax for type numeric: '123.a'. To return NULL instead, use
'try_cast'. If necessary set spark.sql.ansi.enabled to false to bypass this
error.
+== SQL(line 1, position 7) ==
+SELECT CAST('123.a' AS long)
+ ^^^^^^^^^^^^^^^^^^^^^
-- !query
@@ -141,6 +177,9 @@ struct<>
-- !query output
java.lang.NumberFormatException
invalid input syntax for type numeric: '-2147483649'. To return NULL instead,
use 'try_cast'. If necessary set spark.sql.ansi.enabled to false to bypass this
error.
+== SQL(line 1, position 7) ==
+SELECT CAST('-2147483649' AS int)
+ ^^^^^^^^^^^^^^^^^^^^^^^^^^
-- !query
@@ -158,6 +197,9 @@ struct<>
-- !query output
java.lang.NumberFormatException
invalid input syntax for type numeric: '2147483648'. To return NULL instead,
use 'try_cast'. If necessary set spark.sql.ansi.enabled to false to bypass this
error.
+== SQL(line 1, position 7) ==
+SELECT CAST('2147483648' AS int)
+ ^^^^^^^^^^^^^^^^^^^^^^^^^
-- !query
@@ -175,6 +217,9 @@ struct<>
-- !query output
java.lang.NumberFormatException
invalid input syntax for type numeric: '-9223372036854775809'. To return NULL
instead, use 'try_cast'. If necessary set spark.sql.ansi.enabled to false to
bypass this error.
+== SQL(line 1, position 7) ==
+SELECT CAST('-9223372036854775809' AS long)
+ ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
-- !query
@@ -192,6 +237,9 @@ struct<>
-- !query output
java.lang.NumberFormatException
invalid input syntax for type numeric: '9223372036854775808'. To return NULL
instead, use 'try_cast'. If necessary set spark.sql.ansi.enabled to false to
bypass this error.
+== SQL(line 1, position 7) ==
+SELECT CAST('9223372036854775808' AS long)
+ ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
-- !query
@@ -448,6 +496,9 @@ struct<>
-- !query output
java.lang.NumberFormatException
invalid input syntax for type numeric: '1中文'. To return NULL instead, use
'try_cast'. If necessary set spark.sql.ansi.enabled to false to bypass this
error.
+== SQL(line 1, position 7) ==
+select cast('1中文' as tinyint)
+ ^^^^^^^^^^^^^^^^^^^^^^
-- !query
@@ -457,6 +508,9 @@ struct<>
-- !query output
java.lang.NumberFormatException
invalid input syntax for type numeric: '1中文'. To return NULL instead, use
'try_cast'. If necessary set spark.sql.ansi.enabled to false to bypass this
error.
+== SQL(line 1, position 7) ==
+select cast('1中文' as smallint)
+ ^^^^^^^^^^^^^^^^^^^^^^^
-- !query
@@ -466,6 +520,9 @@ struct<>
-- !query output
java.lang.NumberFormatException
invalid input syntax for type numeric: '1中文'. To return NULL instead, use
'try_cast'. If necessary set spark.sql.ansi.enabled to false to bypass this
error.
+== SQL(line 1, position 7) ==
+select cast('1中文' as INT)
+ ^^^^^^^^^^^^^^^^^^
-- !query
@@ -475,6 +532,9 @@ struct<>
-- !query output
java.lang.NumberFormatException
invalid input syntax for type numeric: '中文1'. To return NULL instead, use
'try_cast'. If necessary set spark.sql.ansi.enabled to false to bypass this
error.
+== SQL(line 1, position 7) ==
+select cast('中文1' as bigint)
+ ^^^^^^^^^^^^^^^^^^^^^
-- !query
@@ -484,6 +544,9 @@ struct<>
-- !query output
java.lang.NumberFormatException
invalid input syntax for type numeric: '1中文'. To return NULL instead, use
'try_cast'. If necessary set spark.sql.ansi.enabled to false to bypass this
error.
+== SQL(line 1, position 7) ==
+select cast('1中文' as bigint)
+ ^^^^^^^^^^^^^^^^^^^^^
-- !query
@@ -512,6 +575,9 @@ struct<>
java.lang.UnsupportedOperationException
invalid input syntax for type boolean:
xyz
. To return NULL instead, use 'try_cast'. If necessary set
spark.sql.ansi.enabled to false to bypass this error.
+== SQL(line 1, position 7) ==
+select cast('\t\n xyz \t\r' as boolean)
+ ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
-- !query
@@ -532,3 +598,99 @@ Decimal(expanded,123.45,5,2}) cannot be represented as
Decimal(4, 2). If necessa
== SQL(line 1, position 7) ==
select cast('123.45' as decimal(4, 2))
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
+
+
+-- !query
+select cast('xyz' as decimal(4, 2))
+-- !query schema
+struct<>
+-- !query output
+org.apache.spark.SparkNumberFormatException
+invalid input syntax for type numeric: 'xyz'. To return NULL instead, use
'try_cast'. If necessary set spark.sql.ansi.enabled to false to bypass this
error.
+== SQL(line 1, position 7) ==
+select cast('xyz' as decimal(4, 2))
+ ^^^^^^^^^^^^^^^^^^^^^^^^^^^^
+
+
+-- !query
+select cast('2022-01-01' as date)
+-- !query schema
+struct<CAST(2022-01-01 AS DATE):date>
+-- !query output
+2022-01-01
+
+
+-- !query
+select cast('a' as date)
+-- !query schema
+struct<>
+-- !query output
+java.time.DateTimeException
+Cannot cast a to DateType. To return NULL instead, use 'try_cast'. If
necessary set spark.sql.ansi.enabled to false to bypass this error.
+== SQL(line 1, position 7) ==
+select cast('a' as date)
+ ^^^^^^^^^^^^^^^^^
+
+
+-- !query
+select cast('2022-01-01 00:00:00' as timestamp)
+-- !query schema
+struct<CAST(2022-01-01 00:00:00 AS TIMESTAMP):timestamp>
+-- !query output
+2022-01-01 00:00:00
+
+
+-- !query
+select cast('a' as timestamp)
+-- !query schema
+struct<>
+-- !query output
+java.time.DateTimeException
+Cannot cast a to TimestampType. To return NULL instead, use 'try_cast'. If
necessary set spark.sql.ansi.enabled to false to bypass this error.
+== SQL(line 1, position 7) ==
+select cast('a' as timestamp)
+ ^^^^^^^^^^^^^^^^^^^^^^
+
+
+-- !query
+select cast('2022-01-01 00:00:00' as timestamp_ntz)
+-- !query schema
+struct<CAST(2022-01-01 00:00:00 AS TIMESTAMP_NTZ):timestamp_ntz>
+-- !query output
+2022-01-01 00:00:00
+
+
+-- !query
+select cast('a' as timestamp_ntz)
+-- !query schema
+struct<>
+-- !query output
+java.time.DateTimeException
+Cannot cast a to TimestampNTZType. To return NULL instead, use 'try_cast'. If
necessary set spark.sql.ansi.enabled to false to bypass this error.
+== SQL(line 1, position 7) ==
+select cast('a' as timestamp_ntz)
+ ^^^^^^^^^^^^^^^^^^^^^^^^^^
+
+
+-- !query
+select cast(cast('inf' as double) as timestamp)
+-- !query schema
+struct<>
+-- !query output
+java.time.DateTimeException
+Cannot cast Infinity to TimestampType. To return NULL instead, use 'try_cast'.
If necessary set spark.sql.ansi.enabled to false to bypass this error.
+== SQL(line 1, position 7) ==
+select cast(cast('inf' as double) as timestamp)
+ ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
+
+
+-- !query
+select cast(cast('inf' as float) as timestamp)
+-- !query schema
+struct<>
+-- !query output
+java.time.DateTimeException
+Cannot cast Infinity to TimestampType. To return NULL instead, use 'try_cast'.
If necessary set spark.sql.ansi.enabled to false to bypass this error.
+== SQL(line 1, position 7) ==
+select cast(cast('inf' as float) as timestamp)
+ ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
diff --git a/sql/core/src/test/resources/sql-tests/results/ansi/date.sql.out
b/sql/core/src/test/resources/sql-tests/results/ansi/date.sql.out
index c7058cd7e3b..d9777b53d21 100644
--- a/sql/core/src/test/resources/sql-tests/results/ansi/date.sql.out
+++ b/sql/core/src/test/resources/sql-tests/results/ansi/date.sql.out
@@ -233,6 +233,9 @@ struct<>
-- !query output
java.time.DateTimeException
Cannot cast xx to DateType. To return NULL instead, use 'try_cast'. If
necessary set spark.sql.ansi.enabled to false to bypass this error.
+== SQL(line 1, position 7) ==
+select next_day("xx", "Mon")
+ ^^^^^^^^^^^^^^^^^^^^^
-- !query
@@ -325,6 +328,9 @@ struct<>
-- !query output
java.lang.NumberFormatException
invalid input syntax for type numeric: '1.2'. To return NULL instead, use
'try_cast'. If necessary set spark.sql.ansi.enabled to false to bypass this
error.
+== SQL(line 1, position 7) ==
+select date_add('2011-11-11', '1.2')
+ ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
-- !query
@@ -433,6 +439,9 @@ struct<>
-- !query output
java.lang.NumberFormatException
invalid input syntax for type numeric: '1.2'. To return NULL instead, use
'try_cast'. If necessary set spark.sql.ansi.enabled to false to bypass this
error.
+== SQL(line 1, position 7) ==
+select date_sub(date'2011-11-11', '1.2')
+ ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
-- !query
diff --git
a/sql/core/src/test/resources/sql-tests/results/ansi/datetime-parsing-invalid.sql.out
b/sql/core/src/test/resources/sql-tests/results/ansi/datetime-parsing-invalid.sql.out
index 59761d5ac53..57e39bbfe3a 100644
---
a/sql/core/src/test/resources/sql-tests/results/ansi/datetime-parsing-invalid.sql.out
+++
b/sql/core/src/test/resources/sql-tests/results/ansi/datetime-parsing-invalid.sql.out
@@ -243,6 +243,9 @@ struct<>
-- !query output
java.time.DateTimeException
Cannot cast Unparseable to TimestampType. To return NULL instead, use
'try_cast'. If necessary set spark.sql.ansi.enabled to false to bypass this
error.
+== SQL(line 1, position 7) ==
+select cast("Unparseable" as timestamp)
+ ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
-- !query
@@ -252,3 +255,6 @@ struct<>
-- !query output
java.time.DateTimeException
Cannot cast Unparseable to DateType. To return NULL instead, use 'try_cast'.
If necessary set spark.sql.ansi.enabled to false to bypass this error.
+== SQL(line 1, position 7) ==
+select cast("Unparseable" as date)
+ ^^^^^^^^^^^^^^^^^^^^^^^^^^^
diff --git
a/sql/core/src/test/resources/sql-tests/results/ansi/interval.sql.out
b/sql/core/src/test/resources/sql-tests/results/ansi/interval.sql.out
index 8f88727f66f..3b8d95bca0a 100644
--- a/sql/core/src/test/resources/sql-tests/results/ansi/interval.sql.out
+++ b/sql/core/src/test/resources/sql-tests/results/ansi/interval.sql.out
@@ -123,6 +123,9 @@ struct<>
-- !query output
org.apache.spark.SparkNumberFormatException
invalid input syntax for type numeric: 'a'. To return NULL instead, use
'try_cast'. If necessary set spark.sql.ansi.enabled to false to bypass this
error.
+== SQL(line 1, position 7) ==
+select interval 2 second * 'a'
+ ^^^^^^^^^^^^^^^^^^^^^^^
-- !query
@@ -132,6 +135,9 @@ struct<>
-- !query output
org.apache.spark.SparkNumberFormatException
invalid input syntax for type numeric: 'a'. To return NULL instead, use
'try_cast'. If necessary set spark.sql.ansi.enabled to false to bypass this
error.
+== SQL(line 1, position 7) ==
+select interval 2 second / 'a'
+ ^^^^^^^^^^^^^^^^^^^^^^^
-- !query
@@ -141,6 +147,9 @@ struct<>
-- !query output
org.apache.spark.SparkNumberFormatException
invalid input syntax for type numeric: 'a'. To return NULL instead, use
'try_cast'. If necessary set spark.sql.ansi.enabled to false to bypass this
error.
+== SQL(line 1, position 7) ==
+select interval 2 year * 'a'
+ ^^^^^^^^^^^^^^^^^^^^^
-- !query
@@ -150,6 +159,9 @@ struct<>
-- !query output
org.apache.spark.SparkNumberFormatException
invalid input syntax for type numeric: 'a'. To return NULL instead, use
'try_cast'. If necessary set spark.sql.ansi.enabled to false to bypass this
error.
+== SQL(line 1, position 7) ==
+select interval 2 year / 'a'
+ ^^^^^^^^^^^^^^^^^^^^^
-- !query
@@ -175,6 +187,9 @@ struct<>
-- !query output
org.apache.spark.SparkNumberFormatException
invalid input syntax for type numeric: 'a'. To return NULL instead, use
'try_cast'. If necessary set spark.sql.ansi.enabled to false to bypass this
error.
+== SQL(line 1, position 7) ==
+select 'a' * interval 2 second
+ ^^^^^^^^^^^^^^^^^^^^^^^
-- !query
@@ -184,6 +199,9 @@ struct<>
-- !query output
org.apache.spark.SparkNumberFormatException
invalid input syntax for type numeric: 'a'. To return NULL instead, use
'try_cast'. If necessary set spark.sql.ansi.enabled to false to bypass this
error.
+== SQL(line 1, position 7) ==
+select 'a' * interval 2 year
+ ^^^^^^^^^^^^^^^^^^^^^
-- !query
@@ -1499,6 +1517,9 @@ struct<>
-- !query output
java.time.DateTimeException
Cannot cast 4 11:11 to TimestampType. To return NULL instead, use 'try_cast'.
If necessary set spark.sql.ansi.enabled to false to bypass this error.
+== SQL(line 1, position 7) ==
+select '4 11:11' - interval '4 22:12' day to minute
+ ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
-- !query
@@ -1508,6 +1529,9 @@ struct<>
-- !query output
java.time.DateTimeException
Cannot cast 4 12:12:12 to TimestampType. To return NULL instead, use
'try_cast'. If necessary set spark.sql.ansi.enabled to false to bypass this
error.
+== SQL(line 1, position 7) ==
+select '4 12:12:12' + interval '4 22:12' day to minute
+ ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
-- !query
@@ -1543,6 +1567,9 @@ struct<>
-- !query output
java.time.DateTimeException
Cannot cast 1 to TimestampType. To return NULL instead, use 'try_cast'. If
necessary set spark.sql.ansi.enabled to false to bypass this error.
+== SQL(line 1, position 7) ==
+select str - interval '4 22:12' day to minute from interval_view
+ ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
-- !query
@@ -1552,6 +1579,9 @@ struct<>
-- !query output
java.time.DateTimeException
Cannot cast 1 to TimestampType. To return NULL instead, use 'try_cast'. If
necessary set spark.sql.ansi.enabled to false to bypass this error.
+== SQL(line 1, position 7) ==
+select str + interval '4 22:12' day to minute from interval_view
+ ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
-- !query
diff --git
a/sql/core/src/test/resources/sql-tests/results/ansi/string-functions.sql.out
b/sql/core/src/test/resources/sql-tests/results/ansi/string-functions.sql.out
index c65384673c2..7d07282ab67 100644
---
a/sql/core/src/test/resources/sql-tests/results/ansi/string-functions.sql.out
+++
b/sql/core/src/test/resources/sql-tests/results/ansi/string-functions.sql.out
@@ -1,5 +1,5 @@
-- Automatically generated by SQLQueryTestSuite
--- Number of queries: 142
+-- Number of queries: 143
-- !query
@@ -83,6 +83,9 @@ struct<>
-- !query output
java.lang.NumberFormatException
invalid input syntax for type numeric: 'a'. To return NULL instead, use
'try_cast'. If necessary set spark.sql.ansi.enabled to false to bypass this
error.
+== SQL(line 1, position 42) ==
+...t("abcd", -2), left("abcd", 0), left("abcd", 'a')
+ ^^^^^^^^^^^^^^^^^
-- !query
@@ -108,6 +111,9 @@ struct<>
-- !query output
java.lang.NumberFormatException
invalid input syntax for type numeric: 'a'. To return NULL instead, use
'try_cast'. If necessary set spark.sql.ansi.enabled to false to bypass this
error.
+== SQL(line 1, position 44) ==
+...("abcd", -2), right("abcd", 0), right("abcd", 'a')
+ ^^^^^^^^^^^^^^^^^^
-- !query
@@ -414,6 +420,9 @@ struct<>
-- !query output
java.lang.NumberFormatException
invalid input syntax for type numeric: 'invalid_length'. To return NULL
instead, use 'try_cast'. If necessary set spark.sql.ansi.enabled to false to
bypass this error.
+== SQL(line 1, position 7) ==
+SELECT lpad('hi', 'invalid_length')
+ ^^^^^^^^^^^^^^^^^^^^^^^^^^^^
-- !query
@@ -423,6 +432,9 @@ struct<>
-- !query output
java.lang.NumberFormatException
invalid input syntax for type numeric: 'invalid_length'. To return NULL
instead, use 'try_cast'. If necessary set spark.sql.ansi.enabled to false to
bypass this error.
+== SQL(line 1, position 7) ==
+SELECT rpad('hi', 'invalid_length')
+ ^^^^^^^^^^^^^^^^^^^^^^^^^^^^
-- !query
diff --git a/sql/core/src/test/resources/sql-tests/results/cast.sql.out
b/sql/core/src/test/resources/sql-tests/results/cast.sql.out
index 79a1e28a143..9ed02e3bed2 100644
--- a/sql/core/src/test/resources/sql-tests/results/cast.sql.out
+++ b/sql/core/src/test/resources/sql-tests/results/cast.sql.out
@@ -1,5 +1,5 @@
-- Automatically generated by SQLQueryTestSuite
--- Number of queries: 57
+-- Number of queries: 66
-- !query
@@ -474,3 +474,75 @@ select cast('123.45' as decimal(4, 2))
struct<CAST(123.45 AS DECIMAL(4,2)):decimal(4,2)>
-- !query output
NULL
+
+
+-- !query
+select cast('xyz' as decimal(4, 2))
+-- !query schema
+struct<CAST(xyz AS DECIMAL(4,2)):decimal(4,2)>
+-- !query output
+NULL
+
+
+-- !query
+select cast('2022-01-01' as date)
+-- !query schema
+struct<CAST(2022-01-01 AS DATE):date>
+-- !query output
+2022-01-01
+
+
+-- !query
+select cast('a' as date)
+-- !query schema
+struct<CAST(a AS DATE):date>
+-- !query output
+NULL
+
+
+-- !query
+select cast('2022-01-01 00:00:00' as timestamp)
+-- !query schema
+struct<CAST(2022-01-01 00:00:00 AS TIMESTAMP):timestamp>
+-- !query output
+2022-01-01 00:00:00
+
+
+-- !query
+select cast('a' as timestamp)
+-- !query schema
+struct<CAST(a AS TIMESTAMP):timestamp>
+-- !query output
+NULL
+
+
+-- !query
+select cast('2022-01-01 00:00:00' as timestamp_ntz)
+-- !query schema
+struct<CAST(2022-01-01 00:00:00 AS TIMESTAMP_NTZ):timestamp_ntz>
+-- !query output
+2022-01-01 00:00:00
+
+
+-- !query
+select cast('a' as timestamp_ntz)
+-- !query schema
+struct<CAST(a AS TIMESTAMP_NTZ):timestamp_ntz>
+-- !query output
+NULL
+
+
+-- !query
+select cast(cast('inf' as double) as timestamp)
+-- !query schema
+struct<CAST(CAST(inf AS DOUBLE) AS TIMESTAMP):timestamp>
+-- !query output
+NULL
+
+
+-- !query
+select cast(cast('inf' as float) as timestamp)
+-- !query schema
+struct<CAST(CAST(inf AS FLOAT) AS TIMESTAMP):timestamp>
+-- !query output
+NULL
diff --git
a/sql/core/src/test/resources/sql-tests/results/postgreSQL/boolean.sql.out
b/sql/core/src/test/resources/sql-tests/results/postgreSQL/boolean.sql.out
index 4aba60b0220..166bea4a722 100644
--- a/sql/core/src/test/resources/sql-tests/results/postgreSQL/boolean.sql.out
+++ b/sql/core/src/test/resources/sql-tests/results/postgreSQL/boolean.sql.out
@@ -57,6 +57,9 @@ struct<>
-- !query output
java.lang.UnsupportedOperationException
invalid input syntax for type boolean: test. To return NULL instead, use
'try_cast'. If necessary set spark.sql.ansi.enabled to false to bypass this
error.
+== SQL(line 1, position 7) ==
+SELECT boolean('test') AS error
+ ^^^^^^^^^^^^^^^
-- !query
@@ -74,6 +77,9 @@ struct<>
-- !query output
java.lang.UnsupportedOperationException
invalid input syntax for type boolean: foo. To return NULL instead, use
'try_cast'. If necessary set spark.sql.ansi.enabled to false to bypass this
error.
+== SQL(line 1, position 7) ==
+SELECT boolean('foo') AS error
+ ^^^^^^^^^^^^^^
-- !query
@@ -99,6 +105,9 @@ struct<>
-- !query output
java.lang.UnsupportedOperationException
invalid input syntax for type boolean: yeah. To return NULL instead, use
'try_cast'. If necessary set spark.sql.ansi.enabled to false to bypass this
error.
+== SQL(line 1, position 7) ==
+SELECT boolean('yeah') AS error
+ ^^^^^^^^^^^^^^^
-- !query
@@ -124,6 +133,9 @@ struct<>
-- !query output
java.lang.UnsupportedOperationException
invalid input syntax for type boolean: nay. To return NULL instead, use
'try_cast'. If necessary set spark.sql.ansi.enabled to false to bypass this
error.
+== SQL(line 1, position 7) ==
+SELECT boolean('nay') AS error
+ ^^^^^^^^^^^^^^
-- !query
@@ -133,6 +145,9 @@ struct<>
-- !query output
java.lang.UnsupportedOperationException
invalid input syntax for type boolean: on. To return NULL instead, use
'try_cast'. If necessary set spark.sql.ansi.enabled to false to bypass this
error.
+== SQL(line 1, position 7) ==
+SELECT boolean('on') AS true
+ ^^^^^^^^^^^^^
-- !query
@@ -142,6 +157,9 @@ struct<>
-- !query output
java.lang.UnsupportedOperationException
invalid input syntax for type boolean: off. To return NULL instead, use
'try_cast'. If necessary set spark.sql.ansi.enabled to false to bypass this
error.
+== SQL(line 1, position 7) ==
+SELECT boolean('off') AS `false`
+ ^^^^^^^^^^^^^^
-- !query
@@ -151,6 +169,9 @@ struct<>
-- !query output
java.lang.UnsupportedOperationException
invalid input syntax for type boolean: of. To return NULL instead, use
'try_cast'. If necessary set spark.sql.ansi.enabled to false to bypass this
error.
+== SQL(line 1, position 7) ==
+SELECT boolean('of') AS `false`
+ ^^^^^^^^^^^^^
-- !query
@@ -160,6 +181,9 @@ struct<>
-- !query output
java.lang.UnsupportedOperationException
invalid input syntax for type boolean: o. To return NULL instead, use
'try_cast'. If necessary set spark.sql.ansi.enabled to false to bypass this
error.
+== SQL(line 1, position 7) ==
+SELECT boolean('o') AS error
+ ^^^^^^^^^^^^
-- !query
@@ -169,6 +193,9 @@ struct<>
-- !query output
java.lang.UnsupportedOperationException
invalid input syntax for type boolean: on_. To return NULL instead, use
'try_cast'. If necessary set spark.sql.ansi.enabled to false to bypass this
error.
+== SQL(line 1, position 7) ==
+SELECT boolean('on_') AS error
+ ^^^^^^^^^^^^^^
-- !query
@@ -178,6 +205,9 @@ struct<>
-- !query output
java.lang.UnsupportedOperationException
invalid input syntax for type boolean: off_. To return NULL instead, use
'try_cast'. If necessary set spark.sql.ansi.enabled to false to bypass this
error.
+== SQL(line 1, position 7) ==
+SELECT boolean('off_') AS error
+ ^^^^^^^^^^^^^^^
-- !query
@@ -195,6 +225,9 @@ struct<>
-- !query output
java.lang.UnsupportedOperationException
invalid input syntax for type boolean: 11. To return NULL instead, use
'try_cast'. If necessary set spark.sql.ansi.enabled to false to bypass this
error.
+== SQL(line 1, position 7) ==
+SELECT boolean('11') AS error
+ ^^^^^^^^^^^^^
-- !query
@@ -212,6 +245,9 @@ struct<>
-- !query output
java.lang.UnsupportedOperationException
invalid input syntax for type boolean: 000. To return NULL instead, use
'try_cast'. If necessary set spark.sql.ansi.enabled to false to bypass this
error.
+== SQL(line 1, position 7) ==
+SELECT boolean('000') AS error
+ ^^^^^^^^^^^^^^
-- !query
@@ -221,6 +257,9 @@ struct<>
-- !query output
java.lang.UnsupportedOperationException
invalid input syntax for type boolean: . To return NULL instead, use
'try_cast'. If necessary set spark.sql.ansi.enabled to false to bypass this
error.
+== SQL(line 1, position 7) ==
+SELECT boolean('') AS error
+ ^^^^^^^^^^^
-- !query
@@ -327,6 +366,9 @@ struct<>
-- !query output
java.lang.UnsupportedOperationException
invalid input syntax for type boolean: tru e . To return NULL instead, use
'try_cast'. If necessary set spark.sql.ansi.enabled to false to bypass this
error.
+== SQL(line 1, position 7) ==
+SELECT boolean(string(' tru e ')) AS invalid
+ ^^^^^^^^^^^^^^^^^^^^^^^^^^^
-- !query
@@ -336,6 +378,9 @@ struct<>
-- !query output
java.lang.UnsupportedOperationException
invalid input syntax for type boolean: . To return NULL instead, use
'try_cast'. If necessary set spark.sql.ansi.enabled to false to bypass this
error.
+== SQL(line 1, position 7) ==
+SELECT boolean(string('')) AS invalid
+ ^^^^^^^^^^^^^^^^^^^
-- !query
@@ -479,7 +524,11 @@ INSERT INTO BOOLTBL2
struct<>
-- !query output
org.apache.spark.sql.AnalysisException
-failed to evaluate expression CAST('XXX' AS BOOLEAN): invalid input syntax for
type boolean: XXX. To return NULL instead, use 'try_cast'. If necessary set
spark.sql.ansi.enabled to false to bypass this error.; line 2 pos 3
+failed to evaluate expression CAST('XXX' AS BOOLEAN): invalid input syntax for
type boolean: XXX. To return NULL instead, use 'try_cast'. If necessary set
spark.sql.ansi.enabled to false to bypass this error.
+== SQL(line 2, position 11) ==
+ VALUES (boolean('XXX'))
+ ^^^^^^^^^^^^^^
+; line 2 pos 3
-- !query
diff --git
a/sql/core/src/test/resources/sql-tests/results/postgreSQL/float4.sql.out
b/sql/core/src/test/resources/sql-tests/results/postgreSQL/float4.sql.out
index eccfdbae757..39636e02159 100644
--- a/sql/core/src/test/resources/sql-tests/results/postgreSQL/float4.sql.out
+++ b/sql/core/src/test/resources/sql-tests/results/postgreSQL/float4.sql.out
@@ -97,6 +97,9 @@ struct<>
-- !query output
org.apache.spark.SparkNumberFormatException
invalid input syntax for type numeric: 'N A N'. To return NULL instead, use
'try_cast'. If necessary set spark.sql.ansi.enabled to false to bypass this
error.
+== SQL(line 1, position 7) ==
+SELECT float('N A N')
+ ^^^^^^^^^^^^^^
-- !query
@@ -106,6 +109,9 @@ struct<>
-- !query output
org.apache.spark.SparkNumberFormatException
invalid input syntax for type numeric: 'NaN x'. To return NULL instead, use
'try_cast'. If necessary set spark.sql.ansi.enabled to false to bypass this
error.
+== SQL(line 1, position 7) ==
+SELECT float('NaN x')
+ ^^^^^^^^^^^^^^
-- !query
@@ -115,6 +121,9 @@ struct<>
-- !query output
org.apache.spark.SparkNumberFormatException
invalid input syntax for type numeric: ' INFINITY x'. To return NULL
instead, use 'try_cast'. If necessary set spark.sql.ansi.enabled to false to
bypass this error.
+== SQL(line 1, position 7) ==
+SELECT float(' INFINITY x')
+ ^^^^^^^^^^^^^^^^^^^^^^^
-- !query
@@ -148,6 +157,9 @@ struct<>
-- !query output
org.apache.spark.SparkNumberFormatException
invalid input syntax for type numeric: 'nan'. To return NULL instead, use
'try_cast'. If necessary set spark.sql.ansi.enabled to false to bypass this
error.
+== SQL(line 1, position 13) ==
+SELECT float(decimal('nan'))
+ ^^^^^^^^^^^^^^
-- !query
diff --git
a/sql/core/src/test/resources/sql-tests/results/postgreSQL/float8.sql.out
b/sql/core/src/test/resources/sql-tests/results/postgreSQL/float8.sql.out
index d143e1f1c59..b2f61306c7c 100644
--- a/sql/core/src/test/resources/sql-tests/results/postgreSQL/float8.sql.out
+++ b/sql/core/src/test/resources/sql-tests/results/postgreSQL/float8.sql.out
@@ -129,6 +129,9 @@ struct<>
-- !query output
org.apache.spark.SparkNumberFormatException
invalid input syntax for type numeric: 'N A N'. To return NULL instead, use
'try_cast'. If necessary set spark.sql.ansi.enabled to false to bypass this
error.
+== SQL(line 1, position 7) ==
+SELECT double('N A N')
+ ^^^^^^^^^^^^^^^
-- !query
@@ -138,6 +141,9 @@ struct<>
-- !query output
org.apache.spark.SparkNumberFormatException
invalid input syntax for type numeric: 'NaN x'. To return NULL instead, use
'try_cast'. If necessary set spark.sql.ansi.enabled to false to bypass this
error.
+== SQL(line 1, position 7) ==
+SELECT double('NaN x')
+ ^^^^^^^^^^^^^^^
-- !query
@@ -147,6 +153,9 @@ struct<>
-- !query output
org.apache.spark.SparkNumberFormatException
invalid input syntax for type numeric: ' INFINITY x'. To return NULL
instead, use 'try_cast'. If necessary set spark.sql.ansi.enabled to false to
bypass this error.
+== SQL(line 1, position 7) ==
+SELECT double(' INFINITY x')
+ ^^^^^^^^^^^^^^^^^^^^^^^^
-- !query
@@ -180,6 +189,9 @@ struct<>
-- !query output
org.apache.spark.SparkNumberFormatException
invalid input syntax for type numeric: 'nan'. To return NULL instead, use
'try_cast'. If necessary set spark.sql.ansi.enabled to false to bypass this
error.
+== SQL(line 1, position 14) ==
+SELECT double(decimal('nan'))
+ ^^^^^^^^^^^^^^
-- !query
diff --git
a/sql/core/src/test/resources/sql-tests/results/postgreSQL/text.sql.out
b/sql/core/src/test/resources/sql-tests/results/postgreSQL/text.sql.out
index 9f9f212c731..836370935f6 100755
--- a/sql/core/src/test/resources/sql-tests/results/postgreSQL/text.sql.out
+++ b/sql/core/src/test/resources/sql-tests/results/postgreSQL/text.sql.out
@@ -66,6 +66,9 @@ struct<>
-- !query output
java.lang.NumberFormatException
invalid input syntax for type numeric: 'four: 2'. To return NULL instead, use
'try_cast'. If necessary set spark.sql.ansi.enabled to false to bypass this
error.
+== SQL(line 1, position 7) ==
+select string('four: ') || 2+2
+ ^^^^^^^^^^^^^^^^^^^^^^^
-- !query
@@ -75,6 +78,9 @@ struct<>
-- !query output
java.lang.NumberFormatException
invalid input syntax for type numeric: 'four: 2'. To return NULL instead, use
'try_cast'. If necessary set spark.sql.ansi.enabled to false to bypass this
error.
+== SQL(line 1, position 7) ==
+select 'four: ' || 2+2
+ ^^^^^^^^^^^^^^^
-- !query
diff --git
a/sql/core/src/test/resources/sql-tests/results/postgreSQL/window_part2.sql.out
b/sql/core/src/test/resources/sql-tests/results/postgreSQL/window_part2.sql.out
index 158196e7c82..1d48d7c7b92 100644
---
a/sql/core/src/test/resources/sql-tests/results/postgreSQL/window_part2.sql.out
+++
b/sql/core/src/test/resources/sql-tests/results/postgreSQL/window_part2.sql.out
@@ -463,6 +463,11 @@ struct<>
-- !query output
java.lang.NumberFormatException
invalid input syntax for type numeric: 'NaN'. To return NULL instead, use
'try_cast'. If necessary set spark.sql.ansi.enabled to false to bypass this
error.
+== SQL(line 3, position 12) ==
+window w as (order by f_numeric range between
+ ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
+ 1.1 preceding and 'NaN' following)
+^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
-- !query
diff --git
a/sql/core/src/test/resources/sql-tests/results/postgreSQL/window_part3.sql.out
b/sql/core/src/test/resources/sql-tests/results/postgreSQL/window_part3.sql.out
index 680c5707a45..c799d65985d 100644
---
a/sql/core/src/test/resources/sql-tests/results/postgreSQL/window_part3.sql.out
+++
b/sql/core/src/test/resources/sql-tests/results/postgreSQL/window_part3.sql.out
@@ -72,7 +72,11 @@ insert into datetimes values
struct<>
-- !query output
org.apache.spark.sql.AnalysisException
-failed to evaluate expression CAST('11:00 BST' AS TIMESTAMP): Cannot cast
11:00 BST to TimestampType. To return NULL instead, use 'try_cast'. If
necessary set spark.sql.ansi.enabled to false to bypass this error.; line 1 pos
22
+failed to evaluate expression CAST('11:00 BST' AS TIMESTAMP): Cannot cast
11:00 BST to TimestampType. To return NULL instead, use 'try_cast'. If
necessary set spark.sql.ansi.enabled to false to bypass this error.
+== SQL(line 2, position 23) ==
+(1, timestamp '11:00', cast ('11:00 BST' as timestamp), cast ('1 year' as
timestamp), ...
+ ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
+; line 1 pos 22
-- !query
diff --git
a/sql/core/src/test/resources/sql-tests/results/postgreSQL/window_part4.sql.out
b/sql/core/src/test/resources/sql-tests/results/postgreSQL/window_part4.sql.out
index c937d663771..87beeacc0bc 100644
---
a/sql/core/src/test/resources/sql-tests/results/postgreSQL/window_part4.sql.out
+++
b/sql/core/src/test/resources/sql-tests/results/postgreSQL/window_part4.sql.out
@@ -501,4 +501,8 @@ FROM (VALUES(1,1),(2,2),(3,(cast('nan' as
int))),(4,3),(5,4)) t(a,b)
struct<>
-- !query output
org.apache.spark.sql.AnalysisException
-failed to evaluate expression CAST('nan' AS INT): invalid input syntax for
type numeric: 'nan'. To return NULL instead, use 'try_cast'. If necessary set
spark.sql.ansi.enabled to false to bypass this error.; line 3 pos 6
+failed to evaluate expression CAST('nan' AS INT): invalid input syntax for
type numeric: 'nan'. To return NULL instead, use 'try_cast'. If necessary set
spark.sql.ansi.enabled to false to bypass this error.
+== SQL(line 3, position 28) ==
+FROM (VALUES(1,1),(2,2),(3,(cast('nan' as int))),(4,3),(5,4)) t(a,b)
+ ^^^^^^^^^^^^^^^^^^
+; line 3 pos 6
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
