Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Not a Bug
-
10.0.15, 10.0.21, 10.0(EOL), 10.1(EOL)
-
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]>
|
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.