Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

The volumes calculated from the table `trades` and the volumes registered in the table `markets` doesn't match #1031

Open
CrystalBallBe opened this Issue Feb 13, 2019 · 3 comments

Comments

Projects
None yet
4 participants
@CrystalBallBe
Copy link

CrystalBallBe commented Feb 13, 2019

In some markets, the volumes calculated from the table trades and the volumes registered in the table markets doesn't match. Is my understanding wrong?

As mentioned here, (my understanding is) "volume for trade" is represented by the following formula:
numCreatorTokens + numFillerTokens + min(numCreatorShares , numFillerShares) * (maxPrice - minPrice)
And "volume for market" is the sum of "volume for trade" that is related to that market.

If my understanding is correct, the value of volume which is a field of the table markets and the value of volume which is calculated from the table trades should be equal. Though almost all market are equal, some markets are not equal.

For example, marketId = 0xd9cf030f0ff46c911e1f5b41e350c9d56d342e18 is not equal.
At this time(the last processed blockNumber is 7213806), the value of volume which is a field of table markets is 0.1982, and the value of volume which is calculated from the table trades is 0.5.

The following is the SQL used to acquire these data.

  • SQL for retrieving data from table markets:
select 
	marketId
	,volume
	,maxPrice
	,minPrice
from 
	markets
where
	marketId ='0xd9cf030f0ff46c911e1f5b41e350c9d56d342e18'

The result is that:

marketId volume maxPrice minPrice
0xd9cf030f0ff46c911e1f5b41e350c9d56d342e18 0.1982 1 0
  • SQL for retrieving data from table trades:
select 
	marketId
	,numCreatorTokens
	,numCreatorShares
	,numFillerTokens
	,numFillerShares
from 
	trades
where
	marketId ='0xd9cf030f0ff46c911e1f5b41e350c9d56d342e18'

The result is that:

marketId numCreatorTokens numCreatorShares numFillerTokens numFillerShares
0xd9cf030f0ff46c911e1f5b41e350c9d56d342e18 0.032 0 0.048 0
0xd9cf030f0ff46c911e1f5b41e350c9d56d342e18 0.02364 0 0.03546 0
0xd9cf030f0ff46c911e1f5b41e350c9d56d342e18 0.14436 0 0.21654 0

(Note : I checked these transactions in Etherscan, it seems to me that the data in the table trades is correct. So I think 0.5 is correct.)

  • SQL for calculating volumes from table trades:
select 
	tr.marketId
	,sum(tr.numCreatorTokens + tr.numFillerTokens  + min(tr.numCreatorShares , tr.numFillerShares) * (mk.maxPrice - mk.minPrice)) as calculatedVolume
from
	trades as tr
	join markets as mk  on mk.marketId= tr.marketId 
where
	tr.marketId = '0xd9cf030f0ff46c911e1f5b41e350c9d56d342e18'
group by
	tr.marketId

The result is that:

marketId calculatedVolume
0xd9cf030f0ff46c911e1f5b41e350c9d56d342e18 0.5
  • And my augur-node is running by this command:
docker container run -d --restart=on-failure -p 9001:9001 -p 9002:9002 -e ETHEREUM_HTTP=https://eth-mainnet.alchemyapi.io/jsonrpc/7sE1TzCIRIQA3NJPD5wg7YRiVjhxuWAE -e AUGUR_DATABASE_DIR=/app/db --mount "source=augur-node,dst=/app/db" augurproject/augur-node:2019-02-02-0003

Aside to that, I noticed this volume unmatch by checking the volume of the House market (marketId:0xbbbc0a8baa03535e0a680ee2f057162aaaafd570).
This market's volume is that:
16833.98175895 (that is calculated from table trades)
15634.77935895 (that is from the field volume of the table markets)

@pgebheim

This comment has been minimized.

Copy link
Member

pgebheim commented Feb 20, 2019

The market calculation in augur-node keeps a running calculation (instead of just doing it as a sum from the DB every time anything has change), and I suspect somewhere we do something wrong -- perhaps in the rollback logic.

I'm gonna take a look at this tomorrow and see if I can suss it out, @nuevoalex you agree this report looks accurate?

@pgebheim pgebheim self-assigned this Feb 20, 2019

@ryanberckmans

This comment has been minimized.

Copy link
Contributor

