At the Percona engineering team, we often receive requests to analyze changes in MySQL/Percona Server for MySQL behavior from one version to another, either due to regression or a bug fix (when having to point out to a customer that commit X has fixed their issue and upgrading to a version including that fix will solve their problem).
In this blog post, we will analyze the approach used to fix PS-7019 – Correct query results for LEFT JOIN with GROUP BY.
Each release comes with a lot of changes. For example, the difference between MySQL 8.0.19 to 8.0.20:
|
1 2 3 4 |
git diff mysql-8.0.19..mysql-8.0.20 | wc -l 737454 git diff mysql-8.0.19..mysql-8.0.20 --name-only | wc -l 4495 |
737K lines in 4495 files have changed from one minor version to another.
|
1 2 |
git log mysql-8.0.19..mysql-8.0.20 --pretty=oneline | wc -l 1966 |
8.0.20 alone has 1966 commits. Analyzing each one of the files or either commits isn’t practical. Let’s have a look at how we can narrow down the issue to a particular commit.
Git Bisect
Git bisect is a powerful command in git. It navigates through commit history using the well-known binary search algorithm to efficiently find the offending commit. We basically need to specify a point that we know is bad and ideally, (if known) a starting point where we know the issue is not present. With that information, it will sort the commit from bad and good and start in the middle point. We will then have to evaluate if that point is good or bad. Based on that evaluation, it will decide to move forward or backward to the next half of commits following the binary search algorithm. The below flow helps to demonstrate it in action:
We start with a range of 20 commits, and we instruct git bisect that commit 1 is a good commit (does not have the issue) and commit 20 is a bad commit. With this information, it will checkout commit 10. Then we test this particular commit. On the above example, commit 10 is still bad, so we can infer that commits between 10 and 20 are all bad, move the upper mark (bad commit) to 10 and we move the working range to the bottom half of commits (10 to 1/ 8 commits to test).
At this point, git will stop a commit 5, and we check that 5 is still a good commit. We no longer need commits from 1 to 4 since we know they are all good. This time we move the lower mark (good commit) to 5, cutting down the working range to commits between 5 to 10 (4 commits to test).
Stopping at commit 7, we have validated that it is a bad commit. As you can imagine by now, git will change the upper mark to commit 7. This leaves us to a single commit to test.
As a final step in our example, we validated commit 6 and it is still a bad commit. At this point, we don’t have any further commit to test between upper and lower marks. This means we have found the first commit introducing a regression.
MySQL MTR
MySQL has a powerful test framework – MySQL Test Run, a.k.a. MTR. For brevity, I will not enter too deep into it during this post. Readers can find more information at online documentation and in this webinar.
In the scope of git bisect, we will be using MTR as validation to test each commit to verify if it is a good or bad commit.
Case Study
PS-7019 describes an issue with GROUP BY queries starting to happen on MySQL 8.0.20, where queries are returning different results from MySQL 8.0.19:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 |
mysql> CREATE TABLE t1 (t1_id INT PRIMARY KEY AUTO_INCREMENT, t2_id INT DEFAULT NULL); Query OK, 0 rows affected (0,04 sec) mysql> CREATE TABLE t2 (t2_id INT PRIMARY KEY AUTO_INCREMENT, is_active TINYINT NOT NULL DEFAULT '1'); Query OK, 0 rows affected (0,01 sec) mysql> INSERT INTO t2 VALUES (2,1),(3,0),(1000,1); Query OK, 3 rows affected (0,01 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> INSERT INTO t1 VALUES (1,1000),(2,5); Query OK, 2 rows affected (0,00 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql 8.0.19> SELECT t1.t1_id,t1.t2_id,t2.t2_id FROM t1 LEFT JOIN t2 ON (t1.t2_id = t2.t2_id AND t2.is_active=1) GROUP BY t1_id ORDER BY t1_id LIMIT 2; +-------+-------+-------+ | t1_id | t2_id | t2_id | +-------+-------+-------+ | 1 | 1000 | 1000 | | 2 | 5 | NULL | +-------+-------+-------+ 2 rows in set (0,00 sec) mysql 8.0.20> SELECT t1.t1_id,t1.t2_id,t2.t2_id FROM t1 LEFT JOIN t2 ON (t1.t2_id = t2.t2_id AND t2.is_active=1) GROUP BY t1_id ORDER BY t1_id LIMIT 2; +-------+-------+-------+ | t1_id | t2_id | t2_id | +-------+-------+-------+ | 1 | 1000 | NULL | | 2 | 5 | NULL | +-------+-------+-------+ 2 rows in set (0,00 sec) |
As you can see above, t2_id for the first line returns 1000 on 8.0.19 while on 8.0.20 it returns NULL. We will use the above example as a test case.
To get started, let me explain how my directories are organized:
|
1 2 |
/work/mysql/src - source code / git repository /work/mysql/bld - build directory, where cmake/make and mtr will run |
We also need to set up the test case to validate each commit and the expected result of the test case. We can create the result file manually or we can instruct MTR to record it. MTR is sensitive to white-spaces, uppercase, and so on. As some advice, always get MTR to record the result file for you.
Assuming you have a MySQL version without the issue, set up the test case by running:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
cd mysql-test cat <<EOF > t/bisect.test --echo # --echo BUG #99398 / PS-7019: Wrong query results for LEFT JOIN with GROUP BY since 8.0.20 --echo # CREATE TABLE t1 (t1_id INT PRIMARY KEY AUTO_INCREMENT, t2_id INT DEFAULT NULL); CREATE TABLE t2 (t2_id INT PRIMARY KEY AUTO_INCREMENT, is_active TINYINT NOT NULL DEFAULT '1'); INSERT INTO t2 VALUES (2,1),(3,0),(1000,1); INSERT INTO t1 VALUES (1,1000),(2,5); SELECT t1.t1_id,t1.t2_id,t2.t2_id FROM t1 LEFT JOIN t2 ON (t1.t2_id = t2.t2_id AND t2.is_active=1) GROUP BY t1_id ORDER BY t1_id LIMIT 2; DROP TABLE t1,t2; EOF ./mtr bisect --record |
With all the setup done, move t/bisect.test and r/bisect.result files into your source tree and let’s start git bisect showing bad and good commit. Here we will be passing the release tags instead of commit hash:
|
1 2 3 |
marcelo@marce-bld:/work/mysql/src$ git bisect start mysql-8.0.20 mysql-8.0.19 Bisecting: 983 revisions left to test after this (roughly 10 steps) [780a3f8418b87a8ac7d754050c5303b2658c3dcb] NULL Merge branch 'mysql-8.0' into mysql-trunk |
At this point, git placed my src tree to 780a3f8418b87a8ac7d754050c5303b2658c3dcb commit. Now we will use git bisect run passing a shell script that will run a few steps to validate each commit:
- Enter the build directory
- Run cmake & make to recompile the server with the changes from each commit
- Start mtr testing
- Based on the exit code of MTR we will exit our script
Git bisect will consider exit code 0 as a good commit and everything else as a bad commit.
|
1 2 3 4 5 6 7 8 9 10 |
marcelo@marce-bld:/work/mysql/src$ git bisect run sh -c ' cd /work/mysql/bld/ cmake /work/mysql/src -DWITH_BOOST=/work/boost make -j 32 /work/mysql/bld/mysql-test/mtr bisect if [ "$?" -eq "0" ]; then exit 0 else exit |