# HG changeset patch
# User sylvain.thenault@logilab.fr
# Date 1232127073 -3600
# Node ID 69b79faefa9434146a924df7f73f2072a28e1e36
# Parent  5d8dc9678707f2282a5751427e081c632bb86e96
need_intersect test and fixes

diff -r 5d8dc9678707 -r 69b79faefa94 server/querier.py
--- a/server/querier.py	Thu Jan 15 17:37:43 2009 +0100
+++ b/server/querier.py	Fri Jan 16 18:31:13 2009 +0100
@@ -196,7 +196,7 @@
             self._insert_security(union, noinvariant)
         self.rqlhelper.simplify(union)
         self.sqlannotate(union)
-        set_qdata(union, noinvariant)
+        set_qdata(self.schema.rschema, union, noinvariant)
         if union.has_text_query:
             self.cache_key = None
 
diff -r 5d8dc9678707 -r 69b79faefa94 server/rqlannotation.py
--- a/server/rqlannotation.py	Thu Jan 15 17:37:43 2009 +0100
+++ b/server/rqlannotation.py	Fri Jan 16 18:31:13 2009 +0100
@@ -20,7 +20,7 @@
     #if server.DEBUG:
     #    print '-------- sql annotate', repr(rqlst)
     getrschema = annotator.schema.rschema
-    has_text_query = need_intersect = False
+    has_text_query = False
     need_distinct = rqlst.distinct
     for rel in rqlst.iget_nodes(Relation):
         if rel.neged(strict=True):
@@ -29,13 +29,6 @@
             else:
                 rschema = getrschema(rel.r_type)
                 if not rschema.is_final():
-                    # if one of the relation's variable is ambiguous, an intersection
-                    # will be necessary
-                    for vref in rel.get_nodes(VariableRef):
-                        var = vref.variable
-                        if not var.stinfo['selected'] and len(var.stinfo['possibletypes']) > 1:
-                            need_intersect = True
-                            break
                     if rschema.inlined:
                         try:
                             var = rel.children[1].children[0].variable
@@ -147,7 +140,6 @@
             except CantSelectPrincipal:
                 stinfo['invariant'] = False
     rqlst.need_distinct = need_distinct
-    rqlst.need_intersect = need_intersect
     return has_text_query
 
 
@@ -207,12 +199,12 @@
     return principal
 
 
-def set_qdata(union, noinvariant):
+def set_qdata(getrschema, union, noinvariant):
     """recursive function to set querier data on variables in the syntax tree
     """
     for select in union.children:
         for subquery in select.with_:
-            set_qdata(subquery.query, noinvariant)
+            set_qdata(getrschema, subquery.query, noinvariant)
         for var in select.defined_vars.itervalues():
             if var.stinfo['invariant']:
                 if var in noinvariant and not var.stinfo['principal'].r_type == 'has_text':
@@ -221,6 +213,23 @@
                     var._q_invariant = True
             else:
                 var._q_invariant = False
+        for rel in select.iget_nodes(Relation):
+            if rel.neged(strict=True) and not rel.is_types_restriction():
+                rschema = getrschema(rel.r_type)
+                if not rschema.is_final():
+                    # if one of the relation's variable is ambiguous but not
+                    # invariant, an intersection will be necessary
+                    for vref in rel.get_nodes(VariableRef):
+                        var = vref.variable
+                        if (not var._q_invariant and var.valuable_references() == 1
+                            and len(var.stinfo['possibletypes']) > 1):
+                            select.need_intersect = True
+                            break
+                    else:
+                        continue
+                    break
+        else:
+            select.need_intersect = False
 
 
 class SQLGenAnnotator(object):
diff -r 5d8dc9678707 -r 69b79faefa94 server/sources/native.py
--- a/server/sources/native.py	Thu Jan 15 17:37:43 2009 +0100
+++ b/server/sources/native.py	Fri Jan 16 18:31:13 2009 +0100
@@ -188,7 +188,7 @@
         rqlst.restricted_vars = ()
         rqlst.children[0].solutions = self._sols
         self.repo.querier.sqlgen_annotate(rqlst)
-        set_qdata(rqlst, ())
+        set_qdata(self.schema.rschema, rqlst, ())
         return rqlst
     
     def set_schema(self, schema):
diff -r 5d8dc9678707 -r 69b79faefa94 server/sources/rql2sql.py
--- a/server/sources/rql2sql.py	Thu Jan 15 17:37:43 2009 +0100
+++ b/server/sources/rql2sql.py	Fri Jan 16 18:31:13 2009 +0100
@@ -488,7 +488,8 @@
                 sql.insert(1, 'FROM (SELECT 1) AS _T')
             sqls.append('\n'.join(sql))
         if select.need_intersect:
-            if distinct:
+            # XXX use getattr for lgc bw compat, remove once 0.37.3 is out
+            if distinct or not getattr(self.dbms_helper, 'intersect_all_support', True):
                 return '\nINTERSECT\n'.join(sqls)
             else:
                 return '\nINTERSECT ALL\n'.join(sqls)
