4 
set @row_number = 0;
SELECT 
 *
FROM
 (SELECT 
 (@row_number:=@row_number + 1) AS num,
 id,
 tbl_user_id,
 title,
 description,
 length lengths,
 create_date,
 file_size,
 thumbnails,
 videos.itsOK,
 viewed
 FROM
 tbl_videos videos
 WHERE
 videos.tbl_user_id = 23
 AND videos.tbl_category_id = 265
 ORDER BY videos.create_date DESC
) AS paginateTbl
WHERE
 paginateTbl.num > 0
 && paginateTbl.num <= 9

mysql results: mysql results

mariadb results: enter image description here

Inner query work on both of them but main query work only in mariadb! What's the matter in mysql not working?

Versions used are mysql: 5.5.44-0ubuntu0.14.04.1 and mariadb 10.0.13-MariaDB-log.

The CREATE TABLE statements are identical (except for the AUTO_INCREMENT, number of rows):

MySQL result:

SHOW CREATE TABLE tbl_videos;
CREATE TABLE `tbl_videos` (
 `id` INT (20) NOT NULL AUTO_INCREMENT
 ,`title` VARCHAR(255) COLLATE utf8_persian_ci NOT NULL
 ,`description` TEXT COLLATE utf8_persian_ci NOT NULL
 ,`tags` TEXT COLLATE utf8_persian_ci NOT NULL
 ,`video_quality` VARCHAR(255) COLLATE utf8_persian_ci NOT NULL
 ,`dl_link1` VARCHAR(255) COLLATE utf8_persian_ci NOT NULL
 ,`dl_link2` VARCHAR(255) COLLATE utf8_persian_ci NOT NULL
 ,`dl_link3` VARCHAR(255) COLLATE utf8_persian_ci NOT NULL
 ,`viewed` INT (11) NOT NULL
 ,`viewed_duration` VARCHAR(255) COLLATE utf8_persian_ci NOT NULL
 ,`viewed_traffic` VARCHAR(255) COLLATE utf8_persian_ci NOT NULL
 ,`embed_code` VARCHAR(255) COLLATE utf8_persian_ci NOT NULL
 ,`sharing_code` VARCHAR(255) COLLATE utf8_persian_ci NOT NULL
 ,`replace_times` INT (11) NOT NULL
 ,`actual_link` TEXT COLLATE utf8_persian_ci NOT NULL
 ,`tbl_user_id` INT (11) NOT NULL
 ,`tbl_category_id` INT (11) NOT NULL
 ,`tbl_player_id` INT (11) NOT NULL
 ,`itsOK` TINYINT (2) NOT NULL
 ,`length` INT (20) NOT NULL
 ,`create_date` INT (11) NOT NULL
 ,`modified_date` INT (11) NOT NULL
 ,`thumbnails` TEXT COLLATE utf8_persian_ci
 ,`serverId` VARCHAR(32) COLLATE utf8_persian_ci NOT NULL
 ,`sizes` VARCHAR(100) COLLATE utf8_persian_ci DEFAULT NULL
 ,`our_server_link` VARCHAR(255) COLLATE utf8_persian_ci DEFAULT NULL
 ,`like` INT (11) NOT NULL DEFAULT '0'
 ,`file_size` FLOAT DEFAULT NULL
 ,`islogo` TEXT COLLATE utf8_persian_ci
 ,`uuid` VARCHAR(64) COLLATE utf8_persian_ci DEFAULT NULL
 ,`output_type` VARCHAR(255) COLLATE utf8_persian_ci DEFAULT NULL
 ,`video_file` VARCHAR(255) COLLATE utf8_persian_ci DEFAULT NULL
 ,`video_setting` TEXT COLLATE utf8_persian_ci NOT NULL
 ,`soft_hard` VARCHAR(255) COLLATE utf8_persian_ci DEFAULT NULL
 ,`soft_hard_logo` VARCHAR(255) COLLATE utf8_persian_ci NOT NULL
 ,`vastTag` TEXT COLLATE utf8_persian_ci
 ,`extra_cat_id` INT (11) NOT NULL DEFAULT '0'
 ,`all_terafic` BIGINT (20) NOT NULL DEFAULT '0'
 ,PRIMARY KEY (`id`)
 ,KEY `tbl_user_id`(`tbl_user_id`)
 ,KEY `tbl_category_id`(`tbl_category_id`)
 ,CONSTRAINT `tbl_videos_ibfk_1` FOREIGN KEY (`tbl_user_id`) REFERENCES `tbl_users`(`id`) ON DELETE CASCADE ON UPDATE CASCADE
 ,CONSTRAINT `tbl_videos_ibfk_2` FOREIGN KEY (`tbl_category_id`) REFERENCES `tbl_categories`(`id`) ON DELETE CASCADE ON UPDATE CASCADE
 ) ENGINE = InnoDB AUTO_INCREMENT = 4622 DEFAULT CHARSET = utf8 COLLATE = utf8_persian_ci

