# copyright 2003-2012 LOGILAB S.A. (Paris, FRANCE), all rights reserved.

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

#

# This file is part of CubicWeb.

#

# CubicWeb is free software: you can redistribute it and/or modify it under the

# terms of the GNU Lesser General Public License as published by the Free

# Software Foundation, either version 2.1 of the License, or (at your option)

# any later version.

#

# CubicWeb is distributed in the hope that it will be useful, but WITHOUT

# ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS

# FOR A PARTICULAR PURPOSE.  See the GNU Lesser General Public License for more

# details.

#

# You should have received a copy of the GNU Lesser General Public License along

# with CubicWeb.  If not, see <http://www.gnu.org/licenses/>.

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

import sys

import os

from datetime import date

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

from rql import BadRQLQuery

from rql.utils import register_function, FunctionDescr

from cubicweb.devtools import TestServerConfiguration

from cubicweb.devtools.repotest import RQLGeneratorTC

from cubicweb.server.sources.rql2sql import remove_unused_solutions

# add a dumb registered procedure

class stockproc(FunctionDescr):

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

try:

    register_function(stockproc)

except AssertionError as ex:

    pass # already registered

from logilab import database as db

def monkey_patch_import_driver_module(driver, drivers, quiet=True):

    if not driver in drivers:

        raise db.UnknownDriver(driver)

    for modname in drivers[driver]:

        try:

            if not quiet:

                sys.stderr.write('Trying %s\n' % modname)

            module = db.load_module_from_name(modname, use_sys=False)

            break

        except ImportError:

            if not quiet:

                sys.stderr.write('%s is not available\n' % modname)

            continue

    else:

        return mock_object(STRING=1, BOOLEAN=2, BINARY=3, DATETIME=4, NUMBER=5), drivers[driver][0]

    return module, modname

def setUpModule():

    global config, schema

    config = TestServerConfiguration('data', apphome=CWRQLTC.datadir)

    config.bootstrap_cubes()

    schema = config.load_schema()

    schema['in_state'].inlined = True

    schema['state_of'].inlined = False

    schema['comments'].inlined = False

    db._backup_import_driver_module = db._import_driver_module

    db._import_driver_module = monkey_patch_import_driver_module

def tearDownModule():

    global config, schema

    del config, schema

    db._import_driver_module = db._backup_import_driver_module

    del db._backup_import_driver_module

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 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 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 cw_CWGroup AS _G, in_group_relation AS rel_in_group1, owned_by_relation AS rel_owned_by0 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 cw_CWGroup AS _G, in_group_relation AS rel_in_group1, owned_by_relation AS rel_owned_by0 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 title V, NOT X wikiid V, NOT X title "parent", X is Card',

     '''SELECT _X.cw_eid

FROM cw_Card AS _X

WHERE NOT (_X.cw_wikiid=_X.cw_title) AND NOT (_X.cw_title=parent)'''),

    ("Any -AS WHERE AS is Affaire",

     '''SELECT -_AS.cw_eid

FROM cw_Affaire AS _AS'''),

]

BASIC_WITH_LIMIT = [

    ("Personne P LIMIT 20 OFFSET 10",

     '''SELECT _P.cw_eid

FROM cw_Personne AS _P

LIMIT 20

OFFSET 10'''),

    ("Any P ORDERBY N LIMIT 1 WHERE P is Personne, P travaille S, S eid %(eid)s, P nom N, P nom %(text)s",

     '''SELECT _P.cw_eid

FROM cw_Personne AS _P, travaille_relation AS rel_travaille0

WHERE rel_travaille0.eid_from=_P.cw_eid AND rel_travaille0.eid_to=12345 AND _P.cw_nom=hip hop momo

ORDER BY _P.cw_nom

LIMIT 1'''),

    ]

ADVANCED = [

    ("Societe S WHERE S2 is Societe, S2 nom SN, S nom 'Logilab' OR S nom SN",

     '''SELECT _S.cw_eid

FROM cw_Societe AS _S, cw_Societe AS _S2

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

    ("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 (_S.cw_ecrit_par IS NULL OR _S.cw_ecrit_par!=_O.cw_eid) AND _S.cw_eid=1 AND _S.cw_inline1 IS NOT NULL AND _O.cw_inline2=_S.cw_inline1'''),

    ('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 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(_X.cw_eid) + MIN(LENGTH(_X.cw_data))), _X.cw_data_name

FROM cw_File AS _X

GROUP BY _X.cw_data_name,_X.cw_data_format

ORDER BY 1,2,_X.cw_data_format'''),

    # 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 cw_CWGroup AS _X, tags_relation AS rel_tags0 WHERE rel_tags0.eid_from=_T.cw_eid AND rel_tags0.eid_to=_X.cw_eid UNION SELECT 1 FROM cw_CWUser AS _X, tags_relation AS rel_tags1 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

FROM cw_CWUser AS _U

WHERE _U.cw_eid IN(1, 2) AND EXISTS(SELECT 1 FROM owned_by_relation AS rel_owned_by0 WHERE rel_owned_by0.eid_to=_U.cw_eid)'''),

    ('Any U WHERE EXISTS(U eid IN (1,2), X owned_by U)',

     '''SELECT _U.cw_eid

FROM cw_CWUser AS _U

WHERE EXISTS(SELECT 1 FROM owned_by_relation AS rel_owned_by0 WHERE _U.cw_eid IN(1, 2) AND rel_owned_by0.eid_to=_U.cw_eid)'''),

    ('Any COUNT(U) WHERE EXISTS (P owned_by U, P is IN (Note, Affaire))',

     '''SELECT COUNT(_U.cw_eid)

FROM cw_CWUser AS _U

WHERE EXISTS(SELECT 1 FROM cw_Affaire AS _P, owned_by_relation AS rel_owned_by0 WHERE rel_owned_by0.eid_from=_P.cw_eid AND rel_owned_by0.eid_to=_U.cw_eid UNION SELECT 1 FROM cw_Note AS _P, owned_by_relation AS rel_owned_by1 WHERE rel_owned_by1.eid_from=_P.cw_eid AND rel_owned_by1.eid_to=_U.cw_eid)'''),

    ('Any MAX(X)',

     '''SELECT MAX(_X.eid)

FROM entities AS _X'''),

    ('Any MAX(X) WHERE X is Note',

     '''SELECT MAX(_X.cw_eid)

FROM cw_Note AS _X'''),

    ('Any X WHERE X eid > 12',

     '''SELECT _X.eid