? Success
Pull Request for # 6054

User tests: Successful: Unsuccessful:

avatar alikon
alikon
13 Feb 2015

2nd error fix: removed the duplicated field "a.node" from the select list
as reported #6054 2º Error: Delete 'a.note', because have 2 in "SELECT" and this can provoke a crash in MSSQL.


Steps to reproduce the issue

Go to: /index.php?searchword=tag&ordering=newest&searchphrase=all&option=com_search

Expected result

Search Results.

Actual result

1st error

[Microsoft][SQL Server Native Client 11.0][SQL Server] Column 'yawcx_contact_details.name' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. SQL=SELECT * FROM ( SELECT a.name AS title, '' AS created, a.con_position, a.misc, CASE WHEN DATALENGTH(a.alias) != 0 THEN (CAST(a.id as NVARCHAR(10))+':'+a.alias) ELSE CAST(a.id as NVARCHAR(10)) END as slug, CASE WHEN DATALENGTH(c.alias) != 0 THEN (CAST(c.id as NVARCHAR(10))+':'+c.alias) ELSE CAST(c.id as NVARCHAR(10)) END as catslug, (a.name+','+a.con_position+','+a.misc) AS text,('Contacts'+' / '+c.title) AS section,'2' AS browsernav , ROW_NUMBER() OVER (ORDER BY a.name DESC) AS RowNumber FROM yawcx_contact_details AS a INNER JOIN yawcx_categories AS c ON c.id = a.catid WHERE (a.name LIKE '%tag%' OR a.misc LIKE '%tag%' OR a.con_position LIKE '%tag%' OR a.address LIKE '%tag%' OR a.suburb LIKE '%tag%' OR a.state LIKE '%tag%' OR a.country LIKE '%tag%' OR a.postcode LIKE '%tag%' OR a.telephone LIKE '%tag%' OR a.fax LIKE '%tag%') AND a.published IN (1,2) AND c.published=1 AND a.access IN (1,1,2) AND c.access IN (1,1,2) GROUP BY a.id, a.con_position, a.misc, c.alias, c.id ) _myResults WHERE RowNumber BETWEEN 1 AND 50

2nd error

[Microsoft][SQL Server Native Client 11.0][SQL Server]The column 'note' was specified multiple times for '_myResults'. SQL=SELECT * FROM ( SELECT a.id, a.title, a.alias, a.note, a.published, a.access, a.checked_out, a.checked_out_time, a.created_user_id, a.path, a.parent_id, a.level, a.lft, a.rgt, a.language, a.created_time AS created, a.note, a.description, CASE WHEN DATALENGTH(a.alias) != 0 THEN (CAST(a.id as NVARCHAR(10))+':'+a.alias) ELSE CAST(a.id as NVARCHAR(10)) END as slug , ROW_NUMBER() OVER (ORDER BY a.created_time DESC) AS RowNumber FROM yawcx_tags AS a WHERE a.alias <> 'root' AND (a.title LIKE '%tag%' OR a.alias LIKE '%tag%') AND a.access IN (1,1,2) ) _myResults WHERE RowNumber BETWEEN 1 AND 50

System information (as much as possible)

IIS
MSSQL
Joomla 3.3.6

Additional comments

1º Error: group clause a.name, a.alias, c.title because GROUP BY is diferent in MySQL.
(/plugin/search/contacts/contacts.php)
2º Error: Duplicate 'a.note', 2 times in "SELECT" and this can provoke a crash in MSSQL.
(/plugin/search/tags/tags.php)

avatar alikon alikon - open - 13 Feb 2015
avatar joomla-cms-bot joomla-cms-bot - change - 13 Feb 2015
Labels Added: ?
avatar zero-24 zero-24 - change - 13 Feb 2015
Rel_Number 6054
Relation Type Pull Request for
Easy No Yes
avatar zero-24 zero-24 - change - 13 Feb 2015
Category MS SQL
avatar zero-24 zero-24 - change - 13 Feb 2015
Category MS SQL IIS MS SQL Search
avatar zero-24 zero-24 - change - 13 Feb 2015
The description was changed
avatar alikon alikon - reference | - 14 Feb 15
avatar alikon
alikon - comment - 14 Feb 2015

for the 1st error
If the relation between contact_details <-> categories is 1:1 then
the group by is unnecesary

avatar waader
waader - comment - 16 Feb 2015

Thanks alikon! Your patch corrects the two problems and makes visible a third one in the search-content pluging:

