DISTINCT in subquery is optimized away

I am facing issue with queries having subquery which takes excessive time to execute. That server has 1 UM and 3 PM architecture. When I will try to run same query with my OLD server environment with infinidb that is far far better than mariadb columnstore. Please help me to find out correct way. I have added all detail logs below. One more thing I need to add With 3 PM I have attached 3 dbroots like with PM1 -> dbroot1 as other 2.

Infinidb Server Having 16 Core and 256 GB RAM

mysql> select count from ferrero_mults;
----------

----------

----------
1 row in set, 1 warning (0.34 sec)

mysql> SELECT store.REGION AS STORE_REGION FROM store WHERE store.gid IN (1,2,3,5,6) AND store.SNO IN ( SELECT DISTINCT ferrero_mults.SNO FROM ferrero_mults WHERE ferrero_mults.GID IN (1,2,3,5,6) ) GROUP BY STORE_REGION ;

514 rows in set, 1 warning (1.57 sec)

mysql> select calGetTrace();

Desc Mode Table TableOID ReferencedColumns PIO LIO PBE Elapsed Rows
BPS PM ferrero_mults 30228 (GID,SNO) 0 76935 0 1.317 616380
TAS UM - - - - - - 1.301 7767
TNS UM - - - - - - 0.002 7767
BPS PM store 34335 (REGION,SNO,gid) 0 247 0 0.018 516
HJS PM store-$sub_1_1_1 34335 - - - - ----- -
TAS UM - - - - - - 0.005 514

MariaDB Sever Having 1 UM (8 Core 32 GB RAM) and 3 PM (4 Core 8 GB RAM)

Result of query having subquery

mysql> select count from ferrero_mults;
----------

----------

----------
1 row in set, 1 warning (0.34 sec)

mysql> SELECT store.REGION AS STORE_REGION FROM store WHERE store.gid IN (1,2,3,5,6) AND store.SNO IN ( SELECT DISTINCT ferrero_mults.SNO FROM ferrero_mults WHERE ferrero_mults.GID IN (1,2,3,5,6) ) GROUP BY STORE_REGION ;

514 rows in set, 1 warning (14.09 sec)

MariaDB [canadalcl]> select calGetTrace();

Desc Mode Table TableOID ReferencedColumns PIO LIO PBE Elapsed Rows
BPS PM ferrero_mults 3157 (GID,SNO) 0 49036 0 9.949 44572102
BPS PM store 3222 (REGION,SNO,gid) 0 45 0 4.046 9504
HJS UM store-$sub_1_1_1 3222 - - - - ----- -
TAS UM - - - - - - 0.001 514

Result of query with JOIN

Same query I have converted from subquery to join query than it has taken only 3 sec to execute. Below is the log details for that.

MariaDB [canadalcl]> SELECT store.REGION AS STORE_REGION FROM store,ferrero_mults WHERE ferrero_mults.SNO=store.SNO and ferrero_mults.GID=store.gid and ferrero_mults.gid IN (1,2,3,5,6) GROUP BY STORE_REGION;

508 rows in set, 1 warning (2.32 sec)

MariaDB [canadalcl]> select calGetTrace();

Desc Mode Table TableOID ReferencedColumns PIO LIO PBE Elapsed Rows
BPS PM store 3222 (REGION,SNO,gid) 0 32 0 0.004 15368
BPS PM ferrero_mults 3157 (GID,SNO) 0 27228 0 2.302 347129
HJS PM ferrero_mults-store 3157 - - - - ----- -
TAS UM - - - - - - 2.281 508

Please help me to figure out whats wrong configured with MariaDB.