New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

MariaDB GROUP BY with COUNT and DISTINCT statement got wrong output #8193

Open
kasora opened this Issue Aug 25, 2017 · 5 comments

Comments

2 participants
@kasora

kasora commented Aug 25, 2017

What you are doing?

Hi,( sorry, my english is poor )
I'm trying to use DISTINCT, GROUP BY and COUNT function.
By the document, I got this.

Apply COUNT(DISTINCT(col)) on primary key, Model.aggregate should be used for other columns

so, I wrote the following code.

let Log = sequelize.define('log',
  {
    logType: Sequelize.INTEGER,
    text: Sequelize.STRING
  }
);

let initDB = async () => {
  await Log.sync({ force: true });
};

let insertLog = async () => {
  await Log.create({ text: 'text1', logType: 0 });
  await Log.create({ text: 'text1', logType: 0 });
  await Log.create({ text: 'text2', logType: 1 });
  await Log.create({ text: 'text3', logType: 1 });
};

let start = async () => {
  await initDB();
  await insertLog();

  let query = {
    attributes:['logType'],
    where: {},
    distinct: true,
    group: 'logType',
    logging: true
  }
  let ans = await Log.aggregate('text', 'count', query);
  console.log(ans);
  expect(ans).to.be.an('array');
};

start();

Here is the logging:

Executing (default): DROP TABLE IF EXISTS logs;
Executing (default): CREATE TABLE IF NOT EXISTS logs (id INTEGER NOT NULL auto_increment , logType INTEGER, text VARCHAR(255), createdAt DATETIME NOT NULL, updatedAt DATETIME NOT NULL, PRIMARY KEY (id)) ENGINE=InnoDB;
Executing (default): SHOW INDEX FROM logs
Executing (default): INSERT INTO logs (id,logType,text,createdAt,updatedAt) VALUES (DEFAULT,0,'text1','2017-08-25 01:59:03','2017-08-25 01:59:03');
Executing (default): INSERT INTO logs (id,logType,text,createdAt,updatedAt) VALUES (DEFAULT,0,'text1','2017-08-25 01:59:03','2017-08-25 01:59:03');
Executing (default): INSERT INTO logs (id,logType,text,createdAt,updatedAt) VALUES (DEFAULT,1,'text2','2017-08-25 01:59:03','2017-08-25 01:59:03');
Executing (default): INSERT INTO logs (id,logType,text,createdAt,updatedAt) VALUES (DEFAULT,1,'text3','2017-08-25 01:59:03','2017-08-25 01:59:03');
Executing (default): SELECT logType, count(DISTINCT(text)) AS count FROM logs AS log GROUP BY logType;

What do you expect to happen?

I expect to get the output like this.

[ 
  {logType: 0, count: 1},
  {logType: 1, count: 2} 
]

actually, I copy the sql syntax from logging to mysql command line. I got this.

logType count
0 1
1 2

What is actually happening?

But, I just got an integer.

ans : 1

Dialect: mysql ( MariaDB )
Database version: mysql Ver 15.1 Distrib 10.1.26-MariaDB, for Linux (x86_64) using readline 5.1
Sequelize version: 4.3.2/3.30.2

@kasora kasora changed the title from Group by with COUNT and DISTINCT to [MariaDB] Group by with COUNT and DISTINCT Dec 6, 2017

@kasora kasora changed the title from [MariaDB] Group by with COUNT and DISTINCT to MariaDB Group by with COUNT and DISTINCT statement got wrong result Dec 6, 2017

@kasora kasora changed the title from MariaDB Group by with COUNT and DISTINCT statement got wrong result to MariaDB GROUP BY with COUNT and DISTINCT statement got wrong result Dec 6, 2017

@kasora kasora changed the title from MariaDB GROUP BY with COUNT and DISTINCT statement got wrong result to MariaDB GROUP BY with COUNT and DISTINCT statement got wrong output Dec 6, 2017

@stale

This comment has been minimized.

stale bot commented Mar 6, 2018

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. If this is still an issue, just leave a comment 🙂

@stale stale bot added the stale label Mar 6, 2018

@kasora

This comment has been minimized.

kasora commented Mar 8, 2018

This is still an issue

@stale stale bot removed the stale label Mar 8, 2018

@stale

This comment has been minimized.

stale bot commented Jun 6, 2018

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. If this is still an issue, just leave a comment 🙂

@stale stale bot added the stale label Jun 6, 2018

@stale stale bot closed this Jun 13, 2018

@sushantdhiman sushantdhiman added investigate and removed stale labels Jun 13, 2018

@sushantdhiman sushantdhiman reopened this Jun 13, 2018

@stale

This comment has been minimized.

stale bot commented Sep 11, 2018

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. If this is still an issue, just leave a comment 🙂

@stale stale bot added the stale label Sep 11, 2018

@sushantdhiman sushantdhiman removed the stale label Sep 15, 2018

@stale

This comment has been minimized.

stale bot commented Dec 14, 2018

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. If this is still an issue, just leave a comment 🙂

@stale stale bot added the stale label Dec 14, 2018

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment