MySQL Bugs: #29408: Cannot find view in columns table if the selection contains a function

Bug #29408	Cannot find view in columns table if the selection contains a function
Submitted:	28 Jun 2007 3:37	Modified:	14 Sep 2007 14:29
Reporter:	Edwin Chan	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Information schema	Severity:	S3 (Non-critical)
Version:	5.0.41	OS:	Any (Linux, Windows)
Assigned to:	Sergei Glukhov	CPU Architecture:	Any

Description:
If a view contains a function, the columns of the view is not inserted to the information_schema.columns table.

How to repeat:
1. Create a function that returns int(11), for example:

CREATE FUNCTION `WORKINGDAYS`(date1 DATE, date2 DATE)
    RETURNS int(11)
    NOT DETERMINISTIC
    CONTAINS SQL
    SQL SECURITY DEFINER
    COMMENT ''
BEGIN
  declare x int(11);
  set x=-1;
  select count(workingday.id) into x from oit_web.workingday where workingday.id between date1 and date2;
  RETURN x;
END

2. Create a view that joins a couple of tables, add WORKINGDAYS('2007-01-01','2007-02-01') AS DAYS in the selection

3. Nothing is added to the information_schema.columns table

If the DAYS column is removed, column data is inserted to information_schema.columns without problem

Thank you for the report.

I can not repeat described behavior with test data.

Please provide output of SHOW CREATE VIEW of problem view and output of SHOW CREATE TABLE for all underlying tables.

OLD VIEW is correctly inserted to information_schema.columns, where as none fo the columns in NEW VIEW are in the information_schema table.

-- 
-- Table structure for table `table1`
-- 

CREATE TABLE `table1` (
  `field1` int(11) NOT NULL auto_increment,
  `field2` int(11) NOT NULL,
  PRIMARY KEY  (`field1`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

-- 
-- Dumping data for table `table1`
-- 

INSERT INTO `table1` (`field1`, `field2`) VALUES 
(1, 11),
(2, 23);

-- --------------------------------------------------------

-- 
-- Table structure for table `table2`
-- 

CREATE TABLE `table2` (
  `field1` int(11) NOT NULL auto_increment,
  `field2` date default NULL,
  PRIMARY KEY  (`field1`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

-- 
-- Dumping data for table `table2`
-- 

INSERT INTO `table2` (`field1`, `field2`) VALUES 
(1, '2005-01-01'),
(2, '2005-02-01');

-- --------------------------------------------------------

-- 
-- Table structure for table `table3`
-- 

CREATE TABLE `table3` (
  `field1` int(11) NOT NULL auto_increment,
  `field2` date default NULL,
  PRIMARY KEY  (`field1`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

-- 
-- Dumping data for table `table3`
-- 

INSERT INTO `table3` (`field1`, `field2`) VALUES 
(1, '2006-01-01'),
(2, '2006-02-02');
-- Functions
-- 

CREATE DEFINER=`edwinc`@`%` FUNCTION `function1`() RETURNS tinyint(4)
BEGIN
 return 1;
END

CREATE ALGORITHM=UNDEFINED DEFINER=`edwinc`@`%` SQL SECURITY DEFINER VIEW `old_view` AS 
  select 
    `table1`.`field1` AS `table1_field1`,
    `table1`.`field2` AS `table1_field2`,
    `table2`.`field1` AS `table2_field1`,
    `table2`.`field2` AS `table2_field2`,
    `table3`.`field1` AS `table3_field1`,
    `table3`.`field2` AS `table3_field2`,
  from 
    ((`table1` left join `table2` on((`table1`.`field1` = `table2`.`field1`))) left join `table3` on((`table1`.`field1` = `table3`.`field1`)));

CREATE ALGORITHM=UNDEFINED DEFINER=`edwinc`@`%` SQL SECURITY DEFINER VIEW `new_view` AS 
  select 
    `table1`.`field1` AS `table1_field1`,
    `table1`.`field2` AS `table1_field2`,
    `table2`.`field1` AS `table2_field1`,
    `table2`.`field2` AS `table2_field2`,
    `table3`.`field1` AS `table3_field1`,
    `table3`.`field2` AS `table3_field2`,
    `function1`() AS `test` 
  from 
    ((`table1` left join `table2` on((`table1`.`field1` = `table2`.`field1`))) left join `table3` on((`table1`.`field1` = `table3`.`field1`)));

Thank you for the report.

Verified as described.

To repeat is needed to DO NOT select default database:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.0.41-community-nt MySQL Community Edition (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> select column_name, table_name from information_schema.columns where tabl
e_name ='new_view' and table_schema='test';
Empty set, 1 warning (0.01 sec)

mysql> show warnings;
+---------+------+----------------------+
| Level   | Code | Message              |
+---------+------+----------------------+
| Warning | 1046 | No database selected |
+---------+------+----------------------+
1 row in set (0.00 sec)

mysql> select column_name, table_name from information_schema.columns where table_name ='old_view' and table_schema='test';
+---------------+------------+
| column_name   | table_name |
+---------------+------------+
| table1_field1 | old_view   |
| table1_field2 | old_view   |
| table2_field1 | old_view   |
| table2_field2 | old_view   |
| table3_field1 | old_view   |
| table3_field2 | old_view   |
+---------------+------------+
6 rows in set (0.02 sec)
mysql> use test;
Database changed
mysql> select column_name, table_name from information_schema.columns where table_name ='new_view' and table_schema='test';
+---------------+------------+
| column_name   | table_name |
+---------------+------------+
| table1_field1 | new_view   |
| table1_field2 | new_view   |
| table2_field1 | new_view   |
| table2_field2 | new_view   |
| table3_field1 | new_view   |
| table3_field2 | new_view   |
| test          | new_view   |
+---------------+------------+
7 rows in set (0.00 sec)

Linux version is affected as well.

test case

Attachment: bug29408.test (application/octet-stream, text), 448 bytes.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/32555

ChangeSet@1.2493, 2007-08-15 16:49:36+05:00, gluh@mysql.com +3 -0
  Bug#29408 Cannot find view in columns table if the selection contains a function
  we should use view db name as thread db name when 
  connection is performed without db name

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/32795

ChangeSet@1.2493, 2007-08-21 14:00:16+05:00, gluh@mysql.com +4 -0
  Bug#29408 Cannot find view in columns table if the selection contains a function
  we should use view db name as thread db name when
  connection is performed without db name

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/33573

ChangeSet@1.2512, 2007-09-03 12:22:56+05:00, gluh@mysql.com +4 -0
  Bug#29408 Cannot find view in columns table if the selection contains a function
  Use view db name as thread default database, in order to ensure
  that the view is parsed and prepared correctly.

Pushed into 5.1.23-beta

Pushed into 5.0.50

Thank you for your bug report. This issue has been committed to our source repository of that product and will be incorporated into the next release.

If necessary, you can access the source repository and build the latest available version, including the bug fix. More information about accessing the source trees is available at

    http://dev.mysql.com/doc/en/installing-source.html

Documented bugfix in 5.0.50 and 5.1.23 changelogs as follows:

          If a view used a function in its SELECT
          statement, the columns from the view were not inserted into
          the INFORMATION_SCHEMA.COLUMNS table. (Bug #29408)