Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-15463

CONNECT engine JDBC produces wrong query results

Details

    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

      Attachments

        Activity

          No workflow transitions have been executed yet.

          People

            bertrandop Olivier Bertrand
            rdyas Robert Dyas
            Votes:
            0 Vote for this issue
            Watchers:
            4 Start watching this issue

            Dates

              Created:
              Updated:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.