RQG: With secondary indexing Having clause with group by returning inconsistent result as compared to MySQL

./testrunner -i b/resources/dev-6-nodes-xdcr_n1ql_2i.ini -t rqg.test_rqg.RQGTests.test_rqg_concurrent_with_predefined_input,database=multiple_table_db,reset_database=True,concurreny_count=1,index_quota_percent=40,use_mysql=False,replicas=0,input_rqg_path=/tmp/data_dump,populate_with_replay=True,reset_database=True,create_secondary_indexes=True,run_query_with_primary=True

Scenario :: run_query_without_index_hint
Reason :: Results are incorrect.Actual num 251. Expected num: 484.:: mismatch in results :: expected :: [

{u'int_field1': 90}

,

{u'int_field1': 123}

,

{u'int_field1': 143}

,

{u'int_field1': 148}

,

{u'int_field1': 181}

], actual :: [

{u'int_field1': 90}

,

{u'int_field1': 123}

,

{u'int_field1': 143}

,

{u'int_field1': 181}

,

{u'int_field1': 271}

]

Explain

{
"requestID": "5909e511-f823-45a6-b763-b69bdb9945bc",
"signature": "json",
"results": [
{
"#operator": "Sequence",
"~children": [
{
"#operator": "UnionScan",
"scans": [
{
"#operator": "IndexScan",
"index": "simple_table_idx_char_field1_bool_field1_datetime_field1",
"keyspace": "simple_table",
"limit": 9.223372036854776e+18,
"namespace": "default",
"spans": [
{
"Range":

{ "High": [ "\"N\"" ], "Inclusion": 0, "Low": null }

,
"Seek": null
},
{
"Range":

{ "High": null, "Inclusion": 0, "Low": [ "\"N\"", "true" ] }

,
"Seek": null
}
],
"using": "gsi"
}
]
},
{
"#operator": "Parallel",
"~child": {
"#operator": "Sequence",
"~children": [

{ "#operator": "Fetch", "keyspace": "simple_table", "namespace": "default" }

,

{ "#operator": "Filter", "condition": "(not ((not (`simple_table`.`bool_field1`)) or ((`simple_table`.`char_field1`) = \"N\")))" }

,

{ "#operator": "InitialGroup", "aggregates": [], "group_keys": [ "(`simple_table`.`int_field1`)" ] }

]
}
},

{ "#operator": "IntermediateGroup", "aggregates": [], "group_keys": [ "(`simple_table`.`int_field1`)" ] }

,

{ "#operator": "FinalGroup", "aggregates": [], "group_keys": [ "(`simple_table`.`int_field1`)" ] }

,
{
"#operator": "Parallel",
"~child": {
"#operator": "Sequence",
"~children": [

{ "#operator": "Filter", "condition": "(not (((`simple_table`.`int_field1`) in [16, 23, 25, 39, 53]) or ((`simple_table`.`int_field1`) is null)))" }

,
{
"#operator": "InitialProject",
"result_terms": [

{ "expr": "(`simple_table`.`int_field1`)" }

]
},

{ "#operator": "FinalProject" }

]
}
}
]
}
],
"status": "success",
"metrics":

{ "elapsedTime": "62.067091ms", "executionTime": "61.974507ms", "resultCount": 1, "resultSize": 4209 }

}

cbq>

This works fine with primary index

cbq> SELECT int_field1 FROM simple_table USE INDEX(`#primary` USING GSI) WHERE NOT ((NOT (bool_field1) OR char_field1 = "N")) GROUP BY int_field1 ;
{
"requestID": "8ade4651-e5f7-4e1b-9482-04f50a01a3f5",
"signature":

{ "int_field1": "json" }

,
"results": [

{ "int_field1": 53 }

,

{ "int_field1": 5592 }

,

{ "int_field1": 855 }

,

{ "int_field1": 7179 }

,

{ "int_field1": 1115 }

,

{ "int_field1": 1084 }

,

{ "int_field1": 7011 }

,

{ "int_field1": 8239 }

,

{ "int_field1": 908 }

,

{ "int_field1": 293 }

,

{ "int_field1": 3804 }

,

{ "int_field1": 704 }

,

{ "int_field1": 2954 }

,

{ "int_field1": 2926 }

,

{ "int_field1": 4665 }

,

{ "int_field1": 7727 }

,

{ "int_field1": 1791 }

,

{ "int_field1": 7437 }

,

{ "int_field1": 9462 }

,

{ "int_field1": 9591 }

,

{ "int_field1": 4102 }

,

{ "int_field1": 8895 }

,

{ "int_field1": 5071 }

,

{ "int_field1": 5521 }

,

{ "int_field1": 181 }

,

{ "int_field1": 7996 }

,

{ "int_field1": 4382 }

,

{ "int_field1": 3825 }

,

{ "int_field1": 6897 }

,

{ "int_field1": 2968 }

,

{ "int_field1": 1234 }

,

{ "int_field1": 3170 }

,

{ "int_field1": 2765 }

,

{ "int_field1": 2323 }

,

{ "int_field1": 9634 }

,

{ "int_field1": 1354 }

,

{ "int_field1": 6266 }

,

{ "int_field1": 9761 }

,

{ "int_field1": 4046 }

,

{ "int_field1": 3308 }

,

{ "int_field1": 5198 }

,

{ "int_field1": 7878 }

,

{ "int_field1": 394 }

,

{ "int_field1": 1939 }

,

{ "int_field1": 9174 }

,

{ "int_field1": 9191 }

,

{ "int_field1": 7946 }

,

{ "int_field1": 8712 }

,

{ "int_field1": 4590 }

,

{ "int_field1": 5439 }

,

{ "int_field1": 6733 }

,

{ "int_field1": 5217 }

,

{ "int_field1": 7451 }

,

{ "int_field1": 4121 }

,

{ "int_field1": 7389 }

,

{ "int_field1": 6659 }

,

{ "int_field1": 476 }

,

{ "int_field1": 5789 }

,

{ "int_field1": 4132 }

,

{ "int_field1": 3279 }

,

{ "int_field1": 7639 }

,

{ "int_field1": 8717 }

,

{ "int_field1": 1746 }

,

{ "int_field1": 9005 }

,

{ "int_field1": 6016 }

,

{ "int_field1": 734 }

,

{ "int_field1": 5161 }

,

{ "int_field1": 9842 }

,

{ "int_field1": 8662 }

,

{ "int_field1": 5055 }

,

{ "int_field1": 5927 }

,

{ "int_field1": 2748 }

,

{ "int_field1": 8067 }

,

{ "int_field1": 6041 }

,

{ "int_field1": 8519 }

,

{ "int_field1": 2872 }

,

{ "int_field1": 862 }

,

{ "int_field1": 7948 }

,

{ "int_field1": 3796 }

,

{ "int_field1": 3712 }

,

{ "int_field1": 1264 }

,

{ "int_field1": 9883 }

,

{ "int_field1": 425 }

,

{ "int_field1": 4735 }

,

{ "int_field1": 5376 }

,

{ "int_field1": 4243 }

,

{ "int_field1": 7529 }

,

{ "int_field1": 9512 }

,

{ "int_field1": 3593 }

,

{ "int_field1": 9421 }

,

{ "int_field1": 2419 }

,

{ "int_field1": 5762 }

,

{ "int_field1": 945 }

,

{ "int_field1": 3951 }

,

{ "int_field1": 9207 }

,

{ "int_field1": 8674 }

,

{ "int_field1": 8489 }

,

{ "int_field1": 8194 }

,

{ "int_field1": 8619 }

,

{ "int_field1": 6370 }

,

{ "int_field1": 4238 }

,

{ "int_field1": 8176 }

,

{ "int_field1": 6576 }

,

{ "int_field1": 9053 }

,

{ "int_field1": 1305 }

,

{ "int_field1": 9535 }

,

{ "int_field1": 3950 }

,

{ "int_field1": 451 }

,

{ "int_field1": 7917 }

,

{ "int_field1": 386 }

,

{ "int_field1": 6005 }

,

{ "int_field1": 8280 }

,

{ "int_field1": 8039 }

,

{ "int_field1": 9305 }

,

{ "int_field1": 7742 }

,

{ "int_field1": 669 }

,

{ "int_field1": 7212 }

,

{ "int_field1": 7325 }

,

{ "int_field1": 2522 }

,

{ "int_field1": 7519 }

,

{ "int_field1": 2639 }

,

{ "int_field1": 3525 }

,

{ "int_field1": 7120 }

,

{ "int_field1": 6008 }

,

{ "int_field1": 7004 }

,

{ "int_field1": 5396 }

,

{ "int_field1": 9472 }

,

{ "int_field1": 7764 }

,

{ "int_field1": 4345 }

,

{ "int_field1": 6050 }

,

{ "int_field1": 3165 }

,

{ "int_field1": 3270 }

,

{ "int_field1": 5636 }

,

{ "int_field1": 7852 }

,

{ "int_field1": 5646 }

,

{ "int_field1": 7769 }

,

{ "int_field1": 2988 }

,

{ "int_field1": 7105 }

,

{ "int_field1": 8183 }

,

{ "int_field1": 7421 }

,

{ "int_field1": 3365 }

,

{ "int_field1": 8821 }

,

{ "int_field1": 333 }

,

{ "int_field1": 4872 }

,

{ "int_field1": 6584 }

,

{ "int_field1": 2867 }

,

{ "int_field1": 3482 }

,

{ "int_field1": 2941 }

,

{ "int_field1": 3210 }

,

{ "int_field1": 3312 }

,

{ "int_field1": 6835 }

,

{ "int_field1": 5723 }

,

{ "int_field1": 4113 }

,

{ "int_field1": 8838 }

,

{ "int_field1": 6314 }

,

{ "int_field1": 5462 }

,

{ "int_field1": 2983 }

,

{ "int_field1": 4144 }

,

{ "int_field1": 3866 }

,

{ "int_field1": 9363 }

,

{ "int_field1": 7847 }

,

{ "int_field1": 7628 }

,

{ "int_field1": 1699 }

,

{ "int_field1": 6514 }

,

{ "int_field1": 2732 }

,

{ "int_field1": 1801 }

,

{ "int_field1": 7629 }

,

{ "int_field1": 8666 }

,

{ "int_field1": 8409 }

,

{ "int_field1": 1440 }

,

{ "int_field1": 7027 }

,

{ "int_field1": 7796 }

,

{ "int_field1": 25 }

,

{ "int_field1": 143 }

,

{ "int_field1": 6694 }

,

{ "int_field1": 3435 }

,

{ "int_field1": 318 }

,

{ "int_field1": 755 }

,

{ "int_field1": 4952 }

,

{ "int_field1": 7073 }

,

{ "int_field1": 5086 }

,

{ "int_field1": 1932 }

,

{ "int_field1": 1653 }

,

{ "int_field1": 7100 }

,

{ "int_field1": 4602 }

,

{ "int_field1": 986 }

,

{ "int_field1": 3636 }

,

{ "int_field1": 7395 }

,

{ "int_field1": 8031 }

,

{ "int_field1": 3461 }

,

{ "int_field1": 6523 }

,

{ "int_field1": 1919 }

,

{ "int_field1": 8696 }

,

{ "int_field1": 148 }

,

{ "int_field1": 9095 }

,

{ "int_field1": 7492 }

,

{ "int_field1": 578 }

,

{ "int_field1": 8634 }

,

{ "int_field1": 6277 }

,

{ "int_field1": 7596 }

,

{ "int_field1": 4563 }

,

{ "int_field1": 2637 }

,

{ "int_field1": 8720 }

,

{ "int_field1": 8598 }

,

{ "int_field1": 7175 }

,

{ "int_field1": 6911 }

,

{ "int_field1": 5856 }

,

{ "int_field1": 5053 }

,

{ "int_field1": 1209 }

,

{ "int_field1": 1402 }

,

{ "int_field1": 6673 }

,

{ "int_field1": 9028 }

,

{ "int_field1": 1010 }

,

{ "int_field1": 236 }

,

{ "int_field1": 6570 }

,

{ "int_field1": 659 }

,

{ "int_field1": 3070 }

,

{ "int_field1": 9558 }

,

{ "int_field1": 3146 }

,

{ "int_field1": 6359 }

,

{ "int_field1": 8830 }

,

{ "int_field1": 8228 }

,

{ "int_field1": 8086 }

,

{ "int_field1": 7367 }

,

{ "int_field1": 866 }

,

{ "int_field1": 7788 }

,

{ "int_field1": 3497 }

,

{ "int_field1": 1595 }

,

{ "int_field1": 2135 }

,

{ "int_field1": 3784 }

,

{ "int_field1": 5081 }

,

{ "int_field1": 5669 }

,

{ "int_field1": 2776 }

,

{ "int_field1": 5154 }

,

{ "int_field1": 9312 }

,

{ "int_field1": 5152 }

,

{ "int_field1": 3491 }

,

{ "int_field1": 2745 }

,

{ "int_field1": 3800 }

,

{ "int_field1": 4491 }

,

{ "int_field1": 9955 }

,

{ "int_field1": 2175 }

,

{ "int_field1": 7511 }

,

{ "int_field1": 3375 }

,

{ "int_field1": 9198 }

,

{ "int_field1": 9567 }

,

{ "int_field1": 5607 }

,

{ "int_field1": 9102 }

,

{ "int_field1": 6818 }

,

{ "int_field1": 9885 }

,

{ "int_field1": 5630 }

,

{ "int_field1": 9178 }

,

{ "int_field1": 9706 }

,

{ "int_field1": 2073 }

,

{ "int_field1": 7575 }

,

{ "int_field1": 1370 }

,

{ "int_field1": 680 }

,

{ "int_field1": 9731 }

,

{ "int_field1": 6943 }

,

{ "int_field1": 6550 }

,

{ "int_field1": 419 }

,

{ "int_field1": 2375 }

,

{ "int_field1": 1680 }

,

{ "int_field1": 635 }

,

{ "int_field1": 829 }

,

{ "int_field1": 2513 }

,

{ "int_field1": 3648 }

,

{ "int_field1": 575 }

,

{ "int_field1": 6668 }

,

{ "int_field1": 406 }

,

{ "int_field1": 478 }

,

{ "int_field1": 5598 }

,

{ "int_field1": 3828 }

,

{ "int_field1": 1807 }

,

{ "int_field1": 2576 }

,

{ "int_field1": 7107 }

,

{ "int_field1": 7594 }

,

{ "int_field1": 4371 }

,

{ "int_field1": 3397 }

,

{ "int_field1": 3443 }

,

{ "int_field1": 2104 }

,

{ "int_field1": 9732 }

,

{ "int_field1": 1135 }

,

{ "int_field1": 9969 }

,

{ "int_field1": 1633 }

,

{ "int_field1": 5692 }

,

{ "int_field1": 4479 }

,

{ "int_field1": 4100 }

,

{ "int_field1": 1915 }

,

{ "int_field1": 2336 }

,

{ "int_field1": 348 }

,

{ "int_field1": 4616 }

,

{ "int_field1": 2613 }

,

{ "int_field1": 9138 }

,

{ "int_field1": 4030 }

,

{ "int_field1": 8508 }

,

{ "int_field1": 8846 }

,

{ "int_field1": 5320 }

,

{ "int_field1": 4917 }

,

{ "int_field1": 6143 }

,

{ "int_field1": 4084 }

,

{ "int_field1": 271 }

,

{ "int_field1": 3205 }

,

{ "int_field1": 5437 }

,

{ "int_field1": 5069 }

,

{ "int_field1": 6923 }

,

{ "int_field1": 5155 }

,

{ "int_field1": 4004 }

,

{ "int_field1": 2518 }

,

{ "int_field1": 3765 }

,

{ "int_field1": 6551 }

,

{ "int_field1": 123 }

,

{ "int_field1": 1968 }

,

{ "int_field1": 8315 }

,

{ "int_field1": 1663 }

,

{ "int_field1": 6027 }

,

{ "int_field1": 7983 }

,

{ "int_field1": 2378 }

,

{ "int_field1": 8153 }

,

{ "int_field1": 1003 }

,

{ "int_field1": 8959 }

,

{ "int_field1": 4175 }

,

{ "int_field1": 748 }

,

{ "int_field1": 5211 }

,

{ "int_field1": 90 }

,

{ "int_field1": 1978 }

,

{ "int_field1": 1509 }

,

{ "int_field1": 6964 }

,

{ "int_field1": 7540 }

,

{ "int_field1": 9441 }

,

{ "int_field1": 7425 }

,

{ "int_field1": 2261 }

,

{ "int_field1": 1158 }

,

{ "int_field1": 5680 }

,

{ "int_field1": 4456 }

,

{ "int_field1": 6941 }

,

{ "int_field1": 6378 }

,

{ "int_field1": 823 }

,

{ "int_field1": 4652 }

,

{ "int_field1": 9271 }

,

{ "int_field1": 2012 }

,

{ "int_field1": 7393 }

,

{ "int_field1": 274 }

,

{ "int_field1": 8154 }

,

{ "int_field1": 1380 }

,

{ "int_field1": 8307 }

,

{ "int_field1": 7941 }

,

{ "int_field1": 6797 }

,

{ "int_field1": 6167 }

,

{ "int_field1": 2574 }

,

{ "int_field1": 4586 }

,

{ "int_field1": 8341 }

,

{ "int_field1": 9853 }

,

{ "int_field1": 3643 }

,

{ "int_field1": 332 }

,

{ "int_field1": 4877 }

,

{ "int_field1": 7166 }

,

{ "int_field1": 8123 }

,

{ "int_field1": 7592 }

,

{ "int_field1": 2353 }

,

{ "int_field1": 9125 }

,

{ "int_field1": 3960 }

,

{ "int_field1": 241 }

,

{ "int_field1": 4628 }

,

{ "int_field1": 7253 }

,

{ "int_field1": 8059 }

,

{ "int_field1": 8639 }

,

{ "int_field1": 5949 }

,

{ "int_field1": 8950 }

,

{ "int_field1": 5876 }

,

{ "int_field1": 4074 }

,

{ "int_field1": 6752 }

,

{ "int_field1": 5729 }

,

{ "int_field1": 9188 }

,

{ "int_field1": 9018 }

,

{ "int_field1": 556 }

,

{ "int_field1": 506 }

,

{ "int_field1": 8864 }

,

{ "int_field1": 6938 }

,

{ "int_field1": 9793 }

,

{ "int_field1": 2594 }

,

{ "int_field1": 4015 }

,

{ "int_field1": 7364 }

,

{ "int_field1": 1230 }

,

{ "int_field1": 8828 }

,

{ "int_field1": 5425 }

,

{ "int_field1": 2288 }

,

{ "int_field1": 1748 }

,

{ "int_field1": 6173 }

,

{ "int_field1": 3078 }

,

{ "int_field1": 8464 }

,

{ "int_field1": 3003 }

,

{ "int_field1": 1180 }

,

{ "int_field1": 9730 }

,

{ "int_field1": 1556 }

,

{ "int_field1": 5528 }

,

{ "int_field1": 6464 }

,

{ "int_field1": 2388 }

,

{ "int_field1": 9824 }

,

{ "int_field1": 9926 }

,

{ "int_field1": 2601 }

,

{ "int_field1": 776 }

,

{ "int_field1": 5707 }

,

{ "int_field1": 3132 }

,

{ "int_field1": 5626 }

,

{ "int_field1": 9848 }

,

{ "int_field1": 1772 }

,

{ "int_field1": 4362 }

,

{ "int_field1": 2990 }

,

{ "int_field1": 1113 }

,

{ "int_field1": 6282 }

,

{ "int_field1": 7670 }

,

{ "int_field1": 3503 }

,

{ "int_field1": 3128 }

,

{ "int_field1": 2925 }

,

{ "int_field1": 6338 }

,

{ "int_field1": 2700 }

,

{ "int_field1": 6559 }

,

{ "int_field1": 2989 }

,

{ "int_field1": 2772 }

,

{ "int_field1": 462 }

,

{ "int_field1": 6703 }

,

{ "int_field1": 8916 }

,

{ "int_field1": 6771 }

,

{ "int_field1": 9984 }

,

{ "int_field1": 9231 }

,

{ "int_field1": 8501 }

,

{ "int_field1": 3922 }

,

{ "int_field1": 4530 }

,

{ "int_field1": 7724 }

,

{ "int_field1": 2281 }

,

{ "int_field1": 8894 }

,

{ "int_field1": 6440 }

,

{ "int_field1": 1480 }

,

{ "int_field1": 1983 }

,

{ "int_field1": 579 }

,

{ "int_field1": 9073 }

,

{ "int_field1": 7533 }

,

{ "int_field1": 7050 }

,

{ "int_field1": 6145 }

,

{ "int_field1": 3779 }

,

{ "int_field1": 7322 }

,

{ "int_field1": 8400 }

,

{ "int_field1": 5934 }

,

{ "int_field1": 3467 }

,

{ "int_field1": 7489 }

,

{ "int_field1": 8707 }

,

{ "int_field1": 3863 }

,

{ "int_field1": 1963 }

,

{ "int_field1": 3933 }

,

{ "int_field1": 5123 }

,

{ "int_field1": 837 }

,

{ "int_field1": 2935 }

,

{ "int_field1": 4769 }

,

{ "int_field1": 8500 }

,

{ "int_field1": 6727 }

,

{ "int_field1": 5790 }

,

{ "int_field1": 4466 }

,

{ "int_field1": 5033 }

,

{ "int_field1": 3656 }

,

{ "int_field1": 4598 }

,

{ "int_field1": 7113 }

,

{ "int_field1": 3808 }

,

{ "int_field1": 5267 }

,

{ "int_field1": 4968 }

,

{ "int_field1": 2134 }

,

{ "int_field1": 5256 }

,

{ "int_field1": 8586 }

,

{ "int_field1": 1393 }

,

{ "int_field1": 1269 }

,

{ "int_field1": 4471 }

,

{ "int_field1": 2087 }

,

{ "int_field1": 4836 }

,

{ "int_field1": 2735 }

,

{ "int_field1": 8967 }

,

{ "int_field1": 8238 }

,

{ "int_field1": 6251 }

,

{ "int_field1": 7829 }

,

{ "int_field1": 6998 }

,

{ "int_field1": 9196 }

,

{ "int_field1": 3724 }

,

{ "int_field1": 3370 }

,

{ "int_field1": 7189 }

,

{ "int_field1": 4203 }

,

{ "int_field1": 4009 }

,

{ "int_field1": 7495 }

],
"status": "success",
"metrics":

{ "elapsedTime": "62.731812ms", "executionTime": "62.622706ms", "resultCount": 486, "resultSize": 20361 }

}

cbq>

sql_query :: SELECT int_field1 FROM simple_table WHERE NOT ((NOT (bool_field1) OR char_field1 = "N")) GROUP BY int_field1 HAVING NOT ((int_field1 IN ( 16 , 23 , 25 , 39 , 53 )) OR (int_field1 IS NULL))

n1ql_query :: SELECT int_field1 FROM simple_table WHERE NOT ((NOT (bool_field1) OR char_field1 = "N")) GROUP BY int_field1 HAVING NOT ((int_field1 IN [ 16 , 23 , 25 , 39 , 53 ]) OR (int_field1 IS NULL))