[MDEV-8927] CONNECT does not return the correct row count for "select count(*) from" for OCCUR type table Created: 2015-10-09  Updated: 2015-10-23  Resolved: 2015-10-20

Status: Closed
Project: MariaDB Server
Component/s: Storage Engine - Connect
Affects Version/s: 10.0.15, 10.0.21, 10.0, 10.1
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Tuco Assignee: Olivier Bertrand
Resolution: Not a Bug Votes: 0
Labels: wrong_result
Environment:

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]>



 Comments   
Comment by Olivier Bertrand [ 2015-10-20 ]

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.

Comment by Tuco [ 2015-10-20 ]

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.

Comment by Olivier Bertrand [ 2015-10-21 ]

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.

Comment by Tuco [ 2015-10-23 ]

I understand. Thank you.

Generated at Thu Feb 08 07:30:50 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.