Jump to content

Unknown column 'Relevance' in 'order clause


keat

Recommended Posts

Back to this troublesome V3 to 6.1.5 site.

I've noticed the first search for a product code comes back empty.

 

with an error in debug

[1] 1: Unknown column 'Relevance' in 'order clause' [ERROR - CACHE WRITE]

 

[16] SELECT I.* FROM CubeCart_inventory AS I LEFT JOIN (SELECT product_id, MAX(price) as price, MAX(sale_price) as sale_price FROM CubeCart_pricing_group WHERE group_id = 0 GROUP BY product_id) as G ON G.product_id = I.product_id WHERE I.product_id IN (SELECT product_id FROM `CubeCart_category_index` as CI INNER JOIN CubeCart_category as C where CI.cat_id = C.cat_id AND C.status = 1) AND I.status = 1 AND (I.name RLIKE '[[::]]' OR I.description RLIKE '[[::]]' OR I.product_code RLIKE '[[::]]') ORDER BY Relevance DESC LIMIT 12 OFFSET 0 -- (0.000586986541748 sec) [CACHE WRITE]

 

Any subsequent searches for that product_code are OK.

Any pointers ?

 

Link to comment
Share on other sites

With it being different, I wouldn't know which lines to add or take away.

I also have a live 6.1.5 site and get a similar issue, only the error in debug is a little different.

This live site (IIRC) is untouched virgin site.

[13] INSERT INTO `CubeCart_system_error_log` (`message`,`time`) VALUES ('File: [catalogue.class.php] Line: [1760] \"SELECT I.* FROM CubeCart_inventory AS I LEFT JOIN (SELECT product_id, MAX(price) as price, MAX(sale_price) as sale_price FROM CubeCart_pricing_group WHERE group_id = 0 GROUP BY product_id) as G ON G.product_id = I.product_id WHERE I.product_id IN (SELECT product_id FROM `CubeCart_category_index` as CI INNER JOIN CubeCart_category as C where CI.cat_id = C.cat_id AND C.status = 1) AND I.status = 1 AND (I.name RLIKE \'[[::]]\' OR I.description RLIKE \'[[::]]\' OR I.product_code RLIKE \'[[::]]\') ORDER BY Relevance DESC LIMIT 12 OFFSET 0 \" - Unknown column \'Relevance\' in \'order clause\'','1487172183'); -- (0.00065803527832 sec) [ERROR - NOT CACHED]
[14] INSERT INTO `CubeCart_system_error_log` (`message`,`time`) VALUES ('File: [catalogue.class.php] Line: [1760] \"SELECT I.* FROM CubeCart_inventory AS I LEFT JOIN (SELECT product_id, MAX(price) as price, MAX(sale_price) as sale_price FROM CubeCart_pricing_group WHERE group_id = 0 GROUP BY product_id) as G ON G.product_id = I.product_id WHERE I.product_id IN (SELECT product_id FROM `CubeCart_category_index` as CI INNER JOIN CubeCart_category as C where CI.cat_id = C.cat_id AND C.status = 1) AND I.status = 1 AND (I.name RLIKE \'[[::]]\' OR I.description RLIKE \'[[::]]\' OR I.product_code RLIKE \'[[::]]\') ORDER BY Relevance DESC LIMIT 12 OFFSET 0 \" - Unknown column \'Relevance\' in \'order clause\'','1487172183'); -- (0.00065803527832 sec) [ERROR - CACHE WRITE]

Live site and troublesome v3 to v6 site are on different servers.

Link to comment
Share on other sites

The query has regular expression syntax (RLIKE), but is using a sorter that should have been rejected once CubeCart entered this phase of searching. Nor is the query actually looking for anything ([[::]]).

I have not seen this in CC614 (have not yet installed CC615).

So, if this site is accessing an existing database, that database may have a code snippet that is not working correctly with this installation.

Link to comment
Share on other sites

I think it might be related to the length of the search string ??

eg, if I search for product_code R16 (it does exist), nothing comes back

If I search for R16-BUTT (it also exists) then results come back.

Also search for AT9, no results, AT97 works.

Could 3 carachters be to few

 

www.crimpconnectors.co.uk is the live site.

Debugging is enabled if anyone wants to see.

Search TR1 and then search TR10

Link to comment
Share on other sites

Searching is in three phases: Relevance, RLIKE, then LIKE. The next phase is employed if no results are found.

Relevance searches the FULLTEXT index of which, by default, the minimum number of letters to use as a search term is four.

But this does not explain why the search term is getting lost when doing the RLIKE phase.

Unless this site is accessing an existing database, which that database may have a code snippet that is not working correctly with this installation.

Link to comment
Share on other sites

9 hours ago, bsmither said:

In phpMyAdmin, run this query:

SHOW VARIABLES LIKE 'ft_min_word_len'

If the answer is not a number, then CubeCart will use four.

CubeCart will use 4 as that is the default MySQL setting

8 hours ago, Dirty Butter said:

My server ft_min_word_len = 3.

We set this one character lower on our servers

Link to comment
Share on other sites

My server ft_min_word_len = 4.

 

However

I've just created a new install of 6.0.10, and added 2 x test products. TP1 and TP10.

I can search and find both.

 

I then updated this to 6.1.5 and get the following error when searching TP1

 

[10] SELECT SQL_CALC_FOUND_ROWS `id`, `hits` FROM `CubeCart_search` WHERE CubeCart_search.searchstr = 'TP1' LIMIT 1; -- (0.000303983688354 sec) [NOT CACHED]
[11] SELECT FOUND_ROWS() as Count; -- (8.98838043213E-5 sec) [NOT CACHED]
[12] UPDATE `CubeCart_search` SET `hits` = '5' WHERE CubeCart_search.id = '1'; -- (0.0791778564453 sec) [NOT CACHED]
[13] SHOW INDEX FROM `CubeCart_inventory`; -- (0.000940084457397 sec) [CACHE WRITE]
[14] SHOW VARIABLES LIKE 'ft_min_word_len' -- (0.000609874725342 sec) [CACHE WRITE]
[15] SELECT I.* FROM CubeCart_inventory AS I LEFT JOIN (SELECT product_id, MAX(price) as price, MAX(sale_price) as sale_price FROM CubeCart_pricing_group WHERE group_id = 0 GROUP BY product_id) as G ON G.product_id = I.product_id WHERE I.product_id IN (SELECT product_id FROM `CubeCart_category_index` as CI INNER JOIN CubeCart_category as C where CI.cat_id = C.cat_id AND C.status = 1) AND I.status = 1 AND (I.name RLIKE '[[::]]' OR I.description RLIKE '[[::]]' OR I.product_code RLIKE '[[::]]') ORDER BY Relevance DESC LIMIT 12 OFFSET 0 -- (0.00070595741272 sec) [CACHE WRITE]
[16] SELECT I.* FROM CubeCart_inventory AS I LEFT JOIN (SELECT product_id, MAX(price) as price, MAX(sale_price) as sale_price FROM CubeCart_pricing_group WHERE group_id = 0 GROUP BY product_id) as G ON G.product_id = I.product_id WHERE I.product_id IN (SELECT product_id FROM `CubeCart_category_index` as CI INNER JOIN CubeCart_category as C where CI.cat_id = C.cat_id AND C.status = 1) AND I.status = 1 AND (I.name RLIKE '[[::]]' OR I.description RLIKE '[[::]]' OR I.product_code RLIKE '[[::]]') -- (0.00115895271301 sec) [CACHE WRITE]

[1] 1: Unknown column 'Relevance' in 'order clause' [ERROR - CACHE WRITE]

 

This is a virgin site, absolutely no mods, snippets or plugins.

The only changes from stock, are the changes to ini.inc.php to get around the PHP 5.4 limilation and config.class from 6.0.12

Something must have changed in 6.1 that wasn't there in 6.0

 

 

Here is the search from a working 6.0.10

