H2 inner insert is not counted when a select is around

[MelleD]

Describe the bug An auto increment id is currently inserted with the following statement (H2).

select "ID" from final table (insert into...

The select statement is counted with @ExpectSelect. However, the actual insert statement does not. This means that ExpectInsert is always 0 for a current insert statement.

Expected behavior I would expect the insert statement to be counted.

Actual behavior The insert statement is not counted.

To Reproduce Use jooq with H2 and produce a store entity with a auto increment/identity primary key column id.

Should also be possible to use plain prepared statement with the same SELECT and the inner insert.

Versions

• QuickPerf:
• JDK: 17
• OS: Ventura
• Database (if SQL annotation bug): H2

[jeanbisutti]

Thank you @MelleD for having reported this. The new behavior requires updating this class with refactoring. If you are interested, you can try, and I can help you.

[MelleD]

@jeanbisutti thanks for the quick reply What exactly is your suggestion? Should there be counted 1 select AND 1 insert or just 1 insert?

[jeanbisutti]

I meant 1 select and 1 insert (in your query example, both a select and an insert statement seem executed). The code will have probably to be updated at many places, but I don't think it's difficult. The QuickPerf documentation should perhaps promote more the ExpectJdbcQueryExecution annotation that gives an idea of the number of JDBC roundtrips. This annotation seems not to be extensively used. To determine the exact number of JDBC roundtrips, instrumentation might help (QuickPerf uses only today a proxy on the datasource).

[MelleD]

I'm currently using Quickperf with jOOQ

I'm currently still unsure whether Quickperf with jOOQ offers so many advantages compared to JPA. jOOQ delivers more expected SQL statements.

What is striking, however, is that a large number of statements are not counted, since many statements are nested. Even functions with declare etc. are not working yet.

In addition, the statements are strongly adapted to the database, so that it even makes sense to have different counts for one database. It would be great if you could specify the target database in the annotation.

[jeanbisutti]

@MelleD

What is striking, however, is that a large number of statements are not counted, since many statements are nested. Even functions with declare etc. are not working yet.

For information, you can use the ExpectJdbcQueryExecution annotation to check the number of JDBC executions. I would recommend using this annotation rather than @ExpectSelect, @ExpectInsert, ... The purpose of QuickPerf is to evaluate some performance-related properties. So, any new feature has to be on the evaluation of a performance-related property.

In addition, the statements are strongly adapted to the database, so that it even makes sense to have different counts for one database. It would be great if you could specify the target database in the annotation.

You can build a different datasource proxy in your tests with QuickPerfSqlDataSourceBuilder, an example here. If you use a QuickPerf Spring Boot starter, you could use a Spring Boot profile to select only one datasource by test class.

[MelleD]

You can build a different datasource proxy in your tests with QuickPerfSqlDataSourceBuilder, an example here. If you use a QuickPerf Spring Boot starter, you could use a Spring Boot profile to select only one datasource by test class.

But then I need a separate test class for each database, correct?

The purpose of QuickPerf is to evaluate some performance-related properties. So, any new feature has to be on the evaluation of a performance-related property

Yes, as I said with JPA, I totally understand the advantage of QuickPerf and that's where we use it successfully and there have already been some hints about n+1 problems. Since jOOQ delivers less unexpected SQL, especially because you declare joins directly in the DSL, I'm still unsure whether the advantage here is that big.

[jeanbisutti]

@MelleD

But then I need a separate test class for each database, correct?

As far as I know.

Yes, as I said with JPA, I totally understand the advantage of QuickPerf and that's where we use it successfully and there have already been some hints about n+1 problems. Since jOOQ delivers less unexpected SQL, especially because you declare joins directly in the DSL, I'm still unsure whether the advantage here is that big.

QuickPerf is not only about the verification of the number of SQL statements. See for example the ExpectMaxQueryExecutionTime, DisableLikeWithLeadingWildcard, DisableQueriesWithoutBindParameters annotations and other ones given on this page that can be interesting to use with JOOQ.