[MDEV-15357] Wrong result with join_cache_level=4, BNLH join Created: 2018-02-20  Updated: 2023-12-07

Status: Stalled
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: 10.2.12, 10.2.13, 10.2, 10.3
Fix Version/s: 10.3

Type: Bug Priority: Major
Reporter: NAMHEE KIM Assignee: Igor Babaev
Resolution: Unresolved Votes: 3
Labels: not-10.4+
Environment:

CentOS release 6.5 x86_64, MariaDB 10.2.12, 10.2.13

Attachments: File BNLH_Test.sql    
Sprint: 10.2.14

 Description   

the following query:

SELECT DISTINCT T3.ZEBWD_SYS_ID,
 
                T4.ZEBWD_SYS_IDT,
 
                T3.ZEBWD_SYS_AV
 
FROM            T_ZEBWDT1040 T1,   /* A001/A002 */
 
                T_ZEBWDT1050 T2,
 
                T_ZEBWDT1000 T3,
 
                T_ZEBWDT1000T T4
 
WHERE           T1.ZEBWD_ROLE_ID = T2.ZEBWD_ROLE_ID
 
AND             T2.ZEBWD_MENU_ID = T3.ZEBWD_SYS_ID
 
AND             T3.ZEBWD_SYS_DEL = 'N'
 
AND             T3.ZEBWD_SYS_UP  = '0000000000'
 
AND             T3.ZEBWD_SYS_ID  = T4.ZEBWD_SYS_ID
 
AND             T1.ZEBWD_USER_ID = 'test'
 
ORDER BY        T3.ZEBWD_SYS_AV ASC;

Test Case 1: SET SESSION join_cache_level=8;

+------+-------------+-------+--------+---------------+---------+---------+----------------------------+------+-----------------------------------------------------------+
 
| id   | select_type | table | type   | possible_keys | key     | key_len | ref                        | rows | Extra                                                     |
 
+------+-------------+-------+--------+---------------+---------+---------+----------------------------+------+-----------------------------------------------------------+
 
|    1 | SIMPLE      | T1    | index  | PRIMARY       | PRIMARY | 154     | NULL                       |    1 | Using where; Using index; Using temporary; Using filesort |
 
|    1 | SIMPLE      | T2    | ref    | PRIMARY       | PRIMARY | 62      | bnlh_test.T1.ZEBWD_ROLE_ID |    4 | Using index                                               |
 
|    1 | SIMPLE      | T3    | eq_ref | PRIMARY       | PRIMARY | 92      | bnlh_test.T2.ZEBWD_MENU_ID |    1 | Using where                                               |
 
|    1 | SIMPLE      | T4    | ref    | PRIMARY       | PRIMARY | 92      | bnlh_test.T2.ZEBWD_MENU_ID |    1 |                                                           |
 
+------+-------------+-------+--------+---------------+---------+---------+----------------------------+------+-----------------------------------------------------------+
 
4 rows in set (0.00 sec)
 
 
 
MariaDB [bnlh_test]> 
 
 
+--------------+---------------+--------------+
 
| ZEBWD_SYS_ID | ZEBWD_SYS_IDT | ZEBWD_SYS_AV |
 
+--------------+---------------+--------------+
 
| ZSBILL1000   | MENU1         | 1            |
 
| ZSBILL3000   | MENU3         | 2            |
 
| ZSBILL2000   | MENU2         | 3            |
 
| ZSBILL7000   | MENU7         | 4            |
 
| ZSBILL4000   | MENU4         | 5            |
 
| ZSBILL5000   | MENU5         | 6            |
 
| ZSBILL8000   | MENU8         | 7            |
 
| ZSBILL6000   | MENU6         | 8            |
 
| ZSBILL9000   | MENU9         | 9            |
 
+--------------+---------------+--------------+
 
9 rows in set (0.00 sec)

Test Case 2: SET SESSION join_cache_level=4;

+------+-------------+-------+------------+---------------+-----------------------+---------+----------------------------+------+-----------------------------------------------------------+
 
| id   | select_type | table | type       | possible_keys | key                   | key_len | ref                        | rows | Extra                                                     |
 
+------+-------------+-------+------------+---------------+-----------------------+---------+----------------------------+------+-----------------------------------------------------------+
 
|    1 | SIMPLE      | T1    | index      | PRIMARY       | PRIMARY               | 154     | NULL                       |    1 | Using where; Using index; Using temporary; Using filesort |
 
|    1 | SIMPLE      | T2    | hash_index | PRIMARY       | #hash#PRIMARY:PRIMARY | 62:154  | bnlh_test.T1.ZEBWD_ROLE_ID |    9 | Using index; Using join buffer (flat, BNLH join)          |
 
|    1 | SIMPLE      | T3    | hash_ALL   | PRIMARY       | #hash#PRIMARY         | 92      | bnlh_test.T2.ZEBWD_MENU_ID |   42 | Using where                                               |
 
|    1 | SIMPLE      | T4    | hash_ALL   | PRIMARY       | #hash#PRIMARY         | 92      | bnlh_test.T2.ZEBWD_MENU_ID |   42 |                                                           |
 
+------+-------------+-------+------------+---------------+-----------------------+---------+----------------------------+------+-----------------------------------------------------------+
 
4 rows in set (0.00 sec)
 
 
 
