Mysql Why select count distinct return zero

Question

I would like to know why select count distinct return zero result. I have also checked various answers at here but it's not answer for my case. MySQL version 5.6 Link to check http://sqlfiddle.com/#!9/276302/3/0

Sample schema:

CREATE TABLE IF NOT EXISTS `employees` (
  `id` int(6) unsigned NOT NULL,
  `name` varchar(3) NOT NULL,
  `salary` varchar(200) NOT NULL,
  PRIMARY KEY (`id`)
) DEFAULT CHARSET=utf8;
INSERT INTO `employees` (`id`, `name`, `salary`) VALUES
  ('1', 'a', 6),
  ('2', 'b', 5),
  ('3', 'c', 5),
  ('4', 'd', 4);

Query:

SELECT COUNT(DISTINCT(salary))
FROM employees;

Guys, do you have any idea ?

I cannot reproduce it [demo](http://dbfiddle.uk/?rdbms=mariadb_10.2&fiddle=5e825761accbb2a68d4cb650131bb71e) Anyway I guess it has something to do with datatype VARCHAR(100). If you change it to INT it will work or add `'` [demo](http://sqlfiddle.com/#!9/35ab34/1/0). Probably some bug when sorting for DISTINCt — Lukasz Szozda, Mar 19 '18 at 17:09
@lad2025 hm it's work when I change datatype INT but if I add `'` it will give incorrect value — ToujouAya, Mar 19 '18 at 17:13
**[Demo from MySQL 5.7](https://www.db-fiddle.com/f/5taBEKJ24TgMiYpoGYtYy2/1)** which works :) I suggest to upgrade. — Lukasz Szozda, Mar 19 '18 at 17:17
@lad2025 it's seem ok on MySQL5.7. Do you think it's bug on MySQL 5.6 or something else what cause the incorrect result — ToujouAya, Mar 19 '18 at 17:20
https://stackoverflow.com/a/7250654/645132 I'm not sure if this might be why you're having an issue. Was trying to mess with your fiddle a bit to see if i could do it with a subquery, but I don't know MySQL as well as I do postgres — agmcleod, Mar 19 '18 at 17:20
@lad2025 thanks you, actually I used MariaDB. I just make simple for testing purpose — ToujouAya, Mar 19 '18 at 17:26
@isaace Just a simple for testing. But event if I insert string it still make incorrect result — ToujouAya, Mar 19 '18 at 17:28
so please edit your question and fix it instead of misleading us. You already received 2 answers which are not relevant. — isaace, Mar 19 '18 at 17:32
@isaace Why I need to edit my question. What part make you misleading — ToujouAya, Mar 19 '18 at 17:35
that you are inserting int's into a varchar column. You can do what you want but people are confused as you can see of both answers. — isaace, Mar 19 '18 at 17:35
@isaace I think it's not necessary, actually I think even string or int , it's must give the correct answer. You can check the demo was created by lad2025 above. It give correct result — ToujouAya, Mar 19 '18 at 17:38
@isaace Both answers just repharase my first comment. To sum up, it should work both INT/VARCHAR and so on. For MariaDB/MySQL 5.7/8.0 it is working so I guess it is a bug of version 5.6 — Lukasz Szozda, Mar 19 '18 at 17:43

score 2 · Answer 1 · answered Mar 19 '18 at 17:47

In my opinion it is a bug of this specific version of MySQL 5.6.

But it will work for MariaDB or MySQL 5.7

The other answers suggest that it is the problem with datatype.But if we remove DISTINCT:

SELECT COUNT(salary)
FROM employees;
-- 4

DBFiddle Demo MySQL 5.6

score 1 · Answer 2 · answered Mar 19 '18 at 17:46

1

select count(ds) from (SELECT distinct(salary) as ds
FROM employees) as s;

answered Mar 19 '18 at 17:46

godot

3,422
6
25
42

score 0 · Answer 3 · answered Mar 19 '18 at 17:24

0

you need to change the datatype of salary from varchar to int. OR you need to include salary values in " ".

answered Mar 19 '18 at 17:24

sherry

13
5

because datatype is mismatched. – sherry Mar 19 '18 at 17:25
So please explain this https://www.db-fiddle.com/f/5taBEKJ24TgMiYpoGYtYy2/1 – Lukasz Szozda Mar 19 '18 at 17:26

score 0 · Answer 4 · answered Feb 27 '19 at 08:45

0

This is a known and long-standing bug in MySQL. :-(

answered Feb 27 '19 at 08:45

Michel de Ruiter

7,131
5
49
74

nitsram · Answer 5 · 2018-03-19T17:17:44.113

-2

Your salary column is type VARCHAR, try using INT or another appropriate data type.

See here: http://sqlfiddle.com/#!9/875bde/1/0

edited Mar 19 '18 at 17:17

answered Mar 19 '18 at 17:14

nitsram

686
5
5

1

It doesn't matter. Please check my demo for [MariaDB](http://dbfiddle.uk/?rdbms=mariadb_10.2&fiddle=5e825761accbb2a68d4cb650131bb71e) Changing datatype is nasty workaround while it should work for strings too. – Lukasz Szozda Mar 19 '18 at 17:14
1

Sorry maybe im missing something, why MariaDB? I don't think its a nasty workaround to suggest a different data type if possible. Creating queries which rely on correct formats added into a varchar column could create no end of issues. – nitsram Mar 19 '18 at 17:21
Your answer suggests that COUNT(DISTINCT ...) should work only for INT which is not true. It is a clearly bug. Please check [MySQL Demo](https://www.db-fiddle.com/f/5taBEKJ24TgMiYpoGYtYy2/1) – Lukasz Szozda Mar 19 '18 at 17:22

Mysql Why select count distinct return zero

5 Answers5