CODEWITHSUNDEEP
mysqlphpmyadmin
SubjectCurio
SubjectCurio

Reputation: 4872

phpMyAdmin incorrect results count

Stumbled across potentially a bug(?) within phpMyAdmin, although it's more likely to maybe be my misunderstanding of MySQL, so was hoping someone could shed some light on this behaviour.

Using the following schema

CREATE TABLE IF NOT EXISTS `mlfsql_test` (
    `id` int(11) NOT NULL,
    `frequency_length` smallint(3) DEFAULT NULL,
    `frequency_units` varchar(10) DEFAULT NULL,
    `next_delivery_date` date DEFAULT NULL,
    `last_created_delivery_date` date DEFAULT NULL
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;

INSERT INTO `mlfsql_test` 
(`id`, `frequency_length`, `frequency_units`, `next_delivery_date`, `last_created_delivery_date`) VALUES
(1, 2, 'week', '2014-06-25', NULL),
(2, 3, 'day', '2014-06-27', NULL),
(3, 1, 'week', '2014-08-08', NULL),
(4, 2, 'day', NULL, '2014-06-26');

I want to determine rows with an upcoming delivery, based on their currently set delivery date, or their last delivery date with the frequency taken into consideration.

Came up with the following query which works fine:

    SELECT *, IF (next_delivery_date IS NOT NULL, next_delivery_date,
        CASE frequency_units
            WHEN 'day' THEN DATE_ADD(last_created_delivery_date, INTERVAL frequency_length DAY)
            WHEN 'week' THEN DATE_ADD(last_created_delivery_date, INTERVAL frequency_length WEEK)
            WHEN 'month' THEN DATE_ADD(last_created_delivery_date, INTERVAL frequency_length MONTH)
        END)
    AS next_order_due_date
    FROM mlfsql_test
    HAVING next_order_due_date IS NULL OR (next_order_due_date BETWEEN CURDATE() AND CURDATE() + INTERVAL 10 DAY)

With the data currently in the table, I am expecting it to return 3 rows, but phpMyAdmin states there are a total of 4 rows of results, although it only displays 3...

picture 1

I've found that if I add a WHERE clause to my query such as WHERE 1, it'll return the 3 rows and also state that there is a total of 3.

enter image description here

Why does it give an incorrect number of returned rows without the WHERE clause? I'm assuming without one phpMyAdmin assumes that all rows will match, however only returns those that actually did, so the count is wrong? Any help would be appreciated.

Edit: phpMyAdmin Version 4.2.0

Upvotes: 3

Views: 1687

Answers (1)

Chirayu Chiripal
Chirayu Chiripal

Reputation: 610

This seems to be a bug in phpMyAdmin v4.2.x. I have opened a bug ticket (see Bug #4473). I have also proposed a fix for this bug to them (see PR #1253). You can also apply this patch to fix it in v4.2.4. This is most likely to be fixed in upcoming bugfix release i.e. v4.2.5.

Edit 1: My patch was accepted and this issue is fixed in v4.2.5 (upcoming minor release).

Upvotes: 2

Related Questions