List:Commits« Previous MessageNext Message »
From:Davi Arnaut Date:September 4 2009 10:21pm
Subject:bzr commit into mysql-5.1-bugteam branch (davi:3114) Bug#40277
View as plain text  
# 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
Thread
bzr commit into mysql-5.1-bugteam branch (davi:3114) Bug#40277Davi Arnaut5 Sep
  • Re: bzr commit into mysql-5.1-bugteam branch (davi:3114) Bug#40277Sergei Golubchik17 Nov
    • Re: bzr commit into mysql-5.1-bugteam branch (davi:3114) Bug#40277Davi Arnaut17 Nov
      • Re: bzr commit into mysql-5.1-bugteam branch (davi:3114) Bug#40277Sergei Golubchik19 Nov
        • Re: bzr commit into mysql-5.1-bugteam branch (davi:3114) Bug#40277Davi Arnaut3 Feb
    • Re: bzr commit into mysql-5.1-bugteam branch (davi:3114) Bug#40277Davi Arnaut3 Feb
      • Re: bzr commit into mysql-5.1-bugteam branch (davi:3114) Bug#40277Davi Arnaut3 Feb