MariaDB result:

SHOW CREATE TABLE tbl_videos;
CREATE TABLE `tbl_videos` (
 `id` INT (20) NOT NULL AUTO_INCREMENT
 ,`title` VARCHAR(255) COLLATE utf8_persian_ci NOT NULL
 ,`description` TEXT COLLATE utf8_persian_ci NOT NULL
 ,`tags` TEXT COLLATE utf8_persian_ci NOT NULL
 ,`video_quality` VARCHAR(255) COLLATE utf8_persian_ci NOT NULL
 ,`dl_link1` VARCHAR(255) COLLATE utf8_persian_ci NOT NULL
 ,`dl_link2` VARCHAR(255) COLLATE utf8_persian_ci NOT NULL
 ,`dl_link3` VARCHAR(255) COLLATE utf8_persian_ci NOT NULL
 ,`viewed` INT (11) NOT NULL
 ,`viewed_duration` VARCHAR(255) COLLATE utf8_persian_ci NOT NULL
 ,`viewed_traffic` VARCHAR(255) COLLATE utf8_persian_ci NOT NULL
 ,`embed_code` VARCHAR(255) COLLATE utf8_persian_ci NOT NULL
 ,`sharing_code` VARCHAR(255) COLLATE utf8_persian_ci NOT NULL
 ,`replace_times` INT (11) NOT NULL
 ,`actual_link` TEXT COLLATE utf8_persian_ci NOT NULL
 ,`tbl_user_id` INT (11) NOT NULL
 ,`tbl_category_id` INT (11) NOT NULL
 ,`tbl_player_id` INT (11) NOT NULL
 ,`itsOK` TINYINT (2) NOT NULL
 ,`length` INT (20) NOT NULL
 ,`create_date` INT (11) NOT NULL
 ,`modified_date` INT (11) NOT NULL
 ,`thumbnails` TEXT COLLATE utf8_persian_ci
 ,`serverId` VARCHAR(32) COLLATE utf8_persian_ci NOT NULL
 ,`sizes` VARCHAR(100) COLLATE utf8_persian_ci DEFAULT NULL
 ,`our_server_link` VARCHAR(255) COLLATE utf8_persian_ci DEFAULT NULL
 ,`like` INT (11) NOT NULL DEFAULT '0'
 ,`file_size` FLOAT DEFAULT NULL
 ,`islogo` TEXT COLLATE utf8_persian_ci
 ,`uuid` VARCHAR(64) COLLATE utf8_persian_ci DEFAULT NULL
 ,`output_type` VARCHAR(255) COLLATE utf8_persian_ci DEFAULT NULL
 ,`video_file` VARCHAR(255) COLLATE utf8_persian_ci DEFAULT NULL
 ,`video_setting` TEXT COLLATE utf8_persian_ci NOT NULL
 ,`soft_hard` VARCHAR(255) COLLATE utf8_persian_ci DEFAULT NULL
 ,`soft_hard_logo` VARCHAR(255) COLLATE utf8_persian_ci NOT NULL
 ,`vastTag` TEXT COLLATE utf8_persian_ci
 ,`extra_cat_id` INT (11) NOT NULL DEFAULT '0'
 ,`all_terafic` BIGINT (20) NOT NULL DEFAULT '0'
 ,PRIMARY KEY (`id`)
 ,KEY `tbl_user_id`(`tbl_user_id`)
 ,KEY `tbl_category_id`(`tbl_category_id`)
 ,CONSTRAINT `tbl_videos_ibfk_1` FOREIGN KEY (`tbl_user_id`) REFERENCES `tbl_users`(`id`) ON DELETE CASCADE ON UPDATE CASCADE
 ,CONSTRAINT `tbl_videos_ibfk_2` FOREIGN KEY (`tbl_category_id`) REFERENCES `tbl_categories`(`id`) ON DELETE CASCADE ON UPDATE CASCADE
 ) ENGINE = InnoDB AUTO_INCREMENT = 9387 DEFAULT CHARSET = utf8 COLLATE = utf8_persian_ci

mysql results:

EXPLAIN SELECT * from FROM ...
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY <derived2> ALL NULL NULL NULL NULL 14 Using where
2 DERIVED videos index_merge tbl_user_id,tbl_category_id tbl_category_id,tbl_user_id 4,4 NULL 1 Using intersect(tbl_category_id,tbl_user_id); Using where; Using filesort

mariadb results:

EXPLAIN SELECT * from tbl_videos
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY <derived2> ALL NULL NULL NULL NULL 2 Using where
2 DERIVED videos index_merge tbl_user_id,tbl_category_id tbl_category_id,tbl_user_id 4,4 NULL 1 Using intersect(tbl_category_id,tbl_user_id); Using where; Using filesort
asked Sep 5, 2015 at 7:23
14
  • mysql '5.5.44-0ubuntu0.14.04.1' Commented Sep 5, 2015 at 7:36
  • mariadb '10.0.13-MariaDB-log' Commented Sep 5, 2015 at 7:36
  • From what I see, seems your queries both run fine. But the mysql table has rows without any data in them. Perhaps add (AND length>0) in the inner query to show results that have some useful data and check again. Commented Sep 5, 2015 at 10:26
  • Inner query work on both of them but main query work only in mariadb! Commented Sep 5, 2015 at 10:35
  • The query returns 9 rows in both mysql and mariadb. So it works fine. The different output is because you have different data in the 2 tables. When you say it dosn't work, what do you mean? If you have no valuable data in the table, the query can't magically show something. Commented Sep 5, 2015 at 12:09

1 Answer 1

4

It's MySQL 5.5 bug which reported to MySQL. so I installed mysql 5.6 and main query works well. same query with @@version in results.

set @row_number = 0;
SELECT 
 *, @@version mysql_version
FROM
 (SELECT 
 (@row_number:=@row_number + 1) AS num,
 id,
 tbl_user_id,
 title,
 description,
 length lengths,
 create_date,
 file_size,
 thumbnails,
 videos.itsOK,
 viewed
 FROM
 tbl_videos videos
 WHERE
 videos.tbl_user_id = 9
 AND videos.tbl_category_id = 113
 AND length > 0
 ORDER BY videos.create_date ASC
) AS paginateTbl
WHERE
 paginateTbl.num > 0
 && paginateTbl.num <= 9

mysql 5.5 & mysql 5.6 main query results: mysql 5.5 bug - main query mysql 5.6 - main qu

Now I check a special id eg: 1103 in tbl_videos and both select works well. 

SELECT 
 id,
 tbl_user_id,
 title,
 description,
 length lengths,
 create_date,
 file_size,
 thumbnails,
 itsOK,
 viewed,
 @@version mysql_version
FROM
 tbl_videos
WHERE
 id = 1103

mysql 5.5 & mysql 5.6 results: mysql 5.5 bug - tiny query mysql 5.6 - tiny query

answered Sep 7, 2015 at 5:42
4
  • 2
    Did you find which bug exactly it was (a link to mysql bugs) and when it was fixed? Commented Sep 7, 2015 at 7:56
  • 1
    There would be more upvotes coming your way if you could provide more specific details of the bug in this answer. It would help people know if the problem they are seeing is likely to be fixed the same way (or if they are unable to upgrade immediately could give them the clues needed to work around or otherwise mitigate the problem in the meantime) Commented Sep 7, 2015 at 8:36
  • Would you like to send the screen shot of mysql 5.5 and mysql 5.6 of retured data? Commented Sep 7, 2015 at 8:38
  • Nice. I think you should also make a bug request at the MySQL site. 5.5 is still a supported version and bugs like this should be fixed. Commented Sep 7, 2015 at 12:11

Your Answer

Draft saved
Draft discarded

Sign up or log in

Sign up using Google
Sign up using Email and Password

Post as a guest

Required, but never shown

Post as a guest

Required, but never shown

By clicking "Post Your Answer", you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.