|
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')
|
;
|
|