[MDEV-4711] SQL_CALC_FOUND_ROWS yields wrong result - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Major
Resolution: Fixed
Affects Version/s: 10.0.3
Fix Version/s: 10.0.8
Component/s: None
Labels:
None
Environment:
Ubuntu 12.10, MariaDB 10.0.3 from the mariadb.org repository

Description

CREATE TEMPORARY TABLE A(`id` mediumint, `value` mediumint, PRIMARY KEY(`id`,`value`));

INSERT INTO A VALUES (1,1),(2,2),(3,3);

CREATE TEMPORARY TABLE B(`id` mediumint, PRIMARY KEY(`id`));

INSERT INTO B VALUES (1),(2),(3);

SELECT SQL_CALC_FOUND_ROWS 1 FROM A LEFT JOIN B ON A.`id` = B.`id` WHERE B.`id` IS NULL ORDER BY `value` DESC;

SELECT FOUND_ROWS() AS `count`;

SELECT COUNT(*) FROM (SELECT 1 FROM A LEFT JOIN B ON A.`id` = B.`id` WHERE B.`id` IS NULL ORDER BY `value` DESC) AS `x`;

Expected result:
0
0
(as the left join can never fail)

Actual result:
3
0

This means that SQL_CALC_FOUND_ROWS ignores the fact that all rows will be filtered.

Attachments

Issue Links

relates to

MDEV-6221 SQL_CALC_FOUND_ROWS yields wrong result again

Closed

MDEV-7219 SQL_CALC_FOUND_ROWS yields wrong result

Closed

Activity

Ascending order - Click to sort in descending order

View 10 older comments

Elena Stepanova added a comment - 2014-05-08 18:57

Thanks. Would you mind creating a new bug report?

Elena Stepanova added a comment - 2014-05-08 18:57 Thanks. Would you mind creating a new bug report?

Serg G. Brester added a comment - 2014-05-08 19:19

Thanks. Would you mind creating a new bug report?
You're welcome. New bug report for same bug? Could this closed ticket not be re-opened? Sorry, I am new here...

SQL statement below might possibly help with troubleshooting.
If we remove primary key from second! (left joined) table, it works fine again.
But why it is so?! Imho, the criteria, that produce this wrong result, is "OR A.`value` = 5".

-- *** create test tables:

CREATE TEMPORARY TABLE A(`id` mediumint, `value` mediumint, PRIMARY KEY(`id`,`value`));

INSERT INTO A VALUES (1,1),(2,2),(3,3);

CREATE TEMPORARY TABLE B(`id` mediumint, `val` mediumint, PRIMARY KEY(`id`));

INSERT INTO B VALUES (1,5),(2,5),(3,10);

CREATE TEMPORARY TABLE C(`id` mediumint, `val` mediumint, PRIMARY KEY(`id`,`val`));

INSERT INTO C VALUES (1,5),(2,5),(3,10);

CREATE TEMPORARY TABLE D(`id` mediumint, `val` mediumint);

INSERT INTO D VALUES (1,5),(2,5),(3,10);

-- *** WRONG count, returns 3 instead of 2 :

SELECT SQL_CALC_FOUND_ROWS 1 FROM A

LEFT JOIN B ON A.`id` = B.`id`

WHERE B.`val` = 5

  OR A.`value` = 5

ORDER BY `value` DESC

LIMIT 1; SELECT FOUND_ROWS() AS `count`;

-- *** WRONG count also, returns 3 instead of 2 :

SELECT SQL_CALC_FOUND_ROWS 1 FROM A

LEFT JOIN C ON A.`id` = C.`id`

WHERE C.`val` = 5

  OR A.`value` = 5

ORDER BY `value` DESC

LIMIT 1; SELECT FOUND_ROWS() AS `count`;

-- *** CORRECT count, returns 2 :

SELECT SQL_CALC_FOUND_ROWS 1 FROM A

LEFT JOIN D ON A.`id` = D.`id`

WHERE D.`val` = 5

  OR A.`value` = 5

ORDER BY `value` DESC

LIMIT 1; SELECT FOUND_ROWS() AS `count`;

Result:

count – with primary – should return 2
3

