Subject: [postgresql-interfaces/psqlodbc] issue #79: SQLSpecialColumns ODBC API does not return Columns part of UNIQUE INDEX on YugaByteDB

From: "raddakal (@raddakal)" <[email-masked]>
Date: Tue, 26 Nov 2024 13:25:25 +0000

Hello,

Its a very common use case where an explicit primary key is not defined on the table but instead rely on the unique index. The ODBC driver is expected to return the columns that are of the UNIQUE index when SQLSpecialColumns ODBCAPI is called.

[SQLSpecialColumns.zip](https://github.com/user-attachments/files/17919927/SQLSpecialColumns.zip)

Environment details:
--------------------

OS: 
OEL8 Linux 64bit (el8uek.x86_64)

Database: 
Yugabyte DB 
PostgreSQL
Version 11.02.0001 PostgreSQL 11.2-YB-2024.1.1.0-b0
ODBC Version 03.52.0000

Driver:
Driver Information:
psqlodbcw.so
Version 17.00.0002
ODBC Version 03.51.

I have attached SQLSpecialColumns.zip which contains the standalone ODBC program along with the detailed steps to reproduce the issue.

Our major release is blocked because of this issue. 
A quick resolution/workaround would be highly appreciated.

Thanks.

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Fri, 29 Nov 2024 13:54:00 +0000

What product are you releasing ?

---

From: "raddakal (@raddakal)" <[email-masked]>
Date: Mon, 02 Dec 2024 07:52:42 +0000

The reported issue is not just confined to YugaByteDB but it is seen with PostgreSQL database as well.

I am part of Oracle and We certify native ODBC driver for PostgreSQL with one of the Data integration products.  This issue is critical for us.

Thanks.

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Thu, 05 Dec 2024 14:42:33 +0000

@raddakal OK, any chance you can provide a PR? Otherwise I will attempt to get this shortly

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Thu, 12 Dec 2024 20:58:52 +0000

Ok, I was able to reproduce the problem. Give me some time to see about a fix

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Sun, 29 Dec 2024 15:38:31 +0000

I'm working on this. What would you expect for a multi-column primary key or unique index ?

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Tue, 31 Dec 2024 14:31:14 +0000

@raddakal can you test https://github.com/postgresql-interfaces/psqlodbc/pull/85 ?

---

From: "abhi-555 (@abhi-555)" <[email-masked]>
Date: Thu, 09 Jan 2025 17:16:12 +0000

Hi Dave,

I work with @raddakal. I have tested the fix. It does not seem to be working. I have defined a unique index on the table and still SQLSpecialColumn does not return any value. Attaching the ODBC trace log and the DDL used

DDLs
create table public.test_col (id integer, name varchar(25));
create UNIQUE INDEX test_index ON public.test_col (name);

[REPL_odbctrace_1497393_01.log.zip](https://github.com/user-attachments/files/18364910/REPL_odbctrace_1497393_01.log.zip)

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Fri, 10 Jan 2025 18:01:24 +0000

Thanks, I'll take a look

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Mon, 13 Jan 2025 12:23:58 +0000

@abhi-555 
In the Pull Request #85 I created a table testtab1 
```
CREATE TABLE test_special( id integer primary key, ival integer);
CREATE UNIQUE index test_special_ui on public.test_special(ival);
```
Then in the test you can see the output as:

```
Result set:
NULL	public	test_special	id	int4	int4	4	4	0	1
NULL	public	test_special	ival	int4	int4	4	4	0	1
```

Are you able to run the test on your end and see the same ?

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Thu, 16 Jan 2025 14:31:37 +0000

Fixed by #85

---

From: "abhi-555 (@abhi-555)" <[email-masked]>
Date: Thu, 23 Jan 2025 12:53:38 +0000

Hi Dave,

The test case that you have is not correct. We need a fix for the case where the table should necessarily not have a primary key but unique indexes(can be one or multiple). Can you please check this case?

Regards,
Abhishek

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Thu, 23 Jan 2025 16:11:27 +0000

@abhi-555 This https://github.com/postgresql-interfaces/psqlodbc/pull/87 passes. It has 2 unique keys.

---

From: "abhi-555 (@abhi-555)" <[email-masked]>
Date: Tue, 18 Feb 2025 10:51:47 +0000

I am still receiving zero rows upon running SQLSpecialColumns in the given test case in the shared standalone.
Can you please run the test case in the standalone [SQLSpecialColumns.zip](https://github.com/user-attachments/files/17919927/SQLSpecialColumns.zip) and confirm if this is working for you?