Bug Report: Unexpected result when comparing `BIT` with `VARCHAR`

[suyZhong]

Overview of the Issue

Consider the following test case. It is unexpected that the third query returns an empty result since the second query shows that the expression should be evaluated to 1, and the third query should return the row in the table. Note that after removing the index, the result will be as expected. Thus I suppose the result is unexpected.

This could also reproduced in MySQL. Kindly let me know if it is regarded as expected.

Reproduction Steps

Start the Vittestserver docker container according to https://vitess.io/docs/21.0/get-started/vttestserver-docker-image/#example

Execute the following SQL using a MySQL client

DROP DATABASE IF EXISTS db0;
CREATE DATABASE IF NOT EXISTS db0;
USE db0;

CREATE  TABLE  t0(c1 BIT);
INSERT INTO t0(c1) VALUES ('');
CREATE INDEX t0i0 ON t0(c1); -- index is needed to trigger it


SELECT * FROM t0; -- 0x00
SELECT ((t0.c1='a')) FROM t0; -- 1
SELECT * FROM t0 WHERE ((t0.c1='a'));
-- Expected: 0x00 (if the second query is expected)
-- Actual: empty

Binary Version

Version: 21.0.0-SNAPSHOT (Git revision e0a5069cd378269d5a88c3c48cd699a45ee8ab67 branch 'main')

Operating System and Environment details

docker pull vitess/vttestserver:mysql80
x86_64

Log Fragments

na

[systay]

Thank you for reporting this, @zuyZhong. To clarify for others following this issue, the behavior you’re observing originates from MySQL. Vitess passes the query as-is to the underlying MySQL instance, and the unexpected result is due to how MySQL handles the query. We’ll need to consider whether we should address this behavior in Vitess before the query is passed to MySQL or allow MySQL’s native handling to continue as is.