GORM JSONArrayQuery.Contains isn't compatible

[ns-kliu]

Hello, I found a weird issue when using GROM datatypes.JSONArrayQuery.Contains to access in-memory go-mysql-server, please see the below example or check my repo
I test 3 statements, their SQL query is the same, but only 1 works as expected.

I also test

• Connect to a real MySQL or MariaDB instance, and all 3 statements can work
• Use MySQL CLI to connect in-memory go-mysql-server and run the same query, that can work.

package main

import (
	"fmt"
	sqle "github.com/dolthub/go-mysql-server"
	"github.com/dolthub/go-mysql-server/memory"
	"github.com/dolthub/go-mysql-server/server"
	"gorm.io/datatypes"
	"gorm.io/driver/mysql"
	"gorm.io/gorm"
	"gorm.io/gorm/logger"
	"time"
)

var (
	_dbName    = "mydb"
	_dbUser    = "root"
	_dbAddress = "localhost"
	_dbPort    = 3306

	_dbConn *gorm.DB
)

type User struct {
	ID        uint
	Name      string
	Languages datatypes.JSONSlice[string]
}

func main() {
	var err error

	initMemoryMySQL()
	dsn := fmt.Sprintf("%s@tcp(%s:%d)/%s?charset=utf8mb4&parseTime=True&loc=Local", _dbUser, _dbAddress, _dbPort, _dbName)
	if _dbConn, err = gorm.Open(mysql.Open(dsn), &gorm.Config{
		Logger: logger.Default.LogMode(logger.Info),
	}); err != nil {
		panic(err)
	}

	if err = _dbConn.AutoMigrate(&User{}); err != nil {
		panic(err)
	}

	_dbConn.Create(&User{Name: "Tom", Languages: []string{"ZH", "EN"}})

	result := _dbConn.Where(datatypes.JSONArrayQuery("languages").Contains("ZH")).First(&User{})
	// MySQL:
	// SELECT * FROM `users` WHERE JSON_CONTAINS (`languages`, JSON_ARRAY('ZH')) ORDER BY `users`.`id` LIMIT 1
	fmt.Println(result.RowsAffected) // 0: record not found

	result = _dbConn.Raw("SELECT * FROM `users` WHERE JSON_CONTAINS (`languages`, JSON_ARRAY(?)) ORDER BY `users`.`id` LIMIT 1", "ZH").First(&User{})
	// MySQL:
	// SELECT * FROM `users` WHERE JSON_CONTAINS (`languages`, JSON_ARRAY('ZH')) ORDER BY `users`.`id` LIMIT 1
	fmt.Println(result.RowsAffected) // 0: record not found

	result = _dbConn.Where(fmt.Sprintf("JSON_CONTAINS (`languages`, JSON_ARRAY('%v'))", "ZH")).First(&User{})
	// MySQL:
	// SELECT * FROM `users` WHERE JSON_CONTAINS (`languages`, JSON_ARRAY('ZH')) ORDER BY `users`.`id` LIMIT 1
	fmt.Println(result.RowsAffected) // 1

	time.Sleep(10000000 * time.Second)
}

func initMemoryMySQL() {
	db := memory.NewDatabase(_dbName)
	db.EnablePrimaryKeyIndexes()
	engine := sqle.NewDefault(
		memory.NewDBProvider(db))
	engine.Analyzer.Catalog.MySQLDb.AddRootAccount()

	config := server.Config{
		Protocol: "tcp",
		Address:  fmt.Sprintf("%s:%d", _dbAddress, _dbPort),
	}

	s, err := server.NewDefaultServer(config, engine)
	if err != nil {
		panic(err)
	}

	go func() {
		if err = s.Start(); err != nil {
			panic(err)
		}
	}()
	time.Sleep(1 * time.Second)
}

Result:

2023/07/11 14:57:45 /Users/raymond_liu/Projects/go-mysql-issue/main.go:47 record not found
[1.108ms] [rows:0] SELECT * FROM `users` WHERE JSON_CONTAINS (`languages`, JSON_ARRAY('ZH')) ORDER BY `users`.`id` LIMIT 1
0

2023/07/11 14:57:45 /Users/raymond_liu/Projects/go-mysql-issue/main.go:52 record not found
[1.735ms] [rows:0] SELECT * FROM `users` WHERE JSON_CONTAINS (`languages`, JSON_ARRAY('ZH')) ORDER BY `users`.`id` LIMIT 1
0

2023/07/11 14:57:45 /Users/raymond_liu/Projects/go-mysql-issue/main.go:57
[0.560ms] [rows:1] SELECT * FROM `users` WHERE JSON_CONTAINS (`languages`, JSON_ARRAY('ZH')) ORDER BY `users`.`id` LIMIT 1
1

[ns-kliu]

Can any maintainer have a look?

[timsehn]

@fulghum will look today.

--Tim

[fulghum]

Hey @ns-kliu, thanks for taking the time to report this and to provide such a nice repro case! I've got the repro going and am seeing the same behavior you describe. The first thing I've noticed is that the first two queries (which don't return the user that was just created) are both using prepared statements, and the third query (which works correctly) is simply being executed without being prepared and then having bind vars plugged in. Based on that, it seems like there's something going on with query preparation. I've tried disabling prepared statements to confirm that guess, but the configuration I provided doesn't seem to actually disable them.

I'll keep digging in and see what else I can find in the debugger.

[fulghum]

Good find @ns-kliu. I tracked this down to the JSON_ARRAY function not properly handling arguments when they come in as bind vars, and I opened #1919 with the fix. Thank you for pointing out this problem and sorry about the initial delay in us investigating! 🙏

[fulghum]

I kept digging deeper into this one and noticed that the client/GORM is sending over the type CHAR for the bind var, but Vitess is swallowing that info from the client and instead labeling it as VARBINARY when it sends it to go-mysql-server for processing. I just merged in a fix to our fork of Vitess to stop overwriting CHAR with VARBINARY for bind vars, and I confirmed that change enables JSON_ARRAY to handle the prepared query correctly now.

I've got a few more steps to get that change available in go-mysql-server and dolt and will close this issue once those are done.