Embedded and client behave differently for IJ command "show indexes"

• Key: DERBY-2651
• Type: Bug
• Status: Open
• Priority: Minor
• Assignee: Unassigned
• Reporter: Jørgen Løland
• Component/s: JDBC, Network Client
• Affects Version/s: 10.2.2.0, 10.3.1.4

Description

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

Comments

[Jørgen Løland]

If the two outputs are equal, the ij7 tests can be run in embedded and client modes without duplicating the ij7.out file

[Jørgen Løland]

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--------

[Knut Anders Hatlen]

Since the JDBC spec says that NON_UNIQUE is of type boolean, I would prefer to keep the behaviour on embedded. Better to have it right in one of the drivers than in none of them, in my opinion... :)

ij7.sql is a relatively small test, so having two canons isn't a very big problem. However, if you want to make it run as part of the JUnit test LangScripts (which would be good since we are moving away from the old test harness) I think you would need two separate tests. It's not ideal, but I think it's better than changing the correct implementation on embedded.

[Jørgen Løland]

That's a good point, Knut. Maybe it's better to postpone this issue until the client can handle booleans, and then make the behaviour consistently correct.

Like you say, it should be a small task to duplicate the tests, even though it would be more elegant not to... I'll unassign from this issue for now.