PR-staging

Failure

User tests: Successful: Unsuccessful:

avatar bembelimen
bembelimen
18 Aug 2014

If you use UNION with ORDER BY, UNION is needed first and after that ORDER BY to sort the result.

Votes

# of Users Experiencing Issue
4/4
Average Importance Score
4.00

avatar bembelimen bembelimen - open - 18 Aug 2014
avatar jissues-bot jissues-bot - change - 18 Aug 2014
Status Pending New
Labels Added: ? PR-staging
avatar infograf768 infograf768 - change - 18 Aug 2014
Labels Added: ?
Removed: ?
avatar bembelimen
bembelimen - comment - 18 Aug 2014

If you combine ->order(...) with ->union(...) in the old version, you will always get the error: "Incorrect usage of UNION and ORDER BY". So there is definitely a bug.
Additional you "merge" query 2 into query 1, (query 2 is a part/element of query 1), so I think, an order is acceptable.

avatar sovainfo
sovainfo - comment - 19 Aug 2014

Please provide details on the error "Incorrect usage of UNION and ORDER BY". The methods allow you to specify these options, it doesn't stop you from making errors. Without details it is not possible to say it is a Joomla bug or UBF.

The query object doesn't allow for ORDER BY on result set. A feature request could be made to enable options to be set for the result set. As mentioned, they should be independent from the options on the first set. Don't like options to be switched to result set upon using union, specially because they are intended for the first set. You can add the ORDER BY to the result set by string manipulation. Without a proper feature to set result set options it is not going to be possible to use those options on the first set.

avatar nicksavov nicksavov - change - 21 Aug 2014
Labels Removed: ?
avatar infograf768 infograf768 - change - 21 Aug 2014
Status New Pending
avatar brianteeman brianteeman - change - 4 Oct 2014
Category SQL
avatar brianteeman brianteeman - change - 2 Jan 2015
Status Pending Information Required
avatar brianteeman
brianteeman - comment - 2 Jan 2015

As requested by @sovainfo several months ago pleas provide further information or this will be closed in a few weeks.


This comment was created with the J!Tracker Application at issues.joomla.org/joomla-cms/4127.

avatar chrisdavenport
chrisdavenport - comment - 2 Jan 2015

A change like this would definitely need to be accompanied by unit tests in any case.

avatar bembelimen
bembelimen - comment - 3 Jan 2015

Oh, sorry, I missed that PR

        $query1 = JFactory::getDbo()->getQuery(true);
        $query1->select(array('id', 'title'))->from('#__content')->where('title LIKE ' . $query1->q($query1->e('A', true) . '%', false));

        $query2 = JFactory::getDbo()->getQuery(true);
        $query2->select(array('id', 'title'))->from('#__content')->where('title LIKE ' . $query1->q($query1->e('B', true) . '%', false));

        $query1->union($query2)->order('id DESC');

        JFactory::getDbo()->setQuery($query1)->execute();
avatar bembelimen
bembelimen - comment - 7 Jan 2015

@chrisdavenport the first question is: is this a bug and if yes, should it be fixed...before (I think) it makes not much sense to write tests

avatar chrisdavenport
chrisdavenport - comment - 7 Jan 2015

I don't know if it's a bug or not. A test (or a collection of tests) would clearly demonstrate the expected vs. actual output and make it easier to decide. Bear in mind that we do not guarantee that it's impossible to generate invalid queries.

avatar brianteeman
brianteeman - comment - 21 Jun 2015

After 6 months without update I am closing this at this time. It can always be reopened at a later date if needed


This comment was created with the J!Tracker Application at issues.joomla.org/joomla-cms/4127.

avatar brianteeman brianteeman - change - 21 Jun 2015
Status Information Required Closed
Closed_Date 0000-00-00 00:00:00 2015-06-21 10:29:18
Closed_By brianteeman
avatar brianteeman brianteeman - close - 21 Jun 2015
avatar brianteeman brianteeman - close - 21 Jun 2015
avatar enduro44
enduro44 - comment - 6 Apr 2016

