Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-15381

ORDER BY datediff() reversed with unnamed column in mariaDB

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Minor
    • Resolution: Duplicate
    • 10.1.18, 10.2.12
    • N/A
    • None
    • macOS High Sierra version 10.13.2
      MacBook Air (13-inch, Early 2015)

    Description

      ORDER BY datediff() reversed with unnamed column in mariaDB.

      The queries below return a different result, but there's only one difference between the two: in the second query, the datediff in the select clause is named and re-used in the ORDER BY, while in the first one it is not named. Specifically, the order is reversed. I was expecting the order to be the same.

      FIRST QUERY

      select Start_Date, min(End_Date), datediff(min(End_Date), Start_Date)
      from (
       select Start_Date 
       from Projects 
       where Start_Date 
       not in (select End_Date from Projects)
      ) a,
      (select End_Date 
       from Projects 
       where End_Date 
       not in (select Start_Date from Projects)
      ) b
      where Start_Date < End_Date
      group by Start_Date
      order by datediff(min(End_Date), Start_Date)
      ;
       
      +------------+---------------+-------------------------------------+
      | Start_Date | min(End_Date) | datediff(min(End_Date), Start_Date) |
      +------------+---------------+-------------------------------------+
      | 2015-10-01 | 2015-10-04    |                                   3 |
      | 2015-10-13 | 2015-10-15    |                                   2 |
      | 2015-10-28 | 2015-10-29    |                                   1 |
      | 2015-10-30 | 2015-10-31    |                                   1 |
      +------------+---------------+-------------------------------------+
      

      SECOND QUERY

      select Start_Date, min(End_Date), datediff(min(End_Date), Start_Date) as 'test_diff'
      from (
       select Start_Date 
       from Projects 
       where Start_Date 
       not in (select End_Date from Projects)
      ) a,
      (select End_Date 
       from Projects 
       where End_Date 
       not in (select Start_Date from Projects)
      ) b
      where Start_Date < End_Date
      group by Start_Date
      order by test_diff
      ;
       
      +------------+---------------+-----------+
      | Start_Date | min(End_Date) | test_diff |
      +------------+---------------+-----------+
      | 2015-10-28 | 2015-10-29    |         1 |
      | 2015-10-30 | 2015-10-31    |         1 |
      | 2015-10-13 | 2015-10-15    |         2 |
      | 2015-10-01 | 2015-10-04    |         3 |
      +------------+---------------+-----------+
      

      CREATE TABLE AND INSERT STATEMENTS

      create table Projects (
        Task_ID int unsigned not null
      , Start_Date date not null
      , End_Date date not null 
      ) engine=InnoDB;
       
      insert into Projects values 
        (1, '2015-10-01', '2015-10-02')
      , (2, '2015-10-02', '2015-10-03')
      , (2, '2015-10-03', '2015-10-04')
      , (2, '2015-10-13', '2015-10-14')
      , (2, '2015-10-14', '2015-10-15')
      , (2, '2015-10-28', '2015-10-29')
      , (2, '2015-10-30', '2015-10-31')
      ;
      

      Attachments

        Issue Links

          Activity

            People

              Unassigned Unassigned
              fracchan Francesca Ferrari
              Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.