SQLSpecialColumns ODBC API does not return Columns part of UNIQUE INDEX on YugaByteDB

[raddakal]

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

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.

[davecramer]

What product are you releasing ?

[raddakal]

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.

[davecramer]

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

[davecramer]

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

[davecramer]

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

[davecramer]

@raddakal can you test #85 ?

[abhi-555]

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

[davecramer]

Thanks, I'll take a look

[davecramer]

@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 ?

[davecramer]

Fixed by #85

[abhi-555]

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

[davecramer]

@abhi-555 This #87 passes. It has 2 unique keys.

[abhi-555]

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 and confirm if this is working for you?