[sql generation] fix missing join with outer joins w/ rhs invariant variable... stable
authorSylvain Thénault <sylvain.thenault@logilab.fr>
Wed, 06 Apr 2011 23:23:48 +0200
branchstable
changeset 7193 7eaef037ea9d
parent 7192 9e92c8558fea
child 7194 79686c864bbf
child 7199 589801acf08e
[sql generation] fix missing join with outer joins w/ rhs invariant variable... ... where the principal where not the processed relation. This led to major refactoring in outer join handling, which is now much clearer and powerful. Closes #1579228
server/rqlannotation.py
server/sources/rql2sql.py
server/test/unittest_rql2sql.py
--- a/server/rqlannotation.py	Wed Apr 06 23:01:58 2011 +0200
+++ b/server/rqlannotation.py	Wed Apr 06 23:23:48 2011 +0200
@@ -98,10 +98,19 @@
                     # variable of an inlined relation
                     if not rel in stinfo['rhsrelations'] and rschema.inlined:
                         break
-                else:
-                    # variable used as main variable of an optional relation
-                    # can't be invariant
+                # variable used as main variable of an optional relation can't
+                # be invariant, unless we can use some other relation as
+                # reference for the outer join
+                elif not stinfo['constnode']:
                     break
+                elif len(stinfo['relations']) == 2:
+                    if onlhs:
+                        ostinfo = rhs.children[0].variable.stinfo
+                    else:
+                        ostinfo = lhs.variable.stinfo
+                    if not any(orel for orel in ostinfo['relations']
+                               if orel.optional and orel is not rel):
+                        break
             if rschema.final or (onlhs and rschema.inlined):
                 if rschema.type != 'has_text':
                     # need join anyway if the variable appears in a final or
--- a/server/sources/rql2sql.py	Wed Apr 06 23:01:58 2011 +0200
+++ b/server/sources/rql2sql.py	Wed Apr 06 23:23:48 2011 +0200
@@ -319,6 +319,31 @@
 # IGenerator implementation for RQL->SQL #######################################
 
 class StateInfo(object):
+    """this class stores data accumulated during the RQL syntax tree visit
+    for later SQL generation.
+
+    Attributes related to OUTER JOIN handling
+    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+    * `outer_chains`, list of list of strings. Each list represent a tables
+      that have to be outer joined together.
+
+    * `outer_tables`, dictionnary used as index of tables used in outer join ::
+
+        'table alias': (outertype, [conditions], [chain])
+
+      where:
+
+      * `outertype` is one of None, 'LEFT', 'RIGHT', 'FULL'
+      * `conditions` is a list of join conditions (string)
+      * `chain` is a list of table alias (the *outer chain*) in which the key
+        alias appears
+
+    * `outer_pending` is a dictionary containing some conditions that will have
+      to be added to the outer join when the table will be turned into an
+      outerjoin ::
+
+       'table alias': [conditions]
+    """
     def __init__(self, select, existssols, unstablevars):
         self.existssols = existssols
         self.unstablevars = unstablevars
@@ -338,7 +363,9 @@
         self.actual_tables = [[]]
         for _, tsql in self.tables.itervalues():
             self.actual_tables[-1].append(tsql)
+        self.outer_chains = []
         self.outer_tables = {}
+        self.outer_pending = {}
         self.duplicate_switches = []
         self.aliases = {}
         self.restrictions = []
@@ -403,7 +430,208 @@
         self.scope_nodes.pop()
         restrictions = self.restrictions
         self.restrictions = self._restr_stack.pop()
