[jira] [Commented] (DERBY-7041) null pointer exception when creating view based on other views

[Rick Hillegas (JIRA)]

[ 
https://issues.apache.org/jira/browse/DERBY-7041?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16805881#comment-16805881
 ] 

Rick Hillegas commented on DERBY-7041:
--

Attaching derby-7041.sql, a simpler script for the problem.

> 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
>Priority: Major
> Attachments: JSLDb.sql, JSLDb.sql, JSLDb_DriveThroughPharmacy.zip, 
> 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)

[jira] [Updated] (DERBY-7041) null pointer exception when creating view based on other views

[Rick Hillegas (JIRA)]

 [ 
https://issues.apache.org/jira/browse/DERBY-7041?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
 ]

Rick Hillegas updated DERBY-7041:
-
Attachment: derby-7041.sql

> 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
>Priority: Major
> Attachments: JSLDb.sql, JSLDb.sql, JSLDb_DriveThroughPharmacy.zip, 
> 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)