ODBC Driver SQLStatistics returns duplicate rows/rows for other tables

[faridzidan]

Describe the bug
ODBC Driver returns duplicate rows executing ODBC API SQLStatistics on a table.
Also, ORDINAL_POSITION returned in the resultset is zero.

To Reproduce

1. create some test tables with primary key, alternate key and some foreign keys. Example:

create table in_sync_cmp_type (
cmp_type_cd char(8) not null,
description varchar(30) not null,
ctrl_ins_dtm timestamp not null default CURRENT_TIMESTAMP,
ctrl_upd_dtm timestamp not null,
ctrl_usr_id varchar(256) not null,
constraint pk_in_sync_cmp_type primary key (cmp_type_cd)
);

/----------------------------------------------------------------------------/
/* Table: in_sync_user /
/----------------------------------------------------------------------------*/
create table in_sync_user (
usr_oid int not null,
logid varchar(256) not null,
ctrl_ins_dtm timestamp not null default CURRENT_TIMESTAMP,
ctrl_upd_dtm timestamp not null,
ctrl_usr_id varchar(256) not null,
constraint pk_in_sync_user primary key (usr_oid),
constraint ak_isu_logid unique (logid)
);

/----------------------------------------------------------------------------/
/* Table: in_sync_resultset /
/----------------------------------------------------------------------------*/
create table in_sync_resultset (
rs_oid int not null,
rs_type_cd char(8) not null,
script_oid int null,
script_upd_ind int null,
ctrl_ins_dtm timestamp not null default CURRENT_TIMESTAMP,
ctrl_upd_dtm timestamp not null,
ctrl_usr_id varchar(256) not null,
constraint pk_in_sync_resultset primary key (rs_oid)
);

create index ix1in_sync_resultset on in_sync_resultset (
ctrl_usr_id,
rs_type_cd,
ctrl_ins_dtm
);

create table in_sync_data_source (
ds_oid integer not null,
dbms_name varchar(256) not null,
server_name varchar(256) null,
cluster_id varchar(256) null,
database_name varchar(520) null,
logid varchar(256) null,
owner_oid int null,
root_rs_oid int null,
readonly_ind int not null,
ctrl_ins_dtm timestamp not null default CURRENT_TIMESTAMP,
ctrl_upd_dtm timestamp not null,
ctrl_usr_id varchar(256) not null,
constraint pk_in_sync_data_source primary key (ds_oid)
);

alter table in_sync_data_source
add constraint fk_isds_isu foreign key (owner_oid)
references in_sync_user (usr_oid)
on update restrict
on delete restrict;

alter table in_sync_data_source
add constraint fk_isds_root_rs_oid foreign key (root_rs_oid)
references in_sync_resultset (rs_oid)
on update restrict
on delete restrict;

2. Execute ODBC function SQLStatistics on one table. Example for table, in_sync_user
3. Driver returns duplicate rows all with 0 ORDINAL_POSITION

Expected behavior
In the example above for table in_sync_user, driver should returns only two rows with the name of primary and alternate keys defined on the table and both rows should have ORDINAL_POSITION of 1.

Screenshots
Attached screen shot of SQLStatistic resulset rows executed on the example table in_sync_user described above.

Software versions
MonetDB 11.41.0013
ODBC Driver: MonetDBODBClib 11.41.0013 Jul2021-SP2

• OS and version: Windows 10 x64
• Installed from release package

**Issue labeling **
ODBC Driver SQLStatistics extra rows

Additional context
Looks like the returned resultset has rows for the indexes of other tables but all labeled with the name of the table SQLStatistics is executed on.

[sjoerdmullender]

It turned out there were a couple of issues with the query used to implement the function SQLStatistics. They have now been fixed.