title: Spark SQL UNION - ORDER BY column not in SELECT
body: <p>I'm doing a UNION of two temp tables and trying to order by column but spark complains that the column I am ordering by cannot be resolved. Is this a bug or I'm missing something?</p>
  <pre class="lang-scala prettyprint-override"><code>lazy val spark: SparkSession = SparkSession.builder.master(&quot;local[*]&quot;).getOrCreate()
        import org.apache.spark.sql.types.StringType
  
        val oldOrders = Seq(
          Seq(&quot;old_order_id1&quot;, &quot;old_order_name1&quot;, &quot;true&quot;),
          Seq(&quot;old_order_id2&quot;, &quot;old_order_name2&quot;, &quot;true&quot;)
        )
  
        val newOrders = Seq(
          Seq(&quot;new_order_id1&quot;, &quot;new_order_name1&quot;, &quot;false&quot;),
          Seq(&quot;new_order_id2&quot;, &quot;new_order_name2&quot;, &quot;false&quot;)
        )
        val schema = new StructType()
          .add(&quot;id&quot;, StringType)
          .add(&quot;name&quot;, StringType)
          .add(&quot;is_old&quot;, StringType)
  
        val oldOrdersDF = spark.createDataFrame(spark.sparkContext.makeRDD(oldOrders.map(x =&gt; Row(x:_*))), schema)
        val newOrdersDF = spark.createDataFrame(spark.sparkContext.makeRDD(newOrders.map(x =&gt; Row(x:_*))), schema)
  
        oldOrdersDF.createOrReplaceTempView(&quot;old_orders&quot;)
        newOrdersDF.createOrReplaceTempView(&quot;new_orders&quot;)
  
        //ordering by column not in select works if I'm not doing UNION
        spark.sql(
          &quot;&quot;&quot;
            |SELECT oo.id, oo.name FROM old_orders oo
            |ORDER BY oo.is_old
          &quot;&quot;&quot;.stripMargin).show()
  
        //ordering by column not in select doesn't work as I'm doing a UNION
        spark.sql(
          &quot;&quot;&quot;
            |SELECT oo.id, oo.name FROM old_orders oo
            |UNION
            |SELECT no.id, no.name FROM new_orders no
            |ORDER BY oo.is_old
          &quot;&quot;&quot;.stripMargin).show()
  
  The output of the above code is:
  
      +-------------+---------------+
      |           id|           name|
      +-------------+---------------+
      |old_order_id1|old_order_name1|
      |old_order_id2|old_order_name2|
      +-------------+---------------+
      
      
      cannot resolve '`oo.is_old`' given input columns: [id, name]; line 5 pos 9;
      'Sort ['oo.is_old ASC NULLS FIRST], true
      +- Distinct
         +- Union
            :- Project [id#121, name#122]
            :  +- SubqueryAlias oo
            :     +- SubqueryAlias old_orders
            :        +- LogicalRDD [id#121, name#122, is_old#123]
            +- Project [id#131, name#132]
               +- SubqueryAlias no
                  +- SubqueryAlias new_orders
                     +- LogicalRDD [id#131, name#132, is_old#133]
      
      org.apache.spark.sql.AnalysisException: cannot resolve '`oo.is_old`' given input columns: [id, name]; line 5 pos 9;
      'Sort ['oo.is_old ASC NULLS FIRST], true
      +- Distinct
         +- Union
            :- Project [id#121, name#122]
            :  +- SubqueryAlias oo
            :     +- SubqueryAlias old_orders
            :        +- LogicalRDD [id#121, name#122, is_old#123]
            +- Project [id#131, name#132]
               +- SubqueryAlias no
                  +- SubqueryAlias new_orders
                     +- LogicalRDD [id#131, name#132, is_old#133]
  </code></pre>
  <p>So ordering by a column that's not in the SELECT clause works if I'm not doing a UNION and it fails if I'm doing a UNION of two tables.</p>