| Bug #49906 | Assertion failed; pushed cond fields not in read_set when performing filesort | ||
|---|---|---|---|
| Submitted: | 24 Dec 2009 1:44 | Modified: | 23 Nov 2010 3:10 |
| Reporter: | Patrick Crews | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S3 (Non-critical) |
| Version: | 6.0 | OS: | Any |
| Assigned to: | Jørgen Løland | CPU Architecture: | Any |
| Tags: | crashing, index_condition_pushdown, optimizer_switch | ||
[24 Dec 2009 1:44]
Patrick Crews
[24 Dec 2009 1:46]
Patrick Crews
randgen grammar and gendata file for reproducing this bug.
Attachment: bug49906.tar.gz (application/x-gzip, text), 2.83 KiB.
[24 Dec 2009 1:48]
Patrick Crews
Full crash output
Attachment: bug49906_crash_output.txt (text/plain), 45.92 KiB.
[25 Dec 2009 14:38]
Patrick Crews
Notes and test case: This bug if triggered by engine_condition_pushdown='ON'. Setting this variable to 'OFF' removed the crash. This is 6.0 only, not present in 5.1 and requires the multi-part key used in the ALTER TABLE statement below to crash. MTR test case for 6.0 - please comment out SET statements as needed for use on other versions of the server. Toggle engine_condition_pushdown between ON and OFF to observe the crash going away: #/* Server0: MySQL 6.0.14-alpha-gcov-debug-log */ /*!50400 SET SESSION optimizer_switch = 'firstmatch=on,index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,loosescan=on,materialization=on,semijoin=on' */; /*!50400 SET SESSION optimizer_use_mrr = 'force' */; /*!50400 SET SESSION engine_condition_pushdown = 'ON' */; /*!50400 SET SESSION join_cache_level = 1 */; /*!50400 SET SESSION debug = '' */; #/* Begin test case for query 0 */ --disable_warnings DROP TABLE /*! IF EXISTS */ G; --enable_warnings CREATE TABLE `G` ( pk integer auto_increment, `col_varchar_1024_utf8` varchar(1024) CHARACTER SET utf8, `col_varchar_1024_latin1` varchar(1024) CHARACTER SET latin1, `col_varchar_10_utf8` varchar(10) CHARACTER SET utf8, /*Indices*/ primary key (pk)) ENGINE=myisam; INSERT /*! IGNORE */ INTO G VALUES (NULL, NULL, 'a', NULL) ; INSERT /*! IGNORE */ INTO G VALUES (NULL, 'q', NULL, 'g') ; ALTER TABLE G ADD INDEX `test_idx` USING BTREE (`col_varchar_10_utf8` (10) , `col_varchar_1024_utf8` (5) ); SELECT `col_varchar_1024_latin1` field1 FROM G WHERE NULL OR `col_varchar_10_utf8` AND `col_varchar_10_utf8` LIKE 'g' ORDER BY field1 ; DROP TABLE G; #/* End of test case for query 0 */
[7 Jan 2010 8:33]
Jørgen Løland
Simplified test case:
SET SESSION engine_condition_pushdown = 'ON';
CREATE TABLE t1 (
v1 VARCHAR(1024),
v2 VARCHAR(10),
INDEX test_idx USING BTREE (v2,v1(5))
);
INSERT INTO t1 VALUES ('a', 'c') ;
INSERT INTO t1 VALUES ('b', 'd') ;
SELECT v1
FROM t1
WHERE v2 LIKE 'd'
ORDER BY v1;
DROP TABLE t1;
[7 Jan 2010 15:16]
Jørgen Løland
The problem appears when filesort is combined with ICP. In find_all_keys() (filesort.cc), filesort reads all fields that are necessary for
a) perform ordering, and
b) evaluate where conditions.
The fields in b) are those necessary to evaluate select->cond. However, when we have pushed conditions to an index, the pushed conditions are removed from select->cond in push_index_cond() (sql_select.cc). Because of this, fields referenced from pushed conditions will not be in the read_set.
Indexes with pushed conditions evaluate the condition before deciding if a certain row should be returned. This is done by calling val_int() on the conditions, but since the field is not in the read set an ASSERT is fired:
In Field_varstring::val_str():
DBUG_ASSERT(!table ||
(!table->read_set || bitmap_is_set(table->read_set, field_index)));
because field_index is not set in table->read_set (^ is ASSERT_COLUMN_MARKED_FOR_READ)
[7 Jan 2010 15:27]
Jørgen Løland
Two suggested solutions:
1) Add icp_cond variable to SQL_SELECT and add these fields to the read_set in
find_all_keys() in the same way as is done with select->cond:
if (select && select->icp_cond)
select->icp_cond->walk(&Item::register_field_in_read_map,
1, (uchar*) sort_form);
2) Check if read_set includes all fields before evaluating pushed conditions.
1. has been tested and is confirmed to work but adds another variable to
SQL_SELECT
2. will have to be implemented everywhere ICP is evaluated (which means for
each engine supporting it). After evaluating the condition, any changes
to the read_set will have to be reverted. Thus, this bit fiddling will
happen for every row being evaluated in the table being accessed.
[15 Jan 2010 13:15]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/97071 3831 Jorgen Loland 2010-01-15 BUG#49906: Assertion failed - Field_varstring::val_str in field.cc When filesort reads sort keys, fields that are evaluated by pushed conditions were not in the read_set. This resulted in an ASSERT when the engine evaluated the pushed conditions. The fix is to add fields evaluated by pushed conditions to the read_set. @ mysql-test/r/select.result Add test for BUG#49906 @ mysql-test/r/select_jcl6.result Add test for BUG#49906 @ mysql-test/t/select.test Add test for BUG#49906 @ sql/filesort.cc Include fields used by conditions in the read_set of find_all_keys() @ sql/opt_range.cc Initialize icp_cond in constructor @ sql/opt_range.h Added variable COND *icp_cond to SQL_SELECT. Variable will store conditions pushed to indexes. @ sql/sql_select.cc Make icp_cond point to pushed conditions
[25 Jan 2010 14:02]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/98055 3845 Jorgen Loland 2010-01-25 BUG#49906: Assertion failed - Field_varstring::val_str in field.cc When filesort reads sort keys, fields that are evaluated by pushed conditions were not in the read_set. This resulted in an ASSERT when the engine evaluated the pushed conditions. The fix is to add fields evaluated by pushed conditions to the read_set. @ mysql-test/r/select.result Add test for BUG#49906 @ mysql-test/r/select_jcl6.result Add test for BUG#49906 @ mysql-test/t/select.test Add test for BUG#49906 @ sql/filesort.cc Include fields used by conditions in the read_set of find_all_keys() @ sql/opt_range.cc Initialize icp_cond in constructor @ sql/opt_range.h Added variable COND *icp_cond to SQL_SELECT. Variable will store conditions pushed to indexes. @ sql/sql_select.cc Make icp_cond point to pushed conditions
[26 Jan 2010 6:28]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/98141 3853 Jorgen Loland 2010-01-26 BUG#49906: Assertion failed - Field_varstring::val_str in field.cc When filesort reads sort keys, fields that are evaluated by pushed conditions were not in the read_set. This resulted in an ASSERT when the engine evaluated the pushed conditions. The fix is to add fields evaluated by pushed conditions to the read_set. @ mysql-test/r/select.result Add test for BUG#49906 @ mysql-test/r/select_jcl6.result Add test for BUG#49906 @ mysql-test/t/select.test Add test for BUG#49906 @ sql/filesort.cc Include fields used by conditions in the read_set of find_all_keys() @ sql/opt_range.cc Initialize icp_cond in constructor @ sql/opt_range.h Added variable COND *icp_cond to SQL_SELECT. Variable will store conditions pushed to indexes. @ sql/sql_select.cc Make icp_cond point to pushed conditions
[26 Jan 2010 7:00]
Jørgen Løland
Pushed to mysql-6.0-codebase-bugfixing
[13 Feb 2010 8:37]
Bugs System
Pushed into 6.0.14-alpha (revid:alik@sun.com-20100213083436-9pesg4h55w1mekxc) (version source revid:luis.soares@sun.com-20100211135109-t63avry9fqpgyh78) (merge vers: 6.0.14-alpha) (pib:16)
[25 Feb 2010 1:30]
Paul DuBois
Noted in 6.0.14 changelog. With engine condition pushdown enabled, filesort could raise an assertion due to improper data setup.
[7 May 2010 10:45]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/107728 3145 oystein.grovlen@sun.com 2010-05-07 BUG#49906: Assertion failed - Field_varstring::val_str in field.cc (Backporting of revid:jorgen.loland@sun.com-20100126063016-f4vyz4jcymk2urmt) When filesort reads sort keys, fields that are evaluated by pushed conditions were not in the read_set. This resulted in an ASSERT when the engine evaluated the pushed conditions. The fix is to add fields evaluated by pushed conditions to the read_set. @ mysql-test/r/select.result Add test for BUG#49906 @ mysql-test/r/select_jcl6.result Add test for BUG#49906 @ mysql-test/t/select.test Add test for BUG#49906 @ sql/filesort.cc Include fields used by conditions in the read_set of find_all_keys() @ sql/opt_range.cc Initialize icp_cond in constructor @ sql/opt_range.h Added variable COND *icp_cond to SQL_SELECT. Variable will store conditions pushed to indexes. @ sql/sql_select.cc Make icp_cond point to pushed conditions
[16 Aug 2010 6:33]
Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100816062819-bluwgdq8q4xysmlg) (version source revid:alik@sun.com-20100816062612-enatdwnv809iw3s9) (pib:20)
[13 Nov 2010 16:05]
Bugs System
Pushed into mysql-trunk 5.6.99-m5 (revid:alexander.nozdrin@oracle.com-20101113155825-czmva9kg4n31anmu) (version source revid:vasil.dimov@oracle.com-20100629074804-359l9m9gniauxr94) (merge vers: 5.6.99-m4) (pib:21)
[23 Nov 2010 3:10]
Paul DuBois
Bug does not appear in any released 5.6.x version. No 5.6.1 changelog entry needed.
