###################################################################################
###################################### 8.0.40 ###################################### 
###################################################################################

-- Build pulled from https://dev.mysql.com/get/Downloads/MySQL-8.0/mysql-boost-8.0.40.tar.gz


scl enable gcc-toolset-11 bash
MYSQL_VERSION="Bug116777"
TARGET=/export/home/tmp/ushastry/src/$MYSQL_VERSION
rm -rf /export/home/tmp/ushastry/src/$MYSQL_VERSION
rm -rf bld/
mkdir bld && cd bld
rm -rf CMakeCache.txt

/home/umshastr/work/binaries/utils/cmake-3.28.1/bin/cmake .. -DWITH_BOOST=../boost/ -DBUILD_CONFIG=mysql_release -DCMAKE_BUILD_TYPE=Release -DWITH_SSL=system -DWITH_UNIT_TESTS=0  -DCMAKE_INSTALL_PREFIX=$TARGET -G Ninja
ninja -n 16
ninja install

cd $TARGET

BugNumber=116777
rm -rf $BugNumber/
bin/mysqld --no-defaults --initialize-insecure --basedir=$PWD --datadir=$PWD/$BugNumber --log-error-verbosity=3
bin/mysqld_safe --no-defaults --mysqld-version='' --basedir=$PWD --datadir=$PWD/$BugNumber --core-file --socket=/tmp/mysql.sock  --port=3306 --log-error=$PWD/$BugNumber/log.err --mysqlx-port=33330 --mysqlx-socket=/tmp/mysql_x_ushastry.sock --log-error-verbosity=3  --secure-file-priv="" --local-infile=1  2>&1 &


[umshastr@support-cluster03:/export/home/tmp/ushastry/src/Bug116777]$ bin/mysql -uroot -S/tmp/mysql.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 8.0.40 Source distribution

Copyright (c) 2000, 2024, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create database tpcds;
Query OK, 1 row affected (0.00 sec)

mysql> \q
Bye
[umshastr@support-cluster03:/export/home/tmp/ushastry/src/Bug116777]$ time bin/mysql -uroot -S/tmp/mysql.sock tpcds < /export/home/tmp/ushastry/src/reporter_dumps/tpcds_dump.sql

real	8m5.837s
user	0m9.112s
sys	0m0.404s
[umshastr@support-cluster03:/export/home/tmp/ushastry/src/Bug116777]$ bin/mysql -uroot -S/tmp/mysql.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 8.0.40 Source distribution

Copyright (c) 2000, 2024, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use tpcds
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

mysql> explain format=tree with v1 as(  select i_category, i_brand,         s_store_name, s_company_name,         d_year, d_moy,         sum(ss_sales_price) sum_sales,         avg(sum(ss_sales_price)) over           (partition by i_category, i_brand,                      s_store_name, s_company_name, d_year)           avg_monthly_sales,         rank() over           (partition by i_category, i_brand,                      s_store_name, s_company_name            order by d_year, d_moy) rn  from item, store_sales, date_dim, store  where ss_item_sk = i_item_sk and        ss_sold_date_sk = d_date_sk and        ss_store_sk = s_store_sk and        (          d_year = 2000 or          ( d_year = 2000-1 and d_moy
=12) or          ( d_year = 2000+1 and d_moy =1)        )  group by i_category, i_brand,           s_store_name, s_company_name,           d_year, d_moy),  v2 as(  select v1.i_category, v1.i_brand         ,v1.d_year, v1.d_moy         ,v1.avg_monthly_sales         ,v1.sum_sales, v1_lag.sum_sales psum, v1_lead.sum_sales nsum  from v1, v1 v1_lag, v1 v1_lead  where v1.i_category = v1_lag.i_category and        v1.i_category = v1_lead.i_category and        v1.i_brand = v1_lag.i_brand and        v1.i_brand = v1_lead.i_brand and        v1.s_store_name = v1_lag.s_store_name and        v1.s_store_name = v1_lead.s_store_name and        v1.s_company_name = v1_lag.s_company_name and        v1.s_company_name = v1_lead.s_company_name and        v1.rn = v1_lag.rn + 1 and        v1.rn = v1_lead.rn - 1)   select  *  from v2  where  d_year = 2000 and             avg_monthly_sales > 0 and         case when avg_monthly_sales > 0 then abs(sum_sales - avg_monthly_sales) / avg_monthly_sales else null end > 0.1  order by sum_sales - avg_monthly_sales, nsum  limit 100\G
*************************** 1. row ***************************
EXPLAIN: -> Limit: 100 row(s)
    -> Sort: (v1.sum_sales - v1.avg_monthly_sales), nsum, limit input to 100 row(s) per chunk
        -> Stream results  (cost=410119 rows=987387)
            -> Nested loop inner join  (cost=410119 rows=987387)
                -> Nested loop inner join  (cost=64534 rows=95429)
                    -> Filter: ((v1.d_year = 2000) and (v1.avg_monthly_sales > 0.000000) and ((case when (v1.avg_monthly_sales > 0.000000) then (abs((v1.sum_sales - v1.avg_monthly_sales)) / v1.avg_monthly_sales) else NULL end) > 0.1) and (v1.i_category is not null) and (v1.i_brand is not null) and (v1.s_store_name is not null) and (v1.s_company_name is not null))  (cost=3.38..31133 rows=9223)
                        -> Table scan on v1  (cost=2.5..2.5 rows=0)
                            -> Materialize CTE v1 if needed  (cost=0..0 rows=0)
                                -> Window aggregate: rank() OVER (PARTITION BY item.i_category,item.i_brand,store.s_store_name,store.s_company_name ORDER BY date_dim.d_year,date_dim.d_moy )
                                    -> Sort: item.i_category, item.i_brand, store.s_store_name, store.s_company_name, date_dim.d_year, date_dim.d_moy
                                        -> Table scan on <temporary>  (cost=2.5..2.5 rows=0)
                                            -> Temporary table  (cost=0..0 rows=0)
                                                -> Window aggregate with buffering: avg(```sum(store_sales.ss_sales_price)```) OVER (PARTITION BY item.i_category,item.i_brand,store.s_store_name,store.s_company_name,date_dim.d_year )
                                                    -> Sort: item.i_category, item.i_brand, store.s_store_name, store.s_company_name, date_dim.d_year
                                                        -> Table scan on <temporary>
                                                            -> Aggregate using temporary table
                                                                -> Nested loop inner join  (cost=3.09e+6 rows=276720)
                                                                    -> Nested loop inner join  (cost=2.66e+6 rows=2.35e+6)
                                                                        -> Inner hash join (no condition)  (cost=21503 rows=208164)
                                                                            -> Table scan on item  (cost=202 rows=17347)
                                                                            -> Hash
                                                                                -> Table scan on store  (cost=2.2 rows=12)
                                                                        -> Filter: ((store_sales.ss_store_sk = store.s_store_sk) and (store_sales.ss_sold_date_sk is not null))  (cost=1.39 rows=11.3)
                                                                            -> Index lookup on store_sales using PRIMARY (ss_item_sk=item.i_item_sk)  (cost=1.39 rows=113)
                                                                    -> Filter: ((date_dim.d_year = 2000) or ((date_dim.d_moy = 12) and (date_dim.d_year = <cache>((2000 - 1)))) or ((date_dim.d_moy = 1) and (date_dim.d_year = <cache>((2000 + 1)))))  (cost=0.0832 rows=0.118)
                                                                        -> Single-row index lookup on date_dim using PRIMARY (d_date_sk=store_sales.ss_sold_date_sk)  (cost=0.0832 rows=1)
                    -> Filter: (v1.rn = (v1_lag.rn + 1))  (cost=0.25..2.59 rows=10.3)
                        -> Index lookup on v1_lag using <auto_key1> (i_category=v1.i_category, i_brand=v1.i_brand, s_store_name=v1.s_store_name, s_company_name=v1.s_company_name)  (cost=0.25..2.59 rows=10.3)
                            -> Materialize CTE v1 if needed (query plan printed elsewhere)  (cost=0..0 rows=0)
                -> Filter: (v1.rn = (v1_lead.rn - 1))  (cost=0.25..2.59 rows=10.3)
                    -> Index lookup on v1_lead using <auto_key1> (i_category=v1.i_category, i_brand=v1.i_brand, s_store_name=v1.s_store_name, s_company_name=v1.s_company_name)  (cost=0.25..2.59 rows=10.3)
                        -> Materialize CTE v1 if needed (query plan printed elsewhere)  (cost=0..0 rows=0)

