# HG changeset patch # User Sylvain Thénault # Date 1246302834 -7200 # Node ID bd0a0f2197516efab46ed1667f15c35f71980a9b # Parent ac45d4dbaf765b21bf40686de56f7fb26ab78a4c fix sql generated on NOT inlined_relation queries. Use exists, so no more needs for extra DISTINCT diff -r ac45d4dbaf76 -r bd0a0f219751 server/rqlannotation.py --- a/server/rqlannotation.py Mon Jun 29 18:42:37 2009 +0200 +++ b/server/rqlannotation.py Mon Jun 29 21:13:54 2009 +0200 @@ -22,21 +22,7 @@ has_text_query = False need_distinct = rqlst.distinct for rel in rqlst.iget_nodes(Relation): - if rel.neged(strict=True): - if rel.is_types_restriction(): - need_distinct = True - else: - rschema = getrschema(rel.r_type) - if not rschema.is_final(): - if rschema.inlined: - try: - var = rel.children[1].children[0].variable - except AttributeError: - pass # rewritten variable - else: - if not var.stinfo['constnode']: - need_distinct = True - elif getrschema(rel.r_type).symetric: + if getrschema(rel.r_type).symetric: for vref in rel.iget_nodes(VariableRef): stinfo = vref.variable.stinfo if not stinfo['constnode'] and stinfo['selected']: diff -r ac45d4dbaf76 -r bd0a0f219751 server/sources/rql2sql.py --- a/server/sources/rql2sql.py Mon Jun 29 18:42:37 2009 +0200 +++ b/server/sources/rql2sql.py Mon Jun 29 21:13:54 2009 +0200 @@ -490,10 +490,10 @@ sql.insert(1, 'FROM (SELECT 1) AS _T') sqls.append('\n'.join(sql)) if select.need_intersect: - if distinct or not self.dbms_helper.intersect_all_support: - return '\nINTERSECT\n'.join(sqls) - else: - return '\nINTERSECT ALL\n'.join(sqls) + #if distinct or not self.dbms_helper.intersect_all_support: + return '\nINTERSECT\n'.join(sqls) + #else: + # return '\nINTERSECT ALL\n'.join(sqls) elif distinct: return '\nUNION\n'.join(sqls) else: @@ -661,13 +661,27 @@ lhsvar, _, rhsvar, rhsconst = relation_info(relation) # we are sure here to have a lhsvar assert lhsvar is not None - lhssql = self._inlined_var_sql(lhsvar, relation.r_type) if isinstance(relation.parent, Not): self._state.done.add(relation.parent) - sql = "%s IS NULL" % lhssql if rhsvar is not None and not rhsvar._q_invariant: - sql = '(%s OR %s!=%s)' % (sql, lhssql, rhsvar.accept(self)) + # if the lhs variable is only linked to this relation, this mean we + # only want the relation to NOT exists + self._state.push_scope() + lhssql = self._inlined_var_sql(lhsvar, relation.r_type) + rhssql = rhsvar.accept(self) + restrictions, tables = self._state.pop_scope() + restrictions.append('%s=%s' % (lhssql, rhssql)) + if not tables: + sql = 'NOT EXISTS(SELECT 1 WHERE %s)' % ( + ' AND '.join(restrictions)) + else: + sql = 'NOT EXISTS(SELECT 1 FROM %s WHERE %s)' % ( + ', '.join(tables), ' AND '.join(restrictions)) + else: + lhssql = self._inlined_var_sql(lhsvar, relation.r_type) + sql = '%s IS NULL' % self._inlined_var_sql(lhsvar, relation.r_type) return sql + lhssql = self._inlined_var_sql(lhsvar, relation.r_type) if rhsconst is not None: return '%s=%s' % (lhssql, rhsconst.accept(self)) if isinstance(rhsvar, Variable) and not rhsvar.name in self._varmap: diff -r ac45d4dbaf76 -r bd0a0f219751 server/test/unittest_rql2sql.py --- a/server/test/unittest_rql2sql.py Mon Jun 29 18:42:37 2009 +0200 +++ b/server/test/unittest_rql2sql.py Mon Jun 29 21:13:54 2009 +0200 @@ -251,9 +251,9 @@ # Any O WHERE NOT S corrected_in O, S eid %(x)s, S concerns P, O version_of P, O in_state ST, NOT ST name "published", O modification_date MTIME ORDERBY MTIME DESC LIMIT 9 ('Any O WHERE NOT S ecrit_par O, S eid 1, S inline1 P, O inline2 P', - '''SELECT DISTINCT O.cw_eid + '''SELECT O.cw_eid FROM cw_Note AS S, cw_Personne AS O -WHERE (S.cw_ecrit_par IS NULL OR S.cw_ecrit_par!=O.cw_eid) AND S.cw_eid=1 AND O.cw_inline2=S.cw_inline1'''), +WHERE NOT EXISTS(SELECT 1 WHERE S.cw_ecrit_par=O.cw_eid) AND S.cw_eid=1 AND O.cw_inline2=S.cw_inline1'''), ('DISTINCT Any S ORDERBY stockproc(SI) WHERE NOT S ecrit_par O, S para SI', '''SELECT T1.C0 FROM (SELECT DISTINCT S.cw_eid AS C0, STOCKPROC(S.cw_para) AS C1 @@ -698,7 +698,6 @@ FROM cw_Tag AS S, cw_Tag AS T, tags_relation AS rel_tags0 WHERE NOT (T.cw_eid=28258) AND rel_tags0.eid_from=T.cw_eid AND rel_tags0.eid_to=S.cw_eid'''), - ('Any X,Y WHERE X created_by Y, X eid 5, NOT Y eid 6', '''SELECT 5, rel_created_by0.eid_to FROM created_by_relation AS rel_created_by0 @@ -736,25 +735,25 @@ 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)'''), ('Any X,T WHERE X title T, NOT X is Bookmark', - '''SELECT DISTINCT X.cw_eid, X.cw_title + '''SELECT X.cw_eid, X.cw_title FROM cw_Card AS X -UNION -SELECT DISTINCT X.cw_eid, X.cw_title +UNION ALL +SELECT X.cw_eid, X.cw_title FROM cw_EmailThread AS X'''), ('Any K,V WHERE P is CWProperty, P pkey K, P value V, NOT P for_user U', - '''SELECT DISTINCT P.cw_pkey, P.cw_value + '''SELECT P.cw_pkey, P.cw_value FROM cw_CWProperty AS P WHERE P.cw_for_user IS NULL'''), ('Any S WHERE NOT X in_state S, X is IN(Affaire, CWUser)', - '''SELECT DISTINCT S.cw_eid -FROM cw_Affaire AS X, cw_State AS S -WHERE (X.cw_in_state IS NULL OR X.cw_in_state!=S.cw_eid) + '''SELECT S.cw_eid +FROM cw_State AS S +WHERE NOT EXISTS(SELECT 1 FROM cw_Affaire AS X WHERE X.cw_in_state=S.cw_eid) INTERSECT -SELECT DISTINCT S.cw_eid -FROM cw_CWUser AS X, cw_State AS S -WHERE (X.cw_in_state IS NULL OR X.cw_in_state!=S.cw_eid)'''), +SELECT S.cw_eid +FROM cw_State AS S +WHERE NOT EXISTS(SELECT 1 FROM cw_CWUser AS X WHERE X.cw_in_state=S.cw_eid)'''), ] OUTER_JOIN = [ @@ -1030,9 +1029,9 @@ WHERE N.cw_ecrit_par=P.cw_eid AND N.cw_eid=0'''), ('Any N WHERE NOT N ecrit_par P, P nom "toto"', - '''SELECT DISTINCT N.cw_eid + '''SELECT N.cw_eid FROM cw_Note AS N, cw_Personne AS P -WHERE (N.cw_ecrit_par IS NULL OR N.cw_ecrit_par!=P.cw_eid) AND P.cw_nom=toto'''), +WHERE NOT EXISTS(SELECT 1 WHERE N.cw_ecrit_par=P.cw_eid) AND P.cw_nom=toto'''), ('Any P WHERE N ecrit_par P, N eid 0', '''SELECT N.cw_ecrit_par @@ -1045,9 +1044,9 @@ WHERE N.cw_ecrit_par=P.cw_eid AND N.cw_eid=0'''), ('Any P WHERE NOT N ecrit_par P, P is Personne, N eid 512', - '''SELECT DISTINCT P.cw_eid + '''SELECT P.cw_eid FROM cw_Note AS N, cw_Personne AS P -WHERE (N.cw_ecrit_par IS NULL OR N.cw_ecrit_par!=P.cw_eid) AND N.cw_eid=512'''), +WHERE NOT EXISTS(SELECT 1 WHERE N.cw_ecrit_par=P.cw_eid) AND N.cw_eid=512'''), ('Any S,ES,T WHERE S state_of ET, ET name "CWUser", ES allowed_transition T, T destination_state S', '''SELECT T.cw_destination_state, rel_allowed_transition1.eid_from, T.cw_eid @@ -1070,23 +1069,23 @@ INTERSECT = [ ('Any SN WHERE NOT X in_state S, S name SN', - '''SELECT DISTINCT S.cw_name -FROM cw_Affaire AS X, cw_State AS S -WHERE (X.cw_in_state IS NULL OR X.cw_in_state!=S.cw_eid) + '''SELECT S.cw_name +FROM cw_State AS S +WHERE NOT EXISTS(SELECT 1 FROM cw_Affaire AS X WHERE X.cw_in_state=S.cw_eid) INTERSECT -SELECT DISTINCT S.cw_name -FROM cw_CWUser AS X, cw_State AS S -WHERE (X.cw_in_state IS NULL OR X.cw_in_state!=S.cw_eid) +SELECT S.cw_name +FROM cw_State AS S +WHERE NOT EXISTS(SELECT 1 FROM cw_CWUser AS X WHERE X.cw_in_state=S.cw_eid) INTERSECT -SELECT DISTINCT S.cw_name -FROM cw_Note AS X, cw_State AS S -WHERE (X.cw_in_state IS NULL OR X.cw_in_state!=S.cw_eid)'''), +SELECT S.cw_name +FROM cw_State AS S +WHERE NOT EXISTS(SELECT 1 FROM cw_Note AS X WHERE X.cw_in_state=S.cw_eid)'''), ('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) -INTERSECT ALL +INTERSECT SELECT X.cw_nom FROM cw_Personne AS X WHERE NOT EXISTS(SELECT 1 FROM travaille_relation AS rel_travaille0,cw_Societe AS S WHERE rel_travaille0.eid_from=X.cw_eid AND rel_travaille0.eid_to=S.cw_eid)'''),