avm19 opened a new issue, #2006:
URL: https://github.com/apache/arrow-adbc/issues/2006
### What happened?
`adbc_get_table_schema()` does not respect column order as returned by the
database.
I could not reproduce the bug with a de-novo created table, as this would
require some unknown amount of table butchery in Postgres. <s>You have to take
my word for it.</s> I figured out the source and a fix, which look quite
straightforward. I do not do C/CPP, so unable to commit.
## The bug
```python
conn = adbc_driver_postgresql.dbapi.connect(uri)
cursor = conn.cursor()
cursor.execute('select * from "Posts" where 1 = 2;')
cursor.fetch_arrow_table().schema # Returns a schema in the right order
```
```python
conn.adbc_get_table_schema('"Posts"') # Returns a schema in a WRONG order
```
## The proposed fix
https://github.com/apache/arrow-adbc/blob/6c7ad9951403d4d5b6a61d65a0afd9331372ea52/c/driver/postgresql/connection.cc#L1142
is responsible for querying a table schema from Postgres.
As can be seen from the SQL query string, there is no 'order by' clause.
According to https://stackoverflow.com/a/285740/, the column order is defined
by `pg_attribute.attnum`. So the fix would be:
```diff
std::string query =
"SELECT attname, atttypid "
"FROM pg_catalog.pg_class AS cls "
"INNER JOIN pg_catalog.pg_attribute AS attr ON cls.oid = attr.attrelid
"
"INNER JOIN pg_catalog.pg_type AS typ ON attr.atttypid = typ.oid "
- "WHERE attr.attnum >= 0 AND cls.oid = $1::regclass::oid";
+ "WHERE attr.attnum >= 0 AND cls.oid = $1::regclass::oid"
+ "ORDER BY attr.attnum;"
```
### Stack Trace
_No response_
### How can we reproduce the bug?
_No response_
### Environment/Setup
Python 3.11, installed via pip:
```bash
pip list | grep adbc
adbc-driver-manager 1.1.0
adbc-driver-postgresql 1.1.0
adbc-driver-sqlite 1.1.0
```
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: issues-unsubscr...@arrow.apache.org.apache.org
For queries about this service, please contact Infrastructure at:
us...@infra.apache.org
