How to find gaps in sequential numbering in HSQLDB?

Question

I'm trying to adapt this answer made for MySQL to HSQLDB (2.4.0, through Hibernate 5.2.12). I have replaced MySQL "IF" function by "CASE WHEN" calls as follows:

SELECT
 CONCAT(z.expected, CASE WHEN z.got-1>z.expected THEN CONCAT(' thru ',z.got-1) ELSE '' END) AS missing
FROM (
 SELECT
  @rownum\\:=@rownum+1 AS expected,
  CASE WHEN @rownum=id THEN 0 ELSE @rownum\\:=id END AS got
 FROM
  (SELECT @rownum\\:=0) AS a
  JOIN Notices
  WHERE fonds_cote = '1Fi'
  ORDER BY id
 ) AS z
WHERE z.got!=0;

The : are escaped to avoid a parsing error from Hibernate. Still, it does not work and I face:

org.hsqldb.HsqlException: unexpected token: (
    at org.hsqldb.error.Error.parseError(Unknown Source) ~[hsqldb-2.4.0.jar:2.4.0]
    at org.hsqldb.ParserBase.unexpectedToken(Unknown Source) ~[hsqldb-2.4.0.jar:2.4.0]
    at org.hsqldb.ParserDQL.XreadSelect(Unknown Source) ~[hsqldb-2.4.0.jar:2.4.0]
    at org.hsqldb.ParserDQL.XreadQuerySpecification(Unknown Source) ~[hsqldb-2.4.0.jar:2.4.0]
    at org.hsqldb.ParserDQL.XreadSimpleTable(Unknown Source) ~[hsqldb-2.4.0.jar:2.4.0]
    at org.hsqldb.ParserDQL.XreadQueryPrimary(Unknown Source) ~[hsqldb-2.4.0.jar:2.4.0]
    at org.hsqldb.ParserDQL.XreadQueryTerm(Unknown Source) ~[hsqldb-2.4.0.jar:2.4.0]
    at org.hsqldb.ParserDQL.XreadQueryExpressionBody(Unknown Source) ~[hsqldb-2.4.0.jar:2.4.0]
    at org.hsqldb.ParserDQL.XreadQueryExpression(Unknown Source) ~[hsqldb-2.4.0.jar:2.4.0]
    at org.hsqldb.ParserDQL.compileCursorSpecification(Unknown Source) ~[hsqldb-2.4.0.jar:2.4.0]
    at org.hsqldb.ParserCommand.compilePart(Unknown Source) ~[hsqldb-2.4.0.jar:2.4.0]
    at org.hsqldb.ParserCommand.compileStatement(Unknown Source) ~[hsqldb-2.4.0.jar:2.4.0]
    at org.hsqldb.Session.compileStatement(Unknown Source) ~[hsqldb-2.4.0.jar:2.4.0]
    at org.hsqldb.StatementManager.compile(Unknown Source) ~[hsqldb-2.4.0.jar:2.4.0]
    at org.hsqldb.Session.execute(Unknown Source) ~[hsqldb-2.4.0.jar:2.4.0]
    at org.hsqldb.jdbc.JDBCPreparedStatement.<init>(Unknown Source) ~[hsqldb-2.4.0.jar:2.4.0]
    at org.hsqldb.jdbc.JDBCConnection.prepareStatement(Unknown Source) ~[hsqldb-2.4.0.jar:2.4.0]
    at org.hibernate.engine.jdbc.internal.StatementPreparerImpl$5.doPrepare(StatementPreparerImpl.java:146) ~[hibernate-core-5.2.12.Final.jar:5.2.12.Final]

What is the correct syntax? Or is there a better way to do it in HSQLDB?

fredt · Accepted Answer · 2018-01-26T01:04:23.473

1

The problem is the use of the variable @rownum. This is not supported by HSQLDB.

With HSQLDB you can do it in a simple manner.

Suppose the table is called CUSTOMER and the sequence column is called ID. The queries below show how SEQUENCE_ARRAY works and is used for finding the missing values.

-- this returns consecutive numbers within a fixed range
SELECT * FROM UNNEST (SEQUENCE_ARRAY(1, 1000, 1))
-- this returns all the possible consecutive numbers for an existing table
SELECT * FROM UNNEST (SEQUENCE_ARRAY((SELECT MIN(ID) FROM CUSTOMER), (SELECT MAX(ID) FROM CUSTOMER), 1))

-- this returns the list of unused IDs.
SELECT * FROM UNNEST (SEQUENCE_ARRAY((SELECT MIN(ID) FROM CUSTOMER), (SELECT MAX(ID) FROM CUSTOMER), 1)) SEQ(IDCOL)
LEFT OUTER JOIN CUSTOMER ON CUSTOMER.ID = SEQ.IDCOL WHERE CUSTOMER.ID IS NULL

edited Jan 26 '18 at 01:04

answered Jan 25 '18 at 15:22

fredt

24,044
3
40
61

Thanks. I have an Hibernate error now: "NonUniqueDiscoveredSqlAliasException: Encountered a duplicated sql alias [ID] during auto-discovery of a native-sql query" – vip Jan 25 '18 at 19:43
I made a slight change to use a different name for the sequence column. – fredt Jan 26 '18 at 01:05
It's now returning results but I had to rework the query as I have another condition to met in addition to the id. I came up with "SELECT DISTINCT(id) FROM UNNEST (SEQUENCE_ARRAY((SELECT MIN(id) FROM Notices WHERE Notices.fonds_cote = '1Fi'), (SELECT MAX(id) FROM Notices WHERE Notices.fonds_cote = '1Fi'), 1)) SEQ(id) LEFT OUTER JOIN Notices ON Notices.id = SEQ.id WHERE NOT EXISTS(SELECT n.id FROM Notices n WHERE n.id = Notices.id AND n.fonds_cote = '1Fi')" but this is very slow, do you see a faster approach? – vip Feb 01 '18 at 23:36

How to find gaps in sequential numbering in HSQLDB?

1 Answers1