cubicweb: comparison server/test/unittest

equal deleted inserted replaced

-:6b9fee0c5c42
+:a56eb02f9ce7
 ("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 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))))'''),
 ]
 # 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 _O.cw_eid
 FROM cw_Note AS _S, cw_Personne AS _O
-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'''),
+WHERE NOT (_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
 FROM cw_Note AS _S
 WHERE _S.cw_ecrit_par IS NULL
 (' Any X,U WHERE C owned_by U, NOT X owned_by U, C eid 1, X eid 2',
 '''SELECT 2, rel_owned_by0.eid_to
 FROM owned_by_relation AS rel_owned_by0
-WHERE rel_owned_by0.eid_from=1 AND NOT EXISTS(SELECT 1 FROM owned_by_relation AS rel_owned_by1 WHERE rel_owned_by1.eid_from=2 AND rel_owned_by0.eid_to=rel_owned_by1.eid_to)'''),
+WHERE rel_owned_by0.eid_from=1 AND NOT (EXISTS(SELECT 1 FROM owned_by_relation AS rel_owned_by1 WHERE rel_owned_by1.eid_from=2 AND rel_owned_by0.eid_to=rel_owned_by1.eid_to))'''),
 ('Any GN WHERE X in_group G, G name GN, (G name "managers" OR EXISTS(X copain T, T login in ("comme", "cochon")))',
 '''SELECT _G.cw_name
 FROM cw_CWGroup AS _G, in_group_relation AS rel_in_group0
 WHERE rel_in_group0.eid_to=_G.cw_eid AND ((_G.cw_name=managers) OR (EXISTS(SELECT 1 FROM copain_relation AS rel_copain1, cw_CWUser AS _T WHERE rel_copain1.eid_from=rel_in_group0.eid_from AND rel_copain1.eid_to=_T.cw_eid AND _T.cw_login IN(comme, cochon))))'''),
 WHERE _X.cw_login=admin AND _X.cw_eid=_Y.cw_eid'''),
 ('Any L WHERE X login "admin", NOT X identity Y, Y login L',
 '''SELECT _Y.cw_login
 FROM cw_CWUser AS _X, cw_CWUser AS _Y
-WHERE _X.cw_login=admin AND NOT _X.cw_eid=_Y.cw_eid'''),
+WHERE _X.cw_login=admin AND NOT (_X.cw_eid=_Y.cw_eid)'''),
 ('Any L WHERE X login "admin", X identity Y?, Y login L',
 '''SELECT _Y.cw_login
 FROM cw_CWUser AS _X LEFT OUTER JOIN cw_CWUser AS _Y ON (_X.cw_eid=_Y.cw_eid)
 WHERE _X.cw_login=admin'''),
 # DISTINCT but NEGED exists, can't be invariant
 ('DISTINCT Any X,Y WHERE X name "CWGroup", Y eid IN(1, 2, 3), NOT EXISTS(X read_permission Y)',
 '''SELECT DISTINCT _X.cw_eid, _Y.cw_eid
 FROM cw_CWEType AS _X, cw_CWGroup AS _Y
-WHERE _X.cw_name=CWGroup AND _Y.cw_eid IN(1, 2, 3) AND NOT EXISTS(SELECT 1 FROM read_permission_relation AS rel_read_permission0 WHERE rel_read_permission0.eid_from=_X.cw_eid AND rel_read_permission0.eid_to=_Y.cw_eid)
+WHERE _X.cw_name=CWGroup AND _Y.cw_eid IN(1, 2, 3) AND NOT (EXISTS(SELECT 1 FROM read_permission_relation AS rel_read_permission0 WHERE rel_read_permission0.eid_from=_X.cw_eid AND rel_read_permission0.eid_to=_Y.cw_eid))
 UNION
 SELECT DISTINCT _X.cw_eid, _Y.cw_eid
 FROM cw_CWEType AS _X, cw_RQLExpression AS _Y
-WHERE _X.cw_name=CWGroup AND _Y.cw_eid IN(1, 2, 3) AND NOT EXISTS(SELECT 1 FROM read_permission_relation AS rel_read_permission0 WHERE rel_read_permission0.eid_from=_X.cw_eid AND rel_read_permission0.eid_to=_Y.cw_eid)'''),
+WHERE _X.cw_name=CWGroup AND _Y.cw_eid IN(1, 2, 3) AND NOT (EXISTS(SELECT 1 FROM read_permission_relation AS rel_read_permission0 WHERE rel_read_permission0.eid_from=_X.cw_eid AND rel_read_permission0.eid_to=_Y.cw_eid))'''),
 # should generate the same query as above
 ('DISTINCT Any X,Y WHERE X name "CWGroup", Y eid IN(1, 2, 3), NOT X read_permission Y',
 '''SELECT DISTINCT _X.cw_eid, _Y.cw_eid
 FROM cw_CWEType AS _X, cw_CWGroup AS _Y
