--- 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((