SQLite Forum

Timeline
Login

12 forum posts by user alesXala

2021-01-17
18:24 Edit: UNION of selects returning wrong result (SOLVED) (artifact: 41251a1c1a user: alesXala)

OK, thanks for the fast answer! my confusion "UNION ALL" fixes the "issue"

-------- Original post

My understanding is that a union of several selects should return as many records as all them together.

As a trivial example if select1 and select2 return each one record select1 union select2 should return two records.

but in following example using MAX only one record is returned instead of two

   CREATE TABLE A (id1 , id2 );
   CREATE TABLE B (id1 , id2 );
   INSERT INTO A VALUES(100,200);
   INSERT INTO B VALUES(100,200);

   SELECT MAX(id1) AS na FROM A    
      UNION    
   SELECT MAX(id1) AS na FROM B    
   ;

when different column are requested (id1 and id2) or the maximum values of the same column turn to be different then the result of the union is correct (two records)

this is summarized in the example below

     CREATE TABLE A (id1 , id2 );
     CREATE TABLE B (id1 , id2 );
     INSERT INTO A VALUES(100,200);
     INSERT INTO B VALUES(100,200);
     SELECT "** FIRST union";
        SELECT MAX(id1) FROM A
           UNION
        SELECT MAX(id1) FROM B
     ;
     SELECT "** SECOND union";
        SELECT MAX(id1) FROM A
           UNION
        SELECT MAX(id2) FROM B
     ;
     INSERT INTO B VALUES(300,400);
     SELECT "** THIRD union";
        SELECT MAX(id1) FROM A
           UNION
        SELECT MAX(id1) FROM B
     ;

being the output

    ** FIRST union
    100
    ** SECOND union
    100
    200
    ** THIRD union
    100
    300

maybe I am missing something, but I think that the result of the first union is wrong.

This behavior is present in old sqlite versions (3.2.1) until now 3.34

17:47 Post: UNION of selects returning wrong result (SOLVED) (artifact: 0ad7cc9763 user: alesXala)

My understanding is that a union of several selects should return as many records as all them together.

As a trivial example if select1 and select2 return each one record select1 union select2 should return two records.

but in following example using MAX only one record is returned instead of two

   CREATE TABLE A (id1 , id2 );
   CREATE TABLE B (id1 , id2 );
   INSERT INTO A VALUES(100,200);
   INSERT INTO B VALUES(100,200);

   SELECT MAX(id1) AS na FROM A    
      UNION    
   SELECT MAX(id1) AS na FROM B    
   ;

when different column are requested (id1 and id2) or the maximum values of the same column turn to be different then the result of the union is correct (two records)

this is summarized in the example below

     CREATE TABLE A (id1 , id2 );
     CREATE TABLE B (id1 , id2 );
     INSERT INTO A VALUES(100,200);
     INSERT INTO B VALUES(100,200);
     SELECT "** FIRST union";
        SELECT MAX(id1) FROM A
           UNION
        SELECT MAX(id1) FROM B
     ;
     SELECT "** SECOND union";
        SELECT MAX(id1) FROM A
           UNION
        SELECT MAX(id2) FROM B
     ;
     INSERT INTO B VALUES(300,400);
     SELECT "** THIRD union";
        SELECT MAX(id1) FROM A
           UNION
        SELECT MAX(id1) FROM B
     ;

being the output

    ** FIRST union
    100
    ** SECOND union
    100
    200
    ** THIRD union
    100
    300

maybe I am missing something, but I think that the result of the first union is wrong.

This behavior is present in old sqlite versions (3.2.1) until now 3.34

2020-05-25
09:10 Reply: ORDER BY not working for a specific DB/table (artifact: 7c49fb1831 user: alesXala)

I see, no problem, have fun ;)

2020-05-22
08:14 Reply: ORDER BY not working for a specific DB/table (artifact: 50f9317b58 user: alesXala)

Hi Keith,

please note that actually the cause of the issue was early identified in (8) and the solution of using round also explained in (9). So at that point, I think it is obvious that I knew about floating point arithmetic and the way to fix it.

