MySQL Cursor drops connect when fetching a case statement

[Yaytay]

Version

4.5.14 (can't update until Resteasy does :( )

Context

When using the MySQL client to read the results of a query via a cursor, fetching some rows at a time, if the query contains a "case" statement the second fetch fails.

If the result of the case statement is cast to "signed integer" in SQL it works, but the raw case results don't.

Looking in Wireshark, the case returns a FIELD_TYPE_LONG, but the cast returns FIELD_TYPE_LONGLONG.

The simplest example I have for this is:

SELECT 
  row_number() over () as "Number"
  , case when 1 then 1 else 0 end as "Case" 
FROM 
  mysql.help_relation limit 0,100

With a cursor fetching 50 rows at a time.

Do you have a reproducer?

https://github.com/Yaytay/vertx_mysql_case_cursor/blob/main/src/test/java/uk/co/spudsoft/vmcc/MainTest.java

Steps to reproduce

Pass the result of a "case" statement to a cursor and it will fail on the second fetch.

Extra

MySQL version data: innodb_version: 8.0.41 protocol_version: 10 version: 8.0.41 version_comment: MySQL Community Server - GPL version_compile_machine: x86_64 version_compile_os: Linux version_compile_zlib: 1.3.1

[Yaytay]

The reproducer fails with Vertx 5.0.0 too.

[Yaytay]

I just hit the same issue with a subquery that contained a coalesce of two int fields. The type reported on the first fetch was Integer and it failed with:

java.lang.IndexOutOfBoundsException: index: 484, length: 99 (expected: range(0, 530))
        at io.netty.buffer.AbstractByteBuf.checkRangeBounds(AbstractByteBuf.java:1390)
        at io.netty.buffer.AbstractByteBuf.checkIndex0(AbstractByteBuf.java:1397)
        at io.netty.buffer.PooledSlicedByteBuf.getBytes(PooledSlicedByteBuf.java:234)
        at io.netty.buffer.ByteBufUtil.decodeString(ByteBufUtil.java:1365)
        at io.netty.buffer.AbstractByteBuf.toString(AbstractByteBuf.java:1246)
        at io.netty.buffer.AbstractByteBuf.getCharSequence(AbstractByteBuf.java:513)
        at io.netty.buffer.AbstractByteBuf.readCharSequence(AbstractByteBuf.java:518)
        at io.vertx.mysqlclient.impl.util.BufferUtils.readFixedLengthString(BufferUtils.java:29)
        at io.vertx.mysqlclient.impl.util.BufferUtils.readLengthEncodedString(BufferUtils.java:110)
        at io.vertx.mysqlclient.impl.datatype.DataTypeCodec.binaryDecodeText(DataTypeCodec.java:561)
        at io.vertx.mysqlclient.impl.datatype.DataTypeCodec.decodeBinary(DataTypeCodec.java:249)
        at io.vertx.mysqlclient.impl.codec.RowResultDecoder.decodeRow(RowResultDecoder.java:65)
        at io.vertx.sqlclient.impl.RowDecoder.handleRow(RowDecoder.java:58)

when trying to perform the second fetch.

[Yaytay]

The year(() and week() functions also seem to be affected. In a query with both of them the output didn't break, it just became garbled.

[vietj]

thanks for the reproducer

[tsegismont]

Fixed by https://github.com/eclipse-vertx/vertx-sql-client/pull/1564