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

    XMLWordPrintable

Details

    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

          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.