1import copy
2from .tuq_sanity import QuerySanityTests
3import time
4from deepdiff import DeepDiff
5
6JOIN_INNER = "INNER"
7JOIN_LEFT = "LEFT"
8JOIN_RIGHT = "RIGHT"
9
10
11class JoinTests(QuerySanityTests):
12    def setUp(self):
13        try:
14            self.dataset = 'join'
15            super(JoinTests, self).setUp()
16            self.gens_tasks = self.gen_docs(type='tasks')
17            self.type_join = self.input.param("type_join", JOIN_INNER)
18            self.query_buckets = self.get_query_buckets(check_all_buckets=True)
19        except Exception as ex:
20            self.log.error("ERROR SETUP FAILED: %s" % str(ex))
21            raise ex
22
23    def suite_setUp(self):
24        super(JoinTests, self).suite_setUp()
25        self.load(self.gens_tasks, start_items=self.num_items)
26
27    def tearDown(self):
28        super(JoinTests, self).tearDown()
29
30    def suite_tearDown(self):
31        super(JoinTests, self).suite_tearDown()
32
33    def test_simple_join_keys(self):
34        for query_bucket in self.query_buckets:
35            self.query = "SELECT employee.name, employee.tasks_ids, new_project.project " + \
36                         "FROM %s as employee %s JOIN %s as new_project " % (query_bucket, self.type_join, self.query_buckets[0]) + \
37                         "ON KEYS employee.tasks_ids"
38            time.sleep(30)
39            actual_result = self.run_cbq_query()
40            actual_result = actual_result['results']
41            full_list = self._generate_full_joined_docs_list(join_type=self.type_join)
42            expected_result = [doc for doc in full_list if not doc]
43            expected_result.extend([{"name": doc['name'], "tasks_ids": doc['tasks_ids'], "project": doc['project']}
44                                    for doc in full_list if doc and 'project' in doc])
45            # expected_result.extend([{"name" : doc['name'], "tasks_ids" : doc['tasks_ids']}
46            # for doc in full_list if doc and not 'project' in doc])
47            self._verify_results(actual_result, expected_result)
48
49    def test_prepared_simple_join_keys(self):
50        for query_bucket in self.query_buckets:
51            self.query = "SELECT employee.name, employee.tasks_ids, new_project.project " + \
52                         "FROM %s as employee %s JOIN %s as new_project " % (query_bucket, self.type_join, self.query_buckets[0]) + \
53                         "ON KEYS employee.tasks_ids"
54            self.prepared_common_body()
55
56    def test_join_several_keys(self):
57        for query_bucket in self.query_buckets:
58            self.query = "SELECT employee.name, employee.tasks_ids, new_task.project, new_task.task_name " + \
59                         "FROM %s as employee %s JOIN %s as new_task " % (query_bucket, self.type_join, self.query_buckets[0]) + \
60                         "ON KEYS employee.tasks_ids"
61            actual_result = self.run_cbq_query()
62            actual_result = actual_result['results']
63            full_list = self._generate_full_joined_docs_list(join_type=self.type_join)
64            expected_result = [doc for doc in full_list if not doc]
65            expected_result.extend([{"name": doc['name'], "tasks_ids": doc['tasks_ids'], "project": doc['project'],
66                                     "task_name": doc['task_name']}
67                                    for doc in full_list if doc and 'project' in doc])
68            # expected_result.extend([{"name" : doc['name'], "tasks_ids" : doc['tasks_ids']}
69            #                        for doc in full_list if doc and not 'project' in doc])
70            self._verify_results(actual_result, expected_result)
71
72    def test_where_join_keys(self):
73        for query_bucket in self.query_buckets:
74            self.query = "SELECT employee.name, employee.tasks_ids, new_project_full.project new_project " + \
75                         "FROM %s as employee %s JOIN %s as new_project_full " % (query_bucket, self.type_join, self.query_buckets[0]) + \
76                         "ON KEYS employee.tasks_ids WHERE new_project_full.project == 'IT'"
77            actual_result = self.run_cbq_query()
78            actual_result = actual_result['results']
79            expected_result = self._generate_full_joined_docs_list(join_type=self.type_join)
80            expected_result = [{"name": doc['name'], "tasks_ids": doc['tasks_ids'], "new_project": doc['project']}
81                               for doc in expected_result if doc and 'project' in doc and doc['project'] == 'IT']
82            self._verify_results(actual_result, expected_result)
83
84    def test_bidirectional_join(self):
85        default_bucket =  self.query_buckets[0]
86        self.query = "create index idxbidirec on %s(join_day)" % default_bucket
87        actual_result = self.run_cbq_query()
88        self.assertEqual(actual_result['status'], 'success', 'Query was not run successfully')
89        self.query = "explain SELECT employee.name, employee.join_day " + \
90                     "FROM %s as employee %s JOIN %s as new_project " % (
91                         default_bucket, self.type_join, default_bucket) + \
92                     "ON KEY new_project.join_day FOR employee where new_project.join_day is not null"
93        actual_result = self.run_cbq_query()
94        self.assertTrue("covers" in str(actual_result))
95        self.assertEqual(actual_result['status'], 'success', 'Query was not run successfully')
96        self.test_explain_particular_index("idxbidirec")
97        self.query = "SELECT employee.name, employee.join_day " + \
98                     "FROM %s as employee %s JOIN %s as new_project " % (
99                         default_bucket, self.type_join, default_bucket) + \
100                     "ON KEY new_project.join_day FOR employee where new_project.join_day is not null"
101        actual_result = self.run_cbq_query()
102        # self.assertTrue(actual_result['metrics']['resultCount'] == 0, 'Query was not run successfully')
103        self.query = "drop index idxbidirec ON %s" % default_bucket
104        self.run_cbq_query()
105
106        self.query = "CREATE INDEX ix1 ON %s(docid,name)" % default_bucket
107        self.run_cbq_query()
108        self.query = 'CREATE INDEX ix2 ON %s(docid,name) where type = "wdoc"' % default_bucket
109        self.run_cbq_query()
110        self.query = "CREATE INDEX ix3 ON %s(altid, name, DISTINCT ARRAY p FOR p IN phones END)" % default_bucket
111        self.run_cbq_query()
112        self.query = 'INSERT into %s (key , value) VALUES ("%s", %s)' % (default_bucket, "w001",
113                                                                         {"type": "wdoc", "docid": "x001",
114                                                                          "name": "wdoc",
115                                                                          "phones": ["123-456-7890", "123-456-7891"],
116                                                                          "altid": "x001"})
117        self.run_cbq_query()
118        self.query = 'INSERT into %s (key , value) VALUES ("%s", %s)' % (default_bucket, "pdoc1",
119                                                                         {"type": "pdoc", "docid": "x001",
120                                                                          "name": "pdoc",
121                                                                          "phones": ["123-456-7890", "123-456-7891"],
122                                                                          "altid": "x001"})
123        self.run_cbq_query()
124        self.query = 'INSERT into %s (key , value) VALUES ("%s", %s)' % (default_bucket, "pdoc2",
125                                                                         {"type": "pdoc", "docid": "w001",
126                                                                          "name": "pdoc",
127                                                                          "phones": ["123-456-7890", "123-456-7891"],
128                                                                          "altid": "w001"})
129        self.run_cbq_query()
130        self.query = 'explain SELECT meta(b1).id b1id FROM %s b1 JOIN %s b2 ON KEY b2.docid FOR b1 WHERE meta(b1).id > ""' % (default_bucket,  default_bucket)
131        actual_result = self.run_cbq_query()
132        self.assertTrue("covers" in str(actual_result))
133        self.assertTrue("ix1" in str(actual_result))
134
135        self.query = 'SELECT meta(b1).id b1id FROM ' + default_bucket + ' b1 JOIN ' + default_bucket + ' b2 ON KEY b2.docid FOR b1 WHERE meta(b1).id > ""'
136        actual_result = self.run_cbq_query()
137
138        self.assertTrue(actual_result['results'] == [{'b1id': 'w001'}])
139        self.query = 'explain SELECT meta(b1).id b1id, meta(b2).id b2id FROM ' + default_bucket + ' b1 JOIN ' + default_bucket + ' b2 ON KEY b2.docid FOR b1 WHERE meta(b1).id > ""'
140        actual_result = self.run_cbq_query()
141        self.assertTrue("covers" in str(actual_result))
142        self.assertTrue("ix1" in str(actual_result))
143        self.query = 'SELECT meta(b1).id b1id, meta(b2).id b2id FROM ' + default_bucket + ' b1 JOIN ' + default_bucket + ' b2 ON KEY b2.docid FOR b1 WHERE meta(b1).id > ""'
144        actual_result = self.run_cbq_query()
145        self.assertTrue(actual_result['results'] == [{'b1id': 'w001', 'b2id': 'pdoc2'}])
146        self.query = 'explain SELECT meta(b1).id b1id, b2.docid FROM ' + default_bucket + ' b1 JOIN ' + default_bucket + ' b2 ON KEY b2.docid FOR b1 WHERE meta(b1).id > ""'
147        actual_result = self.run_cbq_query()
148        self.assertTrue("covers" in str(actual_result))
149        self.assertTrue("ix1" in str(actual_result))
150        self.query = 'SELECT meta(b1).id b1id, b2.docid FROM ' + default_bucket + ' b1 JOIN ' + default_bucket + ' b2 ON KEY b2.docid FOR b1 WHERE meta(b1).id > ""'
151        actual_result = self.run_cbq_query()
152        self.assertTrue(actual_result['results'] == [{'docid': 'w001', 'b1id': 'w001'}])
153        self.query = 'explain SELECT meta(b1).id b1id, b2.name FROM ' + default_bucket + ' b1 JOIN ' + default_bucket + ' b2 ON KEY b2.docid FOR b1 WHERE meta(b1).id > ""'
154        actual_result = self.run_cbq_query()
155        self.assertTrue("covers" in str(actual_result))
156        self.assertTrue("ix1" in str(actual_result))
157        self.query = 'SELECT meta(b1).id b1id, b2.name FROM ' + default_bucket + ' b1 JOIN ' + default_bucket + ' b2 ON KEY b2.docid FOR b1 WHERE meta(b1).id > ""'
158        actual_result = self.run_cbq_query()
159        self.assertTrue(actual_result['results'] == [{'b1id': 'w001', 'name': 'pdoc'}])
160        self.query = 'explain SELECT meta(b1).id b1id, b2.name, b3.docid FROM ' + default_bucket + ' b1 JOIN ' + default_bucket + ' b2 ON KEY b2.docid FOR b1 JOIN ' + default_bucket + ' b3 ON KEY b3.docid FOR b1 WHERE meta(b1).id > ""'
161        actual_result = self.run_cbq_query()
162        self.assertTrue("covers" in str(actual_result))
163        self.assertTrue("ix1" in str(actual_result))
164        self.query = 'SELECT meta(b1).id b1id, b2.name, b3.docid FROM ' + default_bucket + ' b1 JOIN ' + default_bucket + ' b2 ON KEY b2.docid FOR b1 JOIN ' + default_bucket + ' b3 ON KEY b3.docid FOR b1 WHERE meta(b1).id > ""'
165        actual_result = self.run_cbq_query()
166        self.assertTrue(actual_result['results'] == [{'docid': 'w001', 'b1id': 'w001', 'name': 'pdoc'}])
167        self.query = 'explain SELECT meta(b1).id b1id, b2.name, b3.docid  FROM ' + default_bucket + ' b1 JOIN ' + default_bucket + ' b2 ON KEY b2.docid FOR b1 JOIN ' + default_bucket + ' b3 ON KEY b3.docid FOR b2 WHERE meta(b1).id > ""'
168        actual_result = self.run_cbq_query()
169        self.assertTrue("covers" in str(actual_result))
170        self.assertTrue("ix1" in str(actual_result))
171        self.query = 'SELECT meta(b1).id b1id, b2.name, b3.docid  FROM ' + default_bucket + ' b1 JOIN ' + default_bucket + ' b2 ON KEY b2.docid FOR b1 JOIN ' + default_bucket + ' b3 ON KEY b3.docid FOR b2 WHERE meta(b1).id > "";'
172        actual_result = self.run_cbq_query()
173        self.assertTrue(actual_result['metrics']['resultCount'] == 0)
174        self.query = 'explain SELECT meta(b1).id b1id, meta(b2).id, b2.name  FROM ' + default_bucket + ' b1 JOIN ' + default_bucket + ' b2 ON KEY b2.docid FOR b1 WHERE meta(b1).id > "" AND b2.type = "wdoc"'
175        actual_result = self.run_cbq_query()
176        self.assertTrue("covers" in str(actual_result))
177        self.assertTrue("ix2" in str(actual_result))
178        self.query = 'SELECT meta(b1).id b1id, meta(b2).id, b2.name  FROM ' + default_bucket + ' b1 JOIN ' + default_bucket + ' b2 ON KEY b2.docid FOR b1 WHERE meta(b1).id > "" AND b2.type = "wdoc"'
179        actual_result = self.run_cbq_query()
180        self.assertTrue(actual_result['metrics']['resultCount'] == 0)
181        self.query = 'explain SELECT meta(b1).id b1id, b2.name, b3.docid FROM ' + default_bucket + ' b1 JOIN ' + default_bucket + ' b2 ON KEY b2.docid FOR b1 JOIN ' + default_bucket + ' b3 ON KEY b3.docid FOR b1 WHERE meta(b1).id > "" AND b2.type = "wdoc"'
182        actual_result = self.run_cbq_query()
183        self.assertTrue("covers" in str(actual_result))
184        self.assertTrue("ix2" in str(actual_result))
185        self.query = 'SELECT meta(b1).id b1id, b2.name, b3.docid FROM ' + default_bucket + ' b1 JOIN ' + default_bucket + ' b2 ON KEY b2.docid FOR b1 JOIN ' + default_bucket + ' b3 ON KEY b3.docid FOR b1 WHERE meta(b1).id > "" AND b2.type = "wdoc"'
186        actual_result = self.run_cbq_query()
187        self.assertTrue(actual_result['metrics']['resultCount'] == 0)
188        self.query = 'explain SELECT meta(b1).id b1id, b2.name, b3.docid FROM ' + default_bucket + ' b1 JOIN ' + default_bucket + ' b2 ON KEY b2.docid FOR b1 JOIN ' + default_bucket + ' b3 ON KEY b3.docid FOR b2 WHERE meta(b1).id > "" AND b2.type = "wdoc"'
189        actual_result = self.run_cbq_query()
190        self.assertTrue("covers" in str(actual_result))
191        self.assertTrue("ix2" in str(actual_result))
192        self.query = 'SELECT meta(b1).id b1id, b2.name, b3.docid FROM ' + default_bucket + ' b1 JOIN ' + default_bucket + ' b2 ON KEY b2.docid FOR b1 JOIN ' + default_bucket + ' b3 ON KEY b3.docid FOR b2 WHERE meta(b1).id > "" AND b2.type = "wdoc"'
193        actual_result = self.run_cbq_query()
194        self.assertTrue(actual_result['metrics']['resultCount'] == 0)
195        self.query = 'SELECT meta(b1).id b1id, b2 from ' + default_bucket + ' b1 JOIN ' + default_bucket + ' b2 ON KEY b2.docid FOR b1 WHERE meta(b1).id > ""'
196        actual_result = self.run_cbq_query()
197        self.assertTrue(actual_result['results'] == [{'b1id': 'w001',
198                                                      'b2': {'phones': ['123-456-7890', '123-456-7891'], 'type': 'pdoc',
199                                                             'docid': 'w001', 'name': 'pdoc', 'altid': 'w001'}}])
200
201        self.query = 'explain SELECT meta(b1).id b1id from ' + default_bucket + ' b1 NEST ' + default_bucket + ' b2 ON KEY b2.docid FOR b1 WHERE meta(b1).id > ""'
202        actual_result = self.run_cbq_query()
203        self.assertTrue("covers" in str(actual_result))
204        self.assertTrue("ix1" in str(actual_result))
205        self.assertTrue("(`b2`.`docid`)" in str(actual_result))
206        self.query = 'SELECT meta(b1).id b1id from ' + default_bucket + ' b1 NEST ' + default_bucket + ' b2 ON KEY b2.docid FOR b1 WHERE meta(b1).id > ""'
207        actual_result = self.run_cbq_query()
208        self.assertTrue(actual_result['results'] == [{'b1id': 'w001'}])
209        self.query = 'explain SELECT meta(b1).id b1id, b2 from ' + default_bucket + ' b1 NEST ' + default_bucket + ' b2 ON KEY b2.docid FOR b1 WHERE meta(b1).id > ""'
210        actual_result = self.run_cbq_query()
211        self.assertTrue("covers" in str(actual_result))
212        self.assertTrue("ix1" in str(actual_result))
213        self.assertTrue("(`b2`.`docid`)" in str(actual_result))
214        self.query = 'SELECT meta(b1).id b1id, b2 from ' + default_bucket + ' b1 NEST ' + default_bucket + ' b2 ON KEY b2.docid FOR b1 WHERE meta(b1).id > ""'
215        actual_result = self.run_cbq_query()
216        self.assertTrue(actual_result['results'] == [{'b1id': 'w001', 'b2': [
217            {'phones': ['123-456-7890', '123-456-7891'], 'type': 'pdoc', 'docid': 'w001', 'name': 'pdoc',
218             'altid': 'w001'}]}])
219        self.query = 'delete from ' + default_bucket + ' use keys["w001","pdoc1","pdoc2"]'
220        self.run_cbq_query()
221
222    def test_basic_nest_join(self):
223        default_bucket =  self.query_buckets[0]
224        self.query = 'insert into ' + default_bucket + ' values("a_12345",{ "_id": "a_12345", "_type": "service" })'
225        self.run_cbq_query()
226        self.query = 'insert into ' + default_bucket + ' values("b_12345", { "_id": "b_12345", "parent": "a_12345", ' \
227                                                       '"data": { "a": "b", "c": "d" } }) '
228        self.run_cbq_query()
229        self.query = 'insert into ' + default_bucket + ' values("b_12346", { "_id": "b_12346", "parent": "a_12345", ' \
230                                                       '"data": { "6": "3", "d": "f" } }) '
231        self.run_cbq_query()
232        self.query = 'CREATE INDEX idx_parent ON ' + default_bucket + '( parent )'
233        self.run_cbq_query()
234        self.query = 'SELECT * FROM ' + default_bucket + ' a NEST ' + default_bucket + ' b ON KEY b.parent FOR a'
235        actual_result = self.run_cbq_query()
236        self.assertTrue(actual_result['results'] == ([{'a': {'_type': 'service', '_id': 'a_12345'}, 'b': [
237            {'_id': 'b_12345', 'data': {'a': 'b', 'c': 'd'}, 'parent': 'a_12345'},
238            {'_id': 'b_12346', 'data': {'d': 'f', '6': '3'}, 'parent': 'a_12345'}]}]))
239        self.query = 'SELECT * FROM ' + default_bucket + ' a join ' + default_bucket + ' b ON KEY b.parent FOR a'
240        actual_result = self.run_cbq_query()
241        self.assertTrue(actual_result['results'] == ([{'a': {'_type': 'service', '_id': 'a_12345'},
242                                                       'b': {'_id': 'b_12345', 'data': {'a': 'b', 'c': 'd'},
243                                                             'parent': 'a_12345'}},
244                                                      {'a': {'_type': 'service', '_id': 'a_12345'},
245                                                       'b': {'_id': 'b_12346', 'data': {'d': 'f', '6': '3'},
246                                                             'parent': 'a_12345'}}]))
247        self.query = 'delete from ' + default_bucket + ' use keys ["a_12345","b_12345","b_12346"]'
248        self.run_cbq_query()
249
250    def test_where_join_keys_covering(self):
251        created_indexes = []
252        ind_list = ["one"]
253        index_name = "one"
254        for query_bucket, bucket in zip(self.query_buckets, self.buckets):
255            for ind in ind_list:
256                index_name = "coveringindex%s" % ind
257                if ind == "one":
258                    self.query = "CREATE INDEX %s ON %s(name, tasks_ids,job_title)  USING %s" % (
259                        index_name, query_bucket, self.index_type)
260                    # if self.gsi_type:
261                    #     self.query += " WITH {'index_type': 'memdb'}"
262                self.run_cbq_query()
263                self._wait_for_index_online(bucket, index_name)
264                created_indexes.append(index_name)
265        for query_bucket in self.query_buckets:
266            try:
267                self.query = "EXPLAIN SELECT employee.name, employee.tasks_ids, employee.job_title new_project " + \
268                             "FROM %s as employee %s JOIN %s as new_project_full " % (
269                                 query_bucket, self.type_join, self.query_buckets[0]) + \
270                             "ON KEYS employee.tasks_ids WHERE employee.name == 'employee-9'"
271                if self.covering_index:
272                    self.check_explain_covering_index(index_name[0])
273                self.query = "SELECT employee.name , employee.tasks_ids " + \
274                             "FROM %s as employee %s JOIN %s as new_project_full " % (
275                                 query_bucket, self.type_join, self.query_buckets[0]) + \
276                             "ON KEYS employee.tasks_ids WHERE employee.name == 'employee-9' limit 10"
277                actual_result = self.run_cbq_query()
278                actual_result = actual_result['results']
279                expected_result = self._generate_full_joined_docs_list(join_type=self.type_join)
280                expected_result = [{"name": doc['name'], "tasks_ids": doc['tasks_ids']
281                                    }
282                                   for doc in expected_result if doc and 'name' in doc and
283                                   doc['name'] == 'employee-9']
284                expected_result = expected_result[0:10]
285                self.query = "create primary index on %s" % query_bucket
286                self.run_cbq_query()
287                self.query = "SELECT employee.name , employee.tasks_ids " + \
288                             "FROM %s as employee %s JOIN %s as new_project_full " % (
289                                 query_bucket, self.type_join, self.query_buckets[0]) + \
290                             "ON KEYS employee.tasks_ids WHERE employee.name == 'employee-9' limit 10"
291                result = self.run_cbq_query()
292                diffs = DeepDiff(actual_result, result['results'], ignore_order=True)
293                if diffs:
294                    self.assertTrue(False, diffs)
295            finally:
296                self.query = "drop primary index on %s" % query_bucket
297                self.run_cbq_query()
298                # self.assertTrue(expected_result == actual_result)
299                for index_name in created_indexes:
300                    self.query = "DROP INDEX %s ON %s USING %s" % (index_name, query_bucket, self.index_type)
301                    self.run_cbq_query()
302
303    def test_where_join_keys_not_equal(self):
304        for query_bucket in self.query_buckets:
305            self.query = "SELECT employee.join_day, employee.tasks_ids, new_project_full.project new_project " + \
306                         "FROM %s as employee %s JOIN %s as new_project_full " % (query_bucket, self.type_join, self.query_buckets[0]) + \
307                         "ON KEYS employee.tasks_ids WHERE employee.join_day != 2"
308            actual_result = self.run_cbq_query()
309            actual_result = actual_result['results']
310            expected_result = self._generate_full_joined_docs_list(join_type=self.type_join)
311            expected_result = [{"join_day": doc['join_day'], "tasks_ids": doc['tasks_ids'],
312                                "new_project": doc['project']}
313                               for doc in expected_result if doc and 'join_day' in doc and
314                               doc['join_day'] != 2]
315            self._verify_results(actual_result, expected_result)
316
317    def test_where_join_keys_between(self):
318        for query_bucket in self.query_buckets:
319            self.query = "SELECT employee.join_day, employee.tasks_ids, new_project_full.project new_project " + \
320                         "FROM %s as employee %s JOIN %s as new_project_full " % (query_bucket, self.type_join, self.query_buckets[0]) + \
321                         "ON KEYS employee.tasks_ids WHERE employee.join_day between 1 and 2"
322            actual_result = self.run_cbq_query()
323            actual_result = actual_result['results']
324            expected_result = self._generate_full_joined_docs_list(join_type=self.type_join)
325            expected_result = [{"join_day": doc['join_day'], "tasks_ids": doc['tasks_ids'],
326                                "new_project": doc['project']}
327                               for doc in expected_result if doc and 'join_day' in doc and
328                               doc['join_day'] <= 2]
329            self._verify_results(actual_result, expected_result)
330
331    def test_where_join_keys_not_equal_more_less(self):
332        for query_bucket in self.query_buckets:
333            self.query = "SELECT employee.join_day, employee.tasks_ids, new_project_full.project new_project " + \
334                         "FROM %s as employee %s JOIN %s as new_project_full " % (query_bucket, self.type_join, self.query_buckets[0]) + \
335                         "ON KEYS employee.tasks_ids WHERE employee.join_day <> 2"
336            actual_result = self.run_cbq_query()
337            actual_result = actual_result['results']
338            expected_result = self._generate_full_joined_docs_list(join_type=self.type_join)
339            expected_result = [{"join_day": doc['join_day'], "tasks_ids": doc['tasks_ids'],
340                                "new_project": doc['project']}
341                               for doc in expected_result if doc and 'join_day' in doc and
342                               doc['join_day'] != 2]
343            self._verify_results(actual_result, expected_result)
344
345    def test_where_join_keys_equal_less(self):
346        for query_bucket in self.query_buckets:
347            self.query = "SELECT employee.join_day, employee.tasks_ids, new_project_full.project new_project " + \
348                         "FROM %s as employee %s JOIN %s as new_project_full " % (query_bucket, self.type_join, self.query_buckets[0]) + \
349                         "ON KEYS employee.tasks_ids WHERE employee.join_day <= 2"
350            actual_result = self.run_cbq_query()
351            actual_result = actual_result['results']
352            expected_result = self._generate_full_joined_docs_list(join_type=self.type_join)
353            expected_result = [{"join_day": doc['join_day'], "tasks_ids": doc['tasks_ids'],
354                                "new_project": doc['project']}
355                               for doc in expected_result if doc and 'join_day' in doc and
356                               doc['join_day'] <= 2]
357            self._verify_results(actual_result, expected_result)
358
359    def test_where_join_keys_equal_more(self):
360        for query_bucket in self.query_buckets:
361            self.query = "SELECT employee.join_day, employee.tasks_ids, new_project_full.project new_project " + \
362                         "FROM %s as employee %s JOIN %s as new_project_full " % (query_bucket, self.type_join, self.query_buckets[0]) + \
363                         "ON KEYS employee.tasks_ids WHERE employee.join_day <= 2"
364            actual_result = self.run_cbq_query()
365            actual_result = actual_result['results']
366            expected_result = self._generate_full_joined_docs_list(join_type=self.type_join)
367            expected_result = [{"join_day": doc['join_day'], "tasks_ids": doc['tasks_ids'],
368                                "new_project": doc['project']}
369                               for doc in expected_result if doc and 'join_day' in doc and
370                               doc['join_day'] <= 2]
371            self._verify_results(actual_result, expected_result)
372
373    def test_where_join_keys_equal_more_covering(self):
374        created_indexes = []
375        ind_list = ["one"]
376        index_name = "one"
377        for query_bucket, bucket in zip(self.query_buckets, self.buckets):
378            for ind in ind_list:
379                index_name = "coveringindex%s" % ind
380                if ind == "one":
381                    self.query = "CREATE INDEX %s ON %s(join_day, tasks_ids, job_title)  USING %s" % (
382                        index_name, query_bucket, self.index_type)
383                    # if self.gsi_type:
384                    #     self.query += " WITH {'index_type': 'memdb'}"
385                self.run_cbq_query()
386                self._wait_for_index_online(bucket, index_name)
387                created_indexes.append(index_name)
388        for query_bucket in self.query_buckets:
389            self.query = "EXPLAIN SELECT employee.join_day, employee.tasks_ids, new_project_full.project new_project " + \
390                         "FROM %s as employee %s JOIN %s as new_project_full " % (query_bucket, self.type_join, self.query_buckets[0]) + \
391                         "ON KEYS employee.tasks_ids WHERE employee.join_day <= 2 order by employee.join_day limit 10"
392            if self.covering_index:
393                self.check_explain_covering_index(index_name[0])
394            self.query = "SELECT employee.join_day, employee.tasks_ids, new_project_full.project new_project " + \
395                         "FROM %s as employee %s JOIN %s as new_project_full " % (query_bucket, self.type_join, self.query_buckets[0]) + \
396                         "ON KEYS employee.tasks_ids WHERE employee.join_day <= 2  order by employee.join_day limit 10"
397            actual_result = self.run_cbq_query()
398            actual_result = actual_result['results']
399            expected_result = self._generate_full_joined_docs_list(join_type=self.type_join)
400            expected_result = [{"join_day": doc['join_day'], "tasks_ids": doc['tasks_ids'],
401                                "new_project": doc['project']}
402                               for doc in expected_result if doc and 'join_day' in doc and
403                               doc['join_day'] <= 2]
404            expected_result = expected_result[0:10]
405            for index_name in created_indexes:
406                self.query = "DROP INDEX %s ON %s USING %s" % (index_name, query_bucket, self.index_type)
407                self.run_cbq_query()
408            self.query = "CREATE PRIMARY INDEX ON %s" % query_bucket
409            self.run_cbq_query()
410            self.sleep(15, 'wait for index')
411            self.query = "SELECT employee.join_day, employee.tasks_ids, new_project_full.project new_project " + \
412                         "FROM %s as employee %s JOIN %s as new_project_full " % (query_bucket, self.type_join, self.query_buckets[0]) + \
413                         "ON KEYS employee.tasks_ids WHERE employee.join_day <= 2  order by employee.join_day limit 10"
414            result = self.run_cbq_query()
415            diffs = DeepDiff(actual_result, result['results'], ignore_order=True)
416            if diffs:
417                self.assertTrue(False, diffs)
418            self.query = "DROP PRIMARY INDEX ON %s" % query_bucket
419            self.run_cbq_query()
420
421    def test_join_unnest_alias(self):
422        for query_bucket in self.query_buckets:
423            self.query = "SELECT task2 FROM %s emp1 JOIN %s" % (query_bucket, query_bucket) + \
424                         " task ON KEYS emp1.tasks_ids UNNEST emp1.tasks_ids as task2"
425            actual_result = self.run_cbq_query()
426            actual_result = actual_result['results']
427            expected_result = self._generate_full_joined_docs_list()
428            expected_result = [{"task2": task} for doc in expected_result
429                               for task in doc['tasks_ids']]
430            self._verify_results(actual_result, expected_result)
431
432    def test_unnest(self):
433        for query_bucket in self.query_buckets:
434            self.query = "SELECT emp.name, task FROM %s emp %s UNNEST emp.tasks_ids task" % (
435                query_bucket, self.type_join)
436            actual_result = self.run_cbq_query()
437            actual_result = actual_result['results']
438            expected_result = self.generate_full_docs_list(self.gens_load)
439            expected_result = [{"task": task, "name": doc["name"]}
440                               for doc in expected_result for task in doc['tasks_ids']]
441            if self.type_join.upper() == JOIN_LEFT:
442                expected_result.extend([{}] * self.gens_tasks[-1].end)
443
444            self._verify_results(actual_result, expected_result)
445
446    def test_unnest_covering(self):
447        created_indexes = []
448        ind_list = ["one"]
449        index_name = "one"
450        for query_bucket, bucket in zip(self.query_buckets, self.buckets):
451            for ind in ind_list:
452                index_name = "coveringindex%s" % ind
453                if ind == "one":
454                    self.query = "CREATE INDEX %s ON %s(name, task, tasks_ids)  USING %s" % (
455                        index_name, query_bucket, self.index_type)
456                    # if self.gsi_type:
457                    #     self.query += " WITH {'index_type': 'memdb'}"
458                self.run_cbq_query()
459                self._wait_for_index_online(bucket, index_name)
460                created_indexes.append(index_name)
461        for query_bucket in self.query_buckets:
462            try:
463                self.query = "EXPLAIN SELECT emp.name, task FROM %s emp %s UNNEST emp.tasks_ids task where emp.name is not null" % (
464                    query_bucket, self.type_join)
465                if self.covering_index:
466                    self.check_explain_covering_index(index_name[0])
467                self.query = "SELECT emp.name, task FROM %s emp %s UNNEST emp.tasks_ids task where emp.name is not null" % (
468                    query_bucket, self.type_join)
469                actual_result = self.run_cbq_query()
470                actual_result = actual_result['results']
471                expected_result = self.generate_full_docs_list(self.gens_load)
472                expected_result = [{"task": task, "name": doc["name"]} for doc in expected_result for task in
473                                   doc['tasks_ids']]
474                if self.type_join.upper() == JOIN_LEFT:
475                    expected_result.extend([{}] * self.gens_tasks[-1].end)
476                try:
477                    self.query = "create primary index on %s" % query_bucket
478                    self.run_cbq_query()
479                    self.sleep(15, 'wait for index')
480                except Exception as e:
481                    if "Index #primary already exists." in str(e):
482                        continue
483                    else:
484                        self.fail("Index failed to be created! {0}".format(str(e)))
485                self.query = "SELECT emp.name, task FROM %s emp use index (`#primary`) %s UNNEST emp.tasks_ids task where emp.name is not null" % (
486                    query_bucket, self.type_join)
487                result = self.run_cbq_query()
488
489                diffs = DeepDiff(actual_result, result['results'], ignore_order=True)
490                if diffs:
491                    self.assertTrue(False, diffs)
492                # self._verify_results(actual_result, expected_result)
493            finally:
494                self.query = "drop primary index on %s" % query_bucket
495                self.run_cbq_query()
496                for index_name in created_indexes:
497                    self.query = "DROP INDEX %s ON %s USING %s" % (index_name, query_bucket, self.index_type)
498                    self.run_cbq_query()
499
500    def test_prepared_unnest(self):
501        for query_bucket in self.query_buckets:
502            self.query = "SELECT emp.name, task FROM %s emp %s UNNEST emp.tasks_ids task" % (
503                query_bucket, self.type_join)
504            self.prepared_common_body()
505
506    ##############################################################################################
507    #
508    #   SUBQUERY
509    ##############################################################################################
510
511    def test_subquery_count(self):
512        for query_bucket in self.query_buckets:
513            self.query = "select name, ARRAY_LENGTH((select task_name  from %s d use keys %s)) as cn from %s" % (
514                query_bucket, str(['test_task-%s' % i for i in range(0, 29)]),
515                query_bucket)
516            self.run_cbq_query()
517            actual_result = self.run_cbq_query()
518            actual_result = actual_result['results']
519            all_docs_list = self.generate_full_docs_list(self.gens_load)
520            expected_result = [{'name': doc['name'], 'cn': 29} for doc in all_docs_list]
521            expected_result.extend([{'cn': 29}] * 29)
522            self._verify_results(actual_result, expected_result)
523
524    def test_subquery_select(self):
525        for query_bucket in self.query_buckets:
526            self.query = "select task_name, (select count(task_name) cn from %s d use keys %s) as names from %s" % (
527                query_bucket, str(['test_task-%s' % i for i in range(0, 29)]),
528                query_bucket)
529            self.run_cbq_query()
530            actual_result = self.run_cbq_query()
531            actual_result = actual_result['results']
532            expected_result_subquery = {"cn": 29}
533            expected_result = [{'names': [expected_result_subquery]}] * len(
534                self.generate_full_docs_list(self.gens_load))
535            expected_result.extend([{'task_name': doc['task_name'], 'names': [expected_result_subquery]}
536                                    for doc in self.generate_full_docs_list(self.gens_tasks)])
537            self._verify_results(actual_result, expected_result)
538
539    def test_prepared_subquery_select(self):
540        for query_bucket in self.query_buckets:
541            self.query = "select task_name, (select count(task_name) cn from %s d use keys %s) as names from %s" % (
542                query_bucket, str(['test_task-%s' % i for i in range(0, 29)]),
543                query_bucket)
544            self.prepared_common_body()
545
546    def test_subquery_where_aggr(self):
547        for query_bucket in self.query_buckets:
548            self.query = "select name, join_day from %s where join_day =" % query_bucket + \
549                         " (select AVG(join_day) as average from %s d use keys %s)[0].average" % (query_bucket,
550                                                                                                  str([
551                                                                                                      'query-test-Sales-2010-1-1-%s' % i
552                                                                                                      for i in
553                                                                                                      range(0,
554                                                                                                            self.docs_per_day)]))
555            all_docs_list = self.generate_full_docs_list(self.gens_load)
556            actual_result = self.run_cbq_query()
557            actual_result = actual_result['results']
558            expected_result = [{'name': doc['name'], 'join_day': doc['join_day']} for doc in all_docs_list if
559                               doc['join_day'] == 1]
560            self._verify_results(actual_result, expected_result)
561
562    def test_subquery_where_in(self):
563        for query_bucket in self.query_buckets:
564            self.query = "select name, join_day from %s where join_day IN " % query_bucket + \
565                         " (select ARRAY_AGG(join_day) as average from %s d use keys %s)[0].average" % (query_bucket,
566                                                                                                        str([
567                                                                                                            'query-test-Sales-2010-1-1-%s' % i
568                                                                                                            for i in
569                                                                                                            range(0,
570                                                                                                                  self.docs_per_day)]))
571            all_docs_list = self.generate_full_docs_list(self.gens_load)
572            actual_result = self.run_cbq_query()
573            actual_result = actual_result['results']
574            expected_result = [{'name': doc['name'], 'join_day': doc['join_day']}
575                               for doc in all_docs_list if doc['join_day'] == 1]
576            self._verify_results(actual_result, expected_result)
577
578    def test_where_in_subquery(self):
579        for query_bucket in self.query_buckets:
580            self.query = "select name, tasks_ids from %s where tasks_ids[0] IN" % query_bucket + \
581                         " (select ARRAY_AGG(DISTINCT task_name) as names from %s d " % query_bucket + \
582                         "use keys %s where project='MB')[0].names" % '["test_task-1", "test_task-2"]'
583            all_docs_list = self.generate_full_docs_list(self.gens_load)
584            actual_result = self.run_cbq_query()
585            actual_result = actual_result['results']
586            expected_result = [{'name': doc['name'], 'tasks_ids': doc['tasks_ids']}
587                               for doc in all_docs_list if doc['tasks_ids'] in ['test_task-1', 'test_task-2']]
588            self._verify_results(actual_result, expected_result)
589
590    def test_where_in_subquery_not_equal(self):
591        for query_bucket in self.query_buckets:
592            self.query = "select name, tasks_ids from %s where tasks_ids[0] IN" % query_bucket + \
593                         " (select ARRAY_AGG(DISTINCT task_name) as names from %s d " % query_bucket + \
594                         "use keys %s where project!='AB')[0].names" % '["test_task-1", "test_task-2"]'
595            all_docs_list = self.generate_full_docs_list(self.gens_load)
596            actual_result = self.run_cbq_query()
597            actual_result = actual_result['results']
598            expected_result = [{'name': doc['name'], 'tasks_ids': doc['tasks_ids']}
599                               for doc in all_docs_list if
600                               ('test_task-1' in doc['tasks_ids'] or 'test_task-2' in doc['tasks_ids'])]
601            self._verify_results(actual_result, expected_result)
602
603    def test_where_in_subquery_equal_more(self):
604        for query_bucket in self.query_buckets:
605            self.query = "select name, tasks_ids,join_day from %s where join_day>=2 and tasks_ids[0] IN" % query_bucket + \
606                         " (select ARRAY_AGG(DISTINCT task_name) as names from %s d " % query_bucket + \
607                         "use keys %s where project!='AB')[0].names" % '["test_task-1", "test_task-2"]'
608            all_docs_list = self.generate_full_docs_list(self.gens_load)
609            actual_result = self.run_cbq_query()
610            actual_result = actual_result['results']
611            expected_result = [{'name': doc['name'], 'tasks_ids': doc['tasks_ids'], 'join_day': doc['join_day']}
612                               for doc in all_docs_list if
613                               ('test_task-1' in doc['tasks_ids'] or 'test_task-2' in doc['tasks_ids']) and doc[
614                                   'join_day'] >= 2]
615            self._verify_results(actual_result, expected_result)
616
617    def test_where_in_subquery_equal_less(self):
618        for query_bucket in self.query_buckets:
619            self.query = "select name, tasks_ids,join_day from %s where join_day<=2 and tasks_ids[0] IN" % query_bucket + \
620                         " (select ARRAY_AGG(DISTINCT task_name) as names from %s d " % query_bucket + \
621                         "use keys %s where project!='AB')[0].names" % '["test_task-1", "test_task-2"]'
622            all_docs_list = self.generate_full_docs_list(self.gens_load)
623            actual_result = self.run_cbq_query()
624            actual_result = actual_result['results']
625            expected_result = [{'name': doc['name'], 'tasks_ids': doc['tasks_ids'], 'join_day': doc['join_day']}
626                               for doc in all_docs_list if
627                               ('test_task-1' in doc['tasks_ids'] or 'test_task-2' in doc['tasks_ids']) and doc[
628                                   'join_day'] <= 2]
629            self._verify_results(actual_result, expected_result)
630
631    def test_where_in_subquery_between(self):
632        for query_bucket in self.query_buckets:
633            self.query = "select name, tasks_ids, join_day from %s where (join_day between 1 and 12) and tasks_ids[0] IN" % query_bucket + \
634                         " (select ARRAY_AGG(DISTINCT task_name) as names from %s d " % query_bucket + \
635                         "use keys %s where project!='AB')[0].names" % '["test_task-1", "test_task-2"]'
636            all_docs_list = self.generate_full_docs_list(self.gens_load)
637            actual_result = self.run_cbq_query()
638            actual_result = actual_result['results']
639            expected_result = [{'name': doc['name'], 'tasks_ids': doc['tasks_ids'], 'join_day': doc['join_day']}
640                               for doc in all_docs_list if
641                               ('test_task-1' in doc['tasks_ids'] or 'test_task-2' in doc['tasks_ids']) and doc[
642                                   'join_day'] <= 12]
643            self._verify_results(actual_result, expected_result)
644
645    def test_subquery_exists(self):
646        for query_bucket in self.query_buckets:
647            self.query = "SELECT name FROM %s d1 WHERE " % query_bucket + \
648                         "EXISTS (SELECT * FROM %s d  use keys toarray(d1.tasks_ids[0]))" % query_bucket
649            all_docs_list = self.generate_full_docs_list(self.gens_load)
650            tasks_ids = [doc["task_name"] for doc in self.generate_full_docs_list(self.gens_tasks)]
651            actual_result = self.run_cbq_query()
652            actual_result = actual_result['results']
653            expected_result = [{'name': doc['name']} for doc in all_docs_list if doc['tasks_ids'][0] in tasks_ids]
654            self._verify_results(actual_result, expected_result)
655
656    def test_subquery_exists_where(self):
657        for query_bucket in self.query_buckets:
658            self.query = "SELECT name FROM %s d1 WHERE " % query_bucket + \
659                         "EXISTS (SELECT * FROM %s d use keys toarray(d1.tasks_ids[0]) where d.project='MB')" % query_bucket
660            all_docs_list = self.generate_full_docs_list(self.gens_load)
661            tasks_ids = [doc["task_name"] for doc in self.generate_full_docs_list(self.gens_tasks) if
662                         doc['project'] == 'MB']
663            actual_result = self.run_cbq_query()
664            actual_result = actual_result['results']
665            expected_result = [{'name': doc['name']} for doc in all_docs_list if doc['tasks_ids'][0] in tasks_ids]
666            self._verify_results(actual_result, expected_result)
667
668    def test_subquery_exists_and(self):
669        for query_bucket in self.query_buckets:
670            self.query = "SELECT name FROM %s d1 WHERE " % query_bucket + \
671                         "EXISTS (SELECT * FROM %s d  use keys toarray(d1.tasks_ids[0])) and join_mo>5" % query_bucket
672            all_docs_list = self.generate_full_docs_list(self.gens_load)
673            tasks_ids = [doc["task_name"] for doc in self.generate_full_docs_list(self.gens_tasks)]
674            actual_result = self.run_cbq_query()
675            actual_result = actual_result['results']
676            expected_result = [{'name': doc['name']} for doc in all_docs_list if
677                               doc['tasks_ids'][0] in tasks_ids and doc['join_mo'] > 5]
678            self._verify_results(actual_result, expected_result)
679
680    def test_subquery_from(self):
681        for query_bucket in self.query_buckets:
682            self.query = "SELECT TASKS.task_name FROM (SELECT task_name, project FROM %s WHERE project = 'CB') as TASKS" % query_bucket
683            all_docs_list = self.generate_full_docs_list(self.gens_tasks)
684            actual_result = self.run_cbq_query()
685            actual_result = actual_result['results']
686            expected_result = [{'task_name': doc['task_name']} for doc in all_docs_list if doc['project'] == 'CB']
687            self._verify_results(actual_result, expected_result)
688
689    def test_subquery_from_join(self):
690        for query_bucket in self.query_buckets:
691            self.query = "SELECT EMP.name Name, TASK.project proj FROM (SELECT tasks_ids, name FROM " + \
692                         "%s WHERE join_mo>10) as EMP %s JOIN %s TASK ON KEYS EMP.tasks_ids" % (
693                             query_bucket, self.type_join, query_bucket)
694            all_docs_list = self._generate_full_joined_docs_list(join_type=self.type_join)
695            actual_result = self.run_cbq_query()
696            actual_result = actual_result['results']
697            expected_result = [{'Name': doc['name'], 'proj': doc['project']} for doc in all_docs_list if
698                               doc['join_mo'] > 10]
699            self._verify_results(actual_result, expected_result)
700
701    ##############################################################################################
702    #
703    #   KEY
704    ##############################################################################################
705
706    def test_keys(self):
707        for query_bucket in self.query_buckets:
708            keys_select = []
709            generator = copy.deepcopy(self.gens_tasks[0])
710            for i in range(5):
711                key, _ = next(generator)
712                keys_select.append(key)
713            self.query = 'select task_name FROM %s USE KEYS %s' % (query_bucket, keys_select)
714            actual_result = self.run_cbq_query()
715            actual_result = actual_result['results']
716            full_list = self.generate_full_docs_list(self.gens_tasks, keys=keys_select)
717            expected_result = [{"task_name": doc['task_name']} for doc in full_list]
718            self._verify_results(actual_result, expected_result)
719
720            keys_select.extend(["wrong"])
721            self.query = 'select task_name FROM %s USE KEYS %s' % (query_bucket, keys_select)
722            actual_result = self.run_cbq_query()
723            actual_result = actual_result['results']
724            self._verify_results(actual_result, expected_result)
725
726            self.query = 'select task_name FROM %s USE KEYS ["wrong_one","wrong_second"]' % query_bucket
727            actual_result = self.run_cbq_query()
728            self.assertFalse(actual_result['results'], "Having a wrong key query returned some result")
729
730    def test_key_array(self):
731        for query_bucket in self.query_buckets:
732            gen_select = copy.deepcopy(self.gens_tasks[0])
733            key_select, value_select = next(gen_select)
734            self.query = 'SELECT * FROM %s d USE KEYS ARRAY emp._id FOR emp IN [%s] END' % (query_bucket, value_select)
735            actual_result = self.run_cbq_query()
736            actual_result = actual_result['results']
737            expected_result = self.generate_full_docs_list(self.gens_tasks, keys=[key_select])
738            expected_result = [{'d': doc} for doc in expected_result]
739            self._verify_results(actual_result, expected_result)
740
741            key2_select, value2_select = next(gen_select)
742            self.query = 'SELECT * FROM %s d USE KEYS ARRAY emp._id FOR emp IN [%s,%s] END' % (
743                query_bucket, value_select, value2_select)
744            actual_result = self.run_cbq_query()
745            actual_result = actual_result['results']
746            expected_result = self.generate_full_docs_list(self.gens_tasks, keys=[key_select, key2_select])
747            expected_result = [{'d': doc} for doc in expected_result]
748            self._verify_results(actual_result, expected_result)
749
750    ##############################################################################################
751    #
752    #   NEST
753    ##############################################################################################
754
755    def test_simple_nest_keys(self):
756        for query_bucket in self.query_buckets:
757            self.query = "SELECT * FROM %s emp %s NEST %s tasks ON KEYS emp.tasks_ids" % (
758                query_bucket, self.type_join, query_bucket)
759            actual_result = self.run_cbq_query()
760            actual_result = actual_result['results']
761            actual_result = {item['emp']['_id']: item for item in actual_result}
762            full_list = self._generate_full_nested_docs_list(join_type=self.type_join)
763            expected_result = [{"emp": doc['item'], "tasks": doc['items_nested']} for doc in full_list if
764                               doc and 'items_nested' in doc]
765            expected_result.extend([{"emp": doc['item']} for doc in full_list if 'items_nested' not in doc])
766            expected_result = {item['emp']['_id']: item for item in expected_result}
767            self.assertEqual(expected_result, actual_result, "Results are not matching")
768
769    def test_simple_nest_key(self):
770        for query_bucket in self.query_buckets:
771            self.query = "SELECT * FROM %s emp %s NEST %s tasks KEY emp.tasks_ids[0]" % (
772                query_bucket, self.type_join, query_bucket)
773            actual_result = self.run_cbq_query()
774            actual_result = sorted(actual_result['results'], key=lambda doc: self._get_for_sort(doc))
775            self._delete_ids(actual_result)
776            full_list = self._generate_full_nested_docs_list(particular_key=0, join_type=self.type_join)
777            expected_result = [{"emp": doc['item'], "tasks": doc['items_nested']} for doc in full_list if
778                               doc and 'items_nested' in doc]
779            expected_result.extend([{"emp": doc['item']} for doc in full_list if 'items_nested' not in doc])
780            expected_result = sorted(expected_result, key=lambda doc: self._get_for_sort(doc))
781            self._delete_ids(expected_result)
782            self._verify_results(actual_result, expected_result)
783
784    def test_nest_keys_with_array(self):
785        for query_bucket in self.query_buckets:
786            self.query = "select emp.name, ARRAY item.project FOR item in items end projects " + \
787                         "FROM %s emp %s NEST %s items " % (
788                             query_bucket, self.type_join, query_bucket) + \
789                         "ON KEYS emp.tasks_ids"
790            actual_result = self.run_cbq_query()
791            actual_result = self.sort_nested_list(actual_result['results'], key='projects')
792            full_list = self._generate_full_nested_docs_list(join_type=self.type_join)
793            expected_result = [{"name": doc['item']['name'],
794                                "projects": [nested_doc['project'] for nested_doc in doc['items_nested']]}
795                               for doc in full_list if doc and 'items_nested' in doc]
796            expected_result.extend([{} for doc in full_list if 'items_nested' not in doc])
797            expected_result = self.sort_nested_list(expected_result, key='projects')
798            self._verify_results(actual_result, expected_result)
799
800    def test_prepared_nest_keys_with_array(self):
801        for query_bucket in self.query_buckets:
802            self.query = "select emp.name, ARRAY item.project FOR item in items end projects " + \
803                         "FROM %s emp %s NEST %s items " % (
804                             query_bucket, self.type_join, query_bucket) + \
805                         "ON KEYS emp.tasks_ids"
806            self.prepared_common_body()
807
808    def test_nest_keys_where(self):
809        for query_bucket in self.query_buckets:
810            self.query = "select emp.name, ARRAY item.project FOR item in items end projects " + \
811                         "FROM %s emp %s NEST %s items " % (
812                             query_bucket, self.type_join, query_bucket) + \
813                         "ON KEYS emp.tasks_ids where ANY item IN items SATISFIES item.project == 'CB' end"
814            actual_result = self.run_cbq_query()
815            actual_result = self.sort_nested_list(actual_result['results'], key='projects')
816            actual_result = sorted(actual_result, key=lambda doc: (doc['name'], doc['projects']))
817            full_list = self._generate_full_nested_docs_list(join_type=self.type_join)
818            expected_result = [{"name": doc['item']['name'],
819                                "projects": [nested_doc['project'] for nested_doc in doc['items_nested']]}
820                               for doc in full_list if doc and 'items_nested' in doc and
821                               len([nested_doc for nested_doc in doc['items_nested']
822                                    if nested_doc['project'] == 'CB']) > 0]
823            expected_result = self.sort_nested_list(expected_result, key='projects')
824            expected_result = sorted(expected_result, key=lambda doc: (doc['name'], doc['projects']))
825            self._verify_results(actual_result, expected_result)
826
827    def test_nest_keys_where_not_equal(self):
828        for query_bucket in self.query_buckets:
829            self.query = "select emp.name, ARRAY item.project FOR item in items end projects " + \
830                         "FROM %s emp %s NEST %s items " % (
831                             query_bucket, self.type_join, query_bucket) + \
832                         "ON KEYS emp.tasks_ids where ANY item IN items SATISFIES item.project != 'CB' end"
833            actual_result = self.run_cbq_query()
834            actual_result = self.sort_nested_list(actual_result['results'], key='projects')
835            actual_result = sorted(actual_result, key=lambda doc: (doc['name'], doc['projects']))
836            full_list = self._generate_full_nested_docs_list(join_type=self.type_join)
837            expected_result = [{"name": doc['item']['name'],
838                                "projects": [nested_doc['project'] for nested_doc in doc['items_nested']]}
839                               for doc in full_list if doc and 'items_nested' in doc and
840                               len([nested_doc for nested_doc in doc['items_nested']
841                                    if nested_doc['project'] != 'CB']) > 0]
842            expected_result = self.sort_nested_list(expected_result, key='projects')
843            expected_result = sorted(expected_result, key=lambda doc: (doc['name'], doc['projects']))
844            self._verify_results(actual_result, expected_result)
845
846    def test_nest_keys_where_between(self):
847        for query_bucket in self.query_buckets:
848            self.query = "select emp.name, emp.join_day, ARRAY item.project FOR item in items end projects " + \
849                         "FROM %s emp %s NEST %s items " % (
850                             query_bucket, self.type_join, query_bucket) + \
851                         "ON KEYS emp.tasks_ids where emp.join_day between 2 and 4"
852            actual_result = self.run_cbq_query()
853            actual_result = self.sort_nested_list(actual_result['results'], key='projects')
854            actual_result = sorted(actual_result, key=lambda doc: (doc['name'], doc['projects']))
855            full_list = self._generate_full_nested_docs_list(join_type=self.type_join)
856            expected_result = [{"name": doc['item']['name'], "join_day": doc['item']['join_day'],
857                                "projects": [nested_doc['project'] for nested_doc in doc['items_nested']]}
858                               for doc in full_list
859                               if doc and 'join_day' in doc['item'] and
860                               2 <= doc['item']['join_day'] <= 4]
861            expected_result = self.sort_nested_list(expected_result, key='projects')
862            expected_result = sorted(expected_result, key=lambda doc: (doc['name'], doc['projects']))
863            self._verify_results(actual_result, expected_result)
864
865    def test_nest_keys_where_less_more_equal(self):
866        for query_bucket in self.query_buckets:
867            self.query = "select emp.name, emp.join_day, emp.join_yr, ARRAY item.project FOR item in items end projects " + \
868                         "FROM %s emp %s NEST %s items " % (
869                             query_bucket, self.type_join, query_bucket) + \
870                         "ON KEYS emp.tasks_ids where emp.join_day <= 4 and emp.join_yr>=2010"
871            actual_result = self.run_cbq_query()
872            actual_result = self.sort_nested_list(actual_result['results'], key='projects')
873            full_list = self._generate_full_nested_docs_list(join_type=self.type_join)
874            expected_result = [{"name": doc['item']['name'], "join_day": doc['item']['join_day'],
875                                'join_yr': doc['item']['join_yr'],
876                                "projects": [nested_doc['project'] for nested_doc in doc['items_nested']]}
877                               for doc in full_list
878                               if doc and 'join_day' in doc['item'] and
879                               doc['item']['join_day'] <= 4 and doc['item']['join_yr'] >= 2010]
880            expected_result = self.sort_nested_list(expected_result, key='projects')
881            self._verify_results(actual_result, expected_result)
882
883    def test_dual(self):
884        self.query = "select 1"
885        actual_result = self.run_cbq_query()
886        self.query = "select 1 from system:dual"
887        expected_result = self.run_cbq_query()
888        self._verify_results(actual_result['results'], expected_result['results'])
889
890    def test_cartesian_join(self):
891        self.run_cbq_query("CREATE INDEX adv_job_title ON `default`(`job_title`)")
892        join_query = 'select a.name from default as a, default as b where a.job_title = b.job_title and a.join_yr = 2011 and a.join_mo = 1 and a.job_title = "Engineer" order by a.`_id` limit 3'
893        expected_result = [{"name": "employee-1"}, {"name": "employee-1"}, {"name": "employee-1"}]
894        actual_result = self.run_cbq_query(join_query)
895        self._verify_results(actual_result['results'], expected_result)