Null values return at in Queries

------=_NextPart_000_0005_01C31369.FFFEFA60
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

SELECT `jobtime`.`workday` , `jobtime`.`jobn` , `jobtime`.`timein` , =
`jobtime`.`timeout` , `jobtime`.`workindex` , @H :=3D HOUR ( =
`jobtime`.`timeout` - `jobtime`.`timein` ) + MINUTE( =
`jobtime`.`timeout` - `jobtime`.`timein` ) / 60, @R :=3D =
`rates`.`payrate` , @DC :=3D @H * @R FROM `jobtime` INNER JOIN =
`rates` ON ( `jobtime`.`workindex` =3D `rates`.`rateindex` ) WHERE =
`jobtime`.`jobn` =3D 2002 ORDER BY `jobtime`.`workday` ASC=20

This query works fine, except that it returns null values at random for =
the @H variable, which creates a null value for @DC. I think this could =
be a function bug in the HOURS function or something along those lines. =
All I would like to know is if it's a bug or not. The structure of the =
two tables mentioned in the queries are as follows:

CREATE TABLE jobtime (
workday date default NULL,
jobn int(11) default NULL,
timein time default NULL,
timeout time default NULL,
workindex text,
description text
) TYPE=3DMyISAM;

#
# Dumping data for table `jobtime`
#

INSERT INTO jobtime VALUES ('2001-03-22', 2001, '16:00:00', '18:00:00', =
'fw-2', '\r');
INSERT INTO jobtime VALUES ('2001-03-23', 2001, '16:00:00', '19:00:00', =
'fw-2', '\r');
INSERT INTO jobtime VALUES ('2001-03-25', 2001, '16:00:00', '20:00:00', =
'drt', '\r');
INSERT INTO jobtime VALUES ('2001-03-27', 2002, '16:30:00', '18:30:00', =
'fw-2', '\r');
INSERT INTO jobtime VALUES ('2001-04-02', 2003, '13:00:00', '15:00:00', =
'fw-1', '\r');

CREATE TABLE rates (
rateindex text NOT NULL,
payrate double NOT NULL default '0',
description text NOT NULL
) TYPE=3DMyISAM;

#
# Dumping data for table `rates`
#

INSERT INTO rates VALUES ('fw-2', '75', '2 man field creww');
INSERT INTO rates VALUES ('fw-1', '45', '1 Man field crew');
INSERT INTO rates VALUES ('drt', '45', 'Office Drafting');
INSERT INTO rates VALUES ('rsh', '45', 'Project Research');

Please let me know what you find out. Thanks in advance =
mailto:mnalley@up-link.net



---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.459 / Virus Database: 258 - Release Date: 2/24/2003

------=_NextPart_000_0005_01C31369.FFFEFA60--

Re: Null values return at in Queries

On Tue, 2003-05-06 at 09:54, Matt Nalley wrote:


Matt,

You have not mentioned MySQL version you have this behavior at

I've tested with MySQL 4.0.13 and was not able to repeat such behavior.

This query ran few tens of times always returned one row with no NULL
values.

Could you please check this example with MySQL 4.0.13 (to be released in
a few days) and try to create more repeatable example if problem
persists.


> SELECT `jobtime`.`workday` , `jobtime`.`jobn` , `jobtime`.`timein` , `jobtime`.`timeout` , `jobtime`.`workindex` , @H := HOUR ( `jobtime`.`timeout` - `jobtime`.`timein` ) + MINUTE( `jobtime`.`timeout` - `jobtime`.`timein` ) / 60, @R := `rates`.`payrate` , @DC := @H * @R FROM `jobtime` INNER JOIN `rates` ON ( `jobtime`.`workindex` = `rates`.`rateindex` ) WHERE `jobtime`.`jobn` = 2002 ORDER BY `jobtime`.`workday` ASC
>
> This query works fine, except that it returns null values at random for the @H variable, which creates a null value for @DC. I think this could be a function bug in the HOURS function or something along those lines. All I would like to know is if it's a bug or not. The structure of the two tables mentioned in the queries are as follows:
>
> CREATE TABLE jobtime (
> workday date default NULL,
> jobn int(11) default NULL,
> timein time default NULL,
> timeout time default NULL,
> workindex text,
> description text
> ) TYPE=MyISAM;
>
> #
> # Dumping data for table `jobtime`
> #
>
> INSERT INTO jobtime VALUES ('2001-03-22', 2001, '16:00:00', '18:00:00', 'fw-2', '\r');
> INSERT INTO jobtime VALUES ('2001-03-23', 2001, '16:00:00', '19:00:00', 'fw-2', '\r');
> INSERT INTO jobtime VALUES ('2001-03-25', 2001, '16:00:00', '20:00:00', 'drt', '\r');
> INSERT INTO jobtime VALUES ('2001-03-27', 2002, '16:30:00', '18:30:00', 'fw-2', '\r');
> INSERT INTO jobtime VALUES ('2001-04-02', 2003, '13:00:00', '15:00:00', 'fw-1', '\r');
>
> CREATE TABLE rates (
> rateindex text NOT NULL,
> payrate double NOT NULL default '0',
> description text NOT NULL
> ) TYPE=MyISAM;
>
> #
> # Dumping data for table `rates`
> #
>
> INSERT INTO rates VALUES ('fw-2', '75', '2 man field creww');
> INSERT INTO rates VALUES ('fw-1', '45', '1 Man field crew');
> INSERT INTO rates VALUES ('drt', '45', 'Office Drafting');
> INSERT INTO rates VALUES ('rsh', '45', 'Project Research');
>
> Please let me know what you find out. Thanks in advance mailto:mnalley@up-link.net
>
>
>
> ---
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.459 / Virus Database: 258 - Release Date: 2/24/2003
--
Are you MySQL certified?, http://www.mysql.com/certification/
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Peter Zaitsev
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer
/_/ /_/\_, /___/\___\_\___/ Moscow, Russia
<___/ www.mysql.com


--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=gcdmb-bugs@m.gmane.org