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

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

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Open
    • Priority: High
    • Resolution: Unresolved
    • Affects Version/s: Not 5.7, 8.0.12-2rc1
    • Fix Version/s: None
    • Component/s: MyRocks
    • Labels:

      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:
                  Unassigned
                  Reporter:
                  george.lorch George Lorch
                • Votes:
                  0 Vote for this issue
                  Watchers:
                  4 Start watching this issue

                  Dates

                  • Created:
                    Updated:

                    Time Tracking

                    Estimated:
                    Original Estimate - Not Specified
                    Not Specified
                    Remaining:
                    Remaining Estimate - 0 minutes
                    0m
                    Logged:
                    Time Spent - 2 days, 6 hours, 22 minutes
                    2d 6h 22m