IgnatiusPang commented on PR #25746:
URL: https://github.com/apache/datafusion/pull/25746#issuecomment-5835190458
```markdown
### 1. SQL Reproducer (Demonstrating Out-Of-Order Data on `ORDER BY ASC`)
When querying a table with an existing sort order, DataFusion's physical
optimizer assumes `log(0.5, x)` preserves ascending order and eliminates the
`SortExec` node. As a result, the query returns rows in descending order when
the user explicitly asked for `ASC`:
```sql
-- Step 1: Create table with sorted data
CREATE EXTERNAL TABLE sorted_t (x DOUBLE)
STORED AS CSV
WITH ORDER (x ASC)
LOCATION 'test_sorted.csv'; -- contains: 1.0, 2.0, 4.0, 8.0
-- Step 2: Query ordered by log(0.5, x) ASC
SELECT x, log(0.5, x) AS log_val
FROM sorted_t
ORDER BY log(0.5, x) ASC;
```
**Current Result (unpatched DataFusion):**
```text
+-----+---------+
| x | log_val |
+-----+---------+
| 1.0 | -0.0 |
| 2.0 | -1.0 |
| 4.0 | -2.0 |
| 8.0 | -3.0 |
+-----+---------+
```
> Notice that `-0.0, -1.0, -2.0, -3.0` is strictly **DESCENDING**, yet the
query requested `ASC`!
**Expected Result (Correct Ascending Sort):**
```text
+-----+---------+
| x | log_val |
+-----+---------+
| 8.0 | -3.0 |
| 4.0 | -2.0 |
| 2.0 | -1.0 |
| 1.0 | -0.0 |
+-----+---------+
```
---
### 2. Physical Plan Comparison (`EXPLAIN`)
In `EXPLAIN SELECT x, log(0.5, x) AS log_val FROM sorted_t ORDER BY log(0.5,
x) ASC;`:
```text
| physical_plan | SortPreservingMergeExec: [log_val@1 ASC NULLS LAST]
| | ProjectionExec: expr=[x@0 as x, log(0.5, x@0) as log_val]
```
The optimizer completely removed the required `SortExec` because
`LogFunc::output_ordering` told the optimizer that `log(0.5, x)` preserves the
sort order of `x`.
---
### 3. Root Cause: Calculus of Monotonicity
In `datafusion/functions/src/math/log.rs`:
```rust
(
first @ (SortProperties::Ordered(_) | SortProperties::Singleton),
SortProperties::Singleton,
) => Ok(first),
```
$$\frac{\partial}{\partial x} \log_b(x) = \frac{1}{x \ln(b)}$$
- For $\text{base} > 1.0$: $\ln(b) > 0 \implies \text{strictly increasing
(order preserving)}$.
- For $\text{base} \in (0, 1)$ (e.g. constant $0.5$): $\ln(b) < 0 \implies
\text{strictly decreasing (order inverting)}$.
### 4. Proposed Fix
Check interval bounds on `base` before asserting sort preservation; return
`SortProperties::Unordered` when base cannot be proven $> 1.0$.
```
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
