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

          Hide
          Mike Matrigali added a comment -

          bug was resolved by DERBY-3944

          Show
          Mike Matrigali added a comment - bug was resolved by DERBY-3944
          Hide
          Kathey Marsden added a comment -

          Reopen for backport

          Show
          Kathey Marsden added a comment - Reopen for backport
          Hide
          Rick Hillegas added a comment -

          This bug appears to have been fixed by some other patch. A regression test to track that fact was included with the patch which closed DERBY-3944.

          Show
          Rick Hillegas added a comment - This bug appears to have been fixed by some other patch. A regression test to track that fact was included with the patch which closed DERBY-3944 .
          Hide
          Kristian Waagan added a comment -

          Triaged July 3, 2009: Assigned normal urgency. Marked as Repro attached.

          Show
          Kristian Waagan added a comment - Triaged July 3, 2009: Assigned normal urgency. Marked as Repro attached.
          Hide
          Rick Hillegas added a comment -

          Interestingly, TRIGGERS don't seem to display this problem. The following script demonstrates correct behavior by TRIGGERS:

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

          drop trigger t1_trig_after_insert_row_trigger;
          drop table t_tsch_1;
          drop function f_tsch_1;
          drop function f_tsch_triggerReports;
          drop procedure p_tsch_report_proc;
          drop procedure p_tsch_clearTriggerReports;

          create function f_tsch_triggerReports()
          returns TABLE
          (
          contents varchar( 100 )
          )
          language java
          parameter style DERBY_JDBC_RESULT_SET
          no sql
          external name 'org.apache.derbyTesting.functionTests.tests.lang.GeneratedColumnsTest.triggerReport'
          ;

          create procedure p_tsch_report_proc
          ( tag varchar( 40 ), a int, b int, c int )
          language java
          parameter style java
          no sql
          external name 'org.apache.derbyTesting.functionTests.tests.lang.GeneratedColumnsTest.showValues'
          ;

          create procedure p_tsch_clearTriggerReports
          ()
          language java
          parameter style java
          no sql
          external name 'org.apache.derbyTesting.functionTests.tests.lang.GeneratedColumnsTest.clearTriggerReports'
          ;

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

          create table t_tsch_1( a int );

          create trigger t1_trig_after_insert_row_trigger
          after insert on t_tsch_1
          referencing new as ar
          for each row
          call p_tsch_report_proc( 'after_insert_row_trigger', f_tsch_1( ar.a ), f_tsch_1( ar.a ), f_tsch_1( ar.a ) )
          ;

          grant execute on function f_tsch_triggerReports to public;
          grant execute on procedure p_tsch_report_proc to public;
          grant execute on procedure p_tsch_clearTriggerReports to public;
          grant select on table t_tsch_1 to public;
          grant insert on table t_tsch_1 to public;

          call test_dbo.p_tsch_clearTriggerReports();
          select * from table( test_dbo.f_tsch_triggerReports() ) s;
          insert into test_dbo.t_tsch_1( a ) values ( 1 );
          select * from table( test_dbo.f_tsch_triggerReports() ) s;

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

          drop function f_tsch_1;

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

          call test_dbo.p_tsch_clearTriggerReports();
          select * from table( test_dbo.f_tsch_triggerReports() ) s;
          insert into test_dbo.t_tsch_1( a ) values ( 1 );
          select * from table( test_dbo.f_tsch_triggerReports() ) s;

          Show
          Rick Hillegas added a comment - Interestingly, TRIGGERS don't seem to display this problem. The following script demonstrates correct behavior by TRIGGERS: connect 'jdbc:derby:derbyauth;create=true;user=test_dbo;password=test_dbopassword' as test_dbo_conn; drop trigger t1_trig_after_insert_row_trigger; drop table t_tsch_1; drop function f_tsch_1; drop function f_tsch_triggerReports; drop procedure p_tsch_report_proc; drop procedure p_tsch_clearTriggerReports; create function f_tsch_triggerReports() returns TABLE ( contents varchar( 100 ) ) language java parameter style DERBY_JDBC_RESULT_SET no sql external name 'org.apache.derbyTesting.functionTests.tests.lang.GeneratedColumnsTest.triggerReport' ; create procedure p_tsch_report_proc ( tag varchar( 40 ), a int, b int, c int ) language java parameter style java no sql external name 'org.apache.derbyTesting.functionTests.tests.lang.GeneratedColumnsTest.showValues' ; create procedure p_tsch_clearTriggerReports () language java parameter style java no sql external name 'org.apache.derbyTesting.functionTests.tests.lang.GeneratedColumnsTest.clearTriggerReports' ; create function f_tsch_1 ( a int ) returns int language java deterministic parameter style java no sql external name 'org.apache.derbyTesting.functionTests.tests.lang.GeneratedColumnsTest.minus' ; create table t_tsch_1( a int ); create trigger t1_trig_after_insert_row_trigger after insert on t_tsch_1 referencing new as ar for each row call p_tsch_report_proc( 'after_insert_row_trigger', f_tsch_1( ar.a ), f_tsch_1( ar.a ), f_tsch_1( ar.a ) ) ; grant execute on function f_tsch_triggerReports to public; grant execute on procedure p_tsch_report_proc to public; grant execute on procedure p_tsch_clearTriggerReports to public; grant select on table t_tsch_1 to public; grant insert on table t_tsch_1 to public; call test_dbo.p_tsch_clearTriggerReports(); select * from table( test_dbo.f_tsch_triggerReports() ) s; insert into test_dbo.t_tsch_1( a ) values ( 1 ); select * from table( test_dbo.f_tsch_triggerReports() ) s; connect 'jdbc:derby:derbyauth;create=true;user=janet;password=janetpassword' as janet_conn; drop function f_tsch_1; create function f_tsch_1 ( a int ) returns int language java deterministic parameter style java no sql external name 'java.lang.Math.abs' ; call test_dbo.p_tsch_clearTriggerReports(); select * from table( test_dbo.f_tsch_triggerReports() ) s; insert into test_dbo.t_tsch_1( a ) values ( 1 ); select * from table( test_dbo.f_tsch_triggerReports() ) s;

            People

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

              Dates

              • Created:
                Updated:
                Resolved:

                Development