Incorrect Join Vars in Gen4 plan

[GuptaManan100]

Overview of the Issue

The following query produces incorrect result in Gen4 but works correctly in v3.

select author5s.* from author5s join book6s on book6s.author5_id = author5s.id join book6s_order2s on book6s_order2s.book6_id = book6s.id join order2s on order2s.id = book6s_order2s.order2_id join customer2s on customer2s.id = order2s.customer2_id join supplier5s on supplier5s.id = book6s.supplier5_id

The Vschema associated with these tables is :-

{
  "keyspaces": {
    "user": {
      "sharded": true,
      "vindexes": {
        "binary_md5": {
          "type": "hash_test"
        }
      },
      "tables": {
        "order2s": {
          "column_vindexes": [
            {
              "columns": [
                "customer2_id"
              ],
              "name": "binary_md5"
            }
          ],
          "auto_increment": {
            "column": "id",
            "sequence": "order2s_seq"
          },
          "columns": [
            {
              "name": "id",
              "type": "INT64"
            },
            {
              "name": "customer2_id",
              "type": "INT64"
            },
            {
              "name": "status",
              "type": "INT32"
            },
            {
              "name": "created_at",
              "type": "DATETIME"
            },
            {
              "name": "updated_at",
              "type": "DATETIME"
            }
          ],
          "column_list_authoritative": true
        },
        "book6s": {
          "column_vindexes": [
            {
              "columns": [
                "author5_id"
              ],
              "name": "binary_md5"
            }
          ],
          "auto_increment": {
            "column": "id",
            "sequence": "book6s_seq"
          },
          "columns": [
            {
              "name": "id",
              "type": "INT64"
            },
            {
              "name": "author5_id",
              "type": "INT64"
            },
            {
              "name": "supplier5_id",
              "type": "INT64"
            },
            {
              "name": "title",
              "type": "VARCHAR"
            },
            {
              "name": "price",
              "type": "INT32"
            },
            {
              "name": "year_published",
              "type": "INT32"
            },
            {
              "name": "out_of_print",
              "type": "INT8"
            },
            {
              "name": "created_at",
              "type": "DATETIME"
            },
            {
              "name": "updated_at",
              "type": "DATETIME"
            }
          ],
          "column_list_authoritative": true
        },
        "book6s_order2s": {
          "column_vindexes": [
            {
              "columns": [
                "book6_id"
              ],
              "name": "binary_md5"
            }
          ],
          "columns": [
            {
              "name": "book6_id",
              "type": "INT64"
            },
            {
              "name": "order2_id",
              "type": "INT64"
            }
          ],
          "column_list_authoritative": true
        },
        "customer2s": {
          "column_vindexes": [
            {
              "columns": [
                "id"
              ],
              "name": "binary_md5"
            }
          ],
          "auto_increment": {
            "column": "id",
            "sequence": "customer2s_seq"
          },
          "columns": [
            {
              "name": "id",
              "type": "INT64"
            },
            {
              "name": "first_name",
              "type": "VARCHAR"
            },
            {
              "name": "orders_count",
              "type": "INT32"
            },
            {
              "name": "lock_version",
              "type": "INT32"
            },
            {
              "name": "created_at",
              "type": "DATETIME"
            },
            {
              "name": "updated_at",
              "type": "DATETIME"
            }
          ],
          "column_list_authoritative": true
        },
        "author5s": {
          "column_vindexes": [
            {
              "columns": [
                "id"
              ],
              "name": "binary_md5"
            }
          ],
          "auto_increment": {
            "column": "id",
            "sequence": "author5s_seq"
          },
          "columns": [
            {
              "name": "id",
              "type": "INT64"
            },
            {
              "name": "name",
              "type": "VARCHAR"
            },
            {
              "name": "created_at",
              "type": "DATETIME"
            },
            {
              "name": "updated_at",
              "type": "DATETIME"
            }
          ],
          "column_list_authoritative": true
        },
        "supplier5s": {
          "column_vindexes": [
            {
              "columns": [
                "id"
              ],
              "name": "binary_md5"
            }
          ],
          "auto_increment": {
            "column": "id",
            "sequence": "supplier5s_seq"
          },
          "columns": [
            {
              "name": "id",
              "type": "INT64"
            },
            {
              "name": "state",
              "type": "VARCHAR"
            },
            {
              "name": "created_at",
              "type": "DATETIME"
            },
            {
              "name": "updated_at",
              "type": "DATETIME"
            }
          ],
          "column_list_authoritative": true
        }
      }
    },
    "main": {
      "tables": {
        "book6s_seq": {
          "type": "sequence",
          "columns": [
            {
              "name": "id",
              "type": "INT64"
            },
            {
              "name": "next_id",
              "type": "INT64"
            },
            {
              "name": "cache",
              "type": "INT64"
            }
          ],
          "column_list_authoritative": true
        },
        "author5s_seq": {
          "type": "sequence",
          "columns": [
            {
              "name": "id",
              "type": "INT64"
            },
            {
              "name": "next_id",
              "type": "INT64"
            },
            {
              "name": "cache",
              "type": "INT64"
            }
          ],
          "column_list_authoritative": true
        },
        "supplier5s_seq": {
          "type": "sequence",
          "columns": [
            {
              "name": "id",
              "type": "INT64"
            },
            {
              "name": "next_id",
              "type": "INT64"
            },
            {
              "name": "cache",
              "type": "INT64"
            }
          ],
          "column_list_authoritative": true
        },
        "customer2s_seq": {
          "type": "sequence",
          "columns": [
            {
              "name": "id",
              "type": "INT64"
            },
            {
              "name": "next_id",
              "type": "INT64"
            },
            {
              "name": "cache",
              "type": "INT64"
            }
          ],
          "column_list_authoritative": true
        },
        "order2s_seq": {
          "type": "sequence",
          "columns": [
            {
              "name": "id",
              "type": "INT64"
            },
            {
              "name": "next_id",
              "type": "INT64"
            },
            {
              "name": "cache",
              "type": "INT64"
            }
          ],
          "column_list_authoritative": true
        }
      }
    }
  }
}

The produced plan from Gen4 is

{
  "QueryType": "SELECT",
  "Original": "select author5s.* from author5s join book6s on book6s.author5_id = author5s.id join book6s_order2s on book6s_order2s.book6_id = book6s.id join order2s on order2s.id = book6s_order2s.order2_id join customer2s on customer2s.id = order2s.customer2_id join supplier5s on supplier5s.id = book6s.supplier5_id",
  "Instructions": {
    "OperatorType": "Join",
    "Variant": "Join",
    "JoinColumnIndexes": "1,2,3,4",
    "JoinVars": {
      "order2s_id": 0
    },
    "TableName": "customer2s, order2s_author5s, book6s_book6s_order2s_supplier5s",
    "Inputs": [
      {
        "OperatorType": "Route",
        "Variant": "SelectScatter",
        "Keyspace": {
          "Name": "user",
          "Sharded": true
        },
        "FieldQuery": "select order2s.id from order2s, customer2s where 1 != 1",
        "Query": "select order2s.id from order2s, customer2s where customer2s.id = order2s.customer2_id",
        "Table": "customer2s, order2s"
      },
      {
        "OperatorType": "Join",
        "Variant": "Join",
        "JoinColumnIndexes": "-2,-3,-4,-5",
        "JoinVars": {
          "book6s_order2s_order2_id": 0,
          "book6s_supplier5_id": 0
        },
        "TableName": "author5s, book6s_book6s_order2s_supplier5s",
        "Inputs": [
          {
            "OperatorType": "Join",
            "Variant": "Join",
            "JoinColumnIndexes": "1,-3,-4,-5,-6",
            "JoinVars": {
              "book6s_id": 0,
              "book6s_supplier5_id": 0
            },
            "TableName": "author5s, book6s_book6s_order2s",
            "Inputs": [
              {
                "OperatorType": "Route",
                "Variant": "SelectScatter",
                "Keyspace": {
                  "Name": "user",
                  "Sharded": true
                },
                "FieldQuery": "select book6s.id, book6s.supplier5_id, author5s.id as id, author5s.`name` as `name`, author5s.created_at as created_at, author5s.updated_at as updated_at from author5s, book6s where 1 != 1",
                "Query": "select book6s.id, book6s.supplier5_id, author5s.id as id, author5s.`name` as `name`, author5s.created_at as created_at, author5s.updated_at as updated_at from author5s, book6s where book6s.author5_id = author5s.id",
                "Table": "author5s, book6s"
              },
              {
                "OperatorType": "Route",
                "Variant": "SelectEqualUnique",
                "Keyspace": {
                  "Name": "user",
                  "Sharded": true
                },
                "FieldQuery": "select book6s_order2s.order2_id from book6s_order2s where 1 != 1",
                "Query": "select book6s_order2s.order2_id from book6s_order2s where book6s_order2s.book6_id = :book6s_id",
                "Table": "book6s_order2s",
                "Values": [
                  ":book6s_id"
                ],
                "Vindex": "binary_md5"
              }
            ]
          },
          {
            "OperatorType": "Route",
            "Variant": "SelectEqualUnique",
            "Keyspace": {
              "Name": "user",
              "Sharded": true
            },
            "FieldQuery": "select 1 from supplier5s where 1 != 1",
            "Query": "select 1 from supplier5s where supplier5s.id = :book6s_supplier5_id and :order2s_id = :book6s_order2s_order2_id",
            "Table": "supplier5s",
            "Values": [
              ":book6s_supplier5_id"
            ],
            "Vindex": "binary_md5"
          }
        ]
      }
    ]
  }
}

The result should contain 220 rows but only contains 20. On further exploration, the problem was found with the JoinVars in the second join. The join var book6s_supplier5_id and book6s_order2s_order2_id are both set to 0. Also the join var book6s_supplier5_id occurs twice which seems wrong.

Reproduction Steps

Steps to reproduce this issue, example:

1. Copy the query in onecase.txt and the vschema into schema_test.json
2. Run TestOneCase

Binary version

• main