apache / apache/datafusion

When reading a csv or json file, columns get sorted in the dataframe, how NOT to?

Open
#18,770 7 comments 0 reactions 0 assignees View on GitHub
question
Dominant language
Rust
Stars
9.3k
Forks
2.4k
Avg merge
3d 7h
Merged PRs (30d)
344

Description

### Describe the bug

Given a json (or a csv) read by datafusion-cli with
```text
create external table t17 stored as json location '20251117.json';
```
whatever the columns order in the json(or csv) file, all columns get sorted in the dataframe, which means when writing into another format, order of columns will be different! I can not find the options either in the API or in datafusion-cli rto prevent this. I do not understand why this happens at all. At least there should be an option.

### To Reproduce

```shell
head -1 20251117.json
{"uti":1763334319,"dat":"2025-11-16 23:05:19.186018808","tim":"23:05:19.186018808","hex":"4d00c3","fli":"","lat":48.20722961425781,"lon":2.240788386418269,"gda":"A","src":"A","alt":38000,"altg":37900,"hgt":-100,"spd":385,"cat":"A0","squ":"2256","vrt":0,"trk":56.84539,"mop":2,"lla":2,"tru":29,"dbm":-90,"nucp":8,"nic":8,"pic":11}
```

datafusion-cli
```text
> create external table t17 stored as json location '20251117.json';
0 row(s) fetched.
Elapsed 0.024 seconds.

> describe t17;
+-------------+-----------+-------------+
| column_name | data_type | is_nullable |
+-------------+-----------+-------------+
| alt | Int64 | YES |
| altg | Int64 | YES |
| cat | Utf8 | YES |
| dat | Utf8 | YES |
| dbm | Int64 | YES |
| fli | Utf8 | YES |
| gda | Utf8 | YES |
| hex | Utf8 | YES |
| hgt | Int64 | YES |
| lat | Float64 | YES |
| lla | Int64 | YES |
| lon | Float64 | YES |
| mop | Int64 | YES |
| nic | Int64 | YES |
| nucp | Int64 | YES |
| pic | Int64 | YES |
| spd | Int64 | YES |
| squ | Utf8 | YES |
| src | Utf8 | YES |
| tim | Utf8 | YES |
| trk | Float64 | YES |
| tru | Int64 | YES |
| uti | Int64 | YES |
| vrt | Int64 | YES |
+-------------+-----------+-------------+
24 row(s) fetched.
```
there should NO SORTING by defaullt.

### Expected behavior

Columns are not sorted.

### Additional context

I have seen the `file_sort_order` option, but I do not believe this is it. I cannot find any option to reverse the behaviour.
I use the [bdt](https://github.com/datafusion-contrib/bdt) tool every day to convert from JSONL to CSV and that's where I found the problem. bdt use datafusion for all its operations.

Contributor guide

Open the contributing guide

Research direction

Start with the datafusion-cli CREATE EXTERNAL TABLE path for JSON and CSV schema inference, and compare it with the reported describe t17 output. Trace where the inferred column order is established, including whether file_sort_order is involved; done means preserving source order by default and confirming the behavior through a relevant reader or CLI test.

Written by the indexing model from the issue text.

Assessment

Tech stack
rust
Domain
databases
Issue type
Bug
Difficulty
3/5
Estimated time
1-2 days
Activity status
Stale
Clarity
Mostly clear
Newbie friendliness
45/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.