[MCOL-652] Derived tables in a join subquery cause error Created: 2017-03-31  Updated: 2017-04-05  Resolved: 2017-04-05

Status: Closed
Project: MariaDB ColumnStore
Component/s: MariaDB Server
Affects Version/s: 1.0.8, 1.1.0
Fix Version/s: 1.0.9, 1.1.0

Type: Bug Priority: Major
Reporter: Andrew Hutchings (Inactive) Assignee: Daniel Lee (Inactive)
Resolution: Fixed Votes: 0
Labels: None

Sprint: 2017-6, 2017-7

 Description   

When using a join with a derived table you get the error:

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'USE INDEX () join (select 1 AS `dummy`,count(test.vehicle.id) AS `total_vehicles' at line 1

We add USE INDEX() as part of MCOL-424 but we only need to do this for real tables, not derived.

 Comments   
Comment by Andrew Hutchings (Inactive) [ 2017-03-31 ]

Pull request open for 1.0 and develop server trees

Comment by Daniel Lee (Inactive) [ 2017-04-04 ]

Build tested: 1.0.9-1 (branch develop-1.0)
[root@localhost mariadb-columnstore-server]# git show
commit 8ef673b333f79586645321a60e1ee3c1bcfc491c
Merge: e688674 7e877fa
Author: dhall-InfiniDB <david.hall@mariadb.com>
Date: Mon Apr 3 09:51:52 2017 -0500
Merge pull request #39 from mariadb-corporation/MCOL-652-1.0
MCOL-652 Fix certain derived table cases
[root@localhost mariadb-columnstore-engine]# git show
commit 1c2e9bd8281c5ad717820b7ec0bc234c47321b22
Merge: 310f8e5 b60c45a
Author: dhall-InfiniDB <david.hall@mariadb.com>
Date: Mon Apr 3 09:47:57 2017 -0500
Merge pull request #144 from mariadb-corporation/MCOL-653-1.0
MCOL-653 Revert SUM(1) behaviour

Reproduced the issue in 1.0.8-1 (1um2pm stack) with just one procedure call
MariaDB [(none)]> use automated_reports;
Database changed
MariaDB [automated_reports]> call DRUG_vehicles_in_crashes( 5 );
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'USE INDEX () join (select 1 AS `dummy`,count(automated_reports.vehicle.id) AS `t' at line 1

Verified 1.0.9-1. Called 10 times without causing the error.

Comment by Daniel Lee (Inactive) [ 2017-04-05 ]

Build verified: 1.1.0-1 (GitHub develop branch)
Author: dhall-InfiniDB <david.hall@mariadb.com>
Date: Mon Apr 3 09:52:07 2017 -0500
Merge pull request #38 from mariadb-corporation/MCOL-652
MCOL-652 Fix certain derived table cases
[root@localhost mariadb-columnstore-server]# cd mariadb-columnstore-engine/
[root@localhost mariadb-columnstore-engine]# git show
commit 3e85b6ef07ffcfb4edc84e170efcccdb9cd15cc2
Merge: 04bdb60 8baf059
Author: dhall-InfiniDB <david.hall@mariadb.com>
Date: Mon Apr 3 09:48:55 2017 -0500
Merge pull request #143 from mariadb-corporation/MCOL-653
MCOL-653 Revert SUM(1) behaviour
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 22
Server version: 10.1.21-MariaDB Columnstore 1.1.0-1

Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

Did the same test as for 1.0.9-1

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