count – with primary, but 'id' together with 'val', should return 2 also
3

count – without primary
2

Serg G. Brester added a comment - 2014-05-08 19:19 Thanks. Would you mind creating a new bug report? You're welcome. New bug report for same bug? Could this closed ticket not be re-opened? Sorry, I am new here... SQL statement below might possibly help with troubleshooting. If we remove primary key from second! (left joined) table, it works fine again. But why it is so?! Imho, the criteria, that produce this wrong result, is "OR A.`value` = 5". -- *** create test tables: CREATE TEMPORARY TABLE A(`id` mediumint, `value` mediumint, PRIMARY KEY (`id`,`value`)); INSERT INTO A VALUES (1,1),(2,2),(3,3); CREATE TEMPORARY TABLE B(`id` mediumint, `val` mediumint, PRIMARY KEY (`id`)); INSERT INTO B VALUES (1,5),(2,5),(3,10); CREATE TEMPORARY TABLE C(`id` mediumint, `val` mediumint, PRIMARY KEY (`id`,`val`)); INSERT INTO C VALUES (1,5),(2,5),(3,10); CREATE TEMPORARY TABLE D(`id` mediumint, `val` mediumint); INSERT INTO D VALUES (1,5),(2,5),(3,10); -- *** WRONG count, returns 3 instead of 2 : SELECT SQL_CALC_FOUND_ROWS 1 FROM A LEFT JOIN B ON A.`id` = B.`id` WHERE B.`val` = 5 OR A.`value` = 5 ORDER BY `value` DESC LIMIT 1; SELECT FOUND_ROWS() AS ` count `; -- *** WRONG count also, returns 3 instead of 2 : SELECT SQL_CALC_FOUND_ROWS 1 FROM A LEFT JOIN C ON A.`id` = C.`id` WHERE C.`val` = 5 OR A.`value` = 5 ORDER BY `value` DESC LIMIT 1; SELECT FOUND_ROWS() AS ` count `; -- *** CORRECT count, returns 2 : SELECT SQL_CALC_FOUND_ROWS 1 FROM A LEFT JOIN D ON A.`id` = D.`id` WHERE D.`val` = 5 OR A.`value` = 5 ORDER BY `value` DESC LIMIT 1; SELECT FOUND_ROWS() AS ` count `; Result: count – with primary – should return 2 3 count – with primary, but 'id' together with 'val', should return 2 also 3 count – without primary 2

Elena Stepanova added a comment - 2014-05-08 21:28

New bug report for same bug? Could this closed ticket not be re-opened?

Yes, in this case a new report is preferable.
the bug was fixed at some point, if you try your test case on 10.0.8 or 10.0.9, it should work all right.
The problem was re-introduced in 10.0.10 by the fix for ~~MDEV-5898~~ (revision-id: sergii@pisem.net-20140319193312-w2xror087po3mx1u).
It will be quite confusing if a bug fix for the same bug appears in the bzr history and in release change logs for different revisions.

You don't have to do it if you don't want to, I can create the bug report; it's just that the original reporter always has first dibs on filing one.

Elena Stepanova added a comment - 2014-05-08 21:28 New bug report for same bug? Could this closed ticket not be re-opened? Yes, in this case a new report is preferable. the bug was fixed at some point, if you try your test case on 10.0.8 or 10.0.9, it should work all right. The problem was re-introduced in 10.0.10 by the fix for MDEV-5898 (revision-id: sergii@pisem.net-20140319193312-w2xror087po3mx1u). It will be quite confusing if a bug fix for the same bug appears in the bzr history and in release change logs for different revisions. You don't have to do it if you don't want to, I can create the bug report; it's just that the original reporter always has first dibs on filing one.

Serg G. Brester added a comment - 2014-05-09 01:49

the bug was fixed at some point, if you try your test case on 10.0.8 or 10.0.9, it should work all right

I don't think so... The original test case from description works still good on 10.0.10... I has as result 0/0 (not 3/0 as before 10.0.8). So I think, the bug was not fixed everywhere (with other words not all possible variants of this). I wrote even:

I found another case, where FOUND_ROWS works wrong.

