I hope someone may be able to help me out with a problem I’m having trying to use Quicksight’s FULL OUTER JOIN method of bringing together multiple query rowsets.
I have a reasonably complex query that generates a rowset based on a calendar quarter’s data. It’s a list of items and the status of that item at the end of a calendar quarter, like this:
ID Status
1 Open
2 Open
3 In Progress
4 Closed
The source for the data is a MySQL instance.
I need to generate a similar set of data for a total of five quarters, so I have replicated that query and modified it to generate a second rowset that also has the ID and status columns, but based on another calendar quarter. I’ve not yet created the 3rd, 4th or 5th versions but they will look the same.
To test, I wanted to join the two rowsets together on the common ID column. Normally this would be easy, just using an INNER JOIN
The issue is that some of the IDs may only be present in rowset 1, some only in rowset 2 (and 3, 4 or 5 when I build them) and some IDs could be in more than one, or all, rowsets. Because my query has some quite complex logic I figured I could create the query five times, each one based on a different calendar quarter, then use Quicksight’s FULL JOIN logic to combine them together rather than try to do the entire thing in one query, which would end up messy and difficult to maintain.
So when I execute either of the two queries individually they run perfectly well and show me the results in the preview window. However once I set the join up in the main editor window to be a FULL JOIN (I’m not selecting either the left or right boxes to indicate a unique key), as soon as I click on the Dataset tab above the preview window to see the combined output, I immediately get an error message “Your database generated a SQL exception. This can be caused by query timeouts, resource constraints, unexpected DDL alterations before or during a query, and other database errors. Check your database settings and your query, and try again.” Looking at the details it tells me:
“You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘FULL JOIN ( SELECT ID AS fe7f6e70-1611-4709-8d18-4f72579d8869.ID, `Q2 Status’ at line 59”
ID is the name of the column that I’m trying to join on, and it present in both rowsets. Q2 Status is the name of the other column from the second rowset.
For the sake of trying things, I have changed the join method to inner, left and right and they all result in some sort of combined rowset being displayed. The only one that doesn’t work is the FULL JOIN.
I know that MySQL does not support FULL JOIN natively, which is why I wanted to do this in Quicksight rather than in the query itself. I thought that Quicksight would be using its own query engine to carry out the joining logic, not relying on that of the underlying RDBMS? So is that not the case, and the available join methods dependent on the availability of the underlying datasource’s native functionality? Or am I just doing something wrong here?
Grateful for any insight into the way that Quicksight actually performs the joining logic