binary type doesn't honor max length

[neelkamath]

The binary fixed-length type doesn't seem to honor the provided length: no error is thrown, no warning is logged, and the image isn't even silently trimmed to the specified maximum number of bytes.

Pseudocode for table:

object Images : IntIdTable() {
    private val image: Column<ByteArray> = binary("image", 10)

    fun create(image: ByteArray): Int = transact {
        insertAndGetId { it[this.image] = image }.value
    }

    fun read(id: Int): ByteArray = transact {
        select { Images.id eq id }.first()[image]
    }
}

Pseudocode for test:

class ImagesTest : FunSpec({
    test("size") {
        val id = Images.create("wallpaper.jpg") // A file which is too large, say 400,000 bytes.
        println("Size: ${Images.read(id).size}") // Prints 400,000.
    }
})

[Tapac]

What Exposed version and database do you use?

[neelkamath]

Postgres 12.3

Kotlin 1.3.72 + JDK 8 + JVM 13

Gradle 6.5.1 dependencies:

val exposedVersion = "0.26.1"
implementation("org.jetbrains.exposed:exposed-core:$exposedVersion")
implementation("org.jetbrains.exposed:exposed-jdbc:$exposedVersion")
implementation("org.jetbrains.exposed:exposed-java-time:$exposedVersion")

[Tapac]

There is no client-side validation of length at the moment and PostgreSQL's bytea type doesn't have a limit param. That's why there is no exception.
I will add validation to prevent unexpected value to be written into database.

[neelkamath]

Ohh. Thanks.

To me, it looked like the DB would provide validation since the docs don't explicitly state which DBs validate lengths by themselves, and the limitless binary type stated in its KDoc:

**Note:** This function is only supported by Oracle and PostgeSQL dialects, for the rest please specify a length.

Since the limited binary type didn't state anything about DB support in its KDoc, I assumed Postgres had limited binarys. Perhaps I have misunderstood where such documentation should go, but maybe the limited binary type's KDoc should similarly state that Postgres doesn't support it.

[apprme]

Is it expected that select queries where value in where clause exceeds column length limit throw the exception as well?

As for me throwing exception on insert is better than silently truncating the value. But throwing on select... I am not so sure.

java.lang.IllegalArgumentException: Value 'impersonate:eyJ0eXAiOiJKV1QiLCJhbGciOiJIUzI1NiJ9.eyJzdWIiOiJObXBRajJPNmZmZlgyblR2MW5GVFVNIiwicyI6ZmFsc2UsInQiOmZhbHNlLCJpc3MiOiJEZWxpZ2h0ZXggR21iSCIsImV4cCI6MTYwMDA4Mzg2NSwiaWF0IjoxNTk5NDc5MDY1LCJodyI6IjlJUnk5SVZKS2p5aCJ9._0O1bbWxJ8ot7JQnsqoJpqrsg1GZMzUtjNltRAms4Xc' can't be stored to database column because exceeds length (255)
    at org.jetbrains.exposed.sql.VarCharColumnType.notNullValueToDB (ColumnType.kt:541)
    at org.jetbrains.exposed.sql.IColumnType$DefaultImpls.valueToDB (ColumnType.kt:37)
    at org.jetbrains.exposed.sql.ColumnType.valueToDB (ColumnType.kt:71)
    at org.jetbrains.exposed.sql.statements.api.PreparedStatementApi$DefaultImpls.fillParameters (PreparedStatementApi.kt:13)
    at org.jetbrains.exposed.sql.statements.jdbc.JdbcPreparedStatementImpl.fillParameters (JdbcPreparedStatementImpl.kt:9)
    at org.jetbrains.exposed.sql.statements.Statement.executeIn$exposed_core (Statement.kt:53)
    at org.jetbrains.exposed.sql.Transaction.exec (Transaction.kt:129)
    at org.jetbrains.exposed.sql.Query.count (Query.kt:246)

[apprme]

Interesting that in case of select and VarCharColumnType it does not throw if argument is longer than VarCharColumnType#colLength but shorter than 256, however it throws if it is longer than 255, regardless of VarCharColumnType#colLength

  object T : Table("testlength") {
    val id = varchar("id", 5)
    override val primaryKey = PrimaryKey(id)
  }

  @Test
  fun shouldNotThrowWhenBelow256() {
    transaction {
      val s = "0".repeat(255) // note the column is VARCHAR(5)
      T.select { T.id eq s }.count()
    }
  }

  @Test
  fun shouldNotThrowWhenAbove255() {
    transaction {
      val s = "0".repeat(300)
      T.select { T.id eq s }.count()
    }
  }

MySQL (tested with mysql command line client and server v8.0.19) does not throw a warning and does not truncate the argument before performing the comparison.

mysql> show create table testlen;
+---------+-------------------------------------------------------------------------------------------------------------------------------------+
| Table   | Create Table                                                                                                                        |
+---------+-------------------------------------------------------------------------------------------------------------------------------------+
| testlen | CREATE TABLE `testlen` (
  `id` varchar(5) COLLATE utf8mb4_bin NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin |
+---------+-------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)


mysql> insert into testlen values ("000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000");
Query OK, 1 row affected, 1 warning (0.00 sec)

; here warning is expected

mysql> select * from testlen where id = "000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000";
Empty set (0.00 sec)

; no warning here; also 0 results; however exposed throws an exception

[asule90]

i still got this issue on postgresql.