At the moment is unfortunately not possible to test 10.0.8 or 10.0.9 (I'm in the middle of moving/upgrading of my mashines), and don't want to test in production, whether ~~MDEV-5898~~ really makes it broken. Had someone opportunity to check it on mentioned versions?

If it should be nevertheless reported as a new bug, so I do.

Serg G. Brester added a comment - 2014-05-09 01:49 the bug was fixed at some point, if you try your test case on 10.0.8 or 10.0.9, it should work all right I don't think so... The original test case from description works still good on 10.0.10... I has as result 0/0 (not 3/0 as before 10.0.8). So I think, the bug was not fixed everywhere (with other words not all possible variants of this). I wrote even: I found another case, where FOUND_ROWS works wrong. At the moment is unfortunately not possible to test 10.0.8 or 10.0.9 (I'm in the middle of moving/upgrading of my mashines), and don't want to test in production, whether MDEV-5898 really makes it broken. Had someone opportunity to check it on mentioned versions? If it should be nevertheless reported as a new bug, so I do.

Elena Stepanova added a comment - 2014-05-09 02:06

The original test case from description works still good on 10.0.10... I has as result 0/0 (not 3/0 as before 10.0.8)

Actually, it doesn't:

MariaDB [test]> CREATE TEMPORARY TABLE A(`id` mediumint, `value` mediumint, PRIMARY KEY(`id`,`value`));

Query OK, 0 rows affected (0.23 sec)

MariaDB [test]> INSERT INTO A VALUES (1,1),(2,2),(3,3);

Query OK, 3 rows affected (0.00 sec)

Records: 3  Duplicates: 0  Warnings: 0

MariaDB [test]> CREATE TEMPORARY TABLE B(`id` mediumint, PRIMARY KEY(`id`));

Query OK, 0 rows affected (0.17 sec)

MariaDB [test]> INSERT INTO B VALUES (1),(2),(3);

Query OK, 3 rows affected (0.01 sec)

Records: 3  Duplicates: 0  Warnings: 0

MariaDB [test]> SELECT SQL_CALC_FOUND_ROWS 1 FROM A LEFT JOIN B ON A.`id` = B.`id` WHERE B.`id` IS NULL ORDER BY `value` DESC;

Empty set (0.02 sec)

MariaDB [test]> SELECT FOUND_ROWS() AS `count`;

+-------+

| count |

+-------+

|     3 |

+-------+

1 row in set (0.00 sec)

MariaDB [test]> SELECT COUNT(*) FROM (SELECT 1 FROM A LEFT JOIN B ON A.`id` = B.`id` WHERE B.`id` IS NULL ORDER BY `value` DESC) AS `x`;

+----------+

| COUNT(*) |

+----------+

|        0 |

+----------+

1 row in set (0.00 sec)

MariaDB [test]> select @@version;

+-----------------+

| @@version       |

+-----------------+

| 10.0.10-MariaDB |

+-----------------+

1 row in set (0.00 sec)

It's rather interesting that it works for you, maybe you have something in your configuration that prevents it from failing.
Answering your other question,

Had someone opportunity to check it on mentioned versions?

here you go:

MariaDB [test]> CREATE TEMPORARY TABLE A(`id` mediumint, `value` mediumint, PRIMARY KEY(`id`,`value`));

Query OK, 0 rows affected (0.19 sec)

MariaDB [test]> INSERT INTO A VALUES (1,1),(2,2),(3,3);

Query OK, 3 rows affected (0.01 sec)

Records: 3  Duplicates: 0  Warnings: 0

MariaDB [test]> CREATE TEMPORARY TABLE B(`id` mediumint, `val` mediumint, PRIMARY KEY(`id`));

Query OK, 0 rows affected (0.15 sec)

MariaDB [test]> INSERT INTO B VALUES (1,5),(2,5),(3,10);

Query OK, 3 rows affected (0.01 sec)

Records: 3  Duplicates: 0  Warnings: 0

MariaDB [test]> CREATE TEMPORARY TABLE C(`id` mediumint, `val` mediumint, PRIMARY KEY(`id`,`val`));

Query OK, 0 rows affected (0.17 sec)