-WHERE _X.cw_name=CWGroup AND _Y.cw_eid IN(1, 2, 3) AND NOT EXISTS(SELECT 1 FROM read_permission_relation AS rel_read_permission0 WHERE rel_read_permission0.eid_from=_X.cw_eid AND rel_read_permission0.eid_to=_Y.cw_eid)
+WHERE _X.cw_name=CWGroup AND _Y.cw_eid IN(1, 2, 3) AND NOT (EXISTS(SELECT 1 FROM read_permission_relation AS rel_read_permission0 WHERE rel_read_permission0.eid_from=_X.cw_eid AND rel_read_permission0.eid_to=_Y.cw_eid))
 UNION
 SELECT DISTINCT _X.cw_eid, _Y.cw_eid
 FROM cw_CWEType AS _X, cw_RQLExpression AS _Y
-WHERE _X.cw_name=CWGroup AND _Y.cw_eid IN(1, 2, 3) AND NOT EXISTS(SELECT 1 FROM read_permission_relation AS rel_read_permission0 WHERE rel_read_permission0.eid_from=_X.cw_eid AND rel_read_permission0.eid_to=_Y.cw_eid)'''),
+WHERE _X.cw_name=CWGroup AND _Y.cw_eid IN(1, 2, 3) AND NOT (EXISTS(SELECT 1 FROM read_permission_relation AS rel_read_permission0 WHERE rel_read_permission0.eid_from=_X.cw_eid AND rel_read_permission0.eid_to=_Y.cw_eid))'''),
 # neged relation, can't be inveriant
 ('Any X,Y WHERE X name "CWGroup", Y eid IN(1, 2, 3), NOT X read_permission Y',
 '''SELECT _X.cw_eid, _Y.cw_eid
 FROM cw_CWEType AS _X, cw_CWGroup AS _Y
-WHERE _X.cw_name=CWGroup AND _Y.cw_eid IN(1, 2, 3) AND NOT EXISTS(SELECT 1 FROM read_permission_relation AS rel_read_permission0 WHERE rel_read_permission0.eid_from=_X.cw_eid AND rel_read_permission0.eid_to=_Y.cw_eid)
+WHERE _X.cw_name=CWGroup AND _Y.cw_eid IN(1, 2, 3) AND NOT (EXISTS(SELECT 1 FROM read_permission_relation AS rel_read_permission0 WHERE rel_read_permission0.eid_from=_X.cw_eid AND rel_read_permission0.eid_to=_Y.cw_eid))
 UNION ALL
 SELECT _X.cw_eid, _Y.cw_eid
 FROM cw_CWEType AS _X, cw_RQLExpression AS _Y
-WHERE _X.cw_name=CWGroup AND _Y.cw_eid IN(1, 2, 3) AND NOT EXISTS(SELECT 1 FROM read_permission_relation AS rel_read_permission0 WHERE rel_read_permission0.eid_from=_X.cw_eid AND rel_read_permission0.eid_to=_Y.cw_eid)'''),
+WHERE _X.cw_name=CWGroup AND _Y.cw_eid IN(1, 2, 3) AND NOT (EXISTS(SELECT 1 FROM read_permission_relation AS rel_read_permission0 WHERE rel_read_permission0.eid_from=_X.cw_eid AND rel_read_permission0.eid_to=_Y.cw_eid))'''),
 ('Any MAX(X)+MIN(X), N GROUPBY N WHERE X name N, X is IN (Basket, Folder, Tag);',
 '''SELECT (MAX(T1.C0) + MIN(T1.C0)), T1.C1 FROM (SELECT _X.cw_eid AS C0, _X.cw_name AS C1
 FROM cw_Basket AS _X
 UNION ALL
 ('Any O,AA,AB,AC ORDERBY AC DESC '
 'WHERE NOT S use_email O, S eid 1, O is EmailAddress, O address AA, O alias AB, O modification_date AC, '
 'EXISTS(A use_email O, EXISTS(A identity B, NOT B in_group D, D name "guests", D is CWGroup), A is CWUser), B eid 2',
 '''SELECT _O.cw_eid, _O.cw_address, _O.cw_alias, _O.cw_modification_date
 FROM cw_EmailAddress AS _O
-WHERE NOT EXISTS(SELECT 1 FROM use_email_relation AS rel_use_email0 WHERE rel_use_email0.eid_from=1 AND rel_use_email0.eid_to=_O.cw_eid) AND EXISTS(SELECT 1 FROM use_email_relation AS rel_use_email1 WHERE rel_use_email1.eid_to=_O.cw_eid AND EXISTS(SELECT 1 FROM cw_CWGroup AS _D WHERE rel_use_email1.eid_from=2 AND NOT EXISTS(SELECT 1 FROM in_group_relation AS rel_in_group2 WHERE rel_in_group2.eid_from=2 AND rel_in_group2.eid_to=_D.cw_eid) AND _D.cw_name=guests))
+WHERE NOT (EXISTS(SELECT 1 FROM use_email_relation AS rel_use_email0 WHERE rel_use_email0.eid_from=1 AND rel_use_email0.eid_to=_O.cw_eid)) AND EXISTS(SELECT 1 FROM use_email_relation AS rel_use_email1 WHERE rel_use_email1.eid_to=_O.cw_eid AND EXISTS(SELECT 1 FROM cw_CWGroup AS _D WHERE rel_use_email1.eid_from=2 AND NOT (EXISTS(SELECT 1 FROM in_group_relation AS rel_in_group2 WHERE rel_in_group2.eid_from=2 AND rel_in_group2.eid_to=_D.cw_eid)) AND _D.cw_name=guests))
 ORDER BY 4 DESC'''),
 ("Any X WHERE X eid 0, X test TRUE",
 '''SELECT _X.cw_eid
 NEGATIONS = [
 ("Personne X WHERE NOT X evaluee Y;",
 '''SELECT _X.cw_eid
 FROM cw_Personne AS _X
-WHERE NOT EXISTS(SELECT 1 FROM evaluee_relation AS rel_evaluee0 WHERE rel_evaluee0.eid_from=_X.cw_eid)'''),
+WHERE NOT (EXISTS(SELECT 1 FROM evaluee_relation AS rel_evaluee0 WHERE rel_evaluee0.eid_from=_X.cw_eid))'''),
 ("Note N WHERE NOT X evaluee N, X eid 0",
 '''SELECT _N.cw_eid
 FROM cw_Note AS _N
-WHERE NOT EXISTS(SELECT 1 FROM evaluee_relation AS rel_evaluee0 WHERE rel_evaluee0.eid_from=0 AND rel_evaluee0.eid_to=_N.cw_eid)'''),
+WHERE NOT (EXISTS(SELECT 1 FROM evaluee_relation AS rel_evaluee0 WHERE rel_evaluee0.eid_from=0 AND rel_evaluee0.eid_to=_N.cw_eid))'''),
 ('Any X WHERE NOT X travaille S, X is Personne',
 '''SELECT _X.cw_eid
 FROM cw_Personne AS _X
-WHERE NOT EXISTS(SELECT 1 FROM travaille_relation AS rel_travaille0 WHERE rel_travaille0.eid_from=_X.cw_eid)'''),
+WHERE NOT (EXISTS(SELECT 1 FROM travaille_relation AS rel_travaille0 WHERE rel_travaille0.eid_from=_X.cw_eid))'''),
 ("Personne P where not P datenaiss TODAY",
 '''SELECT _P.cw_eid
 FROM cw_Personne AS _P
 WHERE NOT (DATE(_P.cw_datenaiss)=CURRENT_DATE)'''),
 ("Personne P where NOT P concerne A",
 '''SELECT _P.cw_eid
 FROM cw_Personne AS _P
-WHERE NOT EXISTS(SELECT 1 FROM concerne_relation AS rel_concerne0 WHERE rel_concerne0.eid_from=_P.cw_eid)'''),
+WHERE NOT (EXISTS(SELECT 1 FROM concerne_relation AS rel_concerne0 WHERE rel_concerne0.eid_from=_P.cw_eid))'''),
 ("Affaire A where not P concerne A",
 '''SELECT _A.cw_eid
 FROM cw_Affaire AS _A
