sql-1: select cpu, count(*) from cpu group by cpu;
+-----------+-----------------+
| cpu | COUNT(UInt8(1)) |
+-----------+-----------------+
| cpu0 | 1 |
| cpu1 | 1 |
| cpu3 | 1 |
| cpu-total | 1 |
| cpu2 | 1 |
+-----------+-----------------+
sql-2: select cpu, count(*) from cpu where usage_user > 0.1 group by cpu;
+-----------+-----------------+
| cpu | COUNT(UInt8(1)) |
+-----------+-----------------+
| cpu0 | 40225 |
| cpu-total | 30712 |
| cpu1 | 64182 |
| cpu3 | 51382 |
| cpu2 | 60444 |
+-----------+-----------------+
sql-3: select count(*) from cpu;
+-----------------+
| COUNT(UInt8(1)) |
+-----------------+
| 426880 |
+-----------------+
Is this a bug? Or is there a reason?
The cpu table is generated by telegraf grabbing the linux environment and writing. The table structure is as follows:
+------------------+-----------------------+-------------+-------------------+
| COLUMN_NAME | DATA_TYPE | COLUMN_TYPE | COMPRESSION_CODEC |
+------------------+-----------------------+-------------+-------------------+
| time | TIMESTAMP(NANOSECOND) | TIME | DEFAULT |
| cpu | STRING | TAG | DEFAULT |
| host | STRING | TAG | DEFAULT |
| usage_guest | DOUBLE | FIELD | DEFAULT |
| usage_guest_nice | DOUBLE | FIELD | DEFAULT |
| usage_idle | DOUBLE | FIELD | DEFAULT |
| usage_iowait | DOUBLE | FIELD | DEFAULT |
| usage_irq | DOUBLE | FIELD | DEFAULT |
| usage_nice | DOUBLE | FIELD | DEFAULT |
| usage_softirq | DOUBLE | FIELD | DEFAULT |
| usage_steal | DOUBLE | FIELD | DEFAULT |
| usage_system | DOUBLE | FIELD | DEFAULT |
| usage_user | DOUBLE | FIELD | DEFAULT |
+------------------+-----------------------+-------------+-------------------+
Statement SELECT COUNT(*) may be sick. Use count(time) to get the correct:
d ❯ select count(*) from cpu;
+-----------------+
| COUNT(UInt8(1)) |
+-----------------+
| 270 |
+-----------------+
d ❯ select cpu, count(time) from cpu group by cpu;
+-----------+-----------------+
| cpu | COUNT(cpu.time) |
+-----------+-----------------+
| cpu2 | 45 |
| cpu3 | 45 |
| cpu1 | 45 |
| cpu4 | 45 |
| cpu0 | 45 |
| cpu-total | 45 |
+-----------+-----------------+