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

Wrong result for SELECT..WHERE derived_table_column='a' AND derived_table_column<>_latin1'A' COLLATE latin1_bin

Details

    Description

      This script:

      DROP TABLE IF EXISTS t1;
      		 
      CREATE TABLE t1 (a VARCHAR(10)) ENGINE=MyISAM;
      		 
      INSERT INTO t1 VALUES ('a'),('A');
      		 
      SELECT * FROM t1 WHERE a='a' AND a <> _latin1'A' COLLATE latin1_bin;

      correctly returns one row:

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

      If I rewrite the query to use a derived table:

      DROP TABLE IF EXISTS t1;
      		 
      CREATE TABLE t1 (a VARCHAR(10)) ENGINE=MyISAM;
      		 
      INSERT INTO t1 VALUES ('a'),('A');
      		 
      SELECT * FROM (SELECT * FROM t1) AS table1 WHERE a='a' AND a <> _latin1'A' COLLATE latin1_bin;

      it erroneously returns two rows:

      +------+
      		 
      | a    |
      		 
      +------+
      		 
      | a    |
      		 
      | A    |
      		 
      +------+

      The problems happens because Item_direct_view_ref has cmp_context correctly set to STRING_RESULT, while its referenced field has cmp_context set to IMPOSSIBLE_RESULT. This makes equal field propagation replace the field to constant in <>.

      The same bug causes ENUM column to return bad results:

      DROP TABLE IF EXISTS t1;
      		 
      CREATE TABLE t1 (a ENUM('5','6')) ENGINE=MyISAM;
      		 
      INSERT INTO t1 VALUES ('5'),('6');
      		 
      SELECT * FROM (SELECT * FROM t1) AS table1 WHERE a='5';
      		 
      SELECT * FROM (SELECT * FROM t1) AS table1 WHERE a=1;
      		 
      SELECT * FROM (SELECT * FROM t1) AS table1 WHERE a='5' AND a=1;

      The first and the second query correctly return one row, equal to '5'. The third query erroneously returns empty set.

      The same problem is also demonstrated in this script:

      DROP TABLE IF EXISTS t1;
      		 
      DROP VIEW IF EXISTS v1;
      		 
      CREATE TABLE t1 (a varchar(10) character set cp1251 collate cp1251_ukrainian_ci, KEY (a)) ;
      		 
      INSERT INTO t1 VALUES ('DD'), ('ZZ'), ('ZZ'), ('KK'), ('FF'), ('HH'),('MM'),('`1');
      		 
      CREATE VIEW v1 AS SELECT * FROM t1;
      		 
      SELECT * FROM t1 WHERE a <> 0 AND a = ' 1';
      		 
      SELECT * FROM v1 WHERE a <> 0 AND a = ' 1';

      The first SELECT returns empty set, the second SELECT returns one row.

      The same problem is repeatable in this script:

      DROP TABLE IF EXISTS t1;
      		 
      CREATE TABLE t1 (a ENUM('5','6'));
      		 
      INSERT INTO t1 VALUES ('5'),('6');
      		 
      DROP VIEW IF EXISTS v1;
      		 
      CREATE VIEW v1 AS SELECT * FROM t1;
      		 
      SELECT * FROM t1 WHERE a='5' AND a<2;
      		 
      SELECT * FROM v1 WHERE a='5' AND a<2;

      The first SELECT correctly return one row, the second SELECT erroneously returns no rows.

      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
          relates to

          Bug - A problem which impairs or prevents the functions of the product. MDEV-8749 Wrong result for SELECT..WHERE derived_table_enum_column='number' AND derived_table_enum_column OP number2

          • Major - Major loss of function.
          • Closed

          Activity

            Ascending order - Click to sort in descending order
            bar Alexander Barkov created issue -
            bar Alexander Barkov made changes -
            Field Original Value New Value
            bar Alexander Barkov made changes -
            Description This script:
            {code}
            DROP TABLE IF EXISTS t1;
            CREATE TABLE t1 (a VARCHAR(10)) ENGINE=MyISAM;
            INSERT INTO t1 VALUES ('a'),('A');
            SELECT * FROM t1 WHERE a='a' AND a <> _latin1'A' COLLATE latin1_bin;
            {code}
            correctly returns one row:
            {noformat}
            +------+
            | a |
            +------+
            | a |
            +------+
            {noformat}


            If I rewrite the query to use a derived table:
            {code}
            DROP TABLE IF EXISTS t1;
            CREATE TABLE t1 (a VARCHAR(10)) ENGINE=MyISAM;
            INSERT INTO t1 VALUES ('a'),('A');
            SELECT * FROM (SELECT * FROM t1) AS table1 WHERE a='a' AND a <> _latin1'A' COLLATE latin1_bin;
            {code}
            it erroneously returns two rows:
            {noformat}
            +------+
            | a |
            +------+
            | a |
            | A |
            +------+
            {noformat}
            		 This script:
            {code}
            DROP TABLE IF EXISTS t1;
            CREATE TABLE t1 (a VARCHAR(10)) ENGINE=MyISAM;
            INSERT INTO t1 VALUES ('a'),('A');
            SELECT * FROM t1 WHERE a='a' AND a <> _latin1'A' COLLATE latin1_bin;
            {code}
            correctly returns one row:
            {noformat}
            +------+
            | a |
            +------+
            | a |
            +------+
            {noformat}


            If I rewrite the query to use a derived table:
            {code}
            DROP TABLE IF EXISTS t1;
            CREATE TABLE t1 (a VARCHAR(10)) ENGINE=MyISAM;
            INSERT INTO t1 VALUES ('a'),('A');
            SELECT * FROM (SELECT * FROM t1) AS table1 WHERE a='a' AND a <> _latin1'A' COLLATE latin1_bin;
            {code}
            it erroneously returns two rows:
            {noformat}
            +------+
            | a |
            +------+
            | a |
            | A |
            +------+
            {noformat}

            The problems happens because Item_direct_view_ref has cmp_context correctly set to STRING_RESULT, while its referenced field has cmp_context set to IMPOSSIBLE_RESULT. This makes equal field propagation replace the field to constant in <>.
            bar Alexander Barkov made changes -
            Description This script:
            {code}
            DROP TABLE IF EXISTS t1;
            CREATE TABLE t1 (a VARCHAR(10)) ENGINE=MyISAM;
            INSERT INTO t1 VALUES ('a'),('A');
            SELECT * FROM t1 WHERE a='a' AND a <> _latin1'A' COLLATE latin1_bin;
            {code}
            correctly returns one row:
            {noformat}
            +------+
            | a |
            +------+
            | a |
            +------+
            {noformat}


            If I rewrite the query to use a derived table:
            {code}
            DROP TABLE IF EXISTS t1;
            CREATE TABLE t1 (a VARCHAR(10)) ENGINE=MyISAM;
            INSERT INTO t1 VALUES ('a'),('A');
            SELECT * FROM (SELECT * FROM t1) AS table1 WHERE a='a' AND a <> _latin1'A' COLLATE latin1_bin;
            {code}
            it erroneously returns two rows:
            {noformat}
            +------+
            | a |
            +------+
            | a |
            | A |
            +------+
            {noformat}

            The problems happens because Item_direct_view_ref has cmp_context correctly set to STRING_RESULT, while its referenced field has cmp_context set to IMPOSSIBLE_RESULT. This makes equal field propagation replace the field to constant in <>.
            		 This script:
            {code}
            DROP TABLE IF EXISTS t1;
            CREATE TABLE t1 (a VARCHAR(10)) ENGINE=MyISAM;
            INSERT INTO t1 VALUES ('a'),('A');
            SELECT * FROM t1 WHERE a='a' AND a <> _latin1'A' COLLATE latin1_bin;
            {code}
            correctly returns one row:
            {noformat}
            +------+
            | a |
            +------+
            | a |
            +------+
            {noformat}


            If I rewrite the query to use a derived table:
            {code}
            DROP TABLE IF EXISTS t1;
            CREATE TABLE t1 (a VARCHAR(10)) ENGINE=MyISAM;
            INSERT INTO t1 VALUES ('a'),('A');
            SELECT * FROM (SELECT * FROM t1) AS table1 WHERE a='a' AND a <> _latin1'A' COLLATE latin1_bin;
            {code}
            it erroneously returns two rows:
            {noformat}
            +------+
            | a |
            +------+
            | a |
            | A |
            +------+
            {noformat}

            The problems happens because Item_direct_view_ref has cmp_context correctly set to STRING_RESULT, while its referenced field has cmp_context set to IMPOSSIBLE_RESULT. This makes equal field propagation replace the field to constant in <>.

            The same bug causes ENUM column to return bad results:
            {code}
            DROP TABLE IF EXISTS t1;
            CREATE TABLE t1 (a ENUM('5','6')) ENGINE=MyISAM;
            INSERT INTO t1 VALUES ('5'),('6');
            SELECT * FROM (SELECT * FROM t1) AS table1 WHERE a='5';
            SELECT * FROM (SELECT * FROM t1) AS table1 WHERE a=1;
            SELECT * FROM (SELECT * FROM t1) AS table1 WHERE a='5' AND a=1;
            {code}
            The first and the second query correctly return one row, equal to '5'. The third query erroneously returns empty set.
            bar Alexander Barkov made changes -
            Description This script:
            {code}
            DROP TABLE IF EXISTS t1;
            CREATE TABLE t1 (a VARCHAR(10)) ENGINE=MyISAM;
            INSERT INTO t1 VALUES ('a'),('A');
            SELECT * FROM t1 WHERE a='a' AND a <> _latin1'A' COLLATE latin1_bin;
            {code}
            correctly returns one row:
            {noformat}
            +------+
            | a |
            +------+
            | a |
            +------+
            {noformat}


            If I rewrite the query to use a derived table:
            {code}
            DROP TABLE IF EXISTS t1;
            CREATE TABLE t1 (a VARCHAR(10)) ENGINE=MyISAM;
            INSERT INTO t1 VALUES ('a'),('A');
            SELECT * FROM (SELECT * FROM t1) AS table1 WHERE a='a' AND a <> _latin1'A' COLLATE latin1_bin;
            {code}
            it erroneously returns two rows:
            {noformat}
            +------+
            | a |
            +------+
            | a |
            | A |
            +------+
            {noformat}

            The problems happens because Item_direct_view_ref has cmp_context correctly set to STRING_RESULT, while its referenced field has cmp_context set to IMPOSSIBLE_RESULT. This makes equal field propagation replace the field to constant in <>.

            The same bug causes ENUM column to return bad results:
            {code}
            DROP TABLE IF EXISTS t1;
            CREATE TABLE t1 (a ENUM('5','6')) ENGINE=MyISAM;
            INSERT INTO t1 VALUES ('5'),('6');
            SELECT * FROM (SELECT * FROM t1) AS table1 WHERE a='5';
            SELECT * FROM (SELECT * FROM t1) AS table1 WHERE a=1;
            SELECT * FROM (SELECT * FROM t1) AS table1 WHERE a='5' AND a=1;
            {code}
            The first and the second query correctly return one row, equal to '5'. The third query erroneously returns empty set.
            		 This script:
            {code}
            DROP TABLE IF EXISTS t1;
            CREATE TABLE t1 (a VARCHAR(10)) ENGINE=MyISAM;
            INSERT INTO t1 VALUES ('a'),('A');
            SELECT * FROM t1 WHERE a='a' AND a <> _latin1'A' COLLATE latin1_bin;
            {code}
            correctly returns one row:
            {noformat}
            +------+
            | a |
            +------+
            | a |
            +------+
            {noformat}


            If I rewrite the query to use a derived table:
            {code}
            DROP TABLE IF EXISTS t1;
            CREATE TABLE t1 (a VARCHAR(10)) ENGINE=MyISAM;
            INSERT INTO t1 VALUES ('a'),('A');
            SELECT * FROM (SELECT * FROM t1) AS table1 WHERE a='a' AND a <> _latin1'A' COLLATE latin1_bin;
            {code}
            it erroneously returns two rows:
            {noformat}
            +------+
            | a |
            +------+
            | a |
            | A |
            +------+
            {noformat}

            The problems happens because Item_direct_view_ref has cmp_context correctly set to STRING_RESULT, while its referenced field has cmp_context set to IMPOSSIBLE_RESULT. This makes equal field propagation replace the field to constant in <>.

            The same bug causes ENUM column to return bad results:
            {code}
            DROP TABLE IF EXISTS t1;
            CREATE TABLE t1 (a ENUM('5','6')) ENGINE=MyISAM;
            INSERT INTO t1 VALUES ('5'),('6');
            SELECT * FROM (SELECT * FROM t1) AS table1 WHERE a='5';
            SELECT * FROM (SELECT * FROM t1) AS table1 WHERE a=1;
            SELECT * FROM (SELECT * FROM t1) AS table1 WHERE a='5' AND a=1;
            {code}
            The first and the second query correctly return one row, equal to '5'. The third query erroneously returns empty set.

            The same problem is also demonstrated in this script:
            {code}
            DROP TABLE IF EXISTS t1;
            DROP VIEW IF EXISTS v1;
            CREATE TABLE t1 (a varchar(10) character set cp1251 collate cp1251_ukrainian_ci, KEY (a)) ;
            INSERT INTO t1 VALUES ('DD'), ('ZZ'), ('ZZ'), ('KK'), ('FF'), ('HH'),('MM'),('`1');
            CREATE VIEW v1 AS SELECT * FROM t1;
            SELECT * FROM t1 WHERE a <> 0 AND a = ' 1';
            SELECT * FROM v1 WHERE a <> 0 AND a = ' 1';
            {code}
            The first SELECT returns empty set, the second SELECT returns one row.
            bar Alexander Barkov made changes -
            Description This script:
            {code}
            DROP TABLE IF EXISTS t1;
            CREATE TABLE t1 (a VARCHAR(10)) ENGINE=MyISAM;
            INSERT INTO t1 VALUES ('a'),('A');
            SELECT * FROM t1 WHERE a='a' AND a <> _latin1'A' COLLATE latin1_bin;
            {code}
            correctly returns one row:
            {noformat}
            +------+
            | a |
            +------+
            | a |
            +------+
            {noformat}


            If I rewrite the query to use a derived table:
            {code}
            DROP TABLE IF EXISTS t1;
            CREATE TABLE t1 (a VARCHAR(10)) ENGINE=MyISAM;
            INSERT INTO t1 VALUES ('a'),('A');
            SELECT * FROM (SELECT * FROM t1) AS table1 WHERE a='a' AND a <> _latin1'A' COLLATE latin1_bin;
            {code}
            it erroneously returns two rows:
            {noformat}
            +------+
            | a |
            +------+
            | a |
            | A |
            +------+
            {noformat}

            The problems happens because Item_direct_view_ref has cmp_context correctly set to STRING_RESULT, while its referenced field has cmp_context set to IMPOSSIBLE_RESULT. This makes equal field propagation replace the field to constant in <>.

            The same bug causes ENUM column to return bad results:
            {code}
            DROP TABLE IF EXISTS t1;
            CREATE TABLE t1 (a ENUM('5','6')) ENGINE=MyISAM;
            INSERT INTO t1 VALUES ('5'),('6');
            SELECT * FROM (SELECT * FROM t1) AS table1 WHERE a='5';
            SELECT * FROM (SELECT * FROM t1) AS table1 WHERE a=1;
            SELECT * FROM (SELECT * FROM t1) AS table1 WHERE a='5' AND a=1;
            {code}
            The first and the second query correctly return one row, equal to '5'. The third query erroneously returns empty set.

            The same problem is also demonstrated in this script:
            {code}
            DROP TABLE IF EXISTS t1;
            DROP VIEW IF EXISTS v1;
            CREATE TABLE t1 (a varchar(10) character set cp1251 collate cp1251_ukrainian_ci, KEY (a)) ;
            INSERT INTO t1 VALUES ('DD'), ('ZZ'), ('ZZ'), ('KK'), ('FF'), ('HH'),('MM'),('`1');
            CREATE VIEW v1 AS SELECT * FROM t1;
            SELECT * FROM t1 WHERE a <> 0 AND a = ' 1';
            SELECT * FROM v1 WHERE a <> 0 AND a = ' 1';
            {code}
            The first SELECT returns empty set, the second SELECT returns one row.
            		 This script:
            {code}
            DROP TABLE IF EXISTS t1;
            CREATE TABLE t1 (a VARCHAR(10)) ENGINE=MyISAM;
            INSERT INTO t1 VALUES ('a'),('A');
            SELECT * FROM t1 WHERE a='a' AND a <> _latin1'A' COLLATE latin1_bin;
            {code}
            correctly returns one row:
            {noformat}
            +------+
            | a |
            +------+
            | a |
            +------+
            {noformat}


            If I rewrite the query to use a derived table:
            {code}
            DROP TABLE IF EXISTS t1;
            CREATE TABLE t1 (a VARCHAR(10)) ENGINE=MyISAM;
            INSERT INTO t1 VALUES ('a'),('A');
            SELECT * FROM (SELECT * FROM t1) AS table1 WHERE a='a' AND a <> _latin1'A' COLLATE latin1_bin;
            {code}
            it erroneously returns two rows:
            {noformat}
            +------+
            | a |
            +------+
            | a |
            | A |
            +------+
            {noformat}

            The problems happens because Item_direct_view_ref has cmp_context correctly set to STRING_RESULT, while its referenced field has cmp_context set to IMPOSSIBLE_RESULT. This makes equal field propagation replace the field to constant in <>.

            The same bug causes ENUM column to return bad results:
            {code}
            DROP TABLE IF EXISTS t1;
            CREATE TABLE t1 (a ENUM('5','6')) ENGINE=MyISAM;
            INSERT INTO t1 VALUES ('5'),('6');
            SELECT * FROM (SELECT * FROM t1) AS table1 WHERE a='5';
            SELECT * FROM (SELECT * FROM t1) AS table1 WHERE a=1;
            SELECT * FROM (SELECT * FROM t1) AS table1 WHERE a='5' AND a=1;
            {code}
            The first and the second query correctly return one row, equal to '5'. The third query erroneously returns empty set.

            The same problem is also demonstrated in this script:
            {code}
            DROP TABLE IF EXISTS t1;
            DROP VIEW IF EXISTS v1;
            CREATE TABLE t1 (a varchar(10) character set cp1251 collate cp1251_ukrainian_ci, KEY (a)) ;
            INSERT INTO t1 VALUES ('DD'), ('ZZ'), ('ZZ'), ('KK'), ('FF'), ('HH'),('MM'),('`1');
            CREATE VIEW v1 AS SELECT * FROM t1;
            SELECT * FROM t1 WHERE a <> 0 AND a = ' 1';
            SELECT * FROM v1 WHERE a <> 0 AND a = ' 1';
            {code}
            The first SELECT returns empty set, the second SELECT returns one row.

            The same problem is repeatable in this script:
            {code}
            DROP TABLE IF EXISTS t1;
            CREATE TABLE t1 (a ENUM('5','6'));
            INSERT INTO t1 VALUES ('5'),('6');
            DROP VIEW IF EXISTS v1;
            CREATE VIEW v1 AS SELECT * FROM t1;
            SELECT * FROM t1 WHERE a='5' AND a<2;
            SELECT * FROM v1 WHERE a='5' AND a<2;
            {code}
            The first SELECT correctly return one row, the second SELECT erroneously returns no rows.
            bar Alexander Barkov made changes -
            bar Alexander Barkov made changes -
            Status Open [ 1 ] In Progress [ 3 ]
            bar Alexander Barkov made changes -
            Fix Version/s 10.1.7 [ 19604 ]
            Fix Version/s 10.1 [ 16100 ]
            Resolution Fixed [ 1 ]
            Status In Progress [ 3 ] Closed [ 6 ]
            bar Alexander Barkov made changes -
            Labels propagation
            bar Alexander Barkov made changes -
            Labels propagation propagation upstream
            bar Alexander Barkov added a comment -

            MySQL-5.7.8 returns a wrong result for this script:

            DROP TABLE IF EXISTS t1;
            		 
            CREATE TABLE t1 (a VARCHAR(10)) ENGINE=MyISAM;
            		 
            INSERT INTO t1 VALUES ('a'),('A');
            		 
            SELECT * FROM t1 WHERE a='a' AND a <> _latin1'A' COLLATE latin1_bin;
            		 

            +------+
            		 
            | a    |
            		 
            +------+
            		 
            | a    |
            		 
            | A    |
            		 
            +------+

            The correct result is to return one row only.

            bar Alexander Barkov added a comment - MySQL-5.7.8 returns a wrong result for this script: DROP TABLE IF EXISTS t1; CREATE TABLE t1 (a VARCHAR(10)) ENGINE=MyISAM; INSERT INTO t1 VALUES ('a'),('A'); SELECT * FROM t1 WHERE a='a' AND a <> _latin1'A' COLLATE latin1_bin; +------+ | a | +------+ | a | | A | +------+ The correct result is to return one row only.
            serg Sergei Golubchik made changes -
            Workflow MariaDB v3 [ 71352 ] MariaDB v4 [ 149570 ]

            People

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

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.