gengliangwang commented on code in PR #36077:
URL: https://github.com/apache/spark/pull/36077#discussion_r845271561
##########
sql/core/src/test/scala/org/apache/spark/sql/sources/InsertSuite.scala:
##########
@@ -1105,6 +1105,97 @@ class InsertSuite extends DataSourceTest with
SharedSparkSession {
}
}
+ test("INSERT INTO with user specified columns and defaults: positive tests")
{
+ withTable("t") {
+ sql("create table t(i boolean default true, s bigint default 42) using
parquet")
+ sql("insert into t (i, s) values (true, default)")
+ checkAnswer(sql("select s from t where i = true"), Seq(42L).map(i =>
Row(i)))
+ }
+ withTable("t") {
+ sql("create table t(i boolean default true, s bigint default 42) using
parquet")
+ sql("insert into t (s, i) values (default, true)")
+ checkAnswer(sql("select s from t where i = true"), Seq(42L).map(i =>
Row(i)))
+ }
+ withTable("t") {
+ sql("create table t(i boolean default true, s bigint default 42) using
parquet")
+ sql("insert into t (i) values (true)")
+ checkAnswer(sql("select s from t where i = true"), Seq(42L).map(i =>
Row(i)))
+ }
+ withTable("t") {
+ sql("create table t(i boolean default true, s bigint default 42) using
parquet")
+ sql("insert into t (i) values (default)")
+ checkAnswer(sql("select s from t where i = true"), Seq(42L).map(i =>
Row(i)))
+ }
+ withTable("t") {
+ sql("create table t(i boolean default true, s bigint default 42) using
parquet")
+ sql("insert into t (s) values (default)")
+ checkAnswer(sql("select s from t where i = true"), Seq(42L).map(i =>
Row(i)))
+ }
+ withTable("t") {
+ sql("create table t(i boolean default true, s bigint default 42) using
parquet")
+ sql("insert into t (s) select default from (select 1)")
+ checkAnswer(sql("select s from t where i = true"), Seq(42L).map(i =>
Row(i)))
+ }
+ withTable("t") {
+ sql("create table t(i boolean default true, s bigint default 42) using
parquet")
+ sql("insert into t (i) select true from (select 1)")
+ checkAnswer(sql("select s from t where i = true"), Seq(42L).map(i =>
Row(i)))
+ }
+ withTable("t") {
+ sql("create table t(i boolean, s bigint default 42, q int default 43)
using parquet")
+ sql("insert into t (i, q) select true from (select 1)")
+ checkAnswer(sql("select s from t where q = 43"), Seq(42L).map(i =>
Row(i)))
+ }
+ // 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 (i) values (true)")
+ checkAnswer(sql("select s from t where i = true"), Seq(null).map(i =>
Row(i)))
+ }
+ withTable("t") {
+ sql("create table t(i boolean default true, s bigint) using parquet")
+ sql("insert into t (i) values (default)")
+ checkAnswer(sql("select s from t where i = true"), Seq(null).map(i =>
Row(i)))
+ }
+ withTable("t") {
+ sql("create table t(i boolean, s bigint default 42) using parquet")
+ sql("insert into t (s) values (default)")
+ checkAnswer(sql("select s from t where i is null"), Seq(42L).map(i =>
Row(i)))
+ }
+ }
+ }
+
+ test("INSERT INTO with user specified columns and defaults: negative tests")
{
Review Comment:
Need a test case for `USE_NULLS_FOR_MISSING_DEFAULT_COLUMN_VALUES` as false
--
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]
