I am encountering a bug when performing a query with sorting on a nullable float field within a jsonb column, using NULLS LAST. Despite this, NULL values appear at the beginning of the sorted results. When casting the value to text, NULL values correctly appear at the end of the results. However, sorting is incorrect because the values are then treated as text, which affects the sorting order.
Expected Behavior: NULL values should appear at the end of the sorted results, and the sorting should be accurate based on the numeric values.
Actual Behavior: NULL values appear at the beginning when sorting numerically.
Interesting...
Since a JSON typed null value is not an SQL NULL value the order by machinery sees a perfectly valid non-null value to be sorted alongside the non-null data. When forcing the json to be text the cast does convert a json null value to a SQL text NULL value
In short, while this can be controlled at the SQL scope, the comparison operator for the json data type provides no such ability for the user to control the result of the comparison between null and non-null json values. null values apparently always sort lower than non-null values.
You will probably need to sort on two expressions, the first one to place all json null values after non-null ones, then a second expression to numerically sort the non-null rows in the desired order.
It is not a bug, and I have no clue if it is even reasonable for the comparison function for json to consider the context in which it is being performed.
