Error 3144 (HY000): Cannot create a JSON value from a string with CHARACTER SET 'binary'.

[hackersean]

Bug Report

the bug is same as MySQL, link

1. Minimal reproduce step (Required)

when use gorm in golang.

storage.DB().Model(filter).Where(filter).Where("JSON_CONTAINS(labels,JSON_OBJECT(?,?))", "flag", true).Find(&list).Error

2. What did you expect to see? (Required)

The query result is displayed normally

3. What did you see instead (Required)

Error 3144 (HY000): Cannot create a JSON value from a string with CHARACTER SET 'binary'.

If concatenated into an SQL statement, it runs properly.

4. What is your TiDB version? (Required)

Release Version: v6.5.0 Edition: Community Git Commit Hash: 706c3fa3c526cdba5b3e9f066b1a568fb96c56e3 Git Branch: heads/refs/tags/v6.5.0 UTC Build Time: 2022-12-27 03:50:44 GoVersion: go1.19.3 Race Enabled: false TiKV Min Version: 6.2.0-alpha Check Table Before Drop: false Store: tikv

[YangKeao]

A reproduce:

package main

import (
    "fmt"

    "gorm.io/driver/mysql"
    "gorm.io/gorm"
)

type Item struct {
    gorm.Model
    Labels string
}

func main() {
    db, err := gorm.Open(mysql.New(mysql.Config{
        DSN: "root@tcp(127.0.0.1:4000)/test",
    }), &gorm.Config{})
    if err != nil {
        panic("failed to connect database")
    }

    db.AutoMigrate(&Item{})

    var filter Item

    var list []Item

    err = db.Model(&filter).Where("JSON_CONTAINS(labels, JSON_OBJECT(?, ?))", "flag", true).Find(&list).Error
    if err != nil {
        fmt.Println("Query error:", err)
        return
    }

    fmt.Println("Query results:", list)
}

It's not related to link, because this program runs well on MySQL.

The problem is that during PREPARE, the charset/collation of the first argument is binary. We know little about the correct collation/type during PREPARE, so maybe it's not correct to assert type and charset/collation in getFunction stage. There must be many similar issues, not only for JSON.

I'll try to find more similar issues and fix them all.