Derby
  1. Derby
  2. DERBY-5005

Error when fully qualifying a field from a view in an ORDER BY clause

    Details

    • Type: Bug Bug
    • Status: Closed
    • Priority: Minor Minor
    • Resolution: Fixed
    • Affects Version/s: 10.0.2.0, 10.0.2.1, 10.1.1.0, 10.1.2.1, 10.1.3.1, 10.2.1.6, 10.2.2.0, 10.3.1.4, 10.3.2.1, 10.3.3.0, 10.4.1.3, 10.4.2.0, 10.5.1.1, 10.5.2.0, 10.5.3.0, 10.6.1.0, 10.6.2.1, 10.7.1.1
    • Fix Version/s: 10.5.3.2, 10.6.2.4, 10.7.1.4, 10.8.1.2
    • Component/s: SQL
    • Labels:
    • Environment:
      Windows 7
    • Urgency:
      Normal
    • Issue & fix info:
      Repro attached, Workaround attached
    • Bug behavior facts:
      Deviation from standard

      Description

      I have a strange issue that can be reproduced easily with the following objects in schema "test":

      create table a (a integer);
      insert into a (a) values(1);
      create view v as select * from a;

      This works:
      select test.a.a from test.a where test.a.a <> 2 order by test.a.a asc;

      This doesn't work:
      select test.v.a from test.v where test.v.a <> 2 order by test.v.a asc;

      But this does:
      select test.v.a from test.v where test.v.a <> 2 order by v.a asc;

      This is the error I get:
      Error: 'TEST.V' is not an exposed table name in the scope in which it appears.
      SQLState: 42X10
      ErrorCode: -1

      I've tried quite a few SELECT clauses, and I think the ORDER BY clause is the only one having this issue.

      1. derby-5005_10_5_diff.txt
        2 kB
        Kathey Marsden
      2. derby-5005b.stat
        0.5 kB
        Dag H. Wanvik
      3. derby-5005b.diff
        7 kB
        Dag H. Wanvik
      4. derby-5005.stat
        0.5 kB
        Dag H. Wanvik
      5. derby-5005.diff
        8 kB
        Dag H. Wanvik
      6. 5005.sql
        0.4 kB
        Rick Hillegas

        Activity

        Hide
        Rick Hillegas added a comment -

        Attaching 5005.sql, a script for the problem which Lukas found. I can verify that this fails for me too.

        Show
        Rick Hillegas added a comment - Attaching 5005.sql, a script for the problem which Lukas found. I can verify that this fails for me too.
        Hide
        Dag H. Wanvik added a comment -

        Uploading a patch that makes the query work. It makes FromSubquery
        implement its own getFromTableByName instead of using the abstract
        superclass FromTable's implementation which returns null when an
        explicit schema is used (a view is represented a FromSubquery here),
        cf. the comment in the default implementation:

        "Only FromBaseTables have schema names"

        which isn't quite true; views can have them, too.

        Added a new test, lang.Derby5005Test instead of adding to the harness test
        orderby.sql. Made a note in orderby.sql to merge with Derby5005Test
        when that test gets rewritten to JUnit.

        Running regressions.

        Show
        Dag H. Wanvik added a comment - Uploading a patch that makes the query work. It makes FromSubquery implement its own getFromTableByName instead of using the abstract superclass FromTable's implementation which returns null when an explicit schema is used (a view is represented a FromSubquery here), cf. the comment in the default implementation: "Only FromBaseTables have schema names" which isn't quite true; views can have them, too. Added a new test, lang.Derby5005Test instead of adding to the harness test orderby.sql. Made a note in orderby.sql to merge with Derby5005Test when that test gets rewritten to JUnit. Running regressions.
        Hide
        Dag H. Wanvik added a comment -

        Regressions ran OK, please review.

        Show
        Dag H. Wanvik added a comment - Regressions ran OK, please review.
        Hide
        Rick Hillegas added a comment -

        Thanks for the patch, Dag. It looks good to me. I have verified that 5005.sql now behaves as expected. +1

        Show
        Rick Hillegas added a comment - Thanks for the patch, Dag. It looks good to me. I have verified that 5005.sql now behaves as expected. +1
        Hide
        Lukas Eder added a comment -

        Excellent guys! That was really quick!

        Show
        Lukas Eder added a comment - Excellent guys! That was really quick!
        Hide
        Dag H. Wanvik added a comment -

        Uploaded derby-5005b, which just cleans up the first version a bit. Committed as svn 1069661, resolving.

        Show
        Dag H. Wanvik added a comment - Uploaded derby-5005b, which just cleans up the first version a bit. Committed as svn 1069661, resolving.
        Hide
        Dag H. Wanvik added a comment -

        Resolving this issue. If you are OK with the fix, please close the Lukas. Thanks!

        Show
        Dag H. Wanvik added a comment - Resolving this issue. If you are OK with the fix, please close the Lukas. Thanks!
        Hide
        Dag H. Wanvik added a comment -

        Should be easy to backport.

        Show
        Dag H. Wanvik added a comment - Should be easy to backport.
        Hide
        Lukas Eder added a comment -

        Should I verify the correctness of the fix before closing it? I'd have to wait for a release (10.8, I guess) before I could close it. What's your preferred process?

        Show
        Lukas Eder added a comment - Should I verify the correctness of the fix before closing it? I'd have to wait for a release (10.8, I guess) before I could close it. What's your preferred process?
        Hide
        Rick Hillegas added a comment -

        Hi Lukas,

        It's fine to verify the fix using the trunk. But that requires that you set up a development environment. It's also ok to wait until 10.8 to verify the fix. From a process point of view, it doesn't matter much: resolving the feature means that our JIRA filters will sweep this issue into the release notes for 10.8. Thanks.

        Show
        Rick Hillegas added a comment - Hi Lukas, It's fine to verify the fix using the trunk. But that requires that you set up a development environment. It's also ok to wait until 10.8 to verify the fix. From a process point of view, it doesn't matter much: resolving the feature means that our JIRA filters will sweep this issue into the release notes for 10.8. Thanks.
        Hide
        Kathey Marsden added a comment -

        For 10.5 there was a slight manual merge. Attached is the patch derby-5005_10_5_diff.txt

        Show
        Kathey Marsden added a comment - For 10.5 there was a slight manual merge. Attached is the patch derby-5005_10_5_diff.txt
        Hide
        Kathey Marsden added a comment -

        Running Suites.All the 10.5 patch I see this failure. I don't think it is related to the change and don't immediately see an issue filed but will take a closer look
        1) SecureServerTest( Opened = false, Authenticated= true, CustomDerbyProperties=
        null, WildCardHost= 0.00.000.0 )junit.framework.AssertionFailedError: SecureSer
        verTest( Opened = false, Authenticated= true, CustomDerbyProperties= null, WildC
        ardHost= 0.00.000.0 )
        Expected: Security manager installed using the Basic server security policy.
        But saw:
        at org.apache.derbyTesting.functionTests.tests.derbynet.SecureServerTest
        .testServerStartup(SecureServerTest.java:345)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.
        java:60)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAcces
        sorImpl.java:37)
        at org.apache.derbyTesting.junit.BaseTestCase.runBare(BaseTestCase.java:
        109)
        at junit.extensions.TestDecorator.basicRun(TestDecorator.java:22)
        at junit.extensions.TestSetup$1.protect(TestSetup.java:19)
        at junit.extensions.TestSetup.run(TestSetup.java:23)
        at org.apache.derbyTesting.junit.BaseTestSetup.run(BaseTestSetup.java:57
        )
        at junit.extensions.TestDecorator.basicRun(TestDecorator.java:22)
        at junit.extensions.TestSetup$1.protect(TestSetup.java:19)
        at junit.extensions.TestSetup.run(TestSetup.java:23)
        at junit.extensions.TestDecorator.basicRun(TestDecorator.java:22)
        at junit.extensions.TestSetup$1.protect(TestSetup.java:19)
        at junit.extensions.TestSetup.run(TestSetup.java:23)
        at junit.extensions.TestDecorator.basicRun(TestDecorator.java:22)
        at junit.extensions.TestSetup$1.protect(TestSetup.java:19)
        at junit.extensions.TestSetup.run(TestSetup.java:23)

        FAILURES!!!
        Tests run: 11318, Failures: 1, Errors: 1

        jvm is:
        $ java -version
        java version "1.6.0"
        Java(TM) SE Runtime Environment (build pwi3260sr9-20101125_01(SR9))
        IBM J9 VM (build 2.4, JRE 1.6.0 IBM J9 2.4 Windows XP x86-32 jvmwi3260sr9-201011
        24_69295 (JIT enabled, AOT enabled)
        J9VM - 20101124_069295
        JIT - r9_20101028_17488ifx2
        GC - 20101027_AA)
        JCL - 20101119_01

        Show
        Kathey Marsden added a comment - Running Suites.All the 10.5 patch I see this failure. I don't think it is related to the change and don't immediately see an issue filed but will take a closer look 1) SecureServerTest( Opened = false, Authenticated= true, CustomDerbyProperties= null, WildCardHost= 0.00.000.0 )junit.framework.AssertionFailedError: SecureSer verTest( Opened = false, Authenticated= true, CustomDerbyProperties= null, WildC ardHost= 0.00.000.0 ) Expected: Security manager installed using the Basic server security policy. But saw: at org.apache.derbyTesting.functionTests.tests.derbynet.SecureServerTest .testServerStartup(SecureServerTest.java:345) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl. java:60) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAcces sorImpl.java:37) at org.apache.derbyTesting.junit.BaseTestCase.runBare(BaseTestCase.java: 109) at junit.extensions.TestDecorator.basicRun(TestDecorator.java:22) at junit.extensions.TestSetup$1.protect(TestSetup.java:19) at junit.extensions.TestSetup.run(TestSetup.java:23) at org.apache.derbyTesting.junit.BaseTestSetup.run(BaseTestSetup.java:57 ) at junit.extensions.TestDecorator.basicRun(TestDecorator.java:22) at junit.extensions.TestSetup$1.protect(TestSetup.java:19) at junit.extensions.TestSetup.run(TestSetup.java:23) at junit.extensions.TestDecorator.basicRun(TestDecorator.java:22) at junit.extensions.TestSetup$1.protect(TestSetup.java:19) at junit.extensions.TestSetup.run(TestSetup.java:23) at junit.extensions.TestDecorator.basicRun(TestDecorator.java:22) at junit.extensions.TestSetup$1.protect(TestSetup.java:19) at junit.extensions.TestSetup.run(TestSetup.java:23) FAILURES!!! Tests run: 11318, Failures: 1, Errors: 1 jvm is: $ java -version java version "1.6.0" Java(TM) SE Runtime Environment (build pwi3260sr9-20101125_01(SR9)) IBM J9 VM (build 2.4, JRE 1.6.0 IBM J9 2.4 Windows XP x86-32 jvmwi3260sr9-201011 24_69295 (JIT enabled, AOT enabled) J9VM - 20101124_069295 JIT - r9_20101028_17488ifx2 GC - 20101027_AA) JCL - 20101119_01
        Hide
        Kathey Marsden added a comment -

        finished backporting to 10.5

        Show
        Kathey Marsden added a comment - finished backporting to 10.5
        Hide
        Lukas Eder added a comment -

        Works for me in 10.8. Thanks guys

        Show
        Lukas Eder added a comment - Works for me in 10.8. Thanks guys

          People

          • Assignee:
            Dag H. Wanvik
            Reporter:
            Lukas Eder
          • Votes:
            0 Vote for this issue
            Watchers:
            0 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved:

              Development