###################################################################################
###################################### 9.1.0 ###################################### 
###################################################################################

## Source build
/export/home/tmp/ushastry/src]$ wget -Omysql-9.1.0.tar.gz https://dev.mysql.com/get/Downloads/MySQL-9.1/mysql-9.1.0.tar.gz
tar -zxvf mysql-9.1.0.tar.gz

--  For TPC-H Setup etc, please see Bug#116309
-- TPC-H data scale: 1GB


############################################################################
###################################### without patch
############################################################################

scl enable devtoolset-11 bash
MYSQL_VERSION="Bug116241_910"
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 .. -DBUILD_CONFIG=mysql_release -DWITH_SSL=system -DWITH_UNIT_TESTS=0  -DCMAKE_INSTALL_PREFIX=$TARGET -G Ninja
ninja
ninja install
cd $TARGET



BugNumber=116241
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/Bug116241_910]$ 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: 9.1.0 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> \q
Bye
[umshastr@support-cluster03:/export/home/tmp/ushastry/src/Bug116241_910]$ bin/mysql -uroot -S/tmp/mysql.sock < /home/umshastr/work/binaries/utils/tpch-kit-master/dbgen/dss.ddl
[umshastr@support-cluster03:/export/home/tmp/ushastry/src/Bug116241_910]$ bin/mysql -uroot -S/tmp/mysql.sock  < /home/umshastr/work/binaries/utils/tpch-kit-master/dbgen/dss.ri
[umshastr@support-cluster03:/export/home/tmp/ushastry/src/Bug116241_910]$ bin/mysql -uroot -S/tmp/mysql.sock --local-infile=1 < /home/umshastr/work/binaries/utils/tpch-kit-master/dbgen/loaddata.sql
[umshastr@support-cluster03:/export/home/tmp/ushastry/src/Bug116241_910]$

[umshastr@support-cluster03:/export/home/tmp/ushastry/src/Bug116241_910]$ time bin/mysql -uroot -S/tmp/mysql.sock tpch < 22.sql
*************************** 1. row ***************************
EXPLAIN: -> Sort: custsale.cntrycode  (actual time=159..159 rows=4 loops=1)
    -> Table scan on <temporary>  (actual time=159..159 rows=4 loops=1)
        -> Aggregate using temporary table  (actual time=159..159 rows=4 loops=1)
            -> Nested loop antijoin  (cost=92777 rows=746204) (actual time=59.8..156 rows=3674 loops=1)
                -> Filter: ((substr(customer.C_PHONE,1,2) in ('20','40','22','30','39','42','21')) and (customer.C_ACCTBAL > (select #3)))  (cost=5423 rows=49664) (actual time=59.8..122 rows=10834 loops=1)
                    -> Table scan on customer  (cost=5423 rows=149006) (actual time=0.128..36 rows=150000 loops=1)
                    -> Select #3 (subquery in condition; run only once)
                        -> Aggregate: avg(customer.C_ACCTBAL)  (cost=26801 rows=1) (actual time=59.7..59.7 rows=1 loops=1)
                            -> Filter: ((customer.C_ACCTBAL > 0.00) and (substr(customer.C_PHONE,1,2) in ('20','40','22','30','39','42','21')))  (cost=15357 rows=49664) (actual time=0.0886..57.8 rows=21632 loops=1)
                                -> Table scan on customer  (cost=15357 rows=149006) (actual time=0.0851..29.2 rows=150000 loops=1)
                -> Covering index lookup on orders using ORDERS_FK1 (O_CUSTKEY = customer.C_CUSTKEY)  (cost=3.85 rows=15) (actual time=0.00307..0.00307 rows=0.661 loops=10834)


real	0m0.166s
user	0m0.004s
sys	0m0.002s

^^ after 5+ run it is stable

############################################################################
###################################### with patch
############################################################################

-- Apply patch on top of 9.1.0

scl enable devtoolset-11 bash
MYSQL_VERSION="Bug116241_910_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 .. -DBUILD_CONFIG=mysql_release -DWITH_SSL=system -DWITH_UNIT_TESTS=0  -DCMAKE_INSTALL_PREFIX=$TARGET -G Ninja
ninja
ninja install
cd $TARGET



BugNumber=116241
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/Bug116241_910_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: 9.1.0 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> \q
Bye
[umshastr@support-cluster03:/export/home/tmp/ushastry/src/Bug116241_910_patched]$ bin/mysql -uroot -S/tmp/mysql.sock < /home/umshastr/work/binaries/utils/tpch-kit-master/dbgen/dss.ddl
[umshastr@support-cluster03:/export/home/tmp/ushastry/src/Bug116241_910_patched]$ bin/mysql -uroot -S/tmp/mysql.sock  < /home/umshastr/work/binaries/utils/tpch-kit-master/dbgen/dss.ri
[umshastr@support-cluster03:/export/home/tmp/ushastry/src/Bug116241_910_patched]$ bin/mysql -uroot -S/tmp/mysql.sock --local-infile=1 < /home/umshastr/work/binaries/utils/tpch-kit-master/dbgen/loaddata.sql
[umshastr@support-cluster03:/export/home/tmp/ushastry/src/Bug116241_910_patched]$

[umshastr@support-cluster03:/export/home/tmp/ushastry/src/Bug116241_910_patched]$ time bin/mysql -uroot -S/tmp/mysql.sock tpch < 22.sql
*************************** 1. row ***************************
EXPLAIN: -> Sort: custsale.cntrycode  (actual time=99.8..99.8 rows=4 loops=1)
    -> Table scan on <temporary>  (actual time=99.8..99.8 rows=4 loops=1)
        -> Aggregate using temporary table  (actual time=99.8..99.8 rows=4 loops=1)
            -> Nested loop antijoin  (cost=102823 rows=747808) (actual time=0.109..97.1 rows=3674 loops=1)
                -> Filter: ((substr(customer.C_PHONE,1,2) in ('20','40','22','30','39','42','21')) and (customer.C_ACCTBAL > 5001.43))  (cost=15331 rows=49575) (actual time=0.0829..62.5 rows=10834 loops=1)
                    -> Table scan on customer  (cost=15331 rows=148740) (actual time=0.0786..37 rows=150000 loops=1)
                -> Covering index lookup on orders using ORDERS_FK1 (O_CUSTKEY = customer.C_CUSTKEY)  (cost=3.87 rows=15.1) (actual time=0.00307..0.00307 rows=0.661 loops=10834)


real	0m0.168s
user	0m0.003s
sys	0m0.003s

^^ after 5+ run it is stable