Hi!

Andrew> tough one to describe. the step by step example shows all Andrew> basicly once a table gets more than 1 row and doing a select with no Andrew> result will yield a blank date... gah please see the example =)

Andrew> create database tmpdb; Andrew> use tmpdb; Andrew> create table test (foo int); Andrew> insert into test values (1); Andrew> select NOW(), count(*) from test where foo in (2); Andrew> +---------------------+----------+ Andrew> | NOW() | count(*) | Andrew> +---------------------+----------+ Andrew> | 2001-04-20 17:21:29 | 0 | Andrew> +---------------------+----------+

Andrew> insert into test values (1); Andrew> select NOW(), count(*) from test where foo in (2); Andrew> +-------+----------+ Andrew> | NOW() | count(*) | Andrew> +-------+----------+ Andrew> | | 0 | Andrew> +-------+----------+

Andrew> notice the date field is blank.

The problem here is that when you normally don't have any matching rows, SQL will return an empty set.

When you add a group function, MySQL will return a surrogate row where all column values are null.

MySQL uses a different optimization when you have just one table in the result set and this was the cause for the different results.

Here is a patch for this:

===== sql/sql_select.cc 1.94 vs edited ===== *** /tmp/sql_select.cc-1.94-21846 Thu Apr 19 20:41:18 2001 --- edited/sql/sql_select.cc Sat Apr 21 17:02:49 2001 *************** *** 4643,4649 **** --- 4643,4653 ---- else { if (!join->first_record) + { + /* No matching rows for group function */ clear_tables(join); + copy_fields(&join->tmp_table_param); + } if (join->having && join->having->val_int() == 0) error= -1; // Didn't satisfy having else *************** *** 4875,4881 **** --- 4879,4889 ---- if (idx < (int) join->send_group_parts) { if (!join->first_record) + { + /* No matching rows for group function */ clear_tables(join); + copy_fields(&join->tmp_table_param); + } copy_sum_funcs(join->sum_funcs); if (!join->having || join->having->val_int()) { *************** *** 4899,4905 **** } if (idx < (int) join->send_group_parts) { - copy_fields(&join->tmp_table_param); copy_funcs(join->tmp_table_param.funcs); init_sum_functions(join->sum_funcs); if (join->procedure) --- 4907,4912 ----

Regards, Monty