Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.4.6
-
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/
I noticed the following:
this result now returns 511 rows:
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