# HG changeset patch # User Sylvain Thénault # Date 1302125028 -7200 # Node ID 7eaef037ea9db1376a39ba195d67f11b78e4d78f # Parent 9e92c8558feaa4b7faabd1c2ffa29e6b52a31a4c [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 diff -r 9e92c8558fea -r 7eaef037ea9d server/rqlannotation.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 diff -r 9e92c8558fea -r 7eaef037ea9d server/sources/rql2sql.py --- 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 JOIN ON + """ + 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 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 diff -r 9e92c8558fea -r 7eaef037ea9d server/test/unittest_rql2sql.py --- 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?',