MySQL Bugs: #40825: Error 1356 while selecting from a view with a "HAVING" clause though query works

Bug #40825	Error 1356 while selecting from a view with a "HAVING" clause though query works
Submitted:	18 Nov 2008 15:32	Modified:	25 Jun 2009 22:30
Reporter:	Matthias Nagel	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Views	Severity:	S2 (Serious)
Version:	5.0.70, 5.0, 5.1, 6.0 bzr	OS:	Linux
Assigned to:	Gleb Shchepa	CPU Architecture:	Any
Tags:	Bug #21809, Error 1356

Description:
This is more or less a re-opened flavor of the already closed bug #21809.

Selecting from a view that has an "GROUP BY ... HAVING ..." clause fails with error 1356, although the underlying SELECT statement works. If you leave out the HAVING clause but keep the GROUP BY clause the view works, too.

Please excuse the long winded view definition, but it seems that the definition must exceed a certain level of complexity before the error arises.

How to repeat:
CREATE TABLE Resident (
  Id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  FamilyName VARCHAR(255) NOT NULL,
  FirstName VARCHAR(255) NOT NULL,
  PRIMARY KEY ( Id )
);

CREATE TABLE RoomAllocation (
  Id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  ResidentId INTEGER UNSIGNED NOT NULL,
  RoomNo SMALLINT UNSIGNED NOT NULL,
  `From` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `To` TIMESTAMP NULL,
  PRIMARY KEY ( Id ),
  FOREIGN KEY IResidentId ( ResidentId )
    REFERENCES Resident ( Id ),
  INDEX ITimeSpan ( `From`, `To` ),
  INDEX ITo ( `To` ),
  INDEX IRoomNo ( RoomNo ),
  KEY IResidentIdFrom ( ResidentId, `From` ),
  KEY IRoomNoFrom ( RoomNo, `From` )
);

CREATE TABLE ResidentAccount (
  Id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  AccountingId INTEGER UNSIGNED NOT NULL,
  ResidentId INTEGER UNSIGNED NOT NULL,
  Debit DECIMAL(6,2) UNSIGNED,
  Credit DECIMAL(6,2) UNSIGNED,
  PRIMARY KEY ( Id ),
  FOREIGN KEY IAccountingId ( AccountingId )
    REFERENCES Accounting ( Id ),
  FOREIGN KEY IResidentId ( ResidentId )
    REFERENCES Resident ( Id )
);

CREATE TABLE `Case` (
  Id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  Description VARCHAR(255) NOT NULL,
  Capacity SMALLINT UNSIGNED NOT NULL,
  Deposit DECIMAL(4,2) UNSIGNED NOT NULL DEFAULT 0,
  PRIMARY KEY ( Id )
);

CREATE TABLE CaseInventory (
  Id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  AccountingId INTEGER UNSIGNED NOT NULL,
  CaseId INTEGER UNSIGNED NOT NULL,
  Debit SMALLINT UNSIGNED
    COMMENT 'Number of cases',
  Credit SMALLINT UNSIGNED
    COMMENT 'Number of cases',
  PRIMARY KEY ( Id ),
  FOREIGN KEY IAccountingId ( AccountingId )
    REFERENCES Accounting ( Id ),
  FOREIGN KEY ICaseId ( CaseId )
    REFERENCES `Case` ( Id )
);

Now the view that fails:

CREATE VIEW CurrentResidentBalance ( ResidentId, FamilyName,
                                     FirstName, RoomNo, Debit,
                                     Credit ) AS
  SELECT Resident.Id, FamilyName, FirstName, RoomNo,
    IF( IFNULL( SUM( Credit ), 0 ) - IFNULL( SUM( Debit ), 0 ) > 0,
      IFNULL( SUM( Credit ), 0 ) - IFNULL( SUM( Debit ), 0 ),
      NULL ) AS TotalDebit,
   IF( IFNULL( SUM( Debit ), 0 ) - IFNULL( SUM( Credit ), 0 ) > 0,
     IFNULL( SUM( Debit ), 0 ) - IFNULL( SUM( Credit ), 0 ),
     NULL ) AS TotalCredit
   FROM ( ResidentAccount
   JOIN Resident
   ON ( ResidentAccount.ResidentId = Resident.Id ) )
   LEFT JOIN RoomAllocation
   ON ( Resident.Id = RoomAllocation.ResidentId )
   WHERE ( NOW() BETWEEN `From` AND `To` ) OR
     ( NOW() > `From` AND `To` IS NULL )
   GROUP BY Resident.Id
   HAVING ( TotalDebit <> 0 OR TotalCredit <> 0 )
   ORDER BY RoomNo;

