Bug #46019 ERROR 1356 When selecting from within another view that has Group By

• Submitted: 7 Jul 2009 19:21
• Modified: 20 Dec 2009 0:50
• Reporter: Reza Rahmaty
• Status: Closed
• Category: MySQL Server: Views
• Severity: S3 (Non-critical)
• Version: 5.1.35, 5.0, 5.1, 5.4 bzr
• OS: Linux (Fedora Core 11)
• Assigned to: Martin Hansson
• CPU Architecture: Any
• Tags: Aggregate Function, create view, GROUP BY, regression

[7 Jul 2009 19:21] Reza Rahmaty

Description:
I have a view that select from another view that uses a Group by with Aggregate functions. when I select from the 2nd view by the Accountant user, Error 1356 is shown!

but select in `GeneralLedgerOnly` runs correctly from mysql logged in as Accountant!

How to repeat:
# mysql -u root

delimiter ;

CREATE TABLE `DocumentsRows` (
  `DFYID` tinyint(3) unsigned NOT NULL,
  `DocumentID` mediumint(8) unsigned NOT NULL,
  `RowID` smallint(5) unsigned NOT NULL,
  `Sub2AccSub1AccGAID` tinyint(3) unsigned NOT NULL default '0',
  `Sub2AccSub1AccID` smallint(5) unsigned NOT NULL default '0',
  `Sub2AccID` smallint(5) unsigned NOT NULL default '0',
  `Description` text NOT NULL,
  `Debit` bigint(20) unsigned NOT NULL,
  `Credit` bigint(20) unsigned NOT NULL,
  PRIMARY KEY (`DFYID`,`DocumentID`,`RowID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

delimiter //

CREATE FUNCTION `AccNeg` (SumOfCredit bigint, SumOfDebit bigint, GeneralAccountID tinyint) RETURNS tinyint unsigned DETERMINISTIC READS SQL DATA SQL SECURITY INVOKER COMMENT 'آیا ماهیت حساب نقض شده است؟'
BEGIN
  DECLARE Cash bigint;
  DECLARE AccType tinyint unsigned default 3;
  SET Cash = SumOfCredit - SumOfDebit;
  /*SELECT `GeneralAccountTypeID` FROM `GeneralAccounts` WHERE `ID` = GeneralAccountID INTO AccType;*/
  SET AccType = IF((IF(Cash >= 0, 2, 1) & AccType) > 0, 0, 1);
  RETURN AccType;
END; //

delimiter ;

CREATE SQL SECURITY INVOKER VIEW `BalanceSheetGeneral` AS
 SELECT `DFYID`, `Sub2AccSub1AccGAID`, Sum(`Debit`) AS `TotalDebit`, Sum(`Credit`) AS `TotalCredit`, Sum(`Credit`) - Sum(`Debit`) AS `Cash`, `Accounting`.`AccNeg`(Sum(`Credit`), Sum(`Debit`), `Sub2AccSub1AccGAID`) AS `AN`
 FROM `DocumentsRows`
 GROUP BY `DFYID`, `Sub2AccSub1AccGAID`
 ORDER BY `DFYID`, `Sub2AccSub1AccGAID`;

CREATE SQL SECURITY INVOKER VIEW `GeneralLedgerOnly` AS
 (SELECT `DFYID`,`Sub2AccSub1AccGAID`, `DocumentID`, `Debit` AS TotalDebit, `Credit` AS TotalCredit, 0 AS `Cash`, 0 AS `AN`
 FROM `DocumentsRows`)
 UNION
 (SELECT `DFYID`, `Sub2AccSub1AccGAID`, 16777215 AS `DocumentID`, `TotalDebit`, `TotalCredit`, `Cash`, `AN`
 FROM `BalanceSheetGeneral`)
 ORDER BY `DFYID`, `Sub2AccSub1AccGAID`, `DocumentID`;

DROP USER 'Accountant'@'%' /*IF EXISTS*/;
CREATE USER 'Accountant'@'%';

GRANT SELECT ON TABLE `DocumentsRows` TO 'Accountant'@'%';
GRANT EXECUTE ON FUNCTION `AccNeg` TO 'Accountant'@'%';
GRANT SELECT, SHOW VIEW ON TABLE `BalanceSheetGeneral` TO 'Accountant'@'%';
GRANT SELECT, SHOW VIEW ON TABLE `GeneralLedgerOnly` TO 'Accountant'@'%';

# mysql -u Accountant

select * from `GeneralLedgerOnly`;

[8 Jul 2009 7:08] Sveta Smirnova

Thank you for the report.

Verified as described.

Bug was introduced after version 5.1.9

[14 Jul 2009 9:44] Sveta Smirnova

test case

Attachment: bug46019.test (application/octet-stream, text), 2.50 KiB.

[14 Jul 2009 9:46] Sveta Smirnova

log file

Attachment: bug46019.log (application/octet-stream, text), 2.49 KiB.

[14 Jul 2009 9:46] Sveta Smirnova

Test case failed with:

CURRENT_TEST: main.bug46019
mysqltest: At line 69: query 'select * from `GeneralLedgerOnly`' failed: 1356: View 'db1.generalledgeronly' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them

[14 Jul 2009 16: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/78656

2781 Georgi Kodinov	2009-07-14
      Bug #46019: ERROR 1356 When selecting from within another
      view that has Group By
            
      Table access rights checking function check_grant() assumed
      that no view is opened when it's called.
      This is not true with nested views where the inner view
      needs materialization. In this case the view is already 
      materialized when check_grant() is called for it.
      This caused check_grant() to not look for table level
      grants on the materialized view table.
      Fixed by checking if a view is already materialized and if 
      it is check table level grants using the original table name
      (not the ones of the materialized temp table).

[14 Jul 2009 17:09] 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/78670

2805 Georgi Kodinov	2009-07-14
      Bug #46019: ERROR 1356 When selecting from within another
      view that has Group By
      
      Table access rights checking function check_grant() assumed
      that no view is opened when it's called.
      This is not true with nested views where the inner view
      needs materialization. In this case the view is already 
      materialized when check_grant() is called for it.
      This caused check_grant() to not look for table level
      grants on the materialized view table.
      Fixed by checking if a view is already materialized and if 
      it is check table level grants using the original table name
      (not the ones of the materialized temp table).

[19 Aug 2009 12:15] 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/81065

2781 Georgi Kodinov	2009-08-19
      Bug #46019: ERROR 1356 When selecting from within another
      view that has Group By
            
      Table access rights checking function check_grant() assumed
      that no view is opened when it's called.
      This is not true with nested views where the inner view
      needs materialization. In this case the view is already 
      materialized when check_grant() is called for it.
      This caused check_grant() to not look for table level
      grants on the materialized view table.
      Fixed by checking if a view is already materialized and if 
      it is check table level grants using the original table name
      (not the ones of the materialized temp table).

[19 Aug 2009 15:37] 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/81097

2791 Georgi Kodinov	2009-08-19
      Bug #46019: ERROR 1356 When selecting from within another
      view that has Group By
            
      Table access rights checking function check_grant() assumed
      that no view is opened when it's called.
      This is not true with nested views where the inner view
      needs materialization. In this case the view is already 
      materialized when check_grant() is called for it.
      This caused check_grant() to not look for table level
      grants on the materialized view table.
      Fixed by checking if a view is already materialized and if 
      it is check table level grants using the original table name
      (not the ones of the materialized temp table).

[20 Aug 2009 14:12] 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/81176

3074 Georgi Kodinov	2009-08-20 [merge]
      merge of bug #46019 to 5.1-bugteam

[21 Aug 2009 14:11] 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/81302

2792 Georgi Kodinov	2009-08-21
      Revert of the fix for bug #46019.

[21 Aug 2009 14:43] 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/81309

3077 Georgi Kodinov	2009-08-21 [merge]
      reverted the fix for bug #46019 from 5.1-bugteam

[1 Sep 2009 14:43] Martin Hansson

Can't fix this bug until Bug#35996 is fixed.

[2 Sep 2009 10:25] Bugs System

Pushed into 5.0.86 (revid:[e-mail]-20090902102337-n5rw8227wwp5cpx8) (version source revid:[e-mail]-20090821141055-qy0hzbrj0kgkhirs) (merge vers: 5.0.86) (pib:11)

[2 Sep 2009 16:42] Bugs System

Pushed into 5.1.39 (revid:[e-mail]-20090902154533-8actmfcsjfqovgsb) (version source revid:[e-mail]-20090821144148-lo8rbehf04x094bz) (merge vers: 5.1.39) (pib:11)

[14 Sep 2009 16:04] Bugs System

Pushed into 5.4.4-alpha (revid:[e-mail]-20090914155317-m1g9wodmndzdj4l1) (version source revid:[e-mail]-20090914155317-m1g9wodmndzdj4l1) (merge vers: 5.4.4-alpha) (pib:11)

[30 Sep 2009 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/85195

3142 Martin Hansson	2009-09-30
      Bug#46019: ERROR 1356 When selecting from within another
      view that has Group By
      
      In order to fix bug number 36086 it was necessary to add
      some hacks in order to make the tangled error handling work
      as before: Table level access checking was turned off for
      materialized views since if it were done, the error message
      would be downgraded to a warning by code that was meant to
      handle a different scenario, but could not distinguish the
      two as it explicitly manipulated error messages. It makes
      more sense to look at the reson for the error and take
      appropriate action.
      
      After view error handling got straightened out by bug number
      35996 we don't need the hack anymore and the bug goes away.
     @ mysql-test/r/view_grant.result
        Bug#46019: Test result.
     @ mysql-test/t/view_grant.test
        Bug#46019: Test case.
     @ sql/sql_parse.cc
        Bug#46019: fix.

[1 Oct 2009 5:59] Bugs System

Pushed into 5.1.39-ndb-6.3.28 (revid:[e-mail]-20091001055605-ap2kiaarr7p40mmv) (version source revid:[e-mail]-20091001055605-ap2kiaarr7p40mmv) (merge vers: 5.1.39-ndb-6.3.28) (pib:11)

[1 Oct 2009 7:25] Bugs System

Pushed into 5.1.39-ndb-7.0.9 (revid:[e-mail]-20091001072547-kv17uu06hfjhgjay) (version source revid:[e-mail]-20091001071652-irejtnumzbpsbgk2) (merge vers: 5.1.39-ndb-7.0.9) (pib:11)

[1 Oct 2009 13:25] Bugs System

Pushed into 5.1.39-ndb-7.1.0 (revid:[e-mail]-20091001123013-g9ob2tsyctpw6zs0) (version source revid:[e-mail]-20091001123013-g9ob2tsyctpw6zs0) (merge vers: 5.1.39-ndb-7.1.0) (pib:11)

[5 Oct 2009 10:50] Bugs System

Pushed into 5.1.39-ndb-6.2.19 (revid:[e-mail]-20091005103850-dwij2dojwpvf5hi6) (version source revid:[e-mail]-20090930185117-bhud4ek1y0hsj1nv) (merge vers: 5.1.39-ndb-6.2.19) (pib:11)

[12 Oct 2009 12: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/86569

3142 Martin Hansson	2009-10-12
      Bug#46019: ERROR 1356 When selecting from within another
                 view that has Group By
      
      When SELECT'ing from a view that mentions another,
      materialized, view, access was being denied. The issue was
      resolved by lifting a special case which avoided such access
      checking in check_single_table_access. In the past, this was
      necessary since if such a check were performed, the error
      message would be downgraded to a warning in the case of SHOW
      CREATE VIEW. The dowgrading of errors was meant to handle
      only that scenario, but could not distinguish the two as it
      read only the error messages.
      
      The special case was needed in the fix of bug no 36086.
      Before that, views were confused with derived tables.
      
      After bug no 35996 was fixed, the manipulation of errors
      during SHOW CREATE VIEW execution is not dependent on the
      actual error messages in the queue, it rather looks at the
      actual cause of the error and takes appropriate
      action. Hence the aforementioned special case is now
      superfluous and the bug is fixed.
     @ mysql-test/r/view_grant.result
        Bug#46019: Test result.
     @ mysql-test/t/view_grant.test
        Bug#46019: Test case.
     @ sql/sql_parse.cc
        Bug#46019: fix.

[12 Oct 2009 12:34] 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/86570

3142 Martin Hansson	2009-10-12
      Bug#46019: ERROR 1356 When selecting from within another
                 view that has Group By
      
      When SELECT'ing from a view that mentions another,
      materialized, view, access was being denied. The issue was
      resolved by lifting a special case which avoided such access
      checking in check_single_table_access. In the past, this was
      necessary since if such a check were performed, the error
      message would be downgraded to a warning in the case of SHOW
      CREATE VIEW. The downgrading of errors was meant to handle
      only that scenario, but could not distinguish the two as it
      read only the error messages.
      
      The special case was needed in the fix of bug no 36086.
      Before that, views were confused with derived tables.
      
      After bug no 35996 was fixed, the manipulation of errors
      during SHOW CREATE VIEW execution is not dependent on the
      actual error messages in the queue, it rather looks at the
      actual cause of the error and takes appropriate
      action. Hence the aforementioned special case is now
      superfluous and the bug is fixed.
     @ mysql-test/r/view_grant.result
        Bug#46019: Test result.
     @ mysql-test/t/view_grant.test
        Bug#46019: Test case.
     @ sql/sql_parse.cc
        Bug#46019: fix.

[13 Oct 2009 9:55] 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/86684

3162 Martin Hansson	2009-10-13 [merge]
      Merge of Bug#46019

[15 Oct 2009 8:38] Martin Hansson

It appears both reviewers are done.

[16 Oct 2009 11:12] 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/87105

3177 Martin Hansson	2009-10-16
      Bug#46019: ERROR 1356 When selecting from within another
                  view that has Group By
            
      When SELECT'ing from a view that mentions another,
      materialized, view, access was being denied. The issue was
      resolved by lifting a special case which avoided such access
      checking in check_single_table_access. In the past, this was
      necessary since if such a check were performed, the error
      message would be downgraded to a warning in the case of SHOW
      CREATE VIEW. The downgrading of errors was meant to handle
      only that scenario, but could not distinguish the two as it
      read only the error messages.
            
      The special case was needed in the fix of bug no 36086.
      Before that, views were confused with derived tables.
            
      After bug no 35996 was fixed, the manipulation of errors
      during SHOW CREATE VIEW execution is not dependent on the
      actual error messages in the queue, it rather looks at the
      actual cause of the error and takes appropriate
      action. Hence the aforementioned special case is now
      superfluous and the bug is fixed.
     @ mysql-test/r/view_grant.result
        Bug#46019: Test result.
     @ mysql-test/t/view_grant.test
        Bug#46019: Test case.
     @ sql/sql_parse.cc
        Bug#46019: fix.

[16 Oct 2009 11:21] 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/87107

3665 Martin Hansson	2009-10-16 [merge]
      Merge 5.1 -> pe
      - Bug#46019
      - Upmerge a merge changeset - no contents change.

[22 Oct 2009 6:35] Bugs System

Pushed into 6.0.14-alpha (revid:[e-mail]-20091022063126-l0qzirh9xyhp0bpc) (version source revid:[e-mail]-20091019135554-s1pvptt6i750lfhv) (merge vers: 6.0.14-alpha) (pib:13)

[4 Nov 2009 9:25] Bugs System

Pushed into 5.1.41 (revid:[e-mail]-20091104092152-qz96bzlf2o1japwc) (version source revid:[e-mail]-20091016131121-z9puhuiqhl2mucfi) (merge vers: 5.1.41) (pib:13)

[11 Nov 2009 7:01] Bugs System

Pushed into 5.5.0-beta (revid:[e-mail]-20091109115615-nuohp02h8mdrz8m2) (version source revid:[e-mail]-20091105084806-dalnvu9fkwdfei19) (merge vers: 5.5.0-beta) (pib:13)

[18 Dec 2009 10:32] Bugs System

Pushed into 5.1.41-ndb-7.1.0 (revid:[e-mail]-20091218102229-64tk47xonu3dv6r6) (version source revid:[e-mail]-20091218095730-26gwjidfsdw45dto) (merge vers: 5.1.41-ndb-7.1.0) (pib:15)

[18 Dec 2009 10:48] Bugs System

Pushed into 5.1.41-ndb-6.2.19 (revid:[e-mail]-20091218100224-vtzr0fahhsuhjsmt) (version source revid:[e-mail]-20091217101452-qwzyaig50w74xmye) (merge vers: 5.1.41-ndb-6.2.19) (pib:15)

[18 Dec 2009 11:03] Bugs System

Pushed into 5.1.41-ndb-6.3.31 (revid:[e-mail]-20091218100616-75d9tek96o6ob6k0) (version source revid:[e-mail]-20091217154335-290no45qdins5bwo) (merge vers: 5.1.41-ndb-6.3.31) (pib:15)

[18 Dec 2009 11:18] Bugs System

Pushed into 5.1.41-ndb-7.0.11 (revid:[e-mail]-20091218101303-ga32mrnr15jsa606) (version source revid:[e-mail]-20091218064304-ezreonykd9f4kelk) (merge vers: 5.1.41-ndb-7.0.11) (pib:15)