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

null pointer exception when creating view based on other views

Attach filesAttach ScreenshotVotersWatch issueWatchersCreate sub-taskLinkCloneUpdate Comment AuthorReplace String in CommentUpdate Comment VisibilityDelete Comments
    XMLWordPrintableJSON

Details

    • Bug
    • Status: Closed
    • Major
    • Resolution: Fixed
    • 10.14.2.0
    • 10.15.2.0, 10.16.1.1
    • SQL
    • None
    • max os x , intellij
    • Repro attached
    • 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.sql
          0.6 kB
          Richard N. Hillegas
        2. derby-7041-01-aa-omitDependencyOnSystemSuppliedAggregate.diff
          4 kB
          Richard N. Hillegas
        3. JSLDb_DriveThroughPharmacy.zip
          266 kB
          Manuel Rossetti
        4. JSLDb.sql
          13 kB
          Manuel Rossetti
        5. JSLDb.sql
          13 kB
          Manuel Rossetti

        Activity

          This comment will be Viewable by All Users Viewable by All Users
          Cancel

          People

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

            Dates

              Created:
              Updated:
              Resolved:

              Slack

                Issue deployment