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

CONNECT does not return the correct row count for "select count(*) from" for OCCUR type table

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Not a Bug
    • 10.0.15, 10.0.21, 10.0(EOL), 10.1(EOL)
    • N/A
    • Windows Server 2008 R2; Windows 7

    Description

      CONNECT does not return the correct total row count for "select count(*) from " query against OCCUR type table. It instead returns the total number of rows of the base(source) table.

      Welcome to the MariaDB monitor.  Commands end with ; or \g.
      Your MariaDB connection id is 11
      Server version: 10.0.21-MariaDB mariadb.org binary distribution
       
      Copyright (c) 2000, 2015, Oracle, MariaDB Corporation Ab and others.
       
      Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
       
      MariaDB [(none)]> use test
      Database changed
      MariaDB [test]> create table pets
          -> (        name varchar(20),
          ->  dog int,
          ->  cat int,
          ->  rabbit int,
          ->  bird int,
          ->  fish int
          -> );
      Query OK, 0 rows affected (0.01 sec)
       
      MariaDB [test]> insert into pets(name, dog, cat, rabbit, bird, fish)
          -> values
          ->  ('John', 2, 0, 0, 0, 0),
          ->  ('Bill',        0,      1,      0,      0,      0),
          ->  ('Mary',        1,      1,      0,      0,      0),
          ->  ('Lisbeth', 0,  0,      2,      0,      0),
          ->  ('Kevin',       0,      2,      0,      6,      0),
          ->  ('Donald',      1, 0,   0,      0,      3);
      Query OK, 6 rows affected (0.00 sec)
      Records: 6  Duplicates: 0  Warnings: 0
       
      MariaDB [test]> select * from pets\G
      *************************** 1. row ***************************
        name: John
         dog: 2
         cat: 0
      rabbit: 0
        bird: 0
        fish: 0
      *************************** 2. row ***************************
        name: Bill
         dog: 0
         cat: 1
      rabbit: 0
        bird: 0
        fish: 0
      *************************** 3. row ***************************
        name: Mary
         dog: 1
         cat: 1
      rabbit: 0
        bird: 0
        fish: 0
      *************************** 4. row ***************************
        name: Lisbeth
         dog: 0
         cat: 0
      rabbit: 2
        bird: 0
        fish: 0
      *************************** 5. row ***************************
        name: Kevin
         dog: 0
         cat: 2
      rabbit: 0
        bird: 6
        fish: 0
      *************************** 6. row ***************************
        name: Donald
         dog: 1
         cat: 0
      rabbit: 0
        bird: 0
        fish: 3
      6 rows in set (0.00 sec)
       
      MariaDB [test]> create table xpet (
          ->   name varchar(12) not null,
          ->   race char(6) not null,
          ->   number int not null)
          -> engine=connect table_type=occur tabname=pets
          -> option_list='OccurCol=number,RankCol=race,Password=12345'
          -> Colist='dog,cat,rabbit,bird,fish';
      Query OK, 0 rows affected (0.00 sec)
       
      MariaDB [test]> select * from xpet\G
      *************************** 1. row ***************************
        name: John
        race: dog
      number: 2
      *************************** 2. row ***************************
        name: Bill
        race: cat
      number: 1
      *************************** 3. row ***************************
        name: Mary
        race: dog
      number: 1
      *************************** 4. row ***************************
        name: Mary
        race: cat
      number: 1
      *************************** 5. row ***************************
        name: Lisbeth
        race: rabbit
      number: 2
      *************************** 6. row ***************************
        name: Kevin
        race: cat
      number: 2
      *************************** 7. row ***************************
        name: Kevin
        race: bird
      number: 6
      *************************** 8. row ***************************
        name: Donald
        race: dog
      number: 1
      *************************** 9. row ***************************
        name: Donald
        race: fish
      number: 3
      9 rows in set (0.00 sec)
       
      MariaDB [test]> select count(*) from xpet\G
      *************************** 1. row ***************************
      count(*): 6
      1 row in set (0.00 sec)
       
      MariaDB [test]>

      Attachments

        Activity

          OCCURS tables, as well as XCOL tables, make some row multiplication depending on the query. Row multiplication occurs only when a specific "multiple" column is used in the query (XCOL or OCCURCOL)

          This is more clearly explained for XCOL tables, the documentation saying in particular:

          If a query does not involve the “multiple” column, no row multiplication will be done. For instance:

          select count(*) from xchild;		 returns 5
          select count(child) from xchild;	 returns 10
          select count(mother) from xchild;	 returns 5

          The documentation of OCCURS table has just a note saying:

          Note 1: Like for XCOL tables, no row multiplication for queries not implying the Occur column. It is also possible to use the ROWNUM special column with similar result.

          In your example, the queries:

          select count(*) from xpet;
          select count(name) from xpet;
          select count(race) from xpet;
          select count(number) from xpet;

          all return 6 except the last one that return 9 because it includes explicitely the "multiple" column.

          Keep in mind that these tables do not have a proper "count" value as they can return different number of rows even without where clause ot limit value. However, I admit that the documentation should be more explicite.

          bertrandop Olivier Bertrand added a comment - OCCURS tables, as well as XCOL tables, make some row multiplication depending on the query. Row multiplication occurs only when a specific "multiple" column is used in the query (XCOL or OCCURCOL) This is more clearly explained for XCOL tables, the documentation saying in particular: If a query does not involve the “multiple” column, no row multiplication will be done. For instance: select count(*) from xchild; returns 5 select count(child) from xchild; returns 10 select count(mother) from xchild; returns 5 The documentation of OCCURS table has just a note saying: Note 1: Like for XCOL tables, no row multiplication for queries not implying the Occur column. It is also possible to use the ROWNUM special column with similar result. In your example, the queries: select count(*) from xpet; select count(name) from xpet; select count(race) from xpet; select count(number) from xpet; all return 6 except the last one that return 9 because it includes explicitely the "multiple" column. Keep in mind that these tables do not have a proper "count" value as they can return different number of rows even without where clause ot limit value. However, I admit that the documentation should be more explicite.
          Tuco Tuco added a comment -

          Thank you, I should've read the documentation more closely.
          I understand this is not an unintentional bug. But would it be possible to return the multiplied row count for a "select count" query instead? As the asterisk sort of means every column, and the result would be matched to the number of numbers returned for a "select *" query. It might be more intuitive. Just a thought.

          BTW. I really love CONNECT. It is amazingly useful. Thank you for all your hard work.

          Tuco Tuco added a comment - Thank you, I should've read the documentation more closely. I understand this is not an unintentional bug. But would it be possible to return the multiplied row count for a "select count " query instead? As the asterisk sort of means every column, and the result would be matched to the number of numbers returned for a "select *" query. It might be more intuitive. Just a thought. BTW. I really love CONNECT. It is amazingly useful. Thank you for all your hard work.

          I have been thinking about that. Indeed in queries such as:

          select * from t1;

          the star means "all columns". However this may not be the case for "count". It rather means something like the size of the table even with no column involved. This was obvious for standard relational tables but XCOL or OCCUR tables are not really standard relational tables. In addition, the way this is implemented internally does not imply any column and doing differently would be sort of artificial.
          Therefore it will remain as it is... sorry about that.

          bertrandop Olivier Bertrand added a comment - I have been thinking about that. Indeed in queries such as: select * from t1; the star means "all columns". However this may not be the case for "count ". It rather means something like the size of the table even with no column involved. This was obvious for standard relational tables but XCOL or OCCUR tables are not really standard relational tables. In addition, the way this is implemented internally does not imply any column and doing differently would be sort of artificial. Therefore it will remain as it is... sorry about that.
          Tuco Tuco added a comment -

          I understand. Thank you.

          Tuco Tuco added a comment - I understand. Thank you.

          People

            bertrandop Olivier Bertrand
            Tuco Tuco
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Git Integration

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