# At a local mysql-5.1-bugteam repository of davi
3114 Davi Arnaut 2009-09-04
Bug#40277: SHOW CREATE VIEW returns invalid SQL
The problem is that not all column names retrieved from a SELECT
statement can be used as view column names due to length and format
restrictions and the server failed to properly check the conformity
of those automatically generated column names before storing the
final view definition on disk.
Since columns retrieved from a SELECT statement can be anything
ranging from functions to constants values of any format and length,
the solution is to not print a auto generated names that are not
acceptable as view column names.
To avoid this kind of problem, users are encouraged to define
names for the view columns via the column_list clause.
Existing views with invalid query strings that contain invalid
column names should be dropped re-created.
@ mysql-test/r/date_formats.result
Update test case result.
@ mysql-test/r/subselect.result
Update test case result.
@ mysql-test/r/subselect3.result
Update test case result.
@ mysql-test/r/type_blob.result
Update test case result.
@ mysql-test/r/view.result
Add test case result for Bug#40277
@ mysql-test/t/view.test
Add test case for Bug#40277
@ sql/item.cc
Don't print invalid column names.
modified:
mysql-test/r/date_formats.result
mysql-test/r/func_compress.result
mysql-test/r/func_time.result
mysql-test/r/subselect.result
mysql-test/r/subselect3.result
mysql-test/r/type_blob.result
mysql-test/r/view.result
mysql-test/t/view.test
sql/item.cc
=== modified file 'mysql-test/r/date_formats.result'
--- a/mysql-test/r/date_formats.result 2009-06-11 16:21:32 +0000
+++ b/mysql-test/r/date_formats.result 2009-09-04 22:21:49 +0000
@@ -454,7 +454,7 @@ explain extended select makedate(1997,1)
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used
Warnings:
-Note 1003 select makedate(1997,1) AS `makedate(1997,1)`,addtime('31.12.97 11.59.59.999999 PM','1 1.1.1.000002') AS `addtime("31.12.97 11.59.59.999999 PM", "1 1.1.1.000002")`,subtime('31.12.97 11.59.59.999999 PM','1 1.1.1.000002') AS `subtime("31.12.97 11.59.59.999999 PM", "1 1.1.1.000002")`,timediff('01.01.97 11:59:59.000001 PM','31.12.95 11:59:59.000002 PM') AS `timediff("01.01.97 11:59:59.000001 PM","31.12.95 11:59:59.000002 PM")`,cast(str_to_date('15-01-2001 12:59:59','%d-%m-%Y %H:%i:%S') as time) AS `cast(str_to_date("15-01-2001 12:59:59", "%d-%m-%Y %H:%i:%S") as TIME)`,maketime(23,11,12) AS `maketime(23,11,12)`,microsecond('1997-12-31 23:59:59.000001') AS `microsecond("1997-12-31 23:59:59.000001")`
+Note 1003 select makedate(1997,1) AS `makedate(1997,1)`,addtime('31.12.97 11.59.59.999999 PM','1 1.1.1.000002') AS `addtime("31.12.97 11.59.59.999999 PM", "1 1.1.1.000002")`,subtime('31.12.97 11.59.59.999999 PM','1 1.1.1.000002') AS `subtime("31.12.97 11.59.59.999999 PM", "1 1.1.1.000002")`,timediff('01.01.97 11:59:59.000001 PM','31.12.95 11:59:59.000002 PM'),cast(str_to_date('15-01-2001 12:59:59','%d-%m-%Y %H:%i:%S') as time),maketime(23,11,12) AS `maketime(23,11,12)`,microsecond('1997-12-31 23:59:59.000001') AS `microsecond("1997-12-31 23:59:59.000001")`
create table t1 (d date);
insert into t1 values ('2004-07-14'),('2005-07-14');
select date_format(d,"%d") from t1 order by 1;
=== modified file 'mysql-test/r/func_compress.result'
--- a/mysql-test/r/func_compress.result 2009-06-19 09:29:21 +0000
+++ b/mysql-test/r/func_compress.result 2009-09-04 22:21:49 +0000
@@ -19,7 +19,7 @@ explain extended select uncompressed_len
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used
Warnings:
-Note 1003 select (uncompressed_length(compress((@test_compress_string))) = length((@test_compress_string))) AS `uncompressed_length(compress(@test_compress_string))=length(@test_compress_string)`
+Note 1003 select (uncompressed_length(compress((@test_compress_string))) = length((@test_compress_string)))
select uncompressed_length(compress(@test_compress_string));
uncompressed_length(compress(@test_compress_string))
117
=== modified file 'mysql-test/r/func_time.result'
--- a/mysql-test/r/func_time.result 2009-01-23 12:22:05 +0000
+++ b/mysql-test/r/func_time.result 2009-09-04 22:21:49 +0000
@@ -858,7 +858,7 @@ explain extended select period_add("9602
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used
Warnings:
-Note 1003 select period_add('9602',-(12)) AS `period_add("9602",-12)`,period_diff(199505,'9404') AS `period_diff(199505,"9404")`,from_days(to_days('960101')) AS `from_days(to_days("960101"))`,dayofmonth('1997-01-02') AS `dayofmonth("1997-01-02")`,month('1997-01-02') AS `month("1997-01-02")`,monthname('1972-03-04') AS `monthname("1972-03-04")`,dayofyear('0000-00-00') AS `dayofyear("0000-00-00")`,hour('1997-03-03 23:03:22') AS `HOUR("1997-03-03 23:03:22")`,minute('23:03:22') AS `MINUTE("23:03:22")`,second(230322) AS `SECOND(230322)`,quarter(980303) AS `QUARTER(980303)`,week('1998-03-03',0) AS `WEEK("1998-03-03")`,yearweek('2000-01-01',1) AS `yearweek("2000-01-01",1)`,week(19950101,1) AS `week(19950101,1)`,year('98-02-03') AS `year("98-02-03")`,(weekday(curdate()) - weekday(now())) AS `weekday(curdate())-weekday(now())`,dayname('1962-03-03') AS `dayname("1962-03-03")`,unix_timestamp() AS `unix_timestamp()`,sec_to_time((time_to_sec('0:30:47') / 6.21)) AS `sec_to_time(time_to_sec
("0:30:47")/6.21)`,curtime() AS `curtime()`,utc_time() AS `utc_time()`,curdate() AS `curdate()`,utc_date() AS `utc_date()`,utc_timestamp() AS `utc_timestamp()`,date_format('1997-01-02 03:04:05','%M %W %D %Y %y %m %d %h %i %s %w') AS `date_format("1997-01-02 03:04:05", "%M %W %D %Y %y %m %d %h %i %s %w")`,from_unixtime(unix_timestamp('1994-03-02 10:11:12')) AS `from_unixtime(unix_timestamp("1994-03-02 10:11:12"))`,('1997-12-31 23:59:59' + interval 1 second) AS `"1997-12-31 23:59:59" + INTERVAL 1 SECOND`,('1998-01-01 00:00:00' - interval 1 second) AS `"1998-01-01 00:00:00" - INTERVAL 1 SECOND`,('1997-12-31' + interval 1 day) AS `INTERVAL 1 DAY + "1997-12-31"`,extract(year from '1999-01-02 10:11:12') AS `extract(YEAR FROM "1999-01-02 10:11:12")`,('1997-12-31 23:59:59' + interval 1 second) AS `date_add("1997-12-31 23:59:59",INTERVAL 1 SECOND)`
+Note 1003 select period_add('9602',-(12)) AS `period_add("9602",-12)`,period_diff(199505,'9404') AS `period_diff(199505,"9404")`,from_days(to_days('960101')) AS `from_days(to_days("960101"))`,dayofmonth('1997-01-02') AS `dayofmonth("1997-01-02")`,month('1997-01-02') AS `month("1997-01-02")`,monthname('1972-03-04') AS `monthname("1972-03-04")`,dayofyear('0000-00-00') AS `dayofyear("0000-00-00")`,hour('1997-03-03 23:03:22') AS `HOUR("1997-03-03 23:03:22")`,minute('23:03:22') AS `MINUTE("23:03:22")`,second(230322) AS `SECOND(230322)`,quarter(980303) AS `QUARTER(980303)`,week('1998-03-03',0) AS `WEEK("1998-03-03")`,yearweek('2000-01-01',1) AS `yearweek("2000-01-01",1)`,week(19950101,1) AS `week(19950101,1)`,year('98-02-03') AS `year("98-02-03")`,(weekday(curdate()) - weekday(now())) AS `weekday(curdate())-weekday(now())`,dayname('1962-03-03') AS `dayname("1962-03-03")`,unix_timestamp() AS `unix_timestamp()`,sec_to_time((time_to_sec('0:30:47') / 6.21)) AS `sec_to_time(time_to_sec
("0:30:47")/6.21)`,curtime() AS `curtime()`,utc_time() AS `utc_time()`,curdate() AS `curdate()`,utc_date() AS `utc_date()`,utc_timestamp() AS `utc_timestamp()`,date_format('1997-01-02 03:04:05','%M %W %D %Y %y %m %d %h %i %s %w'),from_unixtime(unix_timestamp('1994-03-02 10:11:12')) AS `from_unixtime(unix_timestamp("1994-03-02 10:11:12"))`,('1997-12-31 23:59:59' + interval 1 second) AS `"1997-12-31 23:59:59" + INTERVAL 1 SECOND`,('1998-01-01 00:00:00' - interval 1 second) AS `"1998-01-01 00:00:00" - INTERVAL 1 SECOND`,('1997-12-31' + interval 1 day) AS `INTERVAL 1 DAY + "1997-12-31"`,extract(year from '1999-01-02 10:11:12') AS `extract(YEAR FROM "1999-01-02 10:11:12")`,('1997-12-31 23:59:59' + interval 1 second) AS `date_add("1997-12-31 23:59:59",INTERVAL 1 SECOND)`
SET @TMP='2007-08-01 12:22:49';
CREATE TABLE t1 (d DATETIME);
INSERT INTO t1 VALUES ('2007-08-01 12:22:59');
=== modified file 'mysql-test/r/subselect.result'
--- a/mysql-test/r/subselect.result 2009-08-31 13:40:35 +0000
+++ b/mysql-test/r/subselect.result 2009-09-04 22:21:49 +0000
@@ -224,7 +224,7 @@ id select_type table type possible_keys
3 DEPENDENT SUBQUERY t3 ALL NULL NULL NULL NULL 3 100.00 Using where
Warnings:
Note 1276 Field or reference 'test.t4.a' of SELECT #3 was resolved in SELECT #1
-Note 1003 select `test`.`t4`.`b` AS `b`,(select avg((`test`.`t2`.`a` + (select min(`test`.`t3`.`a`) AS `min(t3.a)` from `test`.`t3` where (`test`.`t3`.`a` >= `test`.`t4`.`a`)))) AS `avg(t2.a+(select min(t3.a) from t3 where t3.a >= t4.a))` from `test`.`t2`) AS `(select avg(t2.a+(select min(t3.a) from t3 where t3.a >= t4.a)) from t2)` from `test`.`t4`
+Note 1003 select `test`.`t4`.`b` AS `b`,(select avg((`test`.`t2`.`a` + (select min(`test`.`t3`.`a`) AS `min(t3.a)` from `test`.`t3` where (`test`.`t3`.`a` >= `test`.`t4`.`a`)))) AS `avg(t2.a+(select min(t3.a) from t3 where t3.a >= t4.a))` from `test`.`t2`) from `test`.`t4`
select * from t3 where exists (select * from t2 where t2.b=t3.a);
a
7
@@ -314,7 +314,7 @@ NULL UNION RESULT <union2,3> ALL NULL NU
Warnings:
Note 1276 Field or reference 'test.t2.a' of SELECT #2 was resolved in SELECT #1
Note 1276 Field or reference 'test.t2.a' of SELECT #3 was resolved in SELECT #1
-Note 1003 select (select '2' AS `a` from `test`.`t1` where ('2' = `test`.`t2`.`a`) union select `test`.`t5`.`a` AS `a` from `test`.`t5` where (`test`.`t5`.`a` = `test`.`t2`.`a`)) AS `(select a from t1 where t1.a=t2.a union select a from t5 where t5.a=t2.a)`,`test`.`t2`.`a` AS `a` from `test`.`t2`
+Note 1003 select (select '2' AS `a` from `test`.`t1` where ('2' = `test`.`t2`.`a`) union select `test`.`t5`.`a` AS `a` from `test`.`t5` where (`test`.`t5`.`a` = `test`.`t2`.`a`)),`test`.`t2`.`a` AS `a` from `test`.`t2`
select (select a from t1 where t1.a=t2.a union all select a from t5 where t5.a=t2.a), a from t2;
ERROR 21000: Subquery returns more than 1 row
create table t6 (patient_uq int, clinic_uq int, index i1 (clinic_uq));
@@ -367,7 +367,7 @@ id select_type table type possible_keys
2 SUBQUERY t8 const PRIMARY PRIMARY 37 const 1 100.00
3 SUBQUERY t8 const PRIMARY PRIMARY 37 1 100.00 Using index
Warnings:
-Note 1003 select 'joce' AS `pseudo`,(select 'test' AS `email` from `test`.`t8` where 1) AS `(SELECT email FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo='joce'))` from `test`.`t8` where 1
+Note 1003 select 'joce' AS `pseudo`,(select 'test' AS `email` from `test`.`t8` where 1) from `test`.`t8` where 1
SELECT pseudo FROM t8 WHERE pseudo=(SELECT pseudo,email FROM
t8 WHERE pseudo='joce');
ERROR 21000: Operand should contain 1 column(s)
=== modified file 'mysql-test/r/subselect3.result'
--- a/mysql-test/r/subselect3.result 2009-08-31 14:09:09 +0000
+++ b/mysql-test/r/subselect3.result 2009-09-04 22:21:49 +0000
@@ -864,9 +864,7 @@ Level Code Message
Note 1276 Field or reference 'test.t1.a' of SELECT #3 was resolved in SELECT #2
Note 1276 Field or reference 'test.t1.c' of SELECT #3 was resolved in SELECT #2
Error 1054 Unknown column 'c' in 'field list'
-Note 1003 select `c` AS `c` from (select (select count(`test`.`t1`.`a`) AS `COUNT(a)` from (select count(`test`.`t1`.`b`) AS `COUNT(b)` from `test`.`t1`) `x` group by `c`) AS `(SELECT COUNT(a) FROM
-(SELECT COUNT(b) FROM t1) AS x GROUP BY c
-)` from `test`.`t1` group by `test`.`t1`.`b`) `y`
+Note 1003 select `c` AS `c` from (select (select count(`test`.`t1`.`a`) AS `COUNT(a)` from (select count(`test`.`t1`.`b`) AS `COUNT(b)` from `test`.`t1`) `x` group by `c`) from `test`.`t1` group by `test`.`t1`.`b`) `y`
DROP TABLE t1;
End of 5.0 tests
create table t0 (a int);
=== modified file 'mysql-test/r/type_blob.result'
--- a/mysql-test/r/type_blob.result 2009-02-16 11:38:15 +0000
+++ b/mysql-test/r/type_blob.result 2009-09-04 22:21:49 +0000
@@ -519,7 +519,7 @@ coercibility(load_file('MYSQLTEST_VARDIR
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used
Warnings:
-Note 1003 select charset(load_file('MYSQLTEST_VARDIR/std_data/words.dat')) AS `charset(load_file('MYSQLTEST_VARDIR/std_data/words.dat'))`,collation(load_file('MYSQLTEST_VARDIR/std_data/words.dat')) AS `collation(load_file('MYSQLTEST_VARDIR/std_data/words.dat'))`,coercibility(load_file('MYSQLTEST_VARDIR/std_data/words.dat')) AS `coercibility(load_file('MYSQLTEST_VARDIR/std_data/words.dat'))`
+Note 1003 select charset(load_file('MYSQLTEST_VARDIR/std_data/words.dat')),collation(load_file('MYSQLTEST_VARDIR/std_data/words.dat')),coercibility(load_file('MYSQLTEST_VARDIR/std_data/words.dat'))
update t1 set imagem=load_file('MYSQLTEST_VARDIR/std_data/words.dat') where id=1;
select if(imagem is null, "ERROR", "OK"),length(imagem) from t1 where id = 1;
if(imagem is null, "ERROR", "OK") length(imagem)
=== modified file 'mysql-test/r/view.result'
--- a/mysql-test/r/view.result 2009-08-31 14:09:09 +0000
+++ b/mysql-test/r/view.result 2009-09-04 22:21:49 +0000
@@ -3844,6 +3844,21 @@ CREATE VIEW v1 AS SELECT a FROM t1;
ALTER TABLE v1;
DROP VIEW v1;
DROP TABLE t1;
+#
+# Bug#40277 SHOW CREATE VIEW returns invalid SQL
+#
+CREATE VIEW v1 AS SELECT '0000000000 1111111111 2222222222 3333333333 4444444444 5555555555';
+DROP VIEW v1;
+CREATE VIEW v1 AS select '0000000000 1111111111 2222222222 3333333333 4444444444 5555555555';
+DROP VIEW v1;
+CREATE VIEW v1 AS SELECT 'c1', 'c2 ', ' c3', ' c4 ';
+DROP VIEW v1;
+CREATE VIEW v1 AS select 'c1' AS `c1`,'c2 ',' c3' AS `c3`,' c4 ';
+DROP VIEW v1;
+CREATE VIEW v1 AS SELECT (SELECT ' c1 ');
+DROP VIEW v1;
+CREATE VIEW v1 AS select (select ' c1 ') AS `(SELECT ' c1 ')`;
+DROP VIEW v1;
# -----------------------------------------------------------------
# -- End of 5.1 tests.
# -----------------------------------------------------------------
=== modified file 'mysql-test/t/view.test'
--- a/mysql-test/t/view.test 2009-08-31 14:09:09 +0000
+++ b/mysql-test/t/view.test 2009-09-04 22:21:49 +0000
@@ -3869,6 +3869,27 @@ ALTER TABLE v1;
DROP VIEW v1;
DROP TABLE t1;
+--echo #
+--echo # Bug#40277 SHOW CREATE VIEW returns invalid SQL
+--echo #
+
+CREATE VIEW v1 AS SELECT '0000000000 1111111111 2222222222 3333333333 4444444444 5555555555';
+let $query = `SELECT VIEW_DEFINITION FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME = 'v1'`;
+DROP VIEW v1;
+eval CREATE VIEW v1 AS $query;
+DROP VIEW v1;
+
+CREATE VIEW v1 AS SELECT 'c1', 'c2 ', ' c3', ' c4 ';
+let $query = `SELECT VIEW_DEFINITION FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME = 'v1'`;
+DROP VIEW v1;
+eval CREATE VIEW v1 AS $query;
+DROP VIEW v1;
+
+CREATE VIEW v1 AS SELECT (SELECT ' c1 ');
+let $query = `SELECT VIEW_DEFINITION FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME = 'v1'`;
+DROP VIEW v1;
+eval CREATE VIEW v1 AS $query;
+DROP VIEW v1;
--echo # -----------------------------------------------------------------
--echo # -- End of 5.1 tests.
=== modified file 'sql/item.cc'
--- a/sql/item.cc 2009-08-28 16:21:54 +0000
+++ b/sql/item.cc 2009-09-04 22:21:49 +0000
@@ -462,7 +462,16 @@ void Item::print_item_w_name(String *str
{
print(str, query_type);
- if (name)
+ /*
+ Don't print a alias for auto generated invalid column names.
+
+ Although this could be addressed at a higher level, there isn't
+ a short cut way to generate unique and conforming column names
+ that can be used to re-create this column. On top, it would be
+ best if no name would be generated at all, but this doubtful
+ behavior has to be retained for backward compatibility reasons.
+ */
+ if (name && (!is_autogenerated_name || !check_column_name(name)))
{
THD *thd= current_thd;
str->append(STRING_WITH_LEN(" AS "));
Attachment: [text/bzr-bundle] bzr/davi.arnaut@sun.com-20090904222149-2atnegw2b4q6uo4e.bundle
