Wrong result with materialization + utf8

[Philip Stoev]

The following query:

SELECT * FROM t3 WHERE (a, c, d) IN (SELECT t1.c, t2.c, t1.d FROM t1 LEFT JOIN t2 ON t1.b = t2.e);

returns no rows when executed with materialization, even though it should return:

+------+------+-------+
| a | c | d |
+------+------+-------+
| h | your | XYFRD |
+------+------+-------+

explain:

| 1 | PRIMARY | t3 | system | NULL | NULL | NULL | NULL | 1 | |
| 2 | SUBQUERY | t1 | ALL | NULL | NULL | NULL | NULL | 54 | |
| 2 | SUBQUERY | t2 | ALL | NULL | NULL | NULL | NULL | 22 | Using where |

minimal optimizer_switch: in_to_exists=off,materialization=on;
full optimizer_switch: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=on,in_to_exists=off,semijoin=off,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=off,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on

bzr version-info:

revision-id: <email address hidden>
date: 2011-08-12 14:31:40 +0300
build-date: 2011-08-14 10:53:15 +0300
revno: 3155
branch-nick: maria-5.3

test case:

DROP TABLE IF EXISTS t1;
CREATE TABLE t1 ( a int(11) NOT NULL , b int(11), c varchar(1014) COLLATE utf8_general_ci, d varchar(1014) COLLATE utf8_general_ci) ;
INSERT INTO t1 VALUES (1,0,'m','k'),(1,5,'w','l'),(1,6,'F','u'),(1,5,'y','n'),(1,1,'e','j'),(1,8,'g','L'),(1,1,'U','m'),(2,2,'d','E'),(2,9,'o','C'),(2,4,'w','c'),(2,9,'n','r'),(3,0,'w','f'),(3,9,'e','o'),(3,9,'d','i'),(3,3,'g','g'),(4,2,'u','d'),(4,3,'u','H'),(4,5,'o','u'),(4,4,'i','r'),(4,9,'s','s'),(4,1,'z','R'),(4,6,'V','x'),(5,7,'b','U'),(5,5,'d','w'),(5,8,'e','W'),(5,4,'M','w'),(6,7,'o','o'),(6,6,'y','s'),(6,9,'h','b'),(6,3,'C','L'),(6,8,'B','P'),(6,0,'M','O'),(6,7,'K','u'),(7,3,'O','Q'),(7,7,'R','t'),(7,6,'x','k'),(7,7,'f','w'),(7,3,'G','i'),(7,8,'a','s'),(7,9,'S','B'),(7,3,'U','x'),(7,1,'i','z'),(8,8,'o','i'),(8,0,'f','r'),(8,0,'a','g'),(8,5,'v','A'),(8,7,'G','b'),(9,5,'I','b'),(9,8,'v','t'),(9,0,'q','j'),(9,3,'g','o'),(9,7,'p','w'),(9,1,'h','t'),(1,9,'h','XYFRD');

DROP TABLE IF EXISTS t2;
CREATE TABLE t2 ( b int(11), c varchar(1024) COLLATE utf8_general_ci, d varchar(1024) COLLATE utf8_general_ci, a int(11) NOT NULL , e int(11)) ;
INSERT INTO t2 VALUES (0,'s','l',1,9),(8,'d','p',5,9),(1,'j','F',6,5),(0,'m','h',7,0),(8,'d','k',8,0),(5,'r','w',1,8),(1,'t','N',1,7),(5,'j','t',1,4),(2,'a','q',1,5),(0,'L','s',1,5),(6,'u','b',2,1),(7,'c','h',3,8),(0,'N','n',3,9),(0,'y','w',4,6),(5,'h','Z',4,4),(6,'your','L',4,9),(5,'u','a',4,3),(0,'have','q',4,4),(0,'t','w',4,0),(5,'I','T',4,4),(0,'s','C',4,4),(2,'j','a',5,6);

DROP TABLE IF EXISTS t3;
CREATE TABLE t3 ( a varchar(10) COLLATE utf8_general_ci, c varchar(10) COLLATE utf8_general_ci, d varchar(10) COLLATE utf8_general_ci);
INSERT INTO t3 VALUES ('h','your','XYFRD');

set session optimizer_switch='in_to_exists=off,materialization=on';

SELECT * FROM t3 WHERE (a, c, d) IN (SELECT t1.c, t2.c, t1.d FROM t1 LEFT JOIN t2 ON t1.b = t2.e);

The UTF8 columns, the 1024 argument to VARCHAR() and the 3-columns to the IN() all seem to be required. Further reducing the number of participating rows was not possible, No NULLs or constant tables are involved.

[Timour Katchaounov (timour)]

The bug is reproducible if one records the test case
as a separate test file with mtr. If the test case is run
from the mariadb console, it works correctly.

[Timour Katchaounov (timour)]

The EXPLAIN in test case above shows that the optimizer uses
materialization, while it should have switched back to in-exists
because materialization is currently not applicable when columns
are bigger than 1024.

This problem has been fixed by the patch for bug lp:823930.