Uploaded image for project: 'Derby'
  1. Derby
  2. DERBY-7041

null pointer exception when creating view based on other views

    XMLWordPrintableJSON

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 10.14.2.0
    • Fix Version/s: 10.15.2.0, 10.16.0.0
    • Component/s: SQL
    • Labels:
      None
    • Environment:
      max os x , intellij
    • Issue & fix info:
      Repro attached
    • Bug behavior facts:
      Seen in production

      Description

      I can execute a SELECT query that works but when I try to create a view on that select query, I simply get a java null pointer exception with no details.  I have tested the same statements on a postgres database and they worked without error.

      Below, the PW_DIFF_WITHIN_REP_VIEW create does work, but the PW_DIFF_AR_REP_VIEW does not.  Again, the SELECT clause of PW_DIFF_AR_REP_VIEW will work, but when used within the create clause the error occurs.

      I have attached the entire database creation script and an embedded instance that can be used for testing. It has data.

       

      – WITHIN_REP_VIEW combines the WITHIN_REP_COUNTER_VIEW and WITHIN_REP_RESPONSE_VIEW into one table from which across
      – replication or other statistical summaries by replication can be produced
      CREATE VIEW JSL_DB.WITHIN_REP_VIEW (SIM_RUN_ID_FK, EXP_NAME, ELEMENT_NAME, STAT_NAME, REP_NUM, VALUE) AS
      (SELECT JSL_DB.WITHIN_REP_STAT.SIM_RUN_ID_FK, EXP_NAME, ELEMENT_NAME, STAT_NAME, REP_NUM, AVERAGE AS VALUE
      FROM (JSL_DB.SIMULATION_RUN JOIN JSL_DB.WITHIN_REP_STAT on JSL_DB.SIMULATION_RUN.ID = JSL_DB.WITHIN_REP_STAT.SIM_RUN_ID_FK)
      JOIN JSL_DB.MODEL_ELEMENT ON ELEMENT_ID = JSL_DB.WITHIN_REP_STAT.ELEMENT_ID_FK
      UNION
      SELECT JSL_DB.WITHIN_REP_COUNTER_STAT.SIM_RUN_ID_FK, EXP_NAME, ELEMENT_NAME, STAT_NAME, REP_NUM, LAST_VALUE as VALUE
      FROM (JSL_DB.SIMULATION_RUN JOIN JSL_DB.WITHIN_REP_COUNTER_STAT on JSL_DB.SIMULATION_RUN.ID = JSL_DB.WITHIN_REP_COUNTER_STAT.SIM_RUN_ID_FK)
      JOIN JSL_DB.MODEL_ELEMENT ON ELEMENT_ID = JSL_DB.WITHIN_REP_COUNTER_STAT.ELEMENT_ID_FK);

      – PW_DIFF_WITHIN_REP_VIEW computes the pairwise differences across difference simulation experiments
      – doesn't work for derby, 3-28-2019, works for postgres

      – create view JSL_DB.PW_DIFF_WITHIN_REP_VIEW
      – as (select SIMULATION_RUN.SIM_NAME, A.SIM_RUN_ID_FK AS A_SIM_NUM, A.STAT_NAME, A.EXP_NAME as A_EXP_NAME, A.REP_NUM, A.VALUE as A_VALUE,
      – B.SIM_RUN_ID_FK as B_SIM_NUM, B.EXP_NAME as B_EXP_NAME, B.VALUE as B_VALUE,
      – '(' || A.EXP_NAME || ' - ' || B.EXP_NAME || ')' as DIFF_NAME, (A.VALUE - B.VALUE) as A_MINUS_B
      – from JSL_DB.WITHIN_REP_VIEW as A, JSL_DB.WITHIN_REP_VIEW as B, JSL_DB.SIMULATION_RUN
      – where A.SIM_RUN_ID_FK = JSL_DB.SIMULATION_RUN.ID
      – and A.STAT_NAME = B.STAT_NAME
      – and A.REP_NUM = B.REP_NUM
      – and A.SIM_RUN_ID_FK > B.SIM_RUN_ID_FK
      – and A.ELEMENT_NAME = B.ELEMENT_NAME);

      -- create view JSL_DB.PW_DIFF_AR_REP_VIEW (SIM_NAME, STAT_NAME, A_EXP_NAME, B_EXP_NAME, DIFF_NAME, AVG_A, STD_DEV_A,
      – AVG_B, STD_DEV_B, AVG_DIFF_A_MINUS_B, STD_DEV_DIFF_A_MINUS_B, STAT_COUNT)
      – as (select SIM_NAME, STAT_NAME, A_EXP_NAME, B_EXP_NAME, DIFF_NAME, AVG(A_VALUE) as AVG_A, STDDEV_SAMP(A_VALUE) as STD_DEV_A,
      – AVG(B_VALUE) as AVG_B, STDDEV_SAMP(B_VALUE) as STD_DEV_B,
      – AVG(A_MINUS_B) as AVG_DIFF_A_MINUS_B, STDDEV_SAMP(A_MINUS_B) as STD_DEV_DIFF_A_MINUS_B,
      – COUNT(A_MINUS_B) as STAT_COUNT
      – from JSL_DB.PW_DIFF_WITHIN_REP_VIEW
      – group by SIM_NAME, STAT_NAME, A_EXP_NAME, B_EXP_NAME, DIFF_NAME);

        Attachments

        1. derby-7041-01-aa-omitDependencyOnSystemSuppliedAggregate.diff
          4 kB
          Richard N. Hillegas
        2. derby-7041.sql
          0.6 kB
          Richard N. Hillegas
        3. JSLDb.sql
          13 kB
          Manuel Rossetti
        4. JSLDb_DriveThroughPharmacy.zip
          266 kB
          Manuel Rossetti
        5. JSLDb.sql
          13 kB
          Manuel Rossetti

          Activity

            People

            • Assignee:
              rhillegas Richard N. Hillegas
              Reporter:
              mdrforspam Manuel Rossetti
            • Votes:
              0 Vote for this issue
              Watchers:
              4 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: