Description:
Hi!
I'm not sure if this is a bug or not, but I have the same in the 5.0.51a,5.1.24rc versions.
I have a proc:
#----------------------------------------------------------------------------
CREATE PROCEDURE prepare_int_funccall(
IN inputtablename VARCHAR(30),
IN inputtablecolu VARCHAR(30),
IN startdate int,
IN enddate int,
IN ob_direction int,
IN viewname VARCHAR(30))
BEGIN
SET @ipts:=startdate;
SET @ipte:=enddate;
SET @drops=CONCAT('DROP VIEW IF EXISTS ', viewname);
SET @stmnt=CONCAT('CREATE OR REPLACE VIEW ', viewname);
SET @stmnt=CONCAT(@stmnt, ' AS SELECT s_dateint,');
SET @stmnt=CONCAT(@stmnt, inputtablecolu);
SET @stmnt=CONCAT(@stmnt, ' AS inp_fv FROM ');
SET @stmnt=CONCAT(@stmnt, inputtablename);
IF startdate>=0 THEN
SET @stmnt=CONCAT(@stmnt, ' WHERE s_dateint>=', startdate);
SET @stmnt=CONCAT(@stmnt, ' AND s_dateint<=', enddate);
END IF;
IF ob_direction>=0 THEN
SET @stmnt=CONCAT(@stmnt, ' ORDER BY s_dateint');
ELSE
SET @stmnt=CONCAT(@stmnt, ' ORDER BY s_dateint DESC');
END IF;
SELECT @stmnt;
# PREPARE stmt FROM @drops;
# EXECUTE stmt;
# DEALLOCATE PREPARE stmt;
PREPARE stmt FROM @stmnt;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END;
//
there is a simple proc showlinregbaseview:
CREATE PROCEDURE showlinregbaseview()
READS SQL DATA
BEGIN
show create view linregbase_view;
END;
//
these procs above are called form this (and the problem is described inline):
CREATE PROCEDURE viewcreate_test()
BEGIN
call prepare_int_funccall('table','col1',-1,-1,1,
'linregbase_view');
call showlinregbaseview();
show create view linregbase_view;
#everything is OK. both outputs show that the view is selecting the col1
call prepare_int_funccall('table','col2',-1,-1,1,
'linregbase_view');
call showlinregbaseview();
show create view linregbase_view;
#!!!!!bug?: the showlinregbaseview still returns a view selecting col1,
#but the the show in the viewcreate proc is OK (col2)
call prepare_int_funccall('table','col3',-1,-1,1,
'linregbase_view');
call showlinregbaseview();
show create view linregbase_view;
#!!!!!bug?: the showlinregbaseview still returns a view selecting col1,
#but the the show in the viewcreate proc is OK (col3)
call prepare_int_funccall('table','col4',-1,-1,1,
'linregbase_view');
call showlinregbaseview();
show create view linregbase_view;
#!!!!!bug?: the showlinregbaseview still returns a view selecting col1,
#but the the show in the viewcreate proc is OK (col4)
END;
//
Thanks,
REgards,
Sandor
The problem with the show create view output is that it is used when I do a select form the view too!!!
How to repeat:
create a table, poppulate with some dummy values
mysql> desc viewtesttable;
+-----------+----------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+----------------+------+-----+---------+-------+
| s_dateint | int(11) | NO | PRI | NULL | |
| col1 | float unsigned | NO | | NULL | |
| col2 | float unsigned | NO | | NULL | |
| col3 | float unsigned | NO | | NULL | |
| col4 | float unsigned | NO | | NULL | |
+-----------+----------------+------+-----+---------+-------+
5 rows in set (0.03 sec)
create the procedures above, and call viewcreate_test()
The logfile is the following:
col1: (OK)
mysql> call viewcreate_test();
+-------------------------------------------------------------------------------
----------------------------------+
| @stmnt
|
+-------------------------------------------------------------------------------
----------------------------------+
| CREATE OR REPLACE VIEW linregbase_view AS SELECT s_dateint,col1 AS inp_fv FROM
viewtesttable ORDER BY s_dateint |
+-------------------------------------------------------------------------------
----------------------------------+
1 row in set (0.02 sec)
+-----------------+-------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
------------------------+----------------------+----------------------+
| View | Create View
| character_set_client | collation_connection |
+-----------------+-------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
------------------------+----------------------+----------------------+
| linregbase_view | CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SE
CURITY DEFINER VIEW `linregbase_view` AS select `viewtesttable`.`s_dateint` AS `
s_dateint`,`viewtesttable`.`col1` AS `inp_fv` from `viewtesttable` order by `vie
wtesttable`.`s_dateint` | latin1 | latin1_swedish_ci |
+-----------------+-------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
------------------------+----------------------+----------------------+
1 row in set (0.06 sec)
+-----------------+-------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
------------------------+----------------------+----------------------+
| View | Create View
| character_set_client | collation_connection |
+-----------------+-------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
------------------------+----------------------+----------------------+
| linregbase_view | CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SE
CURITY DEFINER VIEW `linregbase_view` AS select `viewtesttable`.`s_dateint` AS `
s_dateint`,`viewtesttable`.`col1` AS `inp_fv` from `viewtesttable` order by `vie
wtesttable`.`s_dateint` | latin1 | latin1_swedish_ci |
+-----------------+-------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
------------------------+----------------------+----------------------+
1 row in set (0.11 sec)
col2:(notOK)
+-------------------------------------------------------------------------------
----------------------------------+
| @stmnt
|
+-------------------------------------------------------------------------------
----------------------------------+
| CREATE OR REPLACE VIEW linregbase_view AS SELECT s_dateint,col2 AS inp_fv FROM
viewtesttable ORDER BY s_dateint |
+-------------------------------------------------------------------------------
----------------------------------+
1 row in set (0.16 sec)
+-----------------+-------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
------------------------+----------------------+----------------------+
| View | Create View
| character_set_client | collation_connection |
+-----------------+-------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
------------------------+----------------------+----------------------+
| linregbase_view | CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SE
CURITY DEFINER VIEW `linregbase_view` AS select `viewtesttable`.`s_dateint` AS `
s_dateint`,`viewtesttable`.`col1` AS `inp_fv` from `viewtesttable` order by `vie
wtesttable`.`s_dateint` | latin1 | latin1_swedish_ci |
+-----------------+-------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
------------------------+----------------------+----------------------+
1 row in set (0.17 sec)
+-----------------+-------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
------------------------+----------------------+----------------------+
| View | Create View
| character_set_client | collation_connection |
+-----------------+-------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
------------------------+----------------------+----------------------+
| linregbase_view | CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SE
CURITY DEFINER VIEW `linregbase_view` AS select `viewtesttable`.`s_dateint` AS `
s_dateint`,`viewtesttable`.`col2` AS `inp_fv` from `viewtesttable` order by `vie
wtesttable`.`s_dateint` | latin1 | latin1_swedish_ci |
+-----------------+-------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
------------------------+----------------------+----------------------+
1 row in set (0.22 sec)
col3,col4: so on...
Suggested fix:
??? I'm not a developer, mybe some context in the proc showlinregbaseview() is not updated...
Description: Hi! I'm not sure if this is a bug or not, but I have the same in the 5.0.51a,5.1.24rc versions. I have a proc: #---------------------------------------------------------------------------- CREATE PROCEDURE prepare_int_funccall( IN inputtablename VARCHAR(30), IN inputtablecolu VARCHAR(30), IN startdate int, IN enddate int, IN ob_direction int, IN viewname VARCHAR(30)) BEGIN SET @ipts:=startdate; SET @ipte:=enddate; SET @drops=CONCAT('DROP VIEW IF EXISTS ', viewname); SET @stmnt=CONCAT('CREATE OR REPLACE VIEW ', viewname); SET @stmnt=CONCAT(@stmnt, ' AS SELECT s_dateint,'); SET @stmnt=CONCAT(@stmnt, inputtablecolu); SET @stmnt=CONCAT(@stmnt, ' AS inp_fv FROM '); SET @stmnt=CONCAT(@stmnt, inputtablename); IF startdate>=0 THEN SET @stmnt=CONCAT(@stmnt, ' WHERE s_dateint>=', startdate); SET @stmnt=CONCAT(@stmnt, ' AND s_dateint<=', enddate); END IF; IF ob_direction>=0 THEN SET @stmnt=CONCAT(@stmnt, ' ORDER BY s_dateint'); ELSE SET @stmnt=CONCAT(@stmnt, ' ORDER BY s_dateint DESC'); END IF; SELECT @stmnt; # PREPARE stmt FROM @drops; # EXECUTE stmt; # DEALLOCATE PREPARE stmt; PREPARE stmt FROM @stmnt; EXECUTE stmt; DEALLOCATE PREPARE stmt; END; // there is a simple proc showlinregbaseview: CREATE PROCEDURE showlinregbaseview() READS SQL DATA BEGIN show create view linregbase_view; END; // these procs above are called form this (and the problem is described inline): CREATE PROCEDURE viewcreate_test() BEGIN call prepare_int_funccall('table','col1',-1,-1,1, 'linregbase_view'); call showlinregbaseview(); show create view linregbase_view; #everything is OK. both outputs show that the view is selecting the col1 call prepare_int_funccall('table','col2',-1,-1,1, 'linregbase_view'); call showlinregbaseview(); show create view linregbase_view; #!!!!!bug?: the showlinregbaseview still returns a view selecting col1, #but the the show in the viewcreate proc is OK (col2) call prepare_int_funccall('table','col3',-1,-1,1, 'linregbase_view'); call showlinregbaseview(); show create view linregbase_view; #!!!!!bug?: the showlinregbaseview still returns a view selecting col1, #but the the show in the viewcreate proc is OK (col3) call prepare_int_funccall('table','col4',-1,-1,1, 'linregbase_view'); call showlinregbaseview(); show create view linregbase_view; #!!!!!bug?: the showlinregbaseview still returns a view selecting col1, #but the the show in the viewcreate proc is OK (col4) END; // Thanks, REgards, Sandor The problem with the show create view output is that it is used when I do a select form the view too!!! How to repeat: create a table, poppulate with some dummy values mysql> desc viewtesttable; +-----------+----------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------+----------------+------+-----+---------+-------+ | s_dateint | int(11) | NO | PRI | NULL | | | col1 | float unsigned | NO | | NULL | | | col2 | float unsigned | NO | | NULL | | | col3 | float unsigned | NO | | NULL | | | col4 | float unsigned | NO | | NULL | | +-----------+----------------+------+-----+---------+-------+ 5 rows in set (0.03 sec) create the procedures above, and call viewcreate_test() The logfile is the following: col1: (OK) mysql> call viewcreate_test(); +------------------------------------------------------------------------------- ----------------------------------+ | @stmnt | +------------------------------------------------------------------------------- ----------------------------------+ | CREATE OR REPLACE VIEW linregbase_view AS SELECT s_dateint,col1 AS inp_fv FROM viewtesttable ORDER BY s_dateint | +------------------------------------------------------------------------------- ----------------------------------+ 1 row in set (0.02 sec) +-----------------+------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ------------------------+----------------------+----------------------+ | View | Create View | character_set_client | collation_connection | +-----------------+------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ------------------------+----------------------+----------------------+ | linregbase_view | CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SE CURITY DEFINER VIEW `linregbase_view` AS select `viewtesttable`.`s_dateint` AS ` s_dateint`,`viewtesttable`.`col1` AS `inp_fv` from `viewtesttable` order by `vie wtesttable`.`s_dateint` | latin1 | latin1_swedish_ci | +-----------------+------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ------------------------+----------------------+----------------------+ 1 row in set (0.06 sec) +-----------------+------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ------------------------+----------------------+----------------------+ | View | Create View | character_set_client | collation_connection | +-----------------+------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ------------------------+----------------------+----------------------+ | linregbase_view | CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SE CURITY DEFINER VIEW `linregbase_view` AS select `viewtesttable`.`s_dateint` AS ` s_dateint`,`viewtesttable`.`col1` AS `inp_fv` from `viewtesttable` order by `vie wtesttable`.`s_dateint` | latin1 | latin1_swedish_ci | +-----------------+------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ------------------------+----------------------+----------------------+ 1 row in set (0.11 sec) col2:(notOK) +------------------------------------------------------------------------------- ----------------------------------+ | @stmnt | +------------------------------------------------------------------------------- ----------------------------------+ | CREATE OR REPLACE VIEW linregbase_view AS SELECT s_dateint,col2 AS inp_fv FROM viewtesttable ORDER BY s_dateint | +------------------------------------------------------------------------------- ----------------------------------+ 1 row in set (0.16 sec) +-----------------+------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ------------------------+----------------------+----------------------+ | View | Create View | character_set_client | collation_connection | +-----------------+------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ------------------------+----------------------+----------------------+ | linregbase_view | CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SE CURITY DEFINER VIEW `linregbase_view` AS select `viewtesttable`.`s_dateint` AS ` s_dateint`,`viewtesttable`.`col1` AS `inp_fv` from `viewtesttable` order by `vie wtesttable`.`s_dateint` | latin1 | latin1_swedish_ci | +-----------------+------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ------------------------+----------------------+----------------------+ 1 row in set (0.17 sec) +-----------------+------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ------------------------+----------------------+----------------------+ | View | Create View | character_set_client | collation_connection | +-----------------+------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ------------------------+----------------------+----------------------+ | linregbase_view | CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SE CURITY DEFINER VIEW `linregbase_view` AS select `viewtesttable`.`s_dateint` AS ` s_dateint`,`viewtesttable`.`col2` AS `inp_fv` from `viewtesttable` order by `vie wtesttable`.`s_dateint` | latin1 | latin1_swedish_ci | +-----------------+------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ------------------------+----------------------+----------------------+ 1 row in set (0.22 sec) col3,col4: so on... Suggested fix: ??? I'm not a developer, mybe some context in the proc showlinregbaseview() is not updated...