--- a/server/test/unittest_rql2sql.py Wed Jun 29 14:05:14 2011 +0200
+++ b/server/test/unittest_rql2sql.py Wed Jun 29 18:27:23 2011 +0200
@@ -1529,14 +1529,14 @@
ORDER BY ts_rank(appears0.words, to_tsquery('default', 'hip&hop&momo'))*appears0.weight"""),
('Any X ORDERBY FTIRANK(X) WHERE X has_text "toto tata", X name "tutu", X is IN (Basket,Folder)',
- """SELECT T1.C0 FROM (SELECT _X.cw_eid AS C0, ts_rank(appears0.words, to_tsquery('default', 'toto&tata'))*appears0.weight AS C1
+ """SELECT _X.cw_eid
FROM appears AS appears0, cw_Basket AS _X
WHERE appears0.words @@ to_tsquery('default', 'toto&tata') AND appears0.uid=_X.cw_eid AND _X.cw_name=tutu
UNION ALL
-SELECT _X.cw_eid AS C0, ts_rank(appears0.words, to_tsquery('default', 'toto&tata'))*appears0.weight AS C1
+SELECT _X.cw_eid
FROM appears AS appears0, cw_Folder AS _X
WHERE appears0.words @@ to_tsquery('default', 'toto&tata') AND appears0.uid=_X.cw_eid AND _X.cw_name=tutu
-ORDER BY 2) AS T1"""),
+ORDER BY ts_rank(appears0.words, to_tsquery('default', 'toto&tata'))*appears0.weight"""),
('Personne X ORDERBY FTIRANK(X),FTIRANK(S) WHERE X has_text %(text)s, X travaille S, S has_text %(text)s',
"""SELECT _X.eid
@@ -1643,6 +1643,16 @@
WHERE rel_owned_by0.eid_from=_G.cw_eid AND rel_owned_by0.eid_to=1122
GROUP BY _G.cw_eid''')
+ def test_groupby_orderby_insertion_dont_modify_intention(self):
+ self._check('Any YEAR(XECT)*100+MONTH(XECT), COUNT(X),SUM(XCE),AVG(XSCT-XECT) '
+ 'GROUPBY YEAR(XECT),MONTH(XECT) ORDERBY 1 '
+ 'WHERE X creation_date XSCT, X modification_date XECT, '
+ 'X ordernum XCE, X is CWAttribute',
+ '''SELECT ((CAST(EXTRACT(YEAR from _X.cw_modification_date) AS INTEGER) * 100) + CAST(EXTRACT(MONTH from _X.cw_modification_date) AS INTEGER)), COUNT(_X.cw_eid), SUM(_X.cw_ordernum), AVG((_X.cw_creation_date - _X.cw_modification_date))
+FROM cw_CWAttribute AS _X
+GROUP BY CAST(EXTRACT(YEAR from _X.cw_modification_date) AS INTEGER),CAST(EXTRACT(MONTH from _X.cw_modification_date) AS INTEGER)
+ORDER BY 1'''),
+
class SqlServer2005SQLGeneratorTC(PostgresSQLGeneratorTC):
backend = 'sqlserver2005'
@@ -1749,6 +1759,15 @@
for t in self._parse(WITH_LIMIT):# + ADVANCED_WITH_LIMIT_OR_ORDERBY):
yield t
+ def test_groupby_orderby_insertion_dont_modify_intention(self):
+ self._check('Any YEAR(XECT)*100+MONTH(XECT), COUNT(X),SUM(XCE),AVG(XSCT-XECT) '
+ 'GROUPBY YEAR(XECT),MONTH(XECT) ORDERBY 1 '
+ 'WHERE X creation_date XSCT, X modification_date XECT, '
+ 'X ordernum XCE, X is CWAttribute',
+ '''SELECT ((YEAR(_X.cw_modification_date) * 100) + MONTH(_X.cw_modification_date)), COUNT(_X.cw_eid), SUM(_X.cw_ordernum), AVG((_X.cw_creation_date - _X.cw_modification_date))
+FROM cw_CWAttribute AS _X
+GROUP BY YEAR(_X.cw_modification_date),MONTH(_X.cw_modification_date)
+ORDER BY 1'''),
class SqliteSQLGeneratorTC(PostgresSQLGeneratorTC):
@@ -1880,6 +1899,16 @@
FROM cw_CWUser AS _X
WHERE ((YEAR(_X.cw_creation_date)=2010) OR (_X.cw_creation_date IS NULL))''')
+ def test_groupby_orderby_insertion_dont_modify_intention(self):
+ self._check('Any YEAR(XECT)*100+MONTH(XECT), COUNT(X),SUM(XCE),AVG(XSCT-XECT) '
+ 'GROUPBY YEAR(XECT),MONTH(XECT) ORDERBY 1 '
+ 'WHERE X creation_date XSCT, X modification_date XECT, '
+ 'X ordernum XCE, X is CWAttribute',
+ '''SELECT ((YEAR(_X.cw_modification_date) * 100) + MONTH(_X.cw_modification_date)), COUNT(_X.cw_eid), SUM(_X.cw_ordernum), AVG((_X.cw_creation_date - _X.cw_modification_date))
+FROM cw_CWAttribute AS _X
+GROUP BY YEAR(_X.cw_modification_date),MONTH(_X.cw_modification_date)
+ORDER BY 1'''),
+
class MySQLGenerator(PostgresSQLGeneratorTC):
@@ -1976,6 +2005,16 @@
FROM (SELECT 1) AS _T
WHERE NOT (EXISTS(SELECT 1 FROM in_group_relation AS rel_in_group0))''')
+ def test_groupby_orderby_insertion_dont_modify_intention(self):
+ self._check('Any YEAR(XECT)*100+MONTH(XECT), COUNT(X),SUM(XCE),AVG(XSCT-XECT) '
+ 'GROUPBY YEAR(XECT),MONTH(XECT) ORDERBY 1 '
+ 'WHERE X creation_date XSCT, X modification_date XECT, '
+ 'X ordernum XCE, X is CWAttribute',
+ '''SELECT ((EXTRACT(YEAR from _X.cw_modification_date) * 100) + EXTRACT(MONTH from _X.cw_modification_date)), COUNT(_X.cw_eid), SUM(_X.cw_ordernum), AVG((_X.cw_creation_date - _X.cw_modification_date))
+FROM cw_CWAttribute AS _X
+GROUP BY EXTRACT(YEAR from _X.cw_modification_date),EXTRACT(MONTH from _X.cw_modification_date)
+ORDER BY 1'''),
+
class removeUnsusedSolutionsTC(TestCase):
def test_invariant_not_varying(self):