[ https://issues.apache.org/jira/browse/DERBY-7041?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16808221#comment-16808221 ]
Rick Hillegas commented on DERBY-7041: -------------------------------------- I can port the patch to the 10.14 branch if that helps you. But you are correct, I have no plans to manage a 10.14.2 release at this time. > 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-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)