Conflicting query results with 'IS NULL' in where clause

[Database Administrator]

Background: I have a large table with sales records, which has a
TxnDate datetime and a TxnHalfHourly date time.
For each record, TxnHalfHourly is initially NULL, but it is set to a
to floored time after each record is processed. I was running a query
to look for unprocessed records and I got these two conflicting
results.

Query 1

mysql> SELECT MIN(TxnDate), MAX(TxnDate) FROM sales_rec
where TxnDate BETWEEN ('2008-08-28' AND '2008-10-15') AND
TxnDateHalfHourly IS NULL AND ID>30947224;
+---------------------+---------------------+
| MIN(TxnDate) | MAX(TxnDate) |
+---------------------+---------------------+
| 2008-07-26 11:38:00 | 2008-07-26 21:46:00 |
+---------------------+---------------------+
1 row in set, 65535 warnings (11.68 sec)

Query 2

mysql> SELECT MIN(TxnDate), MAX(TxnDate) FROM sales_rec
where TxnDate BETWEEN ('2008-08-28' AND '2008-10-15') AND
( TxnDateHalfHourly IS NULL AND ID>30947224);
+--------------+--------------+
| MIN(TxnDate) | MAX(TxnDate) |
+--------------+--------------+
| NULL | NULL |
+--------------+--------------+
1 row in set, 65535 warnings (2 min 15.03 sec)


I know that there are no records in the given time period that satisfy
the criteria, so Query 2 gives the correct result.
I know that NULL isn't a value and can't be compared using '=', but I
can't see what is wrong with Query 1.

[Database Administrator]

chris_g schreef:
> Background: I have a large table with sales records, which has a
> TxnDate datetime and a TxnHalfHourly date time.
> For each record, TxnHalfHourly is initially NULL, but it is set to a
> to floored time after each record is processed. I was running a query
> to look for unprocessed records and I got these two conflicting
> results.
>
> Query 1
>
> mysql> SELECT MIN(TxnDate), MAX(TxnDate) FROM sales_rec
> where TxnDate BETWEEN ('2008-08-28' AND '2008-10-15') AND
> TxnDateHalfHourly IS NULL AND ID>30947224;
> +---------------------+---------------------+
> | MIN(TxnDate) | MAX(TxnDate) |
> +---------------------+---------------------+
> | 2008-07-26 11:38:00 | 2008-07-26 21:46:00 |
> +---------------------+---------------------+
> 1 row in set, 65535 warnings (11.68 sec)
>
> Query 2
>
> mysql> SELECT MIN(TxnDate), MAX(TxnDate) FROM sales_rec
> where TxnDate BETWEEN ('2008-08-28' AND '2008-10-15') AND
> ( TxnDateHalfHourly IS NULL AND ID>30947224);
> +--------------+--------------+
> | MIN(TxnDate) | MAX(TxnDate) |
> +--------------+--------------+
> | NULL | NULL |
> +--------------+--------------+
> 1 row in set, 65535 warnings (2 min 15.03 sec)
>
>
> I know that there are no records in the given time period that satisfy
> the criteria, so Query 2 gives the correct result.
> I know that NULL isn't a value and can't be compared using '=', but I
> can't see what is wrong with Query 1.

Could you post a EXPLAIN select..... for these 2 query's?

Based on the execution time of these 2, i guess is there's a different
index involved...

But i do not see anything wrong is either one of these query's...

[Database Administrator]

On Nov 13, 7:58 pm, Luuk wrote:

> Could you post a EXPLAIN select..... for these 2 query's?
>
> Based on the execution time of these 2, i guess is there's a different
> index involved...

Yes it looks that way. I'm also perplexed by the warnigns.

mysql> SELECT MIN(TxnDate), MAX(TxnDate) FROM sales_rec
-> where TxnDate BETWEEN ('2008-08-28' AND '2008-10-15')
AND
-> TxnDateHalfHourly IS NULL AND ID>30947224;
+----+-------------+-----------+-------+---------------+---------
+---------+------+---------+-------------+
| id | select_type | table | type | possible_keys | key |
key_len | ref | rows | Extra |
+----+-------------+-----------+-------+---------------+---------
+---------+------+---------+-------------+
| 1 | SIMPLE | sales_rec | range | PRIMARY | PRIMARY |
4 | NULL | 6937092 | Using where |
+----+-------------+-----------+-------+---------------+---------
+---------+------+---------+-------------+
1 row in set, 3 warnings (1.75 sec)