MariaDB [bnlh_test]> SELECT DISTINCT T3.ZEBWD_SYS_ID,
 
    ->                 T4.ZEBWD_SYS_IDT,
 
    ->                 T3.ZEBWD_SYS_AV
 
    -> FROM            T_ZEBWDT1040 T1,   /* A001/A002 */
 
    ->                 T_ZEBWDT1050 T2,
 
    ->                 T_ZEBWDT1000 T3,
 
    ->                 T_ZEBWDT1000T T4
 
    -> WHERE           T1.ZEBWD_ROLE_ID = T2.ZEBWD_ROLE_ID
 
    -> AND             T2.ZEBWD_MENU_ID = T3.ZEBWD_SYS_ID
 
    -> AND             T3.ZEBWD_SYS_DEL = 'N'
 
    -> AND             T3.ZEBWD_SYS_UP  = '0000000000'
 
    -> AND             T3.ZEBWD_SYS_ID  = T4.ZEBWD_SYS_ID
 
    -> AND             T1.ZEBWD_USER_ID = 'test'
 
    -> ORDER BY        T3.ZEBWD_SYS_AV ASC;
 
+--------------+---------------+--------------+
 
| ZEBWD_SYS_ID | ZEBWD_SYS_IDT | ZEBWD_SYS_AV |
 
+--------------+---------------+--------------+
 
| ZSBILL1000   | MENU4 - SUB1  | 1            |
 
| ZSBILL1000   | MENU3         | 1            |
 
| ZSBILL1000   | MENU9 - SUB5  | 1            |
 
| ZSBILL1000   | MENU1 - SUB3  | 1            |
 
| ZSBILL1000   | MENU9         | 1            |
 
| ZSBILL1000   | MENU6 - SUB3  | 1            |
 
| ZSBILL1000   | MENU5 - SUB3  | 1            |
 
| ZSBILL1000   | MENU4 - SUB8  | 1            |
 
| ZSBILL1000   | MENU4 - SUB3  | 1            |
 
| ZSBILL1000   | MENU3 - SUB2  | 1            |
 
| ZSBILL1000   | MENU2 - SUB1  | 1            |
 
| ZSBILL1000   | MENU9 - SUB2  | 1            |
 
| ZSBILL1000   | MENU1         | 1            |
 
| ZSBILL1000   | MENU6 - SUB5  | 1            |
 
| ZSBILL1000   | MENU6         | 1            |
 
| ZSBILL1000   | MENU5         | 1            |
 
| ZSBILL1000   | MENU4 - SUB5  | 1            |
 
| ZSBILL1000   | MENU4         | 1            |
 
| ZSBILL1000   | MENU2 - SUB3  | 1            |
 
| ZSBILL1000   | MENU9 - SUB4  | 1            |
 
| ZSBILL1000   | MENU1 - SUB2  | 1            |
 
| ZSBILL1000   | MENU8         | 1            |
 
| ZSBILL1000   | MENU6 - SUB2  | 1            |
 
| ZSBILL1000   | MENU5 - SUB2  | 1            |
 
| ZSBILL1000   | MENU4 - SUB7  | 1            |
 
| ZSBILL1000   | MENU4 - SUB2  | 1            |
 
| ZSBILL1000   | MENU3 - SUB1  | 1            |
 
| ZSBILL1000   | MENU9 - SUB6  | 1            |
 
| ZSBILL1000   | MENU2         | 1            |
 
| ZSBILL1000   | MENU9 - SUB1  | 1            |
 
| ZSBILL1000   | MENU6 - SUB4  | 1            |
 
| ZSBILL1000   | MENU5 - SUB4  | 1            |
 
| ZSBILL1000   | MENU4 - SUB9  | 1            |
 
| ZSBILL1000   | MENU4 - SUB4  | 1            |
 
| ZSBILL1000   | MENU3 - SUB3  | 1            |
 
| ZSBILL1000   | MENU2 - SUB2  | 1            |
 
| ZSBILL1000   | MENU9 - SUB3  | 1            |
 
| ZSBILL1000   | MENU1 - SUB1  | 1            |
 
| ZSBILL1000   | MENU7         | 1            |
 
| ZSBILL1000   | MENU6 - SUB1  | 1            |
 
| ZSBILL1000   | MENU5 - SUB1  | 1            |
 
| ZSBILL1000   | MENU4 - SUB6  | 1            |
 
| ZSBILL3000   | MENU5 - SUB3  | 2            |
 
| ZSBILL3000   | MENU4 - SUB8  | 2            |
 
| ZSBILL3000   | MENU4 - SUB3  | 2            |
 
| ZSBILL3000   | MENU3 - SUB2  | 2            |
 
| ZSBILL3000   | MENU2 - SUB1  | 2            |
 
| ZSBILL3000   | MENU9 - SUB2  | 2            |
 
| ZSBILL3000   | MENU1         | 2            |
 
| ZSBILL3000   | MENU6 - SUB5  | 2            |
 
| ZSBILL3000   | MENU6         | 2            |
 
| ZSBILL3000   | MENU5         | 2            |
 
| ZSBILL3000   | MENU4 - SUB5  | 2            |
 
| ZSBILL3000   | MENU4         | 2            |
 
| ZSBILL3000   | MENU2 - SUB3  | 2            |
 
| ZSBILL3000   | MENU9 - SUB4  | 2            |
 
| ZSBILL3000   | MENU1 - SUB2  | 2            |
 
| ZSBILL3000   | MENU8         | 2            |
 
| ZSBILL3000   | MENU6 - SUB2  | 2            |
 
| ZSBILL3000   | MENU5 - SUB2  | 2            |
 
| ZSBILL3000   | MENU4 - SUB7  | 2            |
 
| ZSBILL3000   | MENU4 - SUB2  | 2            |
 
| ZSBILL3000   | MENU3 - SUB1  | 2            |
 
| ZSBILL3000   | MENU9 - SUB6  | 2            |
 
| ZSBILL3000   | MENU2         | 2            |
 
| ZSBILL3000   | MENU9 - SUB1  | 2            |
 
| ZSBILL3000   | MENU6 - SUB4  | 2            |
 
| ZSBILL3000   | MENU5 - SUB4  | 2            |
 
| ZSBILL3000   | MENU4 - SUB9  | 2            |
 
| ZSBILL3000   | MENU4 - SUB4  | 2            |
 