-WHERE NOT EXISTS(SELECT 1 FROM concerne_relation AS rel_concerne0 WHERE rel_concerne0.eid_to=_A.cw_eid)'''),
+WHERE NOT (EXISTS(SELECT 1 FROM concerne_relation AS rel_concerne0 WHERE rel_concerne0.eid_to=_A.cw_eid))'''),
 ("Personne P where not P concerne A, A sujet ~= 'TEST%'",
 '''SELECT _P.cw_eid
 FROM cw_Affaire AS _A, cw_Personne AS _P
-WHERE NOT EXISTS(SELECT 1 FROM concerne_relation AS rel_concerne0 WHERE rel_concerne0.eid_from=_P.cw_eid AND rel_concerne0.eid_to=_A.cw_eid) AND _A.cw_sujet ILIKE TEST%'''),
+WHERE NOT (EXISTS(SELECT 1 FROM concerne_relation AS rel_concerne0 WHERE rel_concerne0.eid_from=_P.cw_eid AND rel_concerne0.eid_to=_A.cw_eid)) AND _A.cw_sujet ILIKE TEST%'''),
 ('Any S WHERE NOT T eid 28258, T tags S',
 '''SELECT rel_tags0.eid_to
 FROM tags_relation AS rel_tags0
 WHERE NOT (rel_tags0.eid_from=28258)'''),
 WHERE rel_created_by0.eid_from=5 AND NOT (rel_created_by0.eid_to=6)'''),
 ('Note X WHERE NOT Y evaluee X',
 '''SELECT _X.cw_eid
 FROM cw_Note AS _X
-WHERE NOT EXISTS(SELECT 1 FROM evaluee_relation AS rel_evaluee0 WHERE rel_evaluee0.eid_to=_X.cw_eid)'''),
+WHERE NOT (EXISTS(SELECT 1 FROM evaluee_relation AS rel_evaluee0 WHERE rel_evaluee0.eid_to=_X.cw_eid))'''),
 ('Any Y WHERE NOT Y evaluee X',
 '''SELECT _Y.cw_eid
 FROM cw_CWUser AS _Y