Now a similar but working view as above, but without the HAVING clause:

CREATE VIEW CurrentResidentBalance ( ResidentId, FamilyName,
                                     FirstName, RoomNo, Debit,
                                     Credit ) AS
  SELECT Resident.Id, FamilyName, FirstName, RoomNo,
    IF( IFNULL( SUM( Credit ), 0 ) - IFNULL( SUM( Debit ), 0 ) > 0,
      IFNULL( SUM( Credit ), 0 ) - IFNULL( SUM( Debit ), 0 ),
      NULL ),
   IF( IFNULL( SUM( Debit ), 0 ) - IFNULL( SUM( Credit ), 0 ) > 0,
     IFNULL( SUM( Debit ), 0 ) - IFNULL( SUM( Credit ), 0 ),
     NULL )
   FROM ( ResidentAccount
   JOIN Resident
   ON ( ResidentAccount.ResidentId = Resident.Id ) )
   LEFT JOIN RoomAllocation
   ON ( Resident.Id = RoomAllocation.ResidentId )
   WHERE ( NOW() BETWEEN `From` AND `To` ) OR
     ( NOW() > `From` AND `To` IS NULL )
   GROUP BY Resident.Id
   ORDER BY RoomNo;

Now ,a less complex view that works although there is a HAVING clause

CREATE VIEW CurrentCaseBalance (
    CaseId, Description, TotalBalance, MonetaryBalance ) AS
  SELECT Inv.CaseId, Description,
    IFNULL( SUM( Debit ), 0 ) - IFNULL( SUM( Credit ), 0 )
      AS TotalBalance,
    ( IFNULL( SUM( Debit ), 0 ) - IFNULL( SUM( Credit ), 0 ) )
      * Deposit
  FROM CaseInventory AS Inv
  JOIN `Case`
  ON Inv.CaseId = `Case`.Id
  GROUP BY CaseId
  HAVING TotalBalance <> 0
  ORDER BY Description;

Thank you for a problem report. Please, try to repeat with a newer version, 5.0.67 at least. In case of the same problem, please, send a self-contained test case (your tables have some unresolved foreign keys).

Same behaviour with 5.0.70.

Sorry, I forgot one table:

CREATE TABLE Accounting (
  Id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  Time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  VoucherNo CHAR(12) NULL,
  Remark VARCHAR(255) NULL,
  PRIMARY KEY ( Id ),
  KEY ITime ( Time ),
  UNIQUE INDEX IVoucherNo( VoucherNo )
);

Stragne, but I can not repeat this with 5.0.70:

C:\Program Files\MySQL\MySQL Server 5.0\bin>mysql -uroot -proot -P3308 test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 5.0.70-enterprise-gpl-nt MySQL Enterprise Server (GPL)

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

mysql> CREATE TABLE Accounting (
    ->   Id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
    ->   Time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    ->   VoucherNo CHAR(12) NULL,
    ->   Remark VARCHAR(255) NULL,
    ->   PRIMARY KEY ( Id ),
    ->   KEY ITime ( Time ),
    ->   UNIQUE INDEX IVoucherNo( VoucherNo )
    -> );
Query OK, 0 rows affected (0.24 sec)

mysql> CREATE TABLE ResidentAccount (
    ->   Id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
    ->   AccountingId INTEGER UNSIGNED NOT NULL,
    ->   ResidentId INTEGER UNSIGNED NOT NULL,
    ->   Debit DECIMAL(6,2) UNSIGNED,
    ->   Credit DECIMAL(6,2) UNSIGNED,
    ->   PRIMARY KEY ( Id ),
    ->   FOREIGN KEY IAccountingId ( AccountingId )
    ->     REFERENCES Accounting ( Id ),
    ->   FOREIGN KEY IResidentId ( ResidentId )
    ->     REFERENCES Resident ( Id )
    -> );
Query OK, 0 rows affected (0.14 sec)

mysql> CREATE TABLE CaseInventory (
    ->   Id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
    ->   AccountingId INTEGER UNSIGNED NOT NULL,
    ->   CaseId INTEGER UNSIGNED NOT NULL,
    ->   Debit SMALLINT UNSIGNED
    ->     COMMENT 'Number of cases',
    ->   Credit SMALLINT UNSIGNED
    ->     COMMENT 'Number of cases',
    ->   PRIMARY KEY ( Id ),
    ->   FOREIGN KEY IAccountingId ( AccountingId )
    ->     REFERENCES Accounting ( Id ),
    ->   FOREIGN KEY ICaseId ( CaseId )
    ->     REFERENCES `Case` ( Id )
    -> );
