Bug #28764 Column 'foo' cannot be null with case, between and date_add

• Submitted: 30 May 2007 5:26
• Modified: 30 May 2007 8:20
• Reporter: Markus Bertheau
• Status: Can't repeat
• Category: MySQL Server: General
• Severity: S3 (Non-critical)
• Version: 5.0.38-Ubuntu_0ubuntu1-log
• OS: Linux (Ubuntu 7.04)
• CPU Architecture: Any
• Tags: case null date_add coalesce between

[30 May 2007 5:26] Markus Bertheau

Description:
CREATE TABLE foo (created DATETIME NOT NULL);

The following doesn't work:

SELECT
    1
FROM
    foo
WHERE
    CASE WHEN TRUE THEN
        created BETWEEN NULL AND DATE_ADD(NULL, INTERVAL 1 DAY)
    ELSE
        TRUE
    END;
ERROR 1048 (23000): Column 'created' cannot be null

But the following does:

SELECT
    1
FROM
    foo
WHERE
    CASE WHEN TRUE THEN
        created BETWEEN NULL AND COALESCE(DATE_ADD(NULL, INTERVAL 1 DAY))
    ELSE
        TRUE
    END;

As does the following:

SELECT
    1
FROM
    foo
WHERE
    CASE WHEN TRUE THEN
        COALESCE(created) BETWEEN NULL AND DATE_ADD(NULL, INTERVAL 1 DAY)
    ELSE
        TRUE
    END;

And the following:

CREATE TABLE foo (created DATETIME NOT NULL);
SELECT
    1
FROM
    foo
WHERE
    CASE WHEN TRUE THEN
        created BETWEEN NULL AND NULL
    ELSE
        TRUE
    END;

I cannot see the logic behind that.

How to repeat:
CREATE TABLE foo (created DATETIME NOT NULL);

SELECT
    1
FROM
    foo
WHERE
    CASE WHEN TRUE THEN
        created BETWEEN NULL AND DATE_ADD(NULL, INTERVAL 1 DAY)
    ELSE
        TRUE
    END;
ERROR 1048 (23000): Column 'created' cannot be null

[30 May 2007 8:20] Sveta Smirnova

Thank you for the report.

I can not repeat described behaviour with current development sources.