MariaDB [test]> INSERT INTO C VALUES (1,5),(2,5),(3,10);

Query OK, 3 rows affected (0.00 sec)

Records: 3  Duplicates: 0  Warnings: 0

MariaDB [test]> CREATE TEMPORARY TABLE D(`id` mediumint, `val` mediumint);

Query OK, 0 rows affected (0.19 sec)

MariaDB [test]> INSERT INTO D VALUES (1,5),(2,5),(3,10);

Query OK, 3 rows affected (0.00 sec)

Records: 3  Duplicates: 0  Warnings: 0

MariaDB [test]> -- *** WRONG count, returns 3 instead of 2 :

MariaDB [test]> SELECT SQL_CALC_FOUND_ROWS 1 FROM A

    -> LEFT JOIN B ON A.`id` = B.`id`

    -> WHERE B.`val` = 5

    ->   OR A.`value` = 5

    -> ORDER BY `value` DESC

    -> LIMIT 1; SELECT FOUND_ROWS() AS `count`;

+---+

| 1 |

+---+

| 1 |

+---+

1 row in set (0.00 sec)

+-------+

| count |

+-------+

|     2 |

+-------+

1 row in set (0.00 sec)

MariaDB [test]> -- *** WRONG count also, returns 3 instead of 2 :

MariaDB [test]> SELECT SQL_CALC_FOUND_ROWS 1 FROM A

    -> LEFT JOIN C ON A.`id` = C.`id`

    -> WHERE C.`val` = 5

    ->   OR A.`value` = 5

    -> ORDER BY `value` DESC

    -> LIMIT 1; SELECT FOUND_ROWS() AS `count`;

+---+

| 1 |

+---+

| 1 |

+---+

1 row in set (0.00 sec)

+-------+

| count |

+-------+

|     2 |

+-------+

1 row in set (0.00 sec)

MariaDB [test]> -- *** CORRECT count, returns 2 :

MariaDB [test]> SELECT SQL_CALC_FOUND_ROWS 1 FROM A

    -> LEFT JOIN D ON A.`id` = D.`id`

    -> WHERE D.`val` = 5

    ->   OR A.`value` = 5

    -> ORDER BY `value` DESC

    -> LIMIT 1; SELECT FOUND_ROWS() AS `count`;

+---+

| 1 |

+---+

| 1 |

+---+

1 row in set (0.00 sec)

+-------+

| count |

+-------+

|     2 |

+-------+

1 row in set (0.00 sec)

MariaDB [test]> select @@version;

+----------------+

| @@version      |

+----------------+

| 10.0.9-MariaDB |

+----------------+

1 row in set (0.00 sec)

And yes, even then it's still possible that the original fix was not complete, but it doesn't change anything. The new report should be created anyway.

