Bug #20543 select on information_schema strange warnings, view, different schemas/users
Submitted: 19 Jun 2006 17:27 Modified: 18 Aug 2006 12:34
Reporter: Matthias Leich Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Views Severity:S1 (Critical)
Version:5.0 OS:Any
Assigned to: Sergey Glukhov CPU Architecture:Any

[19 Jun 2006 17:27] Matthias Leich
Description:
Some new changes within the privilege system around views
break existing and often applied successful tests 
(NIST testsuite, testscript ist001 warnings around view 
 SUN.TESTREPORT + wrong result counts).
Therefore I set the priority to P1.

Example:
# CREATE the users testdb_1, testdb_2, testdb_3
CREATE USER testdb_1@localhost identified by 'PWD';
...
# GRANT them all rights including grant option to schemas having the name of the user
grant ALL ON testdb_1.* to testdb_1@localhost with grant option;
grant ALL ON testdb_2.* to testdb_2@localhost with grant option;
....
# Establish for all new created users a session
# Switch to session of testdb_1
CREATE SCHEMA testdb_1;
USE testdb_1 ;
CREATE TABLE t1
(f1   CHAR(4),
f2   CHAR(4),
f3  CHAR(3));
CREATE VIEW v1 AS
SELECT f1, f2, f3
FROM t1;
GRANT INSERT ON v1 TO testdb_1@localhost WITH GRANT OPTION;
GRANT INSERT ON v1 TO testdb_2@localhost WITH GRANT OPTION;
GRANT INSERT ON v1 TO testdb_3@localhost WITH GRANT OPTION;
COMMIT WORK;
# Switch to session of testdb_2
CREATE SCHEMA testdb_2;
USE testdb_2 ;
COMMIT WORK;
# Switch to session of testdb_3
CREATE SCHEMA testdb_3;
USE testdb_3 ;
CREATE VIEW v1 AS
SELECT f1, f2, f3
FROM testdb_1.v1;
GRANT INSERT ON v1 TO testdb_1@localhost;
GRANT INSERT ON v1 TO testdb_2@localhost;
GRANT INSERT ON v1 TO testdb_3@localhost;
COMMIT WORK;
# Switch to session of testdb_2
use testdb_2 ;

SELECT * FROM INFORMATION_SCHEMA.COLUMNS B;
TABLE_CATALOG   TABLE_SCHEMA    TABLE_NAME      COLUMN_NAME ...    
NULL    information_schema      CHARACTER_SETS  CHARACTER_SET_NAME      1 ...               
---- result rows with mostly information_schema table ---
....
NULL    testdb_1        v1      f1      1       NULL    YES     char    4       4       ...
NULL    testdb_1        v1      f2      2       NULL    YES     char    4       4       ...
NULL    testdb_1        v1      f3      3       NULL    YES     char    3       3       ...
Warnings:
Warning 1356    View 'testdb_3.v1' references invalid table(s) or column(s) 
        or function(s) or definer/invoker of view lack rights to use them

There are also INFORMATION_SCHEMA SELECTs where the result count is wrong.

My environment:
   - PC(Pentium M, x86-32Bit) with Linux(SuSE 9.3)
   - MySQL compiled (compile-pentium-debug-max) from source
        Version 5.0 ChangeSet@1.2177, 2006-06-14 
                           with and without patch for Bug#20482 applied
        MySQL 5.1 ChangeSet@1.2199, 2006-06-14 

How to repeat:
Please use my attached testscript nistx.test
  copy it to mysql-test/t
  echo "Dummy" > r/nistx.result   # Produce a dummy file with 
                                                   # expected results
  ./mysql-test-run nistx
[19 Jun 2006 17:28] Matthias Leich
test script

Attachment: nistx.test (application/test, text), 4.31 KiB.

[20 Jun 2006 15:08] 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/7932
[27 Jun 2006 10:41] 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/8309
[16 Aug 2006 20:52] Reggie Burnett
Pushed to 5.0.25
[17 Aug 2006 10:30] Jon Stephens
Will this need to be pushed to 5.1 as well? Thanks.
[17 Aug 2006 17:00] Reggie Burnett
Yes.  I would expect it in 5.1.12
[18 Aug 2006 12:34] 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.25 and 5.1.12 changelogs.