pjms6JFc

1#Create the table
2DROP TABLE IF EXISTS T;
3
4CREATE TABLE `T` (
5  `id` BIGINT(20) UNSIGNED NOT NULL,
6  `x` VARCHAR(50) COLLATE utf8_bin DEFAULT NULL,
7  `y` VARCHAR(15) COLLATE utf8_bin DEFAULT NULL,
8  PRIMARY KEY (`id`),
9  KEY `d6fe1d0be6347b8ef2427fa629c04485` (`x`),
10  KEY `ee16755af8c89d0c26540daf7a0fe1b3` (`y`)
11) ENGINE=MYISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
12
13#Add two lines
14INSERT INTO T VALUES (1,'login','read'), (3,'','read');
15
16#Query 1: expected 1 line, got 1 line
17SELECT * FROM T WHERE
18`y` = 'read' AND
19(
20        ('login' = '' AND `x` = '') OR
21        (`x` != '' && 'login' REGEXP `x`)
22);
23
24#Query 2: expected 1 line, got 2 line
25SELECT * FROM T WHERE
26`y` = 'read' AND
27(
28        ('login' = '' AND `x` = '') OR
29        ('login' REGEXP `x`)
30);
31
32#Invert field x
33UPDATE T SET `x` = '' WHERE id = 1;
34UPDATE T SET `x` = 'login' WHERE id = 3;
35
36#Another time, Query 1: expected 1 line, got 1 line
37SELECT * FROM T WHERE
38`y` = 'read' AND
39(
40        ('login' = '' AND `x` = '') OR
41        (`x` != '' && 'login' REGEXP `x`)
42);
43
44#Another time, Query 2: expected 1 line, got 1 line
45SELECT * FROM T WHERE
46`y` = 'read' AND
47(
48        ('login' = '' AND `x` = '') OR
49        ('login' REGEXP `x`)
50);
51	
52id      x       y
531       login   read
54
55id      x       y
561       login   read
573               read
58
59id      x       y
603       login   read
61
62id      x       y
633       login   read