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

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 5.5, 10.1, 10.2, 10.0
    • Fix Version/s: 10.1.18, 10.2.2
    • Component/s: Optimizer
    • Labels:
      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

            Activity

              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: