Valgrind complain for a test case from func_gconcat.test

[Igor Babaev]

Valgrind reports errors with the 5.3 tree for the following test case from func_gconcat.test

#
# Bug #36024: group_concat distinct in subquery crash
#

CREATE TABLE t1 (a INT, KEY(a));
CREATE TABLE t2 (b INT);

INSERT INTO t1 VALUES (NULL), (8), (2);
INSERT INTO t2 VALUES (4), (10);

set optimizer_switch='materialization=off';

SELECT 1 FROM t1 WHERE t1.a NOT IN
(
  SELECT GROUP_CONCAT(DISTINCT t1.a)
  FROM t1 WHERE t1.a IN
  (
    SELECT b FROM t2
  )
  AND NOT t1.a >= (SELECT t1.a FROM t1 LIMIT 1)
  GROUP BY t1.a
);

DROP TABLE t1, t2;

If I put this test case into a separate file func_gconcat_1.test and run
./mysql-test-run --valgrind func_gconcat_1
I get:

igor@sophia:~/maria/maria-5.3-trunk/mysql-test> ./mysql-test-run --valgrind func_gconcat_1
Logging: ./mysql-test-run --valgrind func_gconcat_1
MariaDB Version 5.3.2
Turning on valgrind for all executables
Running valgrind with options " --show-reachable=yes --quiet "
vardir: /home/igor/maria/maria-5.3-trunk/mysql-test/var
Checking leftover processes...
Removing old var directory...
Creating var directory '/home/igor/maria/maria-5.3-trunk/mysql-test/var'...
Installing system database...
Checking supported features...
 - skipping ndbcluster
 - SSL connections supported
 - binaries are debug compiled
Using "../libtool" when running valgrind, strace or debugger
Collecting tests...
Using server port 50637

==============================================================================

TEST RESULT TIME (ms)
------------------------------------------------------------

worker[1] Using MTR_BUILD_THREAD 300, with reserved ports 16000..16019
main.func_gconcat_1 [ pass ] 1840
***Warnings generated in error logs during shutdown after running tests: main.func_gconcat_1

==30982== 1 bytes in 1 blocks are definitely lost in loss record 1 of 6
==30982== at 0x4C26C3A: malloc (in /usr/lib64/valgrind/vgpreload_memcheck-amd64-linux.so)
==30982== by 0xC768D5: my_malloc (my_malloc.c:40)
==30982== by 0x868CAE: save_index(st_sort_param*, unsigned char**, unsigned int, st_filesort_info*) (filesort.cc:1079)
==30982== by 0x866870: filesort(THD*, st_table*, st_sort_field*, unsigned int, SQL_SELECT*, unsigned long long, bool, unsigned long long*) (filesort.cc:248)
==30982== by 0x79F80F: create_sort_index(THD*, JOIN*, st_order*, unsigned long long, unsigned long long, bool) (sql_select.cc:18157)
==30982== by 0x778ED3: JOIN::exec() (sql_select.cc:2634)
==30982== by 0x683417: subselect_single_select_engine::exec() (item_subselect.cc:2984)
==30982== by 0x67C2EE: Item_subselect::exec() (item_subselect.cc:588)
==30982== by 0x67C854: Item_in_subselect::exec() (item_subselect.cc:743)
==30982== by 0x67E70E: Item_in_subselect::val_bool() (item_subselect.cc:1456)
==30982== by 0x6041C0: Item::val_bool_result() (item.h:843)
==30982== by 0x6394B5: Item_in_optimizer::val_int() (item_cmpfunc.cc:1709)
==30982== by 0x604134: Item::val_int_result() (item.h:839)
==30982== by 0x5FE5D6: Item_cache_int::cache_value() (item.cc:8209)
==30982== by 0x60977F: Item_cache_wrapper::cache() (item.cc:7056)
==30982== by 0x5FB985: Item_cache_wrapper::val_bool() (item.cc:7220)
==30982== 1 bytes in 1 blocks are definitely lost in loss record 2 of 6
==30982== at 0x4C26C3A: malloc (in /usr/lib64/valgrind/vgpreload_memcheck-amd64-linux.so)
==30982== by 0xC768D5: my_malloc (my_malloc.c:40)
==30982== by 0x868CAE: save_index(st_sort_param*, unsigned char**, unsigned int, st_filesort_info*) (filesort.cc:1079)
==30982== by 0x866870: filesort(THD*, st_table*, st_sort_field*, unsigned int, SQL_SELECT*, unsigned long long, bool, unsigned long long*) (filesort.cc:248)
==30982== by 0x79F80F: create_sort_index(THD*, JOIN*, st_order*, unsigned long long, unsigned long long, bool) (sql_select.cc:18157)
==30982== by 0x778ED3: JOIN::exec() (sql_select.cc:2634)
==30982== by 0x683417: subselect_single_select_engine::exec() (item_subselect.cc:2984)
==30982== by 0x67C2EE: Item_subselect::exec() (item_subselect.cc:588)
==30982== by 0x67C854: Item_in_subselect::exec() (item_subselect.cc:743)
==30982== by 0x67E70E: Item_in_subselect::val_bool() (item_subselect.cc:1456)
==30982== by 0x6041C0: Item::val_bool_result() (item.h:843)
==30982== by 0x63957D: Item_in_optimizer::val_int() (item_cmpfunc.cc:1724)
==30982== by 0x604134: Item::val_int_result() (item.h:839)
==30982== by 0x5FE5D6: Item_cache_int::cache_value() (item.cc:8209)
==30982== by 0x60977F: Item_cache_wrapper::cache() (item.cc:7056)
==30982== by 0x5FB985: Item_cache_wrapper::val_bool() (item.cc:7220)
==30982== 392 bytes in 1 blocks are definitely lost in loss record 5 of 6
==30982== at 0x4C26C3A: malloc (in /usr/lib64/valgrind/vgpreload_memcheck-amd64-linux.so)
==30982== by 0xC768D5: my_malloc (my_malloc.c:40)
==30982== by 0x79F5FB: create_sort_index(THD*, JOIN*, st_order*, unsigned long long, unsigned long long, bool) (sql_select.cc:18113)
==30982== by 0x778ED3: JOIN::exec() (sql_select.cc:2634)
==30982== by 0x683417: subselect_single_select_engine::exec() (item_subselect.cc:2984)
==30982== by 0x67C2EE: Item_subselect::exec() (item_subselect.cc:588)
==30982== by 0x67C854: Item_in_subselect::exec() (item_subselect.cc:743)
==30982== by 0x67E70E: Item_in_subselect::val_bool() (item_subselect.cc:1456)
==30982== by 0x6041C0: Item::val_bool_result() (item.h:843)
==30982== by 0x6394B5: Item_in_optimizer::val_int() (item_cmpfunc.cc:1709)
==30982== by 0x604134: Item::val_int_result() (item.h:839)
==30982== by 0x5FE5D6: Item_cache_int::cache_value() (item.cc:8209)
==30982== by 0x60977F: Item_cache_wrapper::cache() (item.cc:7056)
==30982== by 0x5FB985: Item_cache_wrapper::val_bool() (item.cc:7220)
==30982== by 0x635514: Item_func_not::val_int() (item_cmpfunc.cc:331)
==30982== by 0x797D37: evaluate_join_record(JOIN*, st_join_table*, int) (sql_select.cc:15256)
==30982== 392 bytes in 1 blocks are definitely lost in loss record 6 of 6
==30982== at 0x4C26C3A: malloc (in /usr/lib64/valgrind/vgpreload_memcheck-amd64-linux.so)
==30982== by 0xC768D5: my_malloc (my_malloc.c:40)
==30982== by 0x79F5FB: create_sort_index(THD*, JOIN*, st_order*, unsigned long long, unsigned long long, bool) (sql_select.cc:18113)
==30982== by 0x778ED3: JOIN::exec() (sql_select.cc:2634)
==30982== by 0x683417: subselect_single_select_engine::exec() (item_subselect.cc:2984)
==30982== by 0x67C2EE: Item_subselect::exec() (item_subselect.cc:588)
==30982== by 0x67C854: Item_in_subselect::exec() (item_subselect.cc:743)
==30982== by 0x67E70E: Item_in_subselect::val_bool() (item_subselect.cc:1456)
==30982== by 0x6041C0: Item::val_bool_result() (item.h:843)
==30982== by 0x63957D: Item_in_optimizer::val_int() (item_cmpfunc.cc:1724)
==30982== by 0x604134: Item::val_int_result() (item.h:839)
==30982== by 0x5FE5D6: Item_cache_int::cache_value() (item.cc:8209)
==30982== by 0x60977F: Item_cache_wrapper::cache() (item.cc:7056)
==30982== by 0x5FB985: Item_cache_wrapper::val_bool() (item.cc:7220)
==30982== by 0x635514: Item_func_not::val_int() (item_cmpfunc.cc:331)
==30982== by 0x797D37: evaluate_join_record(JOIN*, st_join_table*, int) (sql_select.cc:15256)

------------------------------------------------------------
The servers were restarted 0 times
Spent 1.840 of 16 seconds executing testcases

Warnings in log: All 1 tests were successful.

Errors/warnings were found in logfiles during server shutdown after running the
following sequence(s) of tests:
    main.func_gconcat_1
mysql-test-run: *** ERROR: There where errors/warnings in server logs after running test cases.

There are no valgrind errors with the following setting:
set optimizer_switch='materialization=off';

[Timour Katchaounov (timour)]

Simpler example:

CREATE TABLE t1 (a INT, KEY(a));
CREATE TABLE t2 (b INT);

INSERT INTO t1 VALUES (NULL), (8), (2);
INSERT INTO t2 VALUES (4), (10);

set @@optimizer_switch='subquery_cache=off';

SELECT 1
FROM t1
WHERE t1.a IN
(
  SELECT GROUP_CONCAT(t1.a)
  FROM t1
  WHERE t1.a IN (SELECT b FROM t2)
  GROUP BY t1.a
);

DROP TABLE t1, t2;

[Timour Katchaounov (timour)]

The bug is related to sj-materialization because:

- both the original and the simplified test cases cannot be reproduced with 'semijoin=off',
- in GDB it is visible that the materialized table for which filesort is performed is "sj-materialize"

As evident from GDB, and explain, the non-SJ materialization plan doesn't use filesort at all.
On the other hand sj-materialization uses quite a different strategy with filesort. The sort
buffers created for the temporary sj-materialize table are not freed properly, which is the
cause of the bug.

It is also unclear why sj-materialization needs filesort, this looks less efficient that the
non-sj materialization plan.