[
https://issues.apache.org/jira/browse/DERBY-7041?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16806178#comment-16806178
]
Rick Hillegas commented on DERBY-7041:
--------------------------------------
The stack trace shows that the CREATE VIEW statement incurs an NPE while trying
to record a persistent dependency of the view on the STDDEV_SAMP aggregate:
{noformat}
java.lang.NullPointerException
at java.base/java.util.Hashtable.put(Hashtable.java:479)
at
org.apache.derby.iapi.sql.depend.ProviderList.addProvider(ProviderList.java:42)
at
org.apache.derby.impl.sql.compile.CompilerContextImpl.addProviderToAuxiliaryList(CompilerContextImpl.java:319)
at
org.apache.derby.impl.sql.compile.CompilerContextImpl.createDependency(CompilerContextImpl.java:290)
at
org.apache.derby.impl.sql.compile.AggregateNode.bindExpression(AggregateNode.java:404)
at
org.apache.derby.impl.sql.compile.JavaToSQLValueNode.bindExpression(JavaToSQLValueNode.java:237)
at
org.apache.derby.impl.sql.compile.ResultColumn.bindExpression(ResultColumn.java:759)
at
org.apache.derby.impl.sql.compile.ResultColumnList.bindExpressions(ResultColumnList.java:839)
at
org.apache.derby.impl.sql.compile.SelectNode.bindExpressions(SelectNode.java:575)
at
org.apache.derby.impl.sql.compile.CreateViewNode.bindViewDefinition(CreateViewNode.java:255)
at
org.apache.derby.impl.sql.compile.CreateViewNode.bindStatement(CreateViewNode.java:175)
at
org.apache.derby.impl.sql.GenericStatement.prepMinion(GenericStatement.java:401)
at
org.apache.derby.impl.sql.GenericStatement.prepare(GenericStatement.java:99)
at
org.apache.derby.impl.sql.conn.GenericLanguageConnectionContext.prepareInternalStatement(GenericLanguageConnectionContext.java:1114)
at
org.apache.derby.impl.jdbc.EmbedStatement.execute(EmbedStatement.java:689)
at
org.apache.derby.impl.jdbc.EmbedStatement.execute(EmbedStatement.java:637)
at org.apache.derby.impl.tools.ij.ij.executeImmediate(ij.java:372)
at org.apache.derby.impl.tools.ij.utilMain.doCatch(utilMain.java:534)
at
org.apache.derby.impl.tools.ij.utilMain.runScriptGuts(utilMain.java:375)
at org.apache.derby.impl.tools.ij.utilMain.go(utilMain.java:251)
at org.apache.derby.impl.tools.ij.Main.go(Main.java:229)
at org.apache.derby.impl.tools.ij.Main.mainCore(Main.java:184)
at org.apache.derby.impl.tools.ij.Main.main(Main.java:75)
at org.apache.derby.tools.ij.main(ij.java:59)
{noformat}
This happens because we implemented the statistical aggregates as user defined
aggregates and because the aggregate descriptor has a NULL object ID.
Two things look odd here and could serve as the basis for a fix:
* There should be no need to declare a persistent dependency between a
user-defined view and a system-supplied aggregate.
* The aggregate descriptor, nonetheless, should have a real object ID.
> 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)
