Expression based GSI index not being picked up when using the same expression in where clause

./testrunner -i b/resources/dev-6-nodes-xdcr_n1ql_2i.ini -t 2i.indexscans_2i.SecondaryIndexingScanTests.test_multi_create_query_explain_drop_index_with_index_expressions,groups=simple,dataset=default,use_gsi_for_primary=True,use_gsi_for_secondary=True,doc-per-day=10,skip_cleanup=True

In this test case we create a set of indexes as follows

2015-03-27 11:51:43 | INFO | MainProcess | test_thread | [tuq_helper.create_primary_index] CREATE PRIMARY INDEX ON default USING GSI
2015-03-27 11:51:43 | INFO | MainProcess | test_thread | [tuq_helper.run_cbq_query] RUN QUERY SELECT * FROM system:indexes
2015-03-27 11:51:43 | INFO | MainProcess | test_thread | [rest_client.query_tool] query params : statement=SELECT+%2A+FROM+system%3Aindexes
2015-03-27 11:51:43 | INFO | MainProcess | test_thread | [tuq_helper.run_cbq_query] TOTAL ELAPSED TIME: 138.076554ms
2015-03-27 11:51:43 | INFO | MainProcess | test_thread | [tuq_helper.run_cbq_query] RUN QUERY CREATE PRIMARY INDEX ON default USING GSI
2015-03-27 11:51:43 | INFO | MainProcess | test_thread | [rest_client.query_tool] query params : statement=CREATE+PRIMARY+INDEX+ON+default++USING+GSI
2015-03-27 11:51:43 | INFO | MainProcess | test_thread | [tuq_helper.run_cbq_query] TOTAL ELAPSED TIME: 203.168732ms
2015-03-27 11:51:43 | INFO | MainProcess | test_thread | [tuq_helper.run_cbq_query] RUN QUERY SELECT * FROM system:indexes
2015-03-27 11:51:43 | INFO | MainProcess | test_thread | [rest_client.query_tool] query params : statement=SELECT+%2A+FROM+system%3Aindexes
2015-03-27 11:51:43 | INFO | MainProcess | test_thread | [tuq_helper.run_cbq_query] TOTAL ELAPSED TIME: 137.954369ms
2015-03-27 11:51:53 | INFO | MainProcess | test_thread | [tuq_helper.run_cbq_query] RUN QUERY SELECT * FROM system:indexes
2015-03-27 11:51:53 | INFO | MainProcess | test_thread | [rest_client.query_tool] query params : statement=SELECT+%2A+FROM+system%3Aindexes
2015-03-27 11:51:53 | INFO | MainProcess | test_thread | [tuq_helper.run_cbq_query] TOTAL ELAPSED TIME: 45.671021ms
2015-03-27 11:51:54 | INFO | MainProcess | Cluster_Thread | [tuq_helper.run_cbq_query] RUN QUERY CREATE INDEX simple_index_4 ON default(join_yr > 1999) USING GSI WITH

{'nodes': ['127.0.0.1:9000']}

2015-03-27 11:51:54 | INFO | MainProcess | Cluster_Thread | [rest_client.query_tool] query params : statement=CREATE+INDEX+simple_index_4+ON+default%28join_yr+%3E+1999%29+USING+GSI+WITH%7B%27nodes%27%3A+%5B%27127.0.0.1%3A9000%27%5D%7D
2015-03-27 11:51:54 | INFO | MainProcess | Cluster_Thread | [tuq_helper.run_cbq_query] TOTAL ELAPSED TIME: 131.847081ms
2015-03-27 11:51:54 | INFO | MainProcess | Cluster_Thread | [tuq_helper.run_cbq_query] RUN QUERY SELECT * FROM system:indexes
2015-03-27 11:51:54 | INFO | MainProcess | Cluster_Thread | [rest_client.query_tool] query params : statement=SELECT+%2A+FROM+system%3Aindexes
2015-03-27 11:51:54 | INFO | MainProcess | Cluster_Thread | [tuq_helper.run_cbq_query] TOTAL ELAPSED TIME: 56.882941ms
2015-03-27 11:52:04 | INFO | MainProcess | Cluster_Thread | [tuq_helper.run_cbq_query] RUN QUERY SELECT * FROM system:indexes
2015-03-27 11:52:04 | INFO | MainProcess | Cluster_Thread | [rest_client.query_tool] query params : statement=SELECT+%2A+FROM+system%3Aindexes
2015-03-27 11:52:04 | INFO | MainProcess | Cluster_Thread | [tuq_helper.run_cbq_query] TOTAL ELAPSED TIME: 46.094421ms
2015-03-27 11:52:05 | INFO | MainProcess | Cluster_Thread | [tuq_helper.run_cbq_query] RUN QUERY CREATE INDEX simple_index_3 ON default(join_yr > 1999) USING GSI WITH

