Manuel Rossetti created DERBY-7041:
--------------------------------------
Summary: 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
Attachments: JSLDb.sql, JSLDb_DriveThroughPharmacy.zip
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)