Elena Stepanova added a comment - 2014-05-09 02:06 The original test case from description works still good on 10.0.10... I has as result 0/0 (not 3/0 as before 10.0.8) Actually, it doesn't: MariaDB [test]> CREATE TEMPORARY TABLE A(`id` mediumint, `value` mediumint, PRIMARY KEY(`id`,`value`)); Query OK, 0 rows affected (0.23 sec) MariaDB [test]> INSERT INTO A VALUES (1,1),(2,2),(3,3); Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0 MariaDB [test]> CREATE TEMPORARY TABLE B(`id` mediumint, PRIMARY KEY(`id`)); Query OK, 0 rows affected (0.17 sec) MariaDB [test]> INSERT INTO B VALUES (1),(2),(3); Query OK, 3 rows affected (0.01 sec) Records: 3 Duplicates: 0 Warnings: 0 MariaDB [test]> SELECT SQL_CALC_FOUND_ROWS 1 FROM A LEFT JOIN B ON A.`id` = B.`id` WHERE B.`id` IS NULL ORDER BY `value` DESC; Empty set (0.02 sec) MariaDB [test]> SELECT FOUND_ROWS() AS `count`; +-------+ | count | +-------+ | 3 | +-------+ 1 row in set (0.00 sec) MariaDB [test]> SELECT COUNT(*) FROM (SELECT 1 FROM A LEFT JOIN B ON A.`id` = B.`id` WHERE B.`id` IS NULL ORDER BY `value` DESC) AS `x`; +----------+ | COUNT(*) | +----------+ | 0 | +----------+ 1 row in set (0.00 sec) MariaDB [test]> select @@version; +-----------------+ | @@version | +-----------------+ | 10.0.10-MariaDB | +-----------------+ 1 row in set (0.00 sec) It's rather interesting that it works for you, maybe you have something in your configuration that prevents it from failing. Answering your other question, Had someone opportunity to check it on mentioned versions? here you go: MariaDB [test]> CREATE TEMPORARY TABLE A(`id` mediumint, `value` mediumint, PRIMARY KEY(`id`,`value`)); Query OK, 0 rows affected (0.19 sec) MariaDB [test]> INSERT INTO A VALUES (1,1),(2,2),(3,3); Query OK, 3 rows affected (0.01 sec) Records: 3 Duplicates: 0 Warnings: 0 MariaDB [test]> CREATE TEMPORARY TABLE B(`id` mediumint, `val` mediumint, PRIMARY KEY(`id`)); Query OK, 0 rows affected (0.15 sec) MariaDB [test]> INSERT INTO B VALUES (1,5),(2,5),(3,10); Query OK, 3 rows affected (0.01 sec) Records: 3 Duplicates: 0 Warnings: 0 MariaDB [test]> CREATE TEMPORARY TABLE C(`id` mediumint, `val` mediumint, PRIMARY KEY(`id`,`val`)); Query OK, 0 rows affected (0.17 sec) MariaDB [test]> INSERT INTO C VALUES (1,5),(2,5),(3,10); Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0 MariaDB [test]> CREATE TEMPORARY TABLE D(`id` mediumint, `val` mediumint); Query OK, 0 rows affected (0.19 sec) MariaDB [test]> INSERT INTO D VALUES (1,5),(2,5),(3,10); Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0 MariaDB [test]> -- *** WRONG count, returns 3 instead of 2 : MariaDB [test]> SELECT SQL_CALC_FOUND_ROWS 1 FROM A -> LEFT JOIN B ON A.`id` = B.`id` -> WHERE B.`val` = 5 -> OR A.`value` = 5 -> ORDER BY `value` DESC -> LIMIT 1; SELECT FOUND_ROWS() AS `count`; +---+ | 1 | +---+ | 1 | +---+ 1 row in set (0.00 sec) +-------+ | count | +-------+ | 2 | +-------+ 1 row in set (0.00 sec) MariaDB [test]> -- *** WRONG count also, returns 3 instead of 2 : MariaDB [test]> SELECT SQL_CALC_FOUND_ROWS 1 FROM A -> LEFT JOIN C ON A.`id` = C.`id` -> WHERE C.`val` = 5 -> OR A.`value` = 5 -> ORDER BY `value` DESC -> LIMIT 1; SELECT FOUND_ROWS() AS `count`; +---+ | 1 | +---+ | 1 | +---+ 1 row in set (0.00 sec) +-------+ | count | +-------+ | 2 | +-------+ 1 row in set (0.00 sec) MariaDB [test]> -- *** CORRECT count, returns 2 : MariaDB [test]> SELECT SQL_CALC_FOUND_ROWS 1 FROM A -> LEFT JOIN D ON A.`id` = D.`id` -> WHERE D.`val` = 5 -> OR A.`value` = 5 -> ORDER BY `value` DESC -> LIMIT 1; SELECT FOUND_ROWS() AS `count`; +---+ | 1 | +---+ | 1 | +---+ 1 row in set (0.00 sec) +-------+ | count | +-------+ | 2 | +-------+ 1 row in set (0.00 sec) MariaDB [test]> select @@version; +----------------+ | @@version | +----------------+ | 10.0.9-MariaDB | +----------------+ 1 row in set (0.00 sec) And yes, even then it's still possible that the original fix was not complete, but it doesn't change anything. The new report should be created anyway.

MariaDB Server

SQL_CALC_FOUND_ROWS yields wrong result

Details

Description

Attachments

Issue Links

Activity

People

Dates

Git Integration