mysql> show warnings;
+---------+------
+-------------------------------------------------------------+
| Level | Code |
Message |
+---------+------
+-------------------------------------------------------------+
| Warning | 1292 | Truncated incorrect DOUBLE value:
'2008-08-28' |
| Warning | 1292 | Truncated incorrect DOUBLE value:
'2008-10-15' |
| Warning | 1292 | Incorrect datetime value: '1' for column 'TxnDate'
at row 1 |
+---------+------
+-------------------------------------------------------------+
3 rows in set (0.00 sec)

mysql> EXPLAIN SELECT MIN(TxnDate), MAX(TxnDate) FROM sales_rec
-> where TxnDate BETWEEN ('2008-08-28' AND '2008-10-15')
AND
-> ( TxnDateHalfHourly IS NULL AND ID>30947224);
+----+-------------+-----------+------+---------------+------+---------
+------+----------+-------------+
| id | select_type | table | type | possible_keys | key | key_len
| ref | rows | Extra |
+----+-------------+-----------+------+---------------+------+---------
+------+----------+-------------+
| 1 | SIMPLE | sales_rec | ALL | TxnDateIDX | NULL | NULL
| NULL | 13874184 | Using where |
+----+-------------+-----------+------+---------------+------+---------
+------+----------+-------------+
1 row in set, 3 warnings (0.00 sec)

mysql> show warnings;
+---------+------
+-------------------------------------------------------------+
| Level | Code |
Message |
+---------+------
+-------------------------------------------------------------+
| Warning | 1292 | Truncated incorrect DOUBLE value:
'2008-08-28' |
| Warning | 1292 | Truncated incorrect DOUBLE value:
'2008-10-15' |
| Warning | 1292 | Incorrect datetime value: '1' for column 'TxnDate'
at row 1 |
+---------+------
+-------------------------------------------------------------+
3 rows in set (0.00 sec)

[Database Administrator]

chris_g schreef:
> On Nov 13, 7:58 pm, Luuk wrote:
>
>> Could you post a EXPLAIN select..... for these 2 query's?
>>
>> Based on the execution time of these 2, i guess is there's a different
>> index involved...
>
> Yes it looks that way. I'm also perplexed by the warnigns.
>
> mysql> SELECT MIN(TxnDate), MAX(TxnDate) FROM sales_rec
> -> where TxnDate BETWEEN ('2008-08-28' AND '2008-10-15')
> AND
> -> TxnDateHalfHourly IS NULL AND ID>30947224;
> +----+-------------+-----------+-------+---------------+---------
> +---------+------+---------+-------------+
> | id | select_type | table | type | possible_keys | key |
> key_len | ref | rows | Extra |
> +----+-------------+-----------+-------+---------------+---------
> +---------+------+---------+-------------+
> | 1 | SIMPLE | sales_rec | range | PRIMARY | PRIMARY |
> 4 | NULL | 6937092 | Using where |
> +----+-------------+-----------+-------+---------------+---------
> +---------+------+---------+-------------+
> 1 row in set, 3 warnings (1.75 sec)
>
>
>
> mysql> show warnings;
> +---------+------
> +-------------------------------------------------------------+
> | Level | Code |
> Message |
> +---------+------
> +-------------------------------------------------------------+
> | Warning | 1292 | Truncated incorrect DOUBLE value:
> '2008-08-28' |
> | Warning | 1292 | Truncated incorrect DOUBLE value:
> '2008-10-15' |
> | Warning | 1292 | Incorrect datetime value: '1' for column 'TxnDate'
> at row 1 |
> +---------+------
> +-------------------------------------------------------------+
> 3 rows in set (0.00 sec)
>
> mysql> EXPLAIN SELECT MIN(TxnDate), MAX(TxnDate) FROM sales_rec
> -> where TxnDate BETWEEN ('2008-08-28' AND '2008-10-15')
> AND
> -> ( TxnDateHalfHourly IS NULL AND ID>30947224);
> +----+-------------+-----------+------+---------------+------+---------
> +------+----------+-------------+
> | id | select_type | table | type | possible_keys | key | key_len
> | ref | rows | Extra |
> +----+-------------+-----------+------+---------------+------+---------
> +------+----------+-------------+
> | 1 | SIMPLE | sales_rec | ALL | TxnDateIDX | NULL | NULL
> | NULL | 13874184 | Using where |
> +----+-------------+-----------+------+---------------+------+---------
> +------+----------+-------------+
> 1 row in set, 3 warnings (0.00 sec)
>
> mysql> show warnings;
> +---------+------
> +-------------------------------------------------------------+
> | Level | Code |
> Message |
> +---------+------
> +-------------------------------------------------------------+
> | Warning | 1292 | Truncated incorrect DOUBLE value:
> '2008-08-28' |
> | Warning | 1292 | Truncated incorrect DOUBLE value:
> '2008-10-15' |
> | Warning | 1292 | Incorrect datetime value: '1' for column 'TxnDate'
> at row 1 |
> +---------+------
> +-------------------------------------------------------------+
> 3 rows in set (0.00 sec)

