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

Wrong result on character comparison inside a view

    XMLWordPrintable

Details

    • Bug
    • Status: Confirmed (View Workflow)
    • Minor
    • Resolution: Unresolved
    • 10.0, 10.1
    • 10.1
    • Character Sets
    • None

    Description

      Test case

      CREATE TABLE t1 (fld VARCHAR(1) CHARSET utf8);
      INSERT INTO t1 VALUES ('x'),('y');
       
      SELECT * FROM t1 WHERE fld BETWEEN 'a' AND CHAR(222);
       
      CREATE OR REPLACE VIEW v1 AS
        SELECT * FROM t1 WHERE fld BETWEEN 'a' AND CHAR(222);
      SELECT * FROM v1; 
       
      DROP VIEW v1;
      DROP TABLE t1;
      

      Direct SELECT returns 2 rows, while the same query inside a view produces an empty result set with a warning:

      Result

      MariaDB [test]> SELECT * FROM t1 WHERE fld BETWEEN 'a' AND CHAR(222);
      +------+
      | fld  |
      +------+
      | x    |
      | y    |
      +------+
      2 rows in set (0.00 sec)
       
      MariaDB [test]> 
      MariaDB [test]> CREATE OR REPLACE VIEW v1 AS
          ->   SELECT * FROM t1 WHERE fld BETWEEN 'a' AND CHAR(222);
      Query OK, 0 rows affected (0.00 sec)
       
      MariaDB [test]> 
      MariaDB [test]> SELECT * FROM v1; 
      Empty set, 1 warning (0.00 sec)
       
      MariaDB [test]> SHOW WARNINGS;
      +---------+------+-------------------------------------+
      | Level   | Code | Message                             |
      +---------+------+-------------------------------------+
      | Warning | 1300 | Invalid utf8 character string: 'DE' |
      +---------+------+-------------------------------------+
      1 row in set (0.00 sec)
      

      The problem appeared in 10.0 tree with this commit

      commit 1427e1db99ac44dedbc78e8655742a8ed9bfd755
      Author: Alexander Barkov <bar@mariadb.org>
      Date:   Mon Sep 1 20:57:32 2014 +0400
       
          MDEV-6661 PI() does not work well in UCS2/UTF16/UTF32 context
          MDEV-6666 Malformed result for CONCAT(utf8_column, binary_string)
          
          Item_static_string_func::safe_charset_converter() and
          Item_hex_string::safe_charset_converter() did not
          handle character sets with mbminlen>1 properly, as well as
          did not handle conversion from binary to multi-byte well.
          
          Introducing Item::const_charset_converter(), to reuse it in a number
          of Item_*::safe_charset_converter().
      

      Attachments

        Activity

          People

            bar Alexander Barkov
            elenst Elena Stepanova
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

            Dates

              Created:
              Updated:

              Git Integration

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