In the PHPCR implementation using Doctrine DBAL we support search queries by converting the SQL2/QOM statements into XPath queries that we run on the XML stored in an RDBMS. Sounds insane, yes .. but it works pretty well .. obviously will not scale very well .. but it works for smaller data sets and there will be ways to improve performance later. In terms of functionality we have everything working quite well including JOIN support that was added recently.
However we are stuck with multivalue queries. What makes it particularly tricky is that we do not know which properties are multivalue and which are not as illustrated by these tests.
As you can see in the test we have a multivalue property "tags" and in the SQL2 statement we want to test if that multivalue property contains both "foo" and "bar". However inside the query engine we do not know that this property is multivalued or not.
The relevant code is in QOMWalker:: sqlXpathValueExists() and QOMWalker:: sqlXpathExtractValue(). As you can see it currently only looks at "sv:value".
You can find some more information here:
Just for the record. Liip founder Chregu proposed an approach in the ticket which Willem-Jan has worked into a patch.