Advanced MSSQL query workaround for limit, offset and for sql with aggregate functions by csthomas · Pull Request #13262 · joomla/joomla-cms

csthomas · 2016-12-18T10:53:25Z

Competition for #11938

Summary of Changes

Fix various problems with sqlsrv queries:

Add alias to column (or expression) which does not have it, but it is required if query will use OFFSET, which means use wrapped query to emulate offset. Every column in SELECT statement required alias if it is placed in subquery.
If SELECT contains a wild column x.* then load and put all columns from x table to GROUP BY
If GROUP BY contains alias to (column or expression) then replace it with expression.
Ex:
SELECT LOWER(name) AS name, catid AS cc, count(*) ... GROUP BY name, cc
will be replaced to
SELECT LOWER(name) AS name, count(*) ... GROUP BY LOWER(name), catid
Add column from ORDER BY to GROUP BY if not yet exists. If this is an alias then replace it with expression as mentioned earlier.
Add columns from SELECT to GROUP BY if missing (related to mysql ONLY_FULL_GROUP_BY). Probably it would be better to fix joomla queries than create such workaround.
Example of fixed query:
SELECT id, name, COUNT(*) FROM #__x GROUP BY id
will be replaced by:
SELECT id, name, COUNT(*) AS columnAlias0 FROM #__x GROUP BY id, name

Fixed issue: #11279

Removes notices, warnings and errors from:

/administrator/index.php?option=com_banners
/administrator/index.php?option=com_categories&extension=com_banners
/administrator/index.php?option=com_banners&view=clients
/administrator/index.php?option=com_finder&view=maps
/administrator/index.php?option=com_categories&extension=com_newsfeeds
/administrator/index.php?option=com_categories&extension=com_contact
/administrator/index.php?option=com_postinstall

Fix issue with Popular Tags module on the front end.

Testing Instructions

Be sure that you can see PHP Notices.
Test above links and module Popular Tags before and after patch.

Install a new staging version of joomla (without sample data because currently joomla on mssql is broken - menu assets)
Create a category and two articles with a few tags.
Be sure that you have module Popular Tags enabled (ex. position-7)
Go to the front page
I should see some similar sql error as on image but with less details

Documentation Changes Required

No

csthomas · 2017-01-07T17:41:46Z

@waader Which URL page or sql query did not work?

waader · 2017-01-07T17:50:31Z

With current staging, testing data and your patch applied just go to the frontend. There are a lot of notices (something in the module helper). I have already shutdown my test pc, so I could give you the exact messages tomorrow.

csthomas · 2017-01-08T03:39:24Z

I have found a few errors and I fixed it.
Please inform me if you find some errors.

alikon · 2017-01-08T09:40:32Z

i'm testing on backend and seems ok, unfortunately even if is not caused by this pr i see issues on frontend too as reported by @waader
can you have a look at frontend with current staging and testing data
thanks in advance

waader · 2017-01-08T10:00:38Z

With testing data installed frontend gives an "404 Component not found" error. Starting with index.php?option=com_content the page loads. The module helper notices are also present and no modul content is displayed.

In backend I get an error when deleting Banner Tracks:
An expression of non-boolean type specified in a context where a condition is expected, near ')'.

waader · 2017-01-08T10:47:31Z

Can you have a look at Smart Search > Content Maps:

[Microsoft][SQL Server Native Client 11.0][SQL Server]Column "d.branch_title" is invalid in the ORDER BY clause because it is not contained in either an aggregate function or the GROUP BY clause.

csthomas · 2017-01-08T12:00:54Z

I will take a look. Are you sure do you use the last commit in this PR, I added it today morning (night)?

waader · 2017-01-08T15:03:19Z

I have several patches for mssql installed now and also this patch and now the problem with content map has gone away.

Frontend is broken nonetheless.

csthomas · 2017-01-08T17:56:31Z

On test database I have found two 404 links because mssql table does not have 2 articles for:

Popular Tags (Alias: popular-tags)
Similar Tags (Alias: similar-tags)

one in news-feed - ??
and search component has some error. ??

but home page work for me ok.

wilsonge · 2017-02-14T13:15:32Z

Frontend issue is a separate issue with a fix here: #14057

csthomas · 2017-02-14T13:21:00Z

Warning: ksort() expects parameter 2 to be long

I assume this is php 5.3 issue, I have created an issue.

waader · 2017-02-14T15:41:42Z

@WilsonG #14057 resolves the problem - thanks
@csthomas You are right. My mssql-test system run´s on 5.3.29

By the way: with the redirect plugin enabled I get 500 - PLG_SYSTEM_REDIRECT_ERROR_UPDATING_DATABASE

wilsonge · 2017-02-14T15:44:28Z

@csthomas that redirect plugin error might be related to this pr :)

csthomas · 2017-02-15T10:40:40Z