Query OK, 0 rows affected (0.16 sec)

mysql> SELECT Resident.Id, FamilyName, FirstName, RoomNo,
    ->     IF( IFNULL( SUM( Credit ), 0 ) - IFNULL( SUM( Debit ), 0 ) > 0,
    ->       IFNULL( SUM( Credit ), 0 ) - IFNULL( SUM( Debit ), 0 ),
    ->       NULL ) AS TotalDebit,
    ->    IF( IFNULL( SUM( Debit ), 0 ) - IFNULL( SUM( Credit ), 0 ) > 0,
    ->      IFNULL( SUM( Debit ), 0 ) - IFNULL( SUM( Credit ), 0 ),
    ->      NULL ) AS TotalCredit
    ->    FROM ( ResidentAccount
    ->    JOIN Resident
    ->    ON ( ResidentAccount.ResidentId = Resident.Id ) )
    ->    LEFT JOIN RoomAllocation
    ->    ON ( Resident.Id = RoomAllocation.ResidentId )
    ->    WHERE ( NOW() BETWEEN `From` AND `To` ) OR
    ->      ( NOW() > `From` AND `To` IS NULL )
    ->    GROUP BY Resident.Id
    ->    HAVING ( TotalDebit <> 0 OR TotalCredit <> 0 )
    ->    ORDER BY RoomNo;
Empty set (0.17 sec)

mysql> CREATE VIEW CurrentResidentBalance ( ResidentId, FamilyName,
    ->                                      FirstName, RoomNo, Debit,
    ->                                      Credit ) AS
    ->   SELECT Resident.Id, FamilyName, FirstName, RoomNo,
    ->     IF( IFNULL( SUM( Credit ), 0 ) - IFNULL( SUM( Debit ), 0 ) > 0,
    ->       IFNULL( SUM( Credit ), 0 ) - IFNULL( SUM( Debit ), 0 ),
    ->       NULL ) AS TotalDebit,
    ->    IF( IFNULL( SUM( Debit ), 0 ) - IFNULL( SUM( Credit ), 0 ) > 0,
    ->      IFNULL( SUM( Debit ), 0 ) - IFNULL( SUM( Credit ), 0 ),
    ->      NULL ) AS TotalCredit
    ->    FROM ( ResidentAccount
    ->    JOIN Resident
    ->    ON ( ResidentAccount.ResidentId = Resident.Id ) )
    ->    LEFT JOIN RoomAllocation
    ->    ON ( Resident.Id = RoomAllocation.ResidentId )
    ->    WHERE ( NOW() BETWEEN `From` AND `To` ) OR
    ->      ( NOW() > `From` AND `To` IS NULL )
    ->    GROUP BY Resident.Id
    ->    HAVING ( TotalDebit <> 0 OR TotalCredit <> 0 )
    ->    ORDER BY RoomNo;
Query OK, 0 rows affected (0.05 sec)

Had you tried on a clean installation of 5.0.70 or just upgraded?

First the problem does not arise, if you create the view, but if you select from it. I do not see that in your output.

But meanwhile I found the core of the problem, when I tried to dump the database into a file as a test case for this bug report.

The problem is the definition of the view.

The 5th and 6th column are labeled as "Debit" and "Credit" according to the view definition itsself. See here:

CREATE VIEW CurrentResidentBalance ( ..., Debit, Credit )

Later (in the query ittself) the columns get the alias "TotalDebit" and "TotalCredit". See here:

SELECT ... IF( ..., ..., NULL ) AS TotalDebit

Later in the HAVING clause these columns are referred as "TotalDebit" and "TotalCredit".

If I only execute the SELECT statement everything works just fine. But because of the VIEW definition the aliases "TotalCredit" and "TotalDebit" are overridden with "Credit" and "Debit". Thus if you select from the view, the HAVING clause refers to columns that do not exist under this name anymore.

Just change the first line into "CREATE VIEW CurrentResidentBalance ( ..., TotalDebit, TotalCredit )" and everything works out fine.

I still call this a bug, because there should be a warning, when you create a view this way. A warning could be:

"Warning: The aliases in the view definition do not match the aliases given in the SELECT statement"

