On 01/08/2012 02:46 AM, Sergei Petrunia wrote:

Hello Igor, Timour

Could anybody of please review the below fix? additional details about the problem were posted at https://mariadb.atlassian.net/browse/MDEV-67.

Sergey, After the discussion with you on skype I reviewed Timour's code if (mixed_implicit_grouping) tbl->table->maybe_null= 1; and realized that it concerned a very special case when aggregate functions were used together with fields in the select list. Timour's code is ok for this case. So is yours. Ok to push. Regards, Igor.

----- Forwarded message from Sergey Petrunya <psergey@askmonty.org> -----

From: Sergey Petrunya <psergey@askmonty.org> To: commits@mariadb.org X-Mailer: mail (GNU Mailutils 1.2) Date: Sun, 8 Jan 2012 14:43:17 +0400 (GST) Subject: [Commits] Rev 3377: BUG#912510: Crash in do_copy_not_null with semijoin=ON, firstmatch=ON, aggregate ... in file:///home/psergey/dev2/5.3-look46/

At file:///home/psergey/dev2/5.3-look46/

------------------------------------------------------------ revno: 3377 revision-id: psergey@askmonty.org-20120108104314-xa6cxdbfive3elwf parent: igor@askmonty.org-20120103040636-nc6o55vsxqadd1n0 committer: Sergey Petrunya <psergey@askmonty.org> branch nick: 5.3-look46 timestamp: Sun 2012-01-08 14:43:14 +0400 message: BUG#912510: Crash in do_copy_not_null with semijoin=ON, firstmatch=ON, aggregate ... - Create/use do_copy_nullable_row_to_notnull() function for ref access, which is used when copying from not-NULL field in table that can be NULL-complemented to not-NULL field. === modified file 'mysql-test/r/subselect_sj.result' --- a/mysql-test/r/subselect_sj.result 2011-12-24 16:55:10 +0000 +++ b/mysql-test/r/subselect_sj.result 2012-01-08 10:43:14 +0000 @@ -2152,4 +2152,21 @@ set optimizer_prune_level= @opl_901399; set optimizer_switch= @os_091399; DROP TABLE t1,t2; +# +# BUG#912510: Crash in do_copy_not_null with semijoin=ON, firstmatch=ON, aggregate ... +# +CREATE TABLE t1 ( a VARCHAR(1) NOT NULL ); +INSERT INTO t1 VALUES ('k'),('l'); +CREATE TABLE t2 ( b VARCHAR(1) NOT NULL, KEY(b) ); +INSERT INTO t2 VALUES ('k'),('l'); +CREATE TABLE t3 ( c VARCHAR(1) NOT NULL, KEY(c) ); +INSERT INTO t3 VALUES ('m'),('n'); +SELECT a, COUNT(*) FROM t1 +WHERE a IN ( +SELECT b FROM t2 force index(b), t3 force index(c) +WHERE c = b AND b = a +); +a COUNT(*) +NULL 0 +DROP TABLE t1, t2, t3; set optimizer_switch=@subselect_sj_tmp;

=== modified file 'mysql-test/r/subselect_sj_jcl6.result' --- a/mysql-test/r/subselect_sj_jcl6.result 2011-12-24 16:55:10 +0000 +++ b/mysql-test/r/subselect_sj_jcl6.result 2012-01-08 10:43:14 +0000 @@ -2166,6 +2166,23 @@ set optimizer_prune_level= @opl_901399; set optimizer_switch= @os_091399; DROP TABLE t1,t2; +# +# BUG#912510: Crash in do_copy_not_null with semijoin=ON, firstmatch=ON, aggregate ... +# +CREATE TABLE t1 ( a VARCHAR(1) NOT NULL ); +INSERT INTO t1 VALUES ('k'),('l'); +CREATE TABLE t2 ( b VARCHAR(1) NOT NULL, KEY(b) ); +INSERT INTO t2 VALUES ('k'),('l'); +CREATE TABLE t3 ( c VARCHAR(1) NOT NULL, KEY(c) ); +INSERT INTO t3 VALUES ('m'),('n'); +SELECT a, COUNT(*) FROM t1 +WHERE a IN ( +SELECT b FROM t2 force index(b), t3 force index(c) +WHERE c = b AND b = a +); +a COUNT(*) +NULL 0 +DROP TABLE t1, t2, t3; set optimizer_switch=@subselect_sj_tmp; # # BUG#49129: Wrong result with IN-subquery with join_cache_level=6 and firstmatch=off

