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:
      None

      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

              • Assignee:
                george.lorch George Lorch
                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 - 1 day, 5 hours, 37 minutes
                  1d 5h 37m