milastdbx commented on code in PR #48621:
URL: https://github.com/apache/spark/pull/48621#discussion_r1818833708
##########
connector/docker-integration-tests/src/test/scala/org/apache/spark/sql/jdbc/v2/MsSqlServerIntegrationSuite.scala:
##########
@@ -146,4 +146,66 @@ class MsSqlServerIntegrationSuite extends
DockerJDBCIntegrationV2Suite with V2JD
|""".stripMargin)
assert(df.collect().length == 2)
}
+
+ test("SPARK-50087: SqlServer handle booleans in IF in SELECT test") {
+ // This doesn't compile on SqlServer unless result boolean expressions
+ // in IF / CASE WHEN are wrapped with an IIF(<>, 1, 0).
+ val df = sql(
+ s"""|WITH dummy AS (
+ | SELECT
+ | DISTINCT name AS full_name,
+ | UPPER(name) AS test_type,
+ | name,
+ | IF(
+ | LOWER(name) = 'adfsaef' OR LOWER(name) = 'agadg',
Review Comment:
nit:
consider changing test literals to something more meaningful.
its easier to check what answer you are expecting
##########
connector/docker-integration-tests/src/test/scala/org/apache/spark/sql/jdbc/v2/MsSqlServerIntegrationSuite.scala:
##########
@@ -146,4 +146,66 @@ class MsSqlServerIntegrationSuite extends
DockerJDBCIntegrationV2Suite with V2JD
|""".stripMargin)
assert(df.collect().length == 2)
}
+
+ test("SPARK-50087: SqlServer handle booleans in IF in SELECT test") {
+ // This doesn't compile on SqlServer unless result boolean expressions
+ // in IF / CASE WHEN are wrapped with an IIF(<>, 1, 0).
+ val df = sql(
+ s"""|WITH dummy AS (
+ | SELECT
+ | DISTINCT name AS full_name,
+ | UPPER(name) AS test_type,
+ | name,
+ | IF(
+ | LOWER(name) = 'adfsaef' OR LOWER(name) = 'agadg',
+ | 'agfagff',
+ | IF(
+ | LOWER(name) = 'adgfda' OR LOWER(name) = 'ssadf',
+ | 'sxzvfvxf',
+ | IF(
+ | LOWER(name) = 'sdfadsf' OR LOWER(name) = 'sadfgvad',
+ | 'sAFvadsfvcds',
+ | LOWER(name)
+ | )
+ | )
+ | ) AS test_type_name
+ | FROM $catalogName.employee
+ |),
+ |dummy_new AS (
+ | SELECT *
+ | FROM dummy WHERE test_type_name = 'safcdfz'
+ |)
+ |SELECT * FROM dummy_new limit 1""".stripMargin
+ )
+ df.collect()
Review Comment:
should we have some pushdown check ?
##########
connector/docker-integration-tests/src/test/scala/org/apache/spark/sql/jdbc/v2/MsSqlServerIntegrationSuite.scala:
##########
@@ -146,4 +146,66 @@ class MsSqlServerIntegrationSuite extends
DockerJDBCIntegrationV2Suite with V2JD
|""".stripMargin)
assert(df.collect().length == 2)
}
+
+ test("SPARK-50087: SqlServer handle booleans in IF in SELECT test") {
+ // This doesn't compile on SqlServer unless result boolean expressions
+ // in IF / CASE WHEN are wrapped with an IIF(<>, 1, 0).
+ val df = sql(
+ s"""|WITH dummy AS (
+ | SELECT
+ | DISTINCT name AS full_name,
+ | UPPER(name) AS test_type,
+ | name,
+ | IF(
+ | LOWER(name) = 'adfsaef' OR LOWER(name) = 'agadg',
+ | 'agfagff',
+ | IF(
+ | LOWER(name) = 'adgfda' OR LOWER(name) = 'ssadf',
+ | 'sxzvfvxf',
+ | IF(
+ | LOWER(name) = 'sdfadsf' OR LOWER(name) = 'sadfgvad',
+ | 'sAFvadsfvcds',
+ | LOWER(name)
+ | )
+ | )
+ | ) AS test_type_name
+ | FROM $catalogName.employee
+ |),
+ |dummy_new AS (
+ | SELECT *
+ | FROM dummy WHERE test_type_name = 'safcdfz'
+ |)
+ |SELECT * FROM dummy_new limit 1""".stripMargin
+ )
+ df.collect()
+ }
+
+ test("SPARK-50087: SqlServer handle booleans in CASE WHEN test") {
+ val df = sql(
+ s"""|SELECT * FROM $catalogName.employee
+ |WHERE CASE WHEN name = '1' THEN name = 'barxxyz' ELSE NOT (name =
'barxxyz') END
+ |""".stripMargin
+ )
+ df.collect()
+ }
+
+ test("SPARK-50087: SqlServer handle booleans in CASE WHEN with always true
test") {
+ val df = sql(
+ s"""|SELECT * FROM $catalogName.employee
+ |WHERE CASE WHEN (name = 'barxxyz') THEN (name = 'barx') ELSE (1=1)
END
+ |""".stripMargin
+ )
+ df.collect()
+ }
+
+ test("SPARK-50087: SqlServer handle booleans in nested CASE WHEN test") {
+ val df = sql(
+ s"""|SELECT * FROM $catalogName.employee
+ |WHERE CASE WHEN (name = 'barxxyz') THEN
+ | CASE WHEN (name = '5') THEN (name = 'barx') ELSE (name = 'aweda')
END
+ | ELSE (name = '1') END
+ |""".stripMargin
+ )
+ df.collect()
Review Comment:
im not sure but do we have test case for when type is not boolean ?
--
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]
