SQL2 queries need to quote searches on integer fields with SQLite

[lsmith77]

The following node

$node = $root->addNode('foo');
$node->setProperty('count', 10);

Will not be found with this query:

$sql = "SELECT * FROM [nt:unstructured] WHERE count = 10";
$query = $qm->createQuery($sql, \PHPCR\Query\QueryInterface::JCR_SQL2);

But it will be found with:

$sql = "SELECT * FROM [nt:unstructured] WHERE count = '10'";
$query = $qm->createQuery($sql, \PHPCR\Query\QueryInterface::JCR_SQL2);

[lsmith77]

Note for Jackrabbit its the other way around.

[dbu]

so the xpath queries make all numbers strings? maybe we need to build that into our query converter, to but quotes around all literals even when they are numeric. if the semantics of count = 10 is to only find integer fields but not string fields called "count" we would need to do an additional check on the type in the xpath.

[lsmith77]

it seems indeed that the semantics of count = 10 is to find only integer fields. for all other cases one is supposed to use CONTAINS() I guess.

[dbu]

@lsmith77 so we close this as well if we give up on #207?

[lsmith77]

no .. imho we can now do this much easier since we have a separate column for the numeric values

[dbu]

@lsmith77 any chance you could fix this? this is one of the few issues left in milestone 1.2...

[lsmith77]

well I had a fix .. the issue is that I was unable to demonstrate the problem in a test.=

[dbu]

so, what do we do? drop the issue, postpone it or apply your fix if we can also prove that it does not do any harm?

[wachterjohannes]

@lsmith77 @dbu i have a similar failure in sulu. We query for tags which are saved as an integer array in a property. we can filter (in a sql2 statement where) with tags = 1 or tags = 2. which works very well as long as only one tag is assigned to a page. when we assign two tags (1, 2) on a page and trying to filter for the first tag (1) we get the right result and we are happy ... but when we filter for the second tag (2) then we dont get the page ... when we reverse the assignment and write (2,1) it works for the tag 2 but not for tag 1 ...

i have talked to @dantleech this morning and he said i should place it into apostrophes and everything works fine!

I will test it for jackrabbit but i think it is not a good solution, or is it this?

[lsmith77]

ok .. could be related. if you can manage to come up with a test case, then I am sure we can fix it.

[wachterjohannes]

@dantleech have found the issue and it is not related to this PR ... thanks!

[dbu]

@lsmith77 do we consider this a blocker for 1.2?

[lsmith77]

no .. I do not consider this a blocker .. at least it has only popped up as an issue in the benchmark. nobody else so far seems to have run into this.