Bug #83098 Query With Function Call Takes Longer With Each Run

• Submitted: 22 Sep 2016 13:52
• Modified: 24 May 2017 12:29
• Reporter: Issuu Operations
• Status: Closed
• Category: MySQL Server: Stored Routines
• Severity: S3 (Non-critical)
• Version: 5.6.25,5.7.8, 5.7.15
• OS: Other
• CPU Architecture: Any
• Tags: regression

[22 Sep 2016 13:52] Issuu Operations

Description:
This bug seems to be very similar to closed bug #59926

We have tested this using various official mysql docker images and found it was introduced in 5.6.25 (5.6.24 does not have the problem) and 5.7.8 (5.7.7 is fine).

The latest 5.7.15 also has the problem.

How to repeat:
CREATE DATABASE perftest;

CREATE TABLE perftest.t (n INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY);

-- Repeat 1000 times:
INSERT INTO perftest.t () VALUES ();

CREATE OR REPLACE VIEW perftest.v AS SELECT n FROM perftest.t;

CREATE FUNCTION perftest.f (n_in INTEGER) RETURNS INTEGER
 RETURN (SELECT n FROM perftest.v WHERE n = n_in);

-- Each select is gradually slower
SELECT perftest.f(n) FROM perftest.t;
SELECT perftest.f(n) FROM perftest.t;

...
SELECT perftest.f(n) FROM perftest.t;

[23 Sep 2016 6:49] MySQL Verification Team

Hello  Issuu,

Thank you for the report and test case.
Observed this with 5.7.15 build.

Thanks,
Umesh

[23 Sep 2016 6:50] MySQL Verification Team

test results

Attachment: 83098_5.7.results (application/octet-stream, text), 7.18 KiB.

[24 May 2017 12:29] Erlend Dahl

Fixed in 5.6.36, 5.7.18, 8.0.1 under the heading of

Bug#25053286 USE VIEW WITH CONDITION IN PROCEDURE CAUSES INCORRECT BEHAVIOR