ryanberckmans commented Feb 22, 2019

Hi @CrystalBallBe, thanks for your work here.

Below is evidence that the volume calculation in SQL is equivalent to that produced by augur-node and written to markets.volume (except for one edge case market, different than IDs you provided). (The query you provided was a big help, thanks!)

Is your augur-node DB latest version? Ie. in version 4 (ie. named augur-1-4.db) with latest migrations from master, or AN bundled with latest augur app release?

With latest Augur App (v1.10.2) and fully synced DB, was able to show that calculatedVolume is correctly equal to markets.volume:

sqlite3 ~/Library/Application\ Support/augur/augur-1-4.db

SELECT
    markets.marketId,
    calculatedVolume as expectedVolume,
    volume as actualVolume,
    abs(1 - cast(calculatedVolume as real)/cast(volume as real)) as diffPercent
FROM markets JOIN
(
    SELECT 
        tr.marketId,
        sum(tr.numCreatorTokens + tr.numFillerTokens  + min(tr.numCreatorShares , tr.numFillerShares) * (mk.maxPrice - mk.minPrice)) as calculatedVolume
    FROM trades as tr
    JOIN markets as mk on mk.marketId= tr.marketId 
group by
    tr.marketId
) cv on markets.marketId = cv.marketId
WHERE
    cast(expectedVolume as real) != cast(actualVolume as real) AND
    abs(1 - cast(expectedVolume as real)/cast(actualVolume as real)) > 0.0001

--> the result of this query is the empty set[1], showing that calculatedVolume in SQL is equal to markets.volume (within a tolerance of 1 basis point).

[1]: Actually it's not the empty set, there is a single offending market 0x78ade4f8da8498b8185dd67018987f638dc6845c with three trades.

Based on this evidence I think we should consider markets.volume calculation as working and investigate this edge case separately.

@CrystalBallBe

This comment has been minimized.

Copy link
Author

CrystalBallBe commented Feb 24, 2019

@ryanberckmans thank you for your reply!

Is your augur-node DB latest version? Ie. in version 4 (ie. named augur-1-4.db) with latest migrations from master, or AN bundled with latest augur app release?

My augur-node DB version was augur-1-4.db.
I downloaded the docker image of augur-node from here and ran it on my laptop. (I wrote that docker run command in my last post, so please refer it if you need.) I don't have augur-app.

When I posted last time, I was running my augur-node whose docker image tag name is 2019-02-02-0003.
Unfortunately, the problem occurred on my augur-node (a unique constraint violation occurred on table transfers), so I deleted it a few days ago. Therefore, the node at that time is not currently exist on my laptop.

Currently I have two augur-nodes. Both are running on docker.
One docker tag name is release, and the other is 2019-02-19-1841. Both DB versions are augur-1-4.db.

In order to verify that my nodes are fully synced, I called API augur.augurNode.getSyncData.

The result on node release is that:

{ version: '6.5.1',
  augurNodeVersion: '7.5.0',
  net_version: '1',
  netId: '1',
  isSyncFinished: true,
  addresses: 
   { Controller: '0xb3337164e91b9f05c87c7662c7ac684e8e0ff3e7',
     Universe: '0xe991247b78f937d7b69cfc00f1a487a293557677',
     Augur: '0x75228dce4d82566d93068a8d5d49435216551599',
     OrdersFinder: '0x01f2aba090b5fa26a64ea9e5afd32f6aab6ba3df',
     LegacyReputationToken: '0xe94327d07fc17907b4db788e5adf2ed424addff6',
     CancelOrder: '0x3448209268e97652bb67ea12777d4dfba81e3aaf',
     Cash: '0xd5524179cb7ae012f5b642c1d6d700bbaa76b96b',
     ClaimTradingProceeds: '0x4334477348222a986fc88a05410aa6b07507872a',
     CompleteSets: '0xa6bff4f91016c2b4aac140b2d8d7317b36afe5ab',
     CreateOrder: '0x7f27b0598949dbf9e539bbd217f15bf3f5e97999',
     FillOrder: '0x39c230f89981274177e79708c921582bd4bd0303',
     Order: '0x75d789ef6344579fbe75a4d2d9217ec23b005f8f',
     Orders: '0xd7a14019aeeba25e676a1b596bb19b6f37db74d2',
     OrdersFetcher: '0xa97a3e2ac932bc7a9c65393ee2c0f9d2f7b2e692',
     ShareToken: '0x2a21d90745dfed999aa0b6e08d648855c5f14663',
     Trade: '0x24e2b1d415e6e0d04042eaa45dc2a08fc33ca6cd',
     TradingEscapeHatch: '0x10b5589fc6922f5c27061ac79ad9a4af35e15806' },
  highestBlock: 
   { number: 7262054,
     hash: '0xb20ed90f4971fbfde7e0a60c8c56ab3abf104f8c6dbaf915cbdd7b4be3b12b95',
     timestamp: 1551019451 },
  lastProcessedBlock: 
   { number: 7262054,
     hash: '0xb20ed90f4971fbfde7e0a60c8c56ab3abf104f8c6dbaf915cbdd7b4be3b12b95',
     timestamp: 1551019451 } }

