Sorted tables without sort direction lead to wrong order

[billziss-gh]

What happens?

A statement like ALTER TABLE data SET SORTED BY (date) sorts the table in date descending order. (Notice that the date is missing the sort direction; it is not clear if this is legal syntax but it is accepted nevertheless.)

A statement like ALTER TABLE data SET SORTED BY (date ASC) sorts the table in the correct date ascending order.

To Reproduce

I attach a script and some data that demonstrate the problem:

• bug.sql
• data.csv

This script creates a number of databases with or without sort_on_insert=false and with or without ALTER TABLE SET SORTED BY. When run it produces the output below. Notice that the data1010 and data1110 outputs appear wrong; those databases use the ALTER TABLE data SET SORTED BY (date) statement.

┌────────────────────┬───────┬────────────┬───────┐
│ current_database() │ rowid │    date    │ value │
│      varchar       │ int64 │    date    │ float │
├────────────────────┼───────┼────────────┼───────┤
│ data0000           │     0 │ 2011-01-03 │   0.0 │
│ data0000           │     1 │ 2011-01-04 │   0.0 │
│ data0000           │     2 │ 2011-01-05 │   0.0 │
│ data0000           │     3 │ 2011-01-06 │   0.0 │
│ data0000           │     4 │ 2011-01-07 │   0.0 │
│ data0000           │     5 │ 2011-01-10 │   0.0 │
│ data0000           │     6 │ 2011-01-11 │   0.0 │
│ data0000           │     7 │ 2011-01-12 │   0.0 │
│ data0000           │     8 │ 2011-01-13 │   0.0 │
│ data0000           │     9 │ 2011-01-14 │   0.0 │
└────────────────────┴───────┴────────────┴───────┘
  10 rows                               4 columns
┌────────────────────┬───────┬────────────┬───────┐
│ current_database() │ rowid │    date    │ value │
│      varchar       │ int64 │    date    │ float │
├────────────────────┼───────┼────────────┼───────┤
│ data1000           │     0 │ 2011-01-03 │   0.0 │
│ data1000           │     1 │ 2011-01-04 │   0.0 │
│ data1000           │     2 │ 2011-01-05 │   0.0 │
│ data1000           │     3 │ 2011-01-06 │   0.0 │
│ data1000           │     4 │ 2011-01-07 │   0.0 │
│ data1000           │     5 │ 2011-01-10 │   0.0 │
│ data1000           │     6 │ 2011-01-11 │   0.0 │
│ data1000           │     7 │ 2011-01-12 │   0.0 │
│ data1000           │     8 │ 2011-01-13 │   0.0 │
│ data1000           │     9 │ 2011-01-14 │   0.0 │
└────────────────────┴───────┴────────────┴───────┘
  10 rows                               4 columns
┌────────────────────┬───────┬────────────┬───────┐
│ current_database() │ rowid │    date    │ value │
│      varchar       │ int64 │    date    │ float │
├────────────────────┼───────┼────────────┼───────┤
│ data1010           │     0 │ 2025-11-07 │   0.0 │
│ data1010           │     1 │ 2025-11-06 │   0.0 │
│ data1010           │     2 │ 2025-11-05 │   0.0 │
│ data1010           │     3 │ 2025-11-04 │   0.0 │
│ data1010           │     4 │ 2025-11-03 │   0.0 │
│ data1010           │     5 │ 2025-10-31 │   0.0 │
│ data1010           │     6 │ 2025-10-30 │   0.0 │
│ data1010           │     7 │ 2025-10-29 │   0.0 │
│ data1010           │     8 │ 2025-10-28 │   0.0 │
│ data1010           │     9 │ 2025-10-27 │   0.0 │
└────────────────────┴───────┴────────────┴───────┘
  10 rows                               4 columns
┌────────────────────┬───────┬────────────┬───────┐
│ current_database() │ rowid │    date    │ value │
│      varchar       │ int64 │    date    │ float │
├────────────────────┼───────┼────────────┼───────┤
│ data1011           │     0 │ 2011-01-03 │   0.0 │
│ data1011           │     1 │ 2011-01-04 │   0.0 │
│ data1011           │     2 │ 2011-01-05 │   0.0 │
│ data1011           │     3 │ 2011-01-06 │   0.0 │
│ data1011           │     4 │ 2011-01-07 │   0.0 │
│ data1011           │     5 │ 2011-01-10 │   0.0 │
│ data1011           │     6 │ 2011-01-11 │   0.0 │
│ data1011           │     7 │ 2011-01-12 │   0.0 │
│ data1011           │     8 │ 2011-01-13 │   0.0 │
│ data1011           │     9 │ 2011-01-14 │   0.0 │
└────────────────────┴───────┴────────────┴───────┘
  10 rows                               4 columns
┌─────────┐
│ Success │
│ boolean │
└─────────┘
  0 rows
