Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-5833

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

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Open (View Workflow)
    • Priority: Major
    • Resolution: Unresolved
    • Affects Version/s: 10.0.9
    • Fix Version/s: 10.0
    • Component/s: None
    • Labels:

      Description

      Noticed while analyzing results difference between 5.6 and 10.0. At least affects tests which include "include/common-tests.inc". Like compress, named_pipe, shm, ssl, ssl_compress:

      --- 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
       

      Fixed in 5.6 with the following revision:

      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.

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              psergey Sergei Petrunia
              Reporter:
              svoj Sergey Vojtovich
              Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

                Dates

                Created:
                Updated: