Item10856: Use of LC in SEARCH String causing a $where compile error

I found that ANDing a lower case conversion filter to a SEARCH causes a $where compile error.

%SEARCH{"slvalues[property~'* *' AND lc('%SPACEOUT{%TOPIC%}%')=lc(property)]"
type="query"
web="Mangroves"
header="---++Properties with whitespace"
format="   * [[$web.$topic][$topic]] has these invalid property names:  $percntFORMAT{
   \"$percntQUERY{'$web.$topic'/slvalues[property~'* *'].property}$percnt\"
   type=\"string\" 
   header=\"$n\"
   format=\"      * $dollaritem\"}$percnt"
}%

-- PaulAlexander - 08 Jun 2011

This is waiting on us to reproduce the problem with latest MongoDBPlugin

-- PaulHarvey - 08 Jun 2011

I got this mongo thing working, yay, and have a search on a form.name='someform' AND lc(text)=~lc(urlparam). Works and no error in mongod.log. This is Ubuntu 10.04.2 with mongo from ubuntu repos. Running Foswiki trunk.

-- LarsEik - 16 Jun 2011

Cool, I'm glad somebody is trying it out

Just a little heads-up: Mongo cannot use its indexes to resolve regex ( =~ ) and 'like' ( ~ ) queries, and although it's still fast on < 3000-ish topics, at 10k, 30k topics - especially nested searches - are taking seconds to answer if regex/like operators are involved. Whereas if you can avoid them, pages are still returning well inside a second even on 25k topics.

For example, form.name~'*MyForm' is better re-written as form.name='MyForm' OR form.name='TheWeb.MyForm' (actually, we define a little %QFORM{"MyForm"}% ParameterizedVariables to generate the OR expressions for us).

Finally, and this might be nothing more than superstition - but when I can't avoid a regex term in the query, I re-arrange it so that it's last in the expression (i.e. "cheapest" operators on the left). For example: form.name='MyForm' AND State!='Foo' AND lc(Product)=~lc(urlparam). You'd think the mongodb query planner would automatically sort this out for us, but sometimes I think it doesn't do very well when some parts of the query are converted to javascript

-- PaulHarvey - 16 Jun 2011

Thanks for the tips, it a hard to keep up on trunk when not being a developer my self. So far MongoDBPlugin works out reasonably well on my test server. I try to port over all DBCache stuff etc.
About this lc() stuff I have a query that works if I use lc(formfield) =~ lc(String) but not when I don't use lc(), even when I know the string matches.... How can that be?
The query is "form.name='SomeForm' AND ( lc(Field) =~ lc('Valuea') OR lc(Field) =~ lc('Valueb') )"
Removing lc() and it stops working. The Valuea is plain string with capital first letter so it's should match with or without lc(). I have enabled experimental/hoist thing in configure.

-- LarsEik - 20 Jun 2011

form.name='SomeForm' AND ( Field =~ 'Valuea' OR Field =~ 'Valueb' ) should really work... so if you switch to BruteForce and Forking/PurePerl it works?

-- PaulHarvey - 20 Jun 2011

Yes, BruteForce and Pure perl/Forking works. It's only with mongodb the issue exist. I also disabled experimental mongodb code, no dice. I hope you can reproduce the same symptom.

-- LarsEik - 20 Jun 2011

Another little thing, I'm not sure if it should be reported as a new bug. URLPARAM doesn't seem to work atm. I have another test server where the only difference is that last update was around June 9. URLPARAM works there. That has nothing todo with the lc() issue.

-- LarsEik - 20 Jun 2011

That's strange. Please feel free to register on our wiki at http://trin.org.au and I will give you Sandbox access, if you are interested in comparing behaviour.

It would also help if you could run the unit tests, so we can compare output. That might help narrow down the problem.

I usually do something like this:

cd /path/to/core/test/unit
# Full test run
sudo -u www-data ../bin/TestRunner.pl FoswikiSuite
# Just the QueryTests
sudo -u www-data ../bin/TestRunner.pl QueryTests
# Just the SEARCH tests
sudo -u www-data ../bin/TestRunner.pl Fn_SEARCH

For reference, I am not experiencing the problems you describe, using core distro:810665813be3 and MongoDBPlugin MongoDBPlugin:2e61b9872a71

-- PaulHarvey - 22 Jun 2011

Set WaitingFor Lars - I think this can be closed (it works for us anyway)

-- PaulHarvey - 16 Jul 2011

Something is wrong with my test server. It will be recycled in a while.

-- LarsEik - 28 Jul 2011

Commenting is disabled while not logged in