[MDEV-16474] result of join incorrectly depends on their order Created: 2018-06-12  Updated: 2018-08-23  Resolved: 2018-08-23

Status: Closed
Project: MariaDB Server
Component/s: Platform Debian, Storage Engine - TokuDB
Affects Version/s: 10.3.7
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: antoine Assignee: Alice Sherepa
Resolution: Incomplete Votes: 1
Labels: need_feedback, tokudb


 Description   

after migrating from 10.2.xx to 10.3.7, the result produced by subselect queries with multiple joins appears sometimes to depends on the order of the join

like in:
select * from (
select *
from A
join B on A.xx=B.xx
join C on A.xx=C.xx
) t1
join (select ...) t2 on ...
group by ...

producing a different result from:
select * from (
select *
from A
join C on A.xx=C.xx
join B on A.xx=B.xx
) t1
join (select ...) t2 on ...
group by ...

in the more detailed example below, the first query correctly returns rows and the second does not (but no error is produced):

SELECT SQL_NO_CACHE
 
    `c_1001` AS `s_559c1`,
 
    SUM(`c_1003`) AS `s_559c2`
 
FROM
 
(
 
    SELECT 
    `s_5162c5` AS `c_1001`,
 
    `c_1010` AS `c_1005`,
 
    `c_1011` AS `c_1007`,
 
    SUM(`s_5162c8`) AS `c_1003`
 
    FROM
 
        (
 
			SELECT 
			PAY.`DESCRIPTION` AS `s_5162c5`,
 
			TRA_PAY.`AMOUNT` AS `s_5162c8`,
 
			TRA.DATABASE_ID AS `c_1010`,
 
			TRA.SHOP_ID AS `c_1011`
 
			FROM `my_db`.`TRANS_PAYMENTS` TRA_PAY
 
            JOIN `my_db`.`PAYMENTS` PAY ON TRA_PAY.STD_PAYMENT_ID = PAY.ID
 
			JOIN `my_db`.`TRANSACTIONS` TRA ON TRA_PAY.DATABASE_ID = TRA.DATABASE_ID AND TRA_PAY.TRANSACTION_ID = TRA.ID
 
			WHERE (TRA_PAY.`BOOKKEEPING_DATE` = DATE_SUB('2018-06-12', INTERVAL 1 DAY))
 
		) _t_s_5162
 
	GROUP BY `c_1001` , `c_1005` , `c_1007`
 
) _t_s_1000
 
JOIN (
 
	SELECT 
    SHO.DATABASE_ID AS `c_1006`,
 
    SHO.ID AS `c_1008`
 
    FROM `eldora_data`.`SHOPS` SHO
 
) _t_c_1004 ON `_t_s_1000`.`c_1005` = `_t_c_1004`.`c_1006` AND `_t_s_1000`.`c_1007` = `_t_c_1004`.`c_1008`
 
GROUP BY `s_559c1` 


SELECT SQL_NO_CACHE
 
    `c_1001` AS `s_559c1`,
 
    SUM(`c_1003`) AS `s_559c2`
 
FROM
 
(
 
    SELECT 
    `s_5162c5` AS `c_1001`,
 
    `c_1010` AS `c_1005`,
 
    `c_1011` AS `c_1007`,
 
    SUM(`s_5162c8`) AS `c_1003`
 
    FROM
 
        (
 
			SELECT 
			PAY.`DESCRIPTION` AS `s_5162c5`,
 
			TRA_PAY.`AMOUNT` AS `s_5162c8`,
 
			TRA.DATABASE_ID AS `c_1010`,
 
			TRA.SHOP_ID AS `c_1011`
 
			FROM `my_db`.`TRANS_PAYMENTS` TRA_PAY
 
			JOIN `my_db`.`TRANSACTIONS` TRA ON TRA_PAY.DATABASE_ID = TRA.DATABASE_ID AND TRA_PAY.TRANSACTION_ID = TRA.ID
 
			JOIN `my_db`.`PAYMENTS` PAY ON TRA_PAY.STD_PAYMENT_ID = PAY.ID
 
			WHERE (TRA_PAY.`BOOKKEEPING_DATE` = DATE_SUB('2018-06-12', INTERVAL 1 DAY))
 
		) _t_s_5162
 
	GROUP BY `c_1001` , `c_1005` , `c_1007`
 
) _t_s_1000
 
