MySQL Bugs: #95423: Prepared statements (server) return part results, cliient statements work

Bug #95423	Prepared statements (server) return part results, cliient statements work
Submitted:	20 May 2019 9:58	Modified:	6 Jul 2020 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:	Windows (MYODBC8A.DLL)
Assigned to:		CPU Architecture:	x86
Tags:	connect, ODBC, prepared statements

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

ODBC trace with server prepared statements

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

ODBC trace with client prepared statements

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

Wireshark capture Server Prepared statements

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

Wireshark capture Client Prepared statements

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

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...

Seems like copy of my bug https://bugs.mysql.com/bug.php?id=92774

Please check version 8.0.18.

8.0.18 same problem.

Not fixed here either

Posted by developer:
 
Verified with 8.0.20

Posted by developer:
 
The patch is pushed into the source repository.

(Status updated for 3 jun 2020)
The bug is fixed in the code for the version 8.0.22.

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.