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

Wrong result for WHERE .. (f2=COMPRESS('test') OR f2=COMPRESS('TEST'))

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • 5.5(EOL), 10.0(EOL), 10.1(EOL), 10.2(EOL)
    • 10.1.18, 10.2.2
    • Optimizer
    • None

    Description

      It's similar to MDEV-10850 (which is about functions returning ASCII values),
      but now for functions returning binary strings.

      In the following script the three SELECT queries must return the same result, but they return three different results:

      DROP TABLE IF EXISTS t1;
      		 
      CREATE TABLE t1 (f1 VARCHAR(4), f2 VARCHAR(64), UNIQUE KEY k1 (f1,f2));
      		 
      INSERT INTO t1 VALUES ( 'test',compress('test')), ('TEST', compress('TEST'));
      		 
      SELECT f1,HEX(f2) FROM t1 ignore index(k1) WHERE f1='test' AND (f2= compress("test") OR f2= compress("TEST"));
      		 
      SELECT f1,HEX(f2) FROM t1                  WHERE f1='test' AND (f2= compress("test") OR f2= compress("TEST"));
      		 
      SELECT f1,HEX(f2) FROM t1                  WHERE f1='test' AND (f2= compress("TEST") OR f2= compress("test"));

      The same problem is repeatable for FROM_BASE64():

      DROP TABLE IF EXISTS t1;
      		 
      CREATE TABLE t1 (f1 VARCHAR(4), f2 VARCHAR(128), UNIQUE KEY k1 (f1,f2));
      		 
      INSERT INTO t1 VALUES ('YQ==',from_base64('YQ==')), ('Yq==', from_base64('Yq=='));
      		 
      SELECT f1,HEX(f2) FROM t1 ignore index(k1) WHERE f1='YQ==' AND (f2= from_base64("YQ==") OR f2= from_base64("Yq=="));
      		 
      SELECT f1,HEX(f2) FROM t1                  WHERE f1='YQ==' AND (f2= from_base64("YQ==") OR f2= from_base64("Yq=="));
      		 
      SELECT f1,HEX(f2) FROM t1                  WHERE f1='YQ==' AND (f2= from_base64("Yq==") OR f2= from_base64("YQ=="));

      The same problem is repeatable for ENCRYPT():

      DROP TABLE IF EXISTS t1;
      		 
      CREATE TABLE t1 (f1 VARCHAR(4), f2 VARCHAR(64), UNIQUE KEY k1 (f1,f2));
      		 
      INSERT INTO t1 VALUES ( 'test',encrypt('test','key')), ('TEST', encrypt('TEST','key'));
      		 
      SELECT f1 FROM t1 ignore index(k1) WHERE f1='test' AND (f2= encrypt('test','key') OR f2= encrypt('TEST','key'));
      		 
      SELECT f1 FROM t1                  WHERE f1='test' AND (f2= encrypt('test','key') OR f2= encrypt('TEST','key'));
      		 
      SELECT f1 FROM t1                  WHERE f1='test' AND (f2= encrypt('TEST','key') OR f2= encrypt('test','key'));

      Attachments

        Issue Links

          relates to

          Bug - A problem which impairs or prevents the functions of the product. MDEV-10850 Wrong result for WHERE .. (f2=TO_BASE64('test') OR f2=TO_BASE64('TEST'))

          • Major - Major loss of function.
          • Closed

          Activity

            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.