1 row in set (0.00 sec)


mysql> with v1 as(
    ->  select i_category, i_brand,
    ->         s_store_name, s_company_name,
    ->         d_year, d_moy,
    ->         sum(ss_sales_price) sum_sales,
    ->         avg(sum(ss_sales_price)) over
    ->           (partition by i_category, i_brand,
    ->                      s_store_name, s_company_name, d_year)
    ->           avg_monthly_sales,
    ->         rank() over
    ->           (partition by i_category, i_brand,
    ->                      s_store_name, s_company_name
    ->            order by d_year, d_moy) rn
    ->  from item, store_sales, date_dim, store
    ->  where ss_item_sk = i_item_sk and
    ->        ss_sold_date_sk = d_date_sk and
    ->        ss_store_sk = s_store_sk and
    ->        (
    ->          d_year = 2000 or
    ->          ( d_year = 2000-1 and d_moy =12) or
    ->          ( d_year = 2000+1 and d_moy =1)
    ->        )
    ->  group by i_category, i_brand,
    ->           s_store_name, s_company_name,
    ->           d_year, d_moy),
    ->  v2 as(
    ->  select v1.i_category, v1.i_brand
    ->         ,v1.d_year, v1.d_moy
    ->         ,v1.avg_monthly_sales
    ->         ,v1.sum_sales, v1_lag.sum_sales psum, v1_lead.sum_sales nsum
    ->  from v1, v1 v1_lag, v1 v1_lead
    ->  where v1.i_category = v1_lag.i_category and
    ->        v1.i_category = v1_lead.i_category and
    ->        v1.i_brand = v1_lag.i_brand and
    ->        v1.i_brand = v1_lead.i_brand and
    ->        v1.s_store_name = v1_lag.s_store_name and
    ->        v1.s_store_name = v1_lead.s_store_name and
    ->        v1.s_company_name = v1_lag.s_company_name and
    ->        v1.s_company_name = v1_lead.s_company_name and
    ->        v1.rn = v1_lag.rn + 1 and
    ->        v1.rn = v1_lead.rn - 1)
    ->   select  *
    ->  from v2
    ->  where  d_year = 2000 and
    ->         avg_monthly_sales > 0 and
    ->         case when avg_monthly_sales > 0 then abs(sum_sales - avg_monthly_sales) / avg_monthly_sales else null end > 0.1
    ->  order by sum_sales - avg_monthly_sales, nsum
    ->  limit 100;
