you have a "solution", so by saying
I am wondering if there is a better solution.
I think you want to comment about it. Let me focus on several aspects:
SELECT ID [...] GROUP BY word
, independently of having or not a WITH ROLLUP
is wrong. You are selecting a field whose value is undetermined. In particular, with MySQL you get exposed of returning a random value; or if you use an strict mode (recommended, and by default in the latest versions of MySQL), of the query to fail: 'db_9_b0ff7.test.ID' isn't in GROUP BY
Be it formally or not, WITH ROLLUP
Note- (I refer to MySQL implementation) is something that is nice to have in some cases because it avoids a double rescan of the table, but I have not seen a lot in production code. Nothing wrong with it, but they were an exclusive MSSQL feature until it was implemented on SQL1999, and either it was not available widely back in the day or, like in the case of MySQL it was a limited implementation. Reference: https://www.percona.com/blog/2007/09/17/using-group-by-with-rollup-for-reporting-performance-optimization/
I do not see in principle a problem with your final query, if we run explain on both queries (without ID), we see:
MariaDB [test]> EXPLAIN SELECT if(count(word) = 0, "empty", ifnull(word, "total")) as word, count(*) as occurrences FROM test group by word with ROLLUP\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: test
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 4
Extra: Using filesort
1 row in set, 1 warning (0.00 sec)
Warning (Code 1052): Column 'word' in group statement is ambiguous
MariaDB [test]> EXPLAIN SELECT ifnull(word, "total") as word, count(*) as occurrences FROM ( SELECT ifnull(word, "empty") as word FROM test ) tmp GROUP BY word WITH ROLLUP\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: test
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 4
Extra: Using filesort
1 row in set, 1 warning (0.01 sec)
Normally the hate towards MySQL is because its limitations on early versions of MySQL. I am not going to say that it is now perfect, there will be still issues, as MySQL development, as it has always been, focused on a fast, easy-in, easy-out RDBMS, and not a full-blown relational engine, but a subquery in the right place is ok (after all, some queries will require a subquery, no matter what. Your usage is ok- it will only have an overhead of going over the summarized rows again and I suppose on the real deal, those will not be significant compared to the full set.
Note- on my instance up here, the subquery is not showed, but you can see it with materialized subqueries in 5.6 here: http://sqlfiddle.com/#!9/b0ff7/7 (It will execute the inner query and then the outer part, with no issue). An index could definitely help here (due to the filesort), but that depends on the final number of records. The subquery may hurt performance if an index (which does not exist now) cannot be used because it. You have to test your specific mysql version and create such an index.
- I think your problem is with the data model- in the SQL world (outside of Oracle) NULL is an unknown/invalid value. Seem to be correlating that to 'empty' which is not right- if a word is known to not exist, it should be '' (the empty string, not NULL). I do not know if you can change that, but I do not like your current model. I understand that you may not have something to say about it, so I will not focus on that. Alos you count NULL words? More reasons to use '' instead of NULL.
Now the final question is, is it possible to do it without a subquery on MySQL, and will it be better/less ugly? I do not really see a way- we could do a join, but it will still be to a subquery. I can think of an alternative, which is:
SELECT * FROM (
SELECT word,
count(*) as occurrences
FROM test
GROUP BY word with rollup)
tmp
ORDER BY occurrences;
That makes sure that the last row contains the summary (and if there is other null, it is the actual null value). The order would happen even on the case of only null values. On the contrary, it may even slower as it requires ordering, and again you may need an index, and that index may or may not be used.
I would suggest rewriting the query in a way that minimises the number of columns necessary to put into GROUP BY. In your case you can do that by applying the grouping to the Product_Category
table only.
According to your example, that table has the following entries:
+------------+-------------+
| product_id | category_id |
+------------+-------------+
| 1 | 1 |
| 2 | 1 |
| 3 | 1 |
| 1 | 2 |
| 4 | 2 |
| 5 | 2 |
+------------+-------------+
Since you want product names to be unique in the output, group this table by product_id
, and for category_id
select e.g. the minimum value per product:
SELECT
product_id,
MIN(category_id) AS category_id
FROM
Product_Category
GROUP BY
product_id
That will give you an output like this:
+------------+-------------+
| product_id | category_id |
+------------+-------------+
| 1 | 1 |
| 2 | 1 |
| 3 | 1 |
| 4 | 2 |
| 5 | 2 |
+------------+-------------+
You can see that each product is listed only once. Joining that table to the other two will not produce duplicates. Therefore, just substitute the above query, as a derived table, for the Product_Category
in your query (also removing your GROUP BY from it, of course):
SELECT
*
FROM
Product
JOIN (
SELECT
product_id,
MIN(category_id) AS category_id
FROM
Product_Category
GROUP BY
product_id
) AS pc USING(product_id)
JOIN Category USING(category_id)
;
Best Answer
I think this problem due to
strict mode
enabled in your MySQL version. Kindly disable strict mode and try again.To disable follow How to turn on/off MySQL strict mode in localhost (xampp)?
STRICT_TRANS_TABLES is responsible for setting MySQL strict mode.
To check whether strict mode is enabled or not run the below sql:
If one of the value is STRICT_TRANS_TABLES, then strict mode is enabled, else not. In my case it gave
Hence strict mode is enabled in my case as one of the value is STRICT_TRANS_TABLES.
To disable strict mode run the below sql:
[or any mode except STRICT_TRANS_TABLES. Ex: set global sql_mode='NO_ENGINE_SUBSTITUTION';]
To again enable strict mode run the below sql:
If you don't what to disable
strict_mode
then you have to change your sql query to follow sqlstandard
.like this..
for your query..
Let me know if that helps.