I can confirm that this is bug. You can try any of examples from https://docs.joomla.org/Using_the_union_methods_in_database_queries where are used order and replicate "Incorrect usage of UNION and ORDER BY", at least in mysql.


This comment was created with the J!Tracker Application at issues.joomla.org/joomla-cms/4127.

avatar gunjanpatel
gunjanpatel - comment - 31 May 2016

I am also having the same issue.

I am getting

screen shot 2016-05-31 at 05 02 35

It should be like

screen shot 2016-05-31 at 05 03 41

ORDER BY should be in the end.


This comment was created with the J!Tracker Application at issues.joomla.org/joomla-cms/4127.

avatar RonakParmar
RonakParmar - comment - 31 May 2016

I can reproduce this issue. There is a bug when we use UNION and ORDER together in query.

System Information:
Joomla! 3.4.7 Stable [ Ember ] 21-December-2015 16:00 GMT
5.4.45-3+donate.sury.org~precise+3
Apache/2.2.22 (Ubuntu)


This comment was created with the J!Tracker Application at issues.joomla.org/joomla-cms/4127.

avatar gunjanpatel gunjanpatel - change - 31 May 2016
Status Closed Confirmed
Closed_Date 2015-06-21 10:29:18
Closed_By brianteeman
avatar gunjanpatel
gunjanpatel - comment - 31 May 2016
avatar joomla-cms-bot joomla-cms-bot - change - 31 May 2016
Status Confirmed New
Closed_Date 0000-00-00 00:00:00
avatar joomla-cms-bot joomla-cms-bot - change - 31 May 2016
Status New Pending
avatar joomla-cms-bot joomla-cms-bot - reopen - 31 May 2016
avatar joomla-cms-bot joomla-cms-bot - reopen - 31 May 2016
avatar gunjanpatel
gunjanpatel - comment - 31 May 2016

Reopening issue as we are having issues at least in MySQL.


This comment was created with the J!Tracker Application at issues.joomla.org/joomla-cms/4127.

avatar RonakParmar RonakParmar - test_item - 31 May 2016 - Tested successfully
avatar RonakParmar
RonakParmar - comment - 31 May 2016

I have tested this item successfully on d578064


This comment was created with the J!Tracker Application at issues.joomla.org/joomla-cms/4127.

avatar gunjanpatel
gunjanpatel - comment - 31 May 2016

Current patch works. Not sure this is valid fix or not but when I have applied patch it works for me. May be we need more tests in different environments.


This comment was created with the J!Tracker Application at issues.joomla.org/joomla-cms/4127.

avatar gunjanpatel gunjanpatel - test_item - 31 May 2016 - Tested successfully
avatar gunjanpatel
gunjanpatel - comment - 31 May 2016

I have tested this item successfully on d578064


This comment was created with the J!Tracker Application at issues.joomla.org/joomla-cms/4127.

avatar alikon
alikon - comment - 31 May 2016

Can you provide a clear test information. thanks

avatar gunjanpatel
gunjanpatel - comment - 31 May 2016

@alikon You can use this example query to reproduce this issue.

$db = JFactory::getDbo();

$query1 = $db->getQuery(true);
$query1->select(array('id', 'title'))
        ->from('#__content')
        ->where('title LIKE ' . $query1->q($query1->e('A', true) . '%', false));

$query2 = $db->getQuery(true)
            ->select(array('id', 'title'))
            ->from('#__content')
            ->where('title LIKE ' . $query1->q($query1->e('B', true) . '%', false));

$query1->union($query2);

$query1->order('id DESC');

echo $query1->dump();

This is query is the same which suggested by @bembelimen before. Just formatted it for more readability.

avatar gunjanpatel gunjanpatel - change - 29 Jun 2016
Status Pending Needs Review
avatar mehmetalipamukci mehmetalipamukci - test_item - 1 Aug 2016 - Tested successfully
avatar mehmetalipamukci
mehmetalipamukci - comment - 1 Aug 2016

I have tested this item successfully on d578064

tested @icampus:
1. reproduce problem
Using a sample component-> Model with 2 query Objects , union them, and ordering results in SQL Error
2. Patch
Fixed the Problem


This comment was created with the J!Tracker Application at issues.joomla.org/joomla-cms/4127.

avatar brianteeman
brianteeman - comment - 1 Aug 2016

This will not be merged without unit tests as requested by @chrisdavenport


This comment was created with the J!Tracker Application at issues.joomla.org/joomla-cms/4127.

avatar roland-d
roland-d - comment - 2 Aug 2016

For what it is worth the MySQL documentation states "To use an ORDER BY or LIMIT clause to sort or limit the entire UNION result, parenthesize the individual SELECT statements and place the ORDER BY or LIMIT after the last one. "
Source: http://dev.mysql.com/doc/refman/5.7/en/union.html

A query like this

(SELECT id FROM c7lja_content WHERE id=10 AND id=1)
ORDER BY id
UNION
(SELECT id FROM c7lja_content WHERE id=11 AND id=2)

will not work as it throws the error

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 'UNION
(SELECT id FROM c7lja_content WHERE id=11 AND id=2)' at line 3

whereas the query in this format is correct:

(SELECT id FROM c7lja_content WHERE id=10 AND id=1)
UNION
(SELECT id FROM c7lja_content WHERE id=11 AND id=2)
ORDER BY id

In which circumstance does the order belong before the UNION?

avatar pedrobicudo
pedrobicudo - comment - 23 Sep 2016

I can confirm the bug exist in Joomla! 3.6.2 and the proposed fix works (inversion sequence in query.php line 254): write UNION before ORDER BY. On d578064 (2014) was line 374.


This comment was created with the J!Tracker Application at issues.joomla.org/joomla-cms/4127.

avatar eshiol eshiol - test_item - 24 Oct 2016 - Tested successfully
avatar eshiol
eshiol - comment - 24 Oct 2016

I have tested this item successfully on d578064

The bug exists in Joomla! 3.6.3 and the proposed fix works

libraries/joomla/database/query.php
254: if ($this->union)
255: {
256: $query .= (string) $this->union;
257: }
258:
259: if ($this->order)
260: {
261: $query .= (string) $this->order;
262: }

Here is a workaround:
$query1 = JFactory::getDbo()->getQuery(true);
$query1->select(array('id', 'title'))->from('#__content')->where('title LIKE ' . $query1->q($query1->e('A', true) . '%', false));

$query2 = JFactory::getDbo()->getQuery(true);
$query2->select(array('id', 'title'))->from('#__content')->where('title LIKE ' . $query1->q($query1->e('B', true) . '%', false));

//$query1->union($query2)->order('id DESC');
//JFactory::getDbo()->setQuery($query1)->execute();

/* append the ORDER BY clause as string */
$query1->union($query2);
JFactory::getDbo()->getQuery(true)->setQuery((string)$query1.' ORDER BY id DESC')->execute();


This comment was created with the J!Tracker Application at issues.joomla.org/tracker/joomla-cms/4127.

avatar astridx
astridx - comment - 4 Nov 2016

I just wanted to look at this problem. I have noticed that it is already implemented in the branch Joomla! 3.7.0-dev.


This comment was created with the J!Tracker Application at issues.joomla.org/tracker/joomla-cms/4127.

avatar csthomas
csthomas - comment - 4 Nov 2016

Can we close it as it is fixed at #10817

avatar rdeutz
rdeutz - comment - 4 Nov 2016

it doesn't makes sense to order individual queries because UNION merge the results into one result so this is a bug

avatar rdeutz rdeutz - change - 4 Nov 2016
Status Needs Review Closed
Closed_Date 0000-00-00 00:00:00 2016-11-04 09:42:31
Closed_By rdeutz
avatar rdeutz rdeutz - close - 4 Nov 2016
avatar joomla-cms-bot joomla-cms-bot - change - 4 Nov 2016
Category SQL Libraries

Add a Comment

Login with GitHub to post a comment