Fun with Bugs #91 - On MySQL Bug Reports I am Subscribed to, Part XXV

Not sure if it's still interesting to anybody else, but MySQL users keep finding and reporting new problems that may be caused by genuine bugs in the code. I keep checking these reports and subscribing to those I consider interesting. Let me start blogging in the New Year of 2020 with a review of some replication, InnoDB and (many!) optimizer bugs reported in September and October, 2019.

As usual, I start from the oldest and care to mention bug reporters by names and add links to their other bug reports, if any. So, here is the new list:

• Bug #96827 - "mysqlbinlog needs options to abort if invalid events are found on in-use binlogs". I had never checked myself, but I see no reasons not to trust Yoshinori Matsunobu in this case, based on code fragments shared. All current MySQL versions, from 5.6.x to 8.0.x, are affected. From what I see here, MariaDB is also affected.
• Bug #96853 - "Inconsistent super_read_only status when changing the variable is blocked". Nice bug report by Przemyslaw Malkowski from Percona. For some reason I do not see clear statement if MySQL 8 is affected.
• Bug #96874 - "The write_notifier_mutex in log_sys is useless". This bug was reported by Chen Zongzhi on MySQL 8.0.17 (see also hist another similar Bug #97358 - "The log.flush_notifier_mutex in log_sys is useless"), but "Version" filed is empty even though the bug is "Verified". This is NOT acceptable.
• Bug #96946 - "Outer reference in join condition isn't allowed". This bug (that affects all MySQL versions) was reported by Laurents Meyer. See also older Bug #35242 (still "Verified" and affects MariaDB 10.3.x as well).
• Bug #96950 - "CONCAT() can generate corrupted output". I wish we'd see the exact test case, but at least based on code review this bug (reported by Jay Edgar) was verified for MySQL 5.6 and 5.7. I see the same code in MariaDB, unfortunately.
• Bug #97001 - "Dangerous optimization reconsidering_access_paths_for_index_ordering". The problems is with queries like this:
  

  SELECT ... WHERE [secondary key conditions] ORDER BY `id` ASC LIMIT n

  and bug reporter, Jeremy Cole, listed a lot of potentially related older bug reports. he had also suggested a patch. I'd be happy to see the fix in MySQL soon.
• Bug #97113 - "BIT column serialized incorrectly in CASE expression". This bug report was created by Bradley Grainger. It is stated there that MySQL 5.7 (not only 8.0) is affected, but "Version:" field of a verified bug does NOT list 5.7.x. Absolutely wrong way of bugs processing. MariaDB also seems to be inconsistent, even though the result for one of the queries is different:
  

  MariaDB [test]> SELECT CASE WHEN name IS NOT NULL THEN value ELSE NULL END FROM query_bit;
  Field   1:  `CASE WHEN name IS NOT NULL THEN value ELSE NULL END`
  Catalog:    `def`
  Database:   ``
  Table:      ``
  Org_table:  ``
  Type:       NEWDECIMAL
  Collation:  binary (63)
  Length:     2
  Max_length: 1
  Decimals:   0
  Flags:      BINARY NUM
  
  
  +-----------------------------------------------------+
  | CASE WHEN name IS NOT NULL THEN value ELSE NULL END |
  +-----------------------------------------------------+
  | 1                                                   |
  +-----------------------------------------------------+
  1 row in set (0.021 sec)

• Bug #97150 - "rwlock: refine lock->recursive with C11 atomics". Patch for MySQL 8.0.x was contributed by Cai Yibo. See also his another contribution, Bug #97228 - "rwlock: refine lock->lock_word with C11 atomics".
• Bug #97299 - "Improve the explain informations for Hash Joins". Simple EXPLAIN (unlike the one with format=tree) does not give a hint that new MySQL 8.0.18+ features, hash join, was used. Simple and useful feature request from Tibor Korocz.
• Bug #97345 - "IO Thread not detecting failed master with relay_log_space_limit." Nice bug report from Jean-François Gagné, but no documented attempt to check if MySQL 5.6.x and 8.0.x are also affected.
• Bug #97347 - "In some cases queries with ST_CONTAINS do not return any results". Simple and easy to check bug report from Christian Koinig. Note that based on quick test MariaDB is NOT affected:
  

  MariaDB [test]> select version(), count(*) FROM test
      -> WHERE ST_CONTAINS(
      ->  geo_footprint,
      ->  ST_GeomFromGeoJSON('{"type":"Polygon","coordinates":[[[15.11333480819996
  6,48.1337532388],[15.113329984100005,48.1337371609],[15.113411697200036,48.13371
  66354],[15.113673777399981,48.1336819199],[15.114544787600039,48.1335464618],[15
  .115336574000025,48.1334415189],[15.116374992200008,48.1332937084],[15.117266346
  799966,48.1330924824],[15.11769786879995,48.1329803459],[15.118129375299986,48.1
  32868199],[15.118515258099933,48.1327388086],[15.118597296700045,48.1327141533],
  [15.118635348899943,48.132702717],[15.11867907729993,48.1327796282],[15.11876276
  890007,48.13290987],[15.118805112699988,48.1330357889],[15.118850101500016,48.13
  33486685],[15.118823191700017,48.1334777297],[15.118820984299987,48.1334784295],
  [15.118821076099948,48.1334779691],[15.113334808199966,48.1337532388],[15.113334
  808199966,48.1337532388]]]}'));
  +--------------------+----------+
  | version()          | count(*) |
  +--------------------+----------+
  | 10.3.7-MariaDB-log |        1 |
  +--------------------+----------+
  1 row in set (0.003 sec)

• Bug #97372 - "Constructor Query_event must check enough space". Contribution to 5.7 and 8.0 by Pengbo Shi. Waiting for the OCI signed by the contributor...
• Bug #97418 - "MySQL chooses different execution plan in 5.7". Interesting bug report from Vinodh Krish. I am again not sure if versions affected match the results of tests presented here.
• Bug #97421 - "Replace into affected row count not match with events in binlog". Not sure if MySQL 8 was checked, but MariaDB 10.3.7 also uses single Update_rows event in the binary log. Thanks to Ke Lu for noticing and reporting this!

Also, on a separate note, this claim of MySQL 8.0 performance regression from Mark Callaghan, Bug #86215, is still being analyzed it seems. No further comments for 2.5 years already!

Autumn of 2019 was fruitful. A lot of interesting MySQL bug reports also, not just grapes on my balcony...

To summarize:

1. For some reason I often do not see explicit documented attempts by Oracle MySQL engineers from the bugs verification team to check bug on different MySQL versions. Sometimes obviously affected version (like MySQL 8.0.x) is not listed in the field. So "Version" field becomes useless This is absolutely wrong. Maybe I should submit yet another talk to some conference on how to process bugs properly?
2. Some regression bugs are still not marked with "regression" tag when verified.
3. MySQL optimizer still requires a lot of work to become decent.
4. I see a lot of new interesting new bug reports both from well known old community members and users I had never noticed before by name. This is great and proves that MySQL is still alive and use all kinds of contributions from Community.

Next time I'll review interesting bugs reported in November and December, 2019. Stay tuned!