1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
|
--- a/expected/select.out
+++ b/expected/select.out
@@ -1601,15 +1601,15 @@ SELECT * FROM f_test_tbl1 ORDER BY c1 DE
-- Order by is not null
EXPLAIN (VERBOSE, COSTS OFF)
-SELECT * FROM f_test_tbl1 ORDER BY c7 IS NOT NULL;
- QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+SELECT * FROM f_test_tbl1 ORDER BY c7 IS NOT NULL, c1;
+ QUERY PLAN
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Foreign Scan on public.f_test_tbl1
Output: c1, c2, c3, c4, c5, c6, c7, c8, (c7 IS NOT NULL)
- Remote query: SELECT `c1`, `c2`, `c3`, `c4`, `c5`, `c6`, `c7`, `c8` FROM `mysql_fdw_regress`.`test_tbl1` ORDER BY (`c7` IS NOT NULL) IS NULL, (`c7` IS NOT NULL) ASC
+ Remote query: SELECT `c1`, `c2`, `c3`, `c4`, `c5`, `c6`, `c7`, `c8` FROM `mysql_fdw_regress`.`test_tbl1` ORDER BY (`c7` IS NOT NULL) IS NULL, (`c7` IS NOT NULL) ASC, `c1` IS NULL, `c1` ASC
(3 rows)
-SELECT * FROM f_test_tbl1 ORDER BY c7 IS NOT NULL;
+SELECT * FROM f_test_tbl1 ORDER BY c7 IS NOT NULL, c1;
c1 | c2 | c3 | c4 | c5 | c6 | c7 | c8
------+-------+-----------+------+------------+------------+------+----
100 | EMP1 | ADMIN | 1300 | 1980-12-17 | 800.23000 | | 20
@@ -1630,15 +1630,15 @@ SELECT * FROM f_test_tbl1 ORDER BY c7 IS
-- Order by is not null desc
EXPLAIN (VERBOSE, COSTS OFF)
-SELECT * FROM f_test_tbl1 ORDER BY c7 IS NOT NULL DESC;
- QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+SELECT * FROM f_test_tbl1 ORDER BY c7 IS NOT NULL DESC, c1;
+ QUERY PLAN
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Foreign Scan on public.f_test_tbl1
Output: c1, c2, c3, c4, c5, c6, c7, c8, (c7 IS NOT NULL)
- Remote query: SELECT `c1`, `c2`, `c3`, `c4`, `c5`, `c6`, `c7`, `c8` FROM `mysql_fdw_regress`.`test_tbl1` ORDER BY (`c7` IS NOT NULL) IS NOT NULL, (`c7` IS NOT NULL) DESC
+ Remote query: SELECT `c1`, `c2`, `c3`, `c4`, `c5`, `c6`, `c7`, `c8` FROM `mysql_fdw_regress`.`test_tbl1` ORDER BY (`c7` IS NOT NULL) IS NOT NULL, (`c7` IS NOT NULL) DESC, `c1` IS NULL, `c1` ASC
(3 rows)
-SELECT * FROM f_test_tbl1 ORDER BY c7 IS NOT NULL DESC;
+SELECT * FROM f_test_tbl1 ORDER BY c7 IS NOT NULL DESC, c1;
c1 | c2 | c3 | c4 | c5 | c6 | c7 | c8
------+-------+-----------+------+------------+------------+------+----
200 | EMP2 | SALESMAN | 600 | 1981-02-20 | 1600.00000 | 300 | 30
@@ -1659,15 +1659,15 @@ SELECT * FROM f_test_tbl1 ORDER BY c7 IS
-- Order by desc nulls first
EXPLAIN (VERBOSE, COSTS OFF)
-SELECT * FROM f_test_tbl1 ORDER BY c7 DESC NULLS FIRST;
- QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------
+SELECT * FROM f_test_tbl1 ORDER BY c7 DESC NULLS FIRST, c1;
+ QUERY PLAN
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Foreign Scan on public.f_test_tbl1
Output: c1, c2, c3, c4, c5, c6, c7, c8
- Remote query: SELECT `c1`, `c2`, `c3`, `c4`, `c5`, `c6`, `c7`, `c8` FROM `mysql_fdw_regress`.`test_tbl1` ORDER BY `c7` IS NOT NULL, `c7` DESC
+ Remote query: SELECT `c1`, `c2`, `c3`, `c4`, `c5`, `c6`, `c7`, `c8` FROM `mysql_fdw_regress`.`test_tbl1` ORDER BY `c7` IS NOT NULL, `c7` DESC, `c1` IS NULL, `c1` ASC
(3 rows)
-SELECT * FROM f_test_tbl1 ORDER BY c7 DESC NULLS FIRST;
+SELECT * FROM f_test_tbl1 ORDER BY c7 DESC NULLS FIRST, c1;
c1 | c2 | c3 | c4 | c5 | c6 | c7 | c8
------+-------+-----------+------+------------+------------+------+----
100 | EMP1 | ADMIN | 1300 | 1980-12-17 | 800.23000 | | 20
@@ -1688,15 +1688,15 @@ SELECT * FROM f_test_tbl1 ORDER BY c7 DE
-- Order by asc nulls last
EXPLAIN (VERBOSE, COSTS OFF)
-SELECT * FROM f_test_tbl1 ORDER BY c7 DESC NULLS LAST;
- QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------
+SELECT * FROM f_test_tbl1 ORDER BY c7 DESC NULLS LAST, c1;
+ QUERY PLAN
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------
Foreign Scan on public.f_test_tbl1
Output: c1, c2, c3, c4, c5, c6, c7, c8
- Remote query: SELECT `c1`, `c2`, `c3`, `c4`, `c5`, `c6`, `c7`, `c8` FROM `mysql_fdw_regress`.`test_tbl1` ORDER BY `c7` IS NULL, `c7` DESC
+ Remote query: SELECT `c1`, `c2`, `c3`, `c4`, `c5`, `c6`, `c7`, `c8` FROM `mysql_fdw_regress`.`test_tbl1` ORDER BY `c7` IS NULL, `c7` DESC, `c1` IS NULL, `c1` ASC
(3 rows)
-SELECT * FROM f_test_tbl1 ORDER BY c7 DESC NULLS LAST;
+SELECT * FROM f_test_tbl1 ORDER BY c7 DESC NULLS LAST, c1;
c1 | c2 | c3 | c4 | c5 | c6 | c7 | c8
------+-------+-----------+------+------------+------------+------+----
500 | EMP5 | SALESMAN | 600 | 1981-09-28 | 1250.00000 | 1400 | 30
--- a/sql/select.sql
+++ b/sql/select.sql
@@ -497,23 +497,23 @@ SELECT * FROM f_test_tbl1 ORDER BY c1 DE
-- Order by is not null
EXPLAIN (VERBOSE, COSTS OFF)
-SELECT * FROM f_test_tbl1 ORDER BY c7 IS NOT NULL;
-SELECT * FROM f_test_tbl1 ORDER BY c7 IS NOT NULL;
+SELECT * FROM f_test_tbl1 ORDER BY c7 IS NOT NULL, c1;
+SELECT * FROM f_test_tbl1 ORDER BY c7 IS NOT NULL, c1;
-- Order by is not null desc
EXPLAIN (VERBOSE, COSTS OFF)
-SELECT * FROM f_test_tbl1 ORDER BY c7 IS NOT NULL DESC;
-SELECT * FROM f_test_tbl1 ORDER BY c7 IS NOT NULL DESC;
+SELECT * FROM f_test_tbl1 ORDER BY c7 IS NOT NULL DESC, c1;
+SELECT * FROM f_test_tbl1 ORDER BY c7 IS NOT NULL DESC, c1;
-- Order by desc nulls first
EXPLAIN (VERBOSE, COSTS OFF)
-SELECT * FROM f_test_tbl1 ORDER BY c7 DESC NULLS FIRST;
-SELECT * FROM f_test_tbl1 ORDER BY c7 DESC NULLS FIRST;
+SELECT * FROM f_test_tbl1 ORDER BY c7 DESC NULLS FIRST, c1;
+SELECT * FROM f_test_tbl1 ORDER BY c7 DESC NULLS FIRST, c1;
-- Order by asc nulls last
EXPLAIN (VERBOSE, COSTS OFF)
-SELECT * FROM f_test_tbl1 ORDER BY c7 DESC NULLS LAST;
-SELECT * FROM f_test_tbl1 ORDER BY c7 DESC NULLS LAST;
+SELECT * FROM f_test_tbl1 ORDER BY c7 DESC NULLS LAST, c1;
+SELECT * FROM f_test_tbl1 ORDER BY c7 DESC NULLS LAST, c1;
-- Cleanup
DROP TABLE l_test_tbl1;
|
