[MDEV-20075] LEFT JOIN and JOIN behaves differently since upgrade to 10.4 Created: 2019-07-16  Updated: 2019-07-17

Status: Open
Project: MariaDB Server
Component/s: Prepared Statements, Query Cache
Affects Version/s: 10.4.6
Fix Version/s: 10.4

Type: Bug Priority: Major
Reporter: Andras Assignee: Igor Babaev
Resolution: Unresolved Votes: 0
Labels: innodb, regression
Environment:

centos 6, 64 bit, 10.4.6-MariaDB (x86_64), no replication


 Description   

I had to upgrade to 10.4 yesterday from 10.1. I went through each major version and ran mysql_upgrade after each.

Today I noticed strange problems on our selects:

This select returns 1 result:

SELECT prod.* FROM product_groups_790 AS prod JOIN product_site_publication_index_790 AS pr_publ_index ON pr_publ_index.product_id=prod.cod_varianta AND pr_publ_index.is_published AND pr_publ_index.site_id = '83' WHERE prod.cod_varianta=6922

This select returns 0 results:

SELECT prod.* FROM product_groups_790 AS prod JOIN product_site_publication_index_790 AS pr_publ_index ON pr_publ_index.product_id=prod.cod_varianta AND pr_publ_index.is_published AND pr_publ_index.site_id = '83'

The same selct on the same database structure on version 10.2.25 returns a a whole lot of results.

I noticed similar problems on LEFT JOINS, even if there is data to be joined, it is NOT joined and the result set is empty, unless there is a condition on the main table on the left side.
The left side select should not affect the joined result.

I am not sure which version is related to (10.3 or 10.4) nor where to start to debug.

I tried setting optimizer switch for 10.1 defaults but no change:
https://mariadb.com/kb/en/library/optimizer-switch/

 Comments   
Comment by Andras [ 2019-07-16 ]

I noticed the following:
this result now returns 511 rows:

SELECT prod.* FROM product_groups_790 AS prod JOIN product_site_publication_index_790 AS pr_publ_index ON pr_publ_index.product_id=prod.cod_varianta AND pr_publ_index.is_published=1 AND pr_publ_index.site_id = '83'

seems to me the is_published is not evaluated to true, even if it contains 1
is_published was defined as boolean:

CREATE TABLE `product_site_publication_index_790` (
`product_id` int(11) NOT NULL,
`site_id` int(11) NOT NULL,
`is_promo` tinyint(1) NOT NULL DEFAULT 0,
`is_published` tinyint(1) NOT NULL DEFAULT 1,
KEY `is_published` (`is_published`),
KEY `product_id` (`product_id`),
KEY `site_id` (`site_id`),
KEY `is_promo` (`is_promo`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

Comment by Andras [ 2019-07-16 ]

I also tried removing all indexes on the joined table and the results change. WHY would having or not having indexes affect the result, it should only affect the speed.

Comment by Andras [ 2019-07-16 ]

this seems to be related to https://jira.mariadb.org/browse/MDEV-19911
after setting optimizer switch rowid_filter=off, the queries seem to return identical results.

I will be monitoring and be back with an update

If someone is looking into this, can you please check:

  • why adding/removing indexes would affect joins
  • why left join would react as a right join
  • why join would return no data, unless there is a condition on the left table's primary key
Comment by Igor Babaev [ 2019-07-17 ]

Hi Andras,
With no test case I won't be able to check whether you problem has been already solved.

Comment by Andras [ 2019-07-17 ]

it is hard to create a test case now as this is on a production server with hundreds of databases.
if 10.4.7 comes out, I will try disabling the rowid_filter and test
if it still presents itself I will create a sample database to simulate
but in principle:
table A with 3 columns id(primary),name(tinytext),status_id(index)
table B with 3 columns status_id(index),status_text(tinytext),active(tinyint(1)) (1 or 0)
SELECT A.* FROM A
JOIN B ON B.status_id=A.status_id AND B.active
this gave a different result than
SELECT A.* FROM A
JOIN B ON B.status_id=A.status_id AND B.active
WHERE A.id=1
also different result if B had no indexes
using innodb, uf_unicode_8bit for text fields, 10.4.6 mariadb

Generated at Thu Feb 08 08:56:32 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.