Professional Web Applications Themes

Wierd query result on MySQL 5 system - MySQL

I have the following table (complete with sample data). When I run the query below on a mysql 4.1.14 system, the only row returned is row 4 as expected. However, when I run the same query on a mysql 5.0.19 system, I get rows 2, 3 & 4; which is not what I would expect. I have found that if I replace the "date_add( Reminder_Sent, INTERVAL 7 DAY )" with a hard coded '2006-01-15', then I get the expected row 4 only, but that defeats the object of the query as the dates in Reminder_Sent are not normally always the ...

  1. #1

    Default Wierd query result on MySQL 5 system

    I have the following table (complete with sample data).
    When I run the query below on a mysql 4.1.14 system, the only row returned
    is row 4 as expected.
    However, when I run the same query on a mysql 5.0.19 system, I get rows 2, 3
    & 4; which is not what I would expect.
    I have found that if I replace the "date_add( Reminder_Sent, INTERVAL 7
    DAY )" with a hard coded '2006-01-15', then I get the expected row 4 only,
    but that defeats the object of the query as the dates in Reminder_Sent are
    not normally always the same.

    Can anyone point me to what is going on here?

    TIA
    Regards
    Paul Lautman


    CREATE TABLE `balls_in_play2` (
    `Ball_Number` tinyint(2) NOT NULL default '0',
    `Name` varchar(50) NOT NULL default '',
    `email` varchar(50) NOT NULL default '',
    `Expiry_Date` date NOT NULL default '0000-00-00',
    `Reminder_Sent` date NOT NULL default '0000-00-00',
    PRIMARY KEY (`Ball_Number`)
    ) ENGINE=MyISAM DEFAULT CHT=latin1;

    --
    -- Dumping data for table `balls_in_play2`
    --

    INSERT INTO `balls_in_play2` (`Ball_Number`, `Name`, `email`, `Expiry_Date`,
    `Reminder_Sent`) VALUES
    (1, 'John Doe', '', '2007-03-31', '2006-01-01'),
    (2, 'Hilary Smith', '', '2006-11-04', '2006-01-01'),
    (3, 'Wendy Jones', '', '2006-10-28', '2006-01-01'),
    (4, 'Fred Brown', '', '2006-05-06', '2006-01-01');

    SELECT *
    FROM balls_in_play2
    WHERE (expiry_date BETWEEN date_add( Reminder_Sent, INTERVAL 7 DAY ) AND
    date_add( current_date, INTERVAL 7 DAY ));


    Paul Lautman Guest

  2. #2

    Default Re: Wierd query result on MySQL 5 system

    To see the problem first hand, I have set up
    [url]http://www.sunnyacres.co.uk/bb/bbqt.php[/url]
    where you can run the query below to see the results.
    You can also run the query

    SELECT *, date_add( Reminder_Sent, INTERVAL 7 DAY ), date_add( current_date,
    INTERVAL 7 DAY ) FROM balls_in_play2 WHERE (expiry_date BETWEEN date_add(
    Reminder_Sent, INTERVAL 7 DAY ) AND date_add( current_date, INTERVAL 7
    DAY ));

    to see that the expiry_date is not within the bounds.

    Paul Lautman wrote:
    > I have the following table (complete with sample data).
    > When I run the query below on a mysql 4.1.14 system, the only row
    > returned is row 4 as expected.
    > However, when I run the same query on a mysql 5.0.19 system, I get
    > rows 2, 3 & 4; which is not what I would expect.
    > I have found that if I replace the "date_add( Reminder_Sent, INTERVAL
    > 7 DAY )" with a hard coded '2006-01-15', then I get the expected row 4
    > only, but that defeats the object of the query as the dates in
    > Reminder_Sent are not normally always the same.
    >
    > Can anyone point me to what is going on here?
    >
    > TIA
    > Regards
    > Paul Lautman
    >
    >
    > CREATE TABLE `balls_in_play2` (
    > `Ball_Number` tinyint(2) NOT NULL default '0',
    > `Name` varchar(50) NOT NULL default '',
    > `email` varchar(50) NOT NULL default '',
    > `Expiry_Date` date NOT NULL default '0000-00-00',
    > `Reminder_Sent` date NOT NULL default '0000-00-00',
    > PRIMARY KEY (`Ball_Number`)
    > ) ENGINE=MyISAM DEFAULT CHT=latin1;
    >
    > --
    > -- Dumping data for table `balls_in_play2`


    Paul Lautman Guest

Similar Threads

  1. Replies: 4
    Last Post: July 30th, 02:02 AM
  2. Outputting query results to excel.. wierd!!
    By jjay@aus in forum Coldfusion - Advanced Techniques
    Replies: 0
    Last Post: April 15th, 06:17 AM
  3. wierd Array of Hash result
    By superfly2 in forum PERL Miscellaneous
    Replies: 1
    Last Post: September 12th, 07:38 PM
  4. Wierd result from hash array
    By Pacman in forum PERL Miscellaneous
    Replies: 3
    Last Post: September 11th, 10:02 PM
  5. Result of Mysql Query in a PHP table
    By Felix in forum PHP Development
    Replies: 2
    Last Post: July 8th, 01:31 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139