Professional Web Applications Themes

MySQL Extract BUG?? - PHP Development

Assume a MYSQL table, foo. One column, bar datetime. Two rows: 2004-01-01 08:00:00 2004-02-01 08:00:00 select * from foo where extract(day from bar)=1; 2 rows in set... select * from foo where extract(month from bar)=2; 1 row in set... select * from foo where extract(month from bar)=1 && extract(day from bar)=1; 1 row in set... select * from foo where extract(month from bar)=2 && extract(day from bar)=1; Empty set... SHOULD BE 1 ROW!!! MySQL version 4.0.13, running on Windows 2000. Am I doing something incredibly stupid, or does this just make no sense? Thanks, Don...

  1. #1

    Default MySQL Extract BUG??

    Assume a MYSQL table, foo.

    One column, bar datetime.

    Two rows:
    2004-01-01 08:00:00
    2004-02-01 08:00:00

    select * from foo where extract(day from bar)=1;
    2 rows in set...

    select * from foo where extract(month from bar)=2;
    1 row in set...

    select * from foo where extract(month from bar)=1 && extract(day from
    bar)=1;
    1 row in set...

    select * from foo where extract(month from bar)=2 && extract(day from
    bar)=1;
    Empty set... SHOULD BE 1 ROW!!!

    MySQL version 4.0.13, running on Windows 2000.

    Am I doing something incredibly stupid, or does this just make no sense?

    Thanks,
    Don
    Don Guest

  2. #2

    Default Re: MySQL Extract BUG??

    Don Crossman wrote: 
     
     

    Works fine for me.

    $ mysql --version
    mysql Ver 12.22 Distrib 4.0.16, for pc-linux-gnu (i686)


    Have you tried
    : select extract(month from bar), extract(day from bar) from foo;
    : select * from foo where (extract(month from bar)=2) && (extract(day from bar)=1);
    --
    --= my mail box only accepts =--
    --= Content-Type: text/plain =--
    --= Size below 10001 bytes =--
    Pedro Guest

  3. #3

    Default Re: MySQL Extract BUG??

    On Mon, 05 Jan 2004 17:44:23 -0000, Don Crossman <email.com>
    wrote:
     

    mysql> select * from foo;
    +---------------------+
    | bar |
    +---------------------+
    | 2004-01-01 08:00:00 |
    | 2004-02-01 08:00:00 |
    +---------------------+
    2 rows in set (0.00 sec)

    mysql> select * from foo where extract(day from bar)=1;
    +---------------------+
    | bar |
    +---------------------+
    | 2004-01-01 08:00:00 |
    | 2004-02-01 08:00:00 |
    +---------------------+
    2 rows in set (0.00 sec)

    mysql> select * from foo where extract(month from bar)=2;
    +---------------------+
    | bar |
    +---------------------+
    | 2004-02-01 08:00:00 |
    +---------------------+
    1 row in set (0.00 sec)

    mysql> select * from foo where extract(month from bar)=1
    -> and extract(day from bar)=1;
    +---------------------+
    | bar |
    +---------------------+
    | 2004-01-01 08:00:00 |
    +---------------------+
    1 row in set (0.00 sec)

    mysql> select * from foo where extract(month from bar)=2
    -> and extract(day from bar)=1;
    +---------------------+
    | bar |
    +---------------------+
    | 2004-02-01 08:00:00 |
    +---------------------+
    1 row in set (0.00 sec)

    4.0.16, Linux.

    --
    Andy Hassall (co.uk) icq(5747695) (http://www.andyh.co.uk)
    Space: disk usage ysis tool (http://www.andyhsoftware.co.uk/space)
    Andy Guest

  4. #4

    Default Re: MySQL Extract BUG??

    Andy Hassall <co.uk> wrote in
    news:com:
     
    >
    > mysql> select * from foo;
    > +---------------------+
    >| bar |
    > +---------------------+
    >| 2004-01-01 08:00:00 |
    >| 2004-02-01 08:00:00 |
    > +---------------------+
    > 2 rows in set (0.00 sec)
    >
    > mysql> select * from foo where extract(day from bar)=1;
    > +---------------------+
    >| bar |
    > +---------------------+
    >| 2004-01-01 08:00:00 |
    >| 2004-02-01 08:00:00 |
    > +---------------------+
    > 2 rows in set (0.00 sec)
    >
    > mysql> select * from foo where extract(month from bar)=2;
    > +---------------------+
    >| bar |
    > +---------------------+
    >| 2004-02-01 08:00:00 |
    > +---------------------+
    > 1 row in set (0.00 sec)
    >
    > mysql> select * from foo where extract(month from bar)=1
    > -> and extract(day from bar)=1;
    > +---------------------+
    >| bar |
    > +---------------------+
    >| 2004-01-01 08:00:00 |
    > +---------------------+
    > 1 row in set (0.00 sec)
    >
    > mysql> select * from foo where extract(month from bar)=2
    > -> and extract(day from bar)=1;
    > +---------------------+
    >| bar |
    > +---------------------+
    >| 2004-02-01 08:00:00 |
    > +---------------------+
    > 1 row in set (0.00 sec)
    >
    > 4.0.16, Linux.
    >[/ref]

    It looks like I'm off to the upgrade farm. Either that, or it's a Windows
    bug!

    Thank you, gentlemen.
    Don Guest

  5. #5

    Default Re: MySQL Extract BUG??

    Don Crossman <email.com> wrote in
    news:168.3.44:
     
    >>
    >> mysql> select * from foo;
    >> +---------------------+
    >>| bar |
    >> +---------------------+
    >>| 2004-01-01 08:00:00 |
    >>| 2004-02-01 08:00:00 |
    >> +---------------------+
    >> 2 rows in set (0.00 sec)
    >>
    >> mysql> select * from foo where extract(day from bar)=1;
    >> +---------------------+
    >>| bar |
    >> +---------------------+
    >>| 2004-01-01 08:00:00 |
    >>| 2004-02-01 08:00:00 |
    >> +---------------------+
    >> 2 rows in set (0.00 sec)
    >>
    >> mysql> select * from foo where extract(month from bar)=2;
    >> +---------------------+
    >>| bar |
    >> +---------------------+
    >>| 2004-02-01 08:00:00 |
    >> +---------------------+
    >> 1 row in set (0.00 sec)
    >>
    >> mysql> select * from foo where extract(month from bar)=1
    >> -> and extract(day from bar)=1;
    >> +---------------------+
    >>| bar |
    >> +---------------------+
    >>| 2004-01-01 08:00:00 |
    >> +---------------------+
    >> 1 row in set (0.00 sec)
    >>
    >> mysql> select * from foo where extract(month from bar)=2
    >> -> and extract(day from bar)=1;
    >> +---------------------+
    >>| bar |
    >> +---------------------+
    >>| 2004-02-01 08:00:00 |
    >> +---------------------+
    >> 1 row in set (0.00 sec)
    >>
    >> 4.0.16, Linux.
    >>[/ref]
    >
    > It looks like I'm off to the upgrade farm. Either that, or it's a
    > Windows bug!
    >
    > Thank you, gentlemen.
    >[/ref]

    It apparently was a bug in the Windows version of 4.0.13. I just upgraded
    to 4.0.17, and it all worked fine!
    Don Guest

Similar Threads

  1. Unicode extract:
    By cstham@adobeforums.com in forum Adobe Acrobat SDK
    Replies: 7
    Last Post: August 11th, 03:59 AM
  2. Replies: 2
    Last Post: February 14th, 10:54 AM
  3. Replies: 0
    Last Post: January 29th, 02:03 PM
  4. Extract Pages help......
    By Kristian_Wells@adobeforums.com in forum Adobe Acrobat Macintosh
    Replies: 1
    Last Post: June 1st, 09:29 PM
  5. extract from html
    By Lydia Shawn in forum PERL Miscellaneous
    Replies: 6
    Last Post: August 7th, 08:38 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