Selecting two columns with the same name from two different tables will result in the second column overwriting the first in the results

[maxcnunes]

Given 2 tables with equal field names (id and name in this exaple):

CREATE TABLE IF NOT EXISTS `roles` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(100) NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS `users` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(150) NULL,
  `username` VARCHAR(45) NULL,
  `email` VARCHAR(150) NULL,
  `password` VARCHAR(45) NULL,
  `role_id` INT,
  `createdat` DATETIME NULL,
  PRIMARY KEY (`id`),
  FOREIGN KEY (role_id) REFERENCES roles (id) ON DELETE CASCADE
) ENGINE = InnoDB;

On selecting all the columns from both tables:

select *
from roles r
inner join users u on r.id = u.role_id

const client = mysql.createConnection(dbConfig);
client.query(query, (err, data, fields) => {
  console.dir(data, { depth: null });
});

The columns of the second table will overwrite the columns from the first in the rows property. Although it does not occur with the fields property.

[ { id: 1,
    name: 'Max',
    username: 'maxcnunes',
    email: 'maxcnunes@gmail.com',
    password: '123456',
    role_id: 1,
    createdat: '2016-10-25 00:00:00' } ]
{ command: 'SELECT',
  rows:
   [ { id: 1,
       name: 'Max',
       username: 'maxcnunes',
       email: 'maxcnunes@gmail.com',
       password: '123456',
       role_id: 1,
       createdat: '2016-10-25 00:00:00' } ],
  fields:
   [ { catalog: 'def',
       db: 'sqlectron',
       table: 'r',
       orgTable: 'roles',
       name: 'id',
       orgName: 'id',
       charsetNr: 63,
       length: 11,
       type: 3,
       flags: 16899,
       decimals: 0,
       default: undefined,
       zeroFill: false,
       protocol41: true },
     { catalog: 'def',
       db: 'sqlectron',
       table: 'r',
       orgTable: 'roles',
       name: 'name',
       orgName: 'name',
       charsetNr: 33,
       length: 300,
       type: 253,
       flags: 0,
       decimals: 0,
       default: undefined,
       zeroFill: false,
       protocol41: true },
     { catalog: 'def',
       db: 'sqlectron',
       table: 'u',
       orgTable: 'users',
       name: 'id',
       orgName: 'id',
       charsetNr: 63,
       length: 11,
       type: 3,
       flags: 16899,
       decimals: 0,
       default: undefined,
       zeroFill: false,
       protocol41: true },
     { catalog: 'def',
       db: 'sqlectron',
       table: 'u',
       orgTable: 'users',
       name: 'name',
       orgName: 'name',
       charsetNr: 33,
       length: 450,
       type: 253,
       flags: 0,
       decimals: 0,
       default: undefined,
       zeroFill: false,
       protocol41: true },
     { catalog: 'def',
       db: 'sqlectron',
       table: 'u',
       orgTable: 'users',
       name: 'username',
       orgName: 'username',
       charsetNr: 33,
       length: 135,
       type: 253,
       flags: 0,
       decimals: 0,
       default: undefined,
       zeroFill: false,
       protocol41: true },
     { catalog: 'def',
       db: 'sqlectron',
       table: 'u',
       orgTable: 'users',
       name: 'email',
       orgName: 'email',
       charsetNr: 33,
       length: 450,
       type: 253,
       flags: 0,
       decimals: 0,
       default: undefined,
       zeroFill: false,
       protocol41: true },
     { catalog: 'def',
       db: 'sqlectron',
       table: 'u',
       orgTable: 'users',
       name: 'password',
       orgName: 'password',
       charsetNr: 33,
       length: 135,
       type: 253,
       flags: 0,
       decimals: 0,
       default: undefined,
       zeroFill: false,
       protocol41: true },
     { catalog: 'def',
       db: 'sqlectron',
       table: 'u',
       orgTable: 'users',
       name: 'role_id',
       orgName: 'role_id',
       charsetNr: 63,
       length: 11,
       type: 3,
       flags: 16392,
       decimals: 0,
       default: undefined,
       zeroFill: false,
       protocol41: true },
     { catalog: 'def',
       db: 'sqlectron',
       table: 'u',
       orgTable: 'users',
       name: 'createdat',
       orgName: 'createdat',
       charsetNr: 63,
       length: 19,
       type: 12,
       flags: 128,
       decimals: 0,
       default: undefined,
       zeroFill: false,
       protocol41: true } ],
  rowCount: 1,
  affectedRows: undefined }

This problem was originally reported on https://github.com/sqlectron/sqlectron-gui/issues/199.

[dougwilson]

Hi @maxcnunes, yes, this is how this module works. There are options to help with this, and you can read about about this in the README: https://github.com/felixge/node-mysql#joins-with-overlapping-column-names