| Bug #22679 | Join using addtime and subtime functions and BETWEEN stopped working V 5.0 | ||
|---|---|---|---|
| Submitted: | 25 Sep 2006 19:01 | Modified: | 26 Sep 2006 1:27 |
| Reporter: | Todd Schmitter | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server | Severity: | S3 (Non-critical) |
| Version: | 5.0.21 | OS: | |
| Assigned to: | CPU Architecture: | Any | |
[25 Sep 2006 22:42]
MySQL Verification Team
Thank you for the bug report. Could you please provide a test script with the tables definition/insert data/queries and the wrong result you got and the expected result. Thanks in advance.
[26 Sep 2006 1:27]
Todd Schmitter
The "original" query that stopped working should have been this (I mistakenly pasted the "new" query in the original post):
SELECT t.date AS date,
t.time AS time,
t.qwhsssid AS subsystem,
qw0225av / ( 1024 *1024 ) AS qw0225av,
qw0225cr / ( 1024 *1024 ) AS qw0225cr,
qw0225so / ( 1024 *1024 ) AS qw0225so,
qw0225at, qtmaxds,
qw0225cd / ( 1024 *1024 ) AS qw0225cd,
qsstcont,
qsstcrit,
qsstabnd
FROM schmitter.db2stato s,
schmitter.t102s225 t
WHERE t.date = '2006-09-21'
AND t.qwhsssid = 'D2D0'
AND t.qw0225av =865607680
AND t.date = s.date
AND t.time BETWEEN subtime( s.time, '00:00:01' ) AND addtime( s.time, '00:00:01' )
AND t.qwhsssid = s.qwhsssid
In any case, I have now created test tables to produce a repeatable script, and the "bug" is not occurring. So now I suspect there is a problem with the original tables.
Description: I am running a query that joins two tables on date, time and DB2 subsystem id. This query works in MySQL version 4.1.7, but not in version 5.0.21. I was able to get the query to work in version 5.0.21 by changing the BETWEEN predicate in the WHERE clause to a >= AND <= predicate. I am using the MyISAM engine. The query that stopped working in version 5.0.21: SELECT t.date AS date, t.time AS time, t.qwhsssid AS subsystem, qw0225av / ( 1024 *1024 ) AS qw0225av, qw0225cr / ( 1024 *1024 ) AS qw0225cr, qw0225so / ( 1024 *1024 ) AS qw0225so, qw0225at, qtmaxds, qw0225cd / ( 1024 *1024 ) AS qw0225cd, qsstcont, qsstcrit, qsstabnd FROM db2.db2stato s, db2.t102s225 t WHERE t.date = '2006-09-21' AND t.qwhsssid = 'D2D0' AND t.qw0225av =865607680 AND t.date = s.date AND t.time >= subtime( s.time, '00:00:01' ) AND t.time <= addtime( s.time, '00:00:01' ) AND t.qwhsssid = s.qwhsssid This query should return one row (shown below), but does not return any rows. 1 row returned: date 2006-09-21 time 00:01:28 subsystem D2D0 qw0225av 825.5078 qw0225cr 19.5508 qw0225so 55.5500 qw0225at 1 qtmaxds 1444 qw0225cd 0.3594 qsstcont 0 qsstcrit 0 qsstabnd 0 The modified query that DOES work in 5.0.21: SELECT t.date AS date, t.time AS time, t.qwhsssid AS subsystem, qw0225av / ( 1024 *1024 ) AS qw0225av, qw0225cr / ( 1024 *1024 ) AS qw0225cr, qw0225so / ( 1024 *1024 ) AS qw0225so, qw0225at, qtmaxds, qw0225cd / ( 1024 *1024 ) AS qw0225cd, qsstcont, qsstcrit, qsstabnd FROM db2.db2stato s, db2.t102s225 t WHERE t.date = '2006-09-21' AND t.qwhsssid = 'D2D0' AND t.qw0225av =865607680 AND t.date = s.date AND t.time >= subtime( s.time, '00:00:01' ) AND t.time <= addtime( s.time, '00:00:01' ) AND t.qwhsssid = s.qwhsssid Table definitions (first several columns only, for readability): TABLE t102s225 Field Type Null Key Default Extra DATE date NO PRI 0000-00-00 TIME time NO PRI 00:00:00 SHIFT tinyint(4) NO 0 HOUR tinyint(4) NO 0 QWHSSSID varchar(8) NO PRI SYSTEM varchar(8) NO QWHCAID varchar(8) NO QWHCATYP char(1) NO QWHCCN varchar(8) NO QWHCCV varchar(12) NO QWHCOPID varchar(8) NO QWHCPLAN varchar(8) NO QWHDPRID varchar(8) NO . . . TABLE db2stato Field Type Null Key Default Extra DATE date NO PRI 0000-00-00 TIME time NO PRI 00:00:00 SHIFT tinyint(4) NO 0 HOUR tinyint(4) NO 0 DURATM float NO 0 SYSTEM char(8) NO QWHSSSID char(8) NO PRI . . . How to repeat: 1. Populate tables with defintions shown above with data in the primary key fields (date, time and qwhsssid, which is any 4-byte alpha field). 2. Make sure there is at least one matching row between the two tables on date,time,qwhsssid 3. Run the first query above with the BETWEEN. Suggested fix: Fix unknown. Work-around is to use >= AND <= instead of BETWEEN.