SitePoint Sponsor

User Tag List

Results 1 to 17 of 17
  1. #1
    38911 Basic Bytes Free johnuk's Avatar
    Join Date
    Jul 2008
    Location
    Somerset, England
    Posts
    458
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Illegal mix of collations?

    Hey all,

    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:

    Code MySQL:
    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!

  2. #2
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    Join Date
    Nov 1999
    Location
    Mechanicsburg, PA
    Posts
    7,264
    Mentioned
    115 Post(s)
    Tagged
    1 Thread(s)
    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.

    You can specify a collation, and if you need a list of the valid choices, here you are, or if you want some opinions on the best choice to use, this (non-SPF) thread is an interesting read.
    Dave Maxwell - Manage Your Site Team Leader
    My favorite YouTube Video! | Star Wars, Dr Suess Style
    Learn how to be ready for The Forums' Move to Discourse

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,250
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by johnuk View Post
    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''
    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

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    38911 Basic Bytes Free johnuk's Avatar
    Join Date
    Jul 2008
    Location
    Somerset, England
    Posts
    458
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hello Rudy!

    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

  5. #5
    38911 Basic Bytes Free johnuk's Avatar
    Join Date
    Jul 2008
    Location
    Somerset, England
    Posts
    458
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Oh by the way, here is the results of the SHOW TABLE :

    Code MySQL:
    customers 	CREATE TABLE `customers` (
     `id` int(11) NOT NULL,
     `name` varchar(99) COLLATE utf8_unicode_ci NOT NULL,
     `billaddr` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
     `shipaddr` varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'See billing address.',
     PRIMARY KEY (`id`)
    ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
     
    carts 	CREATE TABLE `carts` (
     `id` int(11) NOT NULL,
     `customer_id` int(11) NOT NULL,
     `cartdate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
     PRIMARY KEY (`id`)
    ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
     
    cartitems 	CREATE TABLE `cartitems` (
     `cart_id` int(11) NOT NULL,
     `item_id` int(11) NOT NULL,
     `qty` smallint(6) NOT NULL DEFAULT '1'
    ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
     
    items 	CREATE TABLE `items` (
     `id` int(11) NOT NULL,
     `name` varchar(21) COLLATE utf8_unicode_ci NOT NULL,
     `type` varchar(7) COLLATE utf8_unicode_ci NOT NULL,
     `price` decimal(5,2) DEFAULT NULL,
     PRIMARY KEY (`id`)
    ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,250
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by johnuk View Post
    What exactly is Collation out of interest?
    this might help...
    http://dev.mysql.com/doc/refman/5.5/...t-general.html

    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    38911 Basic Bytes Free johnuk's Avatar
    Join Date
    Jul 2008
    Location
    Somerset, England
    Posts
    458
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    [QUOTE=r937;5039960]this might help...
    http://dev.mysql.com/doc/refman/5.5/...t-general.html
    Thanks for the info, I will read that document this evening for sure And yes that sounds like a great idea.

    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?

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,250
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by johnuk View Post
    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?
    not really, no

    would you consider re-installing the tables from the book's code and trying again?

    i notice that some of your columns specify a collation and others don't (whereas the sample code provided mentioned no collations at all)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    38911 Basic Bytes Free johnuk's Avatar
    Join Date
    Jul 2008
    Location
    Somerset, England
    Posts
    458
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yeah I will give that a bash, thanks again Rudy!!

  10. #10
    38911 Basic Bytes Free johnuk's Avatar
    Join Date
    Jul 2008
    Location
    Somerset, England
    Posts
    458
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by DaveMaxwell View Post
    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.

    You can specify a collation, and if you need a list of the valid choices, here you are, or if you want some opinions on the best choice to use, this (non-SPF) thread is an interesting read.
    I tried setting COLLATE on the group by clause, but sadly no change

  11. #11
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    Join Date
    Nov 1999
    Location
    Mechanicsburg, PA
    Posts
    7,264
    Mentioned
    115 Post(s)
    Tagged
    1 Thread(s)
    Quote Originally Posted by johnuk View Post
    I tried setting COLLATE on the group by clause, but sadly no change
    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.
    Dave Maxwell - Manage Your Site Team Leader
    My favorite YouTube Video! | Star Wars, Dr Suess Style
    Learn how to be ready for The Forums' Move to Discourse

  12. #12
    38911 Basic Bytes Free johnuk's Avatar
    Join Date
    Jul 2008
    Location
    Somerset, England
    Posts
    458
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    This appears to be the offending column :

    Code MySQL:
    , CAST(COUNT(items.name) AS CHAR) COLLATE utf8_unicode_ci AS item

    As you can see I added the COLLATE keyword, and the query actually runs now! Only the grouping isnt working as it should

  13. #13
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,250
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by johnuk View Post
    Only the grouping isnt working as it should
    then you've done something else wrong
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  14. #14
    38911 Basic Bytes Free johnuk's Avatar
    Join Date
    Jul 2008
    Location
    Somerset, England
    Posts
    458
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yeah, im beyond confused now to be honest! Think im going to give it a rest

  15. #15
    38911 Basic Bytes Free johnuk's Avatar
    Join Date
    Jul 2008
    Location
    Somerset, England
    Posts
    458
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I got it working heres my query..

    Code MySQL:
    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

  16. #16
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,250
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by johnuk View Post
    ... I removed the CHAR cast from the Group Query - im not sure why a cast was being performed, but the query runs without it.
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  17. #17
    38911 Basic Bytes Free johnuk's Avatar
    Join Date
    Jul 2008
    Location
    Somerset, England
    Posts
    458
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    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

    I get you Rudy! Oh and thanks again for all your help, Dave Maxwell too.. thanks!


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •