Joomla! Issue Tracker | Joomla! CMS #8307 - Smartsearch: collation issue when searching in back-end index

Closed
9 Nov 2015
Medium
Build: staging
# 8307
Diff
infograf768:finder_indexdate

Success

Success Human Test Results: 2 Successful 0 Failed. Details

User tests: Successful: Unsuccessful:

infograf768
7 Nov 2015

This solves #8298

Create an article with title in Hebrew
ביו
then purge and run smartsearch indexer (I used snowball as indexer)
When done, enter the title in the search box (still in the indexer page):

we get

500 Illegal mix of collations for operation 'like' SQL=SELECT COUNT() FROM `#__finder_links` AS l INNER JOIN `#__finder_types` AS t ON t.id = l.type_id WHERE l.type_id = 4 AND l.title LIKE '%ביו%' OR l.url LIKE '%ביו%' OR l.indexdate LIKE '%ביו%' Illegal mix of collations for operation 'like' SQL=SELECT l.,t.title AS t_title FROM `#__finder_links` AS l INNER JOIN `#__finder_types` AS t ON t.id = l.type_id WHERE l.type_id = 4 AND l.title LIKE '%ביו%' OR l.url LIKE '%ביו%' OR l.indexdate LIKE '%ביו%' LIMIT 0, 20 Illegal mix of collations for operation 'like' SQL=SELECT COUNT() FROM `#__finder_links` AS l INNER JOIN `#__finder_types` AS t ON t.id = l.type_id WHERE l.type_id = 4 AND l.title LIKE '%ביו%' OR l.url LIKE '%ביו%' OR l.indexdate LIKE '%ביו%' Illegal mix of collations for operation 'like' SQL=SELECT COUNT() FROM `#__finder_links` AS l INNER JOIN `#__finder_types` AS t ON t.id = l.type_id WHERE l.type_id = 4 AND l.title LIKE '%ביו%' OR l.url LIKE '%ביו%' OR l.indexdate LIKE '%ביו%'

the issue comes from the fact that the search query includes the indexdate while it is anyway useless here.
See: http://stackoverflow.com/questions/18629094/illegal-mix-of-collations-for-operation-like-while-searching-with-ignited-data for explanation.

After getting the 500, logout, login again and patch.
try again.
You should get :

bf3c49e 7 Nov 2015

Search does not need indexdate

infograf768 - open - 7 Nov 2015

infograf768 - change - 7 Nov 2015

Status

New

⇒

Pending

joomla-cms-bot - change - 7 Nov 2015

Labels

Added: ?

pe7er - test_item - 7 Nov 2015 - Tested successfully

pe7er - comment - 7 Nov 2015

I have tested this item successfully on bf3c49e

I was able to reproduce the error. This PR fixes it. Thanks JM!

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

dgt41 - test_item - 7 Nov 2015 - Tested successfully

dgt41 - comment - 7 Nov 2015

I have tested this item successfully on bf3c49e

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

infograf768 - change - 7 Nov 2015

Category

⇒

Administration Components

infograf768 - change - 7 Nov 2015

Status

Pending

⇒

Ready to Commit

infograf768 - comment - 7 Nov 2015

2 good test: RTC

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

joomla-cms-bot - change - 7 Nov 2015

Labels

Added: ?

zero-24 - change - 7 Nov 2015

Milestone

Added:

wilsonge - comment - 7 Nov 2015

@infograf768 if you just remove the like rather than the whole query does it work? Iirc you can't use LIKE for a database date time object. And it would still allow filtering by date for those who want it (sorry travelling on a train so can't test myself to see if it would work)

infograf768 - comment - 7 Nov 2015

@wilsonge
We just do not need that query there. No need to go further imho.

infograf768 - comment - 7 Nov 2015

I meant we do not need ' OR l.indexdate LIKE ' . $search which is only part of the query

infograf768 - comment - 7 Nov 2015

oh, you mean just letting `' OR l.indexdate'). Hmm...
I do not see how anyone would need that in the back-end search...

wilsonge - comment - 7 Nov 2015

I mean like OR l.indexdate = $search yeah

I'm not sure either. But if there is a use case at least it would still work?

infograf768 - comment - 7 Nov 2015

If someone hacks core admin it is his/her problem

wilsonge - comment - 7 Nov 2015

It's not a hack! This is a thing that can be used at the moment?

infograf768 - comment - 7 Nov 2015

The only way to keep a search by date is to preg_match $search to make sure it does not contain unicode characters.
One can indeed search by 2015-11-07 in that case and this whatever the date format for the language used in back-end (in French the column would show 7/11/15 but it would not fit the indexdate in the column.
I doubt anybody would have ever used that in the Indexer page...
Anyway, this would work

        // Check the search phrase.
        if ($this->getState('filter.search') != '')
        {
            $search = $db->quote('%' . str_replace(' ', '%', $db->escape(trim($this->getState('filter.search')), true) . '%'));

            // Do not filter by indexdate if $search contains non-ascii characters
            if (preg_match('/[^\x00-\x7F]/', $search))
            {
                $query->where('l.title LIKE ' . $search . ' OR l.url LIKE ' . $search);
            }
            else
            {
                $query->where('l.title LIKE ' . $search . ' OR l.url LIKE ' . $search . ' OR l.indexdate LIKE  ' . $search);
            }
        }

Would you be satisfied with this?

00f2a62 7 Nov 2015

Allowing search by date

joomla-cms-bot - comment - 7 Nov 2015

This PR has received new commits.

CC: @dgt41, @pe7er

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