parser cache and nestTables issue

[confuser]

Using Knex, the following two queries are built

select `role`.`role_id`, `role`.`name`, `bm_web_player_roles`.`player_id` from `bm_web_player_roles` inner join `bm_web_roles` as `role` on `bm_web_player_roles`.`role_id` = `role`.`role_id` where `bm_web_player_roles`.`player_id` in (X'53458e9db5514e73a7b2d3fac8d41d67', X'1e14467b1013458fa3a1af3fce1eb29f', X'd5918acd7ebb43d8ac42b358f64c5309')

select `serverRole`.`role_id`, `serverRole`.`name`, `bm_web_player_server_roles`.`player_id` from `bm_web_player_server_roles` inner join `bm_web_roles` as `serverRole` on `bm_web_player_server_roles`.`role_id` = `serverRole`.`role_id` where `bm_web_player_server_roles`.`player_id` in (X'53458e9db5514e73a7b2d3fac8d41d67', X'1e14467b1013458fa3a1af3fce1eb29f', X'd5918acd7ebb43d8ac42b358f64c5309')

When executed with mysql2 with the nestTables option set to true, they appear to provide the same key for the joined value despite different aliases.

1st query result:

{
        "role": {
          "role_id": 2,
          "name": "Logged In"
        },
        "bm_web_player_roles": {
          "player_id": {
            "type": "Buffer",
            "data": [
              83,
              69,
              142,
              157,
              181,
              81,
              78,
              115,
              167,
              178,
              211,
              250,
              200,
              212,
              29,
              103
            ]
          }
        }
      },
      {
        "role": {
          "role_id": 2,
          "name": "Logged In"
        },
        "bm_web_player_roles": {
          "player_id": {
            "type": "Buffer",
            "data": [
              213,
              145,
              138,
              205,
              126,
              187,
              67,
              216,
              172,
              66,
              179,
              88,
              246,
              76,
              83,
              9
            ]
          }
        }
      },
      {
        "role": {
          "role_id": 3,
          "name": "Admin"
        },
        "bm_web_player_roles": {
          "player_id": {
            "type": "Buffer",
            "data": [
              30,
              20,
              70,
              123,
              16,
              19,
              69,
              143,
              163,
              161,
              175,
              63,
              206,
              30,
              178,
              159
            ]
          }
        }
      }
    ]

2nd query result:

{
        "role": {
          "role_id": 1,
          "name": "Guest"
        },
        "bm_web_player_roles": {
          "player_id": {
            "type": "Buffer",
            "data": [
              83,
              69,
              142,
              157,
              181,
              81,
              78,
              115,
              167,
              178,
              211,
              250,
              200,
              212,
              29,
              103
            ]
          }
        }
      }
    ]

Note the second query references 'role' instead of 'serverRole' as defined in the query. If the second query is instead executed first, the key is serverRole for both.

On the surface, this seems to be caused by keyFromFields within parser cache returning the same value for both, and therefore reusing the same parser. Not leveraging the cached parser returns the expected result.

I'm aware this may be an edge case and the queries without context may seem odd. I'm trying to dynamically build queries for a GraphQL server to ensure only requested fields are selected from the database, using the graphql schema object name for the join alias.

Not sure what the best way around this is?

[sidorares]

is this what you expect as correct result for the second query?

{
        "serverRole": {
          "role_id": 1,
          "name": "Guest"
        },
        //...
}

[confuser]

Should be serverRole instead of role which is the JOIN alias

[{
        "serverRole": {
          "role_id": 1,
          "name": "Guest"
        },
        "bm_web_player_roles": {
          "player_id": {
            "type": "Buffer",
            "data": [
              83,
              69,
              142,
              157,
              181,
              81,
              78,
              115,
              167,
              178,
              211,
              250,
              200,
              212,
              29,
              103
            ]
          }
        }
      }
    ]

[sidorares]

not sure why key is the same, usually alias is what field.name returns. Could you log what's passed to keyFromFields in

node-mysql2/lib/parsers/parser_cache.js

Line 10 in ecfd554

let res =

?

[confuser]

> con.query({ nestTables: true, sql: 'select `role`.`role_id`, `role`.`name`, `bm_web_player_roles`.`player_id` from `bm_web_player_roles` inner join `bm_web_roles` as `role` on `bm_web_player_roles`.`role_id` = `role`.`role_id` where `bm_web_player_roles`.`player_id` in (X\'53458e9db5514e73a7b2d3fac8d41d67\', X\'1e14467b1013458fa3a1af3fce1eb29f\', X\'d5918acd7ebb43d8ac42b358f64c5309\')' }, () => {})
> con.query({ nestTables: true, sql: 'select `serverRole`.`role_id`, `serverRole`.`name`, `bm_web_player_server_roles`.`player_id` from `bm_web_player_server_roles` inner join `bm_web_roles` as `serverRole` on `bm_web_player_server_roles`.`role_id` = `serverRole`.`role_id` where `bm_web_player_server_roles`.`player_id` in (X\'53458e9db5514e73a7b2d3fac8d41d67\', X\'1e14467b1013458fa3a1af3fce1eb29f\', X\'d5918acd7ebb43d8ac42b358f64c5309\')' }, () => {})

  }
  console.log(res)
  return res;
}

res is the following for both queries:

console.log node_modules/mysql2/lib/parsers/parser_cache.js:27
    text/boolean/true/false/false/false/boolean/local/false/false/role_id:3:16931:63/name:253:4097:224/player_id:254:20619:63

Diff of fields between queries (not sure if this is of any help): https://www.diffchecker.com/iYm7lyVL

[sidorares]

oh, serverRole is not a field name but a table name. We probably need to add table name to key if nestTables is enabled

[sidorares]

table is accessible from field.table getter -

node-mysql2/lib/packets/column_definition.js

Line 125 in ecfd554

addString('table');