Uploaded image for project: 'Apache Drill'
  1. Apache Drill
  2. DRILL-2002

Confusing "star" behavior in UNION ALL operator

    XMLWordPrintableJSON

Details

    Description

      t1.json

      { "a1": 1 ,    "b1" : 1}
      { "a1": 2 ,    "b1" : 1}
      { "a1": 2 ,    "b1" : 2}
      { "a1": 3 ,    "b1" : 2}
      { "a1": null , "b1" : 3}
      

      Star in both legs of UNION ALL works:

      0: jdbc:drill:schema=dfs> select * from `t1.json` union all select * from `t1.json`;
      +------------+------------+
      |     a1     |     b1     |
      +------------+------------+
      | 1          | 1          |
      | 2          | 1          |
      | 2          | 2          |
      | 3          | 2          |
      | null       | 3          |
      | 1          | 1          |
      | 2          | 1          |
      | 2          | 2          |
      | 3          | 2          |
      | null       | 3          |
      +------------+------------+
      10 rows selected (0.126 seconds)
      

      I expected this to work in structured, but it seems that since planner has no idea about meta data, error message seems reasonable:

      0: jdbc:drill:schema=dfs> select a1, b1 from `t1.json` union all select * from `t1.json`;
      Query failed: Query failed: Failure validating SQL. org.eigenbase.util.EigenbaseContextException: At line 1, column 47: Column count mismatch in UNION ALL
      Error: exception while executing query: Failure while executing query. (state=,code=0)
      

      Query below returns very confusing result. I expected it to error out like the query above:

      0: jdbc:drill:schema=dfs> select a1 from `t1.json` union all select * from `t1.json`;
      +------------+
      |     a1     |
      +------------+
      | 1          |
      | 2          |
      | 2          |
      | 3          |
      | null       |
      | 1          |
      | 2          |
      | 2          |
      | 3          |
      | null       |
      +------------+
      10 rows selected (0.111 seconds)
      

      Attachments

        Issue Links

          Activity

            People

              seanhychu Sean Hsuan-Yi Chu
              vicky Victoria Markman
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved: