Joomla-cms: Joomla 3.6.5 - SQL syntax exception with Union

[ybenhssaien]

Steps to reproduce the issue

$db = JFactory::getDbo();

    // Create a new query object.
    $query = $db->getQuery(true);

    $fieldlist = $db->qn(array('category.id','category.title'), array('id', 'title')); // add the field names to an array
    $fieldlist[0] = 'distinct ' . $fieldlist[0]; 

    $query->select($fieldlist);
    $query->from($db->quoteName('#__categories', 'category'));
    $query->join('INNER', $db->quoteName('#__content', 'article') . ' ON (' . $db->quoteName('category.id') . ' = ' . $db->quoteName('article.catid') . ')');
    $query->where("article.state = 1");
    $query->order("title");

    // Reset the query using our newly populated query object.
    $db->setQuery($query);

    // Load the results as a list of stdClass objects
    $results1 = $db->loadObjectList();

    $q = $db->getQuery(true);

    $otherFieldlist = $db->qn(array('category.docman_category_id','category.title'), array('id', 'title')); // add the field names to an array
    $otherFieldlist[0] = 'distinct 0-' . $otherFieldlist[0]; 

    $q->select($otherFieldlist);
    $q->from($db->quoteName('#__docman_categories', 'category'));
    $q->join('INNER', $db->quoteName('#__docman_documents', 'document') . ' ON (' . $db->quoteName('category.docman_category_id') . ' = ' . $db->quoteName('document.docman_category_id') . ')');
    $q->where("document.enabled = 1");
    $q->union($query);
    $q->order("title");

    $db->setQuery($q); die($q);

Generate an exception :

Incorrect usage of UNION and ORDER BY SQL=SELECT distinct 0-category.docman_category_id AS id,category.title AS title FROM #__docman_categories AS category INNER JOIN #__docman_documents AS document ON (category.docman_category_id = document.docman_category_id) WHERE document.enabled = 1 ORDER BY title UNION ( SELECT distinct category.id AS id,category.title AS title FROM #__categories AS category INNER JOIN #__content AS article ON (category.id = article.catid) WHERE article.state = 1 ORDER BY title)

Because of parenthesis needed to encapsulate the first query !

Expected result

(SELECT distinct 0-category.docman_category_idASid,category.titleAStitleFROM#__docman_categoriesAScategoryINNER JOIN#__docman_documentsASdocumentON (category.docman_category_id=document.docman_category_id) WHERE document.enabled = 1 ORDER BY title) UNION ( SELECT distinctcategory.idASid,category.titleAStitleFROM#__categoriesAScategoryINNER JOIN#__contentASarticleON (category.id=article.catid) WHERE article.state = 1 ORDER BY title)

Actual result

SELECT distinct 0-category.docman_category_idASid,category.titleAStitleFROM#__docman_categoriesAScategoryINNER JOIN#__docman_documentsASdocumentON (category.docman_category_id=document.docman_category_id) WHERE document.enabled = 1 ORDER BY title UNION ( SELECT distinctcategory.idASid,category.titleAStitleFROM#__categoriesAScategoryINNER JOIN#__contentASarticleON (category.id=article.catid) WHERE article.state = 1 ORDER BY title)

System information (as much as possible)

Joomla! 3.6.5
Base de données : MySQL 5.7.19

Additional comments

[brianteeman]

Please update to Joomla 3.8.1 and retest. There have been hundreds of bug fixes since 3.6.5

Also please note that you are reporting an extension in com_docman which is a joomla extension and ot part of the core of joomla and you should report any issues with that component to its authors

[ybenhssaien]

Thank you for the response, I took 5 minutes to report this issue and you didn't even suggest something to resolve !!
This bug is related to the core of joomla and specially with (JDatabaseQueryElement, JDatabaseQuery, JDatabaseDriver)
Updating joomla is not even easy, I'm not working for my own project! this is a big project for a company !!
Instead making fast response, I suggest you to help with some solutions !

[brianteeman]

I did suggest something - update joomla

[ybenhssaien]

Thanks (y) You saved my life

[mbabker]

If a bug can be confirmed on the current Joomla release (3.8.1 as of this moment), then we can investigate it and determine if a fix is needed. We will not spend time chasing a reported bug on a year old release because it is very well possible this issue has been previously reported and fixed, therefore the correct fix would be to update your Joomla site to receive the fix.

[ggppdk]

@youssefbenhssaien

i think i remember this issue (fix for union and unionall included in J3.7.0)

so maybe i can help you !

here is the PR
https://github.com/joomla/joomla-cms/pull/10817

and here is the fix that was made
https://github.com/joomla/joomla-cms/pull/10817/files

it seems easy to apply fix to J3.6.5

now after helping you above (i hope)

please note

1. that here you can only report issues for the latest stable released version, (and better after testing with the latest 'staging' branch of this repository (if you are a developer))
2. your request is effectively asking for support (old joomla version) and this repository is not a support forum, so you really posted in the wrong place
3. you could have searched the repository with word "union" to find the relevant issues and the above PR, especially since you are reporting an issue for an old version
4. the answer to your issue was the most proper, "use latest stable release" and get the fix, if you cannot upgrade that does not invalidate the answer you got

[ybenhssaien]

Unfortunately upgrading is not possible in the short-term, I checked the same files on 3.8.1 and seem have the same code (nothing changed), I didn't check and I haven't had time to really investigate deeply (maybe I'll do it later or someone else can do it).

I fixed my problem with :
$union = $db->getQuery(true); $union->setQuery("($q) UNION ($query)");

Is not the best way to do it but it solved the issue !

Thank you (y)

[ggppdk]

Nothing changed ?

https://github.com/joomla/joomla-cms/commit/3186fe5f2f1fb82ba8c3a11b298ea0060d72fc46#diff-099efd7df04a3182a918054c0b9f56d8

the change was included in 3.7.0-alpha1