I made a quick check and noticed that eg. some warnings are gone. But it is not easy to test this one properly as there are currently some problems with a joomla mssql installation. Take for example this one: #11932

This particular problem exists with or without your patch. I am not able to judge without more investigation if this falls in the scope of your patch or not.

Or try out a multilingual installation. There you will find error messages all over the place.

How would you tackle this problem?

@waader, to test this PR you can test to see if issues #11278 and #11279 have been resolved. This PR only fixes the 'select group by' issue caused by MySQL focus queries. MySQL does not require you to put all the columns in your select clause in the group by clause because it will work it out internally. This is not standard SQL so hence the issue with MSSQL.

I did a quick look into PR #11853 and can confirm that this is the patch causing the issue #11932 you raised.

The problem caused in issue #11932 is due to line 200 in the newly added JHelperUsergroups class.

So in the 'total' function they are doing the following:

$query = $db->getQuery(true)
->select('count(id)')
->from('#__usergroups');
$db->setQuery($query, 0, 1);

I am happy to see in the query they are doing the count on the primary key as opposed to doing count(*) as what is usually done. This means this query can utilize the primary key index as opposed to having to go through the whole table.

However, I can not see why we are setting a limit on a simple count query. The result will always be a single scalar value. I can see that PR #111853 is all about performance improvements but in this case limiting the results returned from a simple count makes no difference because you already paid the expense on the count operation already.

I suggest changing line 200 to:

$db->setQuery($query);

This will stop Joomla from blowing up every time it wants to check the user's permissions in MSSQL. waader I am surprised with PR #11853 applied that you still can login into Joomla with MSSQL!

Okay, the reason we get away with the limit on the count query in MySQL is that MySQL just uses the LIMIT clause, and it doesn't care.

So this

$query = $db->getQuery(true)
->select('count(id)')
->from('#__usergroups');
$db->setQuery($query, 0, 1);

is converted to:

'SELECT COUNT(id) from #_usergroups LIMIT 0, 1'

WHERE as in MSSQL this is not the case, and you have to do the following:

Using the WITH clause and CTE (MSSQL 2005+)

WITH cte AS
( SELECT count(id) , ROW_NUMBER() OVER (ORDER BY (select 0)) AS RowNumber FROM #__usergroups)
SELECT * FROM cte WHERE cte.RowNumber BETWEEN 1 AND 1;


Or not using the WITH clause (MSSQL 2000+)

SELECT * FROM ( SELECT count(id) , ROW_NUMBER() OVER (ORDER BY (select 0)) AS RowNumber FROM #__usergroups) A WHERE A.RowNumber BETWEEN 1 AND 1 ;

In either case, you are creating a derived table where every column has to be named, which is not the case and hence the error you are getting in MSSQL.

So waader I suggest changing line 200 as I suggested in the libraries/cms/helper/usergroups.php file to get Joomla working again with PR #11853 on SQL Server.

This PR needs to be synchronized to the current development branch to be tested/reviewed.

If this PR get no Response, it will be closed at 22th June 2017.

closed as stated above.

Set to "closed" on behalf of @franz-wohlkoenig by The JTracker Application at issues.joomla.org/joomla-cms/11938