i overlooked it, but:
TxnDate BETWEEN ('2008-08-28' AND '2008-10-15')
is not correct, at least, i think:
(TxnDate BETWEEN '2008-08-28' AND '2008-10-15')
or
TxnDate BETWEEN '2008-08-28' AND '2008-10-15'
are better choices..

this should solve your warnings.
if not, give a DESC sales_rec;

[Database Administrator]

Luuk schreef:
> chris_g schreef:
>> On Nov 13, 7:58 pm, Luuk wrote:
>>
>>> Could you post a EXPLAIN select..... for these 2 query's?
>>>
>>> Based on the execution time of these 2, i guess is there's a different
>>> index involved...
>>
>> Yes it looks that way. I'm also perplexed by the warnigns.
>>
>> mysql> SELECT MIN(TxnDate), MAX(TxnDate) FROM sales_rec
>> -> where TxnDate BETWEEN ('2008-08-28' AND '2008-10-15')
>> AND
>> -> TxnDateHalfHourly IS NULL AND ID>30947224;
>> +----+-------------+-----------+-------+---------------+---------
>> +---------+------+---------+-------------+
>> | id | select_type | table | type | possible_keys | key |
>> key_len | ref | rows | Extra |
>> +----+-------------+-----------+-------+---------------+---------
>> +---------+------+---------+-------------+
>> | 1 | SIMPLE | sales_rec | range | PRIMARY | PRIMARY |
>> 4 | NULL | 6937092 | Using where |
>> +----+-------------+-----------+-------+---------------+---------
>> +---------+------+---------+-------------+
>> 1 row in set, 3 warnings (1.75 sec)
>>
>>
>>
>> mysql> show warnings;
>> +---------+------
>> +-------------------------------------------------------------+
>> | Level | Code |
>> Message |
>> +---------+------
>> +-------------------------------------------------------------+
>> | Warning | 1292 | Truncated incorrect DOUBLE value:
>> '2008-08-28' |
>> | Warning | 1292 | Truncated incorrect DOUBLE value:
>> '2008-10-15' |
>> | Warning | 1292 | Incorrect datetime value: '1' for column 'TxnDate'
>> at row 1 |
>> +---------+------
>> +-------------------------------------------------------------+
>> 3 rows in set (0.00 sec)
>>
>> mysql> EXPLAIN SELECT MIN(TxnDate), MAX(TxnDate) FROM sales_rec
>> -> where TxnDate BETWEEN ('2008-08-28' AND '2008-10-15')
>> AND
>> -> ( TxnDateHalfHourly IS NULL AND ID>30947224);
>> +----+-------------+-----------+------+---------------+------+---------
>> +------+----------+-------------+
>> | id | select_type | table | type | possible_keys | key | key_len
>> | ref | rows | Extra |
>> +----+-------------+-----------+------+---------------+------+---------
>> +------+----------+-------------+
>> | 1 | SIMPLE | sales_rec | ALL | TxnDateIDX | NULL | NULL
>> | NULL | 13874184 | Using where |
>> +----+-------------+-----------+------+---------------+------+---------
>> +------+----------+-------------+
>> 1 row in set, 3 warnings (0.00 sec)
>>
>> mysql> show warnings;
>> +---------+------
>> +-------------------------------------------------------------+
>> | Level | Code |
>> Message |
>> +---------+------
>> +-------------------------------------------------------------+
>> | Warning | 1292 | Truncated incorrect DOUBLE value:
>> '2008-08-28' |
>> | Warning | 1292 | Truncated incorrect DOUBLE value:
>> '2008-10-15' |
>> | Warning | 1292 | Incorrect datetime value: '1' for column 'TxnDate'
>> at row 1 |
>> +---------+------
>> +-------------------------------------------------------------+
>> 3 rows in set (0.00 sec)
>
> i overlooked it, but:
> TxnDate BETWEEN ('2008-08-28' AND '2008-10-15')
> is not correct, at least, i think:
> (TxnDate BETWEEN '2008-08-28' AND '2008-10-15')
> or
> TxnDate BETWEEN '2008-08-28' AND '2008-10-15'
> are better choices..
>
> this should solve your warnings.
> if not, give a DESC sales_rec;

or,

the syntax is 'expr BETWEEN min AND max '
and not 'expr BETWEEN (min AND max )'