+------------+---------------------+--------+-------+-------------------+-----------+---------+---------+
| i_category | i_brand             | d_year | d_moy | avg_monthly_sales | sum_sales | psum    | nsum    |
+------------+---------------------+--------+-------+-------------------+-----------+---------+---------+
| Women      | edu packamalg #2    |   2000 |     6 |       5167.868333 |   1702.17 | 3236.87 | 3399.13 |
| Music      | exportischolar #2   |   2000 |     4 |       5036.090833 |   1860.99 | 2706.82 | 3303.43 |
| Children   | importoexporti #2   |   2000 |     3 |       5064.604167 |   1912.86 | 3298.07 | 2757.48 |
| Women      | importoamalg #2     |   2000 |     3 |       4913.032500 |   1779.75 | 2390.63 | 2257.04 |
| Shoes      | importoedu pack #2  |   2000 |     7 |       5168.767500 |   2069.14 | 2983.37 | 8035.14 |
| Music      | edu packscholar #2  |   2000 |     3 |       5110.744167 |   2022.81 | 2461.84 | 3067.59 |
| Music      | importoscholar #2   |   2000 |     5 |       4803.555833 |   1743.74 | 2778.83 | 3066.97 |
| Children   | importoexporti #2   |   2000 |     7 |       5175.091667 |   2116.90 | 3610.19 | 6870.27 |
| Men        | importoimporto #2   |   2000 |     4 |       4937.400833 |   1880.07 | 3078.36 | 2756.00 |
| Women      | edu packamalg #2    |   2000 |     3 |       4958.763333 |   1958.78 | 2678.23 | 3027.08 |
| Music      | edu packscholar #2  |   2000 |     3 |       5212.675833 |   2215.63 | 3321.39 | 3357.05 |
| Women      | edu packamalg #2    |   2000 |     6 |       4950.270000 |   1956.92 | 2980.12 | 2196.47 |
| Shoes      | edu packedu pack #2 |   2000 |     6 |       4926.845000 |   1942.09 | 2679.25 | 3503.44 |
| Women      | exportiamalg #2     |   2000 |     5 |       4606.794167 |   1639.46 | 2157.87 | 2583.06 |
| Women      | importoamalg #2     |   2000 |     1 |       4997.976667 |   2033.94 | 4136.88 | 3386.81 |
| Music      | exportischolar #2   |   2000 |     3 |       5212.355000 |   2256.79 | 2483.30 | 3203.46 |
| Shoes      | importoedu pack #2  |   2000 |     3 |       5326.516667 |   2380.52 | 3557.54 | 2381.65 |
| Shoes      | importoedu pack #2  |   2000 |     4 |       5326.516667 |   2381.65 | 2380.52 | 3185.08 |
| Music      | importoscholar #2   |   2000 |     6 |       5076.010833 |   2166.79 | 2636.43 | 2696.26 |
| Shoes      | edu packedu pack #2 |   2000 |     5 |       5213.916667 |   2308.61 | 3484.35 | 2631.62 |
| Shoes      | amalgedu pack #2    |   2000 |     7 |       4944.166667 |   2043.50 | 2714.17 | 7158.49 |
| Children   | importoexporti #2   |   2000 |     6 |       5109.577500 |   2231.87 | 2492.85 | 2716.69 |
| Music      | exportischolar #2   |   2000 |     7 |       5036.090833 |   2162.98 | 2169.49 | 7442.03 |
| Music      | exportischolar #2   |   2000 |     6 |       5036.090833 |   2169.49 | 3303.43 | 2162.98 |
| Music      | importoscholar #2   |   2000 |     3 |       4953.792500 |   2087.58 | 2881.98 | 2396.23 |
| Shoes      | edu packedu pack #2 |   2000 |     3 |       4824.920833 |   1981.97 | 2557.91 | 2178.72 |
| Music      | edu packscholar #2  |   2000 |     4 |       5270.568333 |   2429.81 | 2500.28 | 3121.27 |
| Women      | amalgamalg #2       |   2000 |     4 |       4390.827500 |   1562.28 | 2203.54 | 2814.98 |
| Shoes      | exportiedu pack #2  |   2000 |     2 |       4565.836667 |   1740.10 | 3299.55 | 2905.54 |
| Women      | edu packamalg #2    |   2000 |     4 |       4950.270000 |   2125.36 | 3688.19 | 2980.12 |
| Men        | importoimporto #2   |   2000 |     7 |       4657.830000 |   1836.07 | 2432.42 | 6304.84 |
| Shoes      | edu packedu pack #2 |   2000 |     6 |       5094.271667 |   2285.80 | 2982.59 | 2944.67 |
| Shoes      | importoedu pack #2  |   2000 |     5 |       5233.675000 |   2458.45 | 3530.35 | 4587.71 |
| Music      | edu packscholar #2  |   2000 |     3 |       5270.568333 |   2500.28 | 3185.98 | 2429.81 |
| Music      | edu packscholar #2  |   2000 |     3 |       5257.115000 |   2491.28 | 2494.71 | 2963.86 |
| Music      | edu packscholar #2  |   2000 |     2 |       5257.115000 |   2494.71 | 2788.64 | 2491.28 |
| Shoes      | importoedu pack #2  |   2000 |     4 |       5168.767500 |   2407.05 | 3240.95 | 2483.46 |
| Women      | exportiamalg #2     |   2000 |     3 |       4422.386667 |   1666.68 | 2570.10 | 2097.53 |
| Women      | edu packamalg #2    |   2000 |     7 |       4950.270000 |   2196.47 | 1956.92 | 7068.51 |
| Men        | importoimporto #2   |   2000 |     5 |       5016.401667 |   2267.73 | 2984.52 | 3051.68 |
| Music      | importoscholar #2   |   2000 |     4 |       5076.010833 |   2328.83 | 3139.50 | 2636.43 |
| Men        | importoimporto #2   |   2000 |     4 |       4657.830000 |   1925.05 | 2409.04 | 2999.06 |
| Music      | exportischolar #2   |   2000 |     2 |       4931.629167 |   2199.12 | 3942.13 | 2335.05 |
| Music      | exportischolar #2   |   2000 |     2 |       5212.355000 |   2483.30 | 4582.12 | 2256.79 |
| Men        | importoimporto #2   |   2000 |     3 |       4872.140833 |   2143.87 | 3353.30 | 2688.70 |
| Music      | exportischolar #2   |   2000 |     7 |       5212.355000 |   2487.88 | 2624.81 | 6788.15 |
| Shoes      | edu packedu pack #2 |   2000 |     7 |       4912.345000 |   2194.71 | 3320.16 | 7019.04 |
| Women      | exportiamalg #2     |   2000 |     4 |       4521.217500 |   1803.95 | 2858.45 | 2451.32 |
| Music      | edu packscholar #2  |   2000 |     7 |       5257.115000 |   2540.19 | 3558.30 | 7261.59 |
| Women      | edu packamalg #2    |   2000 |     2 |       5183.971667 |   2471.71 | 3493.02 | 2920.36 |
| Shoes      | exportiedu pack #2  |   2000 |     3 |       4440.288333 |   1737.33 | 1857.98 | 2951.57 |
| Women      | edu packamalg #2    |   2000 |     4 |       5167.868333 |   2468.50 | 3375.26 | 3236.87 |
| Shoes      | amalgedu pack #2    |   2000 |     2 |       4732.806667 |   2035.56 | 3540.84 | 2823.10 |
| Music      | importoscholar #2   |   2000 |     2 |       4960.848333 |   2267.45 | 3486.05 | 3329.26 |
| Shoes      | importoedu pack #2  |   2000 |     2 |       5240.653333 |   2549.66 | 3081.94 | 2798.03 |
| Shoes      | importoedu pack #2  |   2000 |     5 |       5168.767500 |   2483.46 | 2407.05 | 2983.37 |
| Women      | importoamalg #2     |   2000 |     5 |       4888.695833 |   2204.11 | 3149.43 | 2688.11 |
| Music      | edu packscholar #2  |   2000 |     7 |       5270.568333 |   2587.41 | 3050.17 | 6970.69 |
| Children   | exportiexporti #2   |   2000 |     7 |       4372.639167 |   1694.02 | 2257.72 | 6000.24 |
| Music      | importoscholar #2   |   2000 |     4 |       4916.410000 |   2240.65 | 3382.64 | 3182.24 |
| Men        | exportiimporto #2   |   2000 |     6 |       4364.737500 |   1689.53 | 2992.20 | 3120.76 |
| Shoes      | importoedu pack #2  |   2000 |     4 |       5090.508333 |   2415.99 | 3256.81 | 2925.27 |
| Music      | importoscholar #2   |   2000 |     4 |       4813.664167 |   2143.57 | 2637.02 | 2902.70 |
| Men        | importoimporto #2   |   2000 |     5 |       4595.062500 |   1926.36 | 2586.96 | 2922.71 |
| Women      | importoamalg #2     |   2000 |     4 |       4913.032500 |   2257.04 | 1779.75 | 2643.30 |
| Men        | edu packimporto #2  |   2000 |     4 |       4371.782500 |   1718.21 | 2208.02 | 2721.25 |
| Music      | edu packscholar #2  |   2000 |     2 |       5110.744167 |   2461.84 | 4401.88 | 2022.81 |
| Women      | importoamalg #2     |   2000 |     3 |       5192.789167 |   2544.73 | 3587.53 | 3197.85 |
| Shoes      | edu packedu pack #2 |   2000 |     4 |       4824.920833 |   2178.72 | 1981.97 | 2884.49 |
| Shoes      | importoedu pack #2  |   2000 |     3 |       5233.675000 |   2588.01 | 3532.78 | 3530.35 |
| Children   | edu packexporti #2  |   2000 |     5 |       4483.991667 |   1838.66 | 2619.28 | 2374.55 |
| Music      | exportischolar #2   |   2000 |     7 |       5043.647500 |   2403.81 | 3238.49 | 6835.65 |
| Women      | exportiamalg #2     |   2000 |     7 |       4407.061667 |   1776.42 | 2981.25 | 5710.21 |
| Men        | importoimporto #2   |   2000 |     6 |       4830.246667 |   2199.80 | 2412.75 | 3006.00 |
| Shoes      | importoedu pack #2  |   2000 |     6 |       5326.516667 |   2701.75 | 3185.08 | 3285.18 |
| Music      | importoscholar #2   |   2000 |     2 |       4916.410000 |   2298.33 | 3493.76 | 3382.64 |
| Women      | edu packamalg #2    |   2000 |     5 |       4727.370000 |   2110.50 | 2519.62 | 2267.53 |
| Children   | importoexporti #2   |   2000 |     5 |       5109.577500 |   2492.85 | 2728.55 | 2231.87 |
| Music      | exportischolar #2   |   2000 |     4 |       5043.647500 |   2430.73 | 2644.32 | 3481.08 |
| Music      | amalgscholar #2     |   2000 |     6 |       4131.199167 |   1519.23 | 2157.93 | 2655.31 |
| Music      | edu packscholar #2  |   2000 |     2 |       5172.156667 |   2563.98 | 2905.94 | 3777.26 |
| Music      | exportischolar #2   |   2000 |     4 |       4931.629167 |   2327.08 | 2335.05 | 2882.04 |
| Music      | importoscholar #2   |   2000 |     5 |       4960.848333 |   2360.20 | 2406.42 | 3555.36 |
| Shoes      | exportiedu pack #2  |   2000 |     4 |       4823.300000 |   2222.80 | 3016.25 | 2462.91 |
| Music      | exportischolar #2   |   2000 |     3 |       4931.629167 |   2335.05 | 2199.12 | 2327.08 |
| Music      | exportischolar #2   |   2000 |     6 |       5212.355000 |   2624.81 | 2899.34 | 2487.88 |
| Men        | importoimporto #2   |   2000 |     3 |       5016.401667 |   2432.36 | 3088.82 | 2984.52 |
| Shoes      | exportiedu pack #2  |   2000 |     2 |       4440.288333 |   1857.98 | 3346.62 | 1737.33 |
| Shoes      | edu packedu pack #2 |   2000 |     6 |       5213.916667 |   2631.62 | 2308.61 | 3081.21 |
| Music      | exportischolar #2   |   2000 |     6 |       5000.816667 |   2419.42 | 3117.07 | 3086.00 |
| Children   | edu packexporti #2  |   2000 |     3 |       4551.438333 |   1973.72 | 2083.41 | 2621.49 |
| Music      | edu packscholar #2  |   2000 |     7 |       5212.675833 |   2638.50 | 3720.45 | 7180.32 |
| Women      | importoamalg #2     |   2000 |     2 |       5092.845000 |   2525.38 | 3066.60 | 2819.83 |
| Shoes      | edu packedu pack #2 |   2000 |     2 |       5213.916667 |   2649.38 | 3206.27 | 3351.30 |
| Men        | importoimporto #2   |   2000 |     1 |       4595.062500 |   2032.46 | 3557.44 | 2372.72 |
| Shoes      | importoedu pack #2  |   2000 |     7 |       5032.343333 |   2469.75 | 2961.97 | 6219.49 |
| Women      | edu packamalg #2    |   2000 |     6 |       4776.425000 |   2216.70 | 2861.21 | 3620.51 |
| Women      | edu packamalg #2    |   2000 |     7 |       4727.370000 |   2168.76 | 2267.53 | 7819.76 |
| Music      | importoscholar #2   |   2000 |     4 |       4953.792500 |   2396.23 | 2087.58 | 2921.95 |
| Children   | importoexporti #2   |   2000 |     5 |       5206.238333 |   2649.03 | 2879.82 | 3447.77 |
+------------+---------------------+--------+-------+-------------------+-----------+---------+---------+
100 rows in set (25.68 sec)