discussing further about the goodness or the evilness of floating point arithmethic (by the way if not invented at least used in the first computer Z3 by Konrad Zuse in 1940!, pretty old stuff) is simply floating-pointless ;)

01:34 Reply: ORDER BY not working for a specific DB/table (artifact: 5dcc326857 user: alesXala)

I don't think you can round the database, at least using sqlite command line

example: create a table with two numbers

  > sqlite3 myroundingtable.db

  SQLite version 3.29.0 2019-07-10 17:32:03
  Enter ".help" for usage hints.
  sqlite> create table mypar (one, second);

insert two rounded numbers

  sqlite> insert into mypar VALUES (round("30.1233198123987303101203",1), round("30.817232123312"));

query them, ok seems to be rounded

  sqlite> select * from mypar;
  30.1|31.0
  sqlite> .q

but, then dumping

  > sqlite3 myroundingtable.db ".dump"

  PRAGMA foreign_keys=OFF;
  BEGIN TRANSACTION;
  CREATE TABLE mypar (one, second);
  INSERT INTO mypar VALUES(30.10000000000000142,30.999999999999999999);
  COMMIT;

So, we asked sqlite to round and store two numbers, it says yes I do but it doesn't.

I am not asking for any fix, only pointing that it is an issue

2020-05-21
15:17 Reply: ORDER BY not working for a specific DB/table (artifact: 37f02b66ab user: alesXala)