JOIN (
 
	SELECT 
    SHO.DATABASE_ID AS `c_1006`,
 
    SHO.ID AS `c_1008`
 
    FROM `eldora_data`.`SHOPS` SHO
 
) _t_c_1004 ON `_t_s_1000`.`c_1005` = `_t_c_1004`.`c_1006` AND `_t_s_1000`.`c_1007` = `_t_c_1004`.`c_1008`
 
GROUP BY `s_559c1`



 Comments   
Comment by Alice Sherepa [ 2018-06-13 ]

Please add output of 

SHOW CREATE TABLE my_db.TRANS_PAYMENTS;
 
SHOW CREATE TABLE my_db.PAYMENTS;
 
SHOW CREATE TABLE my_db.TRANSACTIONS;
 
SHOW CREATE TABLE `eldora_data`.`SHOPS`;

and your .cnf file(s)
Thanks!

Comment by antoine [ 2018-06-14 ]

here are the create table:

SHOW CREATE TABLE CREATE TABLE `TRANS_PAYMENTS` (
`AMOUNT` decimal(9,2) NOT NULL,
`CREDIT_CARD_ID` tinyint(3) unsigned DEFAULT NULL,
`HASH_CODE` int(10) unsigned NOT NULL,
`PAYMENT_ID` tinyint(3) unsigned NOT NULL,
`TRANSACTION_ID` int(10) unsigned NOT NULL,
`IS_PREPAYMENT` tinyint(1) NOT NULL,
`VOUCHER_ID` tinyint(3) unsigned DEFAULT NULL,
`STD_CREDIT_CARD_ID` tinyint(3) unsigned DEFAULT NULL,
`STD_PAYMENT_ID` tinyint(3) unsigned NOT NULL,
`DATABASE_ID` tinyint(3) unsigned NOT NULL,
`BOOKKEEPING_DATE` date NOT NULL,
`TRANS_TIME` time NOT NULL,
PRIMARY KEY (`DATABASE_ID`,`TRANSACTION_ID`,`HASH_CODE`),
KEY `idx_984476900` (`BOOKKEEPING_DATE`,`STD_PAYMENT_ID`,`DATABASE_ID`,`TRANSACTION_ID`,`HASH_CODE`) `CLUSTERING`=YES,
KEY `idx_1751686773` (`BOOKKEEPING_DATE`,`STD_CREDIT_CARD_ID`,`DATABASE_ID`,`TRANSACTION_ID`,`HASH_CODE`) `CLUSTERING`=YES,
KEY `idx_580352230` (`BOOKKEEPING_DATE`,`DATABASE_ID`,`TRANSACTION_ID`,`HASH_CODE`) `CLUSTERING`=YES,
KEY `idx_TRANS_TIME` (`TRANS_TIME`)
) ENGINE=TokuDB DEFAULT CHARSET=utf8;
TRANS_PAYMENTS;

CREATE TABLE `PAYMENTS` (
`CODE` varchar(10) NOT NULL,
`DESCRIPTION` varchar(40) NOT NULL,
`ID` tinyint(3) unsigned NOT NULL,
`PAYMENT_TYPE` tinyint(3) unsigned NOT NULL,
PRIMARY KEY (`ID`),
KEY `idx_PAYMENT_TYPE_CODE` (`PAYMENT_TYPE`,`CODE`) `CLUSTERING`=YES,
KEY `idx_CODE` (`CODE`) `CLUSTERING`=YES,
KEY `idx_DESCRIPTION` (`DESCRIPTION`) `CLUSTERING`=YES
) ENGINE=TokuDB DEFAULT CHARSET=utf8;

