Bug #111659 Empty result of SELECT only when bigger LIMIT

• Submitted: 4 Jul 2023 15:46
• Modified: 5 Jul 2023 12:30
• Reporter: Oleg Man
• Status: Verified
• Category: MySQL Server: Optimizer
• Severity: S2 (Serious)
• Version: 8.0
• OS: Any
• CPU Architecture: Any

[4 Jul 2023 15:46] Oleg Man

Description:
This query without LIMIT or with LIMIT 100+ return empty result, but with LIMIT 1 result exists:

SELECT id
FROM tbl_test_bug
WHERE col_1 = 1 AND col_2 = 1
#LIMIT 1 -- That work

Also works without specifying LIMIT if dropping index "col_1_idx".

How to repeat:
PS See https://dbfiddle.uk/9JsRaJfI

Source data for playback:

-- Create table
DROP TABLE IF EXISTS tbl_test_bug;
CREATE TABLE tbl_test_bug (
  id int UNSIGNED NOT NULL AUTO_INCREMENT,
  col_1 TINYINT UNSIGNED NOT NULL,
  col_2 TINYINT UNSIGNED NOT NULL,
  PRIMARY KEY (id DESC),
  KEY col_1_idx (col_1),
  KEY col_2_idx (col_2)
) ENGINE=InnoDB;

-- Filling the table with random data (800 rows)
DELIMITER ;;
CREATE PROCEDURE gen_rows_test()
BEGIN
  DECLARE idx INT DEFAULT 800;
  WHILE idx > 0 DO
    INSERT INTO tbl_test_bug (col_1, col_2) VALUES (FLOOR(1 + (RAND() * 2)), FLOOR(1 + (RAND() * 10)));
    SET idx = idx - 1;
  END WHILE;
END;;
CALL gen_rows_test();;
DROP PROCEDURE IF EXISTS gen_rows_test;;

[5 Jul 2023 12:30] MySQL Verification Team

Hi Mr. Man,

Thank you for your bug report and very nice test case.

We repeated what you reported with 8.0.33 ....... Without LIMIT, no result set, with LIMIT 5:

+-----+
| id  |
+-----+
| 792 |
| 710 |
| 702 |
| 699 |
| 697 |
+-----+

Verified as reported.