{'nodes': ['127.0.0.1:9000']}

2015-03-27 11:52:05 | INFO | MainProcess | Cluster_Thread | [rest_client.query_tool] query params : statement=CREATE+INDEX+simple_index_3+ON+default%28join_yr+%3E+1999%29+USING+GSI+WITH%7B%27nodes%27%3A+%5B%27127.0.0.1%3A9000%27%5D%7D
2015-03-27 11:52:05 | INFO | MainProcess | Cluster_Thread | [tuq_helper.run_cbq_query] TOTAL ELAPSED TIME: 299.412921ms
2015-03-27 11:52:05 | INFO | MainProcess | Cluster_Thread | [tuq_helper.run_cbq_query] RUN QUERY SELECT * FROM system:indexes
2015-03-27 11:52:05 | INFO | MainProcess | Cluster_Thread | [rest_client.query_tool] query params : statement=SELECT+%2A+FROM+system%3Aindexes
2015-03-27 11:52:06 | INFO | MainProcess | Cluster_Thread | [tuq_helper.run_cbq_query] TOTAL ELAPSED TIME: 126.39891ms
2015-03-27 11:52:16 | INFO | MainProcess | Cluster_Thread | [tuq_helper.run_cbq_query] RUN QUERY SELECT * FROM system:indexes
2015-03-27 11:52:16 | INFO | MainProcess | Cluster_Thread | [rest_client.query_tool] query params : statement=SELECT+%2A+FROM+system%3Aindexes
2015-03-27 11:52:16 | INFO | MainProcess | Cluster_Thread | [tuq_helper.run_cbq_query] TOTAL ELAPSED TIME: 65.544703ms
2015-03-27 11:52:17 | INFO | MainProcess | Cluster_Thread | [tuq_helper.run_cbq_query] RUN QUERY CREATE INDEX simple_index_2 ON default(job_title == "Sales") USING GSI WITH

{'nodes': ['127.0.0.1:9000']}

2015-03-27 11:52:17 | INFO | MainProcess | Cluster_Thread | [rest_client.query_tool] query params : statement=CREATE+INDEX+simple_index_2+ON+default%28job_title+%3D%3D+%22Sales%22%29+USING+GSI+WITH%7B%27nodes%27%3A+%5B%27127.0.0.1%3A9000%27%5D%7D
2015-03-27 11:52:17 | INFO | MainProcess | Cluster_Thread | [tuq_helper.run_cbq_query] TOTAL ELAPSED TIME: 144.96244ms
2015-03-27 11:52:17 | INFO | MainProcess | Cluster_Thread | [tuq_helper.run_cbq_query] RUN QUERY SELECT * FROM system:indexes
2015-03-27 11:52:17 | INFO | MainProcess | Cluster_Thread | [rest_client.query_tool] query params : statement=SELECT+%2A+FROM+system%3Aindexes
2015-03-27 11:52:17 | INFO | MainProcess | Cluster_Thread | [tuq_helper.run_cbq_query] TOTAL ELAPSED TIME: 105.541358ms
2015-03-27 11:52:27 | INFO | MainProcess | Cluster_Thread | [tuq_helper.run_cbq_query] RUN QUERY SELECT * FROM system:indexes
2015-03-27 11:52:27 | INFO | MainProcess | Cluster_Thread | [rest_client.query_tool] query params : statement=SELECT+%2A+FROM+system%3Aindexes
2015-03-27 11:52:27 | INFO | MainProcess | Cluster_Thread | [tuq_helper.run_cbq_query] TOTAL ELAPSED TIME: 80.794606ms
2015-03-27 11:52:28 | INFO | MainProcess | Cluster_Thread | [tuq_helper.run_cbq_query] RUN QUERY CREATE INDEX simple_index_1 ON default(job_title != "Sales") USING GSI WITH

{'nodes': ['127.0.0.1:9000']}

