Description:
Problem encountered when creating _composite_ table using CREATE TABLE from SELECT:
CREATE TABLE `garbled_example` SELECT ... LEFT JOIN ...
Selecting TEXT, MEDIUMTEXT, and LONGTEXT type fields from the resulting table `garbled_example` causes garbled output to be returned (binary?). The garbled output changes, although not everytime. This seems to happen only if there's a join involved. My example below uses LONGTEXT but same behaviour happens with all types above.
I have included all SQL to reproduce the problem.
I have been able to reproduce this in 3 different environments (3 separate boxes):
1. SuSE Linux 8.2 - MySQL Ver 4.1.1-alpha-standard
2. SuSE Linux 8.0 - MySQL Ver 3.23.48
3. RedHat Linux 9 - MySQL Ver 3.23.56
How to repeat:
#-------------------------------------------------------------------
# 1. SETUP
# a. Create a test database
# b. Create tables and insert data
#-------------------------------------------------------------------
CREATE DATABASE `db_test_1`;
CREATE TABLE `tab1` (
`subID` varchar(10) NOT NULL default '',
`comment` longtext,
PRIMARY KEY (`subID`)
) TYPE=MyISAM;
INSERT INTO `tab1` (`subID`, `comment`) VALUES ('5000000001', NULL),
('5000000003', 'Test'),
('5000000004', NULL);
CREATE TABLE `tab2` (
`subID` varchar(15) NOT NULL default '',
`proc` varchar(100) NOT NULL default '',
`runID` varchar(16) NOT NULL default '',
`start` datetime NOT NULL default '0000-00-00 00:00:00',
PRIMARY KEY (`subID`,`proc`,`runID`,`start`)
) TYPE=MyISAM;
INSERT INTO `tab2` (`subID`, `proc`, `runID`, `start`) VALUES
('5000000001', 'proc01', '20031029090650', '2003-10-29 13:38:40'),
('5000000001', 'proc02', '20031029090650', '2003-10-29 13:38:51'),
('5000000001', 'proc03', '20031029090650', '2003-10-29 13:38:11'),
('5000000002', 'proc09', '20031024013310', '2003-10-24 01:33:11'),
('5000000002', 'proc09', '20031024153537', '2003-10-24 15:36:04'),
('5000000004', 'proc01', '20031024013641', '2003-10-24 01:37:29'),
('5000000004', 'proc02', '20031024013641', '2003-10-24 01:37:39');
#---//--------------------------------------------------------------
#-------------------------------------------------------------------
# 2. CREATE TABLE from SELECT
# a. Create _composite_ table from select query (using join)
#-------------------------------------------------------------------
CREATE TABLE `garbled_example`
SELECT
su.subID,
su.comment,
COUNT( DISTINCT pl.runID ) AS countOfRuns,
MAX( pl.start) AS lastStart
FROM
`tab1` AS su LEFT JOIN `tab2` AS pl ON ( su.subID = pl.subID )
GROUP BY
su.subID;
#---//--------------------------------------------------------------
#-------------------------------------------------------------------
# 3. VERIFY
# a. Run SELECT statement below
# b. Look at `comment` field for subID = '5000000003'
#-------------------------------------------------------------------
SELECT *
FROM `garbled_example`
#---//--------------------------------------------------------------
Suggested fix:
Is the SQL engine interpreting the TEXT type field as BLOB somehow?
Description: Problem encountered when creating _composite_ table using CREATE TABLE from SELECT: CREATE TABLE `garbled_example` SELECT ... LEFT JOIN ... Selecting TEXT, MEDIUMTEXT, and LONGTEXT type fields from the resulting table `garbled_example` causes garbled output to be returned (binary?). The garbled output changes, although not everytime. This seems to happen only if there's a join involved. My example below uses LONGTEXT but same behaviour happens with all types above. I have included all SQL to reproduce the problem. I have been able to reproduce this in 3 different environments (3 separate boxes): 1. SuSE Linux 8.2 - MySQL Ver 4.1.1-alpha-standard 2. SuSE Linux 8.0 - MySQL Ver 3.23.48 3. RedHat Linux 9 - MySQL Ver 3.23.56 How to repeat: #------------------------------------------------------------------- # 1. SETUP # a. Create a test database # b. Create tables and insert data #------------------------------------------------------------------- CREATE DATABASE `db_test_1`; CREATE TABLE `tab1` ( `subID` varchar(10) NOT NULL default '', `comment` longtext, PRIMARY KEY (`subID`) ) TYPE=MyISAM; INSERT INTO `tab1` (`subID`, `comment`) VALUES ('5000000001', NULL), ('5000000003', 'Test'), ('5000000004', NULL); CREATE TABLE `tab2` ( `subID` varchar(15) NOT NULL default '', `proc` varchar(100) NOT NULL default '', `runID` varchar(16) NOT NULL default '', `start` datetime NOT NULL default '0000-00-00 00:00:00', PRIMARY KEY (`subID`,`proc`,`runID`,`start`) ) TYPE=MyISAM; INSERT INTO `tab2` (`subID`, `proc`, `runID`, `start`) VALUES ('5000000001', 'proc01', '20031029090650', '2003-10-29 13:38:40'), ('5000000001', 'proc02', '20031029090650', '2003-10-29 13:38:51'), ('5000000001', 'proc03', '20031029090650', '2003-10-29 13:38:11'), ('5000000002', 'proc09', '20031024013310', '2003-10-24 01:33:11'), ('5000000002', 'proc09', '20031024153537', '2003-10-24 15:36:04'), ('5000000004', 'proc01', '20031024013641', '2003-10-24 01:37:29'), ('5000000004', 'proc02', '20031024013641', '2003-10-24 01:37:39'); #---//-------------------------------------------------------------- #------------------------------------------------------------------- # 2. CREATE TABLE from SELECT # a. Create _composite_ table from select query (using join) #------------------------------------------------------------------- CREATE TABLE `garbled_example` SELECT su.subID, su.comment, COUNT( DISTINCT pl.runID ) AS countOfRuns, MAX( pl.start) AS lastStart FROM `tab1` AS su LEFT JOIN `tab2` AS pl ON ( su.subID = pl.subID ) GROUP BY su.subID; #---//-------------------------------------------------------------- #------------------------------------------------------------------- # 3. VERIFY # a. Run SELECT statement below # b. Look at `comment` field for subID = '5000000003' #------------------------------------------------------------------- SELECT * FROM `garbled_example` #---//-------------------------------------------------------------- Suggested fix: Is the SQL engine interpreting the TEXT type field as BLOB somehow?