mysql>
.

+------------+---------------------+--------+-------+-------------------+-----------+---------+---------+
100 rows in set (25.57 sec)

.
+------------+---------------------+--------+-------+-------------------+-----------+---------+---------+
100 rows in set (25.59 sec)

+------------+---------------------+--------+-------+-------------------+-----------+---------+---------+
100 rows in set (25.51 sec)



############################################################################
###################################### apply patch on top of 8.0.40
############################################################################

scl enable gcc-toolset-11 bash
MYSQL_VERSION="Bug116777_patched"
TARGET=/export/home/tmp/ushastry/src/$MYSQL_VERSION
rm -rf /export/home/tmp/ushastry/src/$MYSQL_VERSION
rm -rf bld/
mkdir bld && cd bld
rm -rf CMakeCache.txt

/home/umshastr/work/binaries/utils/cmake-3.28.1/bin/cmake .. -DWITH_BOOST=../boost/ -DBUILD_CONFIG=mysql_release -DCMAKE_BUILD_TYPE=Release -DWITH_SSL=system -DWITH_UNIT_TESTS=0  -DCMAKE_INSTALL_PREFIX=$TARGET -G Ninja
ninja -j8
ninja install


cd $TARGET

BugNumber=116777
rm -rf $BugNumber/
bin/mysqld --no-defaults --initialize-insecure --basedir=$PWD --datadir=$PWD/$BugNumber --log-error-verbosity=3
bin/mysqld_safe --no-defaults --mysqld-version='' --basedir=$PWD --datadir=$PWD/$BugNumber --core-file --socket=/tmp/mysql.sock  --port=3306 --log-error=$PWD/$BugNumber/log.err --mysqlx-port=33330 --mysqlx-socket=/tmp/mysql_x_ushastry.sock --log-error-verbosity=3  --secure-file-priv="" --local-infile=1  2>&1 &