2015-03-27 11:52:28 | INFO | MainProcess | Cluster_Thread | [rest_client.query_tool] query params : statement=CREATE+INDEX+simple_index_1+ON+default%28job_title+%21%3D+%22Sales%22%29+USING+GSI+WITH%7B%27nodes%27%3A+%5B%27127.0.0.1%3A9000%27%5D%7D
2015-03-27 11:52:28 | INFO | MainProcess | Cluster_Thread | [tuq_helper.run_cbq_query] TOTAL ELAPSED TIME: 154.665177ms
2015-03-27 11:52:28 | INFO | MainProcess | Cluster_Thread | [tuq_helper.run_cbq_query] RUN QUERY SELECT * FROM system:indexes
2015-03-27 11:52:28 | INFO | MainProcess | Cluster_Thread | [rest_client.query_tool] query params : statement=SELECT+%2A+FROM+system%3Aindexes
2015-03-27 11:52:28 | INFO | MainProcess | Cluster_Thread | [tuq_helper.run_cbq_query] TOTAL ELAPSED TIME: 130.342061ms
2015-03-27 11:52:38 | INFO | MainProcess | Cluster_Thread | [tuq_helper.run_cbq_query] RUN QUERY SELECT * FROM system:indexes
2015-03-27 11:52:38 | INFO | MainProcess | Cluster_Thread | [rest_client.query_tool] query params : statement=SELECT+%2A+FROM+system%3Aindexes
2015-03-27 11:52:38 | INFO | MainProcess | Cluster_Thread | [tuq_helper.run_cbq_query] TOTAL ELAPSED TIME: 97.059898ms

But trying to use these indexes does not work, we use primary scan. See the explain output

2015-03-27 11:52:38 | INFO | MainProcess | Cluster_Thread | [tuq_helper.run_cbq_query] TOTAL ELAPSED TIME: 97.059898ms
2015-03-27 11:52:39 | INFO | MainProcess | Cluster_Thread | [tuq_helper.run_cbq_query] RUN QUERY EXPLAIN SELECT * FROM default WHERE job_title != "Sales"
2015-03-27 11:52:39 | INFO | MainProcess | Cluster_Thread | [rest_client.query_tool] query params : statement=EXPLAIN+SELECT+%2A+FROM+default+WHERE+job_title%21%3D+%22Sales%22+
2015-03-27 11:52:39 | INFO | MainProcess | Cluster_Thread | [tuq_helper.run_cbq_query] TOTAL ELAPSED TIME: 8.573976ms
2015-03-27 11:52:39 | INFO | MainProcess | Cluster_Thread | [task.execute] {u'status': u'success', u'metrics':

{u'elapsedTime': u'8.573976ms', u'executionTime': u'8.511707ms', u'resultSize': 1475, u'resultCount': 1}

, u'results': [{u'#operator': u'Sequence', u'~children': [

{u'index': u'#primary', u'#operator': u'PrimaryScan', u'namespace': u'default', u'using': u'gsi', u'keyspace': u'default'}

, {u'#operator': u'Parallel', u'~child': {u'#operator': u'Sequence', u'~children': [

{u'keyspace': u'default', u'#operator': u'Fetch', u'namespace': u'default'}

,

{u'#operator': u'Filter', u'condition': u'(not ((`default`.`job_title`) = "Sales"))'}

, {u'#operator': u'InitialProject', u'result_terms': [

{u'star': True}

]},

{u'#operator': u'FinalProject'}

]}}]}], u'requestID': u'0396ad1f-5dc8-47f3-a06f-7d3d2ff9ce4b', u'signature': u'json'}
2015-03-27 11:52:39 | INFO | MainProcess | Cluster_Thread | [tuq_helper.run_cbq_query] RUN QUERY EXPLAIN SELECT * FROM default WHERE job_title == "Sales"
2015-03-27 11:52:39 | INFO | MainProcess | Cluster_Thread | [rest_client.query_tool] query params : statement=EXPLAIN+SELECT+%2A+FROM+default+WHERE++job_title%3D%3D+%22Sales%22++
2015-03-27 11:52:39 | INFO | MainProcess | Cluster_Thread | [tuq_helper.run_cbq_query] TOTAL ELAPSED TIME: 8.697618ms
2015-03-27 11:52:39 | INFO | MainProcess | Cluster_Thread | [task.execute] {u'status': u'success', u'metrics':

{u'elapsedTime': u'8.697618ms', u'executionTime': u'8.65581ms', u'resultSize': 1469, u'resultCount': 1}

, u'results': [{u'#operator': u'Sequence', u'~children': [

{u'index': u'#primary', u'#operator': u'PrimaryScan', u'namespace': u'default', u'using': u'gsi', u'keyspace': u'default'}

, {u'#operator': u'Parallel', u'~child': {u'#operator': u'Sequence', u'~children': [

{u'keyspace': u'default', u'#operator': u'Fetch', u'namespace': u'default'}

,

{u'#operator': u'Filter', u'condition': u'((`default`.`job_title`) = "Sales")'}

, {u'#operator': u'InitialProject', u'result_terms': [

{u'star': True}

]},

{u'#operator': u'FinalProject'}

]}}]}], u'requestID': u'eea26efa-9d85-47b6-a18b-451cf2a49420', u'signature': u'json'}
2015-03-27 11:52:39 | INFO | MainProcess | Cluster_Thread | [tuq_helper.run_cbq_query] RUN QUERY EXPLAIN SELECT * FROM default WHERE join_yr > 1999
2015-03-27 11:52:39 | INFO | MainProcess | Cluster_Thread | [rest_client.query_tool] query params : statement=EXPLAIN+SELECT+%2A+FROM+default+WHERE+join_yr%3E+1999+
2015-03-27 11:52:39 | INFO | MainProcess | Cluster_Thread | [tuq_helper.run_cbq_query] TOTAL ELAPSED TIME: 10.394511ms
2015-03-27 11:52:39 | INFO | MainProcess | Cluster_Thread | [task.execute] {u'status': u'success', u'metrics':

{u'elapsedTime': u'10.394511ms', u'executionTime': u'10.333343ms', u'resultSize': 1467, u'resultCount': 1}

, u'results': [{u'#operator': u'Sequence', u'~children': [

{u'index': u'#primary', u'#operator': u'PrimaryScan', u'namespace': u'default', u'using': u'gsi', u'keyspace': u'default'}

, {u'#operator': u'Parallel', u'~child': {u'#operator': u'Sequence', u'~children': [

{u'keyspace': u'default', u'#operator': u'Fetch', u'namespace': u'default'}

,

{u'#operator': u'Filter', u'condition': u'(1999 < (`default`.`join_yr`))'}

, {u'#operator': u'InitialProject', u'result_terms': [

{u'star': True}

]},

{u'#operator': u'FinalProject'}

]}}]}], u'requestID': u'febbcb85-482a-486e-886c-ecc80a410b42', u'signature': u'json'}
2015-03-27 11:52:39 | INFO | MainProcess | Cluster_Thread | [tuq_helper.run_cbq_query] RUN QUERY EXPLAIN SELECT * FROM default WHERE join_yr < 2014
2015-03-27 11:52:39 | INFO | MainProcess | Cluster_Thread | [rest_client.query_tool] query params : statement=EXPLAIN+SELECT+%2A+FROM+default+WHERE+join_yr%3C+2014+
2015-03-27 11:52:39 | INFO | MainProcess | Cluster_Thread | [tuq_helper.run_cbq_query] TOTAL ELAPSED TIME: 9.089696ms
2015-03-27 11:52:39 | INFO | MainProcess | Cluster_Thread | [task.execute] {u'status': u'success', u'metrics':

{u'elapsedTime': u'9.089696ms', u'executionTime': u'9.041198ms', u'resultSize': 1467, u'resultCount': 1}

, u'results': [{u'#operator': u'Sequence', u'~children': [

{u'index': u'#primary', u'#operator': u'PrimaryScan', u'namespace': u'default', u'using': u'gsi', u'keyspace': u'default'}

, {u'#operator': u'Parallel', u'~child': {u'#operator': u'Sequence', u'~children': [

{u'keyspace': u'default', u'#operator': u'Fetch', u'namespace': u'default'}

,

{u'#operator': u'Filter', u'condition': u'((`default`.`join_yr`) < 2014)'}

, {u'#operator': u'InitialProject', u'result_terms': [

{u'star': True}

]},

{u'#operator': u'FinalProject'}

]}}]}], u'requestID': u'c2d45d93-e60a-47d8-9f5e-1dbdd91eab9c', u'signature': u'json'}