Description:
The following Query no longer works with version 5. This has been tested on versions between 5.0.37 and 5.2.3. Works fine with 4.1 and previous versions. This is an example as even simple left joins don't seem to work.
How to repeat:
TABLES HAVE BEEN TRUNCATED DUE TO SENSITIVE NATURE OF BUSINESS:
SELECT DISTINCT C.Name, C.FullName, IF(ISNULL(COUNTRY_VIEW.DevaluationId),'0.000000',DEVALUATION.Loss) AS DEVAL, IF(ISNULL(COUNTRY_VIEW.DevaluationId),'0.000000',DEVALUATION.CountryLimit) AS LIMIT, IF(ISNULL(COUNTRY_VIEW.DevaluationId),'AAA',RATING.Name) AS RANK FROM COUNTRY C, DEFAULT_DATA, COUNTRY_VIEW V LEFT JOIN COUNTRY_VIEW ON COUNTRY_VIEW.CountryId=C.CountryId AND COUNTRY_VIEW.CountryGroupId=DEFAULT_DATA.CountryGroupId LEFT JOIN DEVALUATION ON DEVALUATION.DevaluationId=COUNTRY_VIEW.DevaluationId LEFT JOIN RATING ON RATING.RatingId=DEVALUATION.RatingId WHERE DEFAULT_DATA.UserId=1 ORDER by C.Name
CREATE TABLE COUNTRY (
CountryId tinyint(4) NOT NULL auto_increment,
Name char(2) NOT NULL default '',
FullName varchar(22) NOT NULL default '',
PRIMARY KEY (CountryId),
KEY Id (CountryId)
);
CREATE TABLE COUNTRY_VIEW (
CountryViewId smallint(6) NOT NULL auto_increment,
CountryId mediumint(9) NOT NULL default '0',
DevaluationId mediumint(9) NOT NULL default '0',
RatingId mediumint(9) NOT NULL default '0',
CountryGroupId mediumint(9) NOT NULL default '0',
PRIMARY KEY (CountryViewId)
);
CREATE TABLE DEFAULT_DATA (
DefaultDataId int(11) NOT NULL auto_increment,
CountryGroupId mediumint(6) NOT NULL default '1',
UserId int(11) NOT NULL default '0',
);
CREATE TABLE DEVALUATION (
DevaluationId int(11) NOT NULL auto_increment,
CountryLimit float(16,2) default NULL,
Loss float(6,4) default NULL,
);
CREATE TABLE RATING (
RatingId int(11) NOT NULL auto_increment,
Name varchar(10) NOT NULL default 'AAA',
Ranking mediumint(9) NOT NULL default '1',
);
