Details

    • Type: Bug Bug
    • Status: Closed
    • Priority: Major Major
    • Resolution: Cannot Reproduce
    • Affects Version/s: 10.4.1.3, 10.4.2.0
    • Fix Version/s: None
    • Component/s: SQL
    • Environment:
      OS: Windows Vista
      Application environment: Java 1.5 Swing Application using Derby DB in the embedded mode as part of the application.
    • Urgency:
      Normal
    • Issue & fix info:
      High Value Fix, Repro attached, Workaround attached
    • Bug behavior facts:
      Performance

      Description

      Having the following db schema:
      CREATE TABLE Messaged_Users (ACCOUNT_ID VARCHAR(20) NOT NULL,
      USER_ID VARCHAR(20) NOT NULL,
      DATE TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
      STATUS INT DEFAULT 0,
      CONSTRAINT PK_ACC_USER_ID PRIMARY KEY (ACCOUNT_ID, USER_ID))
      CREATE TABLE Users (USER_ID VARCHAR(20) NOT NULL CONSTRAINT USER_ID_PK PRIMARY KEY,
      USER_NAME VARCHAR(50) NOT NULL)
      with approximately 100,000 records in each table and trying to execute the following query:
      PreparedStatement searchStatement = conn.prepareStatement("SELECT b.USER_ID, b.USER_NAME FROM Users a JOIN Messaged_Users b ON a.USER_ID=b.USER_ID AND a.ACCOUNT_ID=");
      searchStatement.setString(1, "5533232");
      ResultSet foundUsers = searchStatement.executeQuery();
      the executeQuery method actually never ends. I've waited for more than 30 mins and it didn't finish. I've tried it really many times but without any progress the query just hangs. I presume that this is definitely a bug in the derby db which should be fixed. Please note also that this fix is very urgent for us so that we can deliver our application.

      Thanks in advance for looking into this issue.

      1. derby_50kRows.log
        3 kB
        Bryan Pendleton
      2. derbyTwoDifferentOneHundredKPlans.log
        11 kB
        Bryan Pendleton
      3. Main_Bryan.java
        7 kB
        Bryan Pendleton
      4. Main_Updated.java
        7 kB
        Bryan Pendleton
      5. Main_Updated.java
        7 kB
        Georgi Hristov
      6. Main_Updated.java
        6 kB
        Georgi Hristov
      7. Main.java
        5 kB
        Dag H. Wanvik
      8. query_plan_derby_3892.pdf
        75 kB
        Nirmal Fernando
      9. query_plan.jpg
        44 kB
        Nirmal Fernando

        Activity

        Georgi Hristov created issue -
        Dag H. Wanvik made changes -
        Field Original Value New Value
        Attachment Main.java [ 12391391 ]
        Georgi Hristov made changes -
        Attachment Main_Updated.java [ 12392031 ]
        Bryan Pendleton made changes -
        Attachment Main_Bryan.java [ 12392098 ]
        Bryan Pendleton made changes -
        Attachment derby_50kRows.log [ 12392100 ]
        Georgi Hristov made changes -
        Attachment Main_Updated.java [ 12392132 ]
        Bryan Pendleton made changes -
        Attachment Main_Updated.java [ 12392134 ]
        Bryan Pendleton made changes -
        Attachment derbyTwoDifferentOneHundredKPlans.log [ 12392135 ]
        Kathey Marsden made changes -
        Derby Categories [Performance] [High Value Fix, Performance]
        Component/s SQL [ 11408 ]
        Dag H. Wanvik made changes -
        Issue & fix info [Existing Application Impact] [High Value Fix]
        Dag H. Wanvik made changes -
        Issue & fix info [High Value Fix] [High Value Fix, Release Note Needed]
        Kristian Waagan made changes -
        Issue & fix info [Release Note Needed, High Value Fix] [High Value Fix, Repro attached, Workaround attached]
        Kathey Marsden made changes -
        Urgency Urgent Normal
        Nirmal Fernando made changes -
        Attachment query_plan_derby_3892.pdf [ 12449361 ]
        Nirmal Fernando made changes -
        Attachment query_plan.jpg [ 12449453 ]
        Kathey Marsden made changes -
        Labels derby_triage10_5_2
        Kathey Marsden made changes -
        Status Open [ 1 ] Resolved [ 5 ]
        Resolution Cannot Reproduce [ 5 ]
        Gavin made changes -
        Workflow jira [ 12443274 ] Default workflow, editable Closed status [ 12802627 ]
        Knut Anders Hatlen made changes -
        Status Resolved [ 5 ] Closed [ 6 ]

          People

          • Assignee:
            Unassigned
            Reporter:
            Georgi Hristov
          • Votes:
            1 Vote for this issue
            Watchers:
            3 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved:

              Development