[25] SELECT `id`, `hits` FROM `CubeCart_search` WHERE CubeCart_search.searchstr = 'UG3' ; -- (0.0163061618805 sec) [CACHE WRITE]
[26] UPDATE `CubeCart_search` SET `hits` = '1' WHERE CubeCart_search.id = '44901'; -- (0.000336885452271 sec) [NOT CACHED]
[27] SHOW COLUMNS FROM CubeCart_customer_membership; -- (0.000882148742676 sec) [CACHE WRITE]
[28] SELECT `group_id` FROM `CubeCart_customer_membership` WHERE CubeCart_customer_membership.customer_id = '19834' ; -- (0.000330209732056 sec) [CACHE WRITE]
[29] SHOW INDEX FROM `CubeCart_inventory`; -- (0.000654935836792 sec) [CACHE WRITE]
[30] SHOW VARIABLES LIKE 'ft_min_word_len' -- (0.000591039657593 sec) [CACHE WRITE]
[31] SELECT I.* FROM CubeCart_inventory AS I LEFT JOIN (SELECT product_id, MAX(price) as price, MAX(sale_price) as sale_price FROM CubeCart_pricing_group WHERE group_id = 0 GROUP BY product_id) as G ON G.product_id = I.product_id WHERE I.product_id IN (SELECT product_id FROM `CubeCart_category_index` as CI INNER JOIN CubeCart_category as C where CI.cat_id = C.cat_id AND C.status = 1) AND I.status = 1 AND (I.name RLIKE '[[::]]' OR I.description RLIKE '[[::]]' OR I.product_code RLIKE '[[::]]') LIMIT 12 OFFSET 0 -- (0.141498088837 sec) [CACHE WRITE]
[32] SELECT I.* FROM CubeCart_inventory AS I LEFT JOIN (SELECT product_id, MAX(price) as price, MAX(sale_price) as sale_price FROM CubeCart_pricing_group WHERE group_id = 0 GROUP BY product_id) as G ON G.product_id = I.product_id WHERE I.product_id IN (SELECT product_id FROM `CubeCart_category_index` as CI INNER JOIN CubeCart_category as C where CI.cat_id = C.cat_id AND C.status = 1) AND I.status = 1 AND (I.name RLIKE '[[::]]' OR I.description RLIKE '[[::]]' OR I.product_code RLIKE '[[::]]') -- (0.13321018219 sec) [CACHE WRITE]
[33] SELECT SQL_CALC_FOUND_ROWS `array` FROM `CubeCart_config` WHERE CubeCart_config.name = 'category_product_options' LIMIT 1; -- (0.000521183013916 sec) [CACHE WRITE]
[34] SELECT FOUND_ROWS() as Count; -- (0.000123023986816 sec) [NOT CACHED]
 

Link to comment
Share on other sites

It seems that it's something in catalogue.class.php but what.

If I restore catalogue.class.php from V6.0.10 in to the 6.1.5 classes folder, the search works.

If I resore catalogue.class.php back to it's original 6.1.5 version, it stops working again.

 

I've had a little look and tried to compare the two, but it might as well be encrypted.

Link to comment
Share on other sites

I've definately narrowed it down to a change in 6.1.

I'm trying to compare 6.0.12 and 6.1.0, but there are sections which are so different, that i've no chance of figuring it out.

 

Any thoughts where I might comment out the step 'Order by Relevance',

Link to comment
Share on other sites

You can try forcing CubeCart to go straight to the second method. In /classes/catalogue.class.php, near line 1571:

From:
public function searchCatalogue($search_data = null, $page = 1, $per_page = 10, $search_mode = 'fulltext') {

To:
public function searchCatalogue($search_data = null, $page = 1, $per_page = 10, $search_mode = 'RLIKE') {

However, as seen earlier, it appears that the search gets to be using RLIKE, but is missing search terms. So, if this edit doesn't smooth that out, then it must be the case that a code snippet is interfering.

Link to comment
Share on other sites

That seems to have fixed the issue.

However, having said that, I'd already tested with catalogue.class from 6.0.12, and the search worked.

The string in catalogue.class is also:       public function searchCatalogue($search_data = null, $page = 1, $per_page = 10, $search_mode = 'fulltext') {

So whilst changing the entry from 'fulltext' to 'RLIKE' appears to have solved the issue, it must be something else that's actually causing it.

Link to comment
Share on other sites

I restored 'fulltext' and then removed '.$order_string.'  (around line 1816), and this also seems to have fixed the issue.

 

So I guess we now need to understand what it does and why is it in there ?

 

And just as importantly, why is it only me who has this problem ?

 

Link to comment
Share on other sites

It was added because otherwise, there would be no sorting at all. But I think the Relevance is coming from the sorter choice on the Advanced Search page.

This part of a query, as it appears in CubeCart's debug section:
(I.name RLIKE '[[::]]' OR I.description RLIKE '[[::]]' OR I.product_code RLIKE '[[::]]')

is supposed to look like this:
(I.name RLIKE '[[:<:]]search_term[[:>:]]' OR I.description RLIKE '[[:<:]]search_term[[:>:]]' OR I.product_code RLIKE '[[:<:]]search_term[[:>:]]')

But this part:
<:]]search_term[[:>
is getting stripped out.

The "tag" (surrounded by angles) is getting stripped off in Debug->debugSQL(). (I've argued for removing that, but it is still there.) So, the search term in the query should be getting to the database, it is just that we will not see this in the debug section. We can probably drop the examination of whether the search term, itself, is at fault.

The code is supposed to not use relevance if the search method starts at 'fulltext', and then falls through to the next method. If 'Relevance', the sort is supposed to switch to using the admin-set default sort parameters.

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...