sqlite does not support RIGHT JOIN: problem with child translation strategy query

a couple of tests fail when running jackalope-doctrine-dbal with sqlite instead of postgres / mysql

the problem is with ChildTranslationStrategy::alterQueryForTranslation that does a QueryObjectModelConstantsInterface::JCR_JOIN_TYPE_RIGHT_OUTER to join a node with its translation child node.

Doctrine\DBAL\Exception\DriverException: An exception occurred while executing 'SELECT n0.path AS n0_path, n0.identifier AS n0_identifier, n0.props AS n0_props, n1.path AS n1_path, n1.identifier AS n1_identifier, n1.props AS n1_props FROM phpcr_nodes n0 RIGHT JOIN phpcr_nodes n1 ON ( n0.workspace_name = n1.workspace_name AND n1.type IN ('nt:base', 'nt:unstructured', 'rep:root', 'phpcr:cms_profile', 'nt:activity', 'nt:address', 'nt:childNodeDefinition', 'nt:configuration', 'nt:frozenNode', 'nt:hierarchyNode', 'nt:file', 'nt:folder', 'nt:linkedFile', 'rep:Authorizable', 'rep:Group', 'rep:User', 'rep:AuthorizableFolder', 'test:protected_property_test', 'test:protected_property_test2', 'nt:nodeType', 'nt:propertyDefinition', 'nt:query', 'nt:resource', 'nt:version', 'nt:versionHistory', 'nt:versionLabels', 'nt:versionedChild', 'rep:ACE', 'rep:DenyACE', 'rep:GrantACE', 'rep:AccessControl', 'rep:PrincipalAccessControl', 'rep:Activities', 'rep:Configurations', 'rep:Members', 'rep:Policy', 'rep:ACL', 'rep:nodeTypes', 'rep:system', 'rep:versionStorage', 'phpcr:managed') AND (n1.path LIKE CONCAT(n0.path, '/%') AND n1.depth = n0.depth + 1) ) WHERE n0.workspace_name = ? AND n0.type IN ('nt:unstructured', 'rep:root', 'phpcr:cms_profile') AND ((EXTRACTVALUE(n1.props, 'count(//sv:property[@sv:name="topic"]/sv:value[text()="Not Exist"]) > 0') AND (EXTRACTVALUE(n0.props, 'count(//sv:property[@sv:name="phpcr:class"]/sv:value[text()="Doctrine\Tests\Models\Translation\ChildTranslationArticle"]) > 0') OR EXTRACTVALUE(n0.props, 'count(//sv:property[@sv:name="phpcr:classparents"]/sv:value[text()="Doctrine\Tests\Models\Translation\ChildTranslationArticle"]) > 0'))) AND n1.namespace || (CASE n1.namespace WHEN '' THEN '' ELSE ':' END) || n1.local_name = 'http://www.doctrine-project.org/projects/phpcr_odm/phpcr_locale:en')' with params ["default"]:

SQLSTATE[HY000]: General error: 1 RIGHT and FULL OUTER JOINs are not currently supported

options i see:
- ignore - the problem has been present since 14.2.2014
- try to find a solution on the jackalope-doctrine-dbal level to work around the limitation
- try to detect the problem in phpcr-odm and output a more specific warning (use other database or attribute translation)

also, should we run the tests with sqlite and postgres to detect such issues?

Start with ChildTranslationStrategy::alterQueryForTranslation and the failing SQLite tests described in the issue; compare the generated RIGHT JOIN with SQLite, PostgreSQL, and MySQL behavior. Determine whether the fix belongs in jackalope-doctrine-dbal or phpcr-odm, and treat the issue's proposed workaround, warning, or cross-database test coverage as the completion criterion.

Written by the indexing model from the issue text.