[jira] [Created] (SPARK-52628) Difference results from Hive View and Temp View based on the same SQL query

["Yu-Ting LIN (Jira)" <ji...@apache.org>]

Yu-Ting LIN created SPARK-52628:
-----------------------------------

             Summary: Difference results from Hive View and Temp View based on the same SQL query
                 Key: SPARK-52628
                 URL: https://issues.apache.org/jira/browse/SPARK-52628
             Project: Spark
          Issue Type: Question
          Components: SQL
    Affects Versions: 3.5.3
            Reporter: Yu-Ting LIN


*Describe the bug*

I have a view {{demo.scr_opt}} which contains more than one records (1060767).

If I query it directly using the following SQL command, then it returns more than one records (1060767).
{{}}
{code:java}
spark.sql("""SELECT
    *,
    CASE
        WHEN SexName = '女' THEN 0
        WHEN SexName = '男' THEN 1
    END AS Sex
FROM demo.scr_opt WHERE SexName IN ('女', '男')""").count() {code}
{{}}
 
However, if I created a HIVE permanent view based on the SQL, then this view returns no records.
{code:java}
spark.sql("""CREATE OR REPLACE VIEW demo.scr_opt1 AS (
SELECT
    *,
    CASE
        WHEN SexName = '女' THEN 0
        WHEN SexName = '男' THEN 1
    END AS Sex
FROM demo.scr_opt WHERE SexName IN ('女', '男'))""")
spark.sql("SELECT * FROM demo.scr_opt1).count() {code}
If I used the query with createOrReplaceTempView("demo.scr_opt1"), then it return records again.
{code:java}
spark.sql("""SELECT
    *,
    CASE
        WHEN SexName = '女' THEN 0
        WHEN SexName = '男' THEN 1
    END AS Sex
FROM demo.scr_opt WHERE SexName IN ('女', '男')""").createOrReplaceTempView("scr_opt1")
spark.sql("SELECT * FROM scr_opt1") 
{code}

What could be the root cause of the strange phenomenon ?

This can be reproduced in different Spark environment (Local Kubernetes or Google Dataproc.){{{}{}}}



--
This message was sent by Atlassian Jira
(v8.20.10#820010)

---------------------------------------------------------------------
To unsubscribe, e-mail: issues-unsubscribe@spark.apache.org
For additional commands, e-mail: issues-help@spark.apache.org