org.hsqldb.HsqlException: discrepancy in the number of rows counted

I’m trying to play around with an insecure application (Insecure Bank) and I’m trying to port it to secure it a bit more as a personal project.

I have a problem inserting the contents of the database into a database in memory. There are these two tables:

create table cashaccount (
    id int,
    number varchar(80) not null,
    username varchar(80)  not null,
    availablebalance double precision,
    description varchar(80)  not null,
    constraint pk_cashaccount primary key (id)
);

create table creditaccount(
    id int,
    number varchar(80) not null,
    username varchar(80)  not null,
    description varchar(80)  not null,
    availablebalance double precision,
    cashaccountid int,
    constraint pk_creditaccount primary key (id)
);

And some of the values that are expected to be inserted are:

INSERT INTO cashaccount VALUES (1,'10669803404133150948', 'ray', 3424.32,'Checking Account');
INSERT INTO cashaccount VALUES (2,'00948343154448310446', 'joby', 2479.13,'Checking Account');
INSERT INTO cashaccount VALUES (3,'51846636433522240425', 'john', 2134.10,'Checking Account');

INSERT INTO creditaccount VALUES (1,'4024 0071 5848 6471', 'ray', 'Visa Gold',3424.32, 1);
INSERT INTO creditaccount VALUES (2,'4929 1294 1877 8806', 'joby', 'Visa Gold', 2479.13, 2);
INSERT INTO creditaccount VALUES (3,'5521 0508 7181 1232', 'john', 'MasterCard', 2134.10, 3);

But when Hibernate tries to insert the values at the start of the application (I use an import.sql file in the resources folder) I get this error:

org.hibernate.tool.schema.spi.CommandAcceptanceException: Error executing DDL "INSERT INTO creditaccount VALUES (1,'4024 0071 5848 6471', 'ray', 'Visa Gold',3424.32, 1)" via JDBC Statement
...
Caused by: java.sql.SQLSyntaxErrorException: discrepancy in the number of rows counted
...
Caused by: org.hsqldb.HsqlException: discrepancy in the number of rows counted
...

I’ve mapped the Entities like this:

@Entity
@Table(name = "cashaccount")
public class CashAccount {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private int id;

    @NotEmpty
    @Size(max = 80)
    private String number;

    @NotEmpty
    @Size(min = 2, max = 80)
    private String username;

    @NotNull
    private double availablebalance;

    @NotEmpty
    @Size(min = 10, max = 80)
    private String description;

    @OneToMany(mappedBy="cashAccount", cascade = CascadeType.ALL, fetch = FetchType.LAZY)
    private Set<CreditAccount> creditAccounts = new HashSet<>();

    public CashAccount() {
    }

    public CashAccount(@NotEmpty @Size(max = 80) String number, @NotEmpty @Size(min = 2, max = 80) String username,
                       @NotNull double availablebalance, @NotEmpty @Size(min = 10, max = 80) String description) {
        this.number = number;
        this.username = username;
        this.availablebalance= availablebalance;
        this.description = description;
    }
    
    // Setters and getters

@Entity
@Table(name = "creditaccount")
public class CreditAccount {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private int id;

    @NotEmpty
    @Size(max = 80)
    private String number;

    @NotEmpty
    @Size(min = 2, max = 80)
    private String username;

    @NotEmpty
    @Size(min = 10, max = 80)
    private String description;

    @NotNull
    private double availablebalance;

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "id", insertable = false, updatable = false)
    private CashAccount cashAccount;

    public CreditAccount() {
    }

    public CreditAccount(@NotEmpty @Size(max = 80) String number, @NotEmpty @Size(min = 2, max = 80) String username,
                         @NotEmpty @Size(min = 10, max = 80) String description, @NotNull double availablebalance,
                         @NotNull CashAccount cashAccount) {
        this.number = number;
        this.username = username;
        this.description = description;
        this.availablebalance = availablebalance;
        this.cashAccount = cashAccount;
    }
    
