Uploaded image for project: 'Derby'
  1. Derby
  2. DERBY-3458

dblook fails on TERRITORY_BASED databases

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Minor
    • Resolution: Fixed
    • Affects Version/s: 10.3.2.1
    • Fix Version/s: 10.3.3.0, 10.4.1.3
    • Component/s: Tools
    • Labels:
      None

      Description

      I've created small patches for myself by replacing all related queries in the 'tools' section with CASTs to CHARs and VARCHARs and would like to contribute these to the community in case anyone else can confirm this is a bug.

      A small test case to reproduce the problem is provided below, the version of Derby that provides the stacktrace is 10.3.2.1.

      Regards,

      Stephan van Loendersloot.

      Reproduction steps:

      ---------- 1: create_territory_db.sql ----------

      CONNECT 'jdbc:derby://localhost/dutch;user=dutch;password=dutch;create=true;territory=nl_NL;collation=TERRITORY_BASED';

      AUTOCOMMIT OFF;

      CREATE TABLE AIRLINES
      (
      AIRLINE CHAR(2) NOT NULL ,
      AIRLINE_FULL VARCHAR(24),
      BASIC_RATE DOUBLE PRECISION,
      DISTANCE_DISCOUNT DOUBLE PRECISION,
      BUSINESS_LEVEL_FACTOR DOUBLE PRECISION,
      FIRSTCLASS_LEVEL_FACTOR DOUBLE PRECISION,
      ECONOMY_SEATS INTEGER,
      BUSINESS_SEATS INTEGER,
      FIRSTCLASS_SEATS INTEGER
      );

      COMMIT;

      DISCONNECT;
      EXIT;

      ---------- 2: use dbloook ----------

      dblook -d "jdbc:derby://localhost/dutch;user=dutch;password=dutch" -o dutch.sql

      ---------- 3: stacktrace ----------

      java.sql.SQLSyntaxErrorException: Comparisons between 'CHAR (UCS_BASIC)' and 'CHAR (TERRITORY_BASED)' are not supported. Types must be comparable. String types must also have matching collation. If collation does not match, a possible solution is to cast operands to force them to the default collation (e.g. select tablename from sys.systables where CAST(tablename as VARCHAR(128)) = 'T1')
      at org.apache.derby.client.am.SQLExceptionFactory40.getSQLException(Unknown Source)
      at org.apache.derby.client.am.SqlException.getSQLException(Unknown Source)
      at org.apache.derby.client.am.Statement.executeQuery(Unknown Source)
      at org.apache.derby.tools.dblook.prepForDump(Unknown Source)
      at org.apache.derby.tools.dblook.go(Unknown Source)
      at org.apache.derby.tools.dblook.<init>(Unknown Source)
      at org.apache.derby.tools.dblook.main(Unknown Source)
      Caused by: org.apache.derby.client.am.SqlException: Comparisons between 'CHAR (UCS_BASIC)' and 'CHAR (TERRITORY_BASED)' are not supported. Types must be comparable. String types must also have matching collation. If collation does not match, a possible solution is to cast operands to force them to the default collation (e.g. select tablename from sys.systables where CAST(tablename as VARCHAR(128)) = 'T1')
      at org.apache.derby.client.am.Statement.completeSqlca(Unknown Source)
      at org.apache.derby.client.net.NetStatementReply.parsePrepareError(Unknown Source)
      at org.apache.derby.client.net.NetStatementReply.parsePRPSQLSTTreply(Unknown Source)
      at org.apache.derby.client.net.NetStatementReply.readPrepareDescribeOutput(Unknown Source)
      at org.apache.derby.client.net.StatementReply.readPrepareDescribeOutput(Unknown Source)
      at org.apache.derby.client.net.NetStatement.readPrepareDescribeOutput_(Unknown Source)
      at org.apache.derby.client.am.Statement.readPrepareDescribeOutput(Unknown Source)
      at org.apache.derby.client.am.Statement.flowExecute(Unknown Source)
      at org.apache.derby.client.am.Statement.executeQueryX(Unknown Source)
      ... 5 more
      – **--> DEBUG: Comparisons between 'CHAR (UCS_BASIC)' and 'CHAR (TERRITORY_BASED)' are not supported. Types must be comparable. String types must also have matching collation. If collation does not match, a possible solution is to cast operands to force them to the default collation (e.g. select tablename from sys.systables where CAST(tablename as VARCHAR(128)) = 'T1')

        Attachments

        1. DERBY-3458_patch_4.stat
          2 kB
          Stephan van Loendersloot
        2. DERBY-3458_patch_4_function_tests.txt
          136 kB
          Stephan van Loendersloot
        3. DERBY-3458_patch_4.txt
          1 kB
          Stephan van Loendersloot
        4. DERBY-3458_patch_3.stat
          2 kB
          Stephan van Loendersloot
        5. DERBY-3458_patch_3_function_tests.txt
          112 kB
          Stephan van Loendersloot
        6. DERBY-3458_patch_3.txt
          1 kB
          Stephan van Loendersloot
        7. DERBY-3458_patch_2.stat
          0.5 kB
          Stephan van Loendersloot
        8. DERBY-3458_patch_2.txt
          79 kB
          Stephan van Loendersloot
        9. DERBY-3458_patch_1.stat
          0.3 kB
          Stephan van Loendersloot
        10. DERBY-3458_patch_1.txt
          6 kB
          Stephan van Loendersloot

          Activity

            People

            • Assignee:
              whistle Stephan van Loendersloot
              Reporter:
              whistle Stephan van Loendersloot
            • Votes:
              0 Vote for this issue
              Watchers:
              0 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: