Prepared statements with bind variables in column specification are not supported

[luisfmcalado]

Overview of the Issue

Prepared statements not working in the following circumstance. The issue doesn't happen with vitessdriver.

Reproduction Steps

Steps to reproduce this issue:

1. Deploy the following vschema:

{
  "tables": {
    "corder": {
    },
    "customer": {
    },
    "product": {
    }
  }
}

2. Deploy the local example and run:

db, err := sql.Open("mysql", "@tcp(127.0.0.1:15306)/live")
if err != nil {
  panic(err.Error())
}
defer db.Close()
	
stmt, err := db.Prepare("SELECT count(*), COALESCE(?, customer_id) FROM customer where customer_id='1' LIMIT 1")	
if err != nil {	
  panic(err.Error())
}
defer stmt.Close()

var value string
err = stmt.QueryRow("1").Scan(&value)
if err != nil {
  panic(err.Error())
}

fmt.Printf("%s", value)

3. It shows the following error:

panic: Error 1105: vtgate: http://lc.home:15001/: target: commerce.0.master, used tablet: zone1-100 (lc.home): vttablet: rpc error: code = InvalidArgument desc = missing bind var v1 (CallerID: userData1)

Operating system and Environment details

Using vitess operator with Kubernetes version 1.15
MariaDB 10.3.22
Running with:

vtctld: vitess/lite:v6.0.20-20200429
vtgate: vitess/lite:v6.0.20-20200429
vttablet: vitess/lite:v6.0.20-20200429
vtbackup: vitess/lite:v6.0.20-20200429

Log Fragments

Prepare 127.0.0.1:56161         'userData1'     ''      2020-06-09 09:56:55.544457      2020-06-09 09:56:55.545311       0.000854        0.000064        0.000790        0.000000SELECT   "select count(*), COALESCE(:v1, customer_id) from customer where customer_id = :vtg2 limit :vtg1"        map[vtg1:type:INT64 value:"1"  vtg2:type:VARBINARY value:"1" ]  10       "target: commerce.0.master, used tablet: zone1-100 (lc.home): vttablet: rpc error: code = InvalidArgument desc = missing bind var v1 (CallerID: userData1)"

[aquarapid]

Verified against current master (2036988). It is failing on the "Prepare Statement" request:

failed response:

Same request against the underlying MySQL works fine.

Here is the full testcase program I used:

$ cat test_prepared_6287.go 
package main

import "database/sql"
import "fmt"
import _ "github.com/go-sql-driver/mysql"

func main() {
	db, err := sql.Open("mysql", "@tcp(127.0.0.1:15306)/commerce")
	//db, err := sql.Open("mysql", "root:root@tcp(127.0.0.1:17100)/vt_commerce")
	if err != nil {
		panic(err.Error())
	}
	defer db.Close()

	stmt, err := db.Prepare("SELECT count(*), COALESCE(?, customer_id) FROM customer where customer_id=1 LIMIT 1")
	if err != nil {
		panic(err.Error())
	}
	defer stmt.Close()

	var value string
	err = stmt.QueryRow("1").Scan(&value)
	if err != nil {
		panic(err.Error())
	}

	fmt.Printf("%s", value)
}

vttablet log error, which is suggestive that the rewriting for GenerateFieldQuery in vtgate is problematic when it makes it to vttablet:

I0609 19:12:08.839894  198107 tabletserver.go:1550] Code: INVALID_ARGUMENT
missing bind var v1 (CallerID: userData1)
: Sql: "select count(*), COALESCE(:v1, customer_id) from customer where 1 != 1", BindVars: {#maxLimit: "type:INT64 value:\"10001\" "vtg1: "type:INT64 value:\"1\" "}

[aquarapid]

Seems that the underlying issue is that Vitess just does not support any prepared statements with bind variables in the column specification of a query today. So, trying to prepare:

SELECT ?, customer_id FROM customer where customer_id=1

would fail in the same way.

[luisfmcalado]

Any reason for it to work with vitess driver?

package main

import (
	//"database/sql"
	"fmt"

	//_ "github.com/go-sql-driver/mysql"
	"vitess.io/vitess/go/vt/vitessdriver"
)

func main() {
	c := vitessdriver.Configuration{
		Address: "127.0.0.1:15991",
		Target:  "@master",
		Protocol: "grpc",
	}
	
	db, err := vitessdriver.OpenWithConfiguration(c)
	//db, err := sql.Open("mysql", "@tcp(127.0.0.1:15306)/commerce@master")
	if err != nil {
		panic(err.Error())
	}
	defer db.Close()

	stmt, err := db.Prepare("SELECT count(*), ? FROM customer where customer_id='1' LIMIT 1")
	if err != nil {
		panic(err.Error())
	}
	defer stmt.Close()

	var count int32
	var value string

	err = stmt.QueryRow("a").Scan(&count, &value)
	if err != nil {
		panic(err.Error())
	}
	fmt.Printf("%d, %s", count, value)
}

[luisfmcalado]

@aquarapid I think I got the issue 🤞. Please take a look and sorry in advance if this is completely wrong. ❤️

[deepthi]

Fixed by #6298