"""

:organization: Logilab

:copyright: 2001-2009 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

from rql import BadRQLQuery

from indexer import get_indexer

#from cubicweb.server.sources.native import remove_unused_solutions

from cubicweb.server.sources.rql2sql import SQLGenerator

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['comments'].inlined = False

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

UNION

SELECT DISTINCT X.cw_eid, rel_read_permission0.eid_to

FROM cw_CWRType 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)

UNION ALL

SELECT X.cw_eid, Y.cw_eid

FROM cw_CWGroup AS Y, cw_CWRType AS X

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

UNION

SELECT DISTINCT X.cw_eid, Y.cw_eid

FROM cw_CWGroup AS Y, cw_CWRType AS X

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

UNION

SELECT DISTINCT X.cw_eid, Y.cw_eid

FROM cw_CWGroup AS Y, cw_CWRType AS X

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

UNION ALL

SELECT X.cw_eid, Y.cw_eid

FROM cw_CWGroup AS Y, cw_CWRType AS X

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

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