[
https://issues.apache.org/jira/browse/DRILL-1499?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14384534#comment-14384534
]
Victoria Markman commented on DRILL-1499:
-----------------------------------------
I just ran into this again, in completely different context and it only
happened when I had Exchange in my plan.
{code:sql}
select *
from test left outer join j2
on (COALESCE(test.c_integer, 10000) = COALESCE(j2.c_integer, 10000))
where
test.c_integer IS NULL
and j2.c_integer IS NULL
{code}
If we are not planning to fix it ever, we need to make sure it is documented in
our SQL section on differences between us and SQL standard along with NULL
behavior.
> Different column order could appear in the result set for a schema-less
> select * query, even there are no changing schemas.
> ---------------------------------------------------------------------------------------------------------------------------
>
> Key: DRILL-1499
> URL: https://issues.apache.org/jira/browse/DRILL-1499
> Project: Apache Drill
> Issue Type: Bug
> Reporter: Jinfeng Ni
> Assignee: Steven Phillips
> Fix For: Future
>
>
> For a select * query referring to a schema-less table, Drill could return
> different column, depending on the physical operators the query involves:
> Q1:
> {code}
> select * from cp.`employee.json` limit 3;
> +-------------+------------+------------+------------+-------------+----------------+------------+---------------+------------+------------+------------+---------------+-----------------+----------------+------------+-----------------+
> | employee_id | full_name | first_name | last_name | position_id |
> position_title | store_id | department_id | birth_date | hire_date |
> salary | supervisor_id | education_level | marital_status | gender |
> management_role |
> +-------------+------------+------------+------------+-------------+----------------+------------+---------------+------------+------------+------------+---------------+-----------------+----------------+------------+-----------------+
> {code}
> Q2:
> {code}
> select * from cp.`employee.json` order by last_name limit 3;
> +------------+---------------+-----------------+-------------+------------+------------+------------+------------+------------+-----------------+----------------+-------------+----------------+------------+------------+---------------+
> | birth_date | department_id | education_level | employee_id | first_name |
> full_name | gender | hire_date | last_name | management_role |
> marital_status | position_id | position_title | salary | store_id |
> supervisor_id |
> +------------+---------------+-----------------+-------------+------------+------------+------------+------------+------------+-----------------+----------------+-------------+----------------+------------+------------+---------------+
> {code}
> The difference between Q1 and Q2 is the order by clause. With order by
> clause in Q2, Drill will sort the column names alphabetically, while for Q1,
> the column names are in the same order as in the data source.
> The underlying cause for such difference is that the sort or sort-based
> merger operator would require canonicalization, since the incoming batches
> could contain different schemas.
> However, it would be better that such canonicalization is used only when the
> incoming batches have changing schemas. If all the incoming batches have
> identical schemas, no need to sort the column orders. With this fix, Drill
> will present the same column order in the result set, for a schema-less
> select * query, if there is no changing schemas from incoming data sources.
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
