MySQL bit(1) type issue

[cameronbraid]

Reproduction See MySQL bit(1) type issue in https://github.com/cameronbraid/readyset_seaorm_issues

readyset reports the following warning in its logs and sends the query to the fallback :

2023-08-10T03:02:04.827055Z  WARN connection{addr=172.25.0.1:54620}:connection{addr=172.25.0.1:54620}:execute:
readyset_adapter::backend: Error received from noria, sending query to fallback error=
DfValue conversion error: Failed to convert value of type Unknown to the type Bit(1): Not allowed

[cameronbraid]

I think this can be fixed (at least it worked for me)

readyset-data/src/integer.rs line 71, making DfType::Bit work the same as Bool

DfType::Bool | DfType::Bit(_) => Ok(DfValue::from(val ^ val != val)),

[frannoriega]

Hi! Thanks for reporting this!

I've been looking over the issue, and I realized the fix you are proposing works perfectly when the column type is Bit(1) (since one bit can effectively be represented as a boolean value), but it's not enough for the general case, since per MySQL specification, such columns can have up to 64 bits.

I'll be uploading a fix for the general case, by just treating the value as an i64 (which I believe is the right approach, based on a few manual experiments I ran against MySQL).

I've also noticed that while a fix in the coercion logic would make the error go away, there's still an issue with queries involving bit values.

Take these queries for example:

mysql> CREATE TABLE t (x bit(3));
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO t VALUES (7);
Query OK, 1 row affected (0.03 sec)

mysql> select * from t where x = b'111';
+------------+
| x          |
+------------+
| 0x07       |
+------------+
1 rows in set (0.00 sec)

mysql> select * from t where x = 7;
Empty set (0.00 sec)

I am currently working on a fix for this as well as part of this ticket. I'll keep you posted!

[frannoriega]

Upon further investigation, I've noticed that this bug runs deeper that I thought.

We shouldn't fix this bug until we tackle https://github.com/readysettech/readyset/issues/373, otherwise we would be returning inconsistent results depending on how you decide to filter (in the example of the previous comment, using 7 or b'111' would yield different results).

For now, we would keep this as unsupported (thus redirecting these queries to upstream for guaranteed correctness), and will get back to this bug once https://github.com/readysettech/readyset/issues/373 is closed.