the first to return 4.199999 in command line is not very beautiful (don't like it) and applying some rounding operation could affect the performance of the query in big data sets. No easy solution. On the other hand, the work around writing ROUND(xx,1) in the query filter has the same or bigger performance cost as if it were implemented in sqlite.

15:05 Reply: ORDER BY not working for a specific DB/table (artifact: 679cb61253 user: alesXala)

Maybe it can be described as a sqlite bug.

I only use sqlite3 command line, I don't know how it would be the real value using library or api calls. But in command line returning 4.2 and using 4.1999999 in the filter condition is not consistent or could be improved.

either returning in command line

 408|41.1999999999|129.727571723508
 840|41.2000000001|-73.970001
 840|41.2000000012|-73.964172

or using for filtering the same rounding as command line does (4.2)

 840|41.2|-73.970001
 840|41.2|-73.964172
 408|41.2|129.727571723508
14:36 Reply: ORDER BY not working for a specific DB/table (artifact: 4e33dcb561 user: alesXala)

Hi, I've seen you question now.

I've tested from 3.8.0.1 (2013) and 3.29.0 and 3.31.1

actually solved, see my explanation

       INSERT INTO latiSaltxi VALUES(408,41.199999999999995735,129.72757172350807764);
       INSERT INTO latiSaltxi VALUES(408,41.200000000000002843,129.72079500000000962);
       INSERT INTO latiSaltxi VALUES(840,41.200000000000002843,-73.970000999999996338);
       INSERT INTO latiSaltxi VALUES(840,41.200000000000002843,-73.964172000000004913);

I don't know if it can be described as correct behavior but its really difficult to find.

  ORDER BY ROUND(lati10, 1), longi

fixes the problem but I don't think is the way to go (as in C or C++ casting absolutely all variables). Anyway from now on I will do it, specially when combining reals in ORDER BY

14:23 Reply: ORDER BY not working for a specific DB/table (artifact: 74c1308fd5 user: alesXala)

Ok, I found the issue from the values in the dumped database

  BEGIN TRANSACTION;
  CREATE TABLE latiSaltxi (westCCod, lati10, longi);
  INSERT INTO latiSaltxi VALUES(408,41.199999999999995735,129.72757172350807764);
  INSERT INTO latiSaltxi VALUES(408,41.200000000000002843,129.72079500000000962);
  INSERT INTO latiSaltxi VALUES(840,41.200000000000002843,-73.970000999999996338);
  INSERT INTO latiSaltxi VALUES(840,41.200000000000002843,-73.964172000000004913);
  COMMIT;

  SELECT * FROM latisaltxi WHERE lati10 >= 41 ORDER BY lati10, longi;

  408|41.2|129.727571723508
  840|41.2|-73.970001
  840|41.2|-73.964172
  408|41.2|129.720795

  SELECT * FROM latisaltxi WHERE lati10 >= 41.2 ORDER BY lati10, longi;      

  840|41.2|-73.970001
  840|41.2|-73.964172
  408|41.2|129.720795

Actually it was not that the result was correctly ordered but it simply eliminated the weird C real

This bizarre decimals come only in a dump but not in all sqlite queries that can be done, that confuses a lot.

Where do they come from? I only use either

     ROUND(xx, 1)   from sqlite
     or 
     Math.round(xx*10)/10.  from javascript (Rhino)

I would say from sqlite's ROUND, it looks like a typical and nasty C/C++ arithmetic issues.

13:33 Reply: ORDER BY not working for a specific DB/table (artifact: 66c9b9b354 user: alesXala)

same result using

 ORDER BY cast(lati10 as real), cast(longi as real)

as with

 ORDER BY lati10+0, longi+0

which was already tried

 380|41.2|9.37497676257462
 380|41.2|16.6342872848924
 792|41.2|43.2255548289229
 860|41.2|66.7126410609169
 408|41.2|129.727571723508
 840|41.2|-73.970001
 840|41.2|-73.964172
13:28 Reply: ORDER BY not working for a specific DB/table (artifact: 68f8295a09 user: alesXala)

the result of your query: all between integer and real, actually lati10 a mixture of both

  integer|392|integer|41|real|140.936371
  integer|392|integer|41|real|140.948578
  integer|392|integer|41|real|140.948578
  integer|392|integer|41|real|140.951355
  integer|392|integer|41|real|141.398491536317
  ...
  integer|392|real|41.1|real|140.819427
  integer|392|real|41.1|real|140.83551
  integer|392|real|41.1|real|141.396450589127
  integer|380|real|41.2|real|9.37497676257462
  integer|380|real|41.2|real|16.6342872848924
  integer|792|real|41.2|real|43.2255548289229
  integer|860|real|41.2|real|66.7126410609169
  integer|408|real|41.2|real|129.727571723508
  integer|840|real|41.2|real|-73.970001
  integer|840|real|41.2|real|-73.964172
  integer|840|real|41.2|real|-73.964172
  integer|840|real|41.2|real|-73.955566

also by examining the dump, alfanumerics are written with quotes which where not found.

on the other side it makes no sense that the order changes depending on the filter. As I've explained when request lati10 >= 41.2 the order is correct.

03:10 Post: ORDER BY not working for a specific DB/table (artifact: e8ab02b992 user: alesXala)

A database containing just one table with following schema

CREATE TABLE latiSaltxi (westCCod, lati10, longi);

The following select should result in records sorted first by lati10 and then longi

  SELECT *
  FROM latiSaltxi 
  WHERE lati10 >= 41 AND lati10 <= 41.3 
  ORDER BY lati10, longi

in general it does but not for all values, for example when arriving at lat10 41.2 at some point is not sorted (e.g. -73 < than 129)

  ...
  392|41.1|140.83551
  392|41.1|141.396450589127
  380|41.2|9.37497676257462
  380|41.2|16.6342872848924
  792|41.2|43.2255548289229
  860|41.2|66.7126410609169
  408|41.2|129.727571723508
  840|41.2|-73.970001
  840|41.2|-73.964172
  840|41.2|-73.964172

all values are numeric so it does not help adding +0 to all columns in the query (tried)

But if the filter is set to start with this particular value

   WHERE lati10 >= 41.2 ..

then the result is sorted!

 840|41.2|-73.970001
 840|41.2|-73.964172
 840|41.2|-73.964172
 840|41.2|-73.955566
 840|41.2|-73.955566
 840|41.2|-73.9538569733247
 ...

Dumping the database and building a new one with sqlite3.exe does not help.

This behavior happens with any sqlite version (last tested 3.31.1) It is very strange, probably I am missing something but right now I cannot figure out what.

I can provide the particular data if needed (~ 1 MB 7zipped)