Details
-
Bug
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Fixed
-
13.1
-
None
-
Not for Release Notes
Description
INSTALL SONAME 'func_test'; |
CREATE TABLE t (g INT, v INT); |
INSERT INTO t VALUES (1,1),(1,1),(1,2),(1,2),(1,2),(2,3),(2,3),(2,NULL),(2,4); |
-- 9 rows, 8 non-NULL, 4 distinct non-NULL values
|
-- correct when run directly:
|
SELECT COUNT(DISTINCT v), test_plugin_count(DISTINCT v) FROM t; --> 4, 4 |
|
|
-- WRONG through a view:
|
CREATE VIEW vw AS SELECT COUNT(DISTINCT v) AS b, test_plugin_count(DISTINCT v) AS pl FROM t; |
SELECT b, pl FROM vw; --> 4, 8 <=== pl must be 4; it silently became the TOTAL count |
-- Stored view shows that the `DISTINCT` is retained in the built-in implementation but not in the plugin.
|
SELECT VIEW_DEFINITION FROM information_schema.VIEWS WHERE TABLE_NAME='vw'; |
CLI Output
13.1.0-opt>INSTALL SONAME 'func_test'; |
Query OK, 0 rows affected (0.002 sec) |
|
|
13.1.0-opt>CREATE TABLE t (g INT, v INT); |
Query OK, 0 rows affected (0.008 sec) |
|
|
13.1.0-opt>INSERT INTO t VALUES (1,1),(1,1),(1,2),(1,2),(1,2),(2,3),(2,3),(2,NULL),(2,4); |
Query OK, 9 rows affected (0.001 sec) |
Records: 9 Duplicates: 0 Warnings: 0
|
|
|
13.1.0-opt>-- 9 rows, 8 non-NULL, 4 distinct non-NULL values |
13.1.0-opt>-- correct when run directly: |
13.1.0-opt>SELECT COUNT(DISTINCT v), test_plugin_count(DISTINCT v) FROM t; --> 4, 4 |
+-------------------+-------------------------------+ |
| COUNT(DISTINCT v) | test_plugin_count(DISTINCT v) | |
+-------------------+-------------------------------+ |
| 4 | 4 |
|
+-------------------+-------------------------------+ |
1 row in set (0.000 sec) |
|
|
13.1.0-opt>
|
13.1.0-opt>-- WRONG through a view: |
13.1.0-opt>CREATE VIEW vw AS SELECT COUNT(DISTINCT v) AS b, test_plugin_count(DISTINCT v) AS pl FROM t; |
Query OK, 0 rows affected (0.004 sec) |
|
|
13.1.0-opt>SELECT b, pl FROM vw; --> 4, 8 <=== pl must be 4; it silently became the TOTAL count |
+---+----+ |
| b | pl |
|
+---+----+ |
| 4 | 8 |
|
+---+----+ |
1 row in set (0.000 sec) |
|
|
13.1.0-opt>-- Stored view shows that the `DISTINCT` is retained in the built-in implementation but not in the plugin. |
13.1.0-opt>SELECT VIEW_DEFINITION FROM information_schema.VIEWS WHERE TABLE_NAME='vw'; |
+--------------------------------------------------------------------------------------------------------+ |
| VIEW_DEFINITION |
|
+--------------------------------------------------------------------------------------------------------+ |
| select count(distinct `test`.`t`.`v`) AS `b`,test_plugin_count(`test`.`t`.`v`) AS `pl` from `test`.`t` | |
+--------------------------------------------------------------------------------------------------------+ |
1 row in set (0.001 sec) |
|
|
13.1.0-opt>
|
|
|
|
|
Same issue is visible with EXPLAIN EXTENDED / SHOW WARNINGS |
|
|
13.1.0-opt>EXPLAIN EXTENDED SELECT test_plugin_count(DISTINCT v) as b FROM t; |
+------+-------------+-------+------+---------------+------+---------+------+------+----------+-------+ |
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | |
+------+-------------+-------+------+---------------+------+---------+------+------+----------+-------+ |
| 1 | SIMPLE | t | ALL | NULL | NULL | NULL | NULL | 9 | 100.00 | | |
+------+-------------+-------+------+---------------+------+---------+------+------+----------+-------+ |
1 row in set, 1 warning (0.000 sec) |
|
|
13.1.0-opt>SHOW WARNINGS;
|
+-------+------+-----------------------------------------------------------------+ |
| Level | Code | Message | |
+-------+------+-----------------------------------------------------------------+ |
| Note | 1003 | select test_plugin_count(`test`.`t`.`v`) AS `b` from `test`.`t` | |
+-------+------+-----------------------------------------------------------------+ |
1 row in set (0.000 sec) |
|
|
13.1.0-opt>
|
Attachments
Issue Links
- blocks
-
MDEV-40672 Pluggable aggregate functions
-
- In Testing
-