"""

:organization: Logilab

:copyright: 2001-2010 LOGILAB S.A. (Paris, FRANCE), license is LGPL v2.

:contact: http://www.logilab.fr/ -- mailto:contact@logilab.fr

:license: GNU Lesser General Public License, v2.1 - http://www.gnu.org/licenses

"""

"""unit tests for module cubicweb.server.sources.rql2sql"""

import sys

from logilab.common.testlib import TestCase, unittest_main, mock_object

from rql import BadRQLQuery

#from cubicweb.server.sources.native import remove_unused_solutions

from cubicweb.server.sources.rql2sql import SQLGenerator, remove_unused_solutions

from rql.utils import register_function, FunctionDescr

# add a dumb registered procedure

class stockproc(FunctionDescr):

    supported_backends = ('postgres', 'sqlite', 'mysql')

try:

    register_function(stockproc)

except AssertionError, ex:

    pass # already registered

from cubicweb.devtools import TestServerConfiguration

from cubicweb.devtools.repotest import RQLGeneratorTC

config = TestServerConfiguration('data')

config.bootstrap_cubes()

schema = config.load_schema()

schema['in_state'].inlined = True

schema['state_of'].inlined = False

schema['comments'].inlined = False

def teardown_module(*args):

    global config, schema

    del config, schema

PARSER = [

    (r"Personne P WHERE P nom 'Zig\'oto';",

     '''SELECT _P.cw_eid

FROM cw_Personne AS _P

WHERE _P.cw_nom=Zig\'oto'''),

    (r'Personne P WHERE P nom ~= "Zig\"oto%";',

     '''SELECT _P.cw_eid

FROM cw_Personne AS _P

WHERE _P.cw_nom ILIKE Zig"oto%'''),

    ]

