Bug #1407 IF in WHERE-clause

• Submitted: 26 Sep 2003 3:26
• Modified: 4 Nov 2003 11:53
• Reporter: Yuriy Dzhenyeyev
• Status: No Feedback
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 4.1.0-alpha
• OS: Windows (Win2k)
• CPU Architecture: Any

[26 Sep 2003 3:26] Yuriy Dzhenyeyev

Description:
This is correct:
****************

mysql> SELECT count(*)
    -> FROM dms_index, d_arten
    -> WHERE d_arten.d_art_lnr = dms_index.d_art_lnr GROUP BY 
dms_index.erstellt_date + interval
    ->  IF (
    ->          dms_index.d_art_lnr = 0, 60,
    ->          (
    ->                  select case (d_arten.mhd)
    ->                  when 0 then 60 else d_arten.mhd end
    ->          )
    ->     ) month < now();
+----------+
| count(*) |
+----------+
|      547 |
|       11 |
+----------+
2 rows in set (0.39 sec)

**********************************************************
i.e. 547 entries with 'false' and 11 entries with 'true'
after I change 'group by' to 'and' in WHERE-clause, I get:
**********************************************************

mysql> SELECT count(*)
    -> FROM dms_index, d_arten
    -> WHERE d_arten.d_art_lnr = dms_index.d_art_lnr AND 
dms_index.erstellt_date + interval
    ->  IF (
    ->          dms_index.d_art_lnr = 0, 60,
    ->          (
    ->                  select case (d_arten.mhd)
    ->                  when 0 then 60 else d_arten.mhd end
    ->          )
    ->     ) month < now();
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.01 sec)

Actually I should get 11 entries... Please advice.

Thanks,

[e-mail]

How to repeat:
no idea

Suggested fix:
no idea

[30 Sep 2003 2:44] Alexander Keremidarski

Are you sure for these 11 rows IF() evaluates as False?

IF() can evaluate as True, False, NULL too. If these are NULLs this can easily explain empty set.

[2 Oct 2003 21:09] MySQL Verification Team

Could you please provide us with a complete test case such as
the 11 rows should be expected.

[4 Oct 2003 11:53] Yuriy Dzhenyeyev

unfortunately the content of the database has changed in the meantime, so I cannon send you this dataset. In the new dataset there is no error any more... Sorry for the inconvenience.

Regards,

YDzh

[14 Feb 2005 22:54] Bugs System

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".