Defining order by null last at database level

Gallus
SQL-Fighter

We are migrating from another database to EXASOL. The default behavior for order by statements of that database corresponds to the option order by xxx desc nulls last in EXASOL.

The question came now up whether we could in EXASOL define the "nulls last" for order by at the database level or whether each and every order by statement would need to be changed manually in order to have the same result as before (also within qualify statements)

I could not find any information regards this question in the documentation. Would anybody now whether this can be set at the database level?

1 ACCEPTED SOLUTION

Accepted Solutions

exa-Matze
Team Exasol
Team Exasol

Hi @Gallus , 

preprocessor might be an option, but should definitely be tested well if the script affects each an every query.

Please note that a preprocessor script can be disabled by any user with a simple SQL statement, which might cause different behaviors.

Ideally you create an idea in our ideation portal (here in the community). Your input will be very valuable for our further DB improvements.

 

Cheers,

Matze

View solution in original post

5 REPLIES 5

exa-Uwe
Moderator
Moderator

I doubt there's a database setting for that, but don't know for sure.

It should be possible to write a preprocessor script that replaces every ORDER BY with ORDER BY DESC NULLS LAST, so you wouldn't have to do that manually.

Best regards

Uwe

mwellbro
Xpert

I was about to suggest the very same 🤣

Gallus
SQL-Fighter

Hello Uwe

Many thanks for the answer. I hesitate a bit to apply this in the preprocessor as this would then be applied to each and every query. I will first check with our internal community to better understand the benefit vs. the cost. We would then probably also need to do some testing to get a better understanding what the performance impact would be.

Kind Regards

Gallus

exa-Matze
Team Exasol
Team Exasol

Hi @Gallus , 

preprocessor might be an option, but should definitely be tested well if the script affects each an every query.

Please note that a preprocessor script can be disabled by any user with a simple SQL statement, which might cause different behaviors.

Ideally you create an idea in our ideation portal (here in the community). Your input will be very valuable for our further DB improvements.

 

Cheers,

Matze

View solution in original post

Gallus
SQL-Fighter

Hello together

Many thanks for your feedback. We decided not use the SQL Preprocessor at this point in time. Some of the arguments against it:

  • Performance: At this point in time we don't have the time to do detailed performance testing
  • Lack of clear specification: We could not find anyone who could write a company wide definition of how the order by should work.
  • Overall implication: This feature was only requested by one user group. Other groups did not want to go through the effort of changing their statements, or retesting them

Therefore we decided that the group who requested the change should rather change their statements to the way EXASOL works

Regards

Gallus