| ZSBILL3000   | MENU3 - SUB3  | 2            |
 
| ZSBILL3000   | MENU2 - SUB2  | 2            |
 
| ZSBILL3000   | MENU9 - SUB3  | 2            |
 
| ZSBILL3000   | MENU1 - SUB1  | 2            |
 
| ZSBILL3000   | MENU7         | 2            |
 
| ZSBILL3000   | MENU6 - SUB1  | 2            |
 
| ZSBILL3000   | MENU5 - SUB1  | 2            |
 
| ZSBILL3000   | MENU4 - SUB6  | 2            |
 
| ZSBILL3000   | MENU4 - SUB1  | 2            |
 
| ZSBILL3000   | MENU3         | 2            |
 
| ZSBILL3000   | MENU9 - SUB5  | 2            |
 
| ZSBILL3000   | MENU1 - SUB3  | 2            |
 
| ZSBILL3000   | MENU9         | 2            |
 
| ZSBILL3000   | MENU6 - SUB3  | 2            |
 
| ZSBILL2000   | MENU1 - SUB3  | 3            |
 
| ZSBILL2000   | MENU9         | 3            |
 
| ZSBILL2000   | MENU6 - SUB3  | 3            |
 
| ZSBILL2000   | MENU5 - SUB3  | 3            |
 
| ZSBILL2000   | MENU4 - SUB8  | 3            |
 
| ZSBILL2000   | MENU4 - SUB3  | 3            |
 
| ZSBILL2000   | MENU3 - SUB2  | 3            |
 
| ZSBILL2000   | MENU2 - SUB1  | 3            |
 
| ZSBILL2000   | MENU9 - SUB2  | 3            |
 
| ZSBILL2000   | MENU1         | 3            |
 
| ZSBILL2000   | MENU6 - SUB5  | 3            |
 
| ZSBILL2000   | MENU6         | 3            |
 
| ZSBILL2000   | MENU5         | 3            |
 
| ZSBILL2000   | MENU4 - SUB5  | 3            |
 
| ZSBILL2000   | MENU4         | 3            |
 
| ZSBILL2000   | MENU2 - SUB3  | 3            |
 
| ZSBILL2000   | MENU9 - SUB4  | 3            |
 
| ZSBILL2000   | MENU1 - SUB2  | 3            |
 
| ZSBILL2000   | MENU8         | 3            |
 
| ZSBILL2000   | MENU6 - SUB2  | 3            |
 
| ZSBILL2000   | MENU5 - SUB2  | 3            |
 
| ZSBILL2000   | MENU4 - SUB7  | 3            |
 
| ZSBILL2000   | MENU4 - SUB2  | 3            |
 
| ZSBILL2000   | MENU3 - SUB1  | 3            |
 
| ZSBILL2000   | MENU9 - SUB6  | 3            |
 
| ZSBILL2000   | MENU2         | 3            |
 
| ZSBILL2000   | MENU9 - SUB1  | 3            |
 
| ZSBILL2000   | MENU6 - SUB4  | 3            |
 
| ZSBILL2000   | MENU5 - SUB4  | 3            |
 
| ZSBILL2000   | MENU4 - SUB9  | 3            |
 
| ZSBILL2000   | MENU4 - SUB4  | 3            |
 
| ZSBILL2000   | MENU3 - SUB3  | 3            |
 
| ZSBILL2000   | MENU2 - SUB2  | 3            |
 
| ZSBILL2000   | MENU9 - SUB3  | 3            |
 
| ZSBILL2000   | MENU1 - SUB1  | 3            |
 
| ZSBILL2000   | MENU7         | 3            |
 
| ZSBILL2000   | MENU6 - SUB1  | 3            |
 
| ZSBILL2000   | MENU5 - SUB1  | 3            |
 
| ZSBILL2000   | MENU4 - SUB6  | 3            |
 
| ZSBILL2000   | MENU4 - SUB1  | 3            |
 
| ZSBILL2000   | MENU3         | 3            |
 
| ZSBILL2000   | MENU9 - SUB5  | 3            |
 
| ZSBILL7000   | MENU4 - SUB5  | 4            |
 
| ZSBILL7000   | MENU4         | 4            |
 
| ZSBILL7000   | MENU2 - SUB3  | 4            |
 
| ZSBILL7000   | MENU9 - SUB4  | 4            |
 
| ZSBILL7000   | MENU1 - SUB2  | 4            |
 
| ZSBILL7000   | MENU8         | 4            |
 
| ZSBILL7000   | MENU6 - SUB2  | 4            |
 
| ZSBILL7000   | MENU5 - SUB2  | 4            |
 
| ZSBILL7000   | MENU4 - SUB7  | 4            |
 
| ZSBILL7000   | MENU4 - SUB2  | 4            |
 
| ZSBILL7000   | MENU3 - SUB1  | 4            |
 
| ZSBILL7000   | MENU9 - SUB6  | 4            |
 
| ZSBILL7000   | MENU2         | 4            |
 
| ZSBILL7000   | MENU9 - SUB1  | 4            |
 
| ZSBILL7000   | MENU6 - SUB4  | 4            |
 
| ZSBILL7000   | MENU5 - SUB4  | 4            |
 
| ZSBILL7000   | MENU4 - SUB9  | 4            |
 
| ZSBILL7000   | MENU4 - SUB4  | 4            |
 
| ZSBILL7000   | MENU3 - SUB3  | 4            |
 
| ZSBILL7000   | MENU2 - SUB2  | 4            |
 
| ZSBILL7000   | MENU9 - SUB3  | 4            |
 
| ZSBILL7000   | MENU1 - SUB1  | 4            |
 
| ZSBILL7000   | MENU7         | 4            |
 
| ZSBILL7000   | MENU6 - SUB1  | 4            |
 
