#1497 unique constraint or index violation: SYS_IDX_10094 when IN predicate contains array larger than 288 elements

[Rostislav]

When issuing SELECT (through JDBC with using technique described in HSQLDB guide) in which IN predicate value is an array of 299 lements or larger, exeption "(java.sql.SQLIntegrityConstraintViolationException) java.sql.SQLIntegrityConstraintViolationException: integrity constraint violation: unique constraint or index violation: SYS_IDX_10094" is thrown.

Test-case code:

package hsqltest;
import java.sql.Array;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Random;

public class HsqlTest
{
public static void main(String[] args)
{
final int TABLE_SIZE=100000;
final int SELECT_SIZE=299;
Random lRandom=new Random();
int lResult=0;
try
{
Class.forName("org.hsqldb.jdbc.JDBCDriver" );
}
catch(Exception aEx)
{
System.err.println("ERROR: failed to load HSQLDB JDBC driver.");
return;
}
try
{
Connection lDbConnection=DriverManager.getConnection("jdbc:hsqldb:hsql://localhost:9001/hq_9001", "RA", "RA");

        Statement lCreateStmnt=lDbConnection.createStatement();
        lResult=lCreateStmnt.executeUpdate("CREATE TABLE Customer(CustomerId INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY, CustomerName VARCHAR(64))");

        PreparedStatement lInsertStmnt=lDbConnection.prepareStatement("INSERT INTO Customer VALUES(DEFAULT, ?)", Statement.NO_GENERATED_KEYS);
        for(int lCounter=0; lCounter<TABLE_SIZE; ++lCounter)
        {
            lInsertStmnt.setNString(1, RandomString(lRandom));
            lResult=lInsertStmnt.executeUpdate();
        }

        Integer[] lSelectArray=new Integer[SELECT_SIZE];
        for(int lCounter=0; lCounter<SELECT_SIZE; ++lCounter)
        {
            lSelectArray[lCounter]=lRandom.nextInt(TABLE_SIZE-1);
        }
        PreparedStatement lSelectStmnt=lDbConnection.prepareStatement("SELECT * FROM Customer WHERE CustomerId IN (UNNEST(?))");
        Array lSqlArray=lDbConnection.createArrayOf("INTEGER", lSelectArray);
        lSelectStmnt.setArray(1, lSqlArray);
        ResultSet lSqlResult=lSelectStmnt.executeQuery();
        int lSize=lSqlResult.getFetchSize();
        System.out.println("Resulting set size: "+lSqlResult.getFetchSize());

        while (lSqlResult.next())
        {
            long lCustId=lSqlResult.getInt(1);
            String lCustName=lSqlResult.getString(2);
            System.out.println("| "+lCustId+" | "+lCustName+" |");
        }
    }
    catch(Exception aEx)
    {
        System.err.println("ERROR: failed to execute statement.");
        return;
    }


}

protected static String RandomString(Random aRandom)
{
    final String ALPHABET="ABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890";
    int lAlphabetLength=ALPHABET.length();
    StringBuilder lResult=new StringBuilder();        
    while (lResult.length()<7)
    {
        lResult.append(ALPHABET.charAt(aRandom.nextInt(lAlphabetLength-1)));
    }
    return lResult.toString();
}

}

[Fred Toussi]

Thanks for reporting. The issue is not related to the size of the array used in the IN clause. It is the existence of duplicates in the list.

Fixed and committed to SVN /base/trunk