[
https://issues.apache.org/jira/browse/DERBY-7041?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Rick Hillegas reassigned DERBY-7041:
------------------------------------
Assignee: Rick Hillegas
> null pointer exception when creating view based on other views
> --------------------------------------------------------------
>
> Key: DERBY-7041
> URL: https://issues.apache.org/jira/browse/DERBY-7041
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.14.2.0
> Environment: max os x , intellij
> Reporter: Manuel Rossetti
> Assignee: Rick Hillegas
> Priority: Major
> Fix For: 10.15.1.4
>
> Attachments: JSLDb.sql, JSLDb.sql, JSLDb_DriveThroughPharmacy.zip,
> derby-7041-01-aa-omitDependencyOnSystemSuppliedAggregate.diff, derby-7041.sql
>
>
> 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);
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
