db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Jorg Janke (JIRA)" <derby-...@db.apache.org>
Subject [jira] Created: (DERBY-1669) DatabaseMetaData.getIndexInfo() does not differentiate between generated and real indexes
Date Fri, 11 Aug 2006 02:30:14 GMT
DatabaseMetaData.getIndexInfo() does not differentiate between generated and real indexes
-----------------------------------------------------------------------------------------

                 Key: DERBY-1669
                 URL: http://issues.apache.org/jira/browse/DERBY-1669
             Project: Derby
          Issue Type: Bug
    Affects Versions: 10.1.3.1
         Environment: Windows 2003 Server
            Reporter: Jorg Janke


In Derby, I cannot distinguish what a "real" index is - all have TYPE=3 (= other) - all other
databases distinguishes correctly between Type=0 (statistic) and TYPE=1 (normal). It is easy
to be replicated with any table with a PK and an index.  Derby lists the internal indexes
for supporting constraints (name starting with SQL...) and the real indexes without making
a difference.

Example:

CREATE TABLE AD_COLUMN
(
   AD_COLUMN_ID    DECIMAL(10)          NOT NULL
)
/
ALTER TABLE AD_COLUMN
   ADD CONSTRAINT AD_COLUMN_KEY
                PRIMARY KEY (AD_COLUMN_ID)
/

CREATE TABLE AD_ACCESSLOG
(
   AD_ACCESSLOG_ID DECIMAL(10)      NOT NULL,
   AD_CLIENT_ID    DECIMAL(10)      NOT NULL,
   AD_COLUMN_ID    DECIMAL(10)          NULL,
   CONSTRAINT ADCOLUMN_ADACCESSLOG 
                                 FOREIGN KEY (AD_COLUMN_ID)
                    REFERENCES AD_COLUMN (AD_COLUMN_ID),
)
/
CREATE INDEX AD_ACCESSLOGTEST
   ON AD_ACCESSLOG(AD_CLIENT_ID)
/
ALTER TABLE AD_ACCESSLOG 
   ADD CONSTRAINT AD_ACCESSLOG_KEY
                PRIMARY KEY (AD_ACCESSLOG_ID)
/

Dump pf the ResultSet of  DatabaseMetaData.getIndexInfo()

 0: , TABLE_CAT=, TABLE_SCHEM=COMPIERE, 
TABLE_NAME=AD_ACCESSLOG, NON_UNIQUE=0, INDEX_QUALIFIER=, 
INDEX_NAME=SQL060709062929330, TYPE=3, ORDINAL_POSITION=1, 
COLUMN_NAME=AD_ACCESSLOG_ID, ASC_OR_DESC=A, CARDINALITY=null, 
PAGES=null, FILTER_CONDITION=null
 1: , TABLE_CAT=, TABLE_SCHEM=COMPIERE, 
TABLE_NAME=AD_ACCESSLOG, NON_UNIQUE=1, INDEX_QUALIFIER=, 
INDEX_NAME=AD_ACCESSLOGTEST, TYPE=3, ORDINAL_POSITION=1, 
COLUMN_NAME=AD_CLIENT_ID, ASC_OR_DESC=A, CARDINALITY=null, 
PAGES=null, FILTER_CONDITION=null
 2: , TABLE_CAT=, TABLE_SCHEM=COMPIERE, 
TABLE_NAME=AD_ACCESSLOG, NON_UNIQUE=1, INDEX_QUALIFIER=, 
INDEX_NAME=SQL060716064852400, TYPE=3, ORDINAL_POSITION=1, 
COLUMN_NAME=AD_COLUMN_ID, ASC_OR_DESC=A, CARDINALITY=null, 
PAGES=null, FILTER_CONDITION=null

Row #0 is the Primary Key
Row #2 is the Index
Row #3 is the Foreign Key

Issue: I cannot distinguish what a "real" index is - all have TYPE=3 (= other) - other databases
distiguish correctly between
Type=0 (statistic) and TYPE=1 (normal)
The bug: Row #1 should have TYPE=1

Unfortunately this puts our project to make Compiere available on Derby on hold.

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira

        

Mime
View raw message