Description:
When we compare an integer to a timestamp, we get different results if the comparison is done in the storage engine (by using LIMIT), than if the comparison is done in the server (by not using LIMIT).
# 13:24:13 Query: SELECT * FROM `AA` WHERE `int_key` < '2004-01-22 18:08:58' returns different result when executed with predicate 'ORDER BY `int_key` LIMIT 1073741824' (2 vs. 1 rows).
--- /tmp/randgen8207-server0.dump 2009-03-05 13:24:13.000000000 +0100
+++ /tmp/randgen8207-server1.dump 2009-03-05 13:24:13.000000000 +0100
@@ -1,2 +1 @@
-10 0 1 2000-11-15 0000-00-00 00:00:00 00:00:00 2002-03-21 00:00:00 0000-00-00 00:00:00
11 0 1 0000-00-00 0000-00-00 00:00:00 00:00:00 2002-05-09 00:00:00 0000-00-00 00:00:00
This is a pretty strange case, though, and quite artificial.
How to repeat:
To reproduce, run the following command, using the falcon_nolimit.yy pasted below:
runall.pl \
--basedir=/home/lb200670/devel/mysql/bugfest/ \
--mysqld=--loose-innodb-lock-wait-timeout=1 \
--mysqld=--table-lock-wait-timeout=1 \
--mysqld=--loose-falcon-lock-wait-timeout=1 \
--mysqld=--loose-falcon-debug-mask=2 \
--mysqld=--skip-safemalloc \
--grammar=conf/falcon_nolimit.yy \
--threads=1 \
--validator=Limit \
--reporters=Deadlock,ErrorLog,Backtrace,Recovery,WinPackage,Shutdown \
--duration=1200 \
--vardir=/tmp/vardir \
--mysqld=--log-output=file \
--queries=100000 \
--engine=falcon
Using the following grammar file:
query:
select | select | select | select | select |
select | select | select | select | select |
select | select | select | select | select |
dml | dml | dml | dml | dml
;
dml:
update | insert | delete ;
select:
SELECT * FROM _table where;
where:
|
WHERE _field sign value |
# WHERE _field BETWEEN value AND value |
WHERE _field IN ( value , value , value , value , value , value ) ;
sign:
> | < | = | >= | <> | <= | != ;
insert:
INSERT INTO _table ( _field , _field ) VALUES ( value , value ) ;
update:
UPDATE _table AS X SET _field = value where ;
delete:
DELETE FROM _table where LIMIT digit ;
transaction: START TRANSACTION | COMMIT | ROLLBACK ;
alter:
ALTER ONLINE TABLE _table DROP KEY letter |
ALTER ONLINE TABLE _table DROP KEY _field |
ALTER ONLINE TABLE _table ADD KEY letter ( _field ) |
ALTER ONLINE TABLE _table ADD KEY letter ( _field ) ;
value:
_english | _digit | _date | _datetime | _time ;
# Use only indexed fields:
_field:
`int_key` | `date_key` | `datetime_key` | `varchar_key` ;
Description: When we compare an integer to a timestamp, we get different results if the comparison is done in the storage engine (by using LIMIT), than if the comparison is done in the server (by not using LIMIT). # 13:24:13 Query: SELECT * FROM `AA` WHERE `int_key` < '2004-01-22 18:08:58' returns different result when executed with predicate 'ORDER BY `int_key` LIMIT 1073741824' (2 vs. 1 rows). --- /tmp/randgen8207-server0.dump 2009-03-05 13:24:13.000000000 +0100 +++ /tmp/randgen8207-server1.dump 2009-03-05 13:24:13.000000000 +0100 @@ -1,2 +1 @@ -10 0 1 2000-11-15 0000-00-00 00:00:00 00:00:00 2002-03-21 00:00:00 0000-00-00 00:00:00 11 0 1 0000-00-00 0000-00-00 00:00:00 00:00:00 2002-05-09 00:00:00 0000-00-00 00:00:00 This is a pretty strange case, though, and quite artificial. How to repeat: To reproduce, run the following command, using the falcon_nolimit.yy pasted below: runall.pl \ --basedir=/home/lb200670/devel/mysql/bugfest/ \ --mysqld=--loose-innodb-lock-wait-timeout=1 \ --mysqld=--table-lock-wait-timeout=1 \ --mysqld=--loose-falcon-lock-wait-timeout=1 \ --mysqld=--loose-falcon-debug-mask=2 \ --mysqld=--skip-safemalloc \ --grammar=conf/falcon_nolimit.yy \ --threads=1 \ --validator=Limit \ --reporters=Deadlock,ErrorLog,Backtrace,Recovery,WinPackage,Shutdown \ --duration=1200 \ --vardir=/tmp/vardir \ --mysqld=--log-output=file \ --queries=100000 \ --engine=falcon Using the following grammar file: query: select | select | select | select | select | select | select | select | select | select | select | select | select | select | select | dml | dml | dml | dml | dml ; dml: update | insert | delete ; select: SELECT * FROM _table where; where: | WHERE _field sign value | # WHERE _field BETWEEN value AND value | WHERE _field IN ( value , value , value , value , value , value ) ; sign: > | < | = | >= | <> | <= | != ; insert: INSERT INTO _table ( _field , _field ) VALUES ( value , value ) ; update: UPDATE _table AS X SET _field = value where ; delete: DELETE FROM _table where LIMIT digit ; transaction: START TRANSACTION | COMMIT | ROLLBACK ; alter: ALTER ONLINE TABLE _table DROP KEY letter | ALTER ONLINE TABLE _table DROP KEY _field | ALTER ONLINE TABLE _table ADD KEY letter ( _field ) | ALTER ONLINE TABLE _table ADD KEY letter ( _field ) ; value: _english | _digit | _date | _datetime | _time ; # Use only indexed fields: _field: `int_key` | `date_key` | `datetime_key` | `varchar_key` ;