ORDER BY removes tablenames from result

ORDER BY removes tablenames from result

am 01.12.2005 19:00:51 von Thomas Gusenleitner

Hi,

i've following problem or bug with joins and order by

if i use a join order by comibination the table name prefix is removed
from the column names


for example it should be like this

table1.feld1


but i get only this
..feld1


this database is 4.0.x upgraged to 5.0.15 on redhat EL 3 - i use the
official pro packages provided on http://mysql.mysql.com


Attached a real example how showing this wrong behavior


With best regards

Thomas Gusenleitner


without ORDER BY everthing looks fine

> > sql->query("SELECT systemPasswd.*, userFranchise.franchiseID from
systemPasswd inner join userFranchise on
systemPasswd.sUID=userFranchise.sUID ");
(39) Result: ({ /* 1 element */
([ /* 66 elements */
"city": "",
"countryCode": "00",
"email": "",
"fax": "",
"firstname": "gusi",
"franchiseID": "1",
"langShort": 0,
"lastSeenReadme": "0",
"lastname": "stats",
"mobile": "",
"passwd": "test",
"phone": "",
"r_admin": "TRUE",
"r_betManagement": "TRUE",
"r_betOffer": "TRUE",
"r_internetAdmin": "FALSE",
"r_internetGuest": "FALSE",
"r_internetPlus": "TRUE",
"r_internetUser": "TRUE",
"r_masterData": "TRUE",
"r_mioAdmin": "FALSE",
"r_mioUser": "TRUE",
"r_outletAccount": "FALSE",
"r_outletAdmin": "FALSE",
"r_outletManagement": "TRUE",
"r_outletUser": "FALSE",
"r_stats": "TRUE",
"sUID": "1",
"status": "ACTIVE",
"street": "",
"systemPasswd.city": "",
"systemPasswd.countryCode": "00",
"systemPasswd.email": "",
"systemPasswd.fax": "",
"systemPasswd.firstname": "gusi",
"systemPasswd.langShort": 0,
"systemPasswd.lastSeenReadme": "0",
"systemPasswd.lastname": "stats",
"systemPasswd.mobile": "",
"systemPasswd.passwd": "test",
"systemPasswd.phone": "",
"systemPasswd.r_admin": "TRUE",
"systemPasswd.r_betManagement": "TRUE",
"systemPasswd.r_betOffer": "TRUE",
"systemPasswd.r_internetAdmin": "FALSE",
"systemPasswd.r_internetGuest": "FALSE",
"systemPasswd.r_internetPlus": "TRUE",
"systemPasswd.r_internetUser": "TRUE",
"systemPasswd.r_masterData": "TRUE",
"systemPasswd.r_mioAdmin": "FALSE",
"systemPasswd.r_mioUser": "TRUE",
"systemPasswd.r_outletAccount": "FALSE",
"systemPasswd.r_outletAdmin": "FALSE",
"systemPasswd.r_outletManagement": "TRUE",
"systemPasswd.r_outletUser": "FALSE",
"systemPasswd.r_stats": "TRUE",
"systemPasswd.sUID": "1",
"systemPasswd.status": "ACTIVE",
"systemPasswd.street": "",
"systemPasswd.username": "gusistats",
"systemPasswd.website": "",
"systemPasswd.zipcode": "",
"userFranchise.franchiseID": "1",
"username": "gusistats",
"website": "",
"zipcode": ""
])
})


With order by - the tablename is removed - for example the column
"systemPasswd.lastname"

> > sql->query("SELECT systemPasswd.*, userFranchise.franchiseID from
systemPasswd inner join userFranchise on
systemPasswd.sUID=userFranchise.sUID order by systemPasswd.lastname");
(40) Result: ({ /* 1 element */
([ /* 66 elements */
".city": "",
".email": "",
".fax": "",
".firstname": "gusi",
".lastname": "stats",
".mobile": "",
".passwd": "test",
".phone": "",
".street": "",
".username": "gusistats",
".website": "",
".zipcode": "",
"city": "",
"countryCode": "00",
"email": "",
"fax": "",
"firstname": "gusi",
"franchiseID": "1",
"langShort": 0,
"lastSeenReadme": "0",
"lastname": "stats",
"mobile": "",
"passwd": "test",
"phone": "",
"r_admin": "TRUE",
"r_betManagement": "TRUE",
"r_betOffer": "TRUE",
"r_internetAdmin": "FALSE",
"r_internetGuest": "FALSE",
"r_internetPlus": "TRUE",
"r_internetUser": "TRUE",
"r_masterData": "TRUE",
"r_mioAdmin": "FALSE",
"r_mioUser": "TRUE",
"r_outletAccount": "FALSE",
"r_outletAdmin": "FALSE",
"r_outletManagement": "TRUE",
"r_outletUser": "FALSE",
"r_stats": "TRUE",
"sUID": "1",
"status": "ACTIVE",
"street": "",
"systemPasswd.countryCode": "00",
"systemPasswd.langShort": 0,
"systemPasswd.lastSeenReadme": "0",
"systemPasswd.r_admin": "TRUE",
"systemPasswd.r_betManagement": "TRUE",
"systemPasswd.r_betOffer": "TRUE",
"systemPasswd.r_internetAdmin": "FALSE",
"systemPasswd.r_internetGuest": "FALSE",
"systemPasswd.r_internetPlus": "TRUE",
"systemPasswd.r_internetUser": "TRUE",
"systemPasswd.r_masterData": "TRUE",
"systemPasswd.r_mioAdmin": "FALSE",
"systemPasswd.r_mioUser": "TRUE",
"systemPasswd.r_outletAccount": "FALSE",
"systemPasswd.r_outletAdmin": "FALSE",
"systemPasswd.r_outletManagement": "TRUE",
"systemPasswd.r_outletUser": "FALSE",
"systemPasswd.r_stats": "TRUE",
"systemPasswd.sUID": "1",
"systemPasswd.status": "ACTIVE",
"userFranchise.franchiseID": "1",
"username": "gusistats",
"website": "",
"zipcode": ""
])
})


CREATE TABLE `systemPasswd` (
`sUID` bigint(20) unsigned NOT NULL auto_increment,
`username` char(64) NOT NULL default '',
`passwd` char(20) NOT NULL default '',
`status` enum('ACTIVE','LOCKED') NOT NULL default 'ACTIVE',
`firstname` char(64) NOT NULL default '',
`lastname` char(64) NOT NULL default '',
`street` char(255) default NULL,
`zipcode` char(6) default NULL,
`city` char(255) default NULL,
`countryCode` char(2) default NULL,
`phone` char(64) default NULL,
`fax` char(64) default NULL,
`mobile` char(64) default NULL,
`email` char(255) default NULL,
`website` char(255) default NULL,
`langShort` char(3) default NULL,
`lastSeenReadme` int(3) default '0',
`r_betOffer` enum('TRUE','FALSE') NOT NULL default 'FALSE',
`r_betManagement` enum('TRUE','FALSE') NOT NULL default 'FALSE',
`r_stats` enum('TRUE','FALSE') NOT NULL default 'FALSE',
`r_masterData` enum('TRUE','FALSE') NOT NULL default 'FALSE',
`r_admin` enum('TRUE','FALSE') NOT NULL default 'FALSE',
`r_internetAdmin` enum('TRUE','FALSE') NOT NULL default 'FALSE',
`r_internetUser` enum('TRUE','FALSE') NOT NULL default 'FALSE',
`r_internetGuest` enum('TRUE','FALSE') NOT NULL default 'FALSE',
`r_internetPlus` enum('FALSE','TRUE') NOT NULL default 'FALSE',
`r_outletManagement` enum('TRUE','FALSE') NOT NULL default 'FALSE',
`r_outletAdmin` enum('FALSE','TRUE') NOT NULL default 'FALSE',
`r_outletUser` enum('FALSE','TRUE') NOT NULL default 'FALSE',
`r_outletAccount` enum('FALSE','TRUE') default 'FALSE',
`r_mioUser` enum('FALSE','TRUE') NOT NULL default 'FALSE',
`r_mioAdmin` enum('FALSE','TRUE') NOT NULL default 'FALSE',
PRIMARY KEY (`sUID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1






--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=gcdmb-bugs@m.gmane.org