Uploaded image for project: 'Apache Trafodion'
  1. Apache Trafodion
  2. TRAFODION-3025

order by clause in subquery does not work

    XMLWordPrintableJSON

    Details

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

      Description

      CREATE TABLE TRAFODION.SEABASE.DT
        (
          D1 DATE DEFAULT NULL NOT SERIALIZED
        , T1 TIME(0) DEFAULT NULL NOT SERIALIZED
        , TS1 TIMESTAMP(2) DEFAULT NULL NOT SERIALIZED
        , TS2 TIMESTAMP(6) DEFAULT NULL NOT SERIALIZED
        )
      ATTRIBUTES ALIGNED FORMAT NAMESPACE 'TRAF_RSRVD_3'
      ;
      insert into dt values(current_date,current_time,current_timestamp, current_timestamp);
      .............
      .............

      >>select * from dt;
      D1 T1 TS1 TS2 
      ---------- -------- ---------------------- --------------------------
      2018-03-27 14:44:36 2018-03-27 14:44:36.80 2018-03-27 14:44:36.808913
      2018-04-16 12:08:37 2018-04-16 12:08:37.92 2018-04-16 12:08:37.922973
      2018-04-16 12:08:39 2018-04-16 12:08:39.53 2018-04-16 12:08:39.530414
      2018-04-16 12:08:40 2018-04-16 12:08:40.63 2018-04-16 12:08:40.634867
      2018-04-16 12:08:42 2018-04-16 12:08:42.06 2018-04-16 12:08:42.069282
      2018-04-16 12:08:42 2018-04-16 12:08:42.97 2018-04-16 12:08:42.971260
      2018-04-16 12:08:43 2018-04-16 12:08:43.81 2018-04-16 12:08:43.812465
      2018-04-16 12:08:44 2018-04-16 12:08:44.57 2018-04-16 12:08:44.578473
      2018-04-16 12:08:45 2018-04-16 12:08:45.24 2018-04-16 12:08:45.240684
      2018-04-16 12:08:45 2018-04-16 12:08:45.92 2018-04-16 12:08:45.923487
      2018-04-16 12:08:46 2018-04-16 12:08:46.62 2018-04-16 12:08:46.623653
      — 11 row(s) selected.

      >>select * from dt order by ts1 desc;
      D1 T1 TS1 TS2 
      ---------- -------- ---------------------- --------------------------
      2018-04-16 12:08:46 2018-04-16 12:08:46.62 2018-04-16 12:08:46.623653
      2018-04-16 12:08:45 2018-04-16 12:08:45.92 2018-04-16 12:08:45.923487
      2018-04-16 12:08:45 2018-04-16 12:08:45.24 2018-04-16 12:08:45.240684
      2018-04-16 12:08:44 2018-04-16 12:08:44.57 2018-04-16 12:08:44.578473
      2018-04-16 12:08:43 2018-04-16 12:08:43.81 2018-04-16 12:08:43.812465
      2018-04-16 12:08:42 2018-04-16 12:08:42.97 2018-04-16 12:08:42.971260
      2018-04-16 12:08:42 2018-04-16 12:08:42.06 2018-04-16 12:08:42.069282
      2018-04-16 12:08:40 2018-04-16 12:08:40.63 2018-04-16 12:08:40.634867
      2018-04-16 12:08:39 2018-04-16 12:08:39.53 2018-04-16 12:08:39.530414
      2018-04-16 12:08:37 2018-04-16 12:08:37.92 2018-04-16 12:08:37.922973
      2018-03-27 14:44:36 2018-03-27 14:44:36.80 2018-03-27 14:44:36.808913
      — 11 row(s) selected.

      >>select * from (select * from dt order by ts1 desc); 
      D1 T1 TS1 TS2 
      ---------- -------- ---------------------- --------------------------
      2018-03-27 14:44:36 2018-03-27 14:44:36.80 2018-03-27 14:44:36.808913
      2018-04-16 12:08:37 2018-04-16 12:08:37.92 2018-04-16 12:08:37.922973
      2018-04-16 12:08:39 2018-04-16 12:08:39.53 2018-04-16 12:08:39.530414
      2018-04-16 12:08:40 2018-04-16 12:08:40.63 2018-04-16 12:08:40.634867
      2018-04-16 12:08:42 2018-04-16 12:08:42.06 2018-04-16 12:08:42.069282
      2018-04-16 12:08:42 2018-04-16 12:08:42.97 2018-04-16 12:08:42.971260
      2018-04-16 12:08:43 2018-04-16 12:08:43.81 2018-04-16 12:08:43.812465
      2018-04-16 12:08:44 2018-04-16 12:08:44.57 2018-04-16 12:08:44.578473
      2018-04-16 12:08:45 2018-04-16 12:08:45.24 2018-04-16 12:08:45.240684
      2018-04-16 12:08:45 2018-04-16 12:08:45.92 2018-04-16 12:08:45.923487
      2018-04-16 12:08:46 2018-04-16 12:08:46.62 2018-04-16 12:08:46.623653

      — 11 row(s) selected. 

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                ovis_poly liu ming
                Reporter:
                ovis_poly liu ming
              • Votes:
                0 Vote for this issue
                Watchers:
                3 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: