Re: bug with join and ELT and ORDER BY

[sasha]

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!
> 
> Best,
> Kyle
> 
> --
> *********************************************************************
> "HEAR THE DIFFERENCE" with a live demo of our products at:
> Communications Solutions Fall 2000, Dec. 5-7, Las Vegas, NV
> *********************************************************************
> 
> Kyle Hayes
> Quicknet Technologies              t: [phone]
> 520 Townsend St. Suite D          f: [phone]
> San Francisco, CA 94103         w: http://www.quicknet.net
> USA

Thanks for an excellent bug report! I have been able to repeat it and now am
investigating the cause...

-- 
MySQL Development Team
   __  ___     ___ ____  __ 
  /  |/  /_ __/ __/ __ \/ /   Sasha Pachev <sasha@stripped>
 / /|_/ / // /\ \/ /_/ / /__  MySQL AB, http://www.mysql.com/
/_/  /_/\_, /___/\___\_\___/  Provo, Utah, USA
       <___/