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: 706c3fa
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.