| ZSBILL7000   | MENU5 - SUB1  | 4            |
 
| ZSBILL7000   | MENU4 - SUB6  | 4            |
 
| ZSBILL7000   | MENU4 - SUB1  | 4            |
 
| ZSBILL7000   | MENU3         | 4            |
 
| ZSBILL7000   | MENU9 - SUB5  | 4            |
 
| ZSBILL7000   | MENU1 - SUB3  | 4            |
 
| ZSBILL7000   | MENU9         | 4            |
 
| ZSBILL7000   | MENU6 - SUB3  | 4            |
 
| ZSBILL7000   | MENU5 - SUB3  | 4            |
 
| ZSBILL7000   | MENU4 - SUB8  | 4            |
 
| ZSBILL7000   | MENU4 - SUB3  | 4            |
 
| ZSBILL7000   | MENU3 - SUB2  | 4            |
 
| ZSBILL7000   | MENU2 - SUB1  | 4            |
 
| ZSBILL7000   | MENU9 - SUB2  | 4            |
 
| ZSBILL7000   | MENU1         | 4            |
 
| ZSBILL7000   | MENU6 - SUB5  | 4            |
 
| ZSBILL7000   | MENU6         | 4            |
 
| ZSBILL7000   | MENU5         | 4            |
 
| ZSBILL4000   | MENU4 - SUB3  | 5            |
 
| ZSBILL4000   | MENU3 - SUB2  | 5            |
 
| ZSBILL4000   | MENU2 - SUB1  | 5            |
 
| ZSBILL4000   | MENU9 - SUB2  | 5            |
 
| ZSBILL4000   | MENU1         | 5            |
 
| ZSBILL4000   | MENU6 - SUB5  | 5            |
 
| ZSBILL4000   | MENU6         | 5            |
 
| ZSBILL4000   | MENU5         | 5            |
 
| ZSBILL4000   | MENU4 - SUB5  | 5            |
 
| ZSBILL4000   | MENU4         | 5            |
 
| ZSBILL4000   | MENU2 - SUB3  | 5            |
 
| ZSBILL4000   | MENU9 - SUB4  | 5            |
 
| ZSBILL4000   | MENU1 - SUB2  | 5            |
 
| ZSBILL4000   | MENU8         | 5            |
 
| ZSBILL4000   | MENU6 - SUB2  | 5            |
 
| ZSBILL4000   | MENU5 - SUB2  | 5            |
 
| ZSBILL4000   | MENU4 - SUB7  | 5            |
 
| ZSBILL4000   | MENU4 - SUB2  | 5            |
 
| ZSBILL4000   | MENU3 - SUB1  | 5            |
 
| ZSBILL4000   | MENU9 - SUB6  | 5            |
 
| ZSBILL4000   | MENU2         | 5            |
 
| ZSBILL4000   | MENU9 - SUB1  | 5            |
 
| ZSBILL4000   | MENU6 - SUB4  | 5            |
 
| ZSBILL4000   | MENU5 - SUB4  | 5            |
 
| ZSBILL4000   | MENU4 - SUB9  | 5            |
 
| ZSBILL4000   | MENU4 - SUB4  | 5            |
 
| ZSBILL4000   | MENU3 - SUB3  | 5            |
 
| ZSBILL4000   | MENU2 - SUB2  | 5            |
 
| ZSBILL4000   | MENU9 - SUB3  | 5            |
 
| ZSBILL4000   | MENU1 - SUB1  | 5            |
 
| ZSBILL4000   | MENU7         | 5            |
 
| ZSBILL4000   | MENU6 - SUB1  | 5            |
 
| ZSBILL4000   | MENU5 - SUB1  | 5            |
 
| ZSBILL4000   | MENU4 - SUB6  | 5            |
 
| ZSBILL4000   | MENU4 - SUB1  | 5            |
 
| ZSBILL4000   | MENU3         | 5            |
 
| ZSBILL4000   | MENU9 - SUB5  | 5            |
 
| ZSBILL4000   | MENU1 - SUB3  | 5            |
 
| ZSBILL4000   | MENU9         | 5            |
 
| ZSBILL4000   | MENU6 - SUB3  | 5            |
 
| ZSBILL4000   | MENU5 - SUB3  | 5            |
 
| ZSBILL4000   | MENU4 - SUB8  | 5            |
 
| ZSBILL5000   | MENU2 - SUB1  | 6            |
 
| ZSBILL5000   | MENU9 - SUB2  | 6            |
 
| ZSBILL5000   | MENU1         | 6            |
 
| ZSBILL5000   | MENU6 - SUB5  | 6            |
 
| ZSBILL5000   | MENU6         | 6            |
 
| ZSBILL5000   | MENU5         | 6            |
 
| ZSBILL5000   | MENU4 - SUB5  | 6            |
 
| ZSBILL5000   | MENU4         | 6            |
 
| ZSBILL5000   | MENU2 - SUB3  | 6            |
 
| ZSBILL5000   | MENU9 - SUB4  | 6            |
 
| ZSBILL5000   | MENU1 - SUB2  | 6            |
 
| ZSBILL5000   | MENU8         | 6            |
 
| ZSBILL5000   | MENU6 - SUB2  | 6            |
 
| ZSBILL5000   | MENU5 - SUB2  | 6            |
 
| ZSBILL5000   | MENU4 - SUB7  | 6            |
 
| ZSBILL5000   | MENU4 - SUB2  | 6            |
 
| ZSBILL5000   | MENU3 - SUB1  | 6            |
 
| ZSBILL5000   | MENU9 - SUB6  | 6            |
 
| ZSBILL5000   | MENU2         | 6            |
 
| ZSBILL5000   | MENU9 - SUB1  | 6            |
 
| ZSBILL5000   | MENU6 - SUB4  | 6            |
 
| ZSBILL5000   | MENU5 - SUB4  | 6            |
 