CREATE TABLE `TRANSACTIONS` (
`BOOKKEEPING_DATE` date NOT NULL,
`CARD_NUM` varchar(30) DEFAULT NULL,
`GUESTS_NUM` smallint(5) unsigned DEFAULT NULL,
`ID` int(10) unsigned NOT NULL,
`OPERATOR_ID` smallint(5) unsigned DEFAULT NULL,
`SHOP_ID` tinyint(3) unsigned NOT NULL,
`TILL_ID` tinyint(3) unsigned NOT NULL,
`TOTAL_AMOUNT` decimal(9,2) NOT NULL,
`TRANSACTION_CAUSAL_ID` tinyint(3) unsigned DEFAULT NULL,
`TRANS_DATE` date NOT NULL,
`TRANS_NUM` int(10) unsigned NOT NULL,
`TRANS_TIME` time NOT NULL,
`DATABASE_ID` tinyint(3) unsigned NOT NULL,
`ARTICLE_COUNT` decimal(9,2) DEFAULT 0.00,
`CUSTOMER_ID` smallint(5) unsigned DEFAULT NULL,
`TOTAL_REBATE` decimal(8,2) DEFAULT 0.00,
`TURNOVER_AMOUNT` decimal(8,2) DEFAULT 0.00,
PRIMARY KEY (`DATABASE_ID`,`ID`),
KEY `idx_680711615` (`BOOKKEEPING_DATE`,`DATABASE_ID`,`SHOP_ID`,`ID`) `CLUSTERING`=YES,
KEY `idx_1470032661` (`DATABASE_ID`,`SHOP_ID`,`BOOKKEEPING_DATE`,`ID`) `CLUSTERING`=YES,
KEY `idx_744752816` (`DATABASE_ID`,`CUSTOMER_ID`,`BOOKKEEPING_DATE`,`ID`) `CLUSTERING`=YES,
KEY `idx_328727407` (`DATABASE_ID`,`CARD_NUM`,`BOOKKEEPING_DATE`,`ID`) `CLUSTERING`=YES,
KEY `idx_742284143` (`BOOKKEEPING_DATE`,`DATABASE_ID`,`ID`) `CLUSTERING`=YES,
KEY `idx_CARD_NUM` (`CARD_NUM`),
KEY `idx_OPERATOR_ID` (`OPERATOR_ID`),
KEY `idx_SHOP_ID` (`SHOP_ID`),
KEY `idx_TILL_ID` (`TILL_ID`),
KEY `idx_TOTAL_AMOUNT` (`TOTAL_AMOUNT`),
KEY `idx_TRANS_DATE` (`TRANS_DATE`),
KEY `idx_TRANS_NUM` (`TRANS_NUM`),
KEY `idx_TRANS_TIME` (`TRANS_TIME`),
KEY `idx_ARTICLE_COUNT` (`ARTICLE_COUNT`),
KEY `idx_TOTAL_REBATE` (`TOTAL_REBATE`),
KEY `idx_TURNOVER_AMOUNT` (`TURNOVER_AMOUNT`)
) ENGINE=TokuDB DEFAULT CHARSET=utf8;

CREATE TABLE `SHOPS` (
`ID` tinyint(3) unsigned NOT NULL,
`CODE` varchar(10) NOT NULL,
`DESCRIPTION` varchar(40) NOT NULL,
`LOCATION_ID` tinyint(3) unsigned NOT NULL,
`SHOP_TYPE_ID` tinyint(3) unsigned NOT NULL,
`DATABASE_ID` tinyint(3) unsigned NOT NULL,
PRIMARY KEY (`DATABASE_ID`,`ID`),
KEY `idx_DATABASE_ID_CODE` (`DATABASE_ID`,`CODE`) `CLUSTERING`=YES,
KEY `idx_1105695918` (`DATABASE_ID`,`DESCRIPTION`) `CLUSTERING`=YES,
KEY `idx_DESCRIPTION` (`DESCRIPTION`) `CLUSTERING`=YES
) ENGINE=TokuDB DEFAULT CHARSET=utf8;

Comment by antoine [ 2018-06-14 ] 

tokudb.cnf:
 
[mysqld]
 
 
 
#
 
# * TokuDB
 
#
 
plugin-load-add                 = ha_tokudb.so
 
tokudb_create_index_online      = OFF
 
tokudb_disable_hot_alter        = ON
 
tokudb_lock_timeout             = 60000
 
tokudb_dir_per_db               = ON
 
 
 
tokudb_loader_memory_size       = 200M
 
tokudb_read_buf_size            = 655360
 
 
 
--------------------------------------
 
 
 
innodb.cnf:
 
 
 
 
 
[mysqld]
 
 
 
#
 
# * InnoDB
 
#
 
innodb_buffer_pool_size         = 2G
 
innodb_autoinc_lock_mode        = 2
 
innodb_file_per_table           = 1
 
innodb_open_files               = 400
 
innodb_autoinc_lock_mode        = 2
 
 
 
 
 
innodb_log_buffer_size          = 8M
 
