Hi!
>>>>> "Andrew" == Andrew Schmidt <aschmidt@stripped> writes:
>> Description:
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 =)
>> How-To-Repeat:
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