umshastr@support-cluster03:/export/home/tmp/ushastry/src/Bug116777_patched]$ bin/mysql -uroot -S/tmp/mysql.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 8.0.40 Source distribution

Copyright (c) 2000, 2024, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create database tpcds;
Query OK, 1 row affected (0.00 sec)

mysql> \q
Bye
[umshastr@support-cluster03:/export/home/tmp/ushastry/src/Bug116777_patched]$ time bin/mysql -uroot -S/tmp/mysql.sock tpcds < /export/home/tmp/ushastry/src/reporter_dumps/tpcds_dump.sql

real	8m2.984s
user	0m9.154s
sys	0m0.437s
[umshastr@support-cluster03:/export/home/tmp/ushastry/src/Bug116777_patched]$ bin/mysql -uroot -S/tmp/mysql.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 8.0.40 Source distribution

Copyright (c) 2000, 2024, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use tpcds
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql>

mysql> explain format=TREE with v1 as(
    ->  select i_category, i_brand,
    ->         s_store_name, s_company_name,
    ->         d_year, d_moy,
    ->         sum(ss_sales_price) sum_sales,
    ->         avg(sum(ss_sales_price)) over
    ->           (partition by i_category, i_brand,
    ->                      s_store_name, s_company_name, d_year)
    ->           avg_monthly_sales,
    ->         rank() over
    ->           (partition by i_category, i_brand,
    ->                      s_store_name, s_company_name
    ->            order by d_year, d_moy) rn
    ->  from item, store_sales, date_dim, store
    ->  where ss_item_sk = i_item_sk and
    ->        ss_sold_date_sk = d_date_sk and
    ->        ss_store_sk = s_store_sk and
    ->        (
    ->          d_year = 2000 or
    ->          ( d_year = 2000-1 and d_moy =12) or
    ->          ( d_year = 2000+1 and d_moy =1)
 v1_lag, v1 v1_lead
 where v1.i_category = v1_lag.i_category and
       v1.i_category = v1_lead.i_category and
       v1.i_brand = v1_lag.i_brand and
       v1.i_brand = v1_lead.i_brand and
       v1.s_store_name = v1_lag.s_store_name and
       v1.s_store_name = v1_lead.s_store_name and
       v1.s_company_name = v1_lag.s_company_name and
       v1.s_company_name = v1_lead.s_company_name and
       v1.rn = v1_lag.rn + 1 and
       v1.rn = v1_lead.rn - 1)
  select  *
 from v2
 where  d_year = 2000 and
        avg_monthly_sales > 0 and
        case when avg_monthly_sales > 0 then abs(sum_sales - avg_monthly_sales) / avg_monthly_sales else null end > 0.1
 order by sum_sales - avg_monthly_sales, nsum
 limit 100    ->        )
    ->  group by i_category, i_brand,
    ->           s_store_name, s_company_name,
    ->           d_year, d_moy),
    ->  v2 as(
    ->  select v1.i_category, v1.i_brand
    ->         ,v1.d_year, v1.d_moy
    ->         ,v1.avg_monthly_sales
    ->         ,v1.sum_sales, v1_lag.sum_sales psum, v1_lead.sum_sales nsum
    ->  from v1, v1 v1_lag, v1 v1_lead
    ->  where v1.i_category = v1_lag.i_category and
    ->        v1.i_category = v1_lead.i_category and
    ->        v1.i_brand = v1_lag.i_brand and
    ->        v1.i_brand = v1_lead.i_brand and
    ->        v1.s_store_name = v1_lag.s_store_name and
    ->        v1.s_store_name = v1_lead.s_store_name and
    ->        v1.s_company_name = v1_lag.s_company_name and
    ->        v1.s_company_name = v1_lead.s_company_name and
    ->        v1.rn = v1_lag.rn + 1 and
    ->        v1.rn = v1_lead.rn - 1)
    ->   select  *
    ->  from v2
    ->  where  d_year = 2000 and
    ->         avg_monthly_sales > 0 and
    ->         case when avg_monthly_sales > 0 then abs(sum_sales - avg_monthly_sales) / avg_monthly_sales else null end > 0.1
    ->  order by sum_sales - avg_monthly_sales, nsum
    ->  limit 100\G
