Uploaded image for project: 'Spark'
  1. Spark
  2. SPARK-19766

INNER JOIN on constant alias columns return incorrect results

    Details

    • Type: Bug
    • Status: Resolved
    • Priority: Critical
    • Resolution: Fixed
    • Affects Version/s: 2.1.0
    • Fix Version/s: 2.0.3, 2.1.1, 2.2.0
    • Component/s: SQL
    • Labels:

      Description

      We can demonstrate the problem with the following data set and query:

      val spark = SparkSession.builder().appName("test").master("local").getOrCreate()
      
      val sql1 =
        """
          |create temporary view t1 as select * from values
          |(1)
          |as grouping(a)
        """.stripMargin
      
      val sql2 =
        """
          |create temporary view t2 as select * from values
          |(1)
          |as grouping(a)
        """.stripMargin
      
      val sql3 =
        """
          |create temporary view t3 as select * from values
          |(1),
          |(1)
          |as grouping(a)
        """.stripMargin
      
      val sql4 =
        """
          |create temporary view t4 as select * from values
          |(1),
          |(1)
          |as grouping(a)
        """.stripMargin
      
      val sqlA =
        """
          |create temporary view ta as
          |select a, 'a' as tag from t1 union all
          |select a, 'b' as tag from t2
        """.stripMargin
      
      val sqlB =
        """
          |create temporary view tb as
          |select a, 'a' as tag from t3 union all
          |select a, 'b' as tag from t4
        """.stripMargin
      
      val sql =
        """
          |select tb.* from ta inner join tb on
          |ta.a = tb.a and
          |ta.tag = tb.tag
        """.stripMargin
      
      spark.sql(sql1)
      spark.sql(sql2)
      spark.sql(sql3)
      spark.sql(sql4)
      spark.sql(sqlA)
      spark.sql(sqlB)
      spark.sql(sql).show()
      

      The results which is incorrect:

      +---+---+
      |  a|tag|
      +---+---+
      |  1|  b|
      |  1|  b|
      |  1|  a|
      |  1|  a|
      |  1|  b|
      |  1|  b|
      |  1|  a|
      |  1|  a|
      +---+---+
      

      The correct results should be:

      +---+---+
      |  a|tag|
      +---+---+
      |  1|  a|
      |  1|  a|
      |  1|  b|
      |  1|  b|
      +---+---+
      

        Attachments

          Activity

            People

            • Assignee:
              stanzhai StanZhai
              Reporter:
              stanzhai StanZhai
            • Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: