--- a/server/rqlannotation.py	Wed Jul 27 14:17:47 2011 +0200
+++ b/server/rqlannotation.py	Wed Jul 27 14:22:32 2011 +0200
@@ -211,16 +211,22 @@
     relation for the rhs variable
     """
     principal = None
+    others = []
     # sort for test predictability
     for rel in sorted(relations, key=lambda x: (x.children[0].name, x.r_type)):
         # only equality relation with a variable as rhs may be principal
         if rel.operator() not in ('=', 'IS') \
                or not isinstance(rel.children[1].children[0], VariableRef) or rel.neged(strict=True):
             continue
+        if rel.optional:
+            others.append(rel)
+            continue
         if rel.scope is rel.stmt:
             return rel
         principal = rel
     if principal is None:
+        if others:
+            return others[0]
         raise BadRQLQuery('unable to find principal in %s' % ', '.join(
             r.as_string() for r in relations))
     return principal

--- a/server/sources/rql2sql.py	Wed Jul 27 14:17:47 2011 +0200
+++ b/server/sources/rql2sql.py	Wed Jul 27 14:22:32 2011 +0200
@@ -1292,9 +1292,16 @@
                                                relation.r_type)
                 try:
                     self._state.ignore_varmap = True
-                    return '%s%s' % (lhssql, relation.children[1].accept(self))
+                    sql = lhssql + relation.children[1].accept(self)
                 finally:
                     self._state.ignore_varmap = False
+                if relation.optional == 'right':
+                    leftalias = self._var_table(principal.children[0].variable)
+                    rightalias = self._var_table(relation.children[0].variable)
+                    self._state.replace_tables_by_outer_join(
+                        leftalias, rightalias, 'LEFT', sql)
+                    return ''
+                return sql
         return ''
 
     def _visit_attribute_relation(self, rel):
@@ -1372,12 +1379,15 @@
 
     def visit_comparison(self, cmp):
         """generate SQL for a comparison"""
+        optional = getattr(cmp, 'optional', None) # rql < 0.30
         if len(cmp.children) == 2:
-            # XXX occurs ?
+            # simplified expression from HAVING clause
             lhs, rhs = cmp.children
         else:
             lhs = None
             rhs = cmp.children[0]
+            assert not optional
+        sql = None
         operator = cmp.operator
         if operator in ('LIKE', 'ILIKE'):
             if operator == 'ILIKE' and not self.dbhelper.ilike_support:
@@ -1385,18 +1395,39 @@
             else:
                 operator = ' %s ' % operator
         elif operator == 'REGEXP':
-            return ' %s' % self.dbhelper.sql_regexp_match_expression(rhs.accept(self))
+            sql = ' %s' % self.dbhelper.sql_regexp_match_expression(rhs.accept(self))
         elif (operator == '=' and isinstance(rhs, Constant)
               and rhs.eval(self._args) is None):
             if lhs is None:
-                return ' IS NULL'
-            return '%s IS NULL' % lhs.accept(self)
+                sql = ' IS NULL'
+            else:
+                sql = '%s IS NULL' % lhs.accept(self)
         elif isinstance(rhs, Function) and rhs.name == 'IN':
             assert operator == '='
             operator = ' '
-        if lhs is None:
-            return '%s%s'% (operator, rhs.accept(self))
-        return '%s%s%s'% (lhs.accept(self), operator, rhs.accept(self))
+        if sql is None:
+            if lhs is None:
+                sql = '%s%s'% (operator, rhs.accept(self))
+            else:
+                sql = '%s%s%s'% (lhs.accept(self), operator, rhs.accept(self))
+        if optional is None:
+            return sql
+        leftvars = cmp.children[0].get_nodes(VariableRef)
+        assert len(leftvars) == 1
+        leftalias = self._var_table(leftvars[0].variable.stinfo['attrvar'])
+        rightvars = cmp.children[1].get_nodes(VariableRef)
+        assert len(rightvars) == 1
+        rightalias = self._var_table(rightvars[0].variable.stinfo['attrvar'])
+        if optional == 'right':
+            self._state.replace_tables_by_outer_join(
+                leftalias, rightalias, 'LEFT', sql)
+        elif optional == 'left':
+            self._state.replace_tables_by_outer_join(
+                rightalias, leftalias, 'LEFT', sql)
+        else:
+            self._state.replace_tables_by_outer_join(
+                leftalias, rightalias, 'FULL', sql)
+        return ''
 
     def visit_mathexpression(self, mexpr):
         """generate SQL for a mathematic expression"""

--- a/server/test/unittest_rql2sql.py	Wed Jul 27 14:17:47 2011 +0200
+++ b/server/test/unittest_rql2sql.py	Wed Jul 27 14:22:32 2011 +0200
@@ -807,6 +807,11 @@
 
 
 OUTER_JOIN = [
+
+    ('Any U,G WHERE U login L, G name L?, G is CWGroup',
+     '''SELECT _U.cw_eid, _G.cw_eid
+FROM cw_CWUser AS _U LEFT OUTER JOIN cw_CWGroup AS _G ON (_G.cw_name=_U.cw_login)'''),
+
     ('Any X,S WHERE X travaille S?',
      '''SELECT _X.cw_eid, rel_travaille0.eid_to
 FROM cw_Personne AS _X LEFT OUTER JOIN travaille_relation AS rel_travaille0 ON (rel_travaille0.eid_from=_X.cw_eid)'''
@@ -969,6 +974,18 @@
      '''SELECT _CFG.cw_ecrit_par, _CALIBCFG.cw_eid, _CFG.cw_eid
 FROM cw_Note AS _CFG LEFT OUTER JOIN cw_Note AS _CALIBCFG ON (_CALIBCFG.cw_ecrit_par=_CFG.cw_ecrit_par)
 WHERE _CFG.cw_ecrit_par=1'''),
+
+    ('Any U,G WHERE U login UL, G name GL, G is CWGroup HAVING UPPER(UL)=UPPER(GL)?',
+     '''SELECT _U.cw_eid, _G.cw_eid
+FROM cw_CWUser AS _U LEFT OUTER JOIN cw_CWGroup AS _G ON (UPPER(_U.cw_login)=UPPER(_G.cw_name))'''),
+
+    ('Any U,G WHERE U login UL, G name GL, G is CWGroup HAVING UPPER(UL)?=UPPER(GL)',
+     '''SELECT _U.cw_eid, _G.cw_eid
+FROM cw_CWGroup AS _G LEFT OUTER JOIN cw_CWUser AS _U ON (UPPER(_U.cw_login)=UPPER(_G.cw_name))'''),
+
+    ('Any U,G WHERE U login UL, G name GL, G is CWGroup HAVING UPPER(UL)?=UPPER(GL)?',
+     '''SELECT _U.cw_eid, _G.cw_eid
+FROM cw_CWUser AS _U FULL OUTER JOIN cw_CWGroup AS _G ON (UPPER(_U.cw_login)=UPPER(_G.cw_name))'''),
     ]
 
 VIRTUAL_VARS = [