| ZSBILL5000   | MENU4 - SUB9  | 6            |
 
| ZSBILL5000   | MENU4 - SUB4  | 6            |
 
| ZSBILL5000   | MENU3 - SUB3  | 6            |
 
| ZSBILL5000   | MENU2 - SUB2  | 6            |
 
| ZSBILL5000   | MENU9 - SUB3  | 6            |
 
| ZSBILL5000   | MENU1 - SUB1  | 6            |
 
| ZSBILL5000   | MENU7         | 6            |
 
| ZSBILL5000   | MENU6 - SUB1  | 6            |
 
| ZSBILL5000   | MENU5 - SUB1  | 6            |
 
| ZSBILL5000   | MENU4 - SUB6  | 6            |
 
| ZSBILL5000   | MENU4 - SUB1  | 6            |
 
| ZSBILL5000   | MENU3         | 6            |
 
| ZSBILL5000   | MENU9 - SUB5  | 6            |
 
| ZSBILL5000   | MENU1 - SUB3  | 6            |
 
| ZSBILL5000   | MENU9         | 6            |
 
| ZSBILL5000   | MENU6 - SUB3  | 6            |
 
| ZSBILL5000   | MENU5 - SUB3  | 6            |
 
| ZSBILL5000   | MENU4 - SUB8  | 6            |
 
| ZSBILL5000   | MENU4 - SUB3  | 6            |
 
| ZSBILL5000   | MENU3 - SUB2  | 6            |
 
| ZSBILL8000   | MENU9 - SUB4  | 7            |
 
| ZSBILL8000   | MENU2 - SUB3  | 7            |
 
| ZSBILL8000   | MENU1 - SUB2  | 7            |
 
| ZSBILL8000   | MENU8         | 7            |
 
| ZSBILL8000   | MENU6 - SUB2  | 7            |
 
| ZSBILL8000   | MENU5 - SUB2  | 7            |
 
| ZSBILL8000   | MENU4 - SUB7  | 7            |
 
| ZSBILL8000   | MENU4 - SUB2  | 7            |
 
| ZSBILL8000   | MENU9 - SUB6  | 7            |
 
| ZSBILL8000   | MENU3 - SUB1  | 7            |
 
| ZSBILL8000   | MENU9 - SUB1  | 7            |
 
| ZSBILL8000   | MENU2         | 7            |
 
| ZSBILL8000   | MENU6 - SUB4  | 7            |
 
| ZSBILL8000   | MENU5 - SUB4  | 7            |
 
| ZSBILL8000   | MENU4 - SUB9  | 7            |
 
| ZSBILL8000   | MENU4 - SUB4  | 7            |
 
| ZSBILL8000   | MENU3 - SUB3  | 7            |
 
| ZSBILL8000   | MENU9 - SUB3  | 7            |
 
| ZSBILL8000   | MENU2 - SUB2  | 7            |
 
| ZSBILL8000   | MENU1 - SUB1  | 7            |
 
| ZSBILL8000   | MENU7         | 7            |
 
| ZSBILL8000   | MENU6 - SUB1  | 7            |
 
| ZSBILL8000   | MENU5 - SUB1  | 7            |
 
| ZSBILL8000   | MENU4 - SUB6  | 7            |
 
| ZSBILL8000   | MENU4 - SUB1  | 7            |
 
| ZSBILL8000   | MENU9 - SUB5  | 7            |
 
| ZSBILL8000   | MENU3         | 7            |
 
| ZSBILL8000   | MENU1 - SUB3  | 7            |
 
| ZSBILL8000   | MENU9         | 7            |
 
| ZSBILL8000   | MENU6 - SUB3  | 7            |
 
| ZSBILL8000   | MENU5 - SUB3  | 7            |
 
| ZSBILL8000   | MENU4 - SUB8  | 7            |
 
| ZSBILL8000   | MENU4 - SUB3  | 7            |
 
| ZSBILL8000   | MENU3 - SUB2  | 7            |
 
| ZSBILL8000   | MENU9 - SUB2  | 7            |
 
| ZSBILL8000   | MENU2 - SUB1  | 7            |
 
| ZSBILL8000   | MENU1         | 7            |
 
| ZSBILL8000   | MENU6 - SUB5  | 7            |
 
| ZSBILL8000   | MENU6         | 7            |
 
| ZSBILL8000   | MENU5         | 7            |
 
| ZSBILL8000   | MENU4 - SUB5  | 7            |
 
| ZSBILL8000   | MENU4         | 7            |
 
| ZSBILL6000   | MENU6         | 8            |
 
| ZSBILL6000   | MENU5         | 8            |
 
| ZSBILL6000   | MENU4 - SUB5  | 8            |
 
| ZSBILL6000   | MENU4         | 8            |
 
| ZSBILL6000   | MENU9 - SUB4  | 8            |
 
| ZSBILL6000   | MENU2 - SUB3  | 8            |
 
| ZSBILL6000   | MENU8         | 8            |
 
| ZSBILL6000   | MENU1 - SUB2  | 8            |
 
| ZSBILL6000   | MENU6 - SUB2  | 8            |
 
| ZSBILL6000   | MENU5 - SUB2  | 8            |
 
| ZSBILL6000   | MENU4 - SUB7  | 8            |
 
| ZSBILL6000   | MENU4 - SUB2  | 8            |
 
| ZSBILL6000   | MENU9 - SUB6  | 8            |
 
| ZSBILL6000   | MENU3 - SUB1  | 8            |
 
| ZSBILL6000   | MENU9 - SUB1  | 8            |
 
| ZSBILL6000   | MENU2         | 8            |
 
| ZSBILL6000   | MENU6 - SUB4  | 8            |
 
| ZSBILL6000   | MENU5 - SUB4  | 8            |
 
