[jira] Commented: (DERBY-2651) Embedded and client behave differently for IJ command "show indexes"

["Jørgen Løland (JIRA)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/DERBY-2651?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12495997 ] 

Jørgen Løland commented on DERBY-2651:
--------------------------------------

After some digging, I found what causes the difference in behaviour and a possible solution:

For embedded, the showIndexes method in IJ gets a org.apache.derby.impl.jdbc.EmbedResultSet20 when calling EmbedDatabaseMetaData#getIndexInfo. The data type for the "NON_UNIQUE" column in this result set is boolean.

For client, on the other hand, IJ gets a org.apache.derby.client.net.NetResultSet. This ResultSet does not support boolean, and the columntype is therefore smallint.

If the query metadata.properties#getIndexInfo is modified as shown below, getIndexInfo consistently returns "non_unique"-values of type smallint. Thus, the "show tables" command always produces the client-like result (0/1). 

Q) Is consistently using non_unique values of 0/1 better than (Embed: 0/1, Client:true/false)? This could, of course, be modified to a consistent boolean type once NetResultSet supports boolean. Other suggestions for solutions are also much appreciated.

If there are no objections or suggestions for other solutions, I will make a patch that changes the metadata query tomorrow.


(svn diff):
--------8<-------
 getIndexInfo=\
        SELECT CAST ('' AS VARCHAR(128)) AS TABLE_CAT, S.SCHEMANAME AS TABLE_SCHEM, T.TABLENAME AS TABLE_NAME, \
-                  (CASE WHEN CONGLOMS.DESCRIPTOR.isUnique() THEN FALSE ELSE TRUE END) AS NON_UNIQUE, \
+                  CAST ((CASE WHEN CONGLOMS.DESCRIPTOR.isUnique() THEN 0 ELSE 1 END) AS SMALLINT) AS NON_UNIQUE, \
                   CAST ('' AS VARCHAR(128)) AS INDEX_QUALIFIER, \
-------->8--------


> Embedded and client behave differently for IJ command "show indexes"
> --------------------------------------------------------------------
>
>                 Key: DERBY-2651
>                 URL: https://issues.apache.org/jira/browse/DERBY-2651
>             Project: Derby
>          Issue Type: Bug
>          Components: JDBC, Network Client
>    Affects Versions: 10.2.2.0, 10.3.0.0
>            Reporter: Jørgen Løland
>         Assigned To: Jørgen Løland
>            Priority: Minor
>
> The column "NON_UNIQUE" is not the same when IJ command "show indexes" is run in embedded and client mode. In embedded, "NON_UNIQUE" has the values true/false, while client has 1/0.
> Embed:
> ----8<----- 
> ij> connect 'jdbc:derby:testdb;create=true';
> ij> SET SCHEMA APP;
> ij> CREATE TABLE t1 (i int primary key, i2 int);
> ij> CREATE INDEX idx1 ON APP.t1 (i2);
> ij> SHOW INDEXES IN APP;
> TABLE_NAME          |COLUMN_NAME         |NON_U&|TYPE|ASC&|CARDINA&|PAGES
> ----------------------------------------------------------------------------
> T1                  |I                   |false |3   |A   |NULL    |NULL
> T1                  |I2                  |true  |3   |A   |NULL    |NULL
> Client:
> ----8<----- 
> ij> connect 'jdbc:derby://localhost/testdb;create=true';
> ij> SHOW INDEXES IN APP;
> TABLE_NAME          |COLUMN_NAME         |NON_U&|TYPE|ASC&|CARDINA&|PAGES
> ----------------------------------------------------------------------------
> T1                  |I                   |0     |3   |A   |NULL    |NULL
> T1                  |I2                  |1     |3   |A   |NULL    |NULL

-- 
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.