Difference between mssql result and actual query result

[Rohithzr]

In reference to issue knex/knex#2557

Environment

Mssql version: 4.1.0
Database + version: MSSQL 2016
OS: Ubuntu

As per the query below, the reulting values in the buy and sell column should be 490560 and 515620 respectively. But the result I get is buy: 490559.99999999994, sell: 515619.99999999994.

The rate column is of type Deimal 38,19 hence a value larger than 32 bit is possible, but this particular value is well within 32 bits and should work simply.

SELECT rateString, 
	currency, 
	market, 
	(
	        SELECT ISNULL(SUM(volume * -1) ,0) 
	        FROM c.trades p 
	        WHERE p.currency = ct.currency 
	        AND p.transactionType LIKE 'sell' 
	        AND p.status LIKE 'executed' 
	        AND p.isExecuted = 1
	        AND p.updated_at > DATEADD(d , -1, GETDATE())
        ) AS volume,
	(
	        SELECT max(rate)
	        FROM c.trades rb 
	        WHERE rb.currency = ct.currency 
	        AND rb.transactionType LIKE 'buy' 
	        AND rb.status LIKE 'placed' 
	        AND rb.isExecuted = 0
			 AND rb.isCancelled = 0
        ) AS buy,
	(
	        SELECT min(rate)
	        FROM c.trades rb 
	        WHERE rb.currency = ct.currency 
	        AND rb.transactionType LIKE 'sell' 
	        AND rb.status LIKE 'placed' 
	        AND rb.isExecuted = 0
		AND rb.isCancelled = 0
        ) AS sell
FROM c.trades ct
WHERE ct.id IN (
            SELECT top 2 id
            FROM c.trades tt
            WHERE tt.currency = ct.currency
            AND tt.isExecuted = 1 
            AND tt.status LIKE 'executed' 
            AND tt.transactionType LIKE 'sell'
            ORDER BY tt.created_at DESC
)
 AND ct.market in ('inr/btc')  ORDER BY currency, created_at DESC

Create Table Statement

CREATE TABLE [c].[trades]
(
  [userId] [int]  NULL,
  [txnId] [nvarchar](100)  NULL,
  [transactionType] [nvarchar](10)  NOT NULL,
  [marketCategory] [nvarchar](10)  NOT NULL,
  [status] [nvarchar](10)  NOT NULL,
  [rate] [decimal](38, 19)  NOT NULL, // <-- the column being used
  [rateString] [nvarchar](38)  NULL,
  [volume] [decimal](38, 19)  NOT NULL,
  [volumeString] [nvarchar](38)  NULL,
  [isCancelled] [bit]  NULL DEFAULT ('0'),
  [isExecuted] [bit]  NULL DEFAULT ('0'),
  [referenceId] [nvarchar](100)  NULL,
  [parentTxnId] [nvarchar](100)  NULL,
  [currency] [nvarchar](5)  NULL,
  [market] [nvarchar](20)  NULL,
  [sessionId] [nvarchar](100)  NULL,
  [created_at] [datetime]  NULL,
  [updated_at] [datetime]  NULL,
  [accountId] [nvarchar](20)  NULL
)
ALTER TABLE [c].[trades] ADD CONSTRAINT PK__trades__3213E83F35AAF493 PRIMARY KEY  ([id])

Data

INSERT INTO c.trades (id, userId, txnId, transactionType, marketCategory, status, rate, rateString, volume, volumeString, isCancelled, isExecuted, referenceId, parentTxnId, currency, market, sessionId, created_at, updated_at, accountId) VALUES (14826, 126, 'cxihub-f116db90-c04a-4a64-b83d-3abdab9377d9', 'sell', 'exchange', 'executed', 492560, '492560', -0.002, '-0.0020000000000000000', 0, 1, NULL, 'cxihub-1d616b85-5c1c-4af6-815c-d78a61ec1618', 'btc', 'inr/btc', NULL, '2018-03-30 07:04:41:000', '2018-03-30 07:04:41:000', NULL);

following is a small snippet of code I used to test this

var sql = require("mssql");

var connection = {
    server: 'rdshost',
    port: 1433,
    user: 'dba_user',
    password: 'passssss',
    database: 'cdb'
};

let query = `
	SELECT max(rate) AS rate
	FROM c.trades rb 
	WHERE rb.currency LIKE 'btc' 
		AND rb.transactionType LIKE 'buy' 
		AND rb.status LIKE 'placed' 
		AND rb.isExecuted = 0
		AND rb.isCancelled = 0
`;

sql.connect(connection, function (err) {
    
    if (err) console.log(err);

    // create Request object
    var request = new sql.Request();
       
    // query to the database and get the records
    request.query(query, function (err, recordset) {
        
        if (err) console.log(err)

        // send records as a response
        console.log(recordset)
        
    });
});

[willmorgan]

Sorry you're having trouble with this. It seems to be an issue relating to Tedious, the driver that this library ultimately relies upon.

Here is an issue that's in progress that should address the problem: tediousjs/tedious#678

[Rohithzr]

@willmorgan i figured, at this moment I am converting the values to string. not a good experience.

[dhensby]

@Rohithzr I'm trying to look into this a little, but your "Create Table Statement" doesn't work, in particular the ALTER part:

sql> ALTER TABLE [c].[trades] ADD CONSTRAINT PK__trades__3213E83F35AAF493 PRIMARY KEY  ([id])
[2018-11-22 12:18:46] [S0001][1911] Column name 'id' does not exist in the target table or view.

[dhensby]

OK, I've made some adjustments and managed to get it executing locally.

The query you give doesn't match the inserted row you provided so I had to modify that too.

When run against SQL Server directly I get output 492560.0000000000000000000 as the value for rate.

When run through mssql library I get:

{ recordsets: [ [ [Object] ] ],
  recordset: [ { rate: 492560 } ],
  output: {},
  rowsAffected: [ 1 ] }

Which means I cannot replicate this problem.

Please can you provide a working set up that replicates the error? I tested against mssql 4.2.2, perhaps you need to upgrade the library?

[Rohithzr]

ill try and and see if the issue still replicates, ill try with current version and if available the upgraded version.
ill get onnit soon.

[Rohithzr]

@dhensby well, i tried replicating this issue and I failed even with the then used [email protected] so probably was something very specific that caused the issue for me. I am closing this issue for now and hope it doesn't pop up, if it does i'll reopen the issue.

Thanks to you all for looking into it.

[dhensby]

No problem, thanks for taking another look :)