-WHERE NOT EXISTS(SELECT 1 FROM evaluee_relation AS rel_evaluee0 WHERE rel_evaluee0.eid_from=_Y.cw_eid)
+WHERE NOT (EXISTS(SELECT 1 FROM evaluee_relation AS rel_evaluee0 WHERE rel_evaluee0.eid_from=_Y.cw_eid))
 UNION ALL
 SELECT _Y.cw_eid
 FROM cw_Division AS _Y
-WHERE NOT EXISTS(SELECT 1 FROM evaluee_relation AS rel_evaluee0 WHERE rel_evaluee0.eid_from=_Y.cw_eid)
+WHERE NOT (EXISTS(SELECT 1 FROM evaluee_relation AS rel_evaluee0 WHERE rel_evaluee0.eid_from=_Y.cw_eid))
 UNION ALL
 SELECT _Y.cw_eid
 FROM cw_Personne AS _Y
-WHERE NOT EXISTS(SELECT 1 FROM evaluee_relation AS rel_evaluee0 WHERE rel_evaluee0.eid_from=_Y.cw_eid)
+WHERE NOT (EXISTS(SELECT 1 FROM evaluee_relation AS rel_evaluee0 WHERE rel_evaluee0.eid_from=_Y.cw_eid))
 UNION ALL
 SELECT _Y.cw_eid
 FROM cw_Societe AS _Y
-WHERE NOT EXISTS(SELECT 1 FROM evaluee_relation AS rel_evaluee0 WHERE rel_evaluee0.eid_from=_Y.cw_eid)
+WHERE NOT (EXISTS(SELECT 1 FROM evaluee_relation AS rel_evaluee0 WHERE rel_evaluee0.eid_from=_Y.cw_eid))
 UNION ALL
 SELECT _Y.cw_eid
 FROM cw_SubDivision AS _Y
