Is insert with null value generate wrong sql query ?

[mmaryo]

Hello,

I wrote a bu report to r2dbc project but they think it's related to spring data

this is the original message r2dbc/r2dbc-spi#271

And the description of the bug :

---

It looks like a bug

CryptoExchanges(null, null) Should generates this request : INSERT INTO "common"."CryptoExchanges" VALUES (DEFAULT, DEFAULT);

But I see it generates: INSERT INTO "common"."CryptoExchanges" VALUES (DEFAULT)

With an error : Caused by: org.h2.jdbc.JdbcSQLSyntaxErrorException: Column count does not match; SQL statement:

I join a project with last version of Spring boot, Java, Kotlin and H2 R2dbc

You can find a test inside that generate this error
And an SQL script for init the DB

    @Test
    fun findByExchangeCodeNotNull() {

        // given
        val uni = CryptoExchanges(null, "uni")
        val null1 = CryptoExchanges(null, null)
        cryptoExchangesRepository.saveAll(listOf(uni, null1)).collectList().block()

        // when
        val res = cryptoExchangesRepository.findByExchangeCodeNotNull()

        // then
        res.`as`(StepVerifier::create)
            .expectNextMatches { it.id == 1L && it.exchangeCode == "uni" }
            .expectNextMatches { it.id == 2L && it.exchangeCode == null}
            .verifyComplete()
    }



    CREATE SCHEMA "common";
    
    CREATE TABLE "common"."CryptoExchanges"
    (
        "Id"           SERIAL NOT NULL,
        "ExchangeCode" character varying(30)
    );
    
    INSERT INTO "common"."CryptoExchanges" VALUES (DEFAULT, 'crypto');
    
    INSERT INTO "common"."CryptoExchanges" VALUES (DEFAULT, DEFAULT);

demo.zip

[schauder]

CryptoExchanges(null, null) Should generates this request : INSERT INTO "common"."CryptoExchanges" VALUES (DEFAULT, DEFAULT);

Why should it create two DEFAULT entries? DEFAULT should only be used for id columns, shouldn't it?

[spring-projects-issues]

If you would like us to look at this issue, please provide the requested information. If the information is not provided within the next 7 days this issue will be closed.

[mmaryo]

Hello @schauder
Sorry for the late
Honestly, I do not know if DEFAULT should be used for another thing than the ID field
But I can see :
-DEFAULT is working on all nullable fields: INSERT INTO "common"."CryptoExchanges" VALUES (DEFAULT, DEFAULT);
-The generated sql request should have 2 parameters, not one: INSERT INTO "common"."CryptoExchanges" VALUES (DEFAULT)
So the sql error is about the number of parameters

What do you think?