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: Sergey Glukhov

[28 Jun 2007 3:37] Edwin Chan
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
[28 Jun 2007 6:47] Sveta Smirnova
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.
[28 Jun 2007 9:47] Edwin Chan
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`)));
[28 Jun 2007 10:46] Sveta Smirnova
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)
[28 Jun 2007 10:50] Sveta Smirnova
Linux version is affected as well.
[28 Jun 2007 10:50] Sveta Smirnova
test case

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

[15 Aug 2007 11:53] Bugs System
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
[21 Aug 2007 9:03] Bugs System
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
[3 Sep 2007 7:29] Bugs System
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.
[14 Sep 2007 7:45] Bugs System
Pushed into 5.1.23-beta
[14 Sep 2007 7:46] Bugs System
Pushed into 5.0.50
[14 Sep 2007 14:29] Jon Stephens
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)