Derby
  1. Derby
  2. DERBY-4411

Scalar subquery erroneously rejected for not returning exactly one row

    Details

    • Type: Bug Bug
    • Status: Closed
    • Priority: Major Major
    • Resolution: Fixed
    • Affects Version/s: 10.5.3.1, 10.6.1.0
    • Fix Version/s: 10.5.3.1, 10.6.1.0
    • Component/s: SQL
    • Labels:
      None
    • Issue & fix info:
      Repro attached

      Description

      This sequence of statements causes an error because the scalar sub-query supposedly doesn't return exactly one row, even though it does return exactly one row:

      ij> create table t (x int unique, y int);
      0 rows inserted/updated/deleted
      ij> insert into t values (1,2);
      1 row inserted/updated/deleted
      ij> select * from t where x > (values 1 union select y from t where 1=0);
      ERROR 21000: Scalar subquery is only allowed to return a single row.
      ij> values 1 union select y from t where 1=0;
      1
      -----------
      1

      1 row selected

      Before DERBY-4391, the problematic statement would have caused a NullPointerException.

      1. constantRestriction.diff
        0.6 kB
        Knut Anders Hatlen
      2. derby-4411-1a.diff
        3 kB
        Knut Anders Hatlen

        Issue Links

          Activity

          Hide
          Knut Anders Hatlen added a comment -

          Merged fix to 10.5 and committed revision 830457. Closing the issue.

          Show
          Knut Anders Hatlen added a comment - Merged fix to 10.5 and committed revision 830457. Closing the issue.
          Hide
          Knut Anders Hatlen added a comment -

          Committed revision 829022.

          I'll leave the issue open until the fix has been back-ported to 10.5.

          Show
          Knut Anders Hatlen added a comment - Committed revision 829022. I'll leave the issue open until the fix has been back-ported to 10.5.
          Hide
          Knut Anders Hatlen added a comment -

          Adding 10.5.3.1 to affects versions since DERBY-4391 was back-ported, and the bug is now reproducible there. The bug is also present in earlier versions, but DERBY-4391 hides it by throwing a NullPointerException when running the repro provided in this issue.

          Show
          Knut Anders Hatlen added a comment - Adding 10.5.3.1 to affects versions since DERBY-4391 was back-ported, and the bug is now reproducible there. The bug is also present in earlier versions, but DERBY-4391 hides it by throwing a NullPointerException when running the repro provided in this issue.
          Hide
          Knut Anders Hatlen added a comment -

          Here's a new patch with a regression test case added. All the existing regression tests ran cleanly.

          Show
          Knut Anders Hatlen added a comment - Here's a new patch with a regression test case added. All the existing regression tests ran cleanly.
          Hide
          Knut Anders Hatlen added a comment -

          When the problematic query is compiled, the byte code for the UnionNode is generated twice. I'm not sure why it's needed twice in this case, but it looks like the byte code generation in general is prepared for it, so I assume it's OK for now.

          The right side of the UnionNode contains a ProjectRestrictNode with the predicate 1=0. The first time PRN.generate() is called, predicates are taken out from restrictionList and put into restriction or constantRestriction, depending on the nature of the predicate. restrictionList is nulled out. The second time PRN.generate() is called on the same object, the predicates are supposed to be taken from the restriction and constantRestriction fields instead of the, so all the predicates should be preserved between the invocations.

          However, PRN has a method nopProjectRestrict() which checks whether the PRN actually restricts the result. This method checks if there are predicates restrictionList and restriction. But since 1=0 is a constant expression and has been moved to constantRestriction, the method doesn't detect that the PRN actually does restrict the result, and the code to restrict the result is not generated.

          The attached patch makes nopProjectRestrict() check the constant restrictions as well. This makes the query return the expected result. The patch does not add any regression tests, nor has any regression tests been run with the patch.

          Show
          Knut Anders Hatlen added a comment - When the problematic query is compiled, the byte code for the UnionNode is generated twice. I'm not sure why it's needed twice in this case, but it looks like the byte code generation in general is prepared for it, so I assume it's OK for now. The right side of the UnionNode contains a ProjectRestrictNode with the predicate 1=0. The first time PRN.generate() is called, predicates are taken out from restrictionList and put into restriction or constantRestriction, depending on the nature of the predicate. restrictionList is nulled out. The second time PRN.generate() is called on the same object, the predicates are supposed to be taken from the restriction and constantRestriction fields instead of the, so all the predicates should be preserved between the invocations. However, PRN has a method nopProjectRestrict() which checks whether the PRN actually restricts the result. This method checks if there are predicates restrictionList and restriction. But since 1=0 is a constant expression and has been moved to constantRestriction, the method doesn't detect that the PRN actually does restrict the result, and the code to restrict the result is not generated. The attached patch makes nopProjectRestrict() check the constant restrictions as well. This makes the query return the expected result. The patch does not add any regression tests, nor has any regression tests been run with the patch.

            People

            • Assignee:
              Knut Anders Hatlen
              Reporter:
              Knut Anders Hatlen
            • Votes:
              0 Vote for this issue
              Watchers:
              0 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved:

                Development