Filtering numbers by string and strings by numbers do not match MySql behavior

[angelamayxie]

Related to comment here.

Related to #9733

dolt

tmp/main*> create table test01(pk varchar(20) primary key);
Query OK, 0 rows affected (0.01 sec)
tmp/main*> insert into test01 values ("11d"),("11wha?"),("11"),("12");
Query OK, 4 rows affected (0.01 sec)
tmp/main*> select * from test01 where pk in ("11");
+----+
| pk |
+----+
| 11 |
+----+
1 row in set (0.00 sec) 


tmp/main*> select * from test01 where pk in (11);
+----+
| pk |
+----+
| 11 |
+----+
1 row in set (0.00 sec) 

mysql

mysql> create table test01(pk varchar(20) primary key);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into test01 values("11d"),("11wha?"),("11"),("12");
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select * from test01 where pk in ("11");
+----+
| pk |
+----+
| 11 |
+----+
1 row in set (0.00 sec)

mysql> select * from test01 where pk in (11);
+--------+
| pk     |
+--------+
| 11     |
| 11d    |
| 11wha? |
+--------+
3 rows in set, 2 warnings (0.00 sec)

[angelamayxie]

dolt

tmp/main*> create table test02(pk int primary key);
Query OK, 0 rows affected (0.01 sec)
tmp/main*> insert into test02 values(11), (12), (13), (14),(15);
Query OK, 5 rows affected (0.01 sec)
tmp/main*> select pk from test02 where pk in ('11asd');
error: '11asd' is not a valid value for 'int'

mysql

mysql> create table test02(pk int primary key);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into test02 values(11),(12),(13),(14),(15);
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> select pk from test02 where pk in ('11asd');
+----+
| pk |
+----+
| 11 |
+----+
1 row in set, 1 warning (0.00 sec)

mysql> select pk from test02 where pk='11asdf';
+----+
| pk |
+----+
| 11 |
+----+
1 row in set, 1 warning (0.00 sec)

[angelamayxie]

mysql

mysql> select pk from test02 where pk='11.12asdf';
Empty set, 1 warning (0.00 sec)

[angelamayxie]

seems like numbers in mysql are casted to floats

mysql> select pk from test01 where pk=3;
+-----------+
| pk        |
+-----------+
|   3 12 4  |
|   3. 12 4 |
| 3. 12 4   |
+-----------+

mysql> select pk from test01 where pk>=3 and pk < 4;
+------------+
| pk         |
+------------+
|   3 12 4   |
|   3. 12 4  |
|   3.2 12 4 |
| +3.1234    |
| 3. 12 4    |
+------------+
5 rows in set, 27 warnings (0.00 sec)

[angelamayxie]

Looks like for filtering primary keys, we need to make a lot of changes to how we build indexed table access

[angelamayxie]

note that mysql has warnings for string truncation