date query issue on sqlite

[dbu]

via @rmsint

Using dbal together with sqlite gives no results for a query using the date literal. 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

[cryptocompress]

Seems xpath converts date-values to "number" on comparison, so it's NaN.

• A string that consists of optional white space followed by an optional minus sign followed by a number followed by white space is converted to the IEEE 754 number that is nearest (according to the IEEE 754 round-to-nearest rule) to the mathematical value represented by the string; any other string is converted to NaN.

In xpath2 there is a function "xs:dateTime" for this.
Here is a hack for xpath1 ("2014-11-11T14:50:35.000+01:00" format missing!):

<?php

$string = '<?xml version="1.0" encoding="UTF-8"?>
<sv:node xmlns:xml="http://www.w3.org/XML/1998/namespace" xmlns:mix="http://www.jcp.org/jcr/mix/1.0" xmlns:nt="http://www.jcp.org/jcr/nt/1.0" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:jcr="http://www.jcp.org/jcr/1.0" xmlns:sv="http://www.jcp.org/jcr/sv/1.0" xmlns:rep="internal">

    <sv:property><sv:value>2013-11-11T14:50:35+01:00</sv:value></sv:property>
    <sv:property><sv:value>2014-11-11T14:50:35+01:00</sv:value></sv:property>
    <sv:property><sv:value>2015-11-11T14:50:35+01:00</sv:value></sv:property>

    <sv:property><sv:value>2003-11-11T14:50:35-01:00</sv:value></sv:property>
    <sv:property><sv:value>2004-11-11T14:50:35-01:00</sv:value></sv:property>
    <sv:property><sv:value>2005-11-11T14:50:35-01:00</sv:value></sv:property>

    <sv:property><sv:value>1004-11-11T14:50:35-01:00</sv:value></sv:property>
    <sv:property><sv:value>3004-11-11T14:50:35-01:00</sv:value></sv:property>

</sv:node>';

#<sv:property><sv:value>1005-11-11T14:50:35.000-01:00</sv:value></sv:property>

#$expression = '//*[text()="2014-11-11T14:50:35+01:00"]'; // working
#$expression = '//*[text()<"2014-11-11T14:50:35+01:00"]'; // not working
#$expression = '//*[xs:dateTime(text())<xs:dateTime("2014-11-11T14:50:35+01:00")]'; // not working (xpath 2.0)
#$expression = '//*[number(translate(substring(text(), 1, 10), "-", ""))>="20141111"]'; // need ISO-8601-to-number

$expression = '//*[
    concat(
        translate(substring-before(text(), "T"), "-", ""),
        translate(substring(substring-after(text(), "T"), 1, string-length(substring-after(text(), "T")) - 6), ":", "")
        +
        concat(number(translate(translate(substring(text(), string-length(text()) - 5, 6), "+", ""), ":", "")), "00")
    ) > "20141111155035"
]'; // ISO-8601-to-number


$dom = new \DOMDocument('1.0', 'UTF-8');
$dom->loadXML($string);
$xpath = new \DOMXPath($dom);
$r = $xpath->evaluate($expression);

var_dump($r->length);
for ($i = 0; $i < $r->length; $i++) {
    var_dump($r->item($i)->nodeValue);
}

Somewhat clumsy and inefficient but i don't see another way.
What do you think?

[dbu]

why is that working on postgres and mysql then, but not sqlite? do we rely on a not standards conformant xpath implementation?

if you could do a PR to use your xslt that would be great. if you can make sure we have tests that cover this, we can try only activating it for sqlite and see if mysql and postgres really don't need it.

[cryptocompress]

Can't do it currently. Will do it some time later. Sorry!

[dbu]

is this still broken? i would assume that the phpcr-api-tests would detect the problem, or not?

[dantleech]

Can confirm this issue with both mysql and sqlite (havn't tried postgres):

SELECT n0.path AS n0_path, n0.identifier AS n0_identifier, n0.props AS n0_props 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="lsys:en-created"]/sv:value[text()>"2015-06-23T12:54:56+02:00"]) > 0') AND EXTRACTVALUE(n0.props, 'count(//sv:property[@sv:name="jcr:mixinTypes"]/sv:value[text()="mix:test"]) > 0'));

Where we have:

<?xml version="1.0" encoding="UTF-8"?>
<sv:node xmlns:mix="http://www.jcp.org/jcr/mix/1.0" xmlns:nt="http://www.jcp.org/jcr/nt/1.0" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:jcr="http://www.jcp.org/jcr/1.0" xmlns:sv="http://www.jcp.org/jcr/sv/1.0" xmlns:rep="internal">
  <sv:property sv:name="jcr:primaryType" sv:type="Name" sv:multi-valued="0">
    <sv:value length="15">nt:unstructured</sv:value>
  </sv:property>
  <sv:property sv:name="jcr:mixinTypes" sv:type="Name" sv:multi-valued="1">
    <sv:value length="8">mix:test</sv:value>
  </sv:property>
  <sv:property sv:name="jcr:uuid" sv:type="String" sv:multi-valued="0">
    <sv:value length="36">f38e4619-451a-4079-a933-f44e2250223f</sv:value>
  </sv:property>
  <sv:property sv:name="lcon:en-title" sv:type="String" sv:multi-valued="0">
    <sv:value length="5">Hello</sv:value>
  </sv:property>
  <sv:property sv:name="lcon:en-body" sv:type="String" sv:multi-valued="0">
    <sv:value length="23">Hello this is something</sv:value>
  </sv:property>
  <sv:property sv:name="nsys:my_status" sv:type="String" sv:multi-valued="0">
    <sv:value length="4">open</sv:value>
  </sv:property>
  <sv:property sv:name="lsys:en-created" sv:type="Date" sv:multi-valued="0">
    <sv:value length="29">2015-06-23T11:54:56.000+00:00</sv:value>
  </sv:property>
  <sv:property sv:name="lsys:en-changed" sv:type="Date" sv:multi-valued="0">
    <sv:value length="29">2015-06-23T11:54:56.000+00:00</sv:value>
  </sv:property>
</sv:node>

0 results.