Adding a new column to a table changes the output of select...like statement

[tplavcic]

Although this possibly isn't a MyRocks bug but InnoDB I'll report it here first because it is a difference in behavior.

Here's the example that if I add one column to a table the output of a select...like statement is different regardless of the fact that that column is not even used in the select (this is at least true for 5.6 innodb, but then 5.6 MyRocks and 5.7 InnoDB have completely different output).

1. FB MyRocks returns 4 rows every time
2. FB InnoDB returns 4 rows before adding a column and 2 rows after adding
3. MySQL 5.6.27 same as above
4. MySQL 5.7.13 returns 2 rows regardless of the alter statement

In my opinion number 1 is most correct but maybe I'm wrong.

FB MyRocks:

mysql> DROP DATABASE test;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE DATABASE test DEFAULT CHARACTER SET="utf8" DEFAULT COLLATE="utf8_bin";
Query OK, 1 row affected (0.00 sec)

mysql> USE test;
Database changed
mysql> create table t1 (v varchar(254), index (v));
Query OK, 0 rows affected (0.16 sec)

mysql> INSERT INTO t1 VALUES ('a'),('a\0'),('a\t'),('a ');
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select * FROM t1 where v like 'a%';
+------+
| v    |
+------+
| a    |
| a    |
| a        |
| a    |
+------+
4 rows in set (0.00 sec)

mysql> ALTER TABLE t1 ADD v2 varchar(5) NOT NULL;
Query OK, 4 rows affected (0.20 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select * FROM t1 where v like 'a%';
+------+----+
| v    | v2 |
+------+----+
| a    |    |
| a    |    |
| a        |    |
| a    |    |
+------+----+
4 rows in set (0.00 sec)

FB InnoDB:

mysql> DROP DATABASE test;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE DATABASE test DEFAULT CHARACTER SET="utf8" DEFAULT COLLATE="utf8_bin";
Query OK, 1 row affected (0.00 sec)

mysql> USE test;
Database changed
mysql> create table t1 (v varchar(254), index (v));
Query OK, 0 rows affected (0.32 sec)

mysql> INSERT INTO t1 VALUES ('a'),('a\0'),('a\t'),('a ');
Query OK, 4 rows affected (0.04 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select * FROM t1 where v like 'a%';
+------+
| v    |
+------+
| a    |
| a        |
| a    |
| a    |
+------+
4 rows in set (0.00 sec)

mysql> ALTER TABLE t1 ADD v2 varchar(5) NOT NULL;
Query OK, 0 rows affected (0.50 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select * FROM t1 where v like 'a%';
+------+----+
| v    | v2 |
+------+----+
| a    |    |
| a    |    |
+------+----+
2 rows in set (0.00 sec)

MySQL 5.6.27:

mysql [localhost] {msandbox} ((none)) > DROP DATABASE test;
Query OK, 1 row affected (0,02 sec)

mysql [localhost] {msandbox} ((none)) > CREATE DATABASE test DEFAULT CHARACTER SET="utf8" DEFAULT COLLATE="utf8_bin";
Query OK, 1 row affected (0,00 sec)

mysql [localhost] {msandbox} ((none)) > USE test;
Database changed
mysql [localhost] {msandbox} (test) > create table t1 (v varchar(254), index (v));
Query OK, 0 rows affected (0,03 sec)

mysql [localhost] {msandbox} (test) > INSERT INTO t1 VALUES ('a'),('a\0'),('a\t'),('a ');
Query OK, 4 rows affected (0,01 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql [localhost] {msandbox} (test) > select * FROM t1 where v like 'a%';
+------+
| v    |
+------+
| a    |
| a        |
| a    |
| a    |
+------+
4 rows in set (0,00 sec)

mysql [localhost] {msandbox} (test) > ALTER TABLE t1 ADD v2 varchar(5) NOT NULL;
Query OK, 0 rows affected (0,07 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql [localhost] {msandbox} (test) > select * FROM t1 where v like 'a%';
+------+----+
| v    | v2 |
+------+----+
| a    |    |
| a    |    |
+------+----+
2 rows in set (0,00 sec)

MySQL 5.7.13:

mysql [localhost] {msandbox} ((none)) > DROP DATABASE test;
Query OK, 1 row affected (0,03 sec)

mysql [localhost] {msandbox} ((none)) > CREATE DATABASE test DEFAULT CHARACTER SET="utf8" DEFAULT COLLATE="utf8_bin";
Query OK, 1 row affected (0,00 sec)

mysql [localhost] {msandbox} ((none)) > USE test;
Database changed
mysql [localhost] {msandbox} (test) > create table t1 (v varchar(254), index (v));
Query OK, 0 rows affected (0,04 sec)

mysql [localhost] {msandbox} (test) > INSERT INTO t1 VALUES ('a'),('a\0'),('a\t'),('a ');
Query OK, 4 rows affected (0,00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql [localhost] {msandbox} (test) > select * FROM t1 where v like 'a%';
+------+
| v    |
+------+
| a    |
| a    |
+------+
2 rows in set (0,01 sec)

mysql [localhost] {msandbox} (test) > ALTER TABLE t1 ADD v2 varchar(5) NOT NULL;
Query OK, 0 rows affected (0,09 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql [localhost] {msandbox} (test) > select * FROM t1 where v like 'a%';
+------+----+
| v    | v2 |
+------+----+
| a    |    |
| a    |    |
+------+----+
2 rows in set (0,00 sec)

[lth]

I suspect this is related to #257 and the inconsistency will go way with this diff: https://reviews.facebook.net/D59553

If you do an "explain" on your queries, you'll find that the two selects before and after the alter are using different query plans. The first one just scans index v filtering on 'a%'. The second does an ICP. I think that we end up skipping 'a\0' and 'a\t' for ICP because we only look at rows that sort after 'a'.

Currently in MyRocks, 'a' always sorts first, so we get all 4 rows. After the above diff, 'a' will sort after 'a\0' and 'a\t' so I think we'll have matching behaviour then.

There's definitely an upstream bug here, but I'm not sure if we want to do anything about it.

[lth]

Just checked that we're getting matching behaviour now that https://reviews.facebook.net/D59553 has landed.