Why SELECT Works in MariaDB but Not in MySQL – Key Differences

mariadbMySQL

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

Best Answer

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

Related Question