dtenedor commented on a change in pull request #35855:
URL: https://github.com/apache/spark/pull/35855#discussion_r833483971
##########
File path:
sql/core/src/test/scala/org/apache/spark/sql/sources/InsertSuite.scala
##########
@@ -846,6 +854,238 @@ class InsertSuite extends DataSourceTest with
SharedSparkSession {
}
}
+ test("INSERT INTO statements with tables with default columns") {
+ // For most of these cases, the test table 't' has two columns:
+ // (1) name 'i' with boolean type and no default value
+ // (2) name 's' with long integer type and a default value of 42L.
+ //
+ // Positive tests:
+ // When the USE_NULLS_FOR_MISSING_DEFAULT_COLUMN_VALUES configuration is
enabled, and no
+ // explicit DEFAULT value is available when the INSERT INTO statement
provides fewer
+ // values than expected, NULL values are appended in their place.
+ withSQLConf(SQLConf.USE_NULLS_FOR_MISSING_DEFAULT_COLUMN_VALUES.key ->
"true") {
+ withTable("t") {
+ sql("create table t(i boolean, s bigint) using parquet")
+ sql("insert into t values(true)")
+ checkAnswer(sql("select s from t where i = true"), Seq(null).map(i =>
Row(i)))
+ }
+ }
+ // The default value for the DEFAULT keyword is the NULL literal.
+ withTable("t") {
+ sql("create table t(i boolean, s bigint) using parquet")
+ sql("insert into t values(true, default)")
+ checkAnswer(sql("select s from t where i = true"), Seq(null).map(i =>
Row(i)))
+ }
+ // There is a complex expression in the default value.
+ withTable("t") {
+ sql("create table t(i boolean, s string default concat('abc', 'def'))
using parquet")
+ sql("insert into t values(true, default)")
+ checkAnswer(sql("select s from t where i = true"), Seq("abcdef").map(i
=> Row(i)))
+ }
+ // The default value parses correctly and the provided value type is
different but coercible.
+ withTable("t") {
+ sql("create table t(i boolean, s bigint default 42) using parquet")
+ sql("insert into t values(false)")
+ checkAnswer(sql("select s from t where i = false"), Seq(42L).map(i =>
Row(i)))
+ }
+ // There are two trailing default values referenced implicitly by the
INSERT INTO statement.
+ withTable("t") {
+ sql("create table t(i int, s bigint default 42, x bigint default 43)
using parquet")
+ sql("insert into t values(1)")
+ checkAnswer(sql("select s + x from t where i = 1"), Seq(85L).map(i =>
Row(i)))
+ }
+ // The table has a partitioning column and a default value is injected.
Review comment:
Good question. It looks like the grammar does not support this. I added
a test case and initially got a weird parsing error message. I added a rule in
the grammar to catch this and return a better error message, and updated the
new test case accordingly.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