More worse, this error only occurs if there are values to display, because the HAVING clause is applied at the end of the query. If the query returns an empty set, the HAVING clause seems to be ignored and the view does not fail.

Matthias

PS: If somebody still wants my database dump, I could place it here. But somebody has to tell me, how to upload a text file.

You can attach a file using the Files tab in this page you can select to be private if you wish if the file has a size which excess the maximum allowed you can upload it at: ftp://ftp.mysql.com/pub/mysql/upload. Please use a file name which identifies this bug report and comment here when done. Thanks.

Test case with correct view

Attachment: bug_40825.sql (text/sql), 29.58 KiB.

Test case is uploaded; file name is bug_40825.sql.

The SQL script contains a working version of the view "CurrentResidentBalance". I could not add a corrupted version to the file, because mysqldump fails with such a version.

If you want to reproduce the error, you have to drop the view "CurrentResidentBalance" and re-create it accordingly to my first post, i.e. with different aliases for the columns in the view definition and the underlying SELECT statement.

Thank you for the feedback.

Verified as described.

Minimized test case:

CREATE TABLE t1 (a INT);
SELECT a AS a1 FROM t1 HAVING a1;
CREATE VIEW v1 (a) AS SELECT a AS a1 FROM t1 HAVING a1;
SELECT * FROM v1;

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/72308

2726 Gleb Shchepa	2009-04-16
      Bug#40825: Error 1356 while selecting from a view 
                 with a "HAVING" clause though query works
      
      SELECT from views defined like:
      
        CREATE VIEW v1 (view_column) 
          AS SELECT c AS alias FROM t1 HAVING alias
          
      fails with an error 1356: 
        View '...' references invalid table(s) or column(s) 
        or function(s) or definer/invoker of view lack rights 
        to use them
        
        
      CREATE VIEW form with a (column list) substitutes
      SELECT column names/aliases with new aliases.
      However, alias references in HAVING clause was
      not substituted.
      
      
      The Item_ref::print function has been modified
      to write correct aliased names of underlying
      items into VIEW definition generation/.frm file.
     @ mysql-test/r/view.result
        Added test file for bug #40825.
     @ mysql-test/t/view.test
        Added test file for bug #40825.
     @ sql/item.cc
        Bug#40825: Error 1356 while selecting from a view 
                   with a "HAVING" clause though query works
        
        The Item_ref::print function has been modified
        to write correct aliased names of underlying
        items into VIEW definition generation/.frm file.

Detailed description of the problem:

mysql_create_view/mysql_register_view functions write SELECT part of a VIEW definition into a .frm file by the st_select_lex::print function. Then the server parses that "query=..." string to load a VIEW definition back. Note: there is only a *SELECT* part, so it can't contain a VIEW column list from "CREATE VIEW view_name (column list)" definition. To represent that column names into the SELECT part, the mysql_create_view function *overwrites* SELECT column list name with new names from the VIEW column list:

  /* view list (list of view fields names) */
  if (lex->view_list.elements)
  {
    List_iterator_fast<Item> it(select_lex->item_list);
    List_iterator_fast<LEX_STRING> nm(lex->view_list);
    Item *item;
    LEX_STRING *name;

    if (lex->view_list.elements != select_lex->item_list.elements)
    {
      my_message(ER_VIEW_WRONG_LIST, ER(ER_VIEW_WRONG_LIST), MYF(0));
      res= TRUE;
      goto err;
    }
    while ((item= it++, name= nm++))
    {
      item->set_name(name->str, name->length, system_charset_info);
      item->is_autogenerated_name= FALSE;
    }
  }

This rewrite is shallow: it affects outermost SELECT column list names only.
I.e. it transforms

  CREATE VIEW v (view_col1, view_col2) SELECT c1 AS alias1, c2 FROM ...

into

  CREATE VIEW v SELECT c1 AS view_col1, c2 AS view_col2 FROM ...

and keep "..." tail untouched.

In most cases it works fine:

  1.if we reference "alias1" from GROUP BY/ORDER BY, its ok, because Item_ref-s in these clauses are resolved to referenced expressions:

  SELECT (c + 10) AS alias FROM t1 ORDER BY alias
  -->
  SELECT (c + 10) AS alias FROM t1 ORDER BY (c + 10);

  2.we can't use aliases in the WHERE clause at all (syntax error), so the WHERE clause it not affected too;

However, the HAVING clause is affected: it's implemented as an Item_ref that references an aliased expression. So, after the transformation in the mysql_create_view function aliased names in the SELECT list and in the HAVING clause may diverge.

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/74423