so, your statement
SELECT MIN(TxnDate), MAX(TxnDate) FROM sales_rec
where TxnDate BETWEEN ('2008-08-28' AND '2008-10-15')
AND
TxnDateHalfHourly IS NULL AND ID>30947224;

should be:
SELECT MIN(TxnDate), MAX(TxnDate) FROM sales_rec
where TxnDate BETWEEN '2008-08-28' AND '2008-10-15'
AND
TxnDateHalfHourly IS NULL AND ID>30947224;

OR:
SELECT MIN(TxnDate), MAX(TxnDate) FROM sales_rec
where TxnDate BETWEEN '2008-08-28' AND '2008-10-15'
AND
(TxnDateHalfHourly IS NULL AND ID>30947224);

and these last two statements should not make a difference...

[Database Administrator]

chris_g wrote:

> Background: I have a large table with sales records, which has a
> TxnDate datetime and a TxnHalfHourly date time.
> For each record, TxnHalfHourly is initially NULL, but it is set to a
> to floored time after each record is processed. I was running a query
> to look for unprocessed records and I got these two conflicting
> results.
>

Excuse me for hi-jacking this thread in a slightly different (but related)
direction. What techniques do people use to "prove" that an SQL select
statement with conditions actually gives the right result? (perhaps I
should re-phrase that to mean the intended result) In the example given by
the OP this could have been so easily missed. As a java programmer I'm
familiar with the technique of unit testing and I could use this technique
over a pre-built test table to check, but when the source table is huge I
would be hard pushed to know that I've got a truly representative sub-set
of the data and all edge cases had been included.

Regards

[Database Administrator]

Gilbert wrote:
> chris_g wrote:
>
>> Background: I have a large table with sales records, which has a
>> TxnDate datetime and a TxnHalfHourly date time.
>> For each record, TxnHalfHourly is initially NULL, but it is set to a
>> to floored time after each record is processed. I was running a query
>> to look for unprocessed records and I got these two conflicting
>> results.
>>
>
> Excuse me for hi-jacking this thread in a slightly different (but related)
> direction. What techniques do people use to "prove" that an SQL select
> statement with conditions actually gives the right result? (perhaps I
> should re-phrase that to mean the intended result) In the example given by
> the OP this could have been so easily missed. As a java programmer I'm
> familiar with the technique of unit testing and I could use this technique
> over a pre-built test table to check, but when the source table is huge I
> would be hard pushed to know that I've got a truly representative sub-set
> of the data and all edge cases had been included.
>
> Regards

Like any other test. Know the results you expect, and see if that's
what you get. You need to be familiar with the test data.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

[Database Administrator]

>Yes it looks that way. I'm also perplexed by the warnigns.
>
>mysql> EXPLAIN SELECT MIN(TxnDate), MAX(TxnDate) FROM sales_rec
> -> where TxnDate BETWEEN ('2008-08-28' AND '2008-10-15')
>AND
> -> ( TxnDateHalfHourly IS NULL AND ID>30947224);

You appear to have a precedence problem.

WHERE TxnDate BETWEEN
((((('2008-08-28' AND '2008-10-15')))))
AND
(((((TxnDateHalfHourly IS NULL AND ID>30947224)))))
This tests whether TxnDate is between two extremely wierd quantities.
What the heck IS the value of '2008-08-28' AND '2008-10-15'?

You probably meant
WHERE (((((TxnDate BETWEEN '2008-08-28' AND '2008-10-15')))))
AND
(((((TxnDateHalfHourly IS NULL)))))
AND
(((((ID > 30947224)))))

[Database Administrator]

Gordon Burditt schreef:
>> Yes it looks that way. I'm also perplexed by the warnigns.
>>
>> mysql> EXPLAIN SELECT MIN(TxnDate), MAX(TxnDate) FROM sales_rec
>> -> where TxnDate BETWEEN ('2008-08-28' AND '2008-10-15')
>> AND
>> -> ( TxnDateHalfHourly IS NULL AND ID>30947224);
>
> You appear to have a precedence problem.
>
> WHERE TxnDate BETWEEN
> ((((('2008-08-28' AND '2008-10-15')))))
> AND
> (((((TxnDateHalfHourly IS NULL AND ID>30947224)))))
> This tests whether TxnDate is between two extremely wierd quantities.
> What the heck IS the value of '2008-08-28' AND '2008-10-15'?

i think is FALSE



>
> You probably meant
> WHERE (((((TxnDate BETWEEN '2008-08-28' AND '2008-10-15')))))
> AND
> (((((TxnDateHalfHourly IS NULL)))))
> AND
> (((((ID > 30947224)))))
>
>