date literal in query invalid

[rmsint]

Creating this query using the QueryBuilder:

$qb = $this->dm->createQueryBuilder();
$qb->from()
    ->document('Symfony\Cmf\Bundle\BlockBundle\Doctrine\Phpcr\SimpleBlock', 'b')
  ->end()
  ->where()
    ->lt()->field('b.createdAt')->literal(new \DateTime())
  ->end()
;

generates this sql-2 query:

SELECT * FROM [nt:unstructured] AS b 
WHERE (b.[jcr:created] < CAST('2013-11-11T14:50:35.000+01:00' AS DATE)
AND (b.[phpcr:class] = 'Symfony\Cmf\Bundle\BlockBundle\Doctrine\Phpcr\SimpleBlock' OR b.[phpcr:classparents] = 'Symfony\Cmf\Bundle\BlockBundle\Doctrine\Phpcr\SimpleBlock'))

Running the query using app/console doctrine:phpcr:workspace:query tells the query is in invalid (PHPCR\Query\InvalidQueryException), and in code returns no results.

Running the same query when using jackalope-jackrabbit is succeeding.

[lsmith77]

can you locate a bit more what triggers the exception? is it triggered during the generation of the sql statement? is it triggered while executing the generated sql? or some where else?

[dbu]

i stumbled over the same, it seems our SQL2 parser does not handle the CAST part, but jackrabbit needs it and will not work when passing a string for a date.

[dbu]

@rmsint is this fixed with what lukas just did on phpcr-utils tonight? phpcr/phpcr-utils#99

[dbu]

ping

[rmsint]

Ah yes, I will do a test somewhere the next days.

[dbu]

hi @rmsint this is the only remaining blocker for releasing 1.1. can you check it maybe this weekend? would be awesome.

[rmsint]

hi @dbu actually I am currently checking it using the symfony-cmf website as test case. Currently the query is not giving an error, however also no results while it should. I have to check it a bit more. The case is that if we have some upcoming talks the query should return the talks that happen after the current date.

[rmsint]

After some more checks imho the date literal issue is solved. However using dbal together with sqlite gives no results for a query using the date literal, I think this caused by another issue. For jackrabbit and dbal mysql the query with a date literal is giving the expected results.

On the cmf-sandbox, this query:

SELECT * FROM [nt:unstructured] AS b 
WHERE (b.[jcr:created] < CAST('2014-11-11T14:50:35.000+01:00' AS DATE) 
AND (b.[phpcr:class] = 'Symfony\Cmf\Bundle\BlockBundle\Doctrine\Phpcr\SimpleBlock' OR b.[phpcr:classparents] = 'Symfony\Cmf\Bundle\BlockBundle\Doctrine\Phpcr\SimpleBlock'))

should give 2 results:

$ app/console doctrine:phpcr:workspace:query "SELECT * FROM [nt:unstructured] AS b WHERE (b.[jcr:created] < CAST('2014-11-11T14:50:35.000+01:00' AS DATE) AND (b.[phpcr:class] = 'Symfony\Cmf\Bundle\BlockBundle\Doctrine\Phpcr\SimpleBlock' OR b.[phpcr:classparents] = 'Symfony\Cmf\Bundle\BlockBundle\Doctrine\Phpcr\SimpleBlock'))"                                                                                                                                                       
Executing, language: JCR-SQL2
Results:

1. Row (Path: /cms/content/home/additionalInfoBlock/child1, Score: 0):
jcr:primaryType: 'b'
jcr:createdBy: 'admin'
jcr:created: '2014-02-01T15:47:29.000+01:00'

2. Row (Path: /cms/content/home/additionalInfoBlock/child2, Score: 0):
jcr:primaryType: 'b'
jcr:createdBy: 'admin'
jcr:created: '2014-02-01T15:47:29.000+01:00'
0.06 seconds

The generated sql is:

#mysql
SELECT n0.id AS n0_id, n0.path AS n0_path, n0.parent AS n0_parent, n0.local_name AS n0_local_name, n0.namespace AS n0_namespace, n0.workspace_name AS n0_workspace_name, n0.identifier AS n0_identifier, n0.type AS n0_type, n0.props AS n0_props, n0.depth AS n0_depth, n0.sort_order AS n0_sort_order 
FROM phpcr_nodes n0 
WHERE n0.workspace_name = 'default'
AND n0.type IN ('nt:unstructured', 'rep:root') 
AND (EXTRACTVALUE(n0.props, 'count(//sv:property[@sv:name=\"jcr:created\"]/sv:value[text()<\"2014-11-11T14:50:35+01:00\"]) > 0') 
AND (EXTRACTVALUE(n0.props, 'count(//sv:property[@sv:name=\"phpcr:class\"]/sv:value[text()=\"Symfony\\\\Cmf\\\\Bundle\\\\BlockBundle\\\\Doctrine\\\\Phpcr\\\\SimpleBlock\"]) > 0') 
OR EXTRACTVALUE(n0.props, 'count(//sv:property[@sv:name=\"phpcr:classparents\"]/sv:value[text()=\"Symfony\\\\Cmf\\\\Bundle\\\\BlockBundle\\\\Doctrine\\\\Phpcr\\\\SimpleBlock\"]) > 0')))

#sqlite
SELECT n0.id AS n0_id, n0.path AS n0_path, n0.parent AS n0_parent, n0.local_name AS n0_local_name, n0.namespace AS n0_namespace, n0.workspace_name AS n0_workspace_name, n0.identifier AS n0_identifier, n0.type AS n0_type, n0.props AS n0_props, n0.depth AS n0_depth, n0.sort_order AS n0_sort_order 
FROM phpcr_nodes n0 
WHERE n0.workspace_name = 'default'
AND n0.type IN ('nt:unstructured', 'rep:root') 
AND (EXTRACTVALUE(n0.props, 'count(//sv:property[@sv:name=\"jcr:created\"]/sv:value[text()<\"2014-11-11T14:50:35+01:00\"]) > 0') 
AND (EXTRACTVALUE(n0.props, 'count(//sv:property[@sv:name=\"phpcr:class\"]/sv:value[text()=\"Symfony\\Cmf\\Bundle\\BlockBundle\\Doctrine\\Phpcr\\SimpleBlock\"]) > 0') 
OR EXTRACTVALUE(n0.props, 'count(//sv:property[@sv:name=\"phpcr:classparents\"]/sv:value[text()=\"Symfony\\Cmf\\Bundle\\BlockBundle\\Doctrine\\Phpcr\\SimpleBlock\"]) > 0')))

The difference is that:

• the escaping is different, I read in the code that this is supposed to be
• and the registered EXTRACTVALUE function for sqlite returns false for the xpath expression of the date, here: https://github.com/jackalope/jackalope-doctrine-dbal/blob/master/src/Jackalope/Transport/DoctrineDBAL/Client.php#L202

[dbu]

thanks a lot. i created #174 to follow this up.