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:Microsoft Windows
Assigned to: CPU Architecture:Any

[3 Nov 2011 21:47] Larry Wakeman
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
[3 Nov 2011 21:53] Larry Wakeman
The RECTYPE column is varchar(1).
[3 Nov 2011 23:49] Larry Wakeman
A simple sql script to show the problem

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

[4 Nov 2011 6:37] Valeriy Kravchuk
Please, send the output of:

show create view view_referrals\G

from mysql command line client connected to your environment.
[4 Nov 2011 17:13] Larry Wakeman
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.
[4 Nov 2011 18:05] Larry Wakeman
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>
[4 Nov 2011 18:36] Valeriy Kravchuk
Now, from the same mysql command line client, please, send the output of:

show create view view_test\G
[4 Nov 2011 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>
[4 Nov 2011 21:47] Larry Wakeman
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.
[14 Nov 2011 10:31] Valeriy Kravchuk
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.
[14 Nov 2011 17:58] Larry Wakeman
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.
[14 Nov 2011 18:01] Valeriy Kravchuk
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...
[14 Nov 2011 18:04] Valeriy Kravchuk
Please, send also the output of:

show variables like 'char%';

from your environment.
[14 Nov 2011 21:03] Larry Wakeman
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.
[20 Dec 2011 12:40] Sveta Smirnova
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)
[16 Jan 2013 16:04] Paul DuBois
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.