SQLite Forum

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