Barrendeitor Barrendeitor - 7 months ago 8
SQL Question

Why MySQL COUNT(*) query return empty set?

I have 3 tables:


  • user
    (a table of users)

  • post
    (a table of posts with a field author which is a
    FOREIGN KEY
    to user id)

  • friend
    (a table which represents a relation user-to-user with
    FOREIGN KEY
    fields:
    user_id
    and
    friend



I have a query where I can set values for filter the results like this (from Java PreparedStatement):

SELECT post.* FROM post, friend WHERE friend.user_id = ? AND post.author = friend.friend AND post.body LIKE ? AND post.date BETWEEN ? AND ? LIMIT ?,?


Inserting values in the query like this:

SELECT post.* FROM post, friend WHERE friend.user_id = 2 AND post.author = friend.friend AND post.body LIKE '%word%' AND post.date BETWEEN '2016-02-10 00:00:00' AND '2016-12-01 00:00:00' LIMIT 1,5;


I get a list of 5 posts. But now, I want to get the number of results. I have tried:

SELECT COUNT(*) FROM post, friend WHERE friend.user_id = 2 AND post.author = friend.friend AND post.body LIKE '%word%' AND post.date BETWEEN '2016-02-10 00:00:00' AND '2016-12-01 00:00:00' LIMIT 1,5;


But I get
Empty set
. Why do I get this instead 5?

EDIT:

If it helps, I post the db code and some inserts of data.

DROP DATABASE IF EXISTS dbname;
CREATE DATABASE dbname;
USE dbname;

CREATE TABLE user (
id INT NOT NULL AUTO_INCREMENT,
username VARCHAR(20) NOT NULL UNIQUE,
name VARCHAR(70) NOT NULL,
PRIMARY KEY(id)
) ENGINE=InnoDB;

CREATE TABLE post (
id INT NOT NULL AUTO_INCREMENT,
date DATETIME NOT NULL,
title VARCHAR(100) NOT NULL,
body VARCHAR(1000) NOT NULL,
author INT NOT NULL,
PRIMARY KEY(id)
) ENGINE=InnoDB;

CREATE TABLE friend (
user_id INT NOT NULL,
friend INT NOT NULL,
PRIMARY KEY(user_id, friend)
) ENGINE=InnoDB;

ALTER TABLE post
ADD FOREIGN KEY fk_autor_post(author) REFERENCES user(id)
ON DELETE CASCADE
ON UPDATE CASCADE;

ALTER TABLE friend
ADD FOREIGN KEY fk_user_id(user_id) REFERENCES user(id)
ON DELETE CASCADE
ON UPDATE CASCADE;

ALTER TABLE friend
ADD FOREIGN KEY fk_friend_id(friend) REFERENCES user(id)
ON DELETE CASCADE
ON UPDATE CASCADE;


INSERT INTO user VALUES(null, "uname1", "User 1");
INSERT INTO user VALUES(null, "uname2", "User 2");
INSERT INTO user VALUES(null, "uname3", "User 3");
INSERT INTO user VALUES(null, "uname4", "User 4");
INSERT INTO user VALUES(null, "uname5", "User 5");

INSERT INTO friend VALUES(1,2);
INSERT INTO friend VALUES(1,3);
INSERT INTO friend VALUES(1,4);
INSERT INTO friend VALUES(1,5);
INSERT INTO friend VALUES(2,1);
INSERT INTO friend VALUES(2,5);
INSERT INTO friend VALUES(4,1);
INSERT INTO friend VALUES(2,4);
INSERT INTO friend VALUES(2,3);

INSERT INTO post values (null, '2016-01-01 10:00:00', "Title post 1", "Body 1", 1);
INSERT INTO post values (null, '2016-01-01 10:00:00', "Title post 2", "Body 2", 1);
INSERT INTO post values (null, '2016-01-01 10:20:40', "Title post 3", "Body 3", 1);
INSERT INTO post values (null, '2016-01-01 10:20:00', "Title post 4", "Body 4", 4);
INSERT INTO post values (null, '2016-02-05 10:00:00', "Title post 5", "Body 5", 5);
INSERT INTO post values (null, '2016-01-05 10:00:00', "Title post 6", "Body 6", 2);
INSERT INTO post values (null, '2016-02-01 22:00:00', "Title post 7", "Body 7", 2);
INSERT INTO post values (null, '2016-02-01 10:00:00', "Title post 8", "Body 8", 3);
INSERT INTO post values (null, '2016-01-02 10:00:00', "Title post 9", "Body 9", 5);
INSERT INTO post values (null, '2016-01-01 10:40:00', "Title post 10", "Body 10", 1);


Query:

SELECT post.* FROM post, friend WHERE friend.user_id = 2 AND post.author = friend.friend AND post.body LIKE '%%' AND post.date BETWEEN '2016-01-01 00:00:00' AND '2016-12-01 00:00:00' LIMIT 1,5;


Return:

+----+---------------------+--------------+--------+--------+
| id | date | title | body | author |
+----+---------------------+--------------+--------+--------+
| 2 | 2016-01-01 10:00:00 | Title post 2 | Body 2 | 1 |
| 3 | 2016-01-01 10:20:40 | Title post 3 | Body 3 | 1 |
| 4 | 2016-01-01 10:20:00 | Title post 4 | Body 4 | 4 |
| 5 | 2016-02-05 10:00:00 | Title post 5 | Body 5 | 5 |
| 8 | 2016-02-01 10:00:00 | Title post 8 | Body 8 | 3 |
+----+---------------------+--------------+--------+--------+
5 rows in set (0.00 sec)


Query:

SELECT COUNT(*) FROM post, friend WHERE friend.user_id = 2 AND post.author = friend.friend AND post.body LIKE '%%' AND post.date BETWEEN '2016-01-01 00:00:00' AND '2016-12-01 00:00:00' LIMIT 1,5;


Return:

Empty set (0.00 sec)


The same using
COUNT(post.id)
,
COUNT(post.body)
,
SUM(1)
,
SUM(post.id)
.
Error using
COUNT(post.*)
:

Answer

Try this

 SELECT COUNT(*) AS TotalCount FROM (SELECT 
  post.* 
FROM
  post,
  friend 
WHERE friend.user_id = 2 
  AND post.author = friend.friend 
  AND post.body LIKE '%%' 
  AND post.date BETWEEN '2016-01-01 00:00:00' 
  AND '2016-12-01 00:00:00' 
LIMIT 1, 5) AS a ;
Comments