MySQL Bugs: #63094: Incorrect view creation with where claus

Bug #63094	Incorrect view creation with where claus
Submitted:	3 Nov 2011 21:47	Modified:	16 Jan 2013 16:04
Reporter:	Larry Wakeman	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: DDL	Severity:	S1 (Critical)
Version:	5.5.16, 5.5.20	OS:	Windows
Assigned to:		CPU Architecture:	Any

Description:
I am trying to create the following view (the actual view definition I am trying to use is much more complex, but I tested this one and it fails):

create or replace view view_referrals AS SELECT ref.guid AS id, ref.NOTES AS notes FROM db_GM_MO_COMMON_RECRUIT_CONTSUPP AS ref WHERE ref.RECTYPE = 'R';

When I look at the view definition (export using phpmyadmin) the where clause becomes:

WHERE ref.RECTYPE = '\0\0\0R';

I can run the Select portion of the query with expected results but theview returns an empty result set.

I tried this using 5.0.75-0ubuntu10.2 on linux and it works fine. Could still be a linux issue with a later version.  That's what I have access to.

How to repeat:
See swadeiption

The RECTYPE column is varchar(1).

A simple sql script to show the problem

Attachment: test.sql (text/x-sql), 56.58 KiB.

Please, send the output of:

show create view view_referrals\G

from mysql command line client connected to your environment.

I am creating the view with php though I have dumped the sql and recreated the view in ohoMyAmin.  They show no errors andthe view is created.  I used teh following script to dump the last 9 characters from the information_schema.

$query = "SELECT `VIEW_DEFINITION` FROM information_schema.`VIEWS` WHERE `TABLE_NAME` = 'view_test'";
if (!$db->Query($query)) {
    echo "Error - Can't create view - ".$db->Error()."<br>";
    exit;
}
$view = $db->RowArray(0);
echo '<pre>'; // debug
print_r ($view); // debug
echo '</pre>'; // debug
for ($i=(strlen($view['VIEW_DEFINITION']) - 8);$i < strlen($view['VIEW_DEFINITION']); $i++) echo ord(substr($view['VIEW_DEFINITION'], $i, 1)).'-'.substr($view['VIEW_DEFINITION'], $i, 1).' ';
echo '<br>';

I use a database object so it isn't the usual php code.  The results are:

Array
(
    [0] => select `sugarcrm6`.`test`.`guid` AS `guid`,`sugarcrm6`.`test`.`NOTES` AS `NOTES` from `sugarcrm6`.`test` where (`sugarcrm6`.`test`.`RECTYPE` = 'R')
    [VIEW_DEFINITION] => select `sugarcrm6`.`test`.`guid` AS `guid`,`sugarcrm6`.`test`.`NOTES` AS `NOTES` from `sugarcrm6`.`test` where (`sugarcrm6`.`test`.`RECTYPE` = 'R')
)

96-` 32- 61-= 32- 39-' 82-R 39-' 41-)

Note that there are no null characters in the definition so they must be added during execution.

I will runtest.sql and get you the dump later today.

Output from create view using mysql

mysql> CREATE Or REPLACE VIEW view_test AS SELECT `guid`, `NOTES` FROM `test` WH
ERE `RECTYPE` = 'R'
    -> ;
Query OK, 0 rows affected (0.00 sec)

mysql>

Now, from the same mysql command line client, please, send the output of:

show create view view_test\G

mysql> CREATE Or REPLACE VIEW view_test AS SELECT `guid`, `NOTES` FROM `test` WHERE `RECTYPE` = 'R'\G
Query OK, 0 rows affected (0.01 sec)

mysql>

I have found a workaround that works for me. If I change the charset to utf8 it works.  Utf16 doesn't.  I think you still have a bug in that it would affect Kanji and Chinese developers and others.  Those are the only languages that I have worked with that need 16bit characters.

Why when I ask you to run

show create view view_test\G

you copy paste me the results of CREATE VIEW...? Is it so hard to paste my exact command, SHOW CREATE VIEW... for the problematic view, and then copy and paste the results here?

I need results like this:

mysql> show create view v1\G
*************************** 1. row ***************************
                View: v1
         Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select 1 AS `asa`
character_set_client: latin1
collation_connection: latin1_swedish_ci
1 row in set (0.00 sec)

to check what character set and collation are used.

Look at it again, I did do a /G:

[4 Nov 20:44] Larry Wakeman

mysql> CREATE Or REPLACE VIEW view_test AS SELECT `guid`, `NOTES` FROM `test` WHERE
`RECTYPE` = 'R'\G
Query OK, 0 rows affected (0.01 sec)

mysql>

The issue is that you are padding the characters in the where clause with null bytes.  You don't need to pad page 0 characters for UTF16.  The asci character us a valid page 0 character abd a bykk byte us the incorrect character to pad with,  To indicate a multibyte sequence, the characters other than the last must be at leasst 0x80.

Again you execute CREATE VIEW ... to create new view, while I asked you for the results of SHOW CREATE VIEW ... for the existing view. 

SHOW, please...

Please, send also the output of:

show variables like 'char%';

from your environment.

I have since changed the character set of the database.  When I was doing this the character set was UTF16 and the collation was UTF16 General CI.  I have since changed the character set to utf8 because I have work to do.

Thank you for the feedback.

Verified as described using test case attached:

[sveta@delly mysql-test]$ ./mtr --start ctype_utf16 &
...
[sveta@delly mysql-test]$ ../client/mysql -uroot -h 127.0.0.1 -P13000 test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.5.20-debug-log Source distribution
...

mysql> SET character_set_connection=utf16;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE IF NOT EXISTS test (
    ->   `imp_id` bigint(20) NOT NULL AUTO_INCREMENT,
    ->   `guid` varchar(36) NOT NULL,
    ->   `RECTYPE` varchar(1) DEFAULT NULL,
    ->   `NOTES` mediumtext,
    ->   PRIMARY KEY (`imp_id`),
    ->   KEY `guid` (`guid`)
    -> ) ENGINE=InnoDB  DEFAULT CHARSET=utf16 AUTO_INCREMENT=11600 ;
Query OK, 0 rows affected (0.19 sec)

mysql> CREATE Or REPLACE VIEW view_test AS SELECT `guid`, `NOTES` FROM `test` WHERE `RECTYPE` = 'R';
Query OK, 0 rows affected (0.08 sec)

mysql> show create view view_test\G
*************************** 1. row ***************************
                View: view_test
         Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `view_test` AS select `test`.`guid` AS `guid`,`test`.`NOTES` AS `NOTES` from `test` where (`test`.`RECTYPE` = '\0\0\0R')
character_set_client: utf8
collation_connection: utf16_general_ci
1 row in set (0.01 sec)

Noted in 5.7.0 changelog.

A view was created with an incorrect definition if the WHERE clause
contained string literals and character_set_client and
character_set_connection were set to different character sets.