Derby
  1. Derby
  2. DERBY-4045

Subquery causes error: 'updateString' not allowed because the ResultSet is not an updatable ResultSet

    Details

    • Type: Bug Bug
    • Status: Closed
    • Priority: Major Major
    • Resolution: Invalid
    • Affects Version/s: None
    • Fix Version/s: None
    • Component/s: SQL
    • Labels:
      None

      Description

      Embedded Derby (don't know about client) demoted an updatable result set apparently due to a nested query.

      select * from orders where (orders.recordno = (select max(orders.recordno) from orders))

      Fix if possible.

      If it can't be fixed, the documentation should be changed.

        Issue Links

          Activity

          Hide
          Knut Anders Hatlen added a comment -

          The reference manual says that subqueries are not allowed in the FROM clause. http://db.apache.org/derby/docs/10.4/ref/rrefsqlj41360.html#rrefsqlj41360__sqlj15384

          But the code doesn't allow subqueries in the WHERE clause either. See SelectNode.isUpdatableCursor() which contains this code:

          if ((getWhereSubquerys() != null) &&
          (getWhereSubquerys().size() != 0))

          { if (SanityManager.DEBUG) SanityManager.DEBUG("DumpUpdateCheck","cursor select has subquery in WHERE clause"); return false; }

          I'm not sure if just removing this check would actually make the query work, though, or if it would work for all queries with a subquery in the WHERE clause. Anyway, if we don't allow subqueries in WHERE clauses, the documentation should say so.

          Show
          Knut Anders Hatlen added a comment - The reference manual says that subqueries are not allowed in the FROM clause. http://db.apache.org/derby/docs/10.4/ref/rrefsqlj41360.html#rrefsqlj41360__sqlj15384 But the code doesn't allow subqueries in the WHERE clause either. See SelectNode.isUpdatableCursor() which contains this code: if ((getWhereSubquerys() != null) && (getWhereSubquerys().size() != 0)) { if (SanityManager.DEBUG) SanityManager.DEBUG("DumpUpdateCheck","cursor select has subquery in WHERE clause"); return false; } I'm not sure if just removing this check would actually make the query work, though, or if it would work for all queries with a subquery in the WHERE clause. Anyway, if we don't allow subqueries in WHERE clauses, the documentation should say so.
          Show
          Knut Anders Hatlen added a comment - Link to discussion on derby-user: http://mail-archives.apache.org/mod_mbox/db-derby-user/200902.mbox/%3c200902012122.n11LMfvg099685@web2.nidhog.com%3e
          Hide
          John T. Dow added a comment -

          I read that, but thought what I was doing should have been ok.

          Thanks for being on top of all this.

          John

          Show
          John T. Dow added a comment - I read that, but thought what I was doing should have been ok. Thanks for being on top of all this. John
          Hide
          Kim Haase added a comment -

          Seems as if this is either a documentation issue or an SQL issue but not both – if the SQL can be fixed there is no need to change the doc?

          If it is a doc issue, would it be sufficient to add another bullet saying the following?

          • If the underlying Query has a WHERE clause, the WHERE clause must not have subqueries

          I'll pick this up if the consensus is that we should fix the doc rather than the SQL.

          Show
          Kim Haase added a comment - Seems as if this is either a documentation issue or an SQL issue but not both – if the SQL can be fixed there is no need to change the doc? If it is a doc issue, would it be sufficient to add another bullet saying the following? If the underlying Query has a WHERE clause, the WHERE clause must not have subqueries I'll pick this up if the consensus is that we should fix the doc rather than the SQL.
          Hide
          John T. Dow added a comment -

          I vote for fixing the SQL because WHERE SELECT MAX is how one finds the most recently entered row in a table, which I find I want to do quite often. And I want to update it once I get it.

          Show
          John T. Dow added a comment - I vote for fixing the SQL because WHERE SELECT MAX is how one finds the most recently entered row in a table, which I find I want to do quite often. And I want to update it once I get it.
          Hide
          Knut Anders Hatlen added a comment -

          I tried to remove the check, and the query in the description of the issue worked fine, and I could use positioned updates on the cursor. However, with other queries it failed when I tried to do the positioned update:

          ij> get cursor c as 'select * from t1 where x in (select x from t1) for update';
          ij> next c;
          X |Y
          ----------------------
          1 |one
          ij> next c;
          X |Y
          ----------------------
          2 |two
          ij> update t1 set y = 'asdfa' where current of c;
          ERROR XJ001: Java exception: 'ASSERT FAILED Join used in positioned update/delete: org.apache.derby.shared.common.sanity.AssertFailure'.

          So the problem with subqueries in WHERE clauses appears to be that the optimizer may choose to rewrite the query to a join, and Derby doesn't have updatable joins.

          Show
          Knut Anders Hatlen added a comment - I tried to remove the check, and the query in the description of the issue worked fine, and I could use positioned updates on the cursor. However, with other queries it failed when I tried to do the positioned update: ij> get cursor c as 'select * from t1 where x in (select x from t1) for update'; ij> next c; X |Y ---------------------- 1 |one ij> next c; X |Y ---------------------- 2 |two ij> update t1 set y = 'asdfa' where current of c; ERROR XJ001: Java exception: 'ASSERT FAILED Join used in positioned update/delete: org.apache.derby.shared.common.sanity.AssertFailure'. So the problem with subqueries in WHERE clauses appears to be that the optimizer may choose to rewrite the query to a join, and Derby doesn't have updatable joins.
          Hide
          Knut Anders Hatlen added a comment -

          Kim, I think the doc will have to be changed anyway, since a fix of the SQL will probably not allow all kinds of subqueries in WHERE clauses. So for now it would be fine to update the doc with a bullet saying what you suggested above. If we change the SQL later, the sentence could be changed to specify which kinds of subqueries we don't allow. We could perhaps change the documentation in a separate issue and leave this one open to track the request for the SQL change.

          Show
          Knut Anders Hatlen added a comment - Kim, I think the doc will have to be changed anyway, since a fix of the SQL will probably not allow all kinds of subqueries in WHERE clauses. So for now it would be fine to update the doc with a bullet saying what you suggested above. If we change the SQL later, the sentence could be changed to specify which kinds of subqueries we don't allow. We could perhaps change the documentation in a separate issue and leave this one open to track the request for the SQL change.
          Hide
          Kim Haase added a comment -

          Thanks for the suggestion, Knut Anders. I've created DERBY-4049 for the documentation issue.

          Show
          Kim Haase added a comment - Thanks for the suggestion, Knut Anders. I've created DERBY-4049 for the documentation issue.
          Hide
          Kim Haase added a comment -

          Removing (I hope) Documentation from affected components because DERBY-4049 has been created for the documentation part of the issue.

          Show
          Kim Haase added a comment - Removing (I hope) Documentation from affected components because DERBY-4049 has been created for the documentation part of the issue.
          Hide
          Dag H. Wanvik added a comment -

          Since this limitation has now been documented, I intend to resolve this as a "not a bug" in a day or two.
          Feel free to file an improvement request for this particular query form, since Knut has established that it might work.

          Show
          Dag H. Wanvik added a comment - Since this limitation has now been documented, I intend to resolve this as a "not a bug" in a day or two. Feel free to file an improvement request for this particular query form, since Knut has established that it might work.
          Hide
          Dag H. Wanvik added a comment -

          Removing "wrong query result" flag because the case here is a limitation, not wrong data returned from a query.

          Show
          Dag H. Wanvik added a comment - Removing "wrong query result" flag because the case here is a limitation, not wrong data returned from a query.
          Hide
          Knut Anders Hatlen added a comment -

          Triaged for 10.5.2. Marking the bug as invalid, as suggested by Dag, since it is now a documented limitation. Feel free to open a new improvement issue to allow these queries.

          Show
          Knut Anders Hatlen added a comment - Triaged for 10.5.2. Marking the bug as invalid, as suggested by Dag, since it is now a documented limitation. Feel free to open a new improvement issue to allow these queries.

            People

            • Assignee:
              Unassigned
              Reporter:
              John T. Dow
            • Votes:
              0 Vote for this issue
              Watchers:
              1 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved:

                Development