Error 1749 (hy00)

Hi, I’m continually having problems with running distributed queries from the Ch-Benchmark…

select  i_name, substr(i_data, 1, 3) as brand, i_price, count(distinct (mod((s_w_id * s_i_id),10000))) as supplier_cnt from  stock, item where  i_id = s_i_id and i_data not like 'zz%' and (mod((s_w_id * s_i_id),10000) not in (select su_suppkey from supplier where su_comment like '%bad%')) group by i_name, substr(i_data, 1, 3), i_price order by supplier_cnt desc;

This query raises ERROR 1749 (HY000) at line 1: Feature ‘Uncorrelated subselect IN-list that is not at bottom of distributed query tree’ is not supported by MemSQL Distributed.

However, if I run the same query with a different partitioning scheme, it works perfectly in a smaller test dataset.

I’m just really confused right now.

Only difference between the two schemas is that in my bigger dataset, the supplier relation is sharded but it’s replicated in the smaller one.

+---------------+--------------+------------+------------+--------------+------------+--------------+-------------+-----------+-------------+----------+--------+----------+------------+---------+---------------+
| TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | NON_UNIQUE | INDEX_SCHEMA | INDEX_NAME | SEQ_IN_INDEX | COLUMN_NAME | COLLATION | CARDINALITY | SUB_PART | PACKED | NULLABLE | INDEX_TYPE | COMMENT | INDEX_COMMENT |
+---------------+--------------+------------+------------+--------------+------------+--------------+-------------+-----------+-------------+----------+--------+----------+------------+---------+---------------+
| def           | tpcch        | customer   |          1 | tpcch        | c_d_id     |            1 | c_d_id      | NULL      |           0 |     NULL | NULL   |          | SHARD      |         |               |
| def           | tpcch        | neworder   |          1 | tpcch        | no_d_id    |            1 | no_d_id     | NULL      |           0 |     NULL | NULL   |          | SHARD      |         |               |
| def           | tpcch        | neworder   |          1 | tpcch        | no_d_id    |            2 | no_w_id     | NULL      |           0 |     NULL | NULL   |          | SHARD      |         |               |
| def           | tpcch        | orderline  |          1 | tpcch        | ol_d_id    |            1 | ol_d_id     | NULL      |           0 |     NULL | NULL   |          | SHARD      |         |               |
| def           | tpcch        | orderline  |          1 | tpcch        | ol_d_id    |            2 | ol_w_id     | NULL      |           0 |     NULL | NULL   |          | SHARD      |         |               |
| def           | tpcch        | orders     |          1 | tpcch        | o_d_id     |            1 | o_d_id      | NULL      |           0 |     NULL | NULL   |          | SHARD      |         |               |
| def           | tpcch        | supplier   |          1 | tpcch        | su_suppkey |            1 | su_suppkey  | NULL      |           0 |     NULL | NULL   |          | SHARD      |         |               |
| def           | tpcch_test   | orderline  |          1 | tpcch_test   | ol_o_id    |            1 | ol_o_id     | NULL      |           0 |     NULL | NULL   |          | SHARD      |         |               |
| def           | tpcch_test   | orderline  |          1 | tpcch_test   | ol_o_id    |            2 | ol_w_id     | NULL      |           0 |     NULL | NULL   |          | SHARD      |         |               |
| def           | tpcch_test   | orderline  |          1 | tpcch_test   | ol_o_id    |            3 | ol_d_id     | NULL      |           0 |     NULL | NULL   |          | SHARD      |         |               |
| def           | tpcch_test   | orders     |          1 | tpcch_test   | o_id       |            1 | o_id        | NULL      |           0 |     NULL | NULL   |          | SHARD      |         |               |
| def           | tpcch_test   | orders     |          1 | tpcch_test   | o_id       |            2 | o_w_id      | NULL      |           0 |     NULL | NULL   |          | SHARD      |         |               |
| def           | tpcch_test   | orders     |          1 | tpcch_test   | o_id       |            3 | o_d_id      | NULL      |           0 |     NULL | NULL   |          | SHARD      |

What version of MemSQL are you running?

Consider also a query rewrite to turn the subquery into just a regular join and use NOT LIKE instead of LIKE. Maybe that can be made to work.

E.g.

...
from stock, item, supplier
where ...
(mod((s_w_id * s_i_id),10000) 
    = su_suppkey and su_comment not like like '%bad%' 
...

We investigated this and found that a new optimization added in 7.1 accidentally prevented us from being able to run this query shape. The issue was for certain shapes of IN subselects. The query runs fine on 7.0 and earlier versions, and we have made a fix to enable running the query again which will be available in future versions of MemSQL.

Was this a bug in the code?

No, not a bug per se, but a new optimization interfered with the ability to execute particular query shapes of IN subselects.

The fix will be in the next patch release (7.1.5).

Thanks for all the help. :slight_smile: