Uploaded image for project: 'Percona Server for MySQL'
  1. Percona Server for MySQL
  2. PS-5024

query returns incorrect result for table with no PK and non-unique CHAR utf8mb4 SK

    XMLWordPrintable

    Details

      Description

      mysql> CREATE TABLE t1 (cola char(3) not null, colb char(3) not null, filler char(200), key ka(cola), key kb(colb)) default charset utf8mb4 engine=rocksdb;
      Query OK, 0 rows affected, 2 warnings (0.01 sec)
      
      mysql> INSERT INTO t1(cola,colb,filler) VALUES('aaa','aaa', 'Z1'), ('aaa','aaa', 'Z2'), ('aaa','aaa', 'Z3'), ('bbb','bbb', 'Z4');
      Query OK, 4 rows affected (0.01 sec)
      Records: 4  Duplicates: 0  Warnings: 0
      
      mysql> SELECT COUNT(*) FROM t1;
      +----------+
      | COUNT(*) |
      +----------+
      |        4 |
      +----------+
      1 row in set (0.00 sec)
      
      mysql> SELECT * FROM t1;
      +------+------+--------+
      | cola | colb | filler |
      +------+------+--------+
      | aaa  | aaa  | Z1     |
      | aaa  | aaa  | Z2     |
      | aaa  | aaa  | Z3     |
      | bbb  | bbb  | Z4     |
      +------+------+--------+
      4 rows in set (0.00 sec)
      
      mysql> SELECT COUNT(*) FROM t1 WHERE cola = 'aaa';
      +----------+
      | COUNT(*) |
      +----------+
      |        3 |
      +----------+
      1 row in set (0.00 sec)
      
      mysql> SELECT * FROM t1 WHERE cola= 'aaa';
      +------+------+--------+
      | cola | colb | filler |
      +------+------+--------+
      | aaa  | aaa  | Z1     |
      | aaa  | aaa  | Z2     |
      | aaa  | aaa  | Z3     |
      +------+------+--------+
      3 rows in set (0.00 sec)
      
      mysql> SELECT COUNT(*) FROM t1 WHERE cola <> 'aaa';
      +----------+
      | COUNT(*) |
      +----------+
      |        1 |
      +----------+
      1 row in set (0.00 sec)
      
      mysql> SELECT * FROM t1 WHERE cola <> 'aaa';
      +------+------+--------+
      | cola | colb | filler |
      +------+------+--------+
      | aaa  | aaa  | Z1     |    <<<< !!!! WRONG RESULT !!!!
      +------+------+--------+
      1 row in set (0.00 sec)
      
      mysql> SELECT * FROM t1 FORCE INDEX(kb) WHERE cola <> 'aaa';
      +------+------+--------+
      | cola | colb | filler |
      +------+------+--------+
      | bbb  | bbb  | Z4     |
      +------+------+--------+
      1 row in set (0.00 sec)
      

        Smart Checklist

          Attachments

            Issue Links

              Activity

                People

                Assignee:
                kamil.holubicki Kamil Holubicki
                Reporter:
                george.lorch George Lorch
                Votes:
                0 Vote for this issue
                Watchers:
                6 Start watching this issue

                  Dates

                  Created:
                  Updated:
                  Resolved:

                    Time Tracking

                    Estimated:
                    Original Estimate - 0 minutes
                    0m
                    Remaining:
                    Remaining Estimate - 0 minutes
                    0m
                    Logged:
                    Time Spent - 1 week, 1 day, 5 hours, 7 minutes
                    1w 1d 5h 7m