Professional Web Applications Themes

BUG in Union Implementation??? Please confirm or explain if possible - MySQL

Hi all, I believe to have found a bug in MySQL's union implementation. Can someone confirm this, please or convince me that this is not a buggy behaviour of mysql : UNION seems to behave like DISTINCT by default: mysql> select 2 c1 -> union -> select 1 c1 -> union -> select 2 c1 -> union -> select 1 c1; +----+ | c1 | +----+ | 2 | | 1 | +----+ 2 rows in set (0.00 sec) mysql> select 2 c1,1 union select 1 c1,2 union select 2 c1,3 union select 1 c1,4; +----+---+ | c1 | 1 ...

  1. #1

    Default BUG in Union Implementation??? Please confirm or explain if possible

    Hi all,
    I believe to have found a bug in MySQL's union implementation. Can someone
    confirm this, please or convince me that this is not a buggy behaviour of
    mysql :

    UNION seems to behave like DISTINCT by default:

    mysql> select 2 c1
    -> union
    -> select 1 c1
    -> union
    -> select 2 c1
    -> union
    -> select 1 c1;
    +----+
    | c1 |
    +----+
    | 2 |
    | 1 |
    +----+
    2 rows in set (0.00 sec)

    mysql> select 2 c1,1 union select 1 c1,2 union select 2 c1,3 union select 1
    c1,4;
    +----+---+
    | c1 | 1 |
    +----+---+
    | 2 | 1 |
    | 1 | 2 |
    | 2 | 3 |
    | 1 | 4 |
    +----+---+
    4 rows in set (0.00 sec)

    mysql> select 2 c1,1 union select 1 c1,2 union select 2 c1,3 union select
    1,2;
    +----+---+
    | c1 | 1 |
    +----+---+
    | 2 | 1 |
    | 1 | 2 |
    | 2 | 3 |
    +----+---+
    3 rows in set (0.00 sec)

    mysql> select avg(c1),avg(distinct c1),sum(c1),count(c1),count(distinct
    c1),count(*) from
    -> (
    -> select 2 c1
    -> union
    -> select 1 c1
    -> union
    -> select 1 c1
    -> union
    -> select 1
    -> ) a
    -> ;
    +-------+----------------+-------+---------+-------------------+----------+
    |avg(c1)|avg(distinct c1)|sum(c1)|count(c1)|count(distinct c1) | count(*) |
    +-------+----------------+-------+---------+-------------------+----------+
    |1.5000 | 1.5000 | 3 | 2 | 2 | 2 |
    +-------+----------------+-------+---------+-------------------+----------+
    1 row in set (0.00 sec)

    but I would have expected:

    +-------+----------------+-------+---------+-------------------+----------+
    |avg(c1)|avg(distinct c1)|sum(c1)|count(c1)|count(distinct c1) | count(*) |
    +-------+----------------+-------+---------+-------------------+----------+
    |1.2500 | 1.5000 | 5 | 4 | 2 | 4 |
    +-------+----------------+-------+---------+-------------------+----------+


    TIA,

    CVH


    CVH Guest

  2. #2

    Default Re: BUG in Union Implementation??? Please confirm or explain if possible

    On 11 Jul, 09:10, CVH <me> wrote: 

    UNION always behaves like DISTINCT in any SQL implementation. That's
    why there is also UNION ALL.

    As the manual so clearly says (http://dev.mysql.com/doc/refman/5.0/en/
    union.html)

    "The default behavior for UNION is that duplicate rows are removed
    from the result. The optional DISTINCT keyword has no effect other
    than the default because it also specifies duplicate-row removal. With
    the optional ALL keyword, duplicate-row removal does not occur and the
    result includes all matching rows from all the SELECT statements."

    Don't know how you missed that when you were reading that section
    before you posted here!

    Captain Guest

  3. #3

    Default Re: BUG in Union Implementation??? Please confirm or explain if possible

    Captain Paralytic wrote:
     [/ref]
    +-------+----------------+-------+---------+-------------------+----------+ [/ref]
    +-------+----------------+-------+---------+-------------------+----------+ [/ref]
    +-------+----------------+-------+---------+-------------------+----------+ [/ref]
    +-------+----------------+-------+---------+-------------------+----------+ [/ref]
    +-------+----------------+-------+---------+-------------------+----------+ [/ref]
    +-------+----------------+-------+---------+-------------------+----------+ 
    >
    > UNION always behaves like DISTINCT in any SQL implementation. That's
    > why there is also UNION ALL.
    >
    > As the manual so clearly says (http://dev.mysql.com/doc/refman/5.0/en/
    > union.html)
    >
    > "The default behavior for UNION is that duplicate rows are removed
    > from the result. The optional DISTINCT keyword has no effect other
    > than the default because it also specifies duplicate-row removal. With
    > the optional ALL keyword, duplicate-row removal does not occur and the
    > result includes all matching rows from all the SELECT statements."
    >
    > Don't know how you missed that when you were reading that section
    > before you posted here![/ref]
    Hmm, You're right. How could i have missed that.
    Thanks
    CVH Guest

Similar Threads

  1. Is confirm() too much to ask?
    By Iceborer in forum Macromedia ColdFusion
    Replies: 1
    Last Post: March 1st, 12:14 PM
  2. confirm before delete ?
    By mark in forum ASP.NET Data Grid Control
    Replies: 2
    Last Post: May 20th, 02:15 PM
  3. Confirm a bug.....
    By Bryan Martin in forum ASP.NET Security
    Replies: 0
    Last Post: November 23rd, 03:08 PM
  4. Replies: 4
    Last Post: September 15th, 06:11 PM
  5. Can someone confirm this for me.........
    By Jason da Cunha in forum Adobe Photoshop 7, CS, CS2 & CS3
    Replies: 1
    Last Post: July 18th, 01:44 AM

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