BASIC = [

    ("Any AS WHERE AS is Affaire",

     '''SELECT _AS.cw_eid

FROM cw_Affaire AS _AS'''),

    ("Any X WHERE X is Affaire",

     '''SELECT _X.cw_eid

FROM cw_Affaire AS _X'''),

    ("Any X WHERE X eid 0",

     '''SELECT 0'''),

    ("Personne P",

     '''SELECT _P.cw_eid

FROM cw_Personne AS _P'''),

    ("Personne P WHERE P test TRUE",

     '''SELECT _P.cw_eid

FROM cw_Personne AS _P

WHERE _P.cw_test=TRUE'''),

    ("Personne P WHERE P test false",

     '''SELECT _P.cw_eid

FROM cw_Personne AS _P

WHERE _P.cw_test=FALSE'''),

    ("Personne P WHERE P eid -1",

     '''SELECT -1'''),

    ("Personne P LIMIT 20 OFFSET 10",

     '''SELECT _P.cw_eid

FROM cw_Personne AS _P

LIMIT 20

OFFSET 10'''),

    ("Personne P WHERE S is Societe, P travaille S, S nom 'Logilab';",

     '''SELECT rel_travaille0.eid_from

FROM cw_Societe AS _S, travaille_relation AS rel_travaille0

WHERE rel_travaille0.eid_to=_S.cw_eid AND _S.cw_nom=Logilab'''),

    ("Personne P WHERE P concerne A, A concerne S, S nom 'Logilab', S is Societe;",

     '''SELECT rel_concerne0.eid_from

FROM concerne_relation AS rel_concerne0, concerne_relation AS rel_concerne1, cw_Societe AS _S

WHERE rel_concerne0.eid_to=rel_concerne1.eid_from AND rel_concerne1.eid_to=_S.cw_eid AND _S.cw_nom=Logilab'''),

    ("Note N WHERE X evaluee N, X nom 'Logilab';",

     '''SELECT rel_evaluee0.eid_to

FROM cw_Division AS _X, evaluee_relation AS rel_evaluee0

WHERE rel_evaluee0.eid_from=_X.cw_eid AND _X.cw_nom=Logilab

UNION ALL

SELECT rel_evaluee0.eid_to

FROM cw_Personne AS _X, evaluee_relation AS rel_evaluee0

WHERE rel_evaluee0.eid_from=_X.cw_eid AND _X.cw_nom=Logilab

UNION ALL

SELECT rel_evaluee0.eid_to

FROM cw_Societe AS _X, evaluee_relation AS rel_evaluee0

WHERE rel_evaluee0.eid_from=_X.cw_eid AND _X.cw_nom=Logilab

UNION ALL

SELECT rel_evaluee0.eid_to

FROM cw_SubDivision AS _X, evaluee_relation AS rel_evaluee0

WHERE rel_evaluee0.eid_from=_X.cw_eid AND _X.cw_nom=Logilab'''),

    ("Note N WHERE X evaluee N, X nom in ('Logilab', 'Caesium');",

     '''SELECT rel_evaluee0.eid_to

FROM cw_Division AS _X, evaluee_relation AS rel_evaluee0

WHERE rel_evaluee0.eid_from=_X.cw_eid AND _X.cw_nom IN(Logilab, Caesium)

UNION ALL

SELECT rel_evaluee0.eid_to

FROM cw_Personne AS _X, evaluee_relation AS rel_evaluee0

WHERE rel_evaluee0.eid_from=_X.cw_eid AND _X.cw_nom IN(Logilab, Caesium)

UNION ALL

SELECT rel_evaluee0.eid_to

FROM cw_Societe AS _X, evaluee_relation AS rel_evaluee0

WHERE rel_evaluee0.eid_from=_X.cw_eid AND _X.cw_nom IN(Logilab, Caesium)

UNION ALL

SELECT rel_evaluee0.eid_to

FROM cw_SubDivision AS _X, evaluee_relation AS rel_evaluee0

WHERE rel_evaluee0.eid_from=_X.cw_eid AND _X.cw_nom IN(Logilab, Caesium)'''),

    ("Any X WHERE X creation_date TODAY, X is Affaire",

     '''SELECT _X.cw_eid

FROM cw_Affaire AS _X

WHERE DATE(_X.cw_creation_date)=CURRENT_DATE'''),

    ("Any N WHERE G is CWGroup, G name N, E eid 12, E read_permission G",

     '''SELECT _G.cw_name

FROM cw_CWGroup AS _G, read_permission_relation AS rel_read_permission0

WHERE rel_read_permission0.eid_from=12 AND rel_read_permission0.eid_to=_G.cw_eid'''),

    ('Any Y WHERE U login "admin", U login Y', # stupid but valid...

     """SELECT _U.cw_login

FROM cw_CWUser AS _U

WHERE _U.cw_login=admin"""),

    ('Any T WHERE T tags X, X is State',

     '''SELECT rel_tags0.eid_from

FROM cw_State AS _X, tags_relation AS rel_tags0

WHERE rel_tags0.eid_to=_X.cw_eid'''),

    ('Any X,Y WHERE X eid 0, Y eid 1, X concerne Y',

     '''SELECT 0, 1

FROM concerne_relation AS rel_concerne0

WHERE rel_concerne0.eid_from=0 AND rel_concerne0.eid_to=1'''),

    ("Any X WHERE X prenom 'lulu',"

     "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)))'''),

    ("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)))'''),

]

ADVANCED= [

    ("Societe S WHERE S nom 'Logilab' OR S nom 'Caesium'",

     '''SELECT _S.cw_eid

FROM cw_Societe AS _S

WHERE ((_S.cw_nom=Logilab) OR (_S.cw_nom=Caesium))'''),

    ('Any X WHERE X nom "toto", X eid IN (9700, 9710, 1045, 674)',

    '''SELECT _X.cw_eid

FROM cw_Division AS _X

WHERE _X.cw_nom=toto AND _X.cw_eid IN(9700, 9710, 1045, 674)

UNION ALL

SELECT _X.cw_eid

FROM cw_Personne AS _X

WHERE _X.cw_nom=toto AND _X.cw_eid IN(9700, 9710, 1045, 674)

UNION ALL

SELECT _X.cw_eid

FROM cw_Societe AS _X

WHERE _X.cw_nom=toto AND _X.cw_eid IN(9700, 9710, 1045, 674)

UNION ALL

SELECT _X.cw_eid

FROM cw_SubDivision AS _X

WHERE _X.cw_nom=toto AND _X.cw_eid IN(9700, 9710, 1045, 674)'''),

    ('Any Y, COUNT(N) GROUPBY Y WHERE Y evaluee N;',

     '''SELECT rel_evaluee0.eid_from, COUNT(rel_evaluee0.eid_to)

FROM evaluee_relation AS rel_evaluee0

GROUP BY rel_evaluee0.eid_from'''),

    ("Any X WHERE X concerne B or C concerne X",

     '''SELECT _X.cw_eid

FROM concerne_relation AS rel_concerne0, concerne_relation AS rel_concerne1, cw_Affaire AS _X

WHERE ((rel_concerne0.eid_from=_X.cw_eid) OR (rel_concerne1.eid_to=_X.cw_eid))'''),

    ("Any X WHERE X travaille S or X concerne A",

     '''SELECT _X.cw_eid

FROM concerne_relation AS rel_concerne1, cw_Personne AS _X, travaille_relation AS rel_travaille0

WHERE ((rel_travaille0.eid_from=_X.cw_eid) OR (rel_concerne1.eid_from=_X.cw_eid))'''),

    ("Any N WHERE A evaluee N or N ecrit_par P",

     '''SELECT _N.cw_eid

FROM cw_Note AS _N, evaluee_relation AS rel_evaluee0

WHERE ((rel_evaluee0.eid_to=_N.cw_eid) OR (_N.cw_ecrit_par IS NOT NULL))'''),

    ("Any N WHERE A evaluee N or EXISTS(N todo_by U)",

     '''SELECT _N.cw_eid

FROM cw_Note AS _N, evaluee_relation AS rel_evaluee0

WHERE ((rel_evaluee0.eid_to=_N.cw_eid) OR (EXISTS(SELECT 1 FROM todo_by_relation AS rel_todo_by1 WHERE rel_todo_by1.eid_from=_N.cw_eid)))'''),

    ("Any N WHERE A evaluee N or N todo_by U",

     '''SELECT _N.cw_eid

FROM cw_Note AS _N, evaluee_relation AS rel_evaluee0, todo_by_relation AS rel_todo_by1

WHERE ((rel_evaluee0.eid_to=_N.cw_eid) OR (rel_todo_by1.eid_from=_N.cw_eid))'''),

    ("Any X WHERE X concerne B or C concerne X, B eid 12, C eid 13",

     '''SELECT _X.cw_eid

FROM concerne_relation AS rel_concerne0, concerne_relation AS rel_concerne1, cw_Affaire AS _X

WHERE ((rel_concerne0.eid_from=_X.cw_eid AND rel_concerne0.eid_to=12) OR (rel_concerne1.eid_from=13 AND rel_concerne1.eid_to=_X.cw_eid))'''),

    ('Any X WHERE X created_by U, X concerne B OR C concerne X, B eid 12, C eid 13',

     '''SELECT rel_created_by0.eid_from

FROM concerne_relation AS rel_concerne1, concerne_relation AS rel_concerne2, created_by_relation AS rel_created_by0

WHERE ((rel_concerne1.eid_from=rel_created_by0.eid_from AND rel_concerne1.eid_to=12) OR (rel_concerne2.eid_from=13 AND rel_concerne2.eid_to=rel_created_by0.eid_from))'''),

    ('Any P WHERE P travaille_subdivision S1 OR P travaille_subdivision S2, S1 nom "logilab", S2 nom "caesium"',

     '''SELECT _P.cw_eid

FROM cw_Personne AS _P, cw_SubDivision AS _S1, cw_SubDivision AS _S2, travaille_subdivision_relation AS rel_travaille_subdivision0, travaille_subdivision_relation AS rel_travaille_subdivision1

WHERE ((rel_travaille_subdivision0.eid_from=_P.cw_eid AND rel_travaille_subdivision0.eid_to=_S1.cw_eid) OR (rel_travaille_subdivision1.eid_from=_P.cw_eid AND rel_travaille_subdivision1.eid_to=_S2.cw_eid)) AND _S1.cw_nom=logilab AND _S2.cw_nom=caesium'''),

    ('Any X WHERE T tags X',

     '''SELECT rel_tags0.eid_to

FROM tags_relation AS rel_tags0'''),

    ('Any X WHERE X in_basket B, B eid 12',

     '''SELECT rel_in_basket0.eid_from

FROM in_basket_relation AS rel_in_basket0

WHERE rel_in_basket0.eid_to=12'''),

    ('Any SEN,RN,OEN WHERE X from_entity SE, SE eid 44, X relation_type R, R eid 139, X to_entity OE, OE eid 42, R name RN, SE name SEN, OE name OEN',

     '''SELECT _SE.cw_name, _R.cw_name, _OE.cw_name

FROM cw_CWAttribute AS _X, cw_CWEType AS _OE, cw_CWEType AS _SE, cw_CWRType AS _R

WHERE _X.cw_from_entity=44 AND _SE.cw_eid=44 AND _X.cw_relation_type=139 AND _R.cw_eid=139 AND _X.cw_to_entity=42 AND _OE.cw_eid=42

UNION ALL

SELECT _SE.cw_name, _R.cw_name, _OE.cw_name

FROM cw_CWEType AS _OE, cw_CWEType AS _SE, cw_CWRType AS _R, cw_CWRelation AS _X

WHERE _X.cw_from_entity=44 AND _SE.cw_eid=44 AND _X.cw_relation_type=139 AND _R.cw_eid=139 AND _X.cw_to_entity=42 AND _OE.cw_eid=42'''),

    # 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'''),

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

ORDER BY 2) AS T1'''),

    ('Any N WHERE N todo_by U, N is Note, U eid 2, N filed_under T, T eid 3',

     # N would actually be invarient if U eid 2 had given a specific type to U

     '''SELECT _N.cw_eid

FROM cw_Note AS _N, filed_under_relation AS rel_filed_under1, todo_by_relation AS rel_todo_by0

WHERE rel_todo_by0.eid_from=_N.cw_eid AND rel_todo_by0.eid_to=2 AND rel_filed_under1.eid_from=_N.cw_eid AND rel_filed_under1.eid_to=3'''),

    ('Any N WHERE N todo_by U, U eid 2, P evaluee N, P eid 3',

     '''SELECT rel_evaluee1.eid_to

FROM evaluee_relation AS rel_evaluee1, todo_by_relation AS rel_todo_by0

WHERE rel_evaluee1.eid_to=rel_todo_by0.eid_from AND rel_todo_by0.eid_to=2 AND rel_evaluee1.eid_from=3'''),

    (' 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)'''),

    ('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))))'''),

    ('Any C WHERE C is Card, EXISTS(X documented_by C)',

      """SELECT _C.cw_eid

FROM cw_Card AS _C

WHERE EXISTS(SELECT 1 FROM documented_by_relation AS rel_documented_by0 WHERE rel_documented_by0.eid_to=_C.cw_eid)"""),

    ('Any C WHERE C is Card, EXISTS(X documented_by C, X eid 12)',

      """SELECT _C.cw_eid

FROM cw_Card AS _C

WHERE EXISTS(SELECT 1 FROM documented_by_relation AS rel_documented_by0 WHERE rel_documented_by0.eid_from=12 AND rel_documented_by0.eid_to=_C.cw_eid)"""),

    ('Any T WHERE C is Card, C title T, EXISTS(X documented_by C, X eid 12)',

      """SELECT _C.cw_title

FROM cw_Card AS _C

WHERE EXISTS(SELECT 1 FROM documented_by_relation AS rel_documented_by0 WHERE rel_documented_by0.eid_from=12 AND rel_documented_by0.eid_to=_C.cw_eid)"""),

    ('Any GN,L WHERE X in_group G, X login L, G name GN, EXISTS(X copain T, T login L, T login IN("comme", "cochon"))',

     '''SELECT _G.cw_name, _X.cw_login

FROM cw_CWGroup AS _G, cw_CWUser AS _X, in_group_relation AS rel_in_group0

WHERE rel_in_group0.eid_from=_X.cw_eid AND rel_in_group0.eid_to=_G.cw_eid AND EXISTS(SELECT 1 FROM copain_relation AS rel_copain1, cw_CWUser AS _T WHERE rel_copain1.eid_from=_X.cw_eid AND rel_copain1.eid_to=_T.cw_eid AND _T.cw_login=_X.cw_login AND _T.cw_login IN(comme, cochon))'''),

    ('Any X,S, MAX(T) GROUPBY X,S ORDERBY S WHERE X is CWUser, T tags X, S eid IN(32), X in_state S',

     '''SELECT _X.cw_eid, 32, MAX(rel_tags0.eid_from)

FROM cw_CWUser AS _X, tags_relation AS rel_tags0

WHERE rel_tags0.eid_to=_X.cw_eid AND _X.cw_in_state=32

GROUP BY _X.cw_eid'''),

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

GROUP BY _C.cw_nom

ORDER BY 1 DESC

LIMIT 10'''),

    ('Any X WHERE Y evaluee X, Y is CWUser',

     '''SELECT rel_evaluee0.eid_to

FROM cw_CWUser AS _Y, evaluee_relation AS rel_evaluee0

WHERE rel_evaluee0.eid_from=_Y.cw_eid'''),

    ('Any L WHERE X login "admin", 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 _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'''),

    ('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'''),

    ('Any XN ORDERBY XN WHERE X name XN, X is IN (Basket,Folder,Tag)',

     '''SELECT _X.cw_name

FROM cw_Basket AS _X

UNION ALL

SELECT _X.cw_name

FROM cw_Folder AS _X

UNION ALL

SELECT _X.cw_name

FROM cw_Tag AS _X

ORDER BY 1'''),

    # DISTINCT, can use relation under exists scope as principal

    ('DISTINCT Any X,Y WHERE X name "CWGroup", Y eid IN(1, 2, 3), EXISTS(X read_permission Y)',

     '''SELECT DISTINCT _X.cw_eid, rel_read_permission0.eid_to

FROM cw_CWEType AS _X, read_permission_relation AS rel_read_permission0

WHERE _X.cw_name=CWGroup AND rel_read_permission0.eid_to IN(1, 2, 3) AND EXISTS(SELECT 1 WHERE rel_read_permission0.eid_from=_X.cw_eid)'''),

    # no distinct, Y can't be invariant

    ('Any X,Y WHERE X name "CWGroup", Y eid IN(1, 2, 3), EXISTS(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 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 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)'''),

    # 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)

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)'''),

    # 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)

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)'''),

    # 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)

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)'''),

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

SELECT _X.cw_eid AS C0, _X.cw_name AS C1

FROM cw_Folder AS _X

UNION ALL

SELECT _X.cw_eid AS C0, _X.cw_name AS C1

FROM cw_Tag AS _X) AS T1

GROUP BY T1.C1'''),

    ('Any MAX(X)+MIN(LENGTH(D)), N GROUPBY N ORDERBY 1, N, DF WHERE X data_name N, X data D, X data_format DF;',

     '''SELECT (MAX(T1.C1) + MIN(LENGTH(T1.C0))), T1.C2 FROM (SELECT _X.cw_data AS C0, _X.cw_eid AS C1, _X.cw_data_name AS C2, _X.cw_data_format AS C3

FROM cw_File AS _X

UNION ALL

SELECT _X.cw_data AS C0, _X.cw_eid AS C1, _X.cw_data_name AS C2, _X.cw_data_format AS C3

FROM cw_Image AS _X) AS T1

GROUP BY T1.C2,T1.C3

ORDER BY 1,2,T1.C3'''),

    ('DISTINCT Any S ORDERBY R WHERE A is Affaire, A sujet S, A ref R',

     '''SELECT T1.C0 FROM (SELECT DISTINCT _A.cw_sujet AS C0, _A.cw_ref AS C1

FROM cw_Affaire AS _A

ORDER BY 2) AS T1'''),

    ('DISTINCT Any MAX(X)+MIN(LENGTH(D)), N GROUPBY N ORDERBY 2, DF WHERE X data_name N, X data D, X data_format DF;',

     '''SELECT T1.C0,T1.C1 FROM (SELECT DISTINCT (MAX(T1.C1) + MIN(LENGTH(T1.C0))) AS C0, T1.C2 AS C1, T1.C3 AS C2 FROM (SELECT DISTINCT _X.cw_data AS C0, _X.cw_eid AS C1, _X.cw_data_name AS C2, _X.cw_data_format AS C3

FROM cw_File AS _X

UNION

SELECT DISTINCT _X.cw_data AS C0, _X.cw_eid AS C1, _X.cw_data_name AS C2, _X.cw_data_format AS C3

FROM cw_Image AS _X) AS T1

GROUP BY T1.C2,T1.C3

ORDER BY 2,3) AS T1

'''),

    # ambiguity in EXISTS() -> should union the sub-query

    ('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)'''),

    # 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)',

     '''SELECT _U.cw_eid