| ZSBILL6000   | MENU4 - SUB9  | 8            |
 
| ZSBILL6000   | MENU4 - SUB4  | 8            |
 
| ZSBILL6000   | MENU3 - SUB3  | 8            |
 
| ZSBILL6000   | MENU9 - SUB3  | 8            |
 
| ZSBILL6000   | MENU2 - SUB2  | 8            |
 
| ZSBILL6000   | MENU1 - SUB1  | 8            |
 
| ZSBILL6000   | MENU7         | 8            |
 
| ZSBILL6000   | MENU6 - SUB1  | 8            |
 
| ZSBILL6000   | MENU5 - SUB1  | 8            |
 
| ZSBILL6000   | MENU4 - SUB6  | 8            |
 
| ZSBILL6000   | MENU4 - SUB1  | 8            |
 
| ZSBILL6000   | MENU9 - SUB5  | 8            |
 
| ZSBILL6000   | MENU3         | 8            |
 
| ZSBILL6000   | MENU9         | 8            |
 
| ZSBILL6000   | MENU1 - SUB3  | 8            |
 
| ZSBILL6000   | MENU6 - SUB3  | 8            |
 
| ZSBILL6000   | MENU5 - SUB3  | 8            |
 
| ZSBILL6000   | MENU4 - SUB8  | 8            |
 
| ZSBILL6000   | MENU4 - SUB3  | 8            |
 
| ZSBILL6000   | MENU3 - SUB2  | 8            |
 
| ZSBILL6000   | MENU9 - SUB2  | 8            |
 
| ZSBILL6000   | MENU2 - SUB1  | 8            |
 
| ZSBILL6000   | MENU1         | 8            |
 
| ZSBILL6000   | MENU6 - SUB5  | 8            |
 
| ZSBILL9000   | MENU6 - SUB2  | 9            |
 
| ZSBILL9000   | MENU5 - SUB2  | 9            |
 
| ZSBILL9000   | MENU4 - SUB7  | 9            |
 
| ZSBILL9000   | MENU4 - SUB2  | 9            |
 
| ZSBILL9000   | MENU3 - SUB1  | 9            |
 
| ZSBILL9000   | MENU9 - SUB6  | 9            |
 
| ZSBILL9000   | MENU2         | 9            |
 
| ZSBILL9000   | MENU9 - SUB1  | 9            |
 
| ZSBILL9000   | MENU6 - SUB4  | 9            |
 
| ZSBILL9000   | MENU5 - SUB4  | 9            |
 
| ZSBILL9000   | MENU4 - SUB9  | 9            |
 
| ZSBILL9000   | MENU4 - SUB4  | 9            |
 
| ZSBILL9000   | MENU3 - SUB3  | 9            |
 
| ZSBILL9000   | MENU2 - SUB2  | 9            |
 
| ZSBILL9000   | MENU9 - SUB3  | 9            |
 
| ZSBILL9000   | MENU1 - SUB1  | 9            |
 
| ZSBILL9000   | MENU7         | 9            |
 
| ZSBILL9000   | MENU6 - SUB1  | 9            |
 
| ZSBILL9000   | MENU5 - SUB1  | 9            |
 
| ZSBILL9000   | MENU4 - SUB6  | 9            |
 
| ZSBILL9000   | MENU4 - SUB1  | 9            |
 
| ZSBILL9000   | MENU3         | 9            |
 
| ZSBILL9000   | MENU9 - SUB5  | 9            |
 
| ZSBILL9000   | MENU1 - SUB3  | 9            |
 
| ZSBILL9000   | MENU9         | 9            |
 
| ZSBILL9000   | MENU6 - SUB3  | 9            |
 
| ZSBILL9000   | MENU5 - SUB3  | 9            |
 
| ZSBILL9000   | MENU4 - SUB8  | 9            |
 
| ZSBILL9000   | MENU4 - SUB3  | 9            |
 
| ZSBILL9000   | MENU3 - SUB2  | 9            |
 
| ZSBILL9000   | MENU2 - SUB1  | 9            |
 
| ZSBILL9000   | MENU9 - SUB2  | 9            |
 
| ZSBILL9000   | MENU1         | 9            |
 
| ZSBILL9000   | MENU6 - SUB5  | 9            |
 
| ZSBILL9000   | MENU6         | 9            |
 
| ZSBILL9000   | MENU5         | 9            |
 
| ZSBILL9000   | MENU4 - SUB5  | 9            |
 
| ZSBILL9000   | MENU4         | 9            |
 
| ZSBILL9000   | MENU2 - SUB3  | 9            |
 
| ZSBILL9000   | MENU9 - SUB4  | 9            |
 
| ZSBILL9000   | MENU1 - SUB2  | 9            |
 
| ZSBILL9000   | MENU8         | 9            |
 
+--------------+---------------+--------------+
 
378 rows in set (0.01 sec)



 Comments   
Comment by Alice Sherepa [ 2018-02-20 ]

Please add your .cnf file(s). I tried to reproduce the issue on 10.2.13, Ubuntu 16.04, and 10.2.12, Centos 6.8, but got the same results in both cases(with join_cache_level=4 and 8)

Comment by NAMHEE KIM [ 2018-02-20 ]
When I tested it, I got a problem with join_buffer_size.

Thanks.

[my.cnf]
 
 
 
[client]
 
port            = 3306 
socket          = /var/lib/mysql/mysql.sock
 
 
 
[mysqld_safe]
 
pid-file        = /var/lib/mysql/mysql.pid
 
socket          = /var/lib/mysql/mysql.sock
 
nice            = 0
 
log_error       = /data/mariadb/log/mysqld.log
 
basedir         = /usr/
 
datadir         = /data/mariadb/mysql
 
 
 
[mysqld]
 
#
 
# * Basic Settings
 
# 
user            = mysql
 
pid-file        = /var/lib/mysql/mysql.pid
 
