[jira] [Updated] (HIVE-20690) Order by in sequential subqueries with swapped columns in select gives wrong key type

["Sergey Kuznetsov (JIRA)" <ji...@apache.org>]

     [ https://issues.apache.org/jira/browse/HIVE-20690?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Sergey Kuznetsov updated HIVE-20690:
------------------------------------
    Description: 
There is a strange bug with order by in sequential subqueries described this [SO question|[https://stackoverflow.com/questions/52639572/hive-1-2-sql-returns-unexpected-special-character]]. If you run the following query:
{code:java}
SELECT t6.amt amt2,t6.color color
FROM(
 SELECT t5.color color, t5.c1 amt
 FROM(
  SELECT t1.c1 c1, t1.c2 AS color 
  from(
   SELECT  7716 AS c1, "Red" AS c2 UNION 
   SELECT  6203 AS c1, "Blue" AS c2
  ) t1
 ) t5
order by color) t6
ORDER BY color
{code}
you will get
{noformat}
amt color
4   �
3   �{noformat}
I tried to turn off query optimization, stats gathering and rpc but the result remains the same. In formatted query plan, Reducer 3, Reduce Output Operator I noticed
{noformat}
key expressions::"_col1 (type: int)"{noformat}
whereas It supposed to be of type string. For some reason swapping two columns changes the key type from string to int. It's not critical but worth knowing.

  was:
There is a strange bug with order by in sequential subqueries described this [SO question|[https://stackoverflow.com/questions/52639572/hive-1-2-sql-returns-unexpected-special-character].|https://stackoverflow.com/questions/52639572/hive-1-2-sql-returns-unexpected-special-character]:]If you run the following query:
{code:java}
SELECT t6.amt amt2,t6.color color
FROM(
 SELECT t5.color color, t5.c1 amt
 FROM(
  SELECT t1.c1 c1, t1.c2 AS color 
  from(
   SELECT  7716 AS c1, "Red" AS c2 UNION 
   SELECT  6203 AS c1, "Blue" AS c2
  ) t1
 ) t5
order by color) t6
ORDER BY color
{code}
you will get
{noformat}
amt color
4   �
3   �{noformat}
I tried to turn off query optimization, stats gathering and rpc but the result remains the same. In formatted query plan, Reducer 3, Reduce Output Operator I noticed
{noformat}
key expressions::"_col1 (type: int)"{noformat}
whereas It supposed to be of type string. For some reason swapping two columns changes the key type from string to int. It's not critical but worth knowing.


> Order by in sequential subqueries with swapped columns in select gives wrong key type
> -------------------------------------------------------------------------------------
>
>                 Key: HIVE-20690
>                 URL: https://issues.apache.org/jira/browse/HIVE-20690
>             Project: Hive
>          Issue Type: Bug
>         Environment: Hive 1.2, 2.3
>            Reporter: Sergey Kuznetsov
>            Priority: Major
>
> There is a strange bug with order by in sequential subqueries described this [SO question|[https://stackoverflow.com/questions/52639572/hive-1-2-sql-returns-unexpected-special-character]]. If you run the following query:
> {code:java}
> SELECT t6.amt amt2,t6.color color
> FROM(
>  SELECT t5.color color, t5.c1 amt
>  FROM(
>   SELECT t1.c1 c1, t1.c2 AS color 
>   from(
>    SELECT  7716 AS c1, "Red" AS c2 UNION 
>    SELECT  6203 AS c1, "Blue" AS c2
>   ) t1
>  ) t5
> order by color) t6
> ORDER BY color
> {code}
> you will get
> {noformat}
> amt color
> 4   �
> 3   �{noformat}
> I tried to turn off query optimization, stats gathering and rpc but the result remains the same. In formatted query plan, Reducer 3, Reduce Output Operator I noticed
> {noformat}
> key expressions::"_col1 (type: int)"{noformat}
> whereas It supposed to be of type string. For some reason swapping two columns changes the key type from string to int. It's not critical but worth knowing.



--
This message was sent by Atlassian JIRA
(v7.6.3#76005)