-        return restrictions, self.actual_tables.pop()
+        scope = len(self.actual_tables) - 1
+        # check if we have some outer chain for this scope
+        matching_chains = []
+        for chain in self.outer_chains:
+            for tablealias in chain:
+                if self.tables[tablealias][0] < scope:
+                    # chain belongs to outer scope
+                    break
+            else:
+                # chain match current scope
+                matching_chains.append(chain)
+        # call to `tables_sql` will pop actual_tables
+        tables = self.tables_sql(matching_chains)
+        # cleanup outer join related structure for tables in matching chains
+        for chain in matching_chains:
+            self.outer_chains.remove(chain)
+            for alias in chain:
+                del self.outer_tables[alias]
+        return restrictions, tables
+
+    # tables handling #########################################################
+
+    def add_table(self, table, key=None, scope=-1):
+        if key is None:
+            key = table
+        if key in self.tables:
+            return
+        if scope < 0:
+            scope = len(self.actual_tables) + scope
+        self.tables[key] = (scope, table)
+        self.actual_tables[scope].append(table)
+
+    def alias_and_add_table(self, tablename, scope=-1):
+        alias = '%s%s' % (tablename, self.count)
+        self.count += 1
+        self.add_table('%s AS %s' % (tablename, alias), alias, scope)
+        return alias
+
+    def relation_table(self, relation):
+        """return the table alias used by the given relation"""
+        if relation in self.done:
+            return relation._q_sqltable
+        rid = 'rel_%s%s' % (relation.r_type, self.count)
+        # relation's table is belonging to the root scope if it is the principal
+        # table of one of it's variable and if that variable belong's to parent
+        # scope
+        for varref in relation.iget_nodes(VariableRef):
+            var = varref.variable
+            if isinstance(var, ColumnAlias):
+                scope = 0
+                break
+            # XXX may have a principal without being invariant for this generation,
+            #     not sure this is a pb or not
+            if var.stinfo.get('principal') is relation and var.scope is var.stmt:
+                scope = 0
+                break
+        else:
+            scope = -1
+        self.count += 1
+        self.add_table('%s_relation AS %s' % (relation.r_type, rid), rid, scope=scope)
+        relation._q_sqltable = rid
+        self.done.add(relation)
+        return rid
+
+    def fti_table(self, relation, fti_table):
+        """return the table alias used by the given has_text relation,
+        `fti_table` being the table name for the plain text index
+        """
+        if relation in self.done:
+            try:
+                return relation._q_sqltable
+            except AttributeError:
+                pass
+        self.done.add(relation)
+        scope = self.scopes[relation.scope]
+        alias = self.alias_and_add_table(fti_table, scope=scope)
+        relation._q_sqltable = alias
+        return alias
+
+    # outer join handling ######################################################
+
+    def mark_as_used_in_outer_join(self, tablealias, addpending=True):
+        """Mark table of given alias as used in outer join. This must be called
+        after `outer_tables[tablealias]` has been initialized.
+        """
+        # remove a table from actual_table because it's used in an outer join
+        # chain
+        scope, tabledef = self.tables[tablealias]
+        self.actual_tables[scope].remove(tabledef)
+        # check if there are some pending outer join condition for this table
+        if addpending:
+            try:
+                pending_conditions = self.outer_pending.pop(tablealias)
+            except KeyError:
+                pass
+            else:
+                self.outer_tables[tablealias][1].extend(pending_conditions)
+        else:
+            assert not tablealias in self.outer_pending
+
+    def add_outer_join_condition(self, tablealias, condition):
+        try:
+            outer, conditions, chain = self.outer_tables[tablealias]
+            conditions.append(condition)
+        except KeyError:
+            self.outer_pending.setdefault(tablealias, []).append(condition)
+
+    def replace_tables_by_outer_join(self, leftalias, rightalias,
+                                     outertype, condition):
+        """tell we need <leftalias> <outertype> JOIN <rightalias> ON <condition>
+        """
+        assert leftalias != rightalias, leftalias
+        outer_tables = self.outer_tables
+        louter, lconditions, lchain = outer_tables.get(leftalias,
+                                                      (None, None, None))
+        router, rconditions, rchain = outer_tables.get(rightalias,
+                                                      (None, None, None))
+        if lchain is None and rchain is None:
+            # create a new outer chaine
+            chain = [leftalias, rightalias]
+            outer_tables[leftalias] = (None, [], chain)
+            outer_tables[rightalias] = (outertype, [condition], chain)
+            self.outer_chains.append(chain)
+            self.mark_as_used_in_outer_join(leftalias, addpending=False)
+            self.mark_as_used_in_outer_join(rightalias)
+        elif lchain is None:
+            # [A > B > C] + [D > A] -> [D > A > B > C]
+            if rightalias == rchain[0]:
+                outer_tables[leftalias] = (None, [], rchain)
+                conditions = outer_tables[rightalias][1] + [condition]
+                outer_tables[rightalias] = (outertype, conditions, rchain)
+                rchain.insert(0, leftalias)
+            else:
+                # [A > B > C] + [D > B] -> [A > B > C < D]
+                if outertype == 'LEFT':
+                    outertype = 'RIGHT'
+                outer_tables[leftalias] = (outertype, [condition], rchain)
+                rchain.append(leftalias)
+            self.mark_as_used_in_outer_join(leftalias)
+        elif rchain is None:
+            # [A > B > C] + [B > D] -> [A > B > C > D]
+            outer_tables[rightalias] = (outertype, [condition], lchain)
+            lchain.append(rightalias)
+            self.mark_as_used_in_outer_join(rightalias)
+        elif lchain is rchain:
+            # already in the same chain, simply check compatibility and append
+            # the condition if it's ok
+            lidx = lchain.index(leftalias)
+            ridx = lchain.index(rightalias)
+            if (outertype == 'FULL' and router != 'FULL') \
+                   or (lidx < ridx and router != 'LEFT') \
+                   or (ridx < lidx and louter != 'RIGHT'):
+                raise BadRQLQuery()
+            # merge conditions
+            if lidx < ridx:
+                rconditions.append(condition)
+            else:
+                lconditions.append(condition)
+        else:
+            if louter is not None:
+                raise BadRQLQuery()
+            # merge chains
+            self.outer_chains.remove(lchain)
+            self.mark_as_used_in_outer_join(leftalias)
+            rchain += lchain
+            for alias, (aouter, aconditions, achain) in outer_tables.iteritems():
+                if achain is lchain:
+                    outer_tables[alias] = (aouter, aconditions, rchain)
+
+    # sql generation helpers ###################################################
+
+    def tables_sql(self, outer_chains=None):
+        """generate SQL for FROM clause"""
+        # sort for test predictability
+        tables = sorted(self.actual_tables.pop())
+        # process outer joins
+        if outer_chains is None:
+            assert not self.actual_tables, self.actual_tables
+            assert not self.outer_pending
+            outer_chains = self.outer_chains
+        for chain in sorted(outer_chains):
+            tablealias = chain[0]
+            outertype, conditions, _ = self.outer_tables[tablealias]
+            assert _ is chain, (chain, _)
+            assert outertype is None, (chain, self.outer_chains)
+            assert not conditions, (chain, self.outer_chains)
+            assert len(chain) > 1
+            tabledef = self.tables[tablealias][1]
+            outerjoin = [tabledef]
+            for tablealias in chain[1:]:
+                outertype, conditions, _ = self.outer_tables[tablealias]
+                assert _ is chain, (chain, self.outer_chains)
+                assert outertype in ('LEFT', 'RIGHT', 'FULL'), (
+                    tablealias, outertype, conditions)
+                assert isinstance(conditions, (list)), (
+                    tablealias, outertype, conditions)
+                tabledef = self.tables[tablealias][1]
+                outerjoin.append('%s OUTER JOIN %s ON (%s)' % (
+                    outertype, tabledef, ' AND '.join(conditions)))
+            tables.append(' '.join(outerjoin))
+        return ', '.join(tables)
+
 
 def extract_fake_having_terms(having):
     """RQL's HAVING may be used to contains stuff that should go in the WHERE
@@ -448,6 +676,7 @@
                 node.parent.remove(node)
     return fakehaving
 
+
 class SQLGenerator(object):
     """
     generation of SQL from the fully expanded RQL syntax tree
@@ -598,12 +827,12 @@
         try:
             sql = self._solutions_sql(select, morerestr, sols, distinct,
                                       needalias or needwrap)
-            # generate groups / having before wrapping query selection to
-            # get correct column aliases
+            # generate groups / having before wrapping query selection to get
+            # correct column aliases
             self._in_wrapping_query = needwrap
             if groups:
-                # no constant should be inserted in GROUP BY else the backend will
-                # interpret it as a positional index in the selection
+                # no constant should be inserted in GROUP BY else the backend
+                # will interpret it as a positional index in the selection
                 groups = ','.join(vref.accept(self) for vref in groups
                                   if not isinstance(vref, Constant))
             if having:
@@ -611,9 +840,9 @@
                 having = ' AND '.join(term.accept(self) for term in having
                                       if not isinstance(term, Constant))
             if needwrap:
-                sql = '%s FROM (%s) AS T1' % (self._selection_sql(outerselection, distinct,
-                                                                  needalias),
-                                              sql)
+                sql = '%s FROM (%s) AS T1' % (
+                    self._selection_sql(outerselection, distinct,needalias),
+                    sql)
             if groups:
                 sql += '\nGROUP BY %s' % groups
             if having:
@@ -626,14 +855,9 @@
                     if _term is not None:
                         sqlsortterms.append(_term)
                 if sqlsortterms:
-                    sql = self.dbhelper.sql_add_order_by(sql, sqlsortterms,
-                                                         origselection,
-                                                         fneedwrap,
-                                                         select.limit or select.offset)
-                    ## sql += '\nORDER BY %s' % ','.join(sqlsortterms)
-                    ## if sorts and fneedwrap:
-                    ##     selection = ['T1.C%s' % i for i in xrange(len(origselection))]
-                    ##     sql = 'SELECT %s FROM (%s) AS T1' % (','.join(selection), sql)
+                    sql = self.dbhelper.sql_add_order_by(
+                        sql, sqlsortterms, origselection, fneedwrap,
+                        select.limit or select.offset)
             else:
                 sqlsortterms = None
             state.finalize_source_cbs()
@@ -683,10 +907,9 @@
             self._state.merge_source_cbs(self._state._needs_source_cb)
             # add required tables
             assert len(self._state.actual_tables) == 1, self._state.actual_tables
-            tables = self._state.actual_tables[-1]
+            tables = self._state.tables_sql()
             if tables:
-                # sort for test predictability
-                sql.insert(1, 'FROM %s' % ', '.join(sorted(tables)))
+                sql.insert(1, 'FROM %s' % tables)
             elif self._state.restrictions and self.dbhelper.needs_from_clause:
                 sql.insert(1, 'FROM (SELECT 1) AS _T')
             sqls.append('\n'.join(sql))
@@ -773,13 +996,13 @@
         restriction = ' AND '.join(restrictions)
         if not restriction:
             if tables:
-                return 'SELECT 1 FROM %s' % ', '.join(tables)
+                return 'SELECT 1 FROM %s' % tables
             return ''
         if not tables:
             # XXX could leave surrounding EXISTS() in this case no?
             sql = 'SELECT 1 WHERE %s' % restriction
         else:
-            sql = 'SELECT 1 FROM %s WHERE %s' % (', '.join(tables), restriction)
+            sql = 'SELECT 1 FROM %s WHERE %s' % (tables, restriction)
         return sql
 
 
@@ -820,12 +1043,11 @@
             # relation has already been processed by a previous step
             return ''
         elif relation.optional:
-            # check it has not already been treaten (to get necessary
-            # information to add an outer join condition)
-            if relation in self._state.done:
-                return ''
             # OPTIONAL relation, generate a left|right outer join
-            sql = self._visit_outer_join_relation(relation, rschema)
+            if rtype == 'identity' or rschema.inlined:
+                sql = self._visit_outer_join_inlined_relation(relation, rschema)
+            else:
+                sql = self._visit_outer_join_relation(relation, rschema)
         elif rschema.inlined:
             sql = self._visit_inlined_relation(relation)
         else:
@@ -888,7 +1110,7 @@
             lhs, rhs = relation.get_parts()
             return '%s%s' % (lhs.accept(self), rhs.accept(self))
         lhsvar, lhsconst, rhsvar, rhsconst = relation_info(relation)
-        rid = self._relation_table(relation)
+        rid = self._state.relation_table(relation)
         sqls = []
         sqls += self._process_relation_term(relation, rid, lhsvar, lhsconst, 'eid_from')
         sqls += self._process_relation_term(relation, rid, rhsvar, rhsconst, 'eid_to')
