Derby
  1. Derby
  2. DERBY-2651

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

    Details

    • Type: Bug Bug
    • Status: Open
    • Priority: Minor Minor
    • Resolution: Unresolved
    • Affects Version/s: 10.2.2.0, 10.3.1.4
    • Fix Version/s: None
    • Component/s: JDBC, Network Client
    • Urgency:
      Normal
    • Issue & fix info:
      Repro attached
    • Bug behavior facts:
      Embedded/Client difference

      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

        Issue Links

          Activity

          Hide
          Jørgen Løland added a comment -

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

          Show
          Jørgen Løland added a comment - If the two outputs are equal, the ij7 tests can be run in embedded and client modes without duplicating the ij7.out file
          Hide
          Jørgen Løland added a comment -

          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-------
          Show
          Jørgen Løland added a comment - 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 -------
          Hide
          Knut Anders Hatlen added a comment -

          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.

          Show
          Knut Anders Hatlen added a comment - 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.
          Hide
          Jørgen Løland added a comment -

          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.

          Show
          Jørgen Løland added a comment - 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.

            People

            • Assignee:
              Unassigned
              Reporter:
              Jørgen Løland
            • Votes:
              0 Vote for this issue
              Watchers:
              0 Start watching this issue

              Dates

              • Created:
                Updated:

                Development