Description:
In the result of explain select ..., select number in the query is decimal while the select number in hints like JOIN_SUFFIX is hexadecimal. There seems to be no reason to use two different number systems here and the mismatch of number systems may confuse users.
How to repeat:
create table t1 (id int, score int);
create table t2 (id int, score int);
create table t3 (id int, score int);
create table t4 (id int, score int);
explain select count(*) from (
(select /*+ JOIN_SUFFIX(t3, t2) */ t1.score from t1, t2, t3, t4 where t1.id = t2.id and t2.id = t3.id and t3.id = t4.id) union all
(select /*+ JOIN_SUFFIX(t3, t2) */ t1.score from t1, t2, t3, t4 where t1.id = t2.id and t2.id = t3.id and t3.id = t4.id) union all
(select /*+ JOIN_SUFFIX(t3, t2) */ t1.score from t1, t2, t3, t4 where t1.id = t2.id and t2.id = t3.id and t3.id = t4.id) union all
(select /*+ JOIN_SUFFIX(t3, t2) */ t1.score from t1, t2, t3, t4 where t1.id = t2.id and t2.id = t3.id and t3.id = t4.id) union all
(select /*+ JOIN_SUFFIX(t3, t2) */ t1.score from t1, t2, t3, t4 where t1.id = t2.id and t2.id = t3.id and t3.id = t4.id) union all
(select /*+ JOIN_SUFFIX(t3, t2) */ t1.score from t1, t2, t3, t4 where t1.id = t2.id and t2.id = t3.id and t3.id = t4.id) union all
(select /*+ JOIN_SUFFIX(t3, t2) */ t1.score from t1, t2, t3, t4 where t1.id = t2.id and t2.id = t3.id and t3.id = t4.id) union all
(select /*+ JOIN_SUFFIX(t3, t2) */ t1.score from t1, t2, t3, t4 where t1.id = t2.id and t2.id = t3.id and t3.id = t4.id) union all
(select /*+ JOIN_SUFFIX(t3, t2) */ t1.score from t1, t2, t3, t4 where t1.id = t2.id and t2.id = t3.id and t3.id = t4.id) union all
(select /*+ JOIN_SUFFIX(t3, t2) */ t1.score from t1, t2, t3, t4 where t1.id = t2.id and t2.id = t3.id and t3.id = t4.id) union all
(select /*+ JOIN_SUFFIX(t3, t2) */ t1.score from t1, t2, t3, t4 where t1.id = t2.id and t2.id = t3.id and t3.id = t4.id) union all
(select /*+ JOIN_SUFFIX(t3, t2) */ t1.score from t1, t2, t3, t4 where t1.id = t2.id and t2.id = t3.id and t3.id = t4.id)
) a;
show warnings \G
The result is
*************************** 1. row ***************************
Level: Note
Code: 1003
Message: /* select#1 */ select /*+ JOIN_SUFFIX(@`select#2` `t3`,`t2`) JOIN_SUFFIX(@`select#3` `t3`,`t2`) JOIN_SUFFIX(@`select#4`
`t3`,`t2`) JOIN_SUFFIX(@`select#5` `t3`,`t2`) JOIN_SUFFIX(@`select#6` `t3`,`t2`) JOIN_SUFFIX(@`select#7` `t3`,`t2`) JOIN_SUFFIX
(@`select#8` `t3`,`t2`) JOIN_SUFFIX(@`select#9` `t3`,`t2`) JOIN_SUFFIX(@`select#a` `t3`,`t2`) JOIN_SUFFIX(@`select#b` `t3`,`t2`)
JOIN_SUFFIX(@`select#c` `t3`,`t2`) JOIN_SUFFIX(@`select#d` `t3`,`t2`) */ count(0) AS `count(*)` from (/* select#2 */ select `db
1`.`t1`.`score` AS `score` from `db1`.`t1` join `db1`.`t2` join `db1`.`t3` join `db1`.`t4` where ((`db1`.`t4`.`id` = `db1`.`t1`.
`id`) and (`db1`.`t3`.`id` = `db1`.`t1`.`id`) and (`db1`.`t2`.`id` = `db1`.`t1`.`id`)) union all /* select#3 */ select `db1`.`t1
`.`score` AS `score` from `db1`.`t1` join `db1`.`t2` join `db1`.`t3` join `db1`.`t4` where ((`db1`.`t4`.`id` = `db1`.`t1`.`id`)
and (`db1`.`t3`.`id` = `db1`.`t1`.`id`) and (`db1`.`t2`.`id` = `db1`.`t1`.`id`)) union all /* select#4 */ select `db1`.`t1`.`sco
re` AS `score` from `db1`.`t1` join `db1`.`t2` join `db1`.`t3` join `db1`.`t4` where ((`db1`.`t4`.`id` = `db1`.`t1`.`id`) and (`
db1`.`t3`.`id` = `db1`.`t1`.`id`) and (`db1`.`t2`.`id` = `db1`.`t1`.`id`)) union all /* select#5 */ select `db1`.`t1`.`score` AS
`score` from `db1`.`t1` join `db1`.`t2` join `db1`.`t3` join `db1`.`t4` where ((`db1`.`t4`.`id` = `db1`.`t1`.`id`) and (`db1`.`
t3`.`id` = `db1`.`t1`.`id`) and (`db1`.`t2`.`id` = `db1`.`t1`.`id`)) union all /* select#6 */ select `db1`.`t1`.`score` AS `scor
e` from `db1`.`t1` join `db1`.`t2` join `db1`.`t3` join `db1`.`t4` where ((`db1`.`t4`.`id` = `db1`.`t1`.`id`) and (`db1`.`t3`.`i
d` = `db1`.`t1`.`id`) and (`db1`.`t2`.`id` = `db1`.`t1`.`id`)) union all /* select#7 */ select `db1`.`t1`.`score` AS `score` fro
m `db1`.`t1` join `db1`.`t2` join `db1`.`t3` join `db1`.`t4` where ((`db1`.`t4`.`id` = `db1`.`t1`.`id`) and (`db1`.`t3`.`id` = `
db1`.`t1`.`id`) and (`db1`.`t2`.`id` = `db1`.`t1`.`id`)) union all /* select#8 */ select `db1`.`t1`.`score` AS `score` from `db1
`.`t1` join `db1`.`t2` join `db1`.`t3` join `db1`.`t4` where ((`db1`.`t4`.`id` = `db1`.`t1`.`id`) and (`db1`.`t3`.`id` = `db1`.`
t1`.`id`) and (`db1`.`t2`.`id` = `db1`.`t1`.`id`)) union all /* select#9 */ select `db1`.`t1`.`score` AS `score` from `db1`.`t1`
join `db1`.`t2` join `db1`.`t3` join `db1`.`t4` where ((`db1`.`t4`.`id` = `db1`.`t1`.`id`) and (`db1`.`t3`.`id` = `db1`.`t1`.`i
d`) and (`db1`.`t2`.`id` = `db1`.`t1`.`id`)) union all /* select#10 */ select `db1`.`t1`.`score` AS `score` from `db1`.`t1` join
`db1`.`t2` join `db1`.`t3` join `db1`.`t4` where ((`db1`.`t4`.`id` = `db1`.`t1`.`id`) and (`db1`.`t3`.`id` = `db1`.`t1`.`id`) a
nd (`db1`.`t2`.`id` = `db1`.`t1`.`id`)) union all /* select#11 */ select `db1`.`t1`.`score` AS `score` from `db1`.`t1` join `db1
`.`t2` join `db1`.`t3` join `db1`.`t4` where ((`db1`.`t4`.`id` = `db1`.`t1`.`id`) and (`db1`.`t3`.`id` = `db1`.`t1`.`id`) and (`
db1`.`t2`.`id` = `db1`.`t1`.`id`)) union all /* select#12 */ select `db1`.`t1`.`score` AS `score` from `db1`.`t1` join `db1`.`t2
` join `db1`.`t3` join `db1`.`t4` where ((`db1`.`t4`.`id` = `db1`.`t1`.`id`) and (`db1`.`t3`.`id` = `db1`.`t1`.`id`) and (`db1`.
`t2`.`id` = `db1`.`t1`.`id`)) union all /* select#13 */ select `db1`.`t1`.`score` AS `score` from `db1`.`t1` join `db1`.`t2` joi
n `db1`.`t3` join `db1`.`t4` where ((`db1`.`t4`.`id` = `db1`.`t1`.`id`) and (`db1`.`t3`.`id` = `db1`.`t1`.`id`) and (`db1`.`t2`.
`id` = `db1`.`t1`.`id`))) `a`
Suggested fix:
I suggest to use decimal in hints.
The code to change is in opt_hints.cc:202
Opt_hints_qb::Opt_hints_qb(Opt_hints *opt_hints_arg, MEM_ROOT *mem_root_arg,
uint select_number_arg)
: Opt_hints(nullptr, opt_hints_arg, mem_root_arg),
select_number(select_number_arg),
subquery_hint(nullptr),
semijoin_hint(nullptr),
join_order_hints(mem_root_arg),
join_order_hints_ignored(0) {
sys_name.str = buff;
sys_name.length =
snprintf(buff, sizeof(buff), "%s%x", sys_qb_prefix.str, select_number);
}
Description: In the result of explain select ..., select number in the query is decimal while the select number in hints like JOIN_SUFFIX is hexadecimal. There seems to be no reason to use two different number systems here and the mismatch of number systems may confuse users. How to repeat: create table t1 (id int, score int); create table t2 (id int, score int); create table t3 (id int, score int); create table t4 (id int, score int); explain select count(*) from ( (select /*+ JOIN_SUFFIX(t3, t2) */ t1.score from t1, t2, t3, t4 where t1.id = t2.id and t2.id = t3.id and t3.id = t4.id) union all (select /*+ JOIN_SUFFIX(t3, t2) */ t1.score from t1, t2, t3, t4 where t1.id = t2.id and t2.id = t3.id and t3.id = t4.id) union all (select /*+ JOIN_SUFFIX(t3, t2) */ t1.score from t1, t2, t3, t4 where t1.id = t2.id and t2.id = t3.id and t3.id = t4.id) union all (select /*+ JOIN_SUFFIX(t3, t2) */ t1.score from t1, t2, t3, t4 where t1.id = t2.id and t2.id = t3.id and t3.id = t4.id) union all (select /*+ JOIN_SUFFIX(t3, t2) */ t1.score from t1, t2, t3, t4 where t1.id = t2.id and t2.id = t3.id and t3.id = t4.id) union all (select /*+ JOIN_SUFFIX(t3, t2) */ t1.score from t1, t2, t3, t4 where t1.id = t2.id and t2.id = t3.id and t3.id = t4.id) union all (select /*+ JOIN_SUFFIX(t3, t2) */ t1.score from t1, t2, t3, t4 where t1.id = t2.id and t2.id = t3.id and t3.id = t4.id) union all (select /*+ JOIN_SUFFIX(t3, t2) */ t1.score from t1, t2, t3, t4 where t1.id = t2.id and t2.id = t3.id and t3.id = t4.id) union all (select /*+ JOIN_SUFFIX(t3, t2) */ t1.score from t1, t2, t3, t4 where t1.id = t2.id and t2.id = t3.id and t3.id = t4.id) union all (select /*+ JOIN_SUFFIX(t3, t2) */ t1.score from t1, t2, t3, t4 where t1.id = t2.id and t2.id = t3.id and t3.id = t4.id) union all (select /*+ JOIN_SUFFIX(t3, t2) */ t1.score from t1, t2, t3, t4 where t1.id = t2.id and t2.id = t3.id and t3.id = t4.id) union all (select /*+ JOIN_SUFFIX(t3, t2) */ t1.score from t1, t2, t3, t4 where t1.id = t2.id and t2.id = t3.id and t3.id = t4.id) ) a; show warnings \G The result is *************************** 1. row *************************** Level: Note Code: 1003 Message: /* select#1 */ select /*+ JOIN_SUFFIX(@`select#2` `t3`,`t2`) JOIN_SUFFIX(@`select#3` `t3`,`t2`) JOIN_SUFFIX(@`select#4` `t3`,`t2`) JOIN_SUFFIX(@`select#5` `t3`,`t2`) JOIN_SUFFIX(@`select#6` `t3`,`t2`) JOIN_SUFFIX(@`select#7` `t3`,`t2`) JOIN_SUFFIX (@`select#8` `t3`,`t2`) JOIN_SUFFIX(@`select#9` `t3`,`t2`) JOIN_SUFFIX(@`select#a` `t3`,`t2`) JOIN_SUFFIX(@`select#b` `t3`,`t2`) JOIN_SUFFIX(@`select#c` `t3`,`t2`) JOIN_SUFFIX(@`select#d` `t3`,`t2`) */ count(0) AS `count(*)` from (/* select#2 */ select `db 1`.`t1`.`score` AS `score` from `db1`.`t1` join `db1`.`t2` join `db1`.`t3` join `db1`.`t4` where ((`db1`.`t4`.`id` = `db1`.`t1`. `id`) and (`db1`.`t3`.`id` = `db1`.`t1`.`id`) and (`db1`.`t2`.`id` = `db1`.`t1`.`id`)) union all /* select#3 */ select `db1`.`t1 `.`score` AS `score` from `db1`.`t1` join `db1`.`t2` join `db1`.`t3` join `db1`.`t4` where ((`db1`.`t4`.`id` = `db1`.`t1`.`id`) and (`db1`.`t3`.`id` = `db1`.`t1`.`id`) and (`db1`.`t2`.`id` = `db1`.`t1`.`id`)) union all /* select#4 */ select `db1`.`t1`.`sco re` AS `score` from `db1`.`t1` join `db1`.`t2` join `db1`.`t3` join `db1`.`t4` where ((`db1`.`t4`.`id` = `db1`.`t1`.`id`) and (` db1`.`t3`.`id` = `db1`.`t1`.`id`) and (`db1`.`t2`.`id` = `db1`.`t1`.`id`)) union all /* select#5 */ select `db1`.`t1`.`score` AS `score` from `db1`.`t1` join `db1`.`t2` join `db1`.`t3` join `db1`.`t4` where ((`db1`.`t4`.`id` = `db1`.`t1`.`id`) and (`db1`.` t3`.`id` = `db1`.`t1`.`id`) and (`db1`.`t2`.`id` = `db1`.`t1`.`id`)) union all /* select#6 */ select `db1`.`t1`.`score` AS `scor e` from `db1`.`t1` join `db1`.`t2` join `db1`.`t3` join `db1`.`t4` where ((`db1`.`t4`.`id` = `db1`.`t1`.`id`) and (`db1`.`t3`.`i d` = `db1`.`t1`.`id`) and (`db1`.`t2`.`id` = `db1`.`t1`.`id`)) union all /* select#7 */ select `db1`.`t1`.`score` AS `score` fro m `db1`.`t1` join `db1`.`t2` join `db1`.`t3` join `db1`.`t4` where ((`db1`.`t4`.`id` = `db1`.`t1`.`id`) and (`db1`.`t3`.`id` = ` db1`.`t1`.`id`) and (`db1`.`t2`.`id` = `db1`.`t1`.`id`)) union all /* select#8 */ select `db1`.`t1`.`score` AS `score` from `db1 `.`t1` join `db1`.`t2` join `db1`.`t3` join `db1`.`t4` where ((`db1`.`t4`.`id` = `db1`.`t1`.`id`) and (`db1`.`t3`.`id` = `db1`.` t1`.`id`) and (`db1`.`t2`.`id` = `db1`.`t1`.`id`)) union all /* select#9 */ select `db1`.`t1`.`score` AS `score` from `db1`.`t1` join `db1`.`t2` join `db1`.`t3` join `db1`.`t4` where ((`db1`.`t4`.`id` = `db1`.`t1`.`id`) and (`db1`.`t3`.`id` = `db1`.`t1`.`i d`) and (`db1`.`t2`.`id` = `db1`.`t1`.`id`)) union all /* select#10 */ select `db1`.`t1`.`score` AS `score` from `db1`.`t1` join `db1`.`t2` join `db1`.`t3` join `db1`.`t4` where ((`db1`.`t4`.`id` = `db1`.`t1`.`id`) and (`db1`.`t3`.`id` = `db1`.`t1`.`id`) a nd (`db1`.`t2`.`id` = `db1`.`t1`.`id`)) union all /* select#11 */ select `db1`.`t1`.`score` AS `score` from `db1`.`t1` join `db1 `.`t2` join `db1`.`t3` join `db1`.`t4` where ((`db1`.`t4`.`id` = `db1`.`t1`.`id`) and (`db1`.`t3`.`id` = `db1`.`t1`.`id`) and (` db1`.`t2`.`id` = `db1`.`t1`.`id`)) union all /* select#12 */ select `db1`.`t1`.`score` AS `score` from `db1`.`t1` join `db1`.`t2 ` join `db1`.`t3` join `db1`.`t4` where ((`db1`.`t4`.`id` = `db1`.`t1`.`id`) and (`db1`.`t3`.`id` = `db1`.`t1`.`id`) and (`db1`. `t2`.`id` = `db1`.`t1`.`id`)) union all /* select#13 */ select `db1`.`t1`.`score` AS `score` from `db1`.`t1` join `db1`.`t2` joi n `db1`.`t3` join `db1`.`t4` where ((`db1`.`t4`.`id` = `db1`.`t1`.`id`) and (`db1`.`t3`.`id` = `db1`.`t1`.`id`) and (`db1`.`t2`. `id` = `db1`.`t1`.`id`))) `a` Suggested fix: I suggest to use decimal in hints. The code to change is in opt_hints.cc:202 Opt_hints_qb::Opt_hints_qb(Opt_hints *opt_hints_arg, MEM_ROOT *mem_root_arg, uint select_number_arg) : Opt_hints(nullptr, opt_hints_arg, mem_root_arg), select_number(select_number_arg), subquery_hint(nullptr), semijoin_hint(nullptr), join_order_hints(mem_root_arg), join_order_hints_ignored(0) { sys_name.str = buff; sys_name.length = snprintf(buff, sizeof(buff), "%s%x", sys_qb_prefix.str, select_number); }