Mysql: table prefix are ignored in the output fields

[flowl]

Please refer to this SO question:

https://stackoverflow.com/questions/47926383/mysql-query-missing-results-in-node-mysql-that-are-returned-in-phpmyadmin/47926908#47926908

var query='SELECT s.result, s.distance, hp.result, hp.distance FROM `table15`....';

Returns

[{result: "36:24", distance: 12}]

Expected:

[{s.result: "36:24", s.distance: 12, hp.result:..., s.result:...}]

I know the dots represent new objects in Javascript, does this mean it is not possible to use prefixed fields without alias?

[orshee]

Your expected result is not valid JSON due to duplicate column names, adjust SQL to produce unique column names.

[dougwilson]

Use the nestTables option: https://github.com/mysqljs/mysql/blob/master/Readme.md#joins-with-overlapping-column-names

[flowl]

The column names ARE unique ("s.result" !== "hp.result").
The library can't handle the literal dot in the output fields.
But to be honest, I don't know how it could elegantly handle them.

Output could look like:

[
    {
        s: { result: "36:24", distance: 12 },
        hp: { result: ..., distance: ... }
    }
]

[dougwilson]

No, the column names are not unique. You are showing identifiers with two parts: a table name and a column name. Over the MySql protocol, thry do not appear together. This library never parses SQL it just reads the underlying protocol packets. The MySql server is not sending the column names as "s.result" only as "result". You can see it come back as "s.result" if you alias the column name in your SQL like s.result AS 's.result' but withiut that your MySQL server just send back "result" as the column name.

The "nestTables" option provides the ability to create the nested output like you are expecting above. You can even do "nestedTables": "." to get them separated with dots instead of nested.

If you know how this should be handling the dots, please feel free to make a pull request implementing your change to help out :+1: