Bug #95423 Prepared statements (server) return part results, cliient statements work
Submitted: 20 May 9:58
Reporter: Graham Cotgreave Email Updates:
Status: Open 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 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 9:59] Graham Cotgreave
ODBC trace with server prepared statements

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

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

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

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

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

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

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