I am working through the sitepoint book Simply SQL, nearly finished the section of the book on SQL but I got this error 'Illegal mix of collations for operation 'UNION'' when running the following query from the books:
SELECT
*
FROM (
SELECT
customers.name AS customer
, carts.id AS cart
, items.name AS item
, cartitems.qty
, items.price
, cartitems.qty
* items.price AS total
FROM
customers
INNER JOIN carts
ON carts.customer_id = customers.id
INNER JOIN cartitems
ON cartitems.cart_id = carts.id
INNER JOIN items
ON items.id = cartitems.item_id
UNION ALL
SELECT
customers.name AS customer
, NULL AS cart
, CAST(COUNT(items.name) AS CHAR) AS item
, NULL AS qty
, NULL AS price
, SUM(cartitems.qty
* items.price) AS total
FROM
customers
INNER JOIN carts
ON carts.customer_id = customers.id
INNER JOIN cartitems
ON cartitems.cart_id = carts.id
INNER JOIN items
ON items.id = cartitems.item_id
GROUP BY
customers.name
) AS dt
ORDER BY
customer
, cart
, item
;
Could someone kindly tell me what this error means, and why im getting it? thanks!
Not 100% sure how mySQL works with collations, but based on what I know from other RDBMS, the problem is the group by changes the collation and UNIONs require matching collations to put the two sets of records together.
i have to admit, i never ran across any sort of collation problems when i was doing the testing for the book
i simply created my tables without ever specifying any collations, so presumably i got the defaults
if you do a SHOW CREATE TABLE for each of the tables, this might uncover something where an individual column was given a different collation from the default
other than that, i have no idea about where your error came from
by the way, that UNION query (with its “interleaving” of total and detail rows) is the trickiest in the whole book, so once you’re past it, you’ll be a qualified SQL master
Thankyou very much for your reply. I have to say, this is an extremely well written book - and I dont say that often! I usually go off tech books after reading a few pages due to being so dry but this wasnt the case.
What exactly is Collation out of interest? It would be good to get this final query running for sure Also I was wondering if anyone can suggest a good way to consolidate the principles I learnt in this book? I dont have any SQL based projects on the go at present that I can test my new skills on
thanks for the kind words about the book – it took a lot of work to make it seem so simple
as for testing your skills, perhaps you could build a rudimentary CMS? using the tables in the book, but adding additional columns or even tables to flesh it out more towards a real-world app
By the way, with regards to the aforementioned error, both of the queries in the UNION work individually but not as part of that ‘mega query’ - any idea why this might be?
Put the collate on both statements to ensure you’re getting the same collation. That should right the ship…at least it did in SQL Server when I ran into this exact issue.
SELECT
*
FROM (
SELECT
customers.name AS customer
, carts.id AS cart
, items.name AS item
, cartitems.qty
, items.price
, cartitems.qty
* items.price AS total
FROM
customers
INNER JOIN carts
ON carts.customer_id = customers.id
INNER JOIN cartitems
ON cartitems.cart_id = carts.id
INNER JOIN items
ON items.id = cartitems.item_id
UNION ALL
SELECT
customers.name AS customer
, NULL AS cart
, COUNT(items.name) AS item
, NULL AS qty
, NULL AS price
, SUM(cartitems.qty
* items.price) AS total
FROM
customers
INNER JOIN carts
ON carts.customer_id = customers.id
INNER JOIN cartitems
ON cartitems.cart_id = carts.id
INNER JOIN items
ON items.id = cartitems.item_id
GROUP BY
customers.name
) AS dt
ORDER BY
customer
, cart
, item
;
A good nights sleep was all that was needed (my client had me up fixing Javascript bugs all evening). I re-wrote the query from scratch, this time I removed the CHAR cast from the Group Query - im not sure why a cast was being performed, but the query runs without it.
I didnt even have to set the COLLATE key word! I also noticed that some of the data in the tables was slightly different between the book and the download, so I dropped all the tables and started again
that column (called “item”) in the union query results is supposed to be a character column – see the first SELECT where item.name is assigned the “item” alias
the CAST of the COUNT(), which is numeric, was intended to teach the point that columns in a union must be datatype compatible
