SQLite Forum

Incorrect Result From SQLite Query
Login

Incorrect Result From SQLite Query

(1) By Bsy (Bshuyi) on 2021-07-02 03:50:18 [link] [source]

The result query from the SQLite is incorrect.

Some data are not being calculate by the formula but the data are included in the result table.

There are 10 row data, but only 9 row data is calculate, the another 1 row data is not calculate.

select (amount * rate) as cost

from report_A

Example:

Raw data: | Amount | Rate row 1 | 100 | 0.12 row 2 | 50 | 0.15 row 3 | 20 | 0.005 row 4 | 60 | 0.13 row 5 | 10 | 0.15 row 6 | 67 | 0.005 row 7 | 50 | 0.001 row 8 | 87 | 0.006 row 9 | 12 | 0.005 row 10 | 43 | 0.12

Result:

Amount Rate Cost
row 1 100 0.12 12
row 2 50 0.15 7.5
row 3 20 0.005 0.1
row 4 60 0.13 7.8
row 5 10 0.15 1.5
row 6 67 0.005 0.335
row 7 50 0.001 0.05
row 8 87 0.006 0.522
row 9 12 0.005 0.06
row 10 43 0.12 0

Expected Result:

Amount Rate Cost
row 1 100 0.12 12
row 2 50 0.15 7.5
row 3 20 0.005 0.1
row 4 60 0.13 7.8
row 5 10 0.15 1.5
row 6 67 0.005 0.335
row 7 50 0.001 0.05
row 8 87 0.006 0.522
row 9 12 0.005 0.06
row 10 43 0.12 5.16

(2) By anonymous on 2021-07-02 04:22:39 in reply to 1 [link] [source]

Which program do you use and which sqlite Version? Is ist always the last record in answer table?

(3) By Bsy (Bshuyi) on 2021-07-02 05:35:24 in reply to 2 [source]

SQLite Studio 3.2.1

Not always the last record in answer table.

It is in the between of the set of data.

(5) By Larry Brasfield (larrybr) on 2021-07-02 05:42:33 in reply to 3 [link] [source]

You should understand that "SQLite Studio" is not a product of the SQLite development team, and this forum is not the right place to deal with that product's misbehavior.

If you can reduce your problem to something reproducible with the CLI shell available at the SQLite Download page, that will be on-topic here. I submit that your effort to reduce the problem that way is likely to reveal what is going wrong with your use of SQLite Studio. And if you end up showing that the problem is unique to SQLite Studio, you should bring it to the attention of that product's developers.

(6) By Stephan Beal (stephan) on 2021-07-02 05:45:21 in reply to 3 [link] [source]

SQLite Studio 3.2.1

SQLite Studio is a 3rd-party project neither maintained nor supported by this project. If you can reproduce your query result with the sqlite3 CLI application (as Larry attempts in his response), the libsqlite3 C API, or the System.Data dot-net bindings for that API, this is the correct forum. If you can't reproduce it in one those then the SQLite Studio team is your best bet.

(4) By Larry Brasfield (larrybr) on 2021-07-02 05:35:39 in reply to 1 [link] [source]

I executed this DDL to begin reproducing your incorrect result: CREATE TABLE report_A (row INTEGER, amount INTEGER, rate REAL);

Then I used your so-called raw data to create, then execute this DML: INSERT INTO report_A VALUES(1,100,0.120); INSERT INTO report_A VALUES(2,50,0.150); INSERT INTO report_A VALUES(3,20,0.005); INSERT INTO report_A VALUES(4,60,0.130); INSERT INTO report_A VALUES(5,10,0.150); INSERT INTO report_A VALUES(6,67,0.005); INSERT INTO report_A VALUES(7,50,0.001); INSERT INTO report_A VALUES(8,87,0.006); INSERT INTO report_A VALUES(9,12,0.005); INSERT INTO report_A VALUES(10,43,0.120);

Then, upon executing this query, SELECT row, (amount*rate) as cost FROM report_A; , I see this result set: 1|12.0 2|7.5 3|0.1 4|7.8 5|1.5 6|0.335 7|0.05 8|0.522 9|0.06 10|5.16

To me, it looks like the correct result. I get the same result with all versions of the SQLite CLI that I have tried. So you must be doing something different. What would that be? I suspect that difference is important.

(7) By TripeHound on 2021-07-02 06:09:17 in reply to 1 [link] [source]

It may be worth running the query:

select typeof(amount), typeof(rate) from ...

to see if any of the values are, for example, being stored as text.

(8) By Ryan Smith (cuz) on 2021-07-02 10:30:14 in reply to 7 [link] [source]

It shouldn't matter - See example below where I make text columns and sometimes populate text and sometimes numbers, SQLite is ambivalent:

  -- SQLite version 3.35.4  [ Release: 2021-04-02 ]  on  SQLitespeed version 2.1.3.11.
================================================================================================

CREATE TABLE report_A (
   rows INTEGER PRIMARY KEY,
   amount TEXT,
   rate TEXT
 );


INSERT INTO report_A VALUES
  ( 1,100 , 0.120 )
 ,( 2, 50 , 0.150 )
 ,( 3,'20', 0.005 )
 ,( 4, 60 ,'0.130')
 ,( 5, 10 ,'0.150')
 ,( 6,'67', 0.005 )
 ,( 7, 50 , 0.001 )
 ,( 8,'87','0.006')
 ,( 9,'12','0.005')
 ,(10, 43 , 0.120 )
 ;


SELECT rows, amount, (amount * rate) AS cost
 FROM report_A

  --     rows    |amount|     cost
  -- ------------|------|---------
  --       1     |  100 |     12.0
  --       2     |  50  |      7.5
  --       3     |  20  |      0.1
  --       4     |  60  |      7.8
  --       5     |  10  |      1.5
  --       6     |  67  |    0.335
  --       7     |  50  |     0.05
  --       8     |  87  |    0.522
  --       9     |  12  |     0.06
  --      10     |  43  |     5.16


Still a good idea to run the typeof() query though, just in case we learn something else. My bet is however the program he uses is getting it wrong - I think it's the same program recently mentioned in another thread where it didn't deal correctly with Windows UAC - perhaps it's just a tad old.