diff -r 5d8dc9678707 -r 69b79faefa94 server/test/unittest_rql2sql.py
--- a/server/test/unittest_rql2sql.py	Thu Jan 15 17:37:43 2009 +0100
+++ b/server/test/unittest_rql2sql.py	Fri Jan 16 18:31:13 2009 +0100
@@ -156,11 +156,6 @@
 ]
 
 ADVANCED= [
-    ('Any X WHERE X is ET, ET eid 2',
-     '''SELECT rel_is0.eid_from
-FROM is_relation AS rel_is0
-WHERE rel_is0.eid_to=2'''),
-
 
     ("Societe S WHERE S nom 'Logilab' OR S nom 'Caesium'",
      '''SELECT S.eid
@@ -1064,7 +1059,57 @@
     
     ]
 
+INTERSECT = [
+    ('Any SN WHERE NOT X in_state S, S name SN',
+     '''SELECT DISTINCT S.name
+FROM Affaire AS X, State AS S
+WHERE (X.in_state IS NULL OR X.in_state!=S.eid)
+INTERSECT
+SELECT DISTINCT S.name
+FROM EUser AS X, State AS S
+WHERE (X.in_state IS NULL OR X.in_state!=S.eid)
+INTERSECT
+SELECT DISTINCT S.name
+FROM Note AS X, State AS S
+WHERE (X.in_state IS NULL OR X.in_state!=S.eid)'''),
 
+    ('Any PN WHERE NOT X travaille S, X nom PN, S is IN(Division, Societe)',
+     '''SELECT X.nom
+FROM Personne AS X
+WHERE NOT EXISTS(SELECT 1 FROM travaille_relation AS rel_travaille0,Division AS S WHERE rel_travaille0.eid_from=X.eid AND rel_travaille0.eid_to=S.eid)
+INTERSECT ALL
+SELECT X.nom
+FROM Personne AS X
+WHERE NOT EXISTS(SELECT 1 FROM travaille_relation AS rel_travaille0,Societe AS S WHERE rel_travaille0.eid_from=X.eid AND rel_travaille0.eid_to=S.eid)'''),
+    
+    ('Any PN WHERE NOT X travaille S, S nom PN, S is IN(Division, Societe)',
+     '''SELECT S.nom
+FROM Division AS S
+WHERE NOT EXISTS(SELECT 1 FROM travaille_relation AS rel_travaille0 WHERE rel_travaille0.eid_to=S.eid)
+UNION ALL
+SELECT S.nom
+FROM Societe AS S
+WHERE NOT EXISTS(SELECT 1 FROM travaille_relation AS rel_travaille0 WHERE rel_travaille0.eid_to=S.eid)'''),
+    
+    ('Personne X WHERE NOT X travaille S, S nom "chouette"',
+     '''SELECT X.eid
+FROM Division AS S, Personne AS X
+WHERE NOT EXISTS(SELECT 1 FROM travaille_relation AS rel_travaille0 WHERE rel_travaille0.eid_from=X.eid AND rel_travaille0.eid_to=S.eid) AND S.nom=chouette
+UNION ALL
+SELECT X.eid
+FROM Personne AS X, Societe AS S
+WHERE NOT EXISTS(SELECT 1 FROM travaille_relation AS rel_travaille0 WHERE rel_travaille0.eid_from=X.eid AND rel_travaille0.eid_to=S.eid) AND S.nom=chouette
+UNION ALL
+SELECT X.eid
+FROM Personne AS X, SubDivision AS S
+WHERE NOT EXISTS(SELECT 1 FROM travaille_relation AS rel_travaille0 WHERE rel_travaille0.eid_from=X.eid AND rel_travaille0.eid_to=S.eid) AND S.nom=chouette'''),
+    
+    ('Any X WHERE X is ET, ET eid 2',
+     '''SELECT rel_is0.eid_from
+FROM is_relation AS rel_is0
+WHERE rel_is0.eid_to=2'''),
+
+    ]
 from logilab.common.adbh import ADV_FUNC_HELPER_DIRECTORY
     
 class PostgresSQLGeneratorTC(RQLGeneratorTC):
@@ -1196,6 +1241,10 @@
     def test_negation(self):
         for t in self._parse(NEGATIONS):
             yield t
+        
+    def test_intersection(self):
+        for t in self._parse(INTERSECT):
+            yield t
 
     def test_union(self):
         for t in self._parse((
@@ -1370,7 +1419,7 @@
         self.o = SQLGenerator(schema, dbms_helper)
 
     def _norm_sql(self, sql):
-        return sql.strip().replace(' ILIKE ', ' LIKE ')
+        return sql.strip().replace(' ILIKE ', ' LIKE ').replace('\nINTERSECT ALL\n', '\nINTERSECT\n')
 
     def test_union(self):
         for t in self._parse((