Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.2.13
-
None
-
centos7
Description
CONNECT engine JDBC produces wrong query results when 3 remote tables are joined:
- All tables have a "remote index" added on their primary key column (id) added via ALTER TABLE localConnectTable ADD INDEX (id)
- All tables are declared with Memory=0 and Scrollable=yes and BLOCK_SIZE=1000
- The remote tables have 50,000 to 500,000 records each.
The following query takes forever or times out (essentially fails):
SELECT `ftl_orders`.`id`, `ftl_orders`.`order_id`, `ftl_orders`.`created`, `ftl_order_lines`.`order_id`, `ftl_order_lines`.`quantity`, `ftl_order_lines`.`item_id` |
FROM `ftl_orders` |
JOIN `ftl_order_lines` ON ( `ftl_order_lines`.`order_id` = `ftl_orders`.`id` ) |
WHERE ( ( `ftl_orders`.`created` > '2018-03-01' ) AND ( TRUE ) ) |
LIMIT 0,10
|
The following query runs in 15 seconds and produced the correct results (only difference is LEFT JOIN vs JOIN... the one that works is the LEFT join below):
SELECT `ftl_orders`.`id`, `ftl_orders`.`order_id`, `ftl_orders`.`created`, `ftl_order_lines`.`order_id`, `ftl_order_lines`.`quantity`, `ftl_order_lines`.`item_id` |
FROM `ftl_orders` |
LEFT JOIN `ftl_order_lines` ON ( `ftl_order_lines`.`order_id` = `ftl_orders`.`id` ) |
WHERE ( ( `ftl_orders`.`created` > '2018-03-01' ) AND ( TRUE ) ) |
LIMIT 0,10
|
RESULTS:
id order_id created order_id quantity item_id
|
264640 FT344314640 3/1/2018 0:11 264640 1 155914
|
264641 FT887314641 3/1/2018 0:12 264641 1 63664
|
264642 FT797314642 3/1/2018 0:20 264642 1 185649
|
264642 FT797314642 3/1/2018 0:20 264642 1 185650
|
264642 FT797314642 3/1/2018 0:20 264642 2 185654
|
264643 FT307314643 3/1/2018 0:32 264643 1 177806
|
264643 FT307314643 3/1/2018 0:32 264643 1 181239
|
264644 FT300314644 3/1/2018 0:46 264644 1 152669
|
264645 FT743314645 3/1/2018 1:11 264645 1 178042
|
264646 FT221314646 3/1/2018 1:12 264646 1 185037
|
The following query, which is the same as above but with 1 extra join, now produces INCORRECT results:
SELECT `ftl_orders`.`id`, `ftl_orders`.`order_id`, `ftl_orders`.`created`, `ftl_order_lines`.`order_id`, `ftl_order_lines`.`quantity`, `ftl_order_lines`.`item_id` |
FROM `ftl_orders` |
LEFT JOIN `ftl_order_lines` ON ( `ftl_order_lines`.`order_id` = `ftl_orders`.`id` ) |
LEFT JOIN `ftl_items` ON ( `ftl_items`.`id` = `ftl_order_lines`.`item_id` ) |
WHERE ( ( `ftl_orders`.`created` > '2018-03-01' ) AND ( TRUE ) ) |
LIMIT 0,10
|
RESULTS:
id order_id created order_id quantity item_id
|
264640 FT344314640 3/1/2018 0:11 264640 1 155914
|
264641 FT887314641 3/1/2018 0:12 264641 1 63664
|
264641 FT887314641 3/1/2018 0:12 264641 1 63664
|
264641 FT887314641 3/1/2018 0:12 264641 1 63664
|
264641 FT887314641 3/1/2018 0:12 264641 1 63664
|
264641 FT887314641 3/1/2018 0:12 264641 1 63664
|
264641 FT887314641 3/1/2018 0:12 264641 1 63664
|
264641 FT887314641 3/1/2018 0:12 264641 1 63664
|
264641 FT887314641 3/1/2018 0:12 264641 1 63664
|
264641 FT887314641 3/1/2018 0:12 264641 1 63664
|