By the way: with the redirect plugin enabled I get 500 - PLG_SYSTEM_REDIRECT_ERROR_UPDATING_DATABASE

[Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Cannot insert the value NULL into column 'comment', table 'joomla.dbo.j37_redirect_links'; column does not allow nulls. INSERT fails.
This is unrelated.

I have found a big problem with sqlsrv updates in joomla (administrator/components/com_admin/sql/updates/sqlazure/*.sql).
A lots of that ALTER TABLE ALTER COLUMN has invalid syntax.

https://msdn.microsoft.com/en-us/library/ms190273.aspx

They are not executed to database at all. At least for my environment.
I need a little time to create an issue.

waader · 2017-02-22T20:49:43Z

@csthomas: Can you sychronize this patch with the changes made in other yet commited sqlserver-patches.

csthomas · 2017-02-22T20:51:36Z

OK. Please wait a moment

* Fix offset, limit - intruduce SELECT TOP * Add multi table update for mssql query * Add more advanced workaround for mssql GROUP BY. * Add required aliases to complex columns (ex. function) in subqueries. * Add workaround for use alias in GROUP BY statement * Add to GROUP BY statement columns from ORDER BY * Remove column with static string, NULL or numeric from GROUP BY statement * Add unit tests

csthomas · 2017-02-22T21:09:54Z

I have rebased it. It should work but you can not use sample data, because it is broken, I'm working on that now (#13981).

But you can get sample data from #7680

csthomas · 2017-02-22T21:17:52Z

Please ignore errors like:

Cannot insert the value NULL into column 'term_id', table 'joomla.dbo.#__finder_tokens_aggregate'; column does not allow nulls. INSERT fails.

It is unrelated, and it is waiting for a new PR.

…onents/com_admin/sql/updates/sqlazure

csthomas · 2017-02-23T08:13:04Z

There is not need to combine this PR for tests with #14102 in the same time.

But if someone want to test 2 PRs at once then:

first apply MSSQL fix sql syntax in administrator/components/com_admin/sql/updates/sqlazure #14102 and upgrade joomla database to latest version,
remove above patch from Patch Tester,
apply this PR (this PR has common, identical file database/driver/sqlsrv.php with PR MSSQL fix sql syntax in administrator/components/com_admin/sql/updates/sqlazure #14102)
test this PR.

waader · 2017-02-24T19:07:21Z

I have tested this item ✅ successfully on 00fd4a9

Fixes these problems:
/administrator/index.php?option=com_banners
/administrator/index.php?option=com_categories&extension=com_banners
/administrator/index.php?option=com_banners&view=clients
/administrator/index.php?option=com_finder&view=maps
/administrator/index.php?option=com_categories&extension=com_newsfeeds
/administrator/index.php?option=com_categories&extension=com_contact
/administrator/index.php?option=com_postinstall

Thanks!

_{This comment was created with the J!Tracker Application at issues.joomla.org/tracker/joomla-cms/13262.}

csthomas · 2017-02-24T23:21:17Z

Last commit fix error on "sample testing" for menu items:

Compact tagged: should show one record
Tagged items: should show one record

csthomas · 2017-02-24T23:28:13Z

For All tags the is other error which has to be resolved by separate PR.

DB function failed with error number 169
[Microsoft][ODBC Driver 13 for SQL Server][SQL Server]A column has been specified more than once in the order by list. Columns in the order by list must be unique.
SQL =

SELECT TOP 20 a.*
FROM [#__tags] AS a
WHERE [a].[access] IN (1,5,1) AND [a].[parent_id] <> 0 AND [a].[title] LIKE N'%[gl]%' AND . [a].[published] = 1
ORDER BY [title] ASC, a.title ASC

[Microsoft][ODBC Driver 13 for SQL Server][SQL Server]A column has been specified more than once in the order by list. Columns in the order by list must be unique.

wilsonge · 2017-02-25T12:30:07Z

Merged with waaders test given this is only affecting mssql

csthomas · 2017-02-25T12:41:57Z

Thanks

photodude · 2017-02-25T15:54:21Z

@csthomas Will you consider making these changes to the Framework also?

joomla-cms-bot added the PR-staging label Dec 18, 2016

csthomas force-pushed the fixmssql branch from 4013a84 to f3b4dbe Compare December 18, 2016 12:36

csthomas mentioned this pull request Dec 19, 2016

Menu manager for Joomla Backend Menu #13036

Merged

csthomas force-pushed the fixmssql branch from f3b4dbe to 465b3c1 Compare December 19, 2016 18:29

joomla-cms-bot added the Unit/System Tests label Dec 19, 2016

csthomas force-pushed the fixmssql branch 2 times, most recently from 7318025 to 4e6663c Compare December 19, 2016 20:14

csthomas mentioned this pull request Dec 19, 2016

[RFC] Another way to fast reorder table with single SQL query #12839

Closed

csthomas changed the title ~~[WIP] More advanced MSSQL query workaround for limit, offset and for sql with aggregate functions~~ Advanced MSSQL query workaround for limit, offset and for sql with aggregate functions Dec 19, 2016

csthomas mentioned this pull request Dec 19, 2016

Mssql: article can´t be created #11932

Closed

csthomas force-pushed the fixmssql branch 2 times, most recently from f48b75d to c5d2340 Compare December 21, 2016 20:29

csthomas force-pushed the fixmssql branch 2 times, most recently from 349f10b to 238413e Compare January 6, 2017 00:07

csthomas mentioned this pull request Jan 7, 2017

[com_fields] MSSQL - sql for Custom fields #12660

Merged

csthomas force-pushed the fixmssql branch from 8fe9b71 to 909e957 Compare January 10, 2017 01:11

csthomas mentioned this pull request Jan 12, 2017

Fixing search for MySQL #13571

Merged

csthomas force-pushed the fixmssql branch from 909e957 to ff30279 Compare January 12, 2017 14:21

csthomas mentioned this pull request Jan 13, 2017

Mssql - fix escape() and add support for unicode chars #13585

Merged

csthomas mentioned this pull request Jan 27, 2017

Can not install joomla on MSSQL - remove semicolon before UNION ALL #13767

Merged

csthomas mentioned this pull request Feb 3, 2017

[mssql] Simpler workaround for query with limit and offset #13895

Closed

csthomas mentioned this pull request Feb 14, 2017

Backend Multilingual Associations: Warning on php 5.3 #14063

Closed

wilsonge mentioned this pull request Feb 14, 2017

Fix issue with callback cache controller hashing Closures #14057

Merged

csthomas mentioned this pull request Feb 15, 2017

Escape NULL byte in mssql driver #14077

Merged

This was referenced Feb 15, 2017

Plugin System - Redirect - Display error 404 instead 500 when page does not exist [postgresql, sqlsrv] #14093

Merged

MSSQL fix sql syntax in administrator/components/com_admin/sql/updates/sqlazure #14102

Merged

Tomasz Narloch added 2 commits February 22, 2017 21:56

[mssql] Simpler workaround for query with limit and offset

f776322

csthomas force-pushed the fixmssql branch from efc85ce to d4089c3 Compare February 22, 2017 21:06

Add changes to driver from MSSQL fix sql syntax in administrator/comp…

00fd4a9

…onents/com_admin/sql/updates/sqlazure

csthomas mentioned this pull request Feb 24, 2017

Sync sql install and samples files #14223

Merged

Do not add to GROUP BY aggregate expression

95f9f6a

wilsonge merged commit aeebb2b into joomla:staging Feb 25, 2017

wilsonge added this to the Joomla 3.7.0 milestone Feb 25, 2017

csthomas deleted the fixmssql branch February 25, 2017 12:42

Uh oh!

Conversation

csthomas commented Dec 18, 2016 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Summary of Changes

Testing Instructions

Documentation Changes Required

Uh oh!

csthomas commented Jan 7, 2017

Uh oh!

waader commented Jan 7, 2017

Uh oh!

csthomas commented Jan 8, 2017

Uh oh!

alikon commented Jan 8, 2017

Uh oh!

waader commented Jan 8, 2017

Uh oh!

waader commented Jan 8, 2017

Uh oh!

csthomas commented Jan 8, 2017

Uh oh!

waader commented Jan 8, 2017

Uh oh!

csthomas commented Jan 8, 2017

Uh oh!

wilsonge commented Feb 14, 2017

Uh oh!

csthomas commented Feb 14, 2017 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Uh oh!

waader commented Feb 14, 2017 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Uh oh!

wilsonge commented Feb 14, 2017

Uh oh!

csthomas commented Feb 15, 2017

Uh oh!

waader commented Feb 22, 2017

Uh oh!

csthomas commented Feb 22, 2017

Uh oh!

csthomas commented Feb 22, 2017

Uh oh!

csthomas commented Feb 22, 2017 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Uh oh!

csthomas commented Feb 23, 2017

Uh oh!

waader commented Feb 24, 2017

Uh oh!

csthomas commented Feb 24, 2017

Uh oh!

csthomas commented Feb 24, 2017

Uh oh!

wilsonge commented Feb 25, 2017

Uh oh!

csthomas commented Feb 25, 2017

Uh oh!

photodude commented Feb 25, 2017

Uh oh!

Reviewers

Assignees

Labels

Projects

Milestone

Development

Uh oh!

6 participants

csthomas commented Dec 18, 2016 •

edited

Loading

csthomas commented Feb 14, 2017 •

edited

Loading

waader commented Feb 14, 2017 •

edited

Loading

csthomas commented Feb 22, 2017 •

edited

Loading