DB2 - Problem description
Problem IT39266 | Status: Closed |
WIRE LISTENER DO NOT HONOR THE LIMIT OPTION USED WITH FIND COMMAND | |
product: | |
DB2 CONNECT / DB2CONNCT / B50 - DB2 | |
Problem description: | |
Wire Listener do not honor the limit option used with find command. When a mongo client connects to IBM NoSQL JSON Wire Listener and executes a find command with "skip" and "limit" options, the find command do not process the skip and limit options and hence only one result get returned. For the command {find:"two", filter:{"q2":true}, "sort":{"_id":1}, skip: 2, limit: 3.0}, if collection two has seven satisfying rows for the passed filter condition, two docs should get skipped and three json docs should get returned, but it returns only one json doc as below: > db.runCommand({"find":"users","filter":{},"sort":{"_id":1},"skip ": 2.0, "limit":2}) { "cursor" : {"firstBatch" : [ {"_id" : 1,"name" : "dave123","q1" : true,"q2" : true} ], "id" : NumberLong(0), "ns" : "test.users" },"ok" : 1} > The reason is the formation of wrong SQL: SELECT ID, DATA FROM "TEST"."users" ORDER BY ID ASC FETCH FIRST 1 ROWS ONLY When similar command get executed from mongo shell against mongodb server, it return correct data as below: > db.runCommand({"find":"users","filter":{},"sort":{"_id":1},"skip ": 2.0, "limit":2}) { "cursor" : {"firstBatch" : [ {"_id" : 3,"name" : "ahn","q1" : true,"q2" : true} , {"_id" : 4,"name" : "li","q1" : true, "q2" : false}], "id" : NumberLong(0), "ns" : "test.users" },"ok" : 1} > | |
Problem Summary: | |
**************************************************************** * USERS AFFECTED: * * MongoDb shell or any other mongo driver connect to IBM JSON * * Wire listener. * **************************************************************** * PROBLEM DESCRIPTION: * * When a mongo client connects to IBM NoSQL JSON Wire Listener * * and executes a find command with "skip" and "limit" options, * * the * * find command do not process the skip and limit options and * * hence only one result get returned. * * * * For the command {find:"two", * * filter:{"q2":true},"sort":{"_id":1}, skip: 2, limit: 3.0}, * * if collection two has seven satisfying rows for the passed * * filter condition, two docs should get skipped and three json * * docs should get returned, but it returns only one json doc * * as below: * * * * db.runCommand({"find":"users","filter":{},"sort":{"_id":1}," * * skip": 2.0, "limit":2}) * * > {"cursor" : {"firstBatch" : [{"_id" : 1,"name" : * * "dave123","q1" : true,"q2" : true}],"id" : * * NumberLong(0),"ns" : "test.users"},"ok" : 1} * * > * * * * The reason is the formation of wrong SQL: SELECT ID, DATA * * FROM "TEST"."users" ORDER BY ID ASC FETCH FIRST 1 ROWS ONLY * * * * When similar command get executed from mongo shell against * * mongodb server, it return correct data as below: * * * * > * * db.runCommand({"find":"users","filter":{},"sort":{"_id":1}," * * skip": 2.0, "limit":2}) * * { "cursor" : {"firstBatch" : [{"_id" : 3,"name" : "ahn","q1" * * : true,"q2" : true}, {"_id" : 4,"name" :"li","q1" : true, * * "q2" : false}], * * "id" : NumberLong(0),"ns" : "test.users"},"ok" : 1} * * > * **************************************************************** * RECOMMENDATION: * * Upgrade to Db2 version 11.5.7.1 or later releases. * **************************************************************** | |
Local Fix: | |
Solution | |
Workaround | |
**************************************************************** * USERS AFFECTED: * * MongoDb shell or any other mongo driver connect to IBM JSON * * Wire listener. * **************************************************************** * PROBLEM DESCRIPTION: * * When a mongo client connects to IBM NoSQL JSON Wire Listener * * and executes a find command with "skip" and "limit" options, * * the * * find command do not process the skip and limit options and * * hence only one result get returned. * * * * For the command {find:"two", * * filter:{"q2":true},"sort":{"_id":1}, skip: 2, limit: 3.0}, * * if collection two has seven satisfying rows for the passed * * filter condition, two docs should get skipped and three json * * docs should get returned, but it returns only one json doc * * as below: * * * * db.runCommand({"find":"users","filter":{},"sort":{"_id":1}," * * skip": 2.0, "limit":2}) * * > {"cursor" : {"firstBatch" : [{"_id" : 1,"name" : * * "dave123","q1" : true,"q2" : true}],"id" : * * NumberLong(0),"ns" : "test.users"},"ok" : 1} * * > * * * * The reason is the formation of wrong SQL: SELECT ID, DATA * * FROM "TEST"."users" ORDER BY ID ASC FETCH FIRST 1 ROWS ONLY * * * * When similar command get executed from mongo shell against * * mongodb server, it return correct data as below: * * * * > * * db.runCommand({"find":"users","filter":{},"sort":{"_id":1}," * * skip": 2.0, "limit":2}) * * { "cursor" : {"firstBatch" : [{"_id" : 3,"name" : "ahn","q1" * * : true,"q2" : true}, {"_id" : 4,"name" :"li","q1" : true, * * "q2" : false}], * * "id" : NumberLong(0),"ns" : "test.users"},"ok" : 1} * * > * **************************************************************** * RECOMMENDATION: * * Upgrade to Db2 version 11.5.7.1 or later releases. * **************************************************************** | |
Comment | |
db2_v11571 contains fix of this issue. After the fix, find() command with skip and limit options return correct result. | |
Timestamps | |
Date - problem reported : Date - problem closed : Date - last modified : | 30.11.2021 15.12.2021 24.10.2022 |
Problem solved at the following versions (IBM BugInfos) | |
Problem solved according to the fixlist(s) of the following version(s) |