Uploaded image for project: 'Calcite'
  1. Calcite
  2. CALCITE-176

ORDER BY expression doesn't work with SELECT *

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: None
    • Component/s: None
    • Labels:

      Description

      `ORDER BY CAST(expression AS TYPE)` does not have the desired effect. The following example (against optiq-csv) illustrates that ORDER BY succeeds if the expression is explicitly in the SELECT clause:

      ```sql
      sqlline> !set outputformat csv
      sqlline> !connect jdbc:optiq:model=target/test-classes/bug.json admin ''
      0: jdbc:optiq:model=target/test-classes/bug.j> select * from numeric_columns order by cast(empno as integer);
      'EMPNO','NAME','DEPTNO','GENDER','CITY','EMPID','AGE','SLACKER','MANAGER','JOINEDAT'
      '130','Alice','40','F','Vancouver','2','null','false','true','2007-01-01'
      '110','Eric','20','M','San Francisco','3','80','null','false','2001-01-01'
      '100','Fred','10','','','30','25','true','false','1996-08-03'
      '99','John','40','M','Vancouver','2','null','false','true','2002-05-03'
      '120','Wilma','20','F','','1','5','null','true','2005-09-07'
      5 rows selected (0.484 seconds)
      0: jdbc:optiq:model=target/test-classes/bug.j> select cast(empno as integer) as x, * from numeric_columns order by x;
      'X','EMPNO','NAME','DEPTNO','GENDER','CITY','EMPID','AGE','SLACKER','MANAGER','JOINEDAT'
      '100','100','Fred','10','','','30','25','true','false','1996-08-03'
      '110','110','Eric','20','M','San Francisco','3','80','null','false','2001-01-01'
      '120','120','Wilma','20','F','','1','5','null','true','2005-09-07'
      '130','130','Alice','40','F','Vancouver','2','null','false','true','2007-01-01'
      '99','99','John','40','M','Vancouver','2','null','false','true','2002-05-03'
      5 rows selected (0.068 seconds)
      ```

      ---------------- Imported from GitHub ----------------
      Url: https://github.com/julianhyde/optiq/issues/176
      Created by: julianhyde
      Labels: bug,
      Created at: Tue Mar 11 17:47:53 CET 2014
      State: closed

        Activity

        Hide
        github-import GitHub Import added a comment -

        [Date: Fri Mar 14 02:32:10 CET 2014, Author: julianhyde]

        The problem appears to be the star. The extra expression (added to the select clause so that it can be sorted) is given an ordinal before the star is expanded, so we sort on the wrong column.

        Show
        github-import GitHub Import added a comment - [Date: Fri Mar 14 02:32:10 CET 2014, Author: julianhyde ] The problem appears to be the star. The extra expression (added to the select clause so that it can be sorted) is given an ordinal before the star is expanded, so we sort on the wrong column.

          People

          • Assignee:
            Unassigned
            Reporter:
            github-import GitHub Import
          • Votes:
            0 Vote for this issue
            Watchers:
            0 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved:

              Development