The result on node 2019-02-19-1841 is that:

{ version: '6.5.1',
  augurNodeVersion: '7.6.0-0',
  net_version: '1',
  netId: '1',
  isSyncFinished: true,
  addresses: 
   { Controller: '0xb3337164e91b9f05c87c7662c7ac684e8e0ff3e7',
     Universe: '0xe991247b78f937d7b69cfc00f1a487a293557677',
     Augur: '0x75228dce4d82566d93068a8d5d49435216551599',
     OrdersFinder: '0x01f2aba090b5fa26a64ea9e5afd32f6aab6ba3df',
     LegacyReputationToken: '0xe94327d07fc17907b4db788e5adf2ed424addff6',
     CancelOrder: '0x3448209268e97652bb67ea12777d4dfba81e3aaf',
     Cash: '0xd5524179cb7ae012f5b642c1d6d700bbaa76b96b',
     ClaimTradingProceeds: '0x4334477348222a986fc88a05410aa6b07507872a',
     CompleteSets: '0xa6bff4f91016c2b4aac140b2d8d7317b36afe5ab',
     CreateOrder: '0x7f27b0598949dbf9e539bbd217f15bf3f5e97999',
     FillOrder: '0x39c230f89981274177e79708c921582bd4bd0303',
     Order: '0x75d789ef6344579fbe75a4d2d9217ec23b005f8f',
     Orders: '0xd7a14019aeeba25e676a1b596bb19b6f37db74d2',
     OrdersFetcher: '0xa97a3e2ac932bc7a9c65393ee2c0f9d2f7b2e692',
     ShareToken: '0x2a21d90745dfed999aa0b6e08d648855c5f14663',
     Trade: '0x24e2b1d415e6e0d04042eaa45dc2a08fc33ca6cd',
     TradingEscapeHatch: '0x10b5589fc6922f5c27061ac79ad9a4af35e15806' },
  highestBlock: 
   { number: 7262058,
     hash: '0x3c6222c8a9c4483b077310c7238a447e848535dbb9f539a5b878ea55850bf0b9',
     timestamp: 1551019533 },
  lastProcessedBlock: 
   { number: 7262058,
     hash: '0x3c6222c8a9c4483b077310c7238a447e848535dbb9f539a5b878ea55850bf0b9',
     timestamp: 1551019533 } }

And I excuted the SQL you provided on these nodes.

The result on node release is that:
20190224_release

The result on node 2019-02-19-1841 is that:
20190224_2019-02-19-1841

The two results are exactly the same. And there is a difference in some markets other than market 0x78ade4f8da8498b8185dd67018987f638dc6845c.
The following is a list of market IDs which have a difference.

0x1b8c8c7b62d8a6875cbe95ec36e6257e46745115
0x78ade4f8da8498b8185dd67018987f638dc6845c
0x79ee0681a6090ac5169aaf04422daa8c5cccacb3
0x91c426724f5d4c0a918fdfb21e0c893f62cd5b1c
0xb9f345d057bf524f8e69d7956c98b680fdac8b63
0xbbbc0a8baa03535e0a680ee2f057162aaaafd570
0xd9cf030f0ff46c911e1f5b41e350c9d56d342e18

I don't know why multiple markets are detected in my DB...

@pgebheim , If you execute the above SQL in your DB, is there only one row(marketID:0x78ade4f8da8498b8185dd67018987f638dc6845c) returned?

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.