-WHERE NOT EXISTS(SELECT 1 FROM evaluee_relation AS rel_evaluee0 WHERE rel_evaluee0.eid_from=_Y.cw_eid)'''),
+WHERE NOT (EXISTS(SELECT 1 FROM evaluee_relation AS rel_evaluee0 WHERE rel_evaluee0.eid_from=_Y.cw_eid))'''),
 ('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 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,RT WHERE X relation_type RT, NOT X is CWAttribute',
 '''SELECT _X.cw_eid, _X.cw_relation_type
 FROM cw_CWRelation AS _X
 WHERE _X.cw_relation_type IS NOT NULL'''),
 WHERE _P.cw_for_user IS NULL'''),
 ('Any S WHERE NOT X in_state S, X is IN(Affaire, CWUser)',
 '''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)
+WHERE NOT (EXISTS(SELECT 1 FROM cw_Affaire AS _X WHERE _X.cw_in_state=_S.cw_eid UNION SELECT 1 FROM cw_CWUser AS _X WHERE _X.cw_in_state=_S.cw_eid))'''),
-INTERSECT
-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)'''),
 ('Any S WHERE NOT(X in_state S, S name "somename"), X is CWUser',
 '''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 AND _S.cw_name=somename)'''),
+WHERE NOT (EXISTS(SELECT 1 FROM cw_CWUser AS _X WHERE _X.cw_in_state=_S.cw_eid AND _S.cw_name=somename))'''),
 # XXXFIXME fail
 #         ('Any X,RT WHERE X relation_type RT?, NOT X is CWAttribute',
 #      '''SELECT _X.cw_eid, _X.cw_relation_type
 # FROM cw_CWRelation AS _X'''),
 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