42000 [Microsoft][SQL Server Native Client 11.0][SQL Server]Column 'e73fz_content.language' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.SQL=SELECT a.title AS title, a.metadesc, a.metakey, a.created AS created, a.language, a.catid,(a.introtext+a.fulltext) AS text,c.title AS section, CASE WHEN DATALENGTH(a.alias) != 0 THEN (CAST(a.id as NVARCHAR(10))+':'+a.alias) ELSE CAST(a.id as NVARCHAR(10)) END as slug, CASE WHEN DATALENGTH(c.alias) != 0 THEN (CAST(c.id as NVARCHAR(10))+':'+c.alias) ELSE CAST(c.id as NVARCHAR(10)) END as catslug, '2' AS browsernav , ROW_NUMBER() OVER (ORDER BY a.created DESC) AS RowNumber FROM e73fz_content AS a INNER JOIN e73fz_categories AS c ON c.id=a.catid WHERE ((LOWER(a.title) LIKE LOWER('%testfest%') OR LOWER(a.introtext) LIKE LOWER('%testfest%') OR LOWER(a.fulltext) LIKE LOWER('%testfest%') OR LOWER(a.metakey) LIKE LOWER('%testfest%') OR LOWER(a.metadesc) LIKE LOWER('%testfest%'))) AND a.state=1 AND c.published = 1 AND a.access IN (1,1) AND c.access IN (1,1) AND (a.publish_up = '1900-01-01 00:00:00' OR a.publish_up <= '2015-02-16 13:23:12') AND (a.publish_down = '1900-01-01 00:00:00' OR a.publish_down >= '2015-02-16 13:23:12') GROUP BY a.id, a.title, a.metadesc, a.metakey, a.created, a.introtext, a.fulltext, c.title, a.alias, c.alias, c.id

So line 181 in content.php should be chanced from
->group('a.id, a.title, a.metadesc, a.metakey, a.created, a.introtext, a.fulltext, c.title, a.alias, c.alias, c.id')
to
->group('a.id, a.title, a.metadesc, a.metakey, a.created, a.language, a.catid, a.introtext, a.fulltext, c.title, a.alias, c.alias, c.id')

Can you add that or should I make a seperate patch?

avatar alikon
alikon - comment - 17 Feb 2015

@waader i've added your suggested tip for search content plugin to this PR can you test?

avatar waader
waader - comment - 17 Feb 2015

@test works on mssql, postgresql and mysql! Thanks!

avatar waader waader - test_item - 17 Feb 2015 - Tested successfully
avatar seagul30
seagul30 - comment - 17 Feb 2015

@test works fine in mysql (sorry, cannot test mssql and postgresql)

avatar zero-24 zero-24 - alter_testresult - 20 Oct 2015 - seagul30: Tested successfully
avatar zero-24 zero-24 - alter_testresult - 20 Oct 2015 - waader: Tested successfully
avatar zero-24 zero-24 - change - 20 Oct 2015
Milestone Added:
Status Pending Ready to Commit
avatar zero-24
zero-24 - comment - 20 Oct 2015

RTC. Sorry for taking so long to RTC :(


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

avatar zero-24 zero-24 - change - 20 Oct 2015
Milestone Added:
avatar joomla-cms-bot joomla-cms-bot - change - 20 Oct 2015
Labels Added: ?
avatar rdeutz rdeutz - close - 20 Oct 2015
avatar joomla-cms-bot joomla-cms-bot - close - 20 Oct 2015
avatar rdeutz rdeutz - reference | 16cb493 - 20 Oct 15
avatar rdeutz rdeutz - merge - 20 Oct 2015
avatar rdeutz rdeutz - close - 20 Oct 2015
avatar rdeutz rdeutz - change - 20 Oct 2015
Status Ready to Commit Closed
Closed_Date 0000-00-00 00:00:00 2015-10-20 15:41:09
Closed_By rdeutz
avatar joomla-cms-bot joomla-cms-bot - change - 20 Oct 2015
Labels Removed: ?
avatar alikon alikon - head_ref_deleted - 20 Oct 2015
avatar zero-24 zero-24 - change - 28 Oct 2015
Milestone
avatar zero-24 zero-24 - change - 28 Oct 2015
Milestone Added:
avatar zero-24 zero-24 - change - 28 Oct 2015
Milestone Added:
avatar zero-24 zero-24 - change - 28 Oct 2015
Milestone

Add a Comment

Login with GitHub to post a comment