Bug #95423 Prepared statements (server) return part results, cliient statements work
Submitted: 20 May 2019 9:58 Modified: 6 Jul 8:26
Reporter: Graham Cotgreave Email Updates:
Status: Closed Impact on me:
None 
Category:Connector / ODBC Severity:S3 (Non-critical)
Version:8.0.16 OS:Microsoft Windows (MYODBC8A.DLL)
Assigned to: CPU Architecture:x86
Tags: connect, ODBC, prepared statements

[20 May 2019 9:58] Graham Cotgreave
Description:
We have noticed the bug fix for 
https://bugs.mysql.com/bug.php?id=93895
appears to have fixed the primary issue.

However, in the case where we have multiple rows in the table that match the query, the first match is not returned when running with server prepared statements, but is returned if using client side prepared statements.

The table has a mix of standard and a BLOB column.

How to repeat:
CREATE TABLE "RTXT" (
  "ID" varchar(60) COLLATE utf8_bin NOT NULL,
  "VERSION" char(2) COLLATE utf8_bin NOT NULL,
  "DRAFT" char(1) COLLATE utf8_bin NOT NULL,
  "FRM_NAME" varchar(40) COLLATE utf8_bin NOT NULL,
  "FRM_VER" int(11) NOT NULL,
  "FRM_SEQ" int(11) NOT NULL,
  "FRM_DTYPE" smallint(6) DEFAULT NULL,
  "FRM_STORAGE" smallint(6) DEFAULT NULL,
  "FRM_COMPRESS" smallint(6) DEFAULT NULL,
  "FRM_DLEN" smallint(6) DEFAULT NULL,
  "FRM_DATA" blob,
  PRIMARY KEY ("ID","VERSION","DRAFT","FRM_NAME","FRM_VER","FRM_SEQ")
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

See https://bugs.mysql.com/bug.php?id=93895 for similar binds / connects / prepares etc.

Populate the data in the columns with

ID,VERSION,DRAFT,FRM_NAME,FRM_VER,FRM_SEQ,FRM_DTYPE,FRM_STORAGE,FRM_COMPRESS,FRM_DLEN,FRM_DATA
"Req 1",,A,ACCEPTANCE,0,0,0,0,0,3,...
"Req 1",,A,COMPLIANCY,0,0,0,0,0,3,...
"Req 1",,A,NOTES,0,0,0,0,0,3,...
"Req 1",,A,SPLIT1,0,0,0,0,0,3,...
"Req 1",,A,SPLIT2,0,0,0,0,0,3,...
"Req 1",,A,TEXT,0,0,0,0,0,3,...
"Req 1.1",,A,ACCEPTANCE,0,0,0,0,0,3,...
"Req 1.1",,A,COMPLIANCY,0,0,0,0,0,3,...
"Req 1.1",,A,NOTES,0,0,0,0,0,3,...
"Req 1.1",,A,SPLIT1,0,0,0,0,0,3,...
"Req 1.1",,A,SPLIT2,0,0,0,0,0,3,...
"Req 1.1",,A,TEXT,0,0,0,0,0,3,...

Where the blob is very simple data is 3 ASCII characters aaa, bbb, ccc and so on

Run a query 

select  * from "RTXT" where  ("ID" = ? and "VERSION" = ? and   "DRAFT" = ? and "FRM_NAME" = ? and   "FRM_VER" = ? and "FRM_SEQ" = ?) or  ("ID"       > ? or ("ID" = ? and  ("VERSION"  > ? or ("VERSION" = ? and  ("DRAFT"    > ? or ("DRAFT" = ? and  ("FRM_NAME" > ? or ("FRM_NAME" = ? and  ("FRM_VER"  > ? or ("FRM_VER" = ? and  ("FRM_SEQ"  > ?))))))))))) order by "ID", "VERSION", "DRAFT",  "FRM_NAME", "FRM_VER", "FRM_SEQ"

with Server Prepared statement and the results omit the first result (in this case the one with a name of ACCEPTANCE)

Change the driver to 'prepare statements on client' and receive all the results.

Wire shark and ODBC driver tracing available
[20 May 2019 9:59] Graham Cotgreave
ODBC trace with server prepared statements

Attachment: trace_server_prep.log (application/octet-stream, text), 101.27 KiB.

[20 May 2019 9:59] Graham Cotgreave
ODBC trace with client prepared statements

Attachment: trace_client_prep.log (application/octet-stream, text), 117.66 KiB.

[20 May 2019 10:00] Graham Cotgreave
Wireshark capture Server Prepared statements

Attachment: mySQL_newdriver_server_prep.pcapng (application/octet-stream, text), 14.21 KiB.

[20 May 2019 10:01] Graham Cotgreave
Wireshark capture Client Prepared statements

Attachment: mySQL_newdriver_client_side.pcapng (application/octet-stream, text), 13.48 KiB.

[6 Jul 2019 9:23] Jernej Pecjak
Yes there are problems with prepared statements since 5.3.11 (and all the 8.0.x). Seems it is still not fixed, though. Developers, please fix this problem, my page break on any prepared statements...
[6 Jul 2019 9:26] Jernej Pecjak
Seems like copy of my bug https://bugs.mysql.com/bug.php?id=92774
[6 Nov 2019 13:29] MySQL Verification Team
Please check version 8.0.18.
[17 Nov 2019 10:51] Jernej Pecjak
8.0.18 same problem.
[18 Nov 2019 8:32] Graham Cotgreave
Not fixed here either
[20 May 8:46] Bogdan Degtyariov
Posted by developer:
 
Verified with 8.0.20
[3 Jun 8:53] Bogdan Degtyariov
Posted by developer:
 
The patch is pushed into the source repository.
[6 Jul 8:26] Bogdan Degtyariov
(Status updated for 3 jun 2020)
The bug is fixed in the code for the version 8.0.22.
[2 Sep 21:41] Philip Olson
Posted by developer:
 
Fixed as of the upcoming MySQL Connector/ODBC 8.0.22 release, and here's the proposed changelog entry from the documentation team:

Under some circumstances when using server-side prepared statements, the
first row of a multi-row match was not returned with the result; while it
was returned when using client-side prepared statements instead.

Thank you for the bug report.