┌────────────────────┬───────┬────────────┬───────┐
│ current_database() │ rowid │    date    │ value │
│      varchar       │ int64 │    date    │ float │
├────────────────────┼───────┼────────────┼───────┤
│ data1100           │     0 │ 2011-01-03 │   0.0 │
│ data1100           │     1 │ 2011-01-04 │   0.0 │
│ data1100           │     2 │ 2011-01-05 │   0.0 │
│ data1100           │     3 │ 2011-01-06 │   0.0 │
│ data1100           │     4 │ 2011-01-07 │   0.0 │
│ data1100           │     5 │ 2011-01-10 │   0.0 │
│ data1100           │     6 │ 2011-01-11 │   0.0 │
│ data1100           │     7 │ 2011-01-12 │   0.0 │
│ data1100           │     8 │ 2011-01-13 │   0.0 │
│ data1100           │     9 │ 2011-01-14 │   0.0 │
└────────────────────┴───────┴────────────┴───────┘
  10 rows                               4 columns
┌─────────┐
│ Success │
│ boolean │
└─────────┘
  0 rows
┌────────────────────┬───────┬────────────┬───────┐
│ current_database() │ rowid │    date    │ value │
│      varchar       │ int64 │    date    │ float │
├────────────────────┼───────┼────────────┼───────┤
│ data1110           │     0 │ 2025-11-07 │   0.0 │
│ data1110           │     1 │ 2025-11-06 │   0.0 │
│ data1110           │     2 │ 2025-11-05 │   0.0 │
│ data1110           │     3 │ 2025-11-04 │   0.0 │
│ data1110           │     4 │ 2025-11-03 │   0.0 │
│ data1110           │     5 │ 2025-10-31 │   0.0 │
│ data1110           │     6 │ 2025-10-30 │   0.0 │
│ data1110           │     7 │ 2025-10-29 │   0.0 │
│ data1110           │     8 │ 2025-10-28 │   0.0 │
│ data1110           │     9 │ 2025-10-27 │   0.0 │
└────────────────────┴───────┴────────────┴───────┘
  10 rows                               4 columns
┌─────────┐
│ Success │
│ boolean │
└─────────┘
  0 rows
┌────────────────────┬───────┬────────────┬───────┐
│ current_database() │ rowid │    date    │ value │
│      varchar       │ int64 │    date    │ float │
├────────────────────┼───────┼────────────┼───────┤
│ data1111           │     0 │ 2011-01-03 │   0.0 │
│ data1111           │     1 │ 2011-01-04 │   0.0 │
│ data1111           │     2 │ 2011-01-05 │   0.0 │
│ data1111           │     3 │ 2011-01-06 │   0.0 │
│ data1111           │     4 │ 2011-01-07 │   0.0 │
│ data1111           │     5 │ 2011-01-10 │   0.0 │
│ data1111           │     6 │ 2011-01-11 │   0.0 │
│ data1111           │     7 │ 2011-01-12 │   0.0 │
│ data1111           │     8 │ 2011-01-13 │   0.0 │
│ data1111           │     9 │ 2011-01-14 │   0.0 │
└────────────────────┴───────┴────────────┴───────┘
  10 rows                               4 columns

OS:

macOS Tahoe

DuckDB Version:

v1.5.2 (Variegata)

DuckLake Version:

415a9eb

DuckDB Client:

CLI

Hardware:

No response

Full Name:

Bill Zissimopoulos

Affiliation:

N/A

What is the latest build you tested with? If possible, we recommend testing with the latest nightly build.

I have tested with a stable release

Did you include all relevant data sets for reproducing the issue?

Yes

Did you include all code required to reproduce the issue?

• Yes, I have

Did you include all relevant configuration (e.g., CPU architecture, Python version, Linux distribution) to reproduce the issue?

• Yes, I have

[billziss-gh]

The exact same failure happens if one changes the pattern:

CREATE TEMP TABLE data_temp AS SELECT * FROM 'data.csv' ORDER BY date;
INSERT INTO data SELECT * FROM data_temp;
DROP TABLE data_temp;

To the pattern:

INSERT INTO data SELECT * FROM 'data.csv' ORDER BY date;

See attached script: bugINS.sql

[mohsinsurani]

Hi, I can reproduce this scenario using your bugINS.sql script. It looks like the implicit default sort direction is dropping to Descending when the explicit modifier is omitted. I am looking into isolating the parser config for SET SORTED BY and will submit a patch.

[mohsinsurani]

Hey @billziss-gh, I've managed to isolate the root cause in ducklake_table_entry.cpp where the missing direction token wasn't resolving back to ASCENDING during streaming file insertions. I've wrapped your bugINS.sql pattern into a new regression test and submitted a fix over at PR #1227

[billziss-gh]

@mohsinsurani thank you very much!