MySQL Bug#11766758: MATERIALIZATION=ON/OFF LEADS TO DIFFERENT RESULT SET WHEN USING IN & VAR_POP

--- m/r/compress.result	2013-07-22 17:03:39.000000000 +0400

+++ r/compress.result	2014-03-03 13:58:10.071341000 +0400

@@ -1506,17 +1509,17 @@

 50	11

 select count(*),min(fld4),max(fld4),sum(fld1),avg(fld1),std(fld1),variance(fld1) from t2 where companynr = 34 and fld4<>"";

 count(*)	min(fld4)	max(fld4)	sum(fld1)	avg(fld1)	std(fld1)	variance(fld1)

-70	absentee	vest	17788966	254128.0857	3272.5939722090234	10709871.306938833

+70	absentee	vest	17788966	254128.0857	3272.5940	10709871.3069

 explain extended select count(*),min(fld4),max(fld4),sum(fld1),avg(fld1),std(fld1),variance(fld1) from t2 where companynr = 34 and fld4<>"";

 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra

 1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	1199	100.00	Using where

 Warnings:

-Note	1003	/* select#1 */ select count(0) AS `count(*)`,min(`test`.`t2`.`fld4`) AS `min(fld4)`,max(`test`.`t2`.`fld4`) AS `max(fld4)`,sum(`test`.`t2`.`fld1`) AS `sum(fld1)`,avg(`test`.`t2`.`fld1`) AS `avg(fld1)`,std(`test`.`t2`.`fld1`) AS `std(fld1)`,variance(`test`.`t2`.`fld1`) AS `variance(fld1)` from `test`.`t2` where ((`test`.`t2`.`companynr` = 34) and (`test`.`t2`.`fld4` <> ''))

+Note	1003	select count(0) AS `count(*)`,min(`test`.`t2`.`fld4`) AS `min(fld4)`,max(`test`.`t2`.`fld4`) AS `max(fld4)`,sum(`test`.`t2`.`fld1`) AS `sum(fld1)`,avg(`test`.`t2`.`fld1`) AS `avg(fld1)`,std(`test`.`t2`.`fld1`) AS `std(fld1)`,variance(`test`.`t2`.`fld1`) AS `variance(fld1)` from `test`.`t2` where ((`test`.`t2`.`companynr` = 34) and (`test`.`t2`.`fld4` <> ''))

 select companynr,count(*),min(fld4),max(fld4),sum(fld1),avg(fld1),std(fld1),variance(fld1) from t2 group by companynr limit 3;

 companynr	count(*)	min(fld4)	max(fld4)	sum(fld1)	avg(fld1)	std(fld1)	variance(fld1)

-00	82	Anthony	windmills	10355753	126289.6707	115550.97568479746	13352027981.708656

-29	95	abut	wetness	14473298	152350.5053	8368.547956641249	70032594.90260443

-34	70	absentee	vest	17788966	254128.0857	3272.5939722090234	10709871.306938833

+00	82	Anthony	windmills	10355753	126289.6707	115550.9757	13352027981.7087

+29	95	abut	wetness	14473298	152350.5053	8368.5480	70032594.9026

+34	70	absentee	vest	17788966	254128.0857	3272.5940	10709871.3069

 select companynr,t2nr,count(price),sum(price),min(price),max(price),avg(price) from t3 where companynr = 37 group by companynr,t2nr limit 10;

 companynr	t2nr	count(price)	sum(price)	min(price)	max(price)	avg(price)

 37	1	1	5987435	5987435	5987435	5987435.0000

revno: 3690.68.30

committer: Oystein Grovlen <oystein.grovlen@oracle.com>

branch nick: mysql-trunk

timestamp: Wed 2012-06-20 12:37:14 +0200

message:

  Bug#11766758: MATERIALIZATION=ON/OFF LEADS TO DIFFERENT RESULT SET

  WHEN USING IN & VAR_POP

  This bug is caused by the fact that number of decimals in VAR_POP

  result, was calculated as the number of decimals in the argument

  (here int => 0) plus 4.

  Hence, in the example in the bug report, 3.5556 is stored in t2.X.

  When materialization is off, result of inner query is compared using

  Arg_comparator::compare_real_fixed() which takes number of decimals into

  account.

  When inner query is materialized, no truncation happens and the materialized

  column contains 3.555555555...

  The IN-expression is evaluated by doing hash-lookup into the materialized

  table, and since no record matches 3.5556, the result set is empty.

  According to the SQL standard the result of VAR_POP should be a

  floating point number. Hence, the result should not be truncated to

  a certain number of decimals.  This patch changes VAR_POP() and similar

  functions (VAR_SAMP(), VARIANCE(), STD(), STDDEV_POP(), STDDEV_SAMP(), and

  STDDEV())

  By changing this, results for these functions will never be truncated

  Hence, materialization should no longer give a different value from

  what is normally returned to users.