@@ -925,74 +1147,104 @@
            elif it's a full outer join:
            -> X FULL OUTER JOIN Y ON (X.relation=Y.eid)
         """
-        lhsvar, lhsconst, rhsvar, rhsconst = relation_info(relation)
-        if relation.optional == 'right':
-            joinattr, restrattr = 'eid_from', 'eid_to'
-        else:
-            lhsvar, rhsvar = rhsvar, lhsvar
-            lhsconst, rhsconst = rhsconst, lhsconst
+        leftvar, leftconst, rightvar, rightconst = relation_info(relation)
+        assert not (leftconst and rightconst), "doesn't make sense"
+        if relation.optional == 'left':
+            leftvar, rightvar = rightvar, leftvar
+            leftconst, rightconst = rightconst, leftconst
             joinattr, restrattr = 'eid_to', 'eid_from'
-        if relation.optional == 'both':
-            outertype = 'FULL'
         else:
-            outertype = 'LEFT'
-        if rschema.inlined or relation.r_type == 'identity':
-            self._state.done.add(relation)
-            t1 = self._var_table(lhsvar)
-            if relation.r_type == 'identity':
-                attr = 'eid'
-            else:
-                attr = relation.r_type
-            # reset lhs/rhs, we need the initial order now
-            lhs, rhs = relation.get_variable_parts()
-            if '%s.%s' % (lhs.name, attr) in self._varmap:
-                lhssql = self._varmap['%s.%s' % (lhs.name, attr)]
+            joinattr, restrattr = 'eid_from', 'eid_to'
+        # search table for this variable, to use as left table of the outer join
+        leftalias = None
+        if leftvar:
+            # take care, may return None for invariant variable
+            leftalias = self._var_table(leftvar)
+        if leftalias is None:
+            if leftvar.stinfo['principal'] is not relation:
+                # use variable's principal relation
+                leftalias = leftvar.stinfo['principal']._q_sqltable
             else:
-                lhssql = '%s.%s%s' % (self._var_table(lhs.variable), SQL_PREFIX, attr)
-            if not rhsvar is None:
-                t2 = self._var_table(rhsvar)
-                if t2 is None:
-                    if rhsconst is not None:
-                        # inlined relation with invariant as rhs
-                        condition = '%s=%s' % (lhssql, rhsconst.accept(self))
-                        if relation.r_type != 'identity':
-                            condition = '(%s OR %s IS NULL)' % (condition, lhssql)
-                        if not lhsvar.stinfo.get('optrelations'):
-                            return condition
-                        self.add_outer_join_condition(lhsvar, t1, condition)
-                    return
+                # search for relation on which we should join
+                for orelation in leftvar.stinfo['relations']:
+                    if (orelation is not relation and
+                        not self.schema.rschema(orelation.r_type).final):
+                        break
+                else:
+                    for orelation in rightvar.stinfo['relations']:
+                        if (orelation is not relation and
+                            not self.schema.rschema(orelation.r_type).final
+                            and orelation.optional):
+                            break
+                    else:
+                        # unexpected
+                        assert False, leftvar
+                leftalias = self._state.relation_table(orelation)
+        # right table of the outer join
+        rightalias = self._state.relation_table(relation)
+        # compute join condition
+        if not leftconst or (leftvar and not leftvar._q_invariant):
+            leftsql = leftvar.accept(self)
+        else:
+            leftsql = leftconst.accept(self)
+        condition = '%s.%s=%s' % (rightalias, joinattr, leftsql)
+        if rightconst:
+            condition += ' AND %s.%s=%s' % (rightalias, restrattr, rightconst.accept(self))
+        # record outer join
+        outertype = 'FULL' if relation.optional == 'both' else 'LEFT'
+        self._state.replace_tables_by_outer_join(leftalias, rightalias,
+                                                 outertype, condition)
+        # need another join?
+        if rightconst is None:
+            # we need another outer join for the other side of the relation (e.g.
+            # for "X relation Y?" in RQL, we treated earlier the (cw_X.eid /
+            # relation.eid_from) join, now we've to do (relation.eid_to /
+            # cw_Y.eid)
+            leftalias = rightalias
+            rightsql = rightvar.accept(self) # accept before using var_table
+            rightalias = self._var_table(rightvar)
+            if rightalias is None:
+                if rightvar.stinfo['principal'] is not relation:
+                    self._state.replace_tables_by_outer_join(
+                        leftalias, rightvar.stinfo['principal']._q_sqltable,
+                        outertype, '%s.%s=%s' % (leftalias, restrattr, rightvar.accept(self)))
             else:
-                condition = '%s=%s' % (lhssql, rhsconst.accept(self))
-                self.add_outer_join_condition(lhsvar, t1, condition)
-            join = '%s OUTER JOIN %s ON (%s=%s)' % (
-                outertype, self._state.tables[t2][1], lhssql, rhs.accept(self))
-            self.replace_tables_by_outer_join(join, t1, t2)
-            return ''
-        lhssql = lhsconst and lhsconst.accept(self) or lhsvar.accept(self)
-        rhssql = rhsconst and rhsconst.accept(self) or rhsvar.accept(self)
-        rid = self._relation_table(relation)
-        if not lhsvar:
-            join = ''
-            toreplace = []
-            maintable = rid
-        else:
-            join = '%s OUTER JOIN %s ON (%s.%s=%s' % (
-                outertype, self._state.tables[rid][1], rid, joinattr, lhssql)
-            toreplace = [rid]
-            maintable = self._var_table(lhsvar)
-            if rhsconst:
-                join += ' AND %s.%s=%s)' % (rid, restrattr, rhssql)
-            else:
-                join += ')'
-        if not rhsconst:
-            rhstable = rhsvar._q_sqltable
-            if rhstable:
-                assert rhstable is not None, rhsvar
-                join += ' %s OUTER JOIN %s ON (%s.%s=%s)' % (
-                    outertype, self._state.tables[rhstable][1], rid, restrattr,
-                    rhssql)
-                toreplace.append(rhstable)
-        self.replace_tables_by_outer_join(join, maintable, *toreplace)
+                self._state.replace_tables_by_outer_join(
+                    leftalias, rightalias, outertype,
+                    '%s.%s=%s' % (leftalias, restrattr, rightvar.accept(self)))
+        # this relation will hence be expressed in FROM clause, return nothing
+        # here
+        return ''
+
+
+    def _visit_outer_join_inlined_relation(self, relation, rschema):
+        leftvar, leftconst, rightvar, rightconst = relation_info(relation)
+        assert not (leftconst and rightconst), "doesn't make sense"
+        if relation.optional != 'right':
+            leftvar, rightvar = rightvar, leftvar
+            leftconst, rightconst = rightconst, leftconst
+        outertype = 'FULL' if relation.optional == 'both' else 'LEFT'
+        leftalias = self._var_table(leftvar)
+        attr = 'eid' if relation.r_type == 'identity' else relation.r_type
+        lhs, rhs = relation.get_variable_parts()
+        try:
+            lhssql = self._varmap['%s.%s' % (lhs.name, attr)]
+        except KeyError:
+            lhssql = '%s.%s%s' % (self._var_table(lhs.variable), SQL_PREFIX, attr)
+        if rightvar is not None:
+            rightalias = self._var_table(rightvar)
+            if rightalias is None:
+                if rightconst is not None:
+                    # inlined relation with invariant as rhs
+                    condition = '%s=%s' % (lhssql, rightconst.accept(self))
+                    if relation.r_type != 'identity':
+                        condition = '(%s OR %s IS NULL)' % (condition, lhssql)
+                    if not leftvar.stinfo.get('optrelations'):
+                        return condition
+                    self._state.add_outer_join_condition(leftalias, condition)
+                return
+        self._state.replace_tables_by_outer_join(
+            leftalias, rightalias, outertype, '%s=%s' % (lhssql, rhs.accept(self)))
         return ''
 
     def _visit_var_attr_relation(self, relation, rhs_vars):
@@ -1054,7 +1306,7 @@
         except AttributeError:
             sql = '%s%s' % (lhssql, rhssql)
         if lhs.variable.stinfo.get('optrelations'):
-            self.add_outer_join_condition(lhs.variable, table, sql)
+            self._state.add_outer_join_condition(table, sql)
         else:
             return sql
 
@@ -1062,7 +1314,7 @@
         """generate SQL for a has_text relation"""
         lhs, rhs = rel.get_parts()
         const = rhs.children[0]
-        alias = self._fti_table(rel)
+        alias = self._state.fti_table(rel, self.dbhelper.fti_table)
         jointo = lhs.accept(self)
         restriction = ''
         lhsvar = lhs.variable
@@ -1076,7 +1328,7 @@
                 # external entities on multisources configurations
                 ealias = lhsvar._q_sqltable = '_' + lhsvar.name
                 jointo = lhsvar._q_sql = '%s.eid' % ealias
-                self.add_table('entities AS %s' % ealias, ealias)
+                self._state.add_table('entities AS %s' % ealias, ealias)
                 if not lhsvar._q_invariant or len(lhsvar.stinfo['possibletypes']) == 1:
                     restriction = " AND %s.type='%s'" % (ealias, self._state.solution[lhs.name])
                 else:
@@ -1139,8 +1391,9 @@
                 raise BadRQLQuery("can't use FTIRANK on variable not used in an"
                                   " 'has_text' relation (eg full-text search)")
             const = rel.get_parts()[1].children[0]
-            return self.dbhelper.fti_rank_order(self._fti_table(rel),
-                                                const.eval(self._args))
+            return self.dbhelper.fti_rank_order(
+                self._state.fti_table(rel, self.dbhelper.fti_table),
+                const.eval(self._args))
         args = [c.accept(self) for c in func.children]
         if func in self._state.source_cb_funcs:
             # function executed as a callback on the source
@@ -1188,7 +1441,7 @@
             table = sql.split('.', 1)[0]
             colalias._q_sqltable = table
             colalias._q_sql = sql
-            self.add_table(table)
+            self._state.add_table(table)
             return sql
         return colalias._q_sql
 
@@ -1212,7 +1465,7 @@
             principal = variable.stinfo['principal']
             if principal is None:
                 vtablename = '_' + variable.name
-                self.add_table('entities AS %s' % vtablename, vtablename)
+                self._state.add_table('entities AS %s' % vtablename, vtablename)
                 sql = '%s.eid' % vtablename
                 if variable.stinfo['typerel'] is not None:
                     # add additional restriction on entities.type column
@@ -1225,15 +1478,16 @@
                         restr = '%s.type IN (%s)' % (vtablename, etypes)
                     self._state.add_restriction(restr)
             elif principal.r_type == 'has_text':
-                sql = '%s.%s' % (self._fti_table(principal),
+                sql = '%s.%s' % (self._state.fti_table(principal,
+                                                       self.dbhelper.fti_table),
                                  self.dbhelper.fti_uid_attr)
             elif principal in variable.stinfo['rhsrelations']:
                 if self.schema.rschema(principal.r_type).inlined:
                     sql = self._linked_var_sql(variable)
                 else:
-                    sql = '%s.eid_to' % self._relation_table(principal)
+                    sql = '%s.eid_to' % self._state.relation_table(principal)
             else:
-                sql = '%s.eid_from' % self._relation_table(principal)
+                sql = '%s.eid_from' % self._state.relation_table(principal)
         else:
             # standard variable: get table name according to etype and use .eid
             # attribute
@@ -1283,7 +1537,7 @@
         sql = self._varmap[key]
         tablealias = sql.split('.', 1)[0]
         scope = self._temp_table_scope(term.stmt, tablealias)
-        self.add_table(tablealias, scope=scope)
+        self._state.add_table(tablealias, scope=scope)
         return sql, tablealias
 
     def _var_info(self, var):
@@ -1297,7 +1551,7 @@
                 raise BadRQLQuery(var.stmt.root)
             tablealias = '_' + var.name
             sql = '%s.%seid' % (tablealias, SQL_PREFIX)
-            self.add_table('%s%s AS %s' % (SQL_PREFIX, etype, tablealias),
+            self._state.add_table('%s%s AS %s' % (SQL_PREFIX, etype, tablealias),
                            tablealias, scope=scope)
         return sql, tablealias
 
@@ -1305,7 +1559,7 @@
         try:
             sql = self._varmap['%s.%s' % (var.name, rtype)]
             scope = self._state.scopes[var.scope]
-            self.add_table(sql.split('.', 1)[0], scope=scope)
+            self._state.add_table(sql.split('.', 1)[0], scope=scope)
         except KeyError:
             sql = '%s.%s%s' % (self._var_table(var), SQL_PREFIX, rtype)
             #self._state.done.add(var.name)
@@ -1345,107 +1599,6 @@
 
     # tables handling #########################################################
 
-    def alias_and_add_table(self, tablename, scope=-1):
-        alias = '%s%s' % (tablename, self._state.count)
-        self._state.count += 1
-        self.add_table('%s AS %s' % (tablename, alias), alias, scope)
-        return alias
-
-    def add_table(self, table, key=None, scope=-1):
-        if key is None:
-            key = table
-        if key in self._state.tables:
-            return
-        if scope < 0:
-            scope = len(self._state.actual_tables) + scope
-        self._state.tables[key] = (scope, table)
-        self._state.actual_tables[scope].append(table)
-
-    def replace_tables_by_outer_join(self, substitute, lefttable, *tables):
-        for table in tables:
-            try:
-                scope, alias = self._state.tables[table]
-                self._state.actual_tables[scope].remove(alias)
-            except ValueError: # huum, not sure about what should be done here
-                msg = "%s already used in an outer join, don't know what to do!"
-                raise Exception(msg % table)
-        try:
-            tablealias = self._state.outer_tables[lefttable]
-            actualtables = self._state.actual_tables[-1]
-        except KeyError:
-            tablescope, tablealias = self._state.tables[lefttable]
-            actualtables = self._state.actual_tables[tablescope]
-        outerjoin = '%s %s' % (tablealias, substitute)
-        self._update_outer_tables(lefttable, actualtables, tablealias, outerjoin)
-        for table in tables:
-            self._state.outer_tables[table] = outerjoin
-
-    def add_outer_join_condition(self, var, table, condition):
-        try:
-            tablealias = self._state.outer_tables[table]
-            actualtables = self._state.actual_tables[-1]
-        except KeyError:
-            for rel in var.stinfo.get('optrelations'):
-                self.visit_relation(rel)
-            assert self._state.outer_tables
-            self.add_outer_join_condition(var, table, condition)
-            return
-        before, after = tablealias.split(' AS %s ' % table, 1)
-        beforep, afterp = after.split(')', 1)
-        outerjoin = '%s AS %s %s AND %s) %s' % (before, table, beforep,
-                                                condition, afterp)
-        self._update_outer_tables(table, actualtables, tablealias, outerjoin)
-
-    def _update_outer_tables(self, table, actualtables, oldalias, newalias):
-        actualtables.remove(oldalias)
-        actualtables.append(newalias)
-        self._state.outer_tables[table] = newalias
-        # some tables which have already been used as outer table and replaced
-        # by <oldalias> may not be reused here, though their associated value
-        # in the outer_tables dict has to be updated as well
-        for table, outerexpr in self._state.outer_tables.iteritems():
-            if outerexpr == oldalias:
-                self._state.outer_tables[table] = newalias
-
     def _var_table(self, var):
         var.accept(self)#.visit_variable(var)
         return var._q_sqltable
-
-    def _relation_table(self, relation):
-        """return the table alias used by the given relation"""
-        if relation in self._state.done:
-            return relation._q_sqltable
-        assert not self.schema.rschema(relation.r_type).final, relation.r_type
-        rid = 'rel_%s%s' % (relation.r_type, self._state.count)
-        # relation's table is belonging to the root scope if it is the principal
-        # table of one of it's variable and if that variable belong's to parent
-        # scope
-        for varref in relation.iget_nodes(VariableRef):
-            var = varref.variable
-            if isinstance(var, ColumnAlias):
-                scope = 0
-                break
-            # XXX may have a principal without being invariant for this generation,
-            #     not sure this is a pb or not
-            if var.stinfo.get('principal') is relation and var.scope is var.stmt:
-                scope = 0
-                break
-        else:
-            scope = -1
-        self._state.count += 1
-        self.add_table('%s_relation AS %s' % (relation.r_type, rid), rid, scope=scope)
-        relation._q_sqltable = rid
-        self._state.done.add(relation)
-        return rid
-
-    def _fti_table(self, relation):
-        if relation in self._state.done:
-            try:
-                return relation._q_sqltable
-            except AttributeError:
-                pass
-        self._state.done.add(relation)
-        scope = self._state.scopes[relation.scope]
-        alias = self.alias_and_add_table(self.dbhelper.fti_table, scope=scope)
-        relation._q_sqltable = alias
-        return alias
--- a/server/test/unittest_rql2sql.py	Wed Apr 06 23:01:58 2011 +0200
+++ b/server/test/unittest_rql2sql.py	Wed Apr 06 23:23:48 2011 +0200
@@ -190,13 +190,13 @@
      "EXISTS(X owned_by U, U in_group G, G name 'lulufanclub' OR G name 'managers');",
      '''SELECT _X.cw_eid
 FROM cw_Personne AS _X
-WHERE _X.cw_prenom=lulu AND EXISTS(SELECT 1 FROM owned_by_relation AS rel_owned_by0, in_group_relation AS rel_in_group1, cw_CWGroup AS _G WHERE rel_owned_by0.eid_from=_X.cw_eid AND rel_in_group1.eid_from=rel_owned_by0.eid_to AND rel_in_group1.eid_to=_G.cw_eid AND ((_G.cw_name=lulufanclub) OR (_G.cw_name=managers)))'''),
+WHERE _X.cw_prenom=lulu AND EXISTS(SELECT 1 FROM cw_CWGroup AS _G, in_group_relation AS rel_in_group1, owned_by_relation AS rel_owned_by0 WHERE rel_owned_by0.eid_from=_X.cw_eid AND rel_in_group1.eid_from=rel_owned_by0.eid_to AND rel_in_group1.eid_to=_G.cw_eid AND ((_G.cw_name=lulufanclub) OR (_G.cw_name=managers)))'''),
 
     ("Any X WHERE X prenom 'lulu',"
      "NOT EXISTS(X owned_by U, U in_group G, G name 'lulufanclub' OR G name 'managers');",
      '''SELECT _X.cw_eid
 FROM cw_Personne AS _X
-WHERE _X.cw_prenom=lulu AND NOT (EXISTS(SELECT 1 FROM owned_by_relation AS rel_owned_by0, in_group_relation AS rel_in_group1, cw_CWGroup AS _G WHERE rel_owned_by0.eid_from=_X.cw_eid AND rel_in_group1.eid_from=rel_owned_by0.eid_to AND rel_in_group1.eid_to=_G.cw_eid AND ((_G.cw_name=lulufanclub) OR (_G.cw_name=managers))))'''),
+WHERE _X.cw_prenom=lulu AND NOT (EXISTS(SELECT 1 FROM cw_CWGroup AS _G, in_group_relation AS rel_in_group1, owned_by_relation AS rel_owned_by0 WHERE rel_owned_by0.eid_from=_X.cw_eid AND rel_in_group1.eid_from=rel_owned_by0.eid_to AND rel_in_group1.eid_to=_G.cw_eid AND ((_G.cw_name=lulufanclub) OR (_G.cw_name=managers))))'''),
 
     ('Any X WHERE  X title V, NOT X wikiid V, NOT X title "parent", X is Card',
      '''SELECT _X.cw_eid
@@ -452,7 +452,7 @@
     ('Any T WHERE T is Tag, NOT T name in ("t1", "t2"), EXISTS(T tags X, X is IN (CWUser, CWGroup))',
      '''SELECT _T.cw_eid
 FROM cw_Tag AS _T
-WHERE NOT (_T.cw_name IN(t1, t2)) AND EXISTS(SELECT 1 FROM tags_relation AS rel_tags0, cw_CWGroup AS _X WHERE rel_tags0.eid_from=_T.cw_eid AND rel_tags0.eid_to=_X.cw_eid UNION SELECT 1 FROM tags_relation AS rel_tags1, cw_CWUser AS _X WHERE rel_tags1.eid_from=_T.cw_eid AND rel_tags1.eid_to=_X.cw_eid)'''),
+WHERE NOT (_T.cw_name IN(t1, t2)) AND EXISTS(SELECT 1 FROM cw_CWGroup AS _X, tags_relation AS rel_tags0 WHERE rel_tags0.eid_from=_T.cw_eid AND rel_tags0.eid_to=_X.cw_eid UNION SELECT 1 FROM cw_CWUser AS _X, tags_relation AS rel_tags1 WHERE rel_tags1.eid_from=_T.cw_eid AND rel_tags1.eid_to=_X.cw_eid)'''),
 
     # must not use a relation in EXISTS scope to inline a variable
     ('Any U WHERE U eid IN (1,2), EXISTS(X owned_by U)',
@@ -468,7 +468,7 @@
     ('Any COUNT(U) WHERE EXISTS (P owned_by U, P is IN (Note, Affaire))',
      '''SELECT COUNT(_U.cw_eid)
 FROM cw_CWUser AS _U
-WHERE EXISTS(SELECT 1 FROM owned_by_relation AS rel_owned_by0, cw_Affaire AS _P WHERE rel_owned_by0.eid_from=_P.cw_eid AND rel_owned_by0.eid_to=_U.cw_eid UNION SELECT 1 FROM owned_by_relation AS rel_owned_by1, cw_Note AS _P WHERE rel_owned_by1.eid_from=_P.cw_eid AND rel_owned_by1.eid_to=_U.cw_eid)'''),
+WHERE EXISTS(SELECT 1 FROM cw_Affaire AS _P, owned_by_relation AS rel_owned_by0 WHERE rel_owned_by0.eid_from=_P.cw_eid AND rel_owned_by0.eid_to=_U.cw_eid UNION SELECT 1 FROM cw_Note AS _P, owned_by_relation AS rel_owned_by1 WHERE rel_owned_by1.eid_from=_P.cw_eid AND rel_owned_by1.eid_to=_U.cw_eid)'''),
 
     ('Any MAX(X)',
      '''SELECT MAX(_X.eid)
@@ -567,7 +567,7 @@
     ('Any COUNT(S),CS GROUPBY CS ORDERBY 1 DESC LIMIT 10 WHERE S is Affaire, C is Societe, S concerne C, C nom CS, (EXISTS(S owned_by 1)) OR (EXISTS(S documented_by N, N title "published"))',
      '''SELECT COUNT(rel_concerne0.eid_from), _C.cw_nom
 FROM concerne_relation AS rel_concerne0, cw_Societe AS _C
-WHERE rel_concerne0.eid_to=_C.cw_eid AND ((EXISTS(SELECT 1 FROM owned_by_relation AS rel_owned_by1 WHERE rel_concerne0.eid_from=rel_owned_by1.eid_from AND rel_owned_by1.eid_to=1)) OR (EXISTS(SELECT 1 FROM documented_by_relation AS rel_documented_by2, cw_Card AS _N WHERE rel_concerne0.eid_from=rel_documented_by2.eid_from AND rel_documented_by2.eid_to=_N.cw_eid AND _N.cw_title=published)))
+WHERE rel_concerne0.eid_to=_C.cw_eid AND ((EXISTS(SELECT 1 FROM owned_by_relation AS rel_owned_by1 WHERE rel_concerne0.eid_from=rel_owned_by1.eid_from AND rel_owned_by1.eid_to=1)) OR (EXISTS(SELECT 1 FROM cw_Card AS _N, documented_by_relation AS rel_documented_by2 WHERE rel_concerne0.eid_from=rel_documented_by2.eid_from AND rel_documented_by2.eid_to=_N.cw_eid AND _N.cw_title=published)))
 GROUP BY _C.cw_nom
 ORDER BY 1 DESC
 LIMIT 10'''),
@@ -723,7 +723,7 @@
     ('Any X WHERE NOT Y evaluee X, Y is CWUser',
      '''SELECT _X.cw_eid
 FROM cw_Note AS _X
-WHERE NOT (EXISTS(SELECT 1 FROM evaluee_relation AS rel_evaluee0, cw_CWUser AS _Y WHERE rel_evaluee0.eid_from=_Y.cw_eid AND rel_evaluee0.eid_to=_X.cw_eid))'''),
+WHERE NOT (EXISTS(SELECT 1 FROM cw_CWUser AS _Y, evaluee_relation AS rel_evaluee0 WHERE rel_evaluee0.eid_from=_Y.cw_eid AND rel_evaluee0.eid_to=_X.cw_eid))'''),
 
     ('Any X,RT WHERE X relation_type RT, NOT X is CWAttribute',
      '''SELECT _X.cw_eid, _X.cw_relation_type
@@ -816,7 +816,7 @@
     ('Any X WHERE X is Affaire, S is Societe, EXISTS(X owned_by U OR (X concerne S?, S owned_by U))',
      '''SELECT _X.cw_eid
 FROM cw_Affaire AS _X
-WHERE EXISTS(SELECT 1 FROM owned_by_relation AS rel_owned_by0, cw_CWUser AS _U, cw_Affaire AS _A LEFT OUTER JOIN concerne_relation AS rel_concerne1 ON (rel_concerne1.eid_from=_A.cw_eid) LEFT OUTER JOIN cw_Societe AS _S ON (rel_concerne1.eid_to=_S.cw_eid), owned_by_relation AS rel_owned_by2 WHERE ((rel_owned_by0.eid_from=_A.cw_eid AND rel_owned_by0.eid_to=_U.cw_eid) OR (rel_owned_by2.eid_from=_S.cw_eid AND rel_owned_by2.eid_to=_U.cw_eid)) AND _X.cw_eid=_A.cw_eid)'''),
+WHERE EXISTS(SELECT 1 FROM cw_CWUser AS _U, owned_by_relation AS rel_owned_by0, owned_by_relation AS rel_owned_by2, cw_Affaire AS _A LEFT OUTER JOIN concerne_relation AS rel_concerne1 ON (rel_concerne1.eid_from=_A.cw_eid) LEFT OUTER JOIN cw_Societe AS _S ON (rel_concerne1.eid_to=_S.cw_eid) WHERE ((rel_owned_by0.eid_from=_A.cw_eid AND rel_owned_by0.eid_to=_U.cw_eid) OR (rel_owned_by2.eid_from=_S.cw_eid AND rel_owned_by2.eid_to=_U.cw_eid)) AND _X.cw_eid=_A.cw_eid)'''),
 
     ('Any C,M WHERE C travaille G?, G evaluee M?, G is Societe',
      '''SELECT _C.cw_eid, rel_evaluee1.eid_to
@@ -827,13 +827,18 @@
      'F name "read", F require_group E, U in_group E)), U eid 1',
      '''SELECT _A.cw_eid, rel_documented_by0.eid_to
 FROM cw_Affaire AS _A LEFT OUTER JOIN documented_by_relation AS rel_documented_by0 ON (rel_documented_by0.eid_from=_A.cw_eid)
-WHERE ((rel_documented_by0.eid_to IS NULL) OR (EXISTS(SELECT 1 FROM require_permission_relation AS rel_require_permission1, cw_CWPermission AS _F, require_group_relation AS rel_require_group2, in_group_relation AS rel_in_group3 WHERE rel_documented_by0.eid_to=rel_require_permission1.eid_from AND rel_require_permission1.eid_to=_F.cw_eid AND _F.cw_name=read AND rel_require_group2.eid_from=_F.cw_eid AND rel_in_group3.eid_to=rel_require_group2.eid_to AND rel_in_group3.eid_from=1)))'''),
+WHERE ((rel_documented_by0.eid_to IS NULL) OR (EXISTS(SELECT 1 FROM cw_CWPermission AS _F, in_group_relation AS rel_in_group3, require_group_relation AS rel_require_group2, require_permission_relation AS rel_require_permission1 WHERE rel_documented_by0.eid_to=rel_require_permission1.eid_from AND rel_require_permission1.eid_to=_F.cw_eid AND _F.cw_name=read AND rel_require_group2.eid_from=_F.cw_eid AND rel_in_group3.eid_to=rel_require_group2.eid_to AND rel_in_group3.eid_from=1)))'''),
 
     ("Any X WHERE X eid 12, P? connait X",
      '''SELECT _X.cw_eid
-FROM cw_Personne AS _X LEFT OUTER JOIN connait_relation AS rel_connait0 ON (rel_connait0.eid_to=12)
+FROM cw_Personne AS _X LEFT OUTER JOIN connait_relation AS rel_connait0 ON (rel_connait0.eid_to=_X.cw_eid)
 WHERE _X.cw_eid=12'''
     ),
+    ("Any P WHERE X eid 12, P? concerne X, X todo_by S",
+     '''SELECT rel_concerne0.eid_from
+FROM todo_by_relation AS rel_todo_by1 LEFT OUTER JOIN concerne_relation AS rel_concerne0 ON (rel_concerne0.eid_to=12)
+WHERE rel_todo_by1.eid_from=12'''
+    ),
 
     ('Any GN, TN ORDERBY GN WHERE T tags G?, T name TN, G name GN',
     '''
@@ -899,11 +904,11 @@
 FROM cw_CWUser AS _G LEFT OUTER JOIN cw_State AS _S ON (_G.cw_in_state=_S.cw_eid AND _S.cw_name=hop)
 UNION ALL
 SELECT _G.cw_eid AS C0, _S.cw_eid AS C1
-FROM cw_Note AS _G LEFT OUTER JOIN cw_State AS _S ON (_G.cw_in_state=_S.cw_eid AND _S.cw_name=hop) ) AS _T0 ON (rel_tags0.eid_to=_T0.C0)'''),
+FROM cw_Note AS _G LEFT OUTER JOIN cw_State AS _S ON (_G.cw_in_state=_S.cw_eid AND _S.cw_name=hop)) AS _T0 ON (rel_tags0.eid_to=_T0.C0)'''),
 
     ('Any O,AD  WHERE NOT S inline1 O, S eid 123, O todo_by AD?',
      '''SELECT _O.cw_eid, rel_todo_by0.eid_to
-FROM cw_Affaire AS _O LEFT OUTER JOIN todo_by_relation AS rel_todo_by0 ON (rel_todo_by0.eid_from=_O.cw_eid), cw_Note AS _S
+FROM cw_Note AS _S, cw_Affaire AS _O LEFT OUTER JOIN todo_by_relation AS rel_todo_by0 ON (rel_todo_by0.eid_from=_O.cw_eid)
 WHERE (_S.cw_inline1 IS NULL OR _S.cw_inline1!=_O.cw_eid) AND _S.cw_eid=123''')
     ]
 
@@ -1121,7 +1126,7 @@
     ('Any PN WHERE NOT X travaille S, X nom PN, S is IN(Division, Societe)',
      '''SELECT _X.cw_nom
 FROM cw_Personne AS _X
-WHERE NOT (EXISTS(SELECT 1 FROM travaille_relation AS rel_travaille0, cw_Division AS _S WHERE rel_travaille0.eid_from=_X.cw_eid AND rel_travaille0.eid_to=_S.cw_eid UNION SELECT 1 FROM travaille_relation AS rel_travaille1, cw_Societe AS _S WHERE rel_travaille1.eid_from=_X.cw_eid AND rel_travaille1.eid_to=_S.cw_eid))'''),
+WHERE NOT (EXISTS(SELECT 1 FROM cw_Division AS _S, travaille_relation AS rel_travaille0 WHERE rel_travaille0.eid_from=_X.cw_eid AND rel_travaille0.eid_to=_S.cw_eid UNION SELECT 1 FROM cw_Societe AS _S, travaille_relation AS rel_travaille1 WHERE rel_travaille1.eid_from=_X.cw_eid AND rel_travaille1.eid_to=_S.cw_eid))'''),
 
     ('Any PN WHERE NOT X travaille S, S nom PN, S is IN(Division, Societe)',
      '''SELECT _S.cw_nom
@@ -1212,6 +1217,7 @@
                     print r, nargs
                 print '!='
                 print sql.strip()
+            print 'RQL:', rql
             raise
 
     def _parse(self, rqls):
@@ -1402,7 +1408,7 @@
 UNION ALL
 (SELECT _X.cw_eid AS C0
 FROM cw_Affaire AS _X
-WHERE ((EXISTS(SELECT 1 FROM owned_by_relation AS rel_owned_by0 WHERE rel_owned_by0.eid_from=_X.cw_eid AND rel_owned_by0.eid_to=1)) OR (((EXISTS(SELECT 1 FROM cw_Affaire AS _D LEFT OUTER JOIN concerne_relation AS rel_concerne1 ON (rel_concerne1.eid_from=_D.cw_eid) LEFT OUTER JOIN cw_Note AS _B ON (rel_concerne1.eid_to=_B.cw_eid), owned_by_relation AS rel_owned_by2 WHERE rel_owned_by2.eid_from=_B.cw_eid AND rel_owned_by2.eid_to=1 AND _X.cw_eid=_D.cw_eid)) OR (EXISTS(SELECT 1 FROM cw_Affaire AS _F LEFT OUTER JOIN concerne_relation AS rel_concerne3 ON (rel_concerne3.eid_from=_F.cw_eid) LEFT OUTER JOIN cw_Societe AS _E ON (rel_concerne3.eid_to=_E.cw_eid), owned_by_relation AS rel_owned_by4 WHERE rel_owned_by4.eid_from=_E.cw_eid AND rel_owned_by4.eid_to=1 AND _X.cw_eid=_F.cw_eid))))))) AS _T0, cw_CWEType AS _ET, is_relation AS rel_is0
+WHERE ((EXISTS(SELECT 1 FROM owned_by_relation AS rel_owned_by0 WHERE rel_owned_by0.eid_from=_X.cw_eid AND rel_owned_by0.eid_to=1)) OR (((EXISTS(SELECT 1 FROM owned_by_relation AS rel_owned_by2, cw_Affaire AS _D LEFT OUTER JOIN concerne_relation AS rel_concerne1 ON (rel_concerne1.eid_from=_D.cw_eid) LEFT OUTER JOIN cw_Note AS _B ON (rel_concerne1.eid_to=_B.cw_eid) WHERE rel_owned_by2.eid_from=_B.cw_eid AND rel_owned_by2.eid_to=1 AND _X.cw_eid=_D.cw_eid)) OR (EXISTS(SELECT 1 FROM owned_by_relation AS rel_owned_by4, cw_Affaire AS _F LEFT OUTER JOIN concerne_relation AS rel_concerne3 ON (rel_concerne3.eid_from=_F.cw_eid) LEFT OUTER JOIN cw_Societe AS _E ON (rel_concerne3.eid_to=_E.cw_eid) WHERE rel_owned_by4.eid_from=_E.cw_eid AND rel_owned_by4.eid_to=1 AND _X.cw_eid=_F.cw_eid))))))) AS _T0, cw_CWEType AS _ET, is_relation AS rel_is0
 WHERE rel_is0.eid_from=_T0.C0 AND rel_is0.eid_to=_ET.cw_eid
 GROUP BY _ET.cw_name'''),
             )):
@@ -1521,7 +1527,7 @@
     def test_ambigous_exists_no_from_clause(self):
         self._check('Any COUNT(U) WHERE U eid 1, EXISTS (P owned_by U, P is IN (Note, Affaire))',
                     '''SELECT COUNT(1)
-WHERE EXISTS(SELECT 1 FROM owned_by_relation AS rel_owned_by0, cw_Affaire AS _P WHERE rel_owned_by0.eid_from=_P.cw_eid AND rel_owned_by0.eid_to=1 UNION SELECT 1 FROM owned_by_relation AS rel_owned_by1, cw_Note AS _P WHERE rel_owned_by1.eid_from=_P.cw_eid AND rel_owned_by1.eid_to=1)''')
+WHERE EXISTS(SELECT 1 FROM cw_Affaire AS _P, owned_by_relation AS rel_owned_by0 WHERE rel_owned_by0.eid_from=_P.cw_eid AND rel_owned_by0.eid_to=1 UNION SELECT 1 FROM cw_Note AS _P, owned_by_relation AS rel_owned_by1 WHERE rel_owned_by1.eid_from=_P.cw_eid AND rel_owned_by1.eid_to=1)''')
 
     def test_attr_map_sqlcb(self):
         def generate_ref(gen, linkedvar, rel):
@@ -1570,6 +1576,26 @@
                     '''SELECT 1
 WHERE NOT (EXISTS(SELECT 1 FROM in_group_relation AS rel_in_group0))''')
 
+    def test_nonregr_subquery_missing_join(self):
+        self._check('Any COUNT(P1148),G GROUPBY G '
+                    'WHERE G owned_by D, D eid 1122, K1148 bookmarked_by P1148, '
+                    'K1148 eid 1148, P1148? in_group G',
+                    '''SELECT COUNT(rel_bookmarked_by1.eid_to), _G.cw_eid
+FROM owned_by_relation AS rel_owned_by0, cw_CWGroup AS _G LEFT OUTER JOIN in_group_relation AS rel_in_group2 ON (rel_in_group2.eid_to=_G.cw_eid) LEFT OUTER JOIN bookmarked_by_relation AS rel_bookmarked_by1 ON (rel_in_group2.eid_from=rel_bookmarked_by1.eid_to)
+WHERE rel_owned_by0.eid_from=_G.cw_eid AND rel_owned_by0.eid_to=1122 AND rel_bookmarked_by1.eid_from=1148
+GROUP BY _G.cw_eid'''
+                    )
+
+    def test_nonregr_subquery_missing_join2(self):
+        self._check('Any COUNT(P1148),G GROUPBY G '
+                    'WHERE G owned_by D, D eid 1122, K1148 bookmarked_by P1148?, '
+                    'K1148 eid 1148, P1148? in_group G',
+                    '''SELECT COUNT(rel_bookmarked_by1.eid_to), _G.cw_eid
+FROM owned_by_relation AS rel_owned_by0, cw_CWGroup AS _G LEFT OUTER JOIN in_group_relation AS rel_in_group2 ON (rel_in_group2.eid_to=_G.cw_eid) LEFT OUTER JOIN bookmarked_by_relation AS rel_bookmarked_by1 ON (rel_bookmarked_by1.eid_from=1148 AND rel_in_group2.eid_from=rel_bookmarked_by1.eid_to)
+WHERE rel_owned_by0.eid_from=_G.cw_eid AND rel_owned_by0.eid_to=1122
+GROUP BY _G.cw_eid''')
+
+
 class SqlServer2005SQLGeneratorTC(PostgresSQLGeneratorTC):
     backend = 'sqlserver2005'
     def _norm_sql(self, sql):
@@ -1625,7 +1651,7 @@
 , ROW_NUMBER() OVER (ORDER BY _L01 DESC) AS __RowNumber
 FROM (
 SELECT COUNT(rel_concerne0.eid_from) AS _L01, _C.cw_nom AS _L02 FROM  concerne_relation AS rel_concerne0, cw_Societe AS _C
-WHERE rel_concerne0.eid_to=_C.cw_eid AND ((EXISTS(SELECT 1 FROM owned_by_relation AS rel_owned_by1 WHERE rel_concerne0.eid_from=rel_owned_by1.eid_from AND rel_owned_by1.eid_to=1)) OR (EXISTS(SELECT 1 FROM documented_by_relation AS rel_documented_by2, cw_Card AS _N WHERE rel_concerne0.eid_from=rel_documented_by2.eid_from AND rel_documented_by2.eid_to=_N.cw_eid AND _N.cw_title=published)))
+WHERE rel_concerne0.eid_to=_C.cw_eid AND ((EXISTS(SELECT 1 FROM owned_by_relation AS rel_owned_by1 WHERE rel_concerne0.eid_from=rel_owned_by1.eid_from AND rel_owned_by1.eid_to=1)) OR (EXISTS(SELECT 1 FROM cw_Card AS _N, documented_by_relation AS rel_documented_by2 WHERE rel_concerne0.eid_from=rel_documented_by2.eid_from AND rel_documented_by2.eid_to=_N.cw_eid AND _N.cw_title=published)))
 GROUP BY _C.cw_nom
 ) AS _SQ1 )
 SELECT
@@ -1874,7 +1900,7 @@
         self._check('Any COUNT(U) WHERE U eid 1, EXISTS (P owned_by U, P is IN (Note, Affaire))',
                     '''SELECT COUNT(1)
 FROM (SELECT 1) AS _T
-WHERE EXISTS(SELECT 1 FROM owned_by_relation AS rel_owned_by0, cw_Affaire AS _P WHERE rel_owned_by0.eid_from=_P.cw_eid AND rel_owned_by0.eid_to=1 UNION SELECT 1 FROM owned_by_relation AS rel_owned_by1, cw_Note AS _P WHERE rel_owned_by1.eid_from=_P.cw_eid AND rel_owned_by1.eid_to=1)''')
+WHERE EXISTS(SELECT 1 FROM cw_Affaire AS _P, owned_by_relation AS rel_owned_by0 WHERE rel_owned_by0.eid_from=_P.cw_eid AND rel_owned_by0.eid_to=1 UNION SELECT 1 FROM cw_Note AS _P, owned_by_relation AS rel_owned_by1 WHERE rel_owned_by1.eid_from=_P.cw_eid AND rel_owned_by1.eid_to=1)''')
 
     def test_groupby_multiple_outerjoins(self):
         self._check('Any A,U,P,group_concat(TN) GROUPBY A,U,P WHERE A is Affaire, A concerne N, N todo_by U?, T? tags A, T name TN, A todo_by P?',