Archive for October, 2015

SQL wont return predefined values if table is empty

13 Oct

I have been working on a billing system in python-twisted for a while now. This system used scratch/serial to clear payments.
I had this callback that returns a Decimal.Decimal which is the value of the scratch card.

A little mod was required to allow direct API calls to clear payments, since the scratch/serial approach is still very much active, i figured it will be best to just refactor it all to work such that, if you pass in a scratch/serial, it checks on the database table, if a decimal e.g 1000.0  is passed in, then we have SELECT 1000.0 from scratch_pin_table;

We have 2 twisted servers running, a live and a test server.

everything works fine on the test server but failed on the live server.

Guess why, The test server has some entry in the scratch_pin_table while the Live has nothing because it has been truncated and scratch/pin has never been generated.

The result is

SELECT 1000.0 from scratch_lin_table returns 1000.0 as expected on test server but returns nothing on live.

Am i the only one or we are many who expects that selecting a predefined value in a query will return it even if table it empty?

it turns out that we are wrong.

you really cannot get anything from an empty table if you try that approach.