    // Setters and getters

But I don’t know where is the mistake or what I’m doing wrong.

As the suggestion by @SternK I’ve changed:

@ManyToOne(fetch = FetchType.LAZY)
@JoinColumn(name = "id", insertable = false, updatable = false)
private CashAccount cashAccount;

to

@ManyToOne(fetch = FetchType.LAZY)
@JoinColumn(name = "cashaccountid")
private CashAccount cashAccount;

but now I get the error:

org.hibernate.tool.schema.spi.CommandAcceptanceException: Error executing DDL "INSERT INTO creditaccount VALUES (1,'4024 0071 5848 6471', 'ray', 'Visa Gold',3424.32, 1)" via JDBC Statement
    at org.hibernate.tool.schema.internal.exec.GenerationTargetToDatabase.accept(GenerationTargetToDatabase.java:67) ~[hibernate-core-5.4.18.Final.jar:5.4.18.Final]
    at org.hibernate.tool.schema.internal.SchemaCreatorImpl.applySqlString(SchemaCreatorImpl.java:439) ~[hibernate-core-5.4.18.Final.jar:5.4.18.Final]
    at org.hibernate.tool.schema.internal.SchemaCreatorImpl.applyImportSources(SchemaCreatorImpl.java:492) ~[hibernate-core-5.4.18.Final.jar:5.4.18.Final]
    at org.hibernate.tool.schema.internal.SchemaCreatorImpl.performCreation(SchemaCreatorImpl.java:180) ~[hibernate-core-5.4.18.Final.jar:5.4.18.Final]
    at org.hibernate.tool.schema.internal.SchemaCreatorImpl.doCreation(SchemaCreatorImpl.java:135) ~[hibernate-core-5.4.18.Final.jar:5.4.18.Final]
    at org.hibernate.tool.schema.internal.SchemaCreatorImpl.doCreation(SchemaCreatorImpl.java:121) ~[hibernate-core-5.4.18.Final.jar:5.4.18.Final]
    at org.hibernate.tool.schema.spi.SchemaManagementToolCoordinator.performDatabaseAction(SchemaManagementToolCoordinator.java:156) ~[hibernate-core-5.4.18.Final.jar:5.4.18.Final]
    at org.hibernate.tool.schema.spi.SchemaManagementToolCoordinator.process(SchemaManagementToolCoordinator.java:73) ~[hibernate-core-5.4.18.Final.jar:5.4.18.Final]
    at org.hibernate.internal.SessionFactoryImpl.<init>(SessionFactoryImpl.java:316) ~[hibernate-core-5.4.18.Final.jar:5.4.18.Final]
    at org.hibernate.boot.internal.SessionFactoryBuilderImpl.build(SessionFactoryBuilderImpl.java:469) ~[hibernate-core-5.4.18.Final.jar:5.4.18.Final]
    at org.hibernate.jpa.boot.internal.EntityManagerFactoryBuilderImpl.build(EntityManagerFactoryBuilderImpl.java:1259) ~[hibernate-core-5.4.18.Final.jar:5.4.18.Final]
    at org.springframework.orm.jpa.vendor.SpringHibernateJpaPersistenceProvider.createContainerEntityManagerFactory(SpringHibernateJpaPersistenceProvider.java:58) ~[spring-orm-5.2.8.RELEASE.jar:5.2.8.RELEASE]
    at org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean.createNativeEntityManagerFactory(LocalContainerEntityManagerFactoryBean.java:365) ~[spring-orm-5.2.8.RELEASE.jar:5.2.8.RELEASE]
    at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.buildNativeEntityManagerFactory(AbstractEntityManagerFactoryBean.java:391) ~[spring-orm-5.2.8.RELEASE.jar:5.2.8.RELEASE]
    at java.base/java.util.concurrent.FutureTask.run$$$capture(FutureTask.java:264) ~[na:na]
    at java.base/java.util.concurrent.FutureTask.run(FutureTask.java) ~[na:na]
    at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128) ~[na:na]
    at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628) ~[na:na]
    at java.base/java.lang.Thread.run(Thread.java:835) ~[na:na]
Caused by: java.sql.SQLDataException: data exception: bad value for CAST
    at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source) ~[hsqldb-2.5.1.jar:2.5.1]
    at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source) ~[hsqldb-2.5.1.jar:2.5.1]
    at org.hsqldb.jdbc.JDBCStatement.fetchResult(Unknown Source) ~[hsqldb-2.5.1.jar:2.5.1]
    at org.hsqldb.jdbc.JDBCStatement.execute(Unknown Source) ~[hsqldb-2.5.1.jar:2.5.1]
    at com.zaxxer.hikari.pool.ProxyStatement.execute(ProxyStatement.java:95) ~[HikariCP-3.4.5.jar:na]
    at com.zaxxer.hikari.pool.HikariProxyStatement.execute(HikariProxyStatement.java) ~[HikariCP-3.4.5.jar:na]
    at org.hibernate.tool.schema.internal.exec.GenerationTargetToDatabase.accept(GenerationTargetToDatabase.java:54) ~[hibernate-core-5.4.18.Final.jar:5.4.18.Final]
    ... 18 common frames omitted
Caused by: org.hsqldb.HsqlException: data exception: bad value for CAST
    at org.hsqldb.error.Error.error(Unknown Source) ~[hsqldb-2.5.1.jar:2.5.1]
    at org.hsqldb.error.Error.error(Unknown Source) ~[hsqldb-2.5.1.jar:2.5.1]
    at org.hsqldb.Scanner.convertToNumber(Unknown Source) ~[hsqldb-2.5.1.jar:2.5.1]
    at org.hsqldb.types.NumberType.convertToType(Unknown Source) ~[hsqldb-2.5.1.jar:2.5.1]
    at org.hsqldb.StatementDML.getInsertData(Unknown Source) ~[hsqldb-2.5.1.jar:2.5.1]
    at org.hsqldb.StatementInsert.getResult(Unknown Source) ~[hsqldb-2.5.1.jar:2.5.1]
    at org.hsqldb.StatementDMQL.execute(Unknown Source) ~[hsqldb-2.5.1.jar:2.5.1]
    at org.hsqldb.Session.executeCompiledStatement(Unknown Source) ~[hsqldb-2.5.1.jar:2.5.1]
    at org.hsqldb.Session.executeDirectStatement(Unknown Source) ~[hsqldb-2.5.1.jar:2.5.1]
    at org.hsqldb.Session.execute(Unknown Source) ~[hsqldb-2.5.1.jar:2.5.1]
    ... 23 common frames omitted

Answer

1. I guess this:

@ManyToOne(fetch = FetchType.LAZY)
@JoinColumn(name = "id", insertable = false, updatable = false)
private CashAccount cashAccount;

should be corrected like this:

@ManyToOne(fetch = FetchType.LAZY)
@JoinColumn(name = "cashaccountid")
private CashAccount cashAccount;

2. As it suggested in this article:

Make sure you are mapping values to correct columns in query. Best way is to define column name and values in query as well.

So, I would suggest you to rewrite your insert statement in the following way:

INSERT INTO cashaccount(id, number, username, availablebalance, description)
VALUES (1,'10669803404133150948', 'ray', 3424.32,'Checking Account');
...
INSERT INTO creditaccount(id, number, username, description, availablebalance, cashaccountid)
VALUES
(1,'4024 0071 5848 6471', 'ray', 'Visa Gold',3424.32, 1);
...