socket          = /var/lib/mysql/mysql.sock
 
port            = 3306
 
basedir         = /usr
 
datadir         = /data/mariadb/mysql
 
tmpdir          = /tmp
 
lc_messages_dir = /usr/share/mysql
 
lc_messages     = en_US
 
skip-external-locking
 
lower_case_table_names = 1
 
general_log_file        = /data/mariadb/log/mysql.log
 
general_log             = 1
 
 
 
log_warnings            = 2 
log_error               = /data/mariadb/log/mariadb-error.log
 
# 
# Enable the slow query log to see queries with especially long duration
 
slow_query_log          = OFF
 
slow_query_log_file     = /data/mariadb/log/mariadb-slow.log
 
long_query_time         = 0.5
 
#log_slow_rate_limit    = 1000
 
log_slow_verbosity      = query_plan
 
 
 
innodb_log_file_size    = 100M 
innodb_log_files_in_group = 4
 
innodb_buffer_pool_size = 300M
 
innodb_log_buffer_size  = 32M 
innodb_file_per_table   = 1 
innodb_data_file_path   = ibdata1:100M:autoextend 
innodb_open_files       = 4096
 
innodb_io_capacity      = 800
 
innodb_flush_method     = O_DIRECT 
innodb_write_io_threads = 8
 
innodb_read_io_threads  = 8
 
innodb_doublewrite      = 1
 
innodb_thread_concurrency = 64
 
innodb_stats_on_metadata  = 0
 
 
 
# CHARACTER SET
 
collation_server = utf8_unicode_ci
 
character_set_server = utf8
 
 
 
join_cache_level               = 4         
join_buffer_size               = 32M
 
join_buffer_space_limit        = 32M
 
 
 
# FULLTEXT                                                                                 
innodb_ft_min_token_size=2
 
ft_min_word_len=2
 
 
 
[galera]
 
wsrep_on=OFF
 
wsrep_provider=/usr/lib64/galera/libgalera_smm.so
 
# wsrep_cluster_address='gcomm://'
 
binlog_format=row
 
default_storage_engine=InnoDB 
innodb_autoinc_lock_mode=2
 
innodb_flush_log_at_trx_commit = 2
 
bind-address=0.0.0.0
 
 
 
[mysqldump]
 
quick
 
quote-names
 
max_allowed_packet      = 16M 
 
 
[mysql]
 
#no-auto-rehash # faster start of mysql but no tab completion     
socket=/var/lib/mysql/mysql.sock
 
# default_character_set = utf8
 
 
 
[isamchk]
 
key_buffer              = 16M
 
 
 
#
 
# This group is read both both by the client and the server
 
# use it for options that affect everything
 
#
 
[client-server]
 
 
 
#
 
# include all files from the config directory
 
#
 
!includedir /etc/my.cnf.d

Comment by Alice Sherepa [ 2018-02-20 ]

Thanks for the report!
Reproducible on MariaDB 10.2-10.3, with Innodb and MyIsam.
testcase:

 
 
SET SESSION join_buffer_size=33554432;
 
 
 
CREATE TABLE t1 (id varchar(30) NOT NULL, PRIMARY KEY (id)) ENGINE=InnoDB;
 
INSERT  INTO t1 VALUES
 
('ZSBILL1000'),('ZSBILL1010'),('ZSBILL1020'),('ZSBILL1030'),('ZSBILL2000'),('ZSBILL2010'),('ZSBILL2020'),('ZSBILL6040'),('ZSBILL6050'),('ZSBILL7000');
 
 
 
CREATE TABLE t2 (id varchar(30) NOT NULL, a char(1) NOT NULL DEFAULT '3', PRIMARY KEY (id,a)) ENGINE=InnoDB ;
 
INSERT  INTO t2(id) VALUES
 
('ZSBILL1000'),('ZSBILL1010'),('ZSBILL1020'),('ZSBILL1030'),('ZSBILL2000'),('ZSBILL2010'),('ZSBILL2020'),('ZSBILL2030'),('ZSBILL3000');
 
 
 
SET SESSION join_cache_level=8;
 
SELECT DISTINCT t1.id, t2.id FROM t1,t2 WHERE t1.id = t2.id;
 
 
 
SET SESSION join_cache_level=4; ### or 3
 
SELECT DISTINCT t1.id, t2.id FROM t1,t2 WHERE t1.id = t2.id;


MariaDB [test]> SELECT DISTINCT t1.id, t2.id FROM t1,t2 WHERE t1.id = t2.id;
 
+------------+------------+
 
| id         | id         |
 
+------------+------------+
 
| ZSBILL1000 | ZSBILL1000 |
 
| ZSBILL1010 | ZSBILL1010 |
 
| ZSBILL1020 | ZSBILL1020 |
 
| ZSBILL1030 | ZSBILL1030 |
 
| ZSBILL2000 | ZSBILL2000 |
 
| ZSBILL2010 | ZSBILL2010 |
 
| ZSBILL2020 | ZSBILL2020 |
 
+------------+------------+
 
7 rows in set (0.00 sec)
 
 
 
MariaDB [test]> 
MariaDB [test]> SET SESSION join_cache_level=4;
 
Query OK, 0 rows affected (0.00 sec)
 
 
 
MariaDB [test]> SELECT DISTINCT t1.id, t2.id FROM t1,t2 WHERE t1.id = t2.id;
 
+------------+------------+
 
| id         | id         |
 
+------------+------------+
 
| ZSBILL1000 | ZSBILL1000 |
 
| ZSBILL1010 | ZSBILL1000 |
 
| ZSBILL1020 | ZSBILL1000 |
 
| ZSBILL1030 | ZSBILL1000 |
 
| ZSBILL2000 | ZSBILL1000 |
 
| ZSBILL2010 | ZSBILL1000 |
 
