-
Jorgen Loland authored
WITH CONST TABLE AND NO MATCHING ROWS BUG#13422961: WRONG RESULTS FROM SELECT WITH AGGREGATES AND IMPLICIT GROUPING + MYISAM OR MEM BUG#11760517: 52935: MIN/MAX FAILS TO EVALUATE HAVING CONDITION, RETURNS INCORRECT NULL RESULT When implicitly grouped queries do not have any rows matching the join predicates, a row based on NULL values is returned (if not filtered out by the HAVING clause). This means that (a) non-aggregated fields should have the value NULL and (b) aggregated fields should be calculated based on NULL. For (a), this is achieved by marking all tables as containing only NULL values by calling mark_as_null_row(). For (b), this is achieved by calling Item::no_rows_in_result(). There were multiple bugs in this area: 1) In locations where mark_as_null_row() was called, const tables were ignored. This has been fixed by storing the info mark_as_null_row() modifies for const tables, call mark_as_null_row() for all tables including const tables, send the result and finally restore const table info. Note that save/restore is only required for subqueries since the value will not be reused otherwise. This was the case for end_send_group() and end_write_group() 2) Item::no_rows_in_result() was not called everywhere where aggregate items needed to be calculated on NULL values. This was the case for do_select(). 3) mark_as_null_row() was not called everywhere non-aggregate items needed to be set to NULL. This was the case for do_select() and end_write_group(). 4) the having clause was evaluated after (a) but before (b). This was the case in return_zero_rows() Additional cleanup: * mark_as_null_row() no longer sets the NULL bits, only table->null_row and table->status * Field::is_null() now checks table->null_row before checking the NULL bits.
Jorgen Loland authoredWITH CONST TABLE AND NO MATCHING ROWS BUG#13422961: WRONG RESULTS FROM SELECT WITH AGGREGATES AND IMPLICIT GROUPING + MYISAM OR MEM BUG#11760517: 52935: MIN/MAX FAILS TO EVALUATE HAVING CONDITION, RETURNS INCORRECT NULL RESULT When implicitly grouped queries do not have any rows matching the join predicates, a row based on NULL values is returned (if not filtered out by the HAVING clause). This means that (a) non-aggregated fields should have the value NULL and (b) aggregated fields should be calculated based on NULL. For (a), this is achieved by marking all tables as containing only NULL values by calling mark_as_null_row(). For (b), this is achieved by calling Item::no_rows_in_result(). There were multiple bugs in this area: 1) In locations where mark_as_null_row() was called, const tables were ignored. This has been fixed by storing the info mark_as_null_row() modifies for const tables, call mark_as_null_row() for all tables including const tables, send the result and finally restore const table info. Note that save/restore is only required for subqueries since the value will not be reused otherwise. This was the case for end_send_group() and end_write_group() 2) Item::no_rows_in_result() was not called everywhere where aggregate items needed to be calculated on NULL values. This was the case for do_select(). 3) mark_as_null_row() was not called everywhere non-aggregate items needed to be set to NULL. This was the case for do_select() and end_write_group(). 4) the having clause was evaluated after (a) but before (b). This was the case in return_zero_rows() Additional cleanup: * mark_as_null_row() no longer sets the NULL bits, only table->null_row and table->status * Field::is_null() now checks table->null_row before checking the NULL bits.
Loading