Kyle Hayes wrote:
>
> Do the following on a running MySQL instance (we tested on 3.23.16 , 3.23.21
> and 3.23.25). We use MySQL on x86 Linux with Red Hat 6.0, 6.1, 6.2, and
> Debian 2.2.
>
> -----------------------
> # create the test tables
> create temporary table elt_ck1 (id int(10) not null unique);
> create temporary table elt_ck2 (id int(10) not null primary key,
> val int(10) not null);
>
> # put in some initial values
> insert into elt_ck1 values (1),(2),(4);
> insert into elt_ck2 values (1,1),(2,1),(3,1),(4,2);
>
> # do a query using ELT, a join and an ORDER BY.
> select one.id, elt(two.val,'one','two') from elt_ck1 one, elt_ck2 two
> where two.id=one.id order by one.id;
>
> This is what I get and it is wrong.
> +----+--------------------------+
> | id | elt(two.val,'one','two') |
> +----+--------------------------+
> | 1 | two |
> | 2 | two |
> | 4 | two |
> +----+--------------------------+
>
> If the ORDER BY is removed, then this prints what I expect. If I remove the
> ELT, then the ORDER BY doesn't change the correctness of the result. We did
> a tiny bit of testing and it appears that without the join, this stuff works.
>
> We do have a full support contract. This is causing us grief in some of our
> reports because we are reporting the wrong values!
There was also the same bug in make_set() - mysqld was getting confused when
using those functions on a temporary table. Fix:
--- PARENT/sql/item_strfunc.cc Mon Oct 16 15:47:14 2000
+++ NEW/sql/item_strfunc.cc Fri Nov 3 07:04:33 2000
@@ -1190,6 +1190,7 @@
}
maybe_null=1; // NULL if wrong first
arg
used_tables_cache|=item->used_tables();
+ const_item_cache&=item->const_item();
}
@@ -1245,6 +1246,7 @@
for (uint i=1 ; i < arg_count ; i++)
max_length+=args[i]->max_length;
used_tables_cache|=item->used_tables();
+ const_item_cache&=item->const_item();
}
Or wait until 3.23.28 :-)
--
MySQL Development Team
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Sasha Pachev <sasha@stripped>
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, http://www.mysql.com/
/_/ /_/\_, /___/\___\_\___/ Provo, Utah, USA
<___/
