You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-dev@db.apache.org by "Jorg Janke (JIRA)" <de...@db.apache.org> on 2006/08/11 04:30:14 UTC
[jira] Created: (DERBY-1669) DatabaseMetaData.getIndexInfo() does
not differentiate between generated and real indexes
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
[jira] Updated: (DERBY-1669) DatabaseMetaData.getIndexInfo() does
not differentiate between generated and real indexes
Posted by "Andrew McIntyre (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-1669?page=all ]
Andrew McIntyre updated DERBY-1669:
-----------------------------------
Component/s: JDBC
> 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
> Components: JDBC
> 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
[jira] Updated: (DERBY-1669) DatabaseMetaData.getIndexInfo() does
not differentiate between generated and real indexes
Posted by "Kathey Marsden (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-1669?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Kathey Marsden updated DERBY-1669:
----------------------------------
Issue Type: Improvement (was: Bug)
Changing this to improvement to provide a way to differentiate between system generated and user created indexes.
> DatabaseMetaData.getIndexInfo() does not differentiate between generated and real indexes
> -----------------------------------------------------------------------------------------
>
> Key: DERBY-1669
> URL: https://issues.apache.org/jira/browse/DERBY-1669
> Project: Derby
> Issue Type: Improvement
> Components: JDBC
> 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.
-
You can reply to this email to add a comment to the issue online.
[jira] Updated: (DERBY-1669) DatabaseMetaData.getIndexInfo() does
not differentiate between generated and real indexes
Posted by "Kathey Marsden (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-1669?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Kathey Marsden updated DERBY-1669:
----------------------------------
Urgency: Normal (was: Urgent)
Changing to normal urgency. This won't make it for 10.5.2. We need to find a standard compliant way to provide this information.
> DatabaseMetaData.getIndexInfo() does not differentiate between generated and real indexes
> -----------------------------------------------------------------------------------------
>
> Key: DERBY-1669
> URL: https://issues.apache.org/jira/browse/DERBY-1669
> Project: Derby
> Issue Type: Improvement
> Components: JDBC
> 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.
-
You can reply to this email to add a comment to the issue online.