Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-8688

Wrong result for SELECT..WHERE varchar_column IN (1,2,3) AND varchar_column=' 1';

        Details

        • Type: Bug
        • Status: Closed
        • Priority: Major
        • Resolution: Fixed
        • Affects Version/s: 5.1.67, 5.2.14, 5.3.12, 10.1, 10.0, 5.5
        • Fix Version/s: 10.1.7
        • Component/s: Optimizer

          Description

          This script:

          DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a VARCHAR(10) CHARACTER SET cp1251 COLLATE cp1251_ukrainian_ci);
INSERT INTO t1 VALUES (' 1'),('`1');
SELECT * FROM t1 WHERE a IN (1,2,3);

          returns one row:

          +------+
| a    |
+------+
|  1   |
+------+

          Now if I make the condition even stroger:

          SELECT * FROM t1 WHERE a IN (1,2,3) AND a=' 1';

          It erroneously returns two rows:

          +------+
| a    |
+------+
|  1   |
| `1   |
+------+

          It should return one row.

          The problem happens in equal fields propagation in this code:

            if (!item || !has_compatible_context(item))
    item= this;

          Item_func_in does not set cmp_type of args[0], so has_compatible_context() passes and the field gets erroneously replaced to the string constant which makes Item_func_in::val_int() always evaluate to TRUE.

            Gliffy Diagrams

              Attachments

                Issue Links

                blocks

                Bug - A problem which impairs or prevents the functions of the product. MDEV-8728 Fix a number of problems in equal field and equal expression propagation

                • Critical - Crashes, loss of data, severe memory leak.
                • Closed

                  Activity

                  Hide
                  Permalink
                  bar Alexander Barkov added a comment -

                  Another example script:

                  SET NAMES utf8;
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a VARCHAR(10) CHARACTER SET latin1);
INSERT INTO t1 VALUES ('1e1'),('1ë1');
SELECT * FROM t1 WHERE a IN (1,2);

                  returns one row:

                  +------+
| a    |
+------+
| 1ë1  |
+------+

                  Now if I make the condition even stronger:

                  SELECT * FROM t1 WHERE a IN (1,2) AND a='1ë1';

                  it erroneously returns two rows:

                  +------+
| a    |
+------+
| 1e1  |
| 1ë1  |
+------+
                  Show
                  bar Alexander Barkov added a comment - Another example script: SET NAMES utf8; DROP TABLE IF EXISTS t1; CREATE TABLE t1 (a VARCHAR(10) CHARACTER SET latin1); INSERT INTO t1 VALUES ('1e1'),('1ë1'); SELECT * FROM t1 WHERE a IN (1,2); returns one row: +------+ | a | +------+ | 1ë1 | +------+ Now if I make the condition even stronger: SELECT * FROM t1 WHERE a IN (1,2) AND a='1ë1'; it erroneously returns two rows: +------+ | a | +------+ | 1e1 | | 1ë1 | +------+

                    People

                    • Assignee:
                      bar Alexander Barkov
                      Reporter:
                      bar Alexander Barkov
                    • Votes:
                      0 Vote for this issue
                      Watchers:
                      1 Start watching this issue

                      Dates

                      • Created:
                        Updated:
                        Resolved: