Aggregation request returns incorrect results

[Nick-S-2018]

Bug Description:

The following SQL agg request cannot be correctly reproduced with the Go client

CREATE TABLE IF NOT EXISTS apps (
    id bigint,
    typeID int,
    title string attribute indexed
);

INSERT INTO apps (ID, TypeID, Title) VALUES (1,1,'First'), (2,1,'Second'), (3,1,'Third');

CREATE TABLE IF NOT EXISTS prices (
    id bigint,
    appID bigint,
    offer bigint,
    discountPercentage int
);

INSERT INTO prices (id, appID, offer, discountPercentage) VALUES
(1, 1, 10,20),
(2, 1, 12,15),
(3, 1, 9,22),
(4, 2, 20,10),
(5, 2, 19,12),
(6, 2, 21,0),
(7, 3, 30,0),
(8, 3, 25,20),
(9, 3, 32,0);

MySQL [(none)]> select * from prices;
+------+-------+-------+--------------------+
| id   | appid | offer | discountpercentage |
+------+-------+-------+--------------------+
|    1 |     1 |    10 |                 20 |
|    2 |     1 |    12 |                 15 |
|    3 |     1 |     9 |                 22 |
|    4 |     2 |    20 |                 10 |
|    5 |     2 |    19 |                 12 |
|    6 |     2 |    21 |                  0 |
|    7 |     3 |    30 |                  0 |
|    8 |     3 |    25 |                 20 |
|    9 |     3 |    32 |                  0 |
+------+-------+-------+--------------------+
9 rows in set (0.000 sec)

MySQL [(none)]> SELECT id, title, prices.offer, prices.discountpercentage FROM apps JOIN prices ON prices.appid = apps.id GROUP 1 BY id WITHIN GROUP ORDER BY prices.offer ASC LIMIT 10;
+------+--------+--------------+---------------------------+
| id   | title  | prices.offer | prices.discountpercentage |
+------+--------+--------------+---------------------------+
|    1 | First  |            9 |                        22 |
|    2 | Second |           19 |                        12 |
|    3 | Third  |           25 |                        20 |
+------+--------+--------------+---------------------------+
3 rows in set (0.000 sec)

sr := manticore.SearchRequest{
    Query: &manticore.SearchQuery{
        Bool: &manticore.BoolFilter{
            MustNot: []*manticore.QueryFilter{
                {
                    Equals: map[string]any{
                        "prices.offer": 0.0, // WHERE prices.offer > 0
                    },
                },
            },
        },
    },
    Table: "apps",
    Join: []manticore.Join{
        {
            Table: "prices",
            Type:  "inner",
            On: []manticore.JoinOn{ // JOIN prices ON prices.appid = apps.id
                {
                    Left: &manticore.JoinCond{
                        Field: "id",
                        Table: "apps",
                    },
                    Right: &manticore.JoinCond{
                        Field: "appid",
                        Table: "prices",
                    },
                    Operator: manticore.PtrString("eq"),
                },
            },
        },
    },
    Aggs: map[string]manticore.Aggregation{
        "group_property": {
            Sort: []interface{}{
                map[string]string{
                    "prices.offer": "asc", // I assume this is WITHIN GROUP ORDER BY
                },
            },
            Terms: &manticore.AggTerms{
                Field: "id",
                Size:  manticore.PtrInt32(100), // SIZE 100
            },
        },
    },
    Sort:   map[string]string{"prices.offer": "asc"},
    Source: []string{"id", "title", "prices.offer", "prices.discountpercentage"}, // Expect to see fields.
    Limit:  manticore.PtrInt32(0), // LIMIT 0
}

res, _, err := cl.SearchAPI.Search(context.Background()).SearchRequest(sr).Execute()
if err != nil {
    log.Fatalln("failed to search: ", err)
}

for prop, inf := range res.GetAggregations() {
    fmt.Println(prop)
    fmt.Println(inf)
}

// OUTPUT:
// group_property
// map[buckets:[map[doc_count:3 key:1] map[doc_count:3 key:2] map[doc_count:3 key:3]]]

Manticore Search Version:

Manticore 10.1.0 c10e1563b@25060414 dev

Client Version:

Ubuntu 22.04

Have you tried the latest development version of Manticore Search and the client?

Yes

Internal Checklist:

^{To be completed by the assignee. Check off tasks that have been completed or are not applicable.}

Details

• Implementation completed
• Tests developed
• Documentation updated
• Documentation reviewed

[Nick-S-2018]

Blocked by manticoresoftware/manticoresearch#3619