[MDEV-8927] CONNECT does not return the correct row count for "select count(*) from" for OCCUR type table - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Major
Resolution: Not a Bug
Affects Version/s: 10.0.15, 10.0.21, 10.0(EOL), 10.1(EOL)
Fix Version/s: N/A
Component/s: Storage Engine - Connect
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]>

Attachments

Activity

Ascending order - Click to sort in descending order

Olivier Bertrand added a comment - 2015-10-20 13:51

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.

Olivier Bertrand added a comment - 2015-10-20 13:51 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 added a comment - 2015-10-20 23:21

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 added a comment - 2015-10-20 23:21 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.

Olivier Bertrand added a comment - 2015-10-21 00:55

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.

Olivier Bertrand added a comment - 2015-10-21 00:55 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 added a comment - 2015-10-23 21:25

I understand. Thank you.

Tuco added a comment - 2015-10-23 21:25 I understand. Thank you.

MariaDB Server

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

Details

Description

Attachments

Activity

People

Dates

Git Integration