Bug #66720 Regexp problem
Submitted: 6 Sep 2012 8:03 Modified: 6 Sep 2012 17:24
Reporter: Mattia Citterio Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: DML Severity:S3 (Non-critical)
Version:5.5.24-0ubuntu0.12.04.1, 5.5.29, 5.0.95, 5.1.66, 5.7.0 OS:Linux (Ubuntu 12.04)
Assigned to: CPU Architecture:Any
Tags: DML, REGEXP
Triage: Needs Triage: D3 (Medium)

[6 Sep 2012 8:03] Mattia Citterio
Description:
#Create the table
DROP TABLE IF EXISTS T;
CREATE TABLE `T` (
  `id` BIGINT(20) UNSIGNED NOT NULL,
  `x` VARCHAR(50) COLLATE utf8_bin DEFAULT NULL,
  `y` VARCHAR(15) COLLATE utf8_bin DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `d6fe1d0be6347b8ef2427fa629c04485` (`x`),
  KEY `ee16755af8c89d0c26540daf7a0fe1b3` (`y`)
) ENGINE=MYISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

#Add two records
INSERT INTO T VALUES (1,'login','read'), (3,'','read');

#Query 1: expected 1 line, got 1 line
SELECT * FROM T WHERE
`y` = 'read' AND
(
        ('login' = '' AND `x` = '') OR
        (`x` != '' && 'login' REGEXP `x`)
);

#id      x       y
#1       login   read

#Query 2: expected 1 line, got 2 line (why?!?)
SELECT * FROM T WHERE
`y` = 'read' AND
(
        ('login' = '' AND `x` = '') OR
        ('login' REGEXP `x`)
);

#id      x       y
#1       login   read
#3               read

#Invert field x
UPDATE T SET `x` = '' WHERE id = 1;
UPDATE T SET `x` = 'login' WHERE id = 3;

#Another time, Query 1: expected 1 line, got 1 line
SELECT * FROM T WHERE
`y` = 'read' AND
(
        ('login' = '' AND `x` = '') OR
        (`x` != '' && 'login' REGEXP `x`)
);

#id      x       y
#3       login   read

#Another time, Query 2: expected 1 line, got 1 line
SELECT * FROM T WHERE
`y` = 'read' AND
(
        ('login' = '' AND `x` = '') OR
        ('login' REGEXP `x`)
);

#id      x       y
#3       login   read

How to repeat:
Just execute the script.
[6 Sep 2012 17:24] Sveta Smirnova
Thank you for the report.

Verified as described.