-WHERE NOT EXISTS(SELECT 1 WHERE _S.cw_inline1=_O.cw_eid) AND _S.cw_eid=123''')
+WHERE NOT (_S.cw_inline1=_O.cw_eid) AND _S.cw_eid=123''')
 ]
 VIRTUAL_VARS = [
 ('Any X WHERE X is CWUser, X creation_date > D1, Y creation_date D1, Y login "SWEB09"',
 ("Any COUNT(P) WHERE P is Personne",
 '''SELECT COUNT(_P.cw_eid)
 FROM cw_Personne AS _P'''),
 ]
-SYMETRIC = [
+SYMMETRIC = [
 ('Any P WHERE X eid 0, X connait P',
 '''SELECT DISTINCT _P.cw_eid
 FROM connait_relation AS rel_connait0, cw_Personne AS _P
 WHERE (rel_connait0.eid_from=0 AND rel_connait0.eid_to=_P.cw_eid OR rel_connait0.eid_to=0 AND rel_connait0.eid_from=_P.cw_eid)'''
 ),
 ),
 ('Any P WHERE X eid 0, NOT X connait P',
 '''SELECT _P.cw_eid
 FROM cw_Personne AS _P
-WHERE NOT EXISTS(SELECT 1 FROM connait_relation AS rel_connait0 WHERE (rel_connait0.eid_from=0 AND rel_connait0.eid_to=_P.cw_eid OR rel_connait0.eid_to=0 AND rel_connait0.eid_from=_P.cw_eid))'''),
+WHERE NOT (EXISTS(SELECT 1 FROM connait_relation AS rel_connait0 WHERE (rel_connait0.eid_from=0 AND rel_connait0.eid_to=_P.cw_eid OR rel_connait0.eid_to=0 AND rel_connait0.eid_from=_P.cw_eid)))'''),
 ('Any P WHERE NOT X connait P',
 '''SELECT _P.cw_eid
 FROM cw_Personne AS _P
-WHERE NOT EXISTS(SELECT 1 FROM connait_relation AS rel_connait0 WHERE (rel_connait0.eid_to=_P.cw_eid OR rel_connait0.eid_from=_P.cw_eid))'''),
+WHERE NOT (EXISTS(SELECT 1 FROM connait_relation AS rel_connait0 WHERE (rel_connait0.eid_to=_P.cw_eid OR rel_connait0.eid_from=_P.cw_eid)))'''),
 ('Any X WHERE NOT X connait P',
 '''SELECT _X.cw_eid
 FROM cw_Personne AS _X
-WHERE NOT EXISTS(SELECT 1 FROM connait_relation AS rel_connait0 WHERE (rel_connait0.eid_from=_X.cw_eid OR rel_connait0.eid_to=_X.cw_eid))'''),
+WHERE NOT (EXISTS(SELECT 1 FROM connait_relation AS rel_connait0 WHERE (rel_connait0.eid_from=_X.cw_eid OR rel_connait0.eid_to=_X.cw_eid)))'''),
 ('Any P WHERE X connait P, P nom "nom"',
 '''SELECT DISTINCT _P.cw_eid
 FROM connait_relation AS rel_connait0, cw_Personne AS _P
 WHERE (rel_connait0.eid_to=_P.cw_eid OR rel_connait0.eid_from=_P.cw_eid) AND _P.cw_nom=nom'''),
 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 _N.cw_eid
 FROM cw_Note AS _N, cw_Personne AS _P
-WHERE NOT EXISTS(SELECT 1 WHERE _N.cw_ecrit_par=_P.cw_eid) AND _P.cw_nom=toto'''),
+WHERE NOT (_N.cw_ecrit_par=_P.cw_eid) AND _P.cw_nom=toto'''),
+('Any P WHERE NOT N ecrit_par P, P nom "toto"',
+'''SELECT _P.cw_eid
+FROM cw_Personne AS _P
+WHERE NOT (EXISTS(SELECT 1 FROM cw_Note AS _N 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
 FROM cw_Note AS _N
 WHERE _N.cw_ecrit_par IS NOT NULL AND _N.cw_eid=0'''),
 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 _P.cw_eid
 FROM cw_Note AS _N, cw_Personne AS _P
-WHERE NOT EXISTS(SELECT 1 WHERE _N.cw_ecrit_par=_P.cw_eid) AND _N.cw_eid=512'''),
+WHERE NOT (_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
 FROM allowed_transition_relation AS rel_allowed_transition1, cw_Transition AS _T, cw_Workflow AS _ET, state_of_relation AS rel_state_of0
 WHERE _T.cw_destination_state=rel_state_of0.eid_from AND rel_state_of0.eid_to=_ET.cw_eid AND _ET.cw_name=CWUser AND rel_allowed_transition1.eid_to=_T.cw_eid'''),
 FROM cw_Note AS _S
 WHERE _S.cw_eid=0 AND _S.cw_in_state IS NOT NULL'''),
 ('Any X WHERE NOT Y for_user X, X eid 123',
 '''SELECT 123
-WHERE NOT EXISTS(SELECT 1 FROM cw_CWProperty AS _Y WHERE _Y.cw_for_user=123)
+WHERE NOT (EXISTS(SELECT 1 FROM cw_CWProperty AS _Y WHERE _Y.cw_for_user=123))
 '''),
 ]
 INTERSECT = [
 ('Any SN WHERE NOT X in_state S, S name SN',
 '''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)
+WHERE NOT (EXISTS(SELECT 1 FROM cw_Affaire AS _X WHERE _X.cw_in_state=_S.cw_eid UNION SELECT 1 FROM cw_Note AS _X WHERE _X.cw_in_state=_S.cw_eid UNION SELECT 1 FROM cw_CWUser AS _X WHERE _X.cw_in_state=_S.cw_eid))'''),
-INTERSECT
-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 _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)
+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))'''),
-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)'''),
 ('Any PN WHERE NOT X travaille S, S nom PN, S is IN(Division, Societe)',
 '''SELECT _S.cw_nom
 FROM cw_Division AS _S
-WHERE NOT EXISTS(SELECT 1 FROM travaille_relation AS rel_travaille0 WHERE rel_travaille0.eid_to=_S.cw_eid)
+WHERE NOT (EXISTS(SELECT 1 FROM travaille_relation AS rel_travaille0 WHERE rel_travaille0.eid_to=_S.cw_eid))
 UNION ALL
 SELECT _S.cw_nom
 FROM cw_Societe AS _S
-WHERE NOT EXISTS(SELECT 1 FROM travaille_relation AS rel_travaille0 WHERE rel_travaille0.eid_to=_S.cw_eid)'''),
+WHERE NOT (EXISTS(SELECT 1 FROM travaille_relation AS rel_travaille0 WHERE rel_travaille0.eid_to=_S.cw_eid))'''),
 ('Personne X WHERE NOT X travaille S, S nom "chouette"',
 '''SELECT _X.cw_eid
 FROM cw_Division AS _S, cw_Personne AS _X
-WHERE NOT EXISTS(SELECT 1 FROM travaille_relation AS rel_travaille0 WHERE rel_travaille0.eid_from=_X.cw_eid AND rel_travaille0.eid_to=_S.cw_eid) AND _S.cw_nom=chouette
+WHERE NOT (EXISTS(SELECT 1 FROM travaille_relation AS rel_travaille0 WHERE rel_travaille0.eid_from=_X.cw_eid AND rel_travaille0.eid_to=_S.cw_eid)) AND _S.cw_nom=chouette
 UNION ALL
 SELECT _X.cw_eid
 FROM cw_Personne AS _X, cw_Societe AS _S
-WHERE NOT EXISTS(SELECT 1 FROM travaille_relation AS rel_travaille0 WHERE rel_travaille0.eid_from=_X.cw_eid AND rel_travaille0.eid_to=_S.cw_eid) AND _S.cw_nom=chouette
+WHERE NOT (EXISTS(SELECT 1 FROM travaille_relation AS rel_travaille0 WHERE rel_travaille0.eid_from=_X.cw_eid AND rel_travaille0.eid_to=_S.cw_eid)) AND _S.cw_nom=chouette
 UNION ALL
 SELECT _X.cw_eid
 FROM cw_Personne AS _X, cw_SubDivision AS _S
-WHERE NOT EXISTS(SELECT 1 FROM travaille_relation AS rel_travaille0 WHERE rel_travaille0.eid_from=_X.cw_eid AND rel_travaille0.eid_to=_S.cw_eid) AND _S.cw_nom=chouette'''),
+WHERE NOT (EXISTS(SELECT 1 FROM travaille_relation AS rel_travaille0 WHERE rel_travaille0.eid_from=_X.cw_eid AND rel_travaille0.eid_to=_S.cw_eid)) AND _S.cw_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'''),
 ' (Any X WHERE X is Transition))')
 rqlst = self._prepare(rql)
 self.assertRaises(BadRQLQuery, self.o.generate, rqlst)
 def test_symmetric(self):
-for t in self._parse(SYMETRIC):
+for t in self._parse(SYMMETRIC):
 yield t
 def test_inline(self):
 for t in self._parse(INLINE):
 yield t
 queries = [("Any 1 WHERE EXISTS(T is CWGroup, T name 'managers')",
 '''SELECT 1
 WHERE EXISTS(SELECT 1 FROM cw_CWGroup AS _T WHERE _T.cw_name=managers)'''),
 ('Any X,Y WHERE NOT X created_by Y, X eid 5, Y eid 6',
 '''SELECT 5, 6
-WHERE NOT EXISTS(SELECT 1 FROM created_by_relation AS rel_created_by0 WHERE rel_created_by0.eid_from=5 AND rel_created_by0.eid_to=6)'''),
+WHERE NOT (EXISTS(SELECT 1 FROM created_by_relation AS rel_created_by0 WHERE rel_created_by0.eid_from=5 AND rel_created_by0.eid_to=6))'''),
 ]
 for t in self._parse(queries):
 yield t
 def test_ambigous_exists_no_from_clause(self):
 RQLGeneratorTC.setUp(self)
 dbhelper = get_db_helper('sqlite')
 self.o = SQLGenerator(schema, dbhelper)
 def _norm_sql(self, sql):
-return sql.strip().replace(' ILIKE ', ' LIKE ').replace('\nINTERSECT ALL\n', '\nINTERSECT\n')
+return sql.strip().replace(' ILIKE ', ' LIKE ')
 def test_date_extraction(self):
 self._check("Any MONTH(D) WHERE P is Personne, P creation_date D",
 '''SELECT MONTH(_P.cw_creation_date)
 FROM cw_Personne AS _P''')
 FROM (SELECT 1) AS _T
 WHERE EXISTS(SELECT 1 FROM cw_CWGroup AS _T WHERE _T.cw_name=managers)'''),
 ('Any X,Y WHERE NOT X created_by Y, X eid 5, Y eid 6',
 '''SELECT 5, 6
 FROM (SELECT 1) AS _T
-WHERE NOT EXISTS(SELECT 1 FROM created_by_relation AS rel_created_by0 WHERE rel_created_by0.eid_from=5 AND rel_created_by0.eid_to=6)'''),
+WHERE NOT (EXISTS(SELECT 1 FROM created_by_relation AS rel_created_by0 WHERE rel_created_by0.eid_from=5 AND rel_created_by0.eid_to=6))'''),
 ]
 for t in self._parse(queries):
 yield t

changeset 5590	a56eb02f9ce7
parent 5575	8a531340c3ef
parent 5582	3e133b29a1a4
child 5768	1e73a466aa69