MySQL Bugs: #33198: View Corrupted When Created Using Hexadecimal / Bit-Field Literals

Bug #33198	View Corrupted When Created Using Hexadecimal / Bit-Field Literals
Submitted:	13 Dec 2007 5:38	Modified:	13 Dec 2007 12:48
Reporter:	Asuka Kenji Siu Ching Pong (Basic Quality Contributor) (OCA)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Views	Severity:	S2 (Serious)
Version:	5.0.50-enterprise-gpl-nt, 5.0.51	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	CHAR(), create view, qc

Description:
Views created using statements in the form "CREATE VIEW ... AS SELECT CHAR(...);" could not be SELECTed nor DESCRIBEd.

How to repeat:
USE test;

SET SQL_NOTES = 0;
DROP TABLE IF EXISTS test.t1;
DROP VIEW IF EXISTS test.t1;
SET SQL_NOTES = 1;

-- The following statements are executed without problem --

CREATE VIEW test.t1 AS SELECT 'A';
SHOW FULL COLUMNS FROM test.t1;
SELECT * FROM test.t1;
DROP VIEW IF EXISTS test.t1;

CREATE VIEW test.t1 AS SELECT CONVERT('A' USING binary);
SHOW FULL COLUMNS FROM test.t1;
SELECT * FROM test.t1;
DROP VIEW IF EXISTS test.t1;

CREATE VIEW test.t1 AS SELECT CONVERT('A' USING latin1);
SHOW FULL COLUMNS FROM test.t1;
SELECT * FROM test.t1;
DROP VIEW IF EXISTS test.t1;

CREATE VIEW test.t1 AS SELECT CONVERT('A' USING utf8);
SHOW FULL COLUMNS FROM test.t1;
SELECT * FROM test.t1;
DROP VIEW IF EXISTS test.t1;

CREATE VIEW test.t1 AS SELECT CONVERT('A' USING ucs2);
SHOW FULL COLUMNS FROM test.t1;
SELECT * FROM test.t1;
DROP VIEW IF EXISTS test.t1;

-- The following SHOW and SELECT statements fail --

CREATE VIEW test.t1 AS SELECT CHAR(0x41);
SHOW FULL COLUMNS FROM test.t1;
SELECT * FROM test.t1;
DROP VIEW IF EXISTS test.t1;

CREATE VIEW test.t1 AS SELECT CHAR(0x41 USING binary);
SHOW FULL COLUMNS FROM test.t1;
SELECT * FROM test.t1;
DROP VIEW IF EXISTS test.t1;

CREATE VIEW test.t1 AS SELECT CHAR(0x41 USING latin1);
SHOW FULL COLUMNS FROM test.t1;
SELECT * FROM test.t1;
DROP VIEW IF EXISTS test.t1;

CREATE VIEW test.t1 AS SELECT CHAR(0x41 USING utf8);
SHOW FULL COLUMNS FROM test.t1;
SELECT * FROM test.t1;
DROP VIEW IF EXISTS test.t1;

CREATE VIEW test.t1 AS SELECT CHAR(0x00, 0x41 USING ucs2);
SHOW FULL COLUMNS FROM test.t1;
SELECT * FROM test.t1;
DROP VIEW IF EXISTS test.t1;

Suggested fix:
Use string literals instead of using the CHAR() function.

However, non-printable characters (eg. control characters, Unicode code-point) that are best input using the CHAR() function have to be input in more difficult ways (eg. LOAD DATA INFILE, etc).

Test case in MySQL test case format

Attachment: bug33198.test (application/octet-stream, text), 1.40 KiB.

Mis-spelt the word "Corrupted" as "Corruptted" in the "Synopsis" above. Fixed.

The root cause of this bug may be the bug described in bug #33205.

It is found that if the hexadecimal literals are replaced by bit-field literals in the form b'1010' and 0b1010, similar problems also occur.

Verified as described with MySQL 5.0.51.

For example:
mysql> CREATE VIEW test.t1 AS SELECT CHAR(0x41);
Query OK, 0 rows affected (0.03 sec)

mysql> SELECT * FROM test.t1;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '???) AS `CHAR(0x41)`' at line 1

Seems fixed in 5.0.52-enterprise-gpl-nt.

Thank you for your bug report. This issue has been committed to our source repository of that product and will be incorporated into the next release (Community server). This issue has already been fixed in the latest released enterprise version.

If necessary, you can access the source repository and build the latest available version, including the bug fix. More information about accessing the source trees is available at

    http://dev.mysql.com/doc/en/installing-source.html