innodb_flush_log_at_trx_commit  = 2
 
innodb_thread_concurrency       = 0
 
innodb_read_io_threads          = 64
 
innodb_write_io_threads         = 64
 
innodb_strict_mode              = 1
 
innodb_log_file_size            = 1G
 
innodb_flush_method             = O_DIRECT
 
innodb_flush_neighbors          = 0
 
innodb_io_capacity              = 2000
 
innodb_io_capacity_max          = 6000
 
innodb_lru_scan_depth           = 2000
 
innodb_compression_algorithm    = zlib
 
 
 
innodb_data_file_path           = ibdata1:12M;ibdata2:12M;ibdata3:50M
 
 
 
 
 
-----------------
 
 
 
general.cnf:
 
 
 
[mysqld]
 
#
 
# * Basic Settings
 
#
 
user                            = mysql
 
pid-file                        = /var/run/mysqld/mysqld.pid
 
socket                          = /var/run/mysqld/mysqld.sock
 
port                            = 3306
 
basedir                         = /usr
 
datadir                         = /srv/mariadb/data
 
tmpdir                          = /srv/mariadb/tmp
 
lc_messages_dir                 = /usr/share/mysql
 
lc_messages                     = en_US
 
skip-external-locking
 
skip-name_resolve
 
default_storage_engine          = TokuDB
 
 
 
#
 
# * Fine Tuning
 
#
 
max_connections                 = 200
 
connect_timeout                 = 5
 
wait_timeout                    = 3600
 
max_allowed_packet              = 1GB
 
thread_handling                 = pool-of-threads
 
sort_buffer_size                = 128M
 
bulk_insert_buffer_size         = 16M
 
tmp_table_size                  = 256M
 
max_heap_table_size             = 256M
 
join_cache_level                = 8
 
optimizer_switch                = "index_merge_sort_intersection=on,mrr=off,mrr_sort_keys=on,optimize_join_buffer_size=off"
 
 
 
 
 
#
 
# * Query Cache
 
#
 
# Cache only tiny result sets, so we can fit more in the query cache.
 
query_cache_limit               = 5M
 
query_cache_size                = 512M
 
query_cache_type                = DEMAND
 
 
 
 
 
#
 
# * Binary log/replication
 
#
 
log-bin                         = /srv/mariadb/binlog/mariadb-bin
 
log_bin_index                   = /srv/mariadb/binlog/mariadb-bin.index
 
sync_binlog                     = 0
 
sync_relay_log                  = 0
 
expire_logs_days                = 1
 
max_binlog_size                 = 1G
 
binlog_format                   = MIXED
 
transaction-isolation           = READ-COMMITTED
 
log_bin_trust_function_creators = 1
 
binlog_format                   = MIXED
 
log-bin-compress                = 1
 
 
 
 
 
#
 
# * Misc
 
#
 
character-set-server            = utf8
 
collation-server                = utf8_general_ci
 
character_set_client_handshake  = false
 
max-connect-errors              = 100000
 
sql_mode                        = "STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_AUTO_VALUE_ON_ZERO,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE"
 
 
 
[client]
 
default-character-set           = utf8
 
 
 
 
 
 
 
---------------
 
 
 
log.cnf:
 
 
 
[mysqld]
 
 
 
# see param values at https://mariadb.com/kb/en/mariadb/server-system-variables/
 
 
 
# error log
 
log_error               = /var/log/mysql/error.log
 
log_warnings            = 2
 
 
 
# slow query log
 
slow_query_log          = 1
 
slow_query_log_file     = /var/log/mysql/slow.log
 
long_query_time = 30
 
log_slow_rate_limit     = 1000
 
log_slow_verbosity      = query_plan
 
log_queries_not_using_indexes = 1
 
 
 
# general log
 
general_log             = 0
 
general_log_file        = /var/log/mysql/mysql.log

Comment by Alice Sherepa [ 2018-06-15 ]

I can not repeat with different results from the query, but I get error "Unknown column 'PAY.DESCRIPTION' in 'field list'"
so it looks that the problem is related to MDEV-16420

Comment by Alice Sherepa [ 2018-07-23 ]

alebacq Is it suitable for you to test if the failure is still presented after the bugfix for MDEV-16420, which was released in 10.3.8?

Comment by Elena Stepanova [ 2018-08-23 ]

Please comment if you have an answer to the question above and/or more information.

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