Derby
  1. Derby
  2. DERBY-3953

VIEWS which invoke user-coded functions may return different results depending on who SELECTs from them

    Details

    • Type: Bug Bug
    • Status: Closed
    • Priority: Major Major
    • Resolution: Fixed
    • Affects Version/s: 10.4.2.0, 10.5.1.1
    • Fix Version/s: 10.5.3.2, 10.6.2.4, 10.7.1.1
    • Component/s: SQL
    • Labels:
      None
    • Urgency:
      Normal
    • Issue & fix info:
      Repro attached
    • Bug behavior facts:
      Wrong query result

      Description

      This issue is similar to DERBY-3944 (CHECK constraints). The correct behavior seems to governed by the same clause of the SQL standard quoted on that issue. As with CHECK constraints, unqualified function names in VIEWs are being incorrectly resolved to the current schema at DML time rather than to the current schema at DDL time. The following script shows this problem:

      connect 'jdbc:derby:derbyauth;create=true;user=test_dbo;password=test_dbopassword' as test_dbo_conn;

      drop view v_fsch_1;
      drop function f_fsch_1;

      create function f_fsch_1
      (
      a int
      )
      returns int
      language java
      deterministic
      parameter style java
      no sql
      external name 'org.apache.derbyTesting.functionTests.tests.lang.GeneratedColumnsTest.minus'
      ;

      create view v_fsch_1( a )
      as values ( f_fsch_1( 1 ) ), ( f_fsch_1( 2 ) );

      grant select on v_fsch_1 to public;

      select * from test_dbo.v_fsch_1;

      connect 'jdbc:derby:derbyauth;create=true;user=janet;password=janetpassword' as janet_conn;

      drop function f_fsch_1;

      create function f_fsch_1
      (
      a int
      )
      returns int
      language java
      deterministic
      parameter style java
      no sql
      external name 'java.lang.Math.abs'
      ;


      -- returns different results than those seen by user test_dbo

      select * from test_dbo.v_fsch_1;

        Issue Links

          Activity

            People

            • Assignee:
              Rick Hillegas
              Reporter:
              Rick Hillegas
            • Votes:
              0 Vote for this issue
              Watchers:
              0 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved:

                Development