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

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)
      

      Attachments

        Issue Links

          Activity

            People

              kamil.holubicki Kamil Holubicki
              george.lorch George Lorch (Inactive)
              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

                  Smart Checklist