=== modified file 'mysql-test/t/subselect_sj.test' --- a/mysql-test/t/subselect_sj.test 2011-12-24 16:55:10 +0000 +++ b/mysql-test/t/subselect_sj.test 2012-01-08 10:43:14 +0000 @@ -1997,6 +1997,25 @@

DROP TABLE t1,t2;

+--echo # +--echo # BUG#912510: Crash in do_copy_not_null with semijoin=ON, firstmatch=ON, aggregate ... +--echo # +CREATE TABLE t1 ( a VARCHAR(1) NOT NULL ); +INSERT INTO t1 VALUES ('k'),('l'); + +CREATE TABLE t2 ( b VARCHAR(1) NOT NULL, KEY(b) ); +INSERT INTO t2 VALUES ('k'),('l'); + +CREATE TABLE t3 ( c VARCHAR(1) NOT NULL, KEY(c) ); +INSERT INTO t3 VALUES ('m'),('n'); + +SELECT a, COUNT(*) FROM t1 + WHERE a IN ( + SELECT b FROM t2 force index(b), t3 force index(c) + WHERE c = b AND b = a + ); + +DROP TABLE t1, t2, t3;

# The following command must be the last one the file set optimizer_switch=@subselect_sj_tmp;

=== modified file 'sql/field_conv.cc' --- a/sql/field_conv.cc 2011-12-11 09:34:44 +0000 +++ b/sql/field_conv.cc 2012-01-08 10:43:14 +0000 @@ -248,6 +248,25 @@ } }

+/* + Copy: (not-NULL field in table that can be NULL-complemented) -> (not-NULL + field) +*/ +static void do_copy_nullable_row_to_notnull(Copy_field *copy) +{ + if (*copy->null_row || + (copy->from_null_ptr && (*copy->from_null_ptr & copy->from_bit))) + { + copy->to_field->set_warning(MYSQL_ERROR::WARN_LEVEL_WARN, + WARN_DATA_TRUNCATED, 1); + copy->to_field->reset(); + } + else + { + (copy->do_copy2)(copy); + } + +}

/* Copy: (NULL-able field) -> (not NULL-able field) */ static void do_copy_not_null(Copy_field *copy) @@ -638,7 +657,15 @@ else if (to_field == to_field->table->next_number_field) do_copy= do_copy_next_number; else - do_copy= do_copy_not_null; + { + if (!from_null_ptr) + { + null_row= &from->table->null_row; + do_copy= do_copy_nullable_row_to_notnull; + } + else + do_copy= do_copy_not_null; + } } } else if (to_field->real_maybe_null())

=== modified file 'sql/sql_select.cc' --- a/sql/sql_select.cc 2011-12-24 16:55:10 +0000 +++ b/sql/sql_select.cc 2012-01-08 10:43:14 +0000 @@ -646,6 +646,9 @@ aggregate functions and non-aggregate fields, any non-aggregated field may produce a NULL value. Set all fields of each table as nullable before semantic analysis to take into account this change of nullability. + + Note: this loop doesn't touch tables inside merged semi-joins, because + subquery-to-semijoin conversion has not been done yet. This is intended. */ if (mixed_implicit_grouping) tbl->table->maybe_null= 1;

_______________________________________________ commits mailing list commits@mariadb.org https://lists.askmonty.org/cgi-bin/mailman/listinfo/commits

----- End forwarded message -----