`ORDER BY expression NULLS FIRST/LAST``

[SimpleForest]

@SimpleForest
BTW if you are fluent with C++ templates and want to help with the project, the PostgreSQL connector lacks a few things that would probably be beneficial to the library users.

• ORDER BY expression NULLS FIRST/LAST. Actually SQLite3 supports that extension of the standard too.
• SELECT DISTINCT ON (expression, ...)
• COPY TO/FROM.

I've had these features on my wishlist for a long time, but never actually found the time to get down and try to implement any of them.

Originally posted by @MeanSquaredError in #59

https://www.postgresql.org/docs/current/queries-order.html

I would give it a try but cant promise:

`ORDER BY expression NULLS FIRST/LAST``

[MeanSquaredError]

Thanks for opening the issue. I will provide my input and ideas on this issue and will try to help you with the implementation if you get stuck anywhere. If my help turns out to be insufficient, we can always resort to asking for help from rbock (and he is THE expert on C++ template metaprogramming anyway).

Wikipedia says that NULLS FIRST and NULLS LAST have been standardized in SQL:2003 extension T611.

The SQL standard's core functionality does not explicitly define a default sort order for Nulls. With the SQL:2003 extension T611, "Elementary OLAP operations", nulls can be sorted before or after all data values by using the NULLS FIRST or NULLS LAST clauses of the ORDER BY list, respectively. Not all DBMS vendors implement this functionality, however. Vendors who do not implement this functionality may specify different treatments for Null sorting in the DBMS.

The first question is, how the syntax of NULLS FIRST/LAST should look? Currently the basic syntax which defines the order at compile time looks like this

order_by(col_1.asc(), col_2.desc(), ...)

Or if want to specify it at runtime, we could use

order_by(col_1.order(sqlpp::sort_type::asc), col_2.order(sqlpp::sort_type::desc), ...)

Any mix between the syntaxes two is possible too.

So I think that it is natural to specify the NULLS FIRST/LAST clause as

order_by(col_1.asc().nulls_first(), col_2.desc().nulls_last(), ...)

Or if we want to specify it at runtime:

order_by(col_1.asc().nulls_order(sqlpp::nulls_pos::first), col_2.desc().nulls_order(sqlpp::nulls_pos::first), ...)

Of course the proposed function names could be changed, to something else, if someone comes up with better names for them.

The next question is: How do we implement the NULLS FIRST/LAST clauses just once and then reuse the implementation both for PostgreSQL and SQLite3? I think that we could implement it in the core and then define some type traits for each connector that would specify if this clause is enabled or disabled for the corresponding connector. But maybe @rbock could chime in and provide his input on this question.

[SimpleForest]

Hello,

The official postgresql docs:

https://www.postgresql.org/docs/current/queries-order.html

states quite nicely the syntax

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

I think here it is justifiable to rewrite NULLS {FIRST|LAST} to NULLS FIRST| NULLS LAST or even nicer NULLS_FIRST| NULLS_LAST

Then we get

SELECT select_list
    FROM table_expression
    ORDER BY sort_expression1 [ASC | DESC] [NULLS_FIRST| NULLS_LAST]
             [, sort_expression2 [ASC | DESC] [NULLS_FIRST| NULLS_LAST] ...]

My Plan:

So I will look how the optional part [ASC | DESC] is implemented. It requires a sort_expression.
[NULLS_FIRST| NULLS_LAST] is also optional and also has a single requirement - in this case the requirement is [ASC | DESC] I will try to adapt from [ASC | DESC] implemtation to [NULLS_FIRST| NULLS_LAST]

Implement the type traits for PostgreSQL and SQLite3 as second requirement.

But tomorrow not today.

[MeanSquaredError]

You should probably take a look first at order_by.h, comparison_functions.h and enable_comparison.h.

The first (order_by.h) implements the order_by clause and the second (comparison_functions.h) and third (enable_comparison.h)) implement the asc() and desc() functions.

I think you will need to implement the nulls_first() and nulls_last() functions for compile-time ordering. And the nulls(nulls_pos_type pos) function for ordering at runtime. nulls_pos_type will be an enum with two values: first, last and default. The third value will mean that we don't really enforce any specific position for the nulls and instead use the database-default ordering.

Also maybe instead of nulls_pos_type the ordering type could be called nulls_order_type or some other name in case someone comes up with a better name.

That's how I think it should be implemented.