[9.5.444 Pro] - Serious issue with $COLUMNS$ in Oracle

[9.5.444 Pro] - Serious issue with $COLUMNS$ in Oracle

$COLUMNS(vertical, types, keys)$ reports /* Cannot find key columns in xyz */ for tables that definitely have primary keys on them. Sometimes it only misses some of the key columns. e.g., for a table, that has a composite primary key of a varchar2(5) + a date columns (yes, I know, but it's in a legacy database) the macro retrieves its date column, but the varchar2 one is lost somewhere in the process.

Thank you very much for reporting this bug. I have logged it in our bug tracking system.

Would you please provide the DDL for the table that you see the issue with?

We're unable so far to reproduce this issue.

Certainly. Here's the code that was used to retrieve the ddl:

	Code:
	select sys.dbms_metadata.get_ddl('TABLE', 'VONAL', 'DEMO1') from dual;

And this is the CREATE TABLE for table vonal as retrieved by the above code:

Code:

CREATE TABLE "DEMO1"."VONAL"
( "VONAL_ID" VARCHAR2(5) NOT NULL ENABLE,
"DATUM_TOL" DATE NOT NULL ENABLE,
"VAROS_ID" NUMBER(10,0) DEFAULT 0 NOT NULL ENABLE,
"VONAL_NEV" VARCHAR2(140) DEFAULT '' NOT NULL ENABLE,
"AKTIV_E" NUMBER(1,0) DEFAULT 1 NOT NULL ENABLE,
"DATUM_IG" DATE,
CONSTRAINT "VONAL_ID" PRIMARY KEY ("VONAL_ID", "DATUM_TOL")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "DEMO" ENABLE,
CONSTRAINT "UK_VONAL__VONAL_ID_DATUM_IG" UNIQUE ("VONAL_ID", "DATUM_IG")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "DEMO" ENABLE
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "DEMO" NO INMEMORY

I'm sure there are tons of irrelevant stuff in this code but the part

	Code:
	CONSTRAINT "VONAL_ID" PRIMARY KEY ("VONAL_ID", "DATUM_TOL")

is clear.

Both $COLUMNS(vertical,types,keys)$ and $COLUMNS(vertical,type_classes,keys)$ return

	Code:
	datum_tol DATE

for this table.

Thank you. It appears that one of PK columns is also member of unique constraint, and that's related to the issue.

Please take a look at the Options, see "Columns (Oracle) + Keys" query. There is a subquery that checks whether a column is referenced in a constraint. That feature is limited to one constraint type only. Whichever constraint comes first is used as a flag for the column / constraint reference. This handling is not specific to Oracle, it does the same for all database interfaces. The internal cache has room just for one constraint type flag.

If you feel like primary keys are always more important than other constraints, you can modify that query and add "ORDER BY c.constraint_type" clause to the subquery

Well, it was not as simple as adding the ORDER BY to the subquery, I've had my battle with "missing right parenthesis" :) But now it seems to work, and as a side-effect, I've learnt a lot about how Oracle handles subqueries. Thank you very much for the hint.

Glad you have found a working solution. As an alternative to ORDER BY in the subquery, it could have been changed to SELECT min(constraint_type) after removing WHERE rownum =1, that way 'P' type would alway come first.