MS Bug 83435 behaves differently on PS

[Roel Van de Paar]

The testcase in http://bugs.mysql.com/bug.php?id=83435 namely

DROP DATABASE test;CREATE DATABASE test;USE test;
SET @@session.default_storage_engine=MYISAM;
CREATE TABLE t1(c1 INT,c2 CHAR)PARTITION BY LINEAR KEY(c1) PARTITIONS 99;
CREATE UNIQUE INDEX i1 ON t1(c1);
insert INTO t1 values(unix_timestamp('2004-10-31 01:00:00'),'2004-10-31 01:00:00'),(unix_timestamp('2004-10-31 02:00:00'),'2004-10-31 02:00:00'),(unix_timestamp('2004-10-31 02:59:59'),'2004-10-31 02:59:59'),(unix_timestamp('2004-10-31 04:00:00'),'2004-10-31 04:00:00'),(unix_timestamp('2004-10-31 02:59:59'),'2004-10-31 02:59:59');
insert INTO t1 values(0,'2004-01-01 00:00:00'),(0,'2004-01-01 01:00:00'),(0,'2004-02-01 00:00:00');
alter table t1 engine=InnoDB;
SELECT * FROM t1 WHERE c1<'1971-01-01 00:00:01' ORDER BY c1 DESC LIMIT 2;

When executed against PS gives this;

mysql> insert INTO t1 values(unix_timestamp('2004-10-31 01:00:00'),'2004-10-31 01:00:00'),(unix_timestamp('2004-10-31 02:00:00'),'2004-10-31 02:00:00'),(unix_timestamp('2004-10-31 02:59:59'),'2004-10-31 02:59:59'),(unix_timestamp('2004-10-31 04:00:00'),'2004-10-31 04:00:00'),(unix_timestamp('2004-10-31 02:59:59'),'2004-10-31 02:59:59');
ERROR 1062 (23000): Duplicate entry '1099152000' for key 'i1'
mysql> insert INTO t1 values(0,'2004-01-01 00:00:00'),(0,'2004-01-01 01:00:00'),(0,'2004-02-01 00:00:00');
ERROR 1062 (23000): Duplicate entry '0' for key 'i1'
mysql> alter table t1 engine=InnoDB;

Query OK, 3 rows affected (10.76 sec)
Records: 3 Duplicates: 0 Warnings: 0

mysql> SELECT * FROM t1 WHERE c1<'1971-01-01 00:00:01' ORDER BY c1 DESC LIMIT 2;
+------+------+
| c1 | c2 |
+------+------+
| 0 | 2 |
+------+------+
1 row in set, 4 warnings (0.04 sec)

Which is twice different from MS: the ALTER takes 3+ seconds longer AND the second query gives a completely different result.

[Roel Van de Paar (roel11)]

TokuDB:

mysql> insert INTO t1 values(unix_timestamp('2004-10-31 01:00:00'),'2004-10-31 01:00:00'),(unix_timestamp('2004-10-31 02:00:00'),'2004-10-31 02:00:00'),(unix_timestamp('2004-10-31 02:59:59'),'2004-10-31 02:59:59'),(unix_timestamp('2004-10-31 04:00:00'),'2004-10-31 04:00:00'),(unix_timestamp('2004-10-31 02:59:59'),'2004-10-31 02:59:59');
ERROR 1062 (23000): Duplicate entry '1099152000' for key 'i1'
mysql> insert INTO t1 values(0,'2004-01-01 00:00:00'),(0,'2004-01-01 01:00:00'),(0,'2004-02-01 00:00:00');
ERROR 1062 (23000): Duplicate entry '0' for key 'i1'
mysql> alter table t1 engine=TokuDB;

Query OK, 3 rows affected (21.16 sec)
Records: 3 Duplicates: 0 Warnings: 0

mysql> SELECT * FROM t1 WHERE c1<'1971-01-01 00:00:01' ORDER BY c1 DESC LIMIT 2;
+------+------+
| c1 | c2 |
+------+------+
| 0 | 2 |
+------+------+
1 row in set, 4 warnings (0.97 sec)

[Roel Van de Paar (roel11)]

Also see https://tokutek.atlassian.net/browse/DB-1025 - this can be made to crash on TokuDB using engine=TokuDB and adding --sql_mode=ONLY_FULL_GROUP_BY --plugin-load-add=tokudb=ha_tokudb.so while using C-API (i.e. pquery)

[Roel Van de Paar (roel11)]

MS 5.6 also produces 0|2 outcome. Ref http://bugs.mysql.com/bug.php?id=83435

[Roel Van de Paar (roel11)]

While the delay is present in 5.6, there is a new regression in 5.7 (query outcome differs), or 5.6 was never correct.

[Laurynas Biveinis (laurynas-biveinis)]

Roel, which PS version above? Also please separate different query result bug from the slow statement bug in the upstream

[Roel Van de Paar (roel11)]

percona-server-5.7.15-7-linux-x86_64-debug

[Shahriyar Rzayev (rzayev-sehriyar)]

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PS-1022