Learn how easy it is to sync an existing GitHub or Google Code repo to a SourceForge project! See Demo

Close

NPE in complex SQL expression for v2.0 rc9

Duff Gold
2010-05-28
2014-01-19
  • Duff Gold
    Duff Gold
    2010-05-28

    We're unable to execute the following SQL:
    Hibernate:
    select table2.id as col_0_0_,
      min(table2.name) as col_1_0_,
      count(table3.id) as col_2_0_,
      (select count(*) from app_table1 table15_ where table15_.table2_id=table2.id and table15_.state=0) as col_3_0_,
      (select count(*) from app_table1 table16_ where table16_.table2_id=table2.id and (table16_.state in (1 ,  6))) as col_4_0_,
      (select count(*) from app_table1 table17_ where table17_.table2_id=table2.id and table17_.state=3) as col_5_0_,
      (select count(*) from app_table1 table18_ where table18_.table2_id=table2.id and table18_.state=4) as col_6_0_,
      (select count(*) from app_table1 table19_ where table19_.table2_id=table2.id and table19_.state=5) as col_7_0_,
      (select count(*) from app_table1 table110_ where table110_.table2_id=table2.id and table110_.state=7) as col_8_0_,
      (select count(*) from app_table1 table111_ where table111_.table2_id=table2.id and table111_.state=8) as col_9_0_,
      (select count(*) from app_table1 table112_ where table112_.table2_id=table2.id and table112_.state=-1) as col_10_0_,
      min(table4.name) as col_11_0_,
      min(table4.appId) as col_12_0_
    from app_table_4 table4,
      app_table_2 table2
      left outer join app_table_3 table3 on table2.id=table3.table2_id and (table3.active=1)
      inner join app_tag_3 tags3_ on table2.id=tags3_.table2_id
      inner join app_tag_4 tag4_ on tags3_.tag_id=tag4_.id
      where tag4_.taggable_id=table4.appId and (tag4_.id in (? ,  ?)) and table2.temporaryProfile=0 group by table2.id

    We get a null pointer exception which boils down to the following:
    Caused by: java.lang.NullPointerException
    at org.hsqldb.ExpressionColumn.getValue(ExpressionColumn.java:523)
    at org.hsqldb.RangeVariable$RangeIteratorMain.getFirstRow(RangeVariable.java:849)
    at org.hsqldb.RangeVariable$RangeIteratorMain.initialiseIterator(RangeVariable.java:806)
    at org.hsqldb.RangeVariable$RangeIteratorMain.next(RangeVariable.java:749)
    at org.hsqldb.QuerySpecification.buildResult(QuerySpecification.java:1178)
    at org.hsqldb.QuerySpecification.getSingleResult(QuerySpecification.java:1115)
    at org.hsqldb.QuerySpecification.getResult(QuerySpecification.java:1105)
    at org.hsqldb.SubQuery.materialise(SubQuery.java:196)
    at org.hsqldb.SubQuery.materialiseCorrelated(SubQuery.java:176)
    at org.hsqldb.Expression.getValue(Expression.java:1277)
    at org.hsqldb.QuerySpecification.buildResult(QuerySpecification.java:1200)
    at org.hsqldb.QuerySpecification.getSingleResult(QuerySpecification.java:1115)
    at org.hsqldb.QuerySpecification.getResult(QuerySpecification.java:1105)
    at org.hsqldb.StatementQuery.getResult(StatementQuery.java:65)
    at org.hsqldb.StatementDMQL.execute(StatementDMQL.java:216)
    … 143 more
    columnIndex == 0, rangePosition == 0, and rangeIterators == null
    rangeIterators is populated at index 2, and 17 through 22

    I have substituted anonymous column & table names. Let me know what you need. Works OK in hsqldb v 1.8.x.

     
  • Fred Toussi
    Fred Toussi
    2010-05-28

    Are you using the latest snapshot jars from http://hsqldb.org/support/

    If not, pleas do and report.

    If the issue still exists, please create examples of the table each with a couple of rows of sample data and submit for further testing.

     
  • Duff Gold
    Duff Gold
    2010-05-28

    Fred, we're using the latest release. I'll replace with the latest snapshot & retry. Let you know after.
    -Duff

     
  • Duff Gold
    Duff Gold
    2010-05-28

    That issue still exists in the most recent snapshot. Will post a schema & data ASAP after I munge the tablenames.

     
  • Duff Gold
    Duff Gold
    2010-06-02

    Apologies for the delay. I found while extracting the minimal schema that the engine does not like the subselects (col_3_0 through col_10_0)

    Schema statements:
    CREATE CACHED TABLE PUBLIC.VC_CORE_TERMINALPROFILE_TAG(TERMINALPROFILE_ID BIGINT NOT NULL,TAG_ID BIGINT NOT NULL,PRIMARY KEY(TERMINALPROFILE_ID,TAG_ID));
    CREATE CACHED TABLE PUBLIC.VC_DEVICE_TERMINAL(ID BIGINT NOT NULL PRIMARY KEY,VERSION INTEGER NOT NULL,NAME VARCHAR(64),
    NOTES VARCHAR(1024),ACTIVE BIT(1),STATE INTEGER NOT NULL, TERMINALPROFILE_ID BIGINT
    );
    CREATE INDEX IDX_TERMINAL_TERMINALPROFILE ON PUBLIC.VC_DEVICE_TERMINAL(TERMINALPROFILE_ID);
    CREATE CACHED TABLE PUBLIC.VC_DEVICE_TERMINALPROFILE(ID BIGINT GENERATED BY DEFAULT AS IDENTITY(START WITH 1) NOT NULL PRIMARY KEY,NAME VARCHAR(128),DATECREATED TIMESTAMP,DATECREATEDTIMEZONEID VARCHAR(50),FAKEDOUTFFISIZE BIGINT,FAKEDOUTFFIMD5 VARCHAR(255),TEMPORARYPROFILE BIT(1),NETWORKCONFIGURATION_ID BIGINT,FFIFILE_ID BIGINT,BBIFILE_ID BIGINT,RRIFILE_ID BIGINT,FIRMWARE_ID BIGINT);
    CREATE CACHED TABLE PUBLIC.VOC_SITE(SITEID BIGINT GENERATED BY DEFAULT AS IDENTITY(START WITH 1) NOT NULL PRIMARY KEY,VERSION INTEGER NOT NULL,NAME VARCHAR(255) NOT NULL,DESCRIPTION VARCHAR(2000),NOTES VARCHAR(2000),TIMEZONE VARCHAR(255),EAPTYPE INTEGER,CREDENTIALASSOCIATION INTEGER,CREDENTIALTYPE INTEGER,USEPINS BIT(1),CHARGERDISCONNECT BIT(1),RESTRICTEDSSID VARCHAR(128),RESTRICTEDEAPTYPE INTEGER,SITEPIN VARCHAR(32),SERVERCREDENTIALS_ID BIGINT,SITEWIDEUSER_ID BIGINT,RESTRICTEDUSER_ID BIGINT,LDAPCONFIG_ID BIGINT,UNIQUE(NAME))
    ;
    ALTER TABLE PUBLIC.VOC_SITE ALTER COLUMN SITEID RESTART WITH 1;
    CREATE CACHED TABLE PUBLIC.VOC_TAG(ID BIGINT GENERATED BY DEFAULT AS IDENTITY(START WITH 1) NOT NULL PRIMARY KEY,TAG_TYPE BIGINT,TAGGABLE_ID BIGINT);
    ALTER TABLE PUBLIC.VOC_TAG ALTER COLUMN ID RESTART WITH 1;
    ALTER TABLE PUBLIC.VC_CORE_TERMINALPROFILE_TAG ADD
    CONSTRAINT FKEC676CBA9924AE56 FOREIGN KEY(TERMINALPROFILE_ID) REFERENCES PUBLIC.VC_DEVICE_TERMINALPROFILE(ID);
    ALTER TABLE PUBLIC.VC_CORE_TERMINALPROFILE_TAG ADD
    CONSTRAINT FKEC676CBA3EF30942 FOREIGN KEY(TAG_ID) REFERENCES PUBLIC.VOC_TAG(ID);
    ALTER TABLE PUBLIC.VC_DEVICE_TERMINAL ADD
    CONSTRAINT FK_TERMINAL_TERMINALPROFILE FOREIGN KEY(TERMINALPROFILE_ID) REFERENCES PUBLIC.VC_DEVICE_TERMINALPROFILE(ID);

    Data inserts:
    insert into VC_DEVICE_TERMINALPROFILE (id, temporaryProfile, name) values(1,0,'tp1');
    insert into VC_DEVICE_TERMINALPROFILE (id, temporaryProfile, name) values(2,0,'tp2');
    insert into voc_tag (id,tag_type, taggable_id) values(-463,1,-463);
    insert into voc_tag (id,tag_type, taggable_id) values(-25,1,-24);
    insert into voc_tag (id,tag_type, taggable_id) values(-1,1,-1);
    insert into vc_core_terminalprofile_tag (terminalprofile_id, tag_id) values (1,-1);
    insert into vc_core_terminalprofile_tag (terminalprofile_id, tag_id) values (2,-1);
    insert into voc_site (siteid, version, name, description) values (-24,0,'two','second');
    insert into voc_site (siteid, version, name, description) values (-1,0,'one','first');

    SQL for the NPE:
    select terminalpr1_.id as termid, terminalpr1_.name,
    min(terminalpr1_.name) as name,
    count(terminals2_.id) as terminals,
    (select count(*) from vc_device_terminal terminal5_ where terminal5_.terminalprofile_id=terminalpr1_.id and terminal5_.state=0) as col_3_0_,
    (select count(*) from vc_device_terminal terminal6_ where terminal6_.terminalprofile_id=terminalpr1_.id and (terminal6_.state in (1 , 6))) as col_4_0_,
    (select count(*) from vc_device_terminal terminal7_ where terminal7_.terminalprofile_id=terminalpr1_.id and terminal7_.state=3) as col_5_0_,
    (select count(*) from vc_device_terminal terminal8_ where terminal8_.terminalprofile_id=terminalpr1_.id and terminal8_.state=4) as col_6_0_,
    (select count(*) from vc_device_terminal terminal9_ where terminal9_.terminalprofile_id=terminalpr1_.id and terminal9_.state=5) as col_7_0_,
    (select count(*) from vc_device_terminal terminal10_ where terminal10_.terminalprofile_id=terminalpr1_.id and terminal10_.state=7) as col_8_0_,
    (select count(*) from vc_device_terminal terminal11_ where terminal11_.terminalprofile_id=terminalpr1_.id and terminal11_.state=8) as col_9_0_,
    (select count(*) from vc_device_terminal terminal12_ where terminal12_.terminalprofile_id=terminalpr1_.id and terminal12_.state=-1) as col_10_0_,
    max(site0_.name) as col_11_0_,
    max(site0_.siteId) as col_12_0_
    from voc_site site0_, vc_core_terminalprofile_tag tags3_, voc_tag tag4_,
    vc_device_terminalprofile terminalpr1_ left outer join vc_device_terminal terminals2_
        on terminalpr1_.id=terminals2_.terminalprofile_id and (terminals2_.active=1)
    where terminalpr1_.temporaryProfile=0
          and terminalpr1_.id=tags3_.terminalprofile_id
          and tags3_.tag_id=tag4_.id
          and tag4_.taggable_id=site0_.siteId and (tag4_.id in (-1))
    group by terminalpr1_.id;

     
  • Fred Toussi
    Fred Toussi
    2010-06-03

    Thanks,

    New snapshot jar is now available with fix. I have added rows to the vc_device_terminal table and checked the result.

     
  • Duff Gold
    Duff Gold
    2010-06-03

    Verified the fix. Nice work! Thank you.