Select count(*) result is confuse


#1

Hi,

I’m trying to select count from a table, but that result was strange.
My table has records, but result of count(*) was zero.
Regards.

mapdql> select * from NIED_EQ where direction=‘N-S’ and origin_time=‘2011-03-11 14:46’ limit 5;
direction|origin_time|lon|lat|StationName|StationLon|StationLat|StationHeight|eq_time|eq_msec|intensity
N-S|2011-03-11 14:46:00|142.86|38.103|CHB008|139.9023|35.6537|4|2011-03-11 14:51:01|301100|27.49622
N-S|2011-03-11 14:46:00|142.86|38.103|CHB008|139.9023|35.6537|4|2011-03-11 14:51:01|301200|27.5406
N-S|2011-03-11 14:46:00|142.86|38.103|CHB008|139.9023|35.6537|4|2011-03-11 14:51:01|301300|26.35435
N-S|2011-03-11 14:46:00|142.86|38.103|CHB008|139.9023|35.6537|4|2011-03-11 14:51:01|301400|25.7609
N-S|2011-03-11 14:46:00|142.86|38.103|CHB008|139.9023|35.6537|4|2011-03-11 14:51:01|301900|14.57234
> mapdql> select count(*) from NIED_EQ where direction=‘N-S’ and origin_time=‘2011-03-11 14:46’;
> EXPR$0
> 0
mapdql> select distinct direction, origin_time from NIED_EQ where direction=‘N-S’ and origin_time=‘2011-03-11 14:46’;
direction|origin_time
N-S|2011-03-11 14:46:00
mapdql> select count() from NIED_EQ where direction=‘N-S’;
EXPR$0
4598570
mapdql> select count(
) from NIED_EQ where origin_time=‘2011-03-11 14:46’;
EXPR$0
5224080
mapdql> select count(*) from NIED_EQ;
EXPR$0
13795710

$ mapdql -v
MapDQL Version: 3.4.0-20180116-a484981


#2

Hello,

Can you please provide us the schema of your table?


#3

Hi, @VrajPandya
Thank you for your reply.
I did not include ‘:00’ in these SQL statements.
I’m glad if this is your help.
Thank you.

mapdql> \d NIED_EQ
CREATE TABLE NIED_EQ (
direction TEXT ENCODING DICT(8),
origin_time TIMESTAMP,
lon FLOAT,
lat FLOAT,
StationName TEXT ENCODING DICT(8),
StationLon FLOAT,
StationLat FLOAT,
StationHeight INTEGER,
eq_time TIMESTAMP,
eq_msec INTEGER,
intensity FLOAT)
mapdql> select count() from NIED_EQ where direction=‘N-S’ and origin_time=‘2011-03-11 14:46:00’;
EXPR$0
1741360
mapdql> select count(
) from NIED_EQ where direction=‘N-S’ and origin_time=‘2011-03-11 14:46’;
EXPR$0
0
mapdql> select * from NIED_EQ where direction=‘N-S’ and origin_time=‘2011-03-11 14:46:00’ limit 5;
direction|origin_time|lon|lat|StationName|StationLon|StationLat|StationHeight|eq_time|eq_msec|intensity
N-S|2011-03-11 14:46:00|142.86|38.103|CHB011|140.5014|35.6021|2|2011-03-11 14:47:18|78100|4.816657
N-S|2011-03-11 14:46:00|142.86|38.103|CHB011|140.5014|35.6021|2|2011-03-11 14:47:18|78200|4.803343
N-S|2011-03-11 14:46:00|142.86|38.103|CHB011|140.5014|35.6021|2|2011-03-11 14:47:18|78300|4.55227
N-S|2011-03-11 14:46:00|142.86|38.103|CHB011|140.5014|35.6021|2|2011-03-11 14:47:18|78400|4.66893
N-S|2011-03-11 14:46:00|142.86|38.103|CHB011|140.5014|35.6021|2|2011-03-11 14:47:18|78900|4.770374
mapdql> select * from NIED_EQ where direction=‘N-S’ and origin_time=‘2011-03-11 14:46’ limit 5;
direction|origin_time|lon|lat|StationName|StationLon|StationLat|StationHeight|eq_time|eq_msec|intensity
N-S|2011-03-11 14:46:00|142.86|38.103|CHB011|140.5014|35.6021|2|2011-03-11 14:49:48|228500|19.78082
N-S|2011-03-11 14:46:00|142.86|38.103|CHB011|140.5014|35.6021|2|2011-03-11 14:49:48|228600|31.52416
N-S|2011-03-11 14:46:00|142.86|38.103|CHB011|140.5014|35.6021|2|2011-03-11 14:48:18|138900|10.89819
N-S|2011-03-11 14:46:00|142.86|38.103|CHB011|140.5014|35.6021|2|2011-03-11 14:49:48|228700|20.37236
N-S|2011-03-11 14:46:00|142.86|38.103|CHB011|140.5014|35.6021|2|2011-03-11 14:49:48|228800|3.980384
mapdql>