Different column order between design - runtime databases error

[melanore]

Currently, type provider explicitly asserts proper column existance, type, and order within returned row by comparing the design time data columns to runtime version from NpgsqlDataReader.
However, in case of multiple environments, - e.q. production/feature branch/staging, column order in postgresql db can be different.
Upon generating a schema dump from production, pgdump does not take into account proper column ordering in output schema creation script for some reason (the columns in table create statement are not in same order as in db that was a target for dump).
The issue is present only in case of "SELECT * FROM ..." statements, when order of columns can not be guaranteed. It is a bad practice to use such statements however in practice some projects do use such queries.
I've checked the provider logic, and on first glance neither DataTable nor single row mapping does not depend on certain order of columns returned in a NpgsqlReader.
So I've relaxed a check inside VerifyOutputColumns to check only number of columns, presence and type of columns, that are expected from design time.
Upon swapping type provider dll it seems to work just fine, however I'm not aware of any hidden problems that could happen.

How to reproduce

• Given a pg database with table A, that has columns A1, A2
• Dump schema and modify a create table script to create columns in order A2, A1
• In design time, use you local copy of db, restored from dump, and create a simple "SELECT * FROM A" db command
• In runtime target original db

Expected:
Query was successfully executed

Actual:
Query fails to execute due to wrong column order

[dmitry-a-morozov]

I'm on favor of getting away from "SELECT *" which is considered to be bad practice for SQL.
Relaxing type provider check won't resolve case where column is deleted or renamed.
Also as food for thought think of following valid query:

select *
from my_table
order by 2, 3

By using select * ... developer intentionally opts-in for unpredictable column order which is ok for quick-n-dirty scripting but not for applications. In the query above output rows ordering will be different depending on physical columns order.

[melanore]

I agree that "SELECT *" is a bad practice, however as mapping to ado net DataTable does not take into consideration column ordering, this check might be too strict. All other checks for column name/type existance must absolutely be in place.

Default DataTable behavior:

PR with relaxed column checking was submitted: #29

[dmitry-a-morozov]

Right, but ADO.NET data table is purely run-time concept. There is no type verification as in case of type provider. This is major point of type provider to guarantee that compile time types aligned with run-time.

[kerams]

If types and names all match, why should it matter what order columns are returned in from Postgres?

This does not concern just select * queries, but also potential support for #54. If I create 2 separate commands, select num, text from table and select text, num from table where id = @id, I'd expect them to have exactly the same return type, allowing me to use a single mapping function.

[daz10000]

It’s mapping parameters positionally for efficiency at runtime. Could look up by name but more work. We debated this a lot and ended up moving away from select * which was particularly dangerous. Explicit selects are fairly robust. Von meinem iPhone gesendet

…

Am 14.02.2020 um 4:32 AM schrieb kerams ***@***.***>:  If types and names all match, why should it matter what order columns are returned in from Postgres? — You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub, or unsubscribe.

[daz10000]

Reading back through all thread now :) memory lane. I agree would be nice to make sure as long as type is consistent we can be flexible about underlying column numeric order Von meinem iPhone gesendet

…

Am 14.02.2020 um 8:16 AM schrieb Darren Platt ***@***.***>: It’s mapping parameters positionally for efficiency at runtime. Could look up by name but more work. We debated this a lot and ended up moving away from select * which was particularly dangerous. Explicit selects are fairly robust. Von meinem iPhone gesendet >> Am 14.02.2020 um 4:32 AM schrieb kerams ***@***.***>: >> >  > If types and names all match, why should it matter what order columns are returned in from Postgres? > > — > You are receiving this because you are subscribed to this thread. > Reply to this email directly, view it on GitHub, or unsubscribe.

[kerams]

It matters for the tuple result type, but DataTable and Record are just key-based inserts at runtime anyway.