*************************** 1. row ***************************
EXPLAIN: -> Limit: 100 row(s)
    -> Sort: (v1.sum_sales - v1.avg_monthly_sales), nsum, limit input to 100 row(s) per chunk
        -> Stream results  (cost=254624 rows=1.41e+6)
            -> Nested loop inner join  (cost=254624 rows=1.41e+6)
                -> Nested loop inner join  (cost=62901 rows=136006)
                    -> Filter: ((v1.d_year = 2000) and (v1.avg_monthly_sales > 0.000000) and ((case when (v1.avg_monthly_sales > 0.000000) then (abs((v1.sum_sales - v1.avg_monthly_sales)) / v1.avg_monthly_sales) else NULL end) > 0.1) and (v1.i_category is not null) and (v1.i_brand is not null) and (v1.s_store_name is not null) and (v1.s_company_name is not null))  (cost=3.38..44371 rows=13145)
                        -> Table scan on v1  (cost=2.5..2.5 rows=0)
                            -> Materialize CTE v1 if needed  (cost=0..0 rows=0)
                                -> Window aggregate: rank() OVER (PARTITION BY item.i_category,item.i_brand,store.s_store_name,store.s_company_name ORDER BY date_dim.d_year,date_dim.d_moy )
                                    -> Sort: item.i_category, item.i_brand, store.s_store_name, store.s_company_name, date_dim.d_year, date_dim.d_moy
                                        -> Table scan on <temporary>  (cost=2.5..2.5 rows=0)
                                            -> Temporary table  (cost=0..0 rows=0)
                                                -> Window aggregate with buffering: avg(```sum(store_sales.ss_sales_price)```) OVER (PARTITION BY item.i_category,item.i_brand,store.s_store_name,store.s_company_name,date_dim.d_year )
                                                    -> Sort: item.i_category, item.i_brand, store.s_store_name, store.s_company_name, date_dim.d_year
                                                        -> Table scan on <temporary>
                                                            -> Aggregate using temporary table
                                                                -> Nested loop inner join  (cost=980171 rows=39439)
                                                                    -> Nested loop inner join  (cost=668300 rows=334481)
                                                                        -> Inner hash join (store_sales.ss_store_sk = store.s_store_sk)  (cost=356453 rows=334481)
                                                                            -> Filter: (store_sales.ss_sold_date_sk is not null)  (cost=4154 rows=278735)
                                                                                -> Table scan on store_sales  (cost=4154 rows=2.79e+6)
                                                                            -> Hash
                                                                                -> Table scan on store  (cost=2.2 rows=12)
                                                                        -> Single-row index lookup on item using PRIMARY (i_item_sk=store_sales.ss_item_sk)  (cost=0.0832 rows=1)
                                                                    -> Filter: ((date_dim.d_year = 2000) or ((date_dim.d_moy = 12) and (date_dim.d_year = <cache>((2000 - 1)))) or ((date_dim.d_moy = 1) and (date_dim.d_year = <cache>((2000 + 1)))))  (cost=0.0832 rows=0.118)
                                                                        -> Single-row index lookup on date_dim using PRIMARY (d_date_sk=store_sales.ss_sold_date_sk)  (cost=0.0832 rows=1)
                    -> Filter: (v1.rn = (v1_lag.rn + 1))  (cost=0.0363..0.375 rows=10.3)
                        -> Index lookup on v1_lag using <auto_key1> (i_category=v1.i_category, i_brand=v1.i_brand, s_store_name=v1.s_store_name, s_company_name=v1.s_company_name)  (cost=0.0363..0.375 rows=10.3)
                            -> Materialize CTE v1 if needed (query plan printed elsewhere)  (cost=0..0 rows=0)
                -> Filter: (v1.rn = (v1_lead.rn - 1))  (cost=0.0362..0.375 rows=10.3)
                    -> Index lookup on v1_lead using <auto_key1> (i_category=v1.i_category, i_brand=v1.i_brand, s_store_name=v1.s_store_name, s_company_name=v1.s_company_name)  (cost=0.0362..0.375 rows=10.3)
                        -> Materialize CTE v1 if needed (query plan printed elsewhere)  (cost=0..0 rows=0)

1 row in set (0.01 sec)

mysql> with v1 as(
    ->  select i_category, i_brand,
    ->         s_store_name, s_company_name,
    ->         d_year, d_moy,
    ->         sum(ss_sales_price) sum_sales,
    ->         avg(sum(ss_sales_price)) over
    ->           (partition by i_category, i_brand,
    ->                      s_store_name, s_company_name, d_year)
    ->           avg_monthly_sales,
    ->         rank() over
    ->           (partition by i_category, i_brand,
    ->                      s_store_name, s_company_name
    ->            order by d_year, d_moy) rn
    ->  from item, store_sales, date_dim, store
    ->  where ss_item_sk = i_item_sk and
    ->        ss_sold_date_sk = d_date_sk and
    ->        ss_store_sk = s_store_sk and
    ->        (
    ->          d_year = 2000 or
    ->          ( d_year = 2000-1 and d_moy =12) or
    ->          ( d_year = 2000+1 and d_moy =1)
    ->        )
    ->  group by i_category, i_brand,
    ->           s_store_name, s_company_name,
    ->           d_year, d_moy),
 v1_lag, v1 v1_lead
 where v1.i_category = v1_lag.i_category and
       v1.i_category = v1_lead.i_category and
       v1.i_brand = v1_lag.i_brand and
       v1.i_brand = v1_lead.i_brand and
       v1.s_store_name = v1_lag.s_store_name and
       v1.s_store_name = v1_lead.s_store_name and
       v1.s_company_name = v1_lag.s_company_name and
       v1.s_company_name = v1_lead.s_company_name and
       v1.rn = v1_lag.rn + 1 and
       v1.rn = v1_lead.rn - 1)
  select  *
 from v2
 where  d_year = 2000 and
        avg_monthly_sales > 0 and
        case when avg_monthly_sales > 0 then abs(sum_sales - avg_monthly_sales) / avg_monthly_sales else null end > 0.1
 order by sum_sales - avg_monthly_sales, nsum
 limit 100;    ->  v2 as(
    ->  select v1.i_category, v1.i_brand
    ->         ,v1.d_year, v1.d_moy
    ->         ,v1.avg_monthly_sales
    ->         ,v1.sum_sales, v1_lag.sum_sales psum, v1_lead.sum_sales nsum
    ->  from v1, v1 v1_lag, v1 v1_lead
    ->  where v1.i_category = v1_lag.i_category and
    ->        v1.i_category = v1_lead.i_category and
    ->        v1.i_brand = v1_lag.i_brand and
    ->        v1.i_brand = v1_lead.i_brand and
    ->        v1.s_store_name = v1_lag.s_store_name and
    ->        v1.s_store_name = v1_lead.s_store_name and
    ->        v1.s_company_name = v1_lag.s_company_name and
    ->        v1.s_company_name = v1_lead.s_company_name and
    ->        v1.rn = v1_lag.rn + 1 and
    ->        v1.rn = v1_lead.rn - 1)
    ->   select  *
    ->  from v2
    ->  where  d_year = 2000 and
    ->         avg_monthly_sales > 0 and
    ->         case when avg_monthly_sales > 0 then abs(sum_sales - avg_monthly_sales) / avg_monthly_sales else null end > 0.1
    ->  order by sum_sales - avg_monthly_sales, nsum
    ->  limit 100;
