List:Bugs« Previous MessageNext Message »
From:sasha Date:November 3 2000 2:09pm
Subject:Re: bug with join and ELT and ORDER BY
View as plain text  
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
       <___/
Thread
bug with join and ELT and ORDER BYKyle Hayes1 Nov
  • Re: bug with join and ELT and ORDER BYThimble Smith1 Nov
  • Re: bug with join and ELT and ORDER BYsasha1 Nov
  • Re: bug with join and ELT and ORDER BYsasha3 Nov