| Bug #78783 | WEIGHT_STRING result is wrong if used in a view (AS CHAR clause is lost) | ||
|---|---|---|---|
| Submitted: | 9 Oct 2015 12:40 | Modified: | 10 Nov 2015 17:03 |
| Reporter: | Guilhem Bichot | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S3 (Non-critical) |
| Version: | 5.7.10, 5.6.27, 8.0.0 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[9 Oct 2015 12:46]
MySQL Verification Team
Hello Guilhem, Thank you for the report and test case. Verified as described with 5.7.10 build. Thanks, Umesh
[10 Nov 2015 17:03]
Paul DuBois
Noted in 5.7.11, 5.8.0 changelogs. The result from WEIGHT_STRING() could be incorrect when used in a view.
[18 Jun 2016 21:28]
Omer Barnir
Posted by developer: Reported version value updated to reflect release name change from 5.8 to 8.0
Description: If I create such view: create view v1 as select weight_string(a as char(4)) as b from t1; the AS CHAR(4) clause is lost during creation. Thus I get unexpected results: select b from v1; does not return the same as select weight_string(a as char(4)) as b from t1; Verified with 5.7.8. Didn't test other versions. How to repeat: create table t1 (a varchar(5)); create view v1 as select weight_string(a as char(4)) as b from t1; show create view v1; insert into t1 values ('MySQL'); select weight_string(a as char(4)) as b from t1; select b from v1; drop view v1; drop table t1; Result of SELECTs: +select weight_string(a as char(4)) as b from t1; +b +MYSQ +select b from v1; +b +MYSQL The last one should return MySQ, not MySQL. Suggested fix: The problem is that Item_func_weight_string::print() does not print all possible clauses. So, as this function is used to print a normalized query expression in the view's frm file (at CREATE VIEW time), some clauses are lost during definition. Look at the syntax from the yacc parser: | WEIGHT_STRING_SYM '(' expr opt_ws_levels ')' { $$= NEW_PTN Item_func_weight_string(@$, $3, 0, 0, $4); } | WEIGHT_STRING_SYM '(' expr AS CHAR_SYM ws_nweights opt_ws_levels ')' { $$= NEW_PTN Item_func_weight_string(@$, $3, 0, $6, $7 | MY_STRXFRM_PAD_WITH_SPACE); } | WEIGHT_STRING_SYM '(' expr AS BINARY ws_nweights ')' { $$= NEW_PTN Item_func_weight_string(@$, $3, 0, $6, MY_STRXFRM_PAD_WITH_SPACE, true); } | WEIGHT_STRING_SYM '(' expr ',' ulong_num ',' ulong_num ',' ulong_num ')' { $$= NEW_PTN Item_func_weight_string(@$, $3, $5, $7, $9); } All those possibilities should be accounted for in print().