+------------+---------------------+--------+-------+-------------------+-----------+---------+---------+
| i_category | i_brand             | d_year | d_moy | avg_monthly_sales | sum_sales | psum    | nsum    |
+------------+---------------------+--------+-------+-------------------+-----------+---------+---------+
| Women      | edu packamalg #2    |   2000 |     6 |       5167.868333 |   1702.17 | 3236.87 | 3399.13 |
| Music      | exportischolar #2   |   2000 |     4 |       5036.090833 |   1860.99 | 2706.82 | 3303.43 |
| Children   | importoexporti #2   |   2000 |     3 |       5064.604167 |   1912.86 | 3298.07 | 2757.48 |
| Women      | importoamalg #2     |   2000 |     3 |       4913.032500 |   1779.75 | 2390.63 | 2257.04 |
| Shoes      | importoedu pack #2  |   2000 |     7 |       5168.767500 |   2069.14 | 2983.37 | 8035.14 |
| Music      | edu packscholar #2  |   2000 |     3 |       5110.744167 |   2022.81 | 2461.84 | 3067.59 |
| Music      | importoscholar #2   |   2000 |     5 |       4803.555833 |   1743.74 | 2778.83 | 3066.97 |
| Children   | importoexporti #2   |   2000 |     7 |       5175.091667 |   2116.90 | 3610.19 | 6870.27 |
| Men        | importoimporto #2   |   2000 |     4 |       4937.400833 |   1880.07 | 3078.36 | 2756.00 |
| Women      | edu packamalg #2    |   2000 |     3 |       4958.763333 |   1958.78 | 2678.23 | 3027.08 |
| Music      | edu packscholar #2  |   2000 |     3 |       5212.675833 |   2215.63 | 3321.39 | 3357.05 |
| Women      | edu packamalg #2    |   2000 |     6 |       4950.270000 |   1956.92 | 2980.12 | 2196.47 |
| Shoes      | edu packedu pack #2 |   2000 |     6 |       4926.845000 |   1942.09 | 2679.25 | 3503.44 |
| Women      | exportiamalg #2     |   2000 |     5 |       4606.794167 |   1639.46 | 2157.87 | 2583.06 |
| Women      | importoamalg #2     |   2000 |     1 |       4997.976667 |   2033.94 | 4136.88 | 3386.81 |
| Music      | exportischolar #2   |   2000 |     3 |       5212.355000 |   2256.79 | 2483.30 | 3203.46 |
| Shoes      | importoedu pack #2  |   2000 |     3 |       5326.516667 |   2380.52 | 3557.54 | 2381.65 |
| Shoes      | importoedu pack #2  |   2000 |     4 |       5326.516667 |   2381.65 | 2380.52 | 3185.08 |
| Music      | importoscholar #2   |   2000 |     6 |       5076.010833 |   2166.79 | 2636.43 | 2696.26 |
| Shoes      | edu packedu pack #2 |   2000 |     5 |       5213.916667 |   2308.61 | 3484.35 | 2631.62 |
| Shoes      | amalgedu pack #2    |   2000 |     7 |       4944.166667 |   2043.50 | 2714.17 | 7158.49 |
| Children   | importoexporti #2   |   2000 |     6 |       5109.577500 |   2231.87 | 2492.85 | 2716.69 |
| Music      | exportischolar #2   |   2000 |     7 |       5036.090833 |   2162.98 | 2169.49 | 7442.03 |
| Music      | exportischolar #2   |   2000 |     6 |       5036.090833 |   2169.49 | 3303.43 | 2162.98 |
| Music      | importoscholar #2   |   2000 |     3 |       4953.792500 |   2087.58 | 2881.98 | 2396.23 |
| Shoes      | edu packedu pack #2 |   2000 |     3 |       4824.920833 |   1981.97 | 2557.91 | 2178.72 |
| Music      | edu packscholar #2  |   2000 |     4 |       5270.568333 |   2429.81 | 2500.28 | 3121.27 |
| Women      | amalgamalg #2       |   2000 |     4 |       4390.827500 |   1562.28 | 2203.54 | 2814.98 |
| Shoes      | exportiedu pack #2  |   2000 |     2 |       4565.836667 |   1740.10 | 3299.55 | 2905.54 |
| Women      | edu packamalg #2    |   2000 |     4 |       4950.270000 |   2125.36 | 3688.19 | 2980.12 |
| Men        | importoimporto #2   |   2000 |     7 |       4657.830000 |   1836.07 | 2432.42 | 6304.84 |
| Shoes      | edu packedu pack #2 |   2000 |     6 |       5094.271667 |   2285.80 | 2982.59 | 2944.67 |
| Shoes      | importoedu pack #2  |   2000 |     5 |       5233.675000 |   2458.45 | 3530.35 | 4587.71 |
| Music      | edu packscholar #2  |   2000 |     3 |       5270.568333 |   2500.28 | 3185.98 | 2429.81 |
| Music      | edu packscholar #2  |   2000 |     3 |       5257.115000 |   2491.28 | 2494.71 | 2963.86 |
| Music      | edu packscholar #2  |   2000 |     2 |       5257.115000 |   2494.71 | 2788.64 | 2491.28 |
| Shoes      | importoedu pack #2  |   2000 |     4 |       5168.767500 |   2407.05 | 3240.95 | 2483.46 |
| Women      | exportiamalg #2     |   2000 |     3 |       4422.386667 |   1666.68 | 2570.10 | 2097.53 |
| Women      | edu packamalg #2    |   2000 |     7 |       4950.270000 |   2196.47 | 1956.92 | 7068.51 |
| Men        | importoimporto #2   |   2000 |     5 |       5016.401667 |   2267.73 | 2984.52 | 3051.68 |
| Music      | importoscholar #2   |   2000 |     4 |       5076.010833 |   2328.83 | 3139.50 | 2636.43 |
| Men        | importoimporto #2   |   2000 |     4 |       4657.830000 |   1925.05 | 2409.04 | 2999.06 |
| Music      | exportischolar #2   |   2000 |     2 |       4931.629167 |   2199.12 | 3942.13 | 2335.05 |
| Music      | exportischolar #2   |   2000 |     2 |       5212.355000 |   2483.30 | 4582.12 | 2256.79 |
| Men        | importoimporto #2   |   2000 |     3 |       4872.140833 |   2143.87 | 3353.30 | 2688.70 |
| Music      | exportischolar #2   |   2000 |     7 |       5212.355000 |   2487.88 | 2624.81 | 6788.15 |
| Shoes      | edu packedu pack #2 |   2000 |     7 |       4912.345000 |   2194.71 | 3320.16 | 7019.04 |
| Women      | exportiamalg #2     |   2000 |     4 |       4521.217500 |   1803.95 | 2858.45 | 2451.32 |
| Music      | edu packscholar #2  |   2000 |     7 |       5257.115000 |   2540.19 | 3558.30 | 7261.59 |
| Women      | edu packamalg #2    |   2000 |     2 |       5183.971667 |   2471.71 | 3493.02 | 2920.36 |
| Shoes      | exportiedu pack #2  |   2000 |     3 |       4440.288333 |   1737.33 | 1857.98 | 2951.57 |
| Women      | edu packamalg #2    |   2000 |     4 |       5167.868333 |   2468.50 | 3375.26 | 3236.87 |
| Shoes      | amalgedu pack #2    |   2000 |     2 |       4732.806667 |   2035.56 | 3540.84 | 2823.10 |
| Music      | importoscholar #2   |   2000 |     2 |       4960.848333 |   2267.45 | 3486.05 | 3329.26 |
| Shoes      | importoedu pack #2  |   2000 |     2 |       5240.653333 |   2549.66 | 3081.94 | 2798.03 |
| Shoes      | importoedu pack #2  |   2000 |     5 |       5168.767500 |   2483.46 | 2407.05 | 2983.37 |
| Women      | importoamalg #2     |   2000 |     5 |       4888.695833 |   2204.11 | 3149.43 | 2688.11 |
| Music      | edu packscholar #2  |   2000 |     7 |       5270.568333 |   2587.41 | 3050.17 | 6970.69 |
| Children   | exportiexporti #2   |   2000 |     7 |       4372.639167 |   1694.02 | 2257.72 | 6000.24 |
| Music      | importoscholar #2   |   2000 |     4 |       4916.410000 |   2240.65 | 3382.64 | 3182.24 |
| Men        | exportiimporto #2   |   2000 |     6 |       4364.737500 |   1689.53 | 2992.20 | 3120.76 |
| Shoes      | importoedu pack #2  |   2000 |     4 |       5090.508333 |   2415.99 | 3256.81 | 2925.27 |
| Music      | importoscholar #2   |   2000 |     4 |       4813.664167 |   2143.57 | 2637.02 | 2902.70 |
| Men        | importoimporto #2   |   2000 |     5 |       4595.062500 |   1926.36 | 2586.96 | 2922.71 |
| Women      | importoamalg #2     |   2000 |     4 |       4913.032500 |   2257.04 | 1779.75 | 2643.30 |
| Men        | edu packimporto #2  |   2000 |     4 |       4371.782500 |   1718.21 | 2208.02 | 2721.25 |
| Music      | edu packscholar #2  |   2000 |     2 |       5110.744167 |   2461.84 | 4401.88 | 2022.81 |
| Women      | importoamalg #2     |   2000 |     3 |       5192.789167 |   2544.73 | 3587.53 | 3197.85 |
| Shoes      | edu packedu pack #2 |   2000 |     4 |       4824.920833 |   2178.72 | 1981.97 | 2884.49 |
| Shoes      | importoedu pack #2  |   2000 |     3 |       5233.675000 |   2588.01 | 3532.78 | 3530.35 |
| Children   | edu packexporti #2  |   2000 |     5 |       4483.991667 |   1838.66 | 2619.28 | 2374.55 |
| Music      | exportischolar #2   |   2000 |     7 |       5043.647500 |   2403.81 | 3238.49 | 6835.65 |
| Women      | exportiamalg #2     |   2000 |     7 |       4407.061667 |   1776.42 | 2981.25 | 5710.21 |
| Men        | importoimporto #2   |   2000 |     6 |       4830.246667 |   2199.80 | 2412.75 | 3006.00 |
| Shoes      | importoedu pack #2  |   2000 |     6 |       5326.516667 |   2701.75 | 3185.08 | 3285.18 |
| Music      | importoscholar #2   |   2000 |     2 |       4916.410000 |   2298.33 | 3493.76 | 3382.64 |
| Women      | edu packamalg #2    |   2000 |     5 |       4727.370000 |   2110.50 | 2519.62 | 2267.53 |
| Children   | importoexporti #2   |   2000 |     5 |       5109.577500 |   2492.85 | 2728.55 | 2231.87 |
| Music      | exportischolar #2   |   2000 |     4 |       5043.647500 |   2430.73 | 2644.32 | 3481.08 |
| Music      | amalgscholar #2     |   2000 |     6 |       4131.199167 |   1519.23 | 2157.93 | 2655.31 |
| Music      | edu packscholar #2  |   2000 |     2 |       5172.156667 |   2563.98 | 2905.94 | 3777.26 |
| Music      | exportischolar #2   |   2000 |     4 |       4931.629167 |   2327.08 | 2335.05 | 2882.04 |
| Music      | importoscholar #2   |   2000 |     5 |       4960.848333 |   2360.20 | 2406.42 | 3555.36 |
| Shoes      | exportiedu pack #2  |   2000 |     4 |       4823.300000 |   2222.80 | 3016.25 | 2462.91 |
| Music      | exportischolar #2   |   2000 |     3 |       4931.629167 |   2335.05 | 2199.12 | 2327.08 |
| Music      | exportischolar #2   |   2000 |     6 |       5212.355000 |   2624.81 | 2899.34 | 2487.88 |
| Men        | importoimporto #2   |   2000 |     3 |       5016.401667 |   2432.36 | 3088.82 | 2984.52 |
| Shoes      | exportiedu pack #2  |   2000 |     2 |       4440.288333 |   1857.98 | 3346.62 | 1737.33 |
| Shoes      | edu packedu pack #2 |   2000 |     6 |       5213.916667 |   2631.62 | 2308.61 | 3081.21 |
| Music      | exportischolar #2   |   2000 |     6 |       5000.816667 |   2419.42 | 3117.07 | 3086.00 |
| Children   | edu packexporti #2  |   2000 |     3 |       4551.438333 |   1973.72 | 2083.41 | 2621.49 |
| Music      | edu packscholar #2  |   2000 |     7 |       5212.675833 |   2638.50 | 3720.45 | 7180.32 |
| Women      | importoamalg #2     |   2000 |     2 |       5092.845000 |   2525.38 | 3066.60 | 2819.83 |
| Shoes      | edu packedu pack #2 |   2000 |     2 |       5213.916667 |   2649.38 | 3206.27 | 3351.30 |
| Men        | importoimporto #2   |   2000 |     1 |       4595.062500 |   2032.46 | 3557.44 | 2372.72 |
| Shoes      | importoedu pack #2  |   2000 |     7 |       5032.343333 |   2469.75 | 2961.97 | 6219.49 |
| Women      | edu packamalg #2    |   2000 |     6 |       4776.425000 |   2216.70 | 2861.21 | 3620.51 |
| Women      | edu packamalg #2    |   2000 |     7 |       4727.370000 |   2168.76 | 2267.53 | 7819.76 |
| Music      | importoscholar #2   |   2000 |     4 |       4953.792500 |   2396.23 | 2087.58 | 2921.95 |
| Children   | importoexporti #2   |   2000 |     5 |       5206.238333 |   2649.03 | 2879.82 | 3447.77 |
+------------+---------------------+--------+-------+-------------------+-----------+---------+---------+
100 rows in set (16.32 sec)
+------------+---------------------+--------+-------+-------------------+-----------+---------+---------+
100 rows in set (16.29 sec)
+------------+---------------------+--------+-------+-------------------+-----------+---------+---------+
100 rows in set (16.33 sec)
+------------+---------------------+--------+-------+-------------------+-----------+---------+---------+
100 rows in set (16.25 sec)