[MDEV-16315] NOT FOUND condition not triggered in stored aggregate functions Created: 2018-05-29  Updated: 2023-04-27

Status: Open
Project: MariaDB Server
Component/s: Stored routines
Affects Version/s: 10.3
Fix Version/s: 10.4

Type: Bug Priority: Major
Reporter: Federico Razzoli Assignee: Vicențiu Ciorbaru
Resolution: Unresolved Votes: 0
Labels: stored_aggregate_functions


 Description   

Both the following example show that NOT FOUND condition does not occur.

This is a different problem than MDEV-16305. In that report, I shows HANDLERs which are never triggered even if the condition occurs. Here I show that a condition doesn't occur at all.

Realistic test:

CREATE AGGREGATE FUNCTION test1(p_value TEXT)
 
    RETURNS BOOL
 
BEGIN
 
    DECLARE CONTINUE HANDLER
 
        FOR NOT FOUND
 
    BEGIN
 
        RETURN FALSE;
 
    END;
 
    FETCH GROUP NEXT ROW;
 
    RETURN TRUE;
 
END;
 
 
 
CREATE TABLE t_empty (
 
    a INT
 
) ENGINE InnoDB;
 
 
 
MariaDB [test]> SELECT test1(n) FROM t;
 
+----------+
 
| test1(n) |
 
+----------+
 
|        1 |
 
+----------+
 
1 row in set (0.000 sec)

Trivial test to prove that the problem is not HANDLER:

CREATE AGGREGATE FUNCTION test2(p_value TEXT)
 
    RETURNS BOOL
 
BEGIN
 
    FETCH GROUP NEXT ROW;
 
    FETCH GROUP NEXT ROW;
 
    FETCH GROUP NEXT ROW;
 
    RETURN TRUE;
 
END;
 
 
 
MariaDB [test]> SELECT test2(n) FROM t;
 
+----------+
 
| test2(n) |
 
+----------+
 
|     NULL |
 
+----------+
 
1 row in set (0.001 sec)
 
 
 
MariaDB [test]> SHOW WARNINGS;
 
Empty set (0.000 sec)



 Comments   
Comment by Elena Stepanova [ 2018-05-29 ]

If I understand correctly, it only applies to empty tables. For example, in the second test case

MariaDB [test]> CREATE OR REPLACE AGGREGATE FUNCTION test2(p_value TEXT)
 
    ->     RETURNS BOOL
 
    -> BEGIN
 
    ->     FETCH GROUP NEXT ROW;
 
    ->     FETCH GROUP NEXT ROW;
 
    ->     FETCH GROUP NEXT ROW;
 
    ->     RETURN TRUE;
 
    -> END$
 
Query OK, 0 rows affected (0.03 sec)
 
 
 
MariaDB [test]> DELIMITER ;
 
MariaDB [test]> 
MariaDB [test]> CREATE OR REPLACE TABLE t (n TEXT);
 
Query OK, 0 rows affected (0.40 sec)

we indeed have

MariaDB [test]> SELECT test2(n) FROM t;
 
+----------+
 
| test2(n) |
 
+----------+
 
|     NULL |
 
+----------+
 
1 row in set (0.00 sec)

but if we insert a row, the expected error starts popping up:

MariaDB [test]> INSERT INTO t VALUES ('foo');
 
Query OK, 1 row affected (0.05 sec)
 
 
 
MariaDB [test]> SELECT test2(n) FROM t;
 
ERROR 1329 (02000): No data - zero rows fetched, selected, or processed

and if we insert two more rows, everything works again:

MariaDB [test]> INSERT INTO t VALUES ('bar'),('qux');
 
Query OK, 2 rows affected (0.05 sec)
 
Records: 2  Duplicates: 0  Warnings: 0
 
 
 
MariaDB [test]> SELECT test2(n) FROM t;
 
+----------+
 
| test2(n) |
 
+----------+
 
|        1 |
 
+----------+
 
1 row in set (0.00 sec)

Is it so also for you, or did you observe problems with non-empty tables?

Comment by Federico Razzoli [ 2018-05-29 ]

I didn't notice before. I confirm that with non-empty tables it doesn't happen. But it happens, in a different way, without tables at all:

MariaDB [test]> SELECT test2(n) FROM t;
 
ERROR 1329 (02000): No data - zero rows fetched, selected, or processed
 
MariaDB [test]> SELECT test2(1); -- same value that is in t
 
ERROR 1329 (02000): No data - zero rows fetched, selected, or processed
 
Error (Code 1329): No data - zero rows fetched, selected, or processed
 
Note (Code 4094): At line 4 in test.test2

"In a different way" means that I also see error 4094.

I'm not complaining because I want to use aggregate function without tables - I'm only reporting this because it could hide some deeper anomaly.

Comment by Varun Gupta (Inactive) [ 2018-08-03 ]

Revised Patch
http://lists.askmonty.org/pipermail/commits/2018-August/012790.html

Generated at Thu Feb 08 08:27:59 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.