[
https://issues.apache.org/jira/browse/DERBY-7041?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16804427#comment-16804427
]
Rick Hillegas commented on DERBY-7041:
--------------------------------------
I'm afraid that the attached script raises other compile-time errors for me and
it does not fail on an NPE. Here is the output from running the script as
provided with the problem CREATE statement uncommented:
{noformat}
ij> connect 'jdbc:derby:db;create=true';
WARNING 01J01: Database 'db' not created, connection made to existing database
instead.
ij> CREATE SCHEMA JSL_DB;
0 rows inserted/updated/deleted
ij> -- SIMULATION_RUN captures the execution of a simulation experiment and its
related options
CREATE TABLE JSL_DB.SIMULATION_RUN (
ID INTEGER NOT NULL PRIMARY KEY GENERATED ALWAYS AS IDENTITY (START
WITH 1 INCREMENT BY 1),
SIM_NAME VARCHAR(510) NOT NULL,
MODEL_NAME VARCHAR(510) NOT NULL,
EXP_NAME VARCHAR(510) NOT NULL,
EXP_START_TIME_STAMP TIMESTAMP,
EXP_END_TIME_STAMP TIMESTAMP,
NUM_REPS INTEGER NOT NULL CHECK (NUM_REPS >=1),
LAST_REP INTEGER,
LENGTH_OF_REP DOUBLE PRECISION,
LENGTH_OF_WARM_UP DOUBLE PRECISION,
HAS_MORE_REPS BOOLEAN,
REP_ALLOWED_EXEC_TIME BIGINT,
REP_INIT_OPTION BOOLEAN,
RESET_START_STREAM_OPTION BOOLEAN,
ANTITHETIC_OPTION BOOLEAN,
ADV_NEXT_SUB_STREAM_OPTION BOOLEAN,
NUM_STREAM_ADVANCES INTEGER
);
0 rows inserted/updated/deleted
ij> ALTER TABLE JSL_DB.SIMULATION_RUN
ADD CONSTRAINT SR_NAME_EXP_UNIQUE UNIQUE (SIM_NAME, EXP_NAME);
0 rows inserted/updated/deleted
ij> -- MODEL_ELEMENT represents the model element hierarchy associated with
various
-- simulation runs, i.e. the model elements in the model and their parent/child
-- relationship. LEFT_COUNT and RIGHT_COUNT uses Joe Celko's SQL for Smarties
-- Advanced SQL Programming Chapter 36 to implement the nested set model for
-- the hierarchy. This allows statistics associated with hierarchical
aggregations
-- and subtrees of the model element hierarchy to be more easily queried.
CREATE TABLE JSL_DB.MODEL_ELEMENT (
SIM_RUN_ID_FK INTEGER NOT NULL,
ELEMENT_ID INTEGER NOT NULL,
ELEMENT_NAME VARCHAR(510) NOT NULL,
CLASS_NAME VARCHAR(510) NOT NULL,
PARENT_ID_FK INTEGER,
PARENT_NAME VARCHAR(510),
LEFT_COUNT INTEGER NOT NULL CHECK (LEFT_COUNT > 0),
RIGHT_COUNT INTEGER NOT NULL CHECK (RIGHT_COUNT > 1),
CONSTRAINT TRAVERSAL_ORDER_OKAY CHECK (LEFT_COUNT < RIGHT_COUNT)
);
0 rows inserted/updated/deleted
ij> ALTER TABLE JSL_DB.MODEL_ELEMENT
ADD CONSTRAINT ME_PRIM_KY PRIMARY KEY (SIM_RUN_ID_FK, ELEMENT_ID);
0 rows inserted/updated/deleted
ij> ALTER TABLE JSL_DB.MODEL_ELEMENT
ADD CONSTRAINT ME_NAME_UNIQUE UNIQUE (SIM_RUN_ID_FK, ELEMENT_NAME);
0 rows inserted/updated/deleted
ij> ALTER TABLE JSL_DB.MODEL_ELEMENT
ADD CONSTRAINT ME_SIMRUN_FK FOREIGN KEY (SIM_RUN_ID_FK) REFERENCES
JSL_DB.SIMULATION_RUN (ID) ON DELETE CASCADE;
0 rows inserted/updated/deleted
ij> CREATE INDEX ME_SIMRUN_FK_INDEX ON JSL_DB.MODEL_ELEMENT(SIM_RUN_ID_FK);
0 rows inserted/updated/deleted
WARNING 01504: 'ME_SIMRUN_FK_INDEX' index not created because it is a duplicate
of an existing index: 'SQL0000000007-a666c073-0169-c691-93ab-000007295718'.
ij> -- WITHIN_REP_STAT represents within replication statistics for each
replication of
-- each simulation for each response
CREATE TABLE JSL_DB.WITHIN_REP_STAT (
ID INTEGER NOT NULL PRIMARY KEY GENERATED ALWAYS AS IDENTITY (START
WITH 1 INCREMENT BY 1),
ELEMENT_ID_FK INTEGER NOT NULL,
SIM_RUN_ID_FK INTEGER NOT NULL,
REP_NUM INTEGER NOT NULL CHECK (REP_NUM >=1),
STAT_NAME VARCHAR(510),
STAT_COUNT DOUBLE PRECISION CHECK (STAT_COUNT >=0),
AVERAGE DOUBLE PRECISION,
MINIMUM DOUBLE PRECISION,
MAXIMUM DOUBLE PRECISION,
WEIGHTED_SUM DOUBLE PRECISION,
SUM_OF_WEIGHTS DOUBLE PRECISION,
WEIGHTED_SSQ DOUBLE PRECISION,
LAST_VALUE DOUBLE PRECISION,
LAST_WEIGHT DOUBLE PRECISION
);
0 rows inserted/updated/deleted
ij> ALTER TABLE JSL_DB.WITHIN_REP_STAT
ADD CONSTRAINT WRS_SIMRUN_FK FOREIGN KEY (SIM_RUN_ID_FK) REFERENCES
JSL_DB.SIMULATION_RUN (ID) ON DELETE CASCADE;
0 rows inserted/updated/deleted
ij> ALTER TABLE JSL_DB.WITHIN_REP_STAT
ADD CONSTRAINT WRS_UNIQUE_ELEMENT_SIMRUN_REPNUM UNIQUE (ELEMENT_ID_FK,
SIM_RUN_ID_FK, REP_NUM);
0 rows inserted/updated/deleted
ij> ALTER TABLE JSL_DB.WITHIN_REP_STAT
ADD CONSTRAINT WRS_MODEL_ELEMENT_FK FOREIGN KEY (SIM_RUN_ID_FK, ELEMENT_ID_FK)
REFERENCES JSL_DB.MODEL_ELEMENT (SIM_RUN_ID_FK, ELEMENT_ID) ON DELETE CASCADE;
0 rows inserted/updated/deleted
ij> CREATE INDEX WRS_ME_FK_INDEX ON JSL_DB.WITHIN_REP_STAT(SIM_RUN_ID_FK,
ELEMENT_ID_FK);
0 rows inserted/updated/deleted
WARNING 01504: 'WRS_ME_FK_INDEX' index not created because it is a duplicate of
an existing index: 'SQL0000000013-020e0090-0169-c691-93ab-000007295718'.
ij> -- ACROSS_REP_STAT represents summary statistics for each simulation
response across
-- the replications within the experiment.
CREATE TABLE JSL_DB.ACROSS_REP_STAT (
ID INTEGER NOT NULL PRIMARY KEY GENERATED ALWAYS AS IDENTITY (START
WITH 1 INCREMENT BY 1),
ELEMENT_ID_FK INTEGER NOT NULL,
SIM_RUN_ID_FK INTEGER NOT NULL,
STAT_NAME VARCHAR(510),
STAT_COUNT DOUBLE PRECISION CHECK (STAT_COUNT >=0),
AVERAGE DOUBLE PRECISION,
STD_DEV DOUBLE PRECISION CHECK (STD_DEV >=0),
STD_ERR DOUBLE PRECISION CHECK (STD_ERR >=0),
HALF_WIDTH DOUBLE PRECISION CHECK (HALF_WIDTH >=0),
CONF_LEVEL DOUBLE PRECISION,
MINIMUM DOUBLE PRECISION,
MAXIMUM DOUBLE PRECISION,
WEIGHTED_SUM DOUBLE PRECISION,
SUM_OF_WEIGHTS DOUBLE PRECISION,
WEIGHTED_SSQ DOUBLE PRECISION,
DEV_SSQ DOUBLE PRECISION,
LAST_VALUE DOUBLE PRECISION,
LAST_WEIGHT DOUBLE PRECISION,
KURTOSIS DOUBLE PRECISION,
SKEWNESS DOUBLE PRECISION,
LAG1_COV DOUBLE PRECISION,
LAG1_CORR DOUBLE PRECISION,
VON_NEUMAN_LAG1_STAT DOUBLE PRECISION,
NUM_MISSING_OBS DOUBLE PRECISION
);
0 rows inserted/updated/deleted
ij> ALTER TABLE JSL_DB.ACROSS_REP_STAT
ADD CONSTRAINT ARS_SIMRUN_FK FOREIGN KEY (SIM_RUN_ID_FK) REFERENCES
JSL_DB.SIMULATION_RUN (ID) ON DELETE CASCADE;
0 rows inserted/updated/deleted
ij> ALTER TABLE JSL_DB.ACROSS_REP_STAT
ADD CONSTRAINT ARS_MODEL_ELEMENT_FK FOREIGN KEY (SIM_RUN_ID_FK, ELEMENT_ID_FK)
REFERENCES JSL_DB.MODEL_ELEMENT (SIM_RUN_ID_FK, ELEMENT_ID) ON DELETE CASCADE;
0 rows inserted/updated/deleted
ij> CREATE UNIQUE INDEX ARS_ME_FK_INDEX ON
JSL_DB.ACROSS_REP_STAT(SIM_RUN_ID_FK, ELEMENT_ID_FK);
0 rows inserted/updated/deleted
ij> -- WITHIN_REP_COUNTER_STAT represents within replication final value for
each replication of
-- each simulation for each counter
CREATE TABLE JSL_DB.WITHIN_REP_COUNTER_STAT (
ID INTEGER NOT NULL PRIMARY KEY GENERATED ALWAYS AS IDENTITY (START
WITH 1 INCREMENT BY 1),
ELEMENT_ID_FK INTEGER NOT NULL,
SIM_RUN_ID_FK INTEGER NOT NULL,
REP_NUM INTEGER NOT NULL CHECK (REP_NUM >=1),
STAT_NAME VARCHAR(510),
LAST_VALUE DOUBLE PRECISION
);
0 rows inserted/updated/deleted
ij> ALTER TABLE JSL_DB.WITHIN_REP_COUNTER_STAT
ADD CONSTRAINT WRCS_SIMRUN_FK FOREIGN KEY (SIM_RUN_ID_FK) REFERENCES
JSL_DB.SIMULATION_RUN (ID) ON DELETE CASCADE;
0 rows inserted/updated/deleted
ij> ALTER TABLE JSL_DB.WITHIN_REP_COUNTER_STAT
ADD CONSTRAINT WRCS_UNIQUE_ELEMENT_SIMRUN_REPNUM UNIQUE (ELEMENT_ID_FK,
SIM_RUN_ID_FK, REP_NUM);
0 rows inserted/updated/deleted
ij> ALTER TABLE JSL_DB.WITHIN_REP_COUNTER_STAT
ADD CONSTRAINT WRCS_MODEL_ELEMENT_FK FOREIGN KEY (SIM_RUN_ID_FK,
ELEMENT_ID_FK)
REFERENCES JSL_DB.MODEL_ELEMENT (SIM_RUN_ID_FK, ELEMENT_ID) ON DELETE CASCADE;
0 rows inserted/updated/deleted
ij> CREATE INDEX WRCS_ME_FK_INDEX ON
JSL_DB.WITHIN_REP_COUNTER_STAT(SIM_RUN_ID_FK, ELEMENT_ID_FK);
0 rows inserted/updated/deleted
WARNING 01504: 'WRCS_ME_FK_INDEX' index not created because it is a duplicate
of an existing index: 'SQL0000000025-4b6f00c8-0169-c691-93ab-000007295718'.
ij> -- BATCH_STAT represents summary statistics for each simulation response
across
-- the batches within a replication. This is produced only if the batch
statistics
-- option is used when running the simulation.
CREATE TABLE JSL_DB.BATCH_STAT (
ID INTEGER NOT NULL PRIMARY KEY GENERATED ALWAYS AS IDENTITY (START
WITH 1 INCREMENT BY 1),
ELEMENT_ID_FK INTEGER NOT NULL,
SIM_RUN_ID_FK INTEGER NOT NULL,
REP_NUM INTEGER NOT NULL CHECK (REP_NUM >=1),
STAT_NAME VARCHAR(510),
STAT_COUNT DOUBLE PRECISION CHECK (STAT_COUNT >=0),
AVERAGE DOUBLE PRECISION,
STD_DEV DOUBLE PRECISION CHECK (STD_DEV >=0),
STD_ERR DOUBLE PRECISION CHECK (STD_ERR >=0),
HALF_WIDTH DOUBLE PRECISION CHECK (HALF_WIDTH >=0),
CONF_LEVEL DOUBLE PRECISION,
MINIMUM DOUBLE PRECISION,
MAXIMUM DOUBLE PRECISION,
WEIGHTED_SUM DOUBLE PRECISION,
SUM_OF_WEIGHTS DOUBLE PRECISION,
WEIGHTED_SSQ DOUBLE PRECISION,
DEV_SSQ DOUBLE PRECISION,
LAST_VALUE DOUBLE PRECISION,
LAST_WEIGHT DOUBLE PRECISION,
KURTOSIS DOUBLE PRECISION,
SKEWNESS DOUBLE PRECISION,
LAG1_COV DOUBLE PRECISION,
LAG1_CORR DOUBLE PRECISION,
VON_NEUMAN_LAG1_STAT DOUBLE PRECISION,
NUM_MISSING_OBS DOUBLE PRECISION,
MIN_BATCH_SIZE DOUBLE PRECISION,
MIN_NUM_BATCHES DOUBLE PRECISION,
MAX_NUM_BATCHES_MULTIPLE DOUBLE PRECISION,
MAX_NUM_BATCHES DOUBLE PRECISION,
NUM_REBATCHES DOUBLE PRECISION,
CURRENT_BATCH_SIZE DOUBLE PRECISION,
AMT_UNBATCHED DOUBLE PRECISION,
TOTAL_NUM_OBS DOUBLE PRECISION
);
0 rows inserted/updated/deleted
ij> ALTER TABLE JSL_DB.BATCH_STAT
ADD CONSTRAINT BS_SIMRUN_FK FOREIGN KEY (SIM_RUN_ID_FK) REFERENCES
JSL_DB.SIMULATION_RUN (ID) ON DELETE CASCADE;
0 rows inserted/updated/deleted
ij> ALTER TABLE JSL_DB.BATCH_STAT
ADD CONSTRAINT BS_MODEL_ELEMENT_FK FOREIGN KEY (SIM_RUN_ID_FK, ELEMENT_ID_FK)
REFERENCES JSL_DB.MODEL_ELEMENT (SIM_RUN_ID_FK, ELEMENT_ID) ON DELETE CASCADE;
0 rows inserted/updated/deleted
ij> CREATE INDEX BS_ME_FK_INDEX ON JSL_DB.BATCH_STAT(SIM_RUN_ID_FK,
ELEMENT_ID_FK);
0 rows inserted/updated/deleted
WARNING 01504: 'BS_ME_FK_INDEX' index not created because it is a duplicate of
an existing index: 'SQL0000000033-01b7c0e6-0169-c691-93ab-000007295718'.
ij> -- WITHIN_REP_RESPONSE_VIEW represents a reduced view of within replication
statistics containing only the average for the replication
CREATE VIEW JSL_DB.WITHIN_REP_RESPONSE_VIEW (SIM_RUN_ID_FK, EXP_NAME,
ELEMENT_NAME, STAT_NAME, REP_NUM, AVERAGE)
AS (SELECT JSL_DB.WITHIN_REP_STAT.SIM_RUN_ID_FK, EXP_NAME, ELEMENT_NAME,
STAT_NAME, REP_NUM, AVERAGE
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);
0 rows inserted/updated/deleted
ij> -- WITHIN_REP_COUNTER_VIEW represents a reduced view of within replication
counter containing only the last value for the replication
CREATE VIEW JSL_DB.WITHIN_REP_COUNTER_VIEW (SIM_RUN_ID_FK, EXP_NAME,
ELEMENT_NAME, STAT_NAME, REP_NUM, LAST_VALUE)
AS (SELECT JSL_DB.WITHIN_REP_COUNTER_STAT.SIM_RUN_ID_FK, EXP_NAME,
ELEMENT_NAME, STAT_NAME, REP_NUM, LAST_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);
0 rows inserted/updated/deleted
ij> -- ACROSS_REP_VIEW represents a reduced view of the across replication
responses containing only n, avg, and stddev
CREATE VIEW JSL_DB.ACROSS_REP_VIEW (SIM_RUN_ID_FK, EXP_NAME, ELEMENT_NAME,
STAT_NAME, STAT_COUNT, AVERAGE, STD_DEV)
AS (SELECT JSL_DB.ACROSS_REP_STAT.SIM_RUN_ID_FK, EXP_NAME, ELEMENT_NAME,
STAT_NAME, STAT_COUNT, AVERAGE, STD_DEV
FROM (JSL_DB.SIMULATION_RUN JOIN JSL_DB.ACROSS_REP_STAT on
JSL_DB.SIMULATION_RUN.ID = JSL_DB.ACROSS_REP_STAT.SIM_RUN_ID_FK)
JOIN JSL_DB.MODEL_ELEMENT ON ELEMENT_ID =
JSL_DB.ACROSS_REP_STAT.ELEMENT_ID_FK);
0 rows inserted/updated/deleted
ij> -- BATCH_STAT_VIEW represents a reduced view of the batch statistics
responses containing only n, avg, and stddev
CREATE VIEW JSL_DB.BATCH_STAT_VIEW (SIM_RUN_ID_FK, EXP_NAME, ELEMENT_NAME,
STAT_NAME, STAT_COUNT, AVERAGE, STD_DEV)
AS (SELECT JSL_DB.BATCH_STAT.SIM_RUN_ID_FK, EXP_NAME, ELEMENT_NAME,
STAT_NAME, STAT_COUNT, AVERAGE, STD_DEV
FROM (JSL_DB.SIMULATION_RUN JOIN JSL_DB.BATCH_STAT on
JSL_DB.SIMULATION_RUN.ID = JSL_DB.BATCH_STAT.SIM_RUN_ID_FK)
JOIN JSL_DB.MODEL_ELEMENT ON ELEMENT_ID =
JSL_DB.BATCH_STAT.ELEMENT_ID_FK);
0 rows inserted/updated/deleted
ij> -- 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);
0 rows inserted/updated/deleted
ij> -- 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.VAE 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);
ERROR 42X04: Column 'SIMULATION_RUN.SIM_NAME' is either not in any table in the
FROM list or appears within a join specification and is outside the scope of
the join specification or appears in a HAVING clause and is not in the GROUP BY
list. If this is a CREATE or ALTER TABLE statement then
'SIMULATION_RUN.SIM_NAME' is not a column in the target table.
{noformat}
When I adjusted the bad column reference, the script terminated with the
following error:
{noformat}
create view JSL_DB.PW_DIFF_WITHIN_REP_VIEW
as (select 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.VAE 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);
ERROR 42X04: Column 'A.VAE' is either not in any table in the FROM list or
appears within a join specification and is outside the scope of the join
specification or appears in a HAVING clause and is not in the GROUP BY list. If
this is a CREATE or ALTER TABLE statement then 'A.VAE' is not a column in the
target table.
{noformat}
At that point, I gave up trying to adjust the script. Could you attach a script
which runs on Derby without any errors except for the NPE? Thanks.
> 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_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)
