Bug #95423 Prepared statements (server) return part results, cliient statements work
Submitted: 20 May 2019 9:58 Modified: 18 Nov 2019 8:32
Reporter: Graham Cotgreave Email Updates:
Status: Open Impact on me:
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
We have noticed the bug fix for 
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:
  "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_DLEN" smallint(6) DEFAULT NULL,
  "FRM_DATA" blob,

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

Populate the data in the columns with

"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] Miguel Solorzano
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