Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-41071

DISTINCT silently dropped from a plugin aggregate function with VIEW / EXPLAIN EXTENDED

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Critical
    • Resolution: Fixed
    • 13.1
    • 13.2.1
    • Plugins, Views
    • 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

          Activity

            People

              drrtuy Roman
              ramesh Ramesh Sivaraman
              Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Time Tracking

                  Estimated:
                  Original Estimate - Not Specified
                  Not Specified
                  Remaining:
                  Remaining Estimate - 0d
                  0d
                  Logged:
                  Time Spent - 0.5h
                  0.5h

                  Git Integration

                    Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.