Columns re-ordered after type change

[seancolsen]

Reproduce

1. "New Table" > "Import Data" > "Copy and Paste Text"

2. Paste the following data and proceed to create and view the table (by clicking "Continue" and then "Finish Import").

   id,a,b
   1,100,200
   2,101,201
   

3. Click on the column header for column a and change the type from "Number" to "Text", clicking "Save".

4. Expect columns to be ordered id, a, b.

5. Once I did this and observed the columns to be ordered a, id, b. Another time. I observed the columns to be ordered id, b, a. I'm not sure what affects the ordering of the columns, but the order seems to remain consistent (even if incorrect) after the type-change operation.

Implementation

The backend should make sure that the column list in the API should always be ordered by suitable index.

Additional details

• The front end displays the columns in the order it receives them from the columns API. In this case the API is returning the columns out-of-order, which is why I've marked this as a backend bug.
• A while back, we had a discussion about whether the user should be able to re-order columns in which we decided not to support re-ordering columns, at least for now.

[vorakunal]

May I work on this issue?

[silentninja]

column_index is just incorrectly computed attnum. attnums is the order by the column are stored on the database, the columns should be ordered by attnum and not by id

[silentninja]

Thanks! @vorakunal I have assigned the issue to you. Let us know if you need any help.

[mathemancer]

column_index is just incorrectly computed attnum. attnums is the order by the column are stored on the database, the columns should be ordered by attnum and not by id

Sort of, it's an incorrectly computed "dense rank" of the column attnum. I agree with @silentninja that we should just use attnum, though. For more detail see my comment in the relevant discussion.

[kgodey]

Sounds good, let's use attnum.

[vorakunal]

@seancolsen I am not to able reproduce the error, I followed the instruction but the order is consistent after the type-change operation for both the column.
I'm attaching a screenshot of the table after performing an operation.

[seancolsen]

@vorakunal Yeah, the fact that you can't reproduce it is not too surprising, given that I was observing some inconsistent behavior.

@silentninja and @mathemancer since you have a deeper understanding of the root cause of the problem, would you be able to provide more resilient reproduction steps?

[silentninja]

@vorakunal By default, Postgres uses random order, most of the time it is based on the order it is on the disk(creation order) but it cannot be relied upon as per postgres docs So this issue is a random bug and cannot be deterministically reproduced.

As some of our use cases depend on the order of the columns, we should explicitly specify the sorting order when fetching column to order it based on the attnum property than reply on the random default ordering of the database (which cannot be relied upon)

[seancolsen]

@vorakunal just to clarify (from chatting with @silentninja) there is no need to successfully reproduce the bug before applying a fix where you order the columns by the attnum property.

[vorakunal]

@seancolsen @silentninja Alright, I'll look into it.
Thank you.

[kgodey]

Unassigned due to inactivity.

[Anish9901]

Can I work on this?

[kgodey]

@Anish9901 Sure, go ahead!