SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";

CREATE TABLE `item` (
  `id` int(11) NOT NULL,
  `content` longtext COLLATE utf8_polish_ci NOT NULL,
  `title` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_polish_ci;

INSERT INTO `item` (`id`, `content`, `title`) VALUES
(1, 'some content', 123),
(2, 'some content 2', 456);

CREATE TABLE `product` (
  `item_parent_id` int(11) NOT NULL,
  `subtitle` varchar(200) COLLATE utf8_polish_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_polish_ci;

INSERT INTO `product` (`item_parent_id`, `subtitle`) VALUES
(1, 'some title'),
(2, 'some title 2');

CREATE TABLE `review` (
  `id` int(11) NOT NULL,
  `description` longtext COLLATE utf8_polish_ci NOT NULL,
  `rating` int(11) NOT NULL,
  `item_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_polish_ci;

INSERT INTO `review` (`id`, `description`, `rating`, `item_id`) VALUES
(1, 'dsadsaads', 2, 1),
(2, 'sadsa', 4, 1),
(3, 'dd', 1, 2),
(4, 'asddas', 2, 2);


ALTER TABLE `item`
  ADD PRIMARY KEY (`id`);

ALTER TABLE `product`
  ADD KEY `item_parent_id` (`item_parent_id`);

ALTER TABLE `review`
  ADD PRIMARY KEY (`id`),
  ADD KEY `item_id` (`item_id`);


ALTER TABLE `item`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=3;
ALTER TABLE `review`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=5;

ALTER TABLE `product`
  ADD CONSTRAINT `product_ibfk_1` FOREIGN KEY (`item_parent_id`) REFERENCES `item` (`id`);

ALTER TABLE `review`
  ADD CONSTRAINT `review_ibfk_1` FOREIGN KEY (`item_id`) REFERENCES `item` (`id`);
  
  /*
  This query bellow should  output 
| id |        content | title | testowo |
|----|----------------|-------|---------|
|  2 | some content 2 |   456 |     1.5 |
|  1 |   some content |   123 |       3 |


but it's outputing result in wrong order, ORDER BY testowo DESC does output the same result

| id |        content | title | testowo |
|----|----------------|-------|---------|
|  1 |   some content |   123 |       3 |
|  2 | some content 2 |   456 |     1.5 |
  */
SELECT
  `item`.`id`,
  `item`.`content`,
  `item`.`title`,
  AVG(`review`.`rating`) AS 'testowo'
FROM
  `product`
INNER JOIN
  `item` ON(
    `product`.`item_parent_id` = `item`.`id`
  )
LEFT OUTER JOIN
  `review` ON(`item`.`id` = `review`.`item_id`)
GROUP BY
  `product`.`item_parent_id`
ORDER BY
  `testowo` ASC
  
  /*
  
when I remove `item`.`content` type of longtext result is ordered correctly
| id | title | testowo |
|----|-------|---------|
|  2 |   456 |     1.5 |
|  1 |   123 |       3 |
  */
  
SELECT
  `item`.`id`,
  `item`.`content`,
  `item`.`title`,
  AVG(`review`.`rating`) AS 'testowo'
FROM
  `product`
INNER JOIN
  `item` ON(
    `product`.`item_parent_id` = `item`.`id`
  )
LEFT OUTER JOIN
  `review` ON(`item`.`id` = `review`.`item_id`)
GROUP BY
  `product`.`item_parent_id`
ORDER BY
  `testowo` ASC