`NULLS { FIRST | LAST }` support in `ORDER BY`

[wey-gu]

Please check the FAQ documentation before raising an issue

As title, we treated NULL differently from other DB, which is counterintuitive to user.

(root@nebula) [basketballplayer]> match (n:player) return n.player.name AS name order by name DESC limit 2
+------------+
| name       |
+------------+
| __NULL__   |
| "Yao Ming" |
+------------+
Got 2 rows (time spent 2.626ms/3.188925ms)

Describe the bug (required)

Your Environments (required)

• OS: uname -a
• Compiler: g++ --version or clang++ --version
• CPU: lscpu
• Commit id (e.g. a3ffc7d8)

Expected behavior

NULL should be at the end when DESC.

Additional context

ref: https://github.com/vesoft-inc/nebula/issues/4810

[HarrisChu]

[HarrisChu]

BTW, PostgreSQL could special the behavior ( null first or null last)

SELECT select_list
    FROM table_expression
    ORDER BY sort_expression1 [ASC | DESC] [NULLS { FIRST | LAST }]
             [, sort_expression2 [ASC | DESC] [NULLS { FIRST | LAST }] ...]

[wey-gu]

BTW, PostgreSQL could special the behavior ( null first or null last)

SELECT select_list
    FROM table_expression
    ORDER BY sort_expression1 [ASC | DESC] [NULLS { FIRST | LAST }]
             [, sort_expression2 [ASC | DESC] [NULLS { FIRST | LAST }] ...]

Thanks! Exposing options is way more elegant!

[wey-gu]

OK, now we know we are not wrong, just one flavor of all approaches :) Thanks!

[wey-gu]

@son2408 it seems current behavior is one of the approaches that PostgreSQL/Oracle also behaves by default

[son2408]

@son2408 it seems current behavior is one of the approaches that PostgreSQL/Oracle also behaves by default

Yes, we are not wrong but it should have a extra option NULL fisrt or last :)