| ZSBILL2020 | ZSBILL1000 |
 
| ZSBILL6040 | ZSBILL1000 |
 
| ZSBILL6050 | ZSBILL1000 |
 
| ZSBILL7000 | ZSBILL1000 |
 
| ZSBILL1000 | ZSBILL1010 |
 
| ZSBILL1010 | ZSBILL1010 |
 
| ZSBILL1020 | ZSBILL1010 |
 
| ZSBILL1030 | ZSBILL1010 |
 
| ZSBILL2000 | ZSBILL1010 |
 
| ZSBILL2010 | ZSBILL1010 |
 
| ZSBILL2020 | ZSBILL1010 |
 
| ZSBILL6040 | ZSBILL1010 |
 
| ZSBILL6050 | ZSBILL1010 |
 
| ZSBILL7000 | ZSBILL1010 |
 
| ZSBILL1000 | ZSBILL1020 |
 
| ZSBILL1010 | ZSBILL1020 |
 
| ZSBILL1020 | ZSBILL1020 |
 
| ZSBILL1030 | ZSBILL1020 |
 
| ZSBILL2000 | ZSBILL1020 |
 
| ZSBILL2010 | ZSBILL1020 |
 
| ZSBILL2020 | ZSBILL1020 |
 
| ZSBILL6040 | ZSBILL1020 |
 
| ZSBILL6050 | ZSBILL1020 |
 
| ZSBILL7000 | ZSBILL1020 |
 
| ZSBILL1000 | ZSBILL1030 |
 
| ZSBILL1010 | ZSBILL1030 |
 
| ZSBILL1020 | ZSBILL1030 |
 
| ZSBILL1030 | ZSBILL1030 |
 
| ZSBILL2000 | ZSBILL1030 |
 
| ZSBILL2010 | ZSBILL1030 |
 
| ZSBILL2020 | ZSBILL1030 |
 
| ZSBILL6040 | ZSBILL1030 |
 
| ZSBILL6050 | ZSBILL1030 |
 
| ZSBILL7000 | ZSBILL1030 |
 
| ZSBILL1000 | ZSBILL2000 |
 
| ZSBILL1010 | ZSBILL2000 |
 
| ZSBILL1020 | ZSBILL2000 |
 
| ZSBILL1030 | ZSBILL2000 |
 
| ZSBILL2000 | ZSBILL2000 |
 
| ZSBILL2010 | ZSBILL2000 |
 
| ZSBILL2020 | ZSBILL2000 |
 
| ZSBILL6040 | ZSBILL2000 |
 
| ZSBILL6050 | ZSBILL2000 |
 
| ZSBILL7000 | ZSBILL2000 |
 
| ZSBILL1000 | ZSBILL2010 |
 
| ZSBILL1010 | ZSBILL2010 |
 
| ZSBILL1020 | ZSBILL2010 |
 
| ZSBILL1030 | ZSBILL2010 |
 
| ZSBILL2000 | ZSBILL2010 |
 
| ZSBILL2010 | ZSBILL2010 |
 
| ZSBILL2020 | ZSBILL2010 |
 
| ZSBILL6040 | ZSBILL2010 |
 
| ZSBILL6050 | ZSBILL2010 |
 
| ZSBILL7000 | ZSBILL2010 |
 
| ZSBILL1000 | ZSBILL2020 |
 
| ZSBILL1010 | ZSBILL2020 |
 
| ZSBILL1020 | ZSBILL2020 |
 
| ZSBILL1030 | ZSBILL2020 |
 
| ZSBILL2000 | ZSBILL2020 |
 
| ZSBILL2010 | ZSBILL2020 |
 
| ZSBILL2020 | ZSBILL2020 |
 
| ZSBILL6040 | ZSBILL2020 |
 
| ZSBILL6050 | ZSBILL2020 |
 
| ZSBILL7000 | ZSBILL2020 |
 
| ZSBILL1000 | ZSBILL2030 |
 
| ZSBILL1010 | ZSBILL2030 |
 
| ZSBILL1020 | ZSBILL2030 |
 
| ZSBILL1030 | ZSBILL2030 |
 
| ZSBILL2000 | ZSBILL2030 |
 
| ZSBILL2010 | ZSBILL2030 |
 
| ZSBILL2020 | ZSBILL2030 |
 
| ZSBILL6040 | ZSBILL2030 |
 
| ZSBILL6050 | ZSBILL2030 |
 
| ZSBILL7000 | ZSBILL2030 |
 
| ZSBILL1000 | ZSBILL3000 |
 
| ZSBILL1010 | ZSBILL3000 |
 
| ZSBILL1020 | ZSBILL3000 |
 
| ZSBILL1030 | ZSBILL3000 |
 
| ZSBILL2000 | ZSBILL3000 |
 
| ZSBILL2010 | ZSBILL3000 |
 
| ZSBILL2020 | ZSBILL3000 |
 
| ZSBILL6040 | ZSBILL3000 |
 
| ZSBILL6050 | ZSBILL3000 |
 
| ZSBILL7000 | ZSBILL3000 |
 
+------------+------------+
 
90 rows in set (0.00 sec)

Comment by Igor Babaev [ 2018-04-04 ]

We have the same results with the query

SELECT t1.id, t2.id FROM t1,t2 WHERE t1.id = t2.id;

as with

SELECT DISTINCT t1.id, t2.id FROM t1,t2 WHERE t1.id = t2.id;

So DISTINCT does not matter.
Wrong result set we get only with a big buffer and join_cache_level=4.
With join_buffer_size=2097279 we still have the right result set, but with join_buffer_size=2097280 and bigger we get the result of the cross product of t1 and t2.

Comment by Alice Sherepa [ 2022-12-01 ]

currently 10.4+ returns correct results, but the bug repeatable on 10.3 ( 4e9206736c403206915c09d )

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