2748 Gleb Shchepa	2009-05-18
      Bug#40825: Error 1356 while selecting from a view
                 with a "HAVING" clause though query works
      
      SELECT from views defined like:
      
        CREATE VIEW v1 (view_column)
          AS SELECT c AS alias FROM t1 HAVING alias
      
      fails with an error 1356:
        View '...' references invalid table(s) or column(s)
        or function(s) or definer/invoker of view lack rights
        to use them
      
      
      CREATE VIEW form with a (column list) substitutes
      SELECT column names/aliases with names from a
      view column list.
      However, alias references in HAVING clause was
      not substituted.
      
      
      The Item_ref::print function has been modified
      to write correct aliased names of underlying
      items into VIEW definition generation/.frm file.
     @ mysql-test/r/view.result
        Added test file for bug #40825.
     @ mysql-test/t/view.test
        Added test file for bug #40825.
     @ sql/item.cc
        Bug#40825: Error 1356 while selecting from a view
                   with a "HAVING" clause though query works
        
        The Item_ref::print function has been modified
        to write correct aliased names of underlying
        items into VIEW definition generation/.frm file.

Pushed into 5.0.83 (revid:joro@sun.com-20090528073529-q9b8s60vlpu28fny) (version source revid:gshchepa@mysql.com-20090518184306-s67bsoxiwzj0a7us) (merge vers: 5.0.83) (pib:6)

Pushed into 5.1.36 (revid:joro@sun.com-20090528073639-yohsb4q1jzg7ycws) (version source revid:gshchepa@mysql.com-20090518195152-d5jv7hv11g3100eu) (merge vers: 5.1.36) (pib:6)

Noted in 5.0.83, 5.1.36 changelog.

For views created with a column list clause, column aliases were not
substituted when selecting through the view using a HAVING clause.

Setting report to NDI pending push into 6.0.x.

Pushed into 5.4.4-alpha (revid:alik@sun.com-20090616183122-chjzbaa30qopdra9) (version source revid:gshchepa@mysql.com-20090518201320-jiqwfz25c2qni8gb) (merge vers: 6.0.12-alpha) (pib:11)

Noted in 5.4.4 changelog.

Noted in 5.4.2 changelog because next 5.4 version will be 5.4.2 and not 5.4.4.

Ignore previous comment about 5.4.2.

Pushed into 5.1.37-ndb-7.0.8 (revid:jonas@mysql.com-20090826132541-yablppc59e3yb54l) (version source revid:jonas@mysql.com-20090826132541-yablppc59e3yb54l) (merge vers: 5.1.37-ndb-7.0.8) (pib:11)

Pushed into 5.1.37-ndb-6.3.27 (revid:jonas@mysql.com-20090826105955-bkj027t47gfbamnc) (version source revid:jonas@mysql.com-20090826105955-bkj027t47gfbamnc) (merge vers: 5.1.37-ndb-6.3.27) (pib:11)

Pushed into 5.1.37-ndb-6.2.19 (revid:jonas@mysql.com-20090825194404-37rtosk049t9koc4) (version source revid:jonas@mysql.com-20090825194404-37rtosk049t9koc4) (merge vers: 5.1.37-ndb-6.2.19) (pib:11)

Pushed into 5.1.35-ndb-7.1.0 (revid:magnus.blaudd@sun.com-20090827163030-6o3kk6r2oua159hr) (version source revid:jonas@mysql.com-20090826132541-yablppc59e3yb54l) (merge vers: 5.1.37-ndb-7.0.8) (pib:11)

Moved 5.4 changelog entry from 5.4.4 to 5.4.3.

This was actually already pushed to 5.4.2.

This bug seems only partly fixed to me. I just stumbled upon it on two machines today, having 5.1.37 and 5.1.43 installed - from what I've read here, these versions should already contain the fix.

The reproduce code above does indeed work fine with these versions, but if the HAVING condition is just slightly modified, the bug reappears. Here's a reproduce code that checks for IS NULL:

DROP VIEW IF EXISTS v1;

CREATE VIEW v1 AS
SELECT 1 AS A
FROM t1
HAVING A IS NULL;

SELECT * FROM v1;

...and there's your error message again.

Markus,

Actually your query is affected by the different bug #48150: "Quoted aliases are not recognized in HAVING clause" (this sounds a bit confusing, but that is).

Thank you for your report.

fix for this introduced bug #60295

still an issue on 5.5.16