Column order changes depending on query

[jameskerr]

Depending on how the data is returned, the order of the columns bounces around depending on the query.

1. Find some heterogenous data so that the columns are off by default
2. Turn the columns from "Auto" to "On"
3. Start click the headers to add "sort" clauses to the query
4. See how sometime the columns are re-ordered

https://user-images.githubusercontent.com/3460638/139930725-9e6f0a51-198e-40e3-b1b9-cc0a42d7b670.mp4

[mccanne]

Shouldn't the column orders follow the order in the underlying records?

[mccanne]

Henri made a reorder primitive as part of his shaping work if you want to change the order. If we want UX to move them around it seems like the query should run a reorder and the display should show the data as it really is.

[philrz]

At the risk of repeating things folks already know, here's a summary of some history in this area and thoughts on what's been proposed thus far. App repros are with commit e17764c and Zed repros are with commit b242ea7.

Here's another repro of the original symptom using sample data all.ndjson.gz, which is a concatenation of the fruit.ndjson and people.ndjson data from the Zed join tutorial.

https://user-images.githubusercontent.com/5934157/191334281-6cd5b9e6-2759-4285-b93f-5d82e2b3547c.mp4

In the video I intentionally focused on the name and age columns because these confirm @mccanne's comment above about how the "column orders follow the order in the underlying records", though this combines with the logic the app currently uses to populate the column headers to produce the potentially confusing UX. Consider the table output at the Zed layer (I've added lines and extra spacing make the headers more obvious).

$ zed query -f table 'from all.ndjson.gz'
name       color flavor
----------------------
strawberry red   sweet
avocado    green savory

name   age likes
----------------------
morgan 61  tart
jessie 30  plain
name   color  flavor
banana yellow sweet

name  age likes note
----------------------
quinn 14  sweet many kids enjoy sweets

name  color flavor note
----------------------
dates brown sweet  in season

name  age likes
----------------------
chris 47  tart

name  color flavor
----------------------
apple red   tart
figs  brown plain

We can see how the app seems to be constructing its header row by adding columns for each new field name encountered in the query result. Therefore the adding of the sort age correlates with the age column header being moved leftward, as it's now encountered earlier in the query result.

$ zed query -f table 'from all.ndjson.gz | sort age'
name  age likes note
----------------------
quinn 14  sweet many kids enjoy sweets

name   age likes
----------------------
jessie 30  plain
chris  47  tart
morgan 61  tart

name       color  flavor
----------------------
strawberry red    sweet
avocado    green  savory
banana     yellow sweet

name  color flavor note
----------------------
dates brown sweet  in season

name  color flavor
----------------------
apple red   tart
figs  brown plain

Incidentally, this logic for ordering column names was flagged in the past as confusing (#949) because it had previously been used to populate the field list in the column chooser, which made it difficult to find named fields in the chooser, particularly if there were lots of fields spread across many diverse record shapes. In #1012 a change was made to sort the field names in the chooser alphabetically, but the order of the actual columns shown in the results pane still seem to follow the original logic.

As @mccanne notes above, the order() function could have a role to play in forcing the column order to conform to the user's preferences, though when combined with the current app logic that alone might not quite do the trick.

However, if I add a fill() before the order, now I get a single header row in both cases.

Therefore it seems that if the app were able to use the current column chooser checkbox/order to construct a type definition like this, the app could provide an experience similar to macOS Finder or Windows Explorer where the user could easily click/drag to reorder the presentation of the columns and have the query result return in that order. (Incidentally, I'd find it a little strange if the app had to know and populate the data types for each field, since there's no casting actually happening here.)

[jameskerr]

It's a pretty cool idea Phil.