000001  # 2001 September 15
000002  #
000003  # The author disclaims copyright to this source code.  In place of
000004  # a legal notice, here is a blessing:
000005  #
000006  #    May you do good and not evil.
000007  #    May you find forgiveness for yourself and forgive others.
000008  #    May you share freely, never taking more than you give.
000009  #
000010  #***********************************************************************
000011  # This file implements regression tests for SQLite library.  The
000012  # focus of this file is testing UNION, INTERSECT and EXCEPT operators
000013  # in SELECT statements.
000014  #
000015  
000016  set testdir [file dirname $argv0]
000017  source $testdir/tester.tcl
000018  
000019  # Most tests in this file depend on compound-select. But there are a couple
000020  # right at the end that test DISTINCT, so we cannot omit the entire file.
000021  #
000022  ifcapable compound {
000023  
000024  # Build some test data
000025  #
000026  execsql {
000027    CREATE TABLE t1(n int, log int);
000028    BEGIN;
000029  }
000030  for {set i 1} {$i<32} {incr i} {
000031    for {set j 0} {(1<<$j)<$i} {incr j} {}
000032    execsql "INSERT INTO t1 VALUES($i,$j)"
000033  }
000034  execsql {
000035    COMMIT;
000036  }
000037  
000038  do_test select4-1.0 {
000039    execsql {SELECT DISTINCT log FROM t1 ORDER BY log}
000040  } {0 1 2 3 4 5}
000041  
000042  # Union All operator
000043  #
000044  do_test select4-1.1a {
000045    lsort [execsql {SELECT DISTINCT log FROM t1}]
000046  } {0 1 2 3 4 5}
000047  do_test select4-1.1b {
000048    lsort [execsql {SELECT n FROM t1 WHERE log=3}]
000049  } {5 6 7 8}
000050  do_test select4-1.1c {
000051    execsql {
000052      SELECT DISTINCT log FROM t1
000053      UNION ALL
000054      SELECT n FROM t1 WHERE log=3
000055      ORDER BY log;
000056    }
000057  } {0 1 2 3 4 5 5 6 7 8}
000058  do_test select4-1.1d {
000059    execsql {
000060      CREATE TABLE t2 AS
000061        SELECT DISTINCT log FROM t1
000062        UNION ALL
000063        SELECT n FROM t1 WHERE log=3
000064        ORDER BY log;
000065      SELECT * FROM t2;
000066    }
000067  } {0 1 2 3 4 5 5 6 7 8}
000068  execsql {DROP TABLE t2}
000069  do_test select4-1.1e {
000070    execsql {
000071      CREATE TABLE t2 AS
000072        SELECT DISTINCT log FROM t1
000073        UNION ALL
000074        SELECT n FROM t1 WHERE log=3
000075        ORDER BY log DESC;
000076      SELECT * FROM t2;
000077    }
000078  } {8 7 6 5 5 4 3 2 1 0}
000079  execsql {DROP TABLE t2}
000080  do_test select4-1.1f {
000081    execsql {
000082      SELECT DISTINCT log FROM t1
000083      UNION ALL
000084      SELECT n FROM t1 WHERE log=2
000085    }
000086  } {0 1 2 3 4 5 3 4}
000087  do_test select4-1.1g {
000088    execsql {
000089      CREATE TABLE t2 AS 
000090        SELECT DISTINCT log FROM t1
000091        UNION ALL
000092        SELECT n FROM t1 WHERE log=2;
000093      SELECT * FROM t2;
000094    }
000095  } {0 1 2 3 4 5 3 4}
000096  execsql {DROP TABLE t2}
000097  ifcapable subquery {
000098    do_test select4-1.2 {
000099      execsql {
000100        SELECT log FROM t1 WHERE n IN 
000101          (SELECT DISTINCT log FROM t1 UNION ALL
000102           SELECT n FROM t1 WHERE log=3)
000103        ORDER BY log;
000104      }
000105    } {0 1 2 2 3 3 3 3}
000106  }
000107  
000108  # EVIDENCE-OF: R-02644-22131 In a compound SELECT statement, only the
000109  # last or right-most simple SELECT may have an ORDER BY clause.
000110  #
000111  do_test select4-1.3 {
000112    set v [catch {execsql {
000113      SELECT DISTINCT log FROM t1 ORDER BY log
000114      UNION ALL
000115      SELECT n FROM t1 WHERE log=3
000116      ORDER BY log;
000117    }} msg]
000118    lappend v $msg
000119  } {1 {ORDER BY clause should come after UNION ALL not before}}
000120  do_catchsql_test select4-1.4 {
000121    SELECT (VALUES(0) INTERSECT SELECT(0) UNION SELECT(0) ORDER BY 1 UNION
000122            SELECT 0 UNION SELECT 0 ORDER BY 1);
000123  } {1 {ORDER BY clause should come after UNION not before}}
000124  
000125  # Union operator
000126  #
000127  do_test select4-2.1 {
000128    execsql {
000129      SELECT DISTINCT log FROM t1
000130      UNION
000131      SELECT n FROM t1 WHERE log=3
000132      ORDER BY log;
000133    }
000134  } {0 1 2 3 4 5 6 7 8}
000135  ifcapable subquery {
000136    do_test select4-2.2 {
000137      execsql {
000138        SELECT log FROM t1 WHERE n IN 
000139          (SELECT DISTINCT log FROM t1 UNION
000140           SELECT n FROM t1 WHERE log=3)
000141        ORDER BY log;
000142      }
000143    } {0 1 2 2 3 3 3 3}
000144  }
000145  do_test select4-2.3 {
000146    set v [catch {execsql {
000147      SELECT DISTINCT log FROM t1 ORDER BY log
000148      UNION
000149      SELECT n FROM t1 WHERE log=3
000150      ORDER BY log;
000151    }} msg]
000152    lappend v $msg
000153  } {1 {ORDER BY clause should come after UNION not before}}
000154  do_test select4-2.4 {
000155    set v [catch {execsql {
000156      SELECT 0 ORDER BY (SELECT 0) UNION SELECT 0;
000157    }} msg]
000158    lappend v $msg
000159  } {1 {ORDER BY clause should come after UNION not before}}
000160  do_execsql_test select4-2.5 {
000161    SELECT 123 AS x ORDER BY (SELECT x ORDER BY 1);
000162  } {123}
000163  
000164  # Except operator
000165  #
000166  do_test select4-3.1.1 {
000167    execsql {
000168      SELECT DISTINCT log FROM t1
000169      EXCEPT
000170      SELECT n FROM t1 WHERE log=3
000171      ORDER BY log;
000172    }
000173  } {0 1 2 3 4}
000174  do_test select4-3.1.2 {
000175    execsql {
000176      CREATE TABLE t2 AS 
000177        SELECT DISTINCT log FROM t1
000178        EXCEPT
000179        SELECT n FROM t1 WHERE log=3
000180        ORDER BY log;
000181      SELECT * FROM t2;
000182    }
000183  } {0 1 2 3 4}
000184  execsql {DROP TABLE t2}
000185  do_test select4-3.1.3 {
000186    execsql {
000187      CREATE TABLE t2 AS 
000188        SELECT DISTINCT log FROM t1
000189        EXCEPT
000190        SELECT n FROM t1 WHERE log=3
000191        ORDER BY log DESC;
000192      SELECT * FROM t2;
000193    }
000194  } {4 3 2 1 0}
000195  execsql {DROP TABLE t2}
000196  ifcapable subquery {
000197    do_test select4-3.2 {
000198      execsql {
000199        SELECT log FROM t1 WHERE n IN 
000200          (SELECT DISTINCT log FROM t1 EXCEPT
000201           SELECT n FROM t1 WHERE log=3)
000202        ORDER BY log;
000203      }
000204    } {0 1 2 2}
000205  }
000206  do_test select4-3.3 {
000207    set v [catch {execsql {
000208      SELECT DISTINCT log FROM t1 ORDER BY log
000209      EXCEPT
000210      SELECT n FROM t1 WHERE log=3
000211      ORDER BY log;
000212    }} msg]
000213    lappend v $msg
000214  } {1 {ORDER BY clause should come after EXCEPT not before}}
000215  
000216  # Intersect operator
000217  #
000218  do_test select4-4.1.1 {
000219    execsql {
000220      SELECT DISTINCT log FROM t1
000221      INTERSECT
000222      SELECT n FROM t1 WHERE log=3
000223      ORDER BY log;
000224    }
000225  } {5}
000226  
000227  do_test select4-4.1.2 {
000228    execsql {
000229      SELECT DISTINCT log FROM t1
000230      UNION ALL
000231      SELECT 6
000232      INTERSECT
000233      SELECT n FROM t1 WHERE log=3
000234      ORDER BY t1.log;
000235    }
000236  } {5 6}
000237  
000238  do_test select4-4.1.3 {
000239    execsql {
000240      CREATE TABLE t2 AS
000241        SELECT DISTINCT log FROM t1 UNION ALL SELECT 6
000242        INTERSECT
000243        SELECT n FROM t1 WHERE log=3
000244        ORDER BY log;
000245      SELECT * FROM t2;
000246    }
000247  } {5 6}
000248  execsql {DROP TABLE t2}
000249  do_test select4-4.1.4 {
000250    execsql {
000251      CREATE TABLE t2 AS
000252        SELECT DISTINCT log FROM t1 UNION ALL SELECT 6
000253        INTERSECT
000254        SELECT n FROM t1 WHERE log=3
000255        ORDER BY log DESC;
000256      SELECT * FROM t2;
000257    }
000258  } {6 5}
000259  execsql {DROP TABLE t2}
000260  ifcapable subquery {
000261    do_test select4-4.2 {
000262      execsql {
000263        SELECT log FROM t1 WHERE n IN 
000264          (SELECT DISTINCT log FROM t1 INTERSECT
000265           SELECT n FROM t1 WHERE log=3)
000266        ORDER BY log;
000267      }
000268    } {3}
000269  }
000270  do_test select4-4.3 {
000271    set v [catch {execsql {
000272      SELECT DISTINCT log FROM t1 ORDER BY log
000273      INTERSECT
000274      SELECT n FROM t1 WHERE log=3
000275      ORDER BY log;
000276    }} msg]
000277    lappend v $msg
000278  } {1 {ORDER BY clause should come after INTERSECT not before}}
000279  do_catchsql_test select4-4.4 {
000280    SELECT 3 IN (
000281      SELECT 0 ORDER BY 1
000282      INTERSECT
000283      SELECT 1
000284      INTERSECT 
000285      SELECT 2
000286      ORDER BY 1
000287    );
000288  } {1 {ORDER BY clause should come after INTERSECT not before}}
000289  
000290  # Various error messages while processing UNION or INTERSECT
000291  #
000292  do_test select4-5.1 {
000293    set v [catch {execsql {
000294      SELECT DISTINCT log FROM t2
000295      UNION ALL
000296      SELECT n FROM t1 WHERE log=3
000297      ORDER BY log;
000298    }} msg]
000299    lappend v $msg
000300  } {1 {no such table: t2}}
000301  do_test select4-5.2 {
000302    set v [catch {execsql {
000303      SELECT DISTINCT log AS "xyzzy" FROM t1
000304      UNION ALL
000305      SELECT n FROM t1 WHERE log=3
000306      ORDER BY xyzzy;
000307    }} msg]
000308    lappend v $msg
000309  } {0 {0 1 2 3 4 5 5 6 7 8}}
000310  do_test select4-5.2b {
000311    set v [catch {execsql {
000312      SELECT DISTINCT log AS xyzzy FROM t1
000313      UNION ALL
000314      SELECT n FROM t1 WHERE log=3
000315      ORDER BY "xyzzy";
000316    }} msg]
000317    lappend v $msg
000318  } {0 {0 1 2 3 4 5 5 6 7 8}}
000319  do_test select4-5.2c {
000320    set v [catch {execsql {
000321      SELECT DISTINCT log FROM t1
000322      UNION ALL
000323      SELECT n FROM t1 WHERE log=3
000324      ORDER BY "xyzzy";
000325    }} msg]
000326    lappend v $msg
000327  } {1 {1st ORDER BY term does not match any column in the result set}}
000328  do_test select4-5.2d {
000329    set v [catch {execsql {
000330      SELECT DISTINCT log FROM t1
000331      INTERSECT
000332      SELECT n FROM t1 WHERE log=3
000333      ORDER BY "xyzzy";
000334    }} msg]
000335    lappend v $msg
000336  } {1 {1st ORDER BY term does not match any column in the result set}}
000337  do_test select4-5.2e {
000338    set v [catch {execsql {
000339      SELECT DISTINCT log FROM t1
000340      UNION ALL
000341      SELECT n FROM t1 WHERE log=3
000342      ORDER BY n;
000343    }} msg]
000344    lappend v $msg
000345  } {0 {0 1 2 3 4 5 5 6 7 8}}
000346  do_test select4-5.2f {
000347    catchsql {
000348      SELECT DISTINCT log FROM t1
000349      UNION ALL
000350      SELECT n FROM t1 WHERE log=3
000351      ORDER BY log;
000352    }
000353  } {0 {0 1 2 3 4 5 5 6 7 8}}
000354  do_test select4-5.2g {
000355    catchsql {
000356      SELECT DISTINCT log FROM t1
000357      UNION ALL
000358      SELECT n FROM t1 WHERE log=3
000359      ORDER BY 1;
000360    }
000361  } {0 {0 1 2 3 4 5 5 6 7 8}}
000362  do_test select4-5.2h {
000363    catchsql {
000364      SELECT DISTINCT log FROM t1
000365      UNION ALL
000366      SELECT n FROM t1 WHERE log=3
000367      ORDER BY 2;
000368    }
000369  } {1 {1st ORDER BY term out of range - should be between 1 and 1}}
000370  do_test select4-5.2i {
000371    catchsql {
000372      SELECT DISTINCT 1, log FROM t1
000373      UNION ALL
000374      SELECT 2, n FROM t1 WHERE log=3
000375      ORDER BY 2, 1;
000376    }
000377  } {0 {1 0 1 1 1 2 1 3 1 4 1 5 2 5 2 6 2 7 2 8}}
000378  do_test select4-5.2j {
000379    catchsql {
000380      SELECT DISTINCT 1, log FROM t1
000381      UNION ALL
000382      SELECT 2, n FROM t1 WHERE log=3
000383      ORDER BY 1, 2 DESC;
000384    }
000385  } {0 {1 5 1 4 1 3 1 2 1 1 1 0 2 8 2 7 2 6 2 5}}
000386  do_test select4-5.2k {
000387    catchsql {
000388      SELECT DISTINCT 1, log FROM t1
000389      UNION ALL
000390      SELECT 2, n FROM t1 WHERE log=3
000391      ORDER BY n, 1;
000392    }
000393  } {0 {1 0 1 1 1 2 1 3 1 4 1 5 2 5 2 6 2 7 2 8}}
000394  do_test select4-5.3 {
000395    set v [catch {execsql {
000396      SELECT DISTINCT log, n FROM t1
000397      UNION ALL
000398      SELECT n FROM t1 WHERE log=3
000399      ORDER BY log;
000400    }} msg]
000401    lappend v $msg
000402  } {1 {SELECTs to the left and right of UNION ALL do not have the same number of result columns}}
000403  do_test select4-5.3-3807-1 {
000404    catchsql {
000405      SELECT 1 UNION SELECT 2, 3 UNION SELECT 4, 5 ORDER BY 1;
000406    }
000407  } {1 {SELECTs to the left and right of UNION do not have the same number of result columns}}
000408  do_test select4-5.4 {
000409    set v [catch {execsql {
000410      SELECT log FROM t1 WHERE n=2
000411      UNION ALL
000412      SELECT log FROM t1 WHERE n=3
000413      UNION ALL
000414      SELECT log FROM t1 WHERE n=4
000415      UNION ALL
000416      SELECT log FROM t1 WHERE n=5
000417      ORDER BY log;
000418    }} msg]
000419    lappend v $msg
000420  } {0 {1 2 2 3}}
000421  
000422  do_test select4-6.1 {
000423    execsql {
000424      SELECT log, count(*) as cnt FROM t1 GROUP BY log
000425      UNION
000426      SELECT log, n FROM t1 WHERE n=7
000427      ORDER BY cnt, log;
000428    }
000429  } {0 1 1 1 2 2 3 4 3 7 4 8 5 15}
000430  do_test select4-6.2 {
000431    execsql {
000432      SELECT log, count(*) FROM t1 GROUP BY log
000433      UNION
000434      SELECT log, n FROM t1 WHERE n=7
000435      ORDER BY count(*), log;
000436    }
000437  } {0 1 1 1 2 2 3 4 3 7 4 8 5 15}
000438  
000439  # NULLs are indistinct for the UNION operator.
000440  # Make sure the UNION operator recognizes this
000441  #
000442  do_test select4-6.3 {
000443    execsql {
000444      SELECT NULL UNION SELECT NULL UNION
000445      SELECT 1 UNION SELECT 2 AS 'x'
000446      ORDER BY x;
000447    }
000448  } {{} 1 2}
000449  do_test select4-6.3.1 {
000450    execsql {
000451      SELECT NULL UNION ALL SELECT NULL UNION ALL
000452      SELECT 1 UNION ALL SELECT 2 AS 'x'
000453      ORDER BY x;
000454    }
000455  } {{} {} 1 2}
000456  
000457  # Make sure the DISTINCT keyword treats NULLs as indistinct.
000458  #
000459  ifcapable subquery {
000460    do_test select4-6.4 {
000461      execsql {
000462        SELECT * FROM (
000463           SELECT NULL, 1 UNION ALL SELECT NULL, 1
000464        );
000465      }
000466    } {{} 1 {} 1}
000467    do_test select4-6.5 {
000468      execsql {
000469        SELECT DISTINCT * FROM (
000470           SELECT NULL, 1 UNION ALL SELECT NULL, 1
000471        );
000472      }
000473    } {{} 1}
000474    do_test select4-6.6 {
000475      execsql {
000476        SELECT DISTINCT * FROM (
000477           SELECT 1,2  UNION ALL SELECT 1,2
000478        );
000479      }
000480    } {1 2}
000481  }
000482  
000483  # Test distinctness of NULL in other ways.
000484  #
000485  do_test select4-6.7 {
000486    execsql {
000487      SELECT NULL EXCEPT SELECT NULL
000488    }
000489  } {}
000490  
000491  
000492  # Make sure column names are correct when a compound select appears as
000493  # an expression in the WHERE clause.
000494  #
000495  do_test select4-7.1 {
000496    execsql {
000497      CREATE TABLE t2 AS SELECT log AS 'x', count(*) AS 'y' FROM t1 GROUP BY log;
000498      SELECT * FROM t2 ORDER BY x;
000499    }
000500  } {0 1 1 1 2 2 3 4 4 8 5 15}  
000501  ifcapable subquery {
000502    do_test select4-7.2 {
000503      execsql2 {
000504        SELECT * FROM t1 WHERE n IN (SELECT n FROM t1 INTERSECT SELECT x FROM t2)
000505        ORDER BY n
000506      }
000507    } {n 1 log 0 n 2 log 1 n 3 log 2 n 4 log 2 n 5 log 3}
000508    do_test select4-7.3 {
000509      execsql2 {
000510        SELECT * FROM t1 WHERE n IN (SELECT n FROM t1 EXCEPT SELECT x FROM t2)
000511        ORDER BY n LIMIT 2
000512      }
000513    } {n 6 log 3 n 7 log 3}
000514    do_test select4-7.4 {
000515      execsql2 {
000516        SELECT * FROM t1 WHERE n IN (SELECT n FROM t1 UNION SELECT x FROM t2)
000517        ORDER BY n LIMIT 2
000518      }
000519    } {n 1 log 0 n 2 log 1}
000520  } ;# ifcapable subquery
000521  
000522  } ;# ifcapable compound
000523  
000524  # Make sure DISTINCT works appropriately on TEXT and NUMERIC columns.
000525  do_test select4-8.1 {
000526    execsql {
000527      BEGIN;
000528      CREATE TABLE t3(a text, b float, c text);
000529      INSERT INTO t3 VALUES(1, 1.1, '1.1');
000530      INSERT INTO t3 VALUES(2, 1.10, '1.10');
000531      INSERT INTO t3 VALUES(3, 1.10, '1.1');
000532      INSERT INTO t3 VALUES(4, 1.1, '1.10');
000533      INSERT INTO t3 VALUES(5, 1.2, '1.2');
000534      INSERT INTO t3 VALUES(6, 1.3, '1.3');
000535      COMMIT;
000536    }
000537    execsql {
000538      SELECT DISTINCT b FROM t3 ORDER BY c;
000539    }
000540  } {1.1 1.2 1.3}
000541  do_test select4-8.2 {
000542    execsql {
000543      SELECT DISTINCT c FROM t3 ORDER BY c;
000544    }
000545  } {1.1 1.10 1.2 1.3}
000546  
000547  # Make sure the names of columns are taken from the right-most subquery
000548  # right in a compound query.  Ticket #1721
000549  #
000550  ifcapable compound {
000551  
000552  do_test select4-9.1 {
000553    execsql2 {
000554      SELECT x, y FROM t2 UNION SELECT a, b FROM t3 ORDER BY x LIMIT 1
000555    }
000556  } {x 0 y 1}
000557  do_test select4-9.2 {
000558    execsql2 {
000559      SELECT x, y FROM t2 UNION ALL SELECT a, b FROM t3 ORDER BY x LIMIT 1
000560    }
000561  } {x 0 y 1}
000562  do_test select4-9.3 {
000563    execsql2 {
000564      SELECT x, y FROM t2 EXCEPT SELECT a, b FROM t3 ORDER BY x LIMIT 1
000565    }
000566  } {x 0 y 1}
000567  do_test select4-9.4 {
000568    execsql2 {
000569      SELECT x, y FROM t2 INTERSECT SELECT 0 AS a, 1 AS b;
000570    }
000571  } {x 0 y 1}
000572  do_test select4-9.5 {
000573    execsql2 {
000574      SELECT 0 AS x, 1 AS y
000575      UNION
000576      SELECT 2 AS p, 3 AS q
000577      UNION
000578      SELECT 4 AS a, 5 AS b
000579      ORDER BY x LIMIT 1
000580    }
000581  } {x 0 y 1}
000582  
000583  ifcapable subquery {
000584  do_test select4-9.6 {
000585    execsql2 {
000586      SELECT * FROM (
000587        SELECT 0 AS x, 1 AS y
000588        UNION
000589        SELECT 2 AS p, 3 AS q
000590        UNION
000591        SELECT 4 AS a, 5 AS b
000592      ) ORDER BY 1 LIMIT 1;
000593    }
000594  } {x 0 y 1}
000595  do_test select4-9.7 {
000596    execsql2 {
000597      SELECT * FROM (
000598        SELECT 0 AS x, 1 AS y
000599        UNION
000600        SELECT 2 AS p, 3 AS q
000601        UNION
000602        SELECT 4 AS a, 5 AS b
000603      ) ORDER BY x LIMIT 1;
000604    }
000605  } {x 0 y 1}
000606  } ;# ifcapable subquery
000607  
000608  do_test select4-9.8 {
000609    execsql {
000610      SELECT 0 AS x, 1 AS y
000611      UNION
000612      SELECT 2 AS y, -3 AS x
000613      ORDER BY x LIMIT 1;
000614    }
000615  } {0 1}
000616  
000617  do_test select4-9.9.1 {
000618    execsql2 {
000619      SELECT 1 AS a, 2 AS b UNION ALL SELECT 3 AS b, 4 AS a
000620    }
000621  } {a 1 b 2 a 3 b 4}
000622  
000623  ifcapable subquery {
000624  do_test select4-9.9.2 {
000625    execsql2 {
000626      SELECT * FROM (SELECT 1 AS a, 2 AS b UNION ALL SELECT 3 AS b, 4 AS a)
000627       WHERE b=3
000628    }
000629  } {}
000630  do_test select4-9.10 {
000631    execsql2 {
000632      SELECT * FROM (SELECT 1 AS a, 2 AS b UNION ALL SELECT 3 AS b, 4 AS a)
000633       WHERE b=2
000634    }
000635  } {a 1 b 2}
000636  do_test select4-9.11 {
000637    execsql2 {
000638      SELECT * FROM (SELECT 1 AS a, 2 AS b UNION ALL SELECT 3 AS e, 4 AS b)
000639       WHERE b=2
000640    }
000641  } {a 1 b 2}
000642  do_test select4-9.12 {
000643    execsql2 {
000644      SELECT * FROM (SELECT 1 AS a, 2 AS b UNION ALL SELECT 3 AS e, 4 AS b)
000645       WHERE b>0
000646    }
000647  } {a 1 b 2 a 3 b 4}
000648  } ;# ifcapable subquery
000649  
000650  # Try combining DISTINCT, LIMIT, and OFFSET.  Make sure they all work
000651  # together.
000652  #
000653  do_test select4-10.1 {
000654    execsql {
000655      SELECT DISTINCT log FROM t1 ORDER BY log
000656    }
000657  } {0 1 2 3 4 5}
000658  do_test select4-10.2 {
000659    execsql {
000660      SELECT DISTINCT log FROM t1 ORDER BY log LIMIT 4
000661    }
000662  } {0 1 2 3}
000663  do_test select4-10.3 {
000664    execsql {
000665      SELECT DISTINCT log FROM t1 ORDER BY log LIMIT 0
000666    }
000667  } {}
000668  do_test select4-10.4 {
000669    execsql {
000670      SELECT DISTINCT log FROM t1 ORDER BY log LIMIT -1
000671    }
000672  } {0 1 2 3 4 5}
000673  do_test select4-10.5 {
000674    execsql {
000675      SELECT DISTINCT log FROM t1 ORDER BY log LIMIT -1 OFFSET 2
000676    }
000677  } {2 3 4 5}
000678  do_test select4-10.6 {
000679    execsql {
000680      SELECT DISTINCT log FROM t1 ORDER BY log LIMIT 3 OFFSET 2
000681    }
000682  } {2 3 4}
000683  do_test select4-10.7 {
000684    execsql {
000685      SELECT DISTINCT log FROM t1 ORDER BY +log LIMIT 3 OFFSET 20
000686    }
000687  } {}
000688  do_test select4-10.8 {
000689    execsql {
000690      SELECT DISTINCT log FROM t1 ORDER BY log LIMIT 0 OFFSET 3
000691    }
000692  } {}
000693  do_test select4-10.9 {
000694    execsql {
000695      SELECT DISTINCT max(n), log FROM t1 ORDER BY +log; -- LIMIT 2 OFFSET 1
000696    }
000697  } {31 5}
000698  
000699  # Make sure compound SELECTs with wildly different numbers of columns
000700  # do not cause assertion faults due to register allocation issues.
000701  #
000702  do_test select4-11.1 {
000703    catchsql {
000704      SELECT x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x FROM t2
000705      UNION
000706      SELECT x FROM t2
000707    }
000708  } {1 {SELECTs to the left and right of UNION do not have the same number of result columns}}
000709  do_test select4-11.2 {
000710    catchsql {
000711      SELECT x FROM t2
000712      UNION
000713      SELECT x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x FROM t2
000714    }
000715  } {1 {SELECTs to the left and right of UNION do not have the same number of result columns}}
000716  do_test select4-11.3 {
000717    catchsql {
000718      SELECT x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x FROM t2
000719      UNION ALL
000720      SELECT x FROM t2
000721    }
000722  } {1 {SELECTs to the left and right of UNION ALL do not have the same number of result columns}}
000723  do_test select4-11.4 {
000724    catchsql {
000725      SELECT x FROM t2
000726      UNION ALL
000727      SELECT x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x FROM t2
000728    }
000729  } {1 {SELECTs to the left and right of UNION ALL do not have the same number of result columns}}
000730  do_test select4-11.5 {
000731    catchsql {
000732      SELECT x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x FROM t2
000733      EXCEPT
000734      SELECT x FROM t2
000735    }
000736  } {1 {SELECTs to the left and right of EXCEPT do not have the same number of result columns}}
000737  do_test select4-11.6 {
000738    catchsql {
000739      SELECT x FROM t2
000740      EXCEPT
000741      SELECT x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x FROM t2
000742    }
000743  } {1 {SELECTs to the left and right of EXCEPT do not have the same number of result columns}}
000744  do_test select4-11.7 {
000745    catchsql {
000746      SELECT x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x FROM t2
000747      INTERSECT
000748      SELECT x FROM t2
000749    }
000750  } {1 {SELECTs to the left and right of INTERSECT do not have the same number of result columns}}
000751  do_test select4-11.8 {
000752    catchsql {
000753      SELECT x FROM t2
000754      INTERSECT
000755      SELECT x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x FROM t2
000756    }
000757  } {1 {SELECTs to the left and right of INTERSECT do not have the same number of result columns}}
000758  
000759  do_test select4-11.11 {
000760    catchsql {
000761      SELECT x FROM t2
000762      UNION
000763      SELECT x FROM t2
000764      UNION ALL
000765      SELECT x FROM t2
000766      EXCEPT
000767      SELECT x FROM t2
000768      INTERSECT
000769      SELECT x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x FROM t2
000770    }
000771  } {1 {SELECTs to the left and right of INTERSECT do not have the same number of result columns}}
000772  do_test select4-11.12 {
000773    catchsql {
000774      SELECT x FROM t2
000775      UNION
000776      SELECT x FROM t2
000777      UNION ALL
000778      SELECT x FROM t2
000779      EXCEPT
000780      SELECT x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x FROM t2
000781      EXCEPT
000782      SELECT x FROM t2
000783    }
000784  } {1 {SELECTs to the left and right of EXCEPT do not have the same number of result columns}}
000785  do_test select4-11.13 {
000786    catchsql {
000787      SELECT x FROM t2
000788      UNION
000789      SELECT x FROM t2
000790      UNION ALL
000791      SELECT x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x FROM t2
000792      UNION ALL
000793      SELECT x FROM t2
000794      EXCEPT
000795      SELECT x FROM t2
000796    }
000797  } {1 {SELECTs to the left and right of UNION ALL do not have the same number of result columns}}
000798  do_test select4-11.14 {
000799    catchsql {
000800      SELECT x FROM t2
000801      UNION
000802      SELECT x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x FROM t2
000803      UNION
000804      SELECT x FROM t2
000805      UNION ALL
000806      SELECT x FROM t2
000807      EXCEPT
000808      SELECT x FROM t2
000809    }
000810  } {1 {SELECTs to the left and right of UNION do not have the same number of result columns}}
000811  do_test select4-11.15 {
000812    catchsql {
000813      SELECT x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x FROM t2
000814      UNION
000815      SELECT x FROM t2
000816      INTERSECT
000817      SELECT x FROM t2
000818      UNION ALL
000819      SELECT x FROM t2
000820      EXCEPT
000821      SELECT x FROM t2
000822    }
000823  } {1 {SELECTs to the left and right of UNION do not have the same number of result columns}}
000824  do_test select4-11.16 {
000825    catchsql {
000826      INSERT INTO t2(rowid) VALUES(2) UNION SELECT 3,4 UNION SELECT 5,6 ORDER BY 1;
000827    }
000828  } {1 {SELECTs to the left and right of UNION do not have the same number of result columns}}
000829  
000830  do_test select4-12.1 {
000831    sqlite3 db2 :memory:
000832    catchsql {
000833      SELECT 1 UNION SELECT 2,3 UNION SELECT 4,5 ORDER BY 1;
000834    } db2
000835  } {1 {SELECTs to the left and right of UNION do not have the same number of result columns}}
000836  
000837  } ;# ifcapable compound
000838  
000839  
000840  # Ticket [3557ad65a076c] - Incorrect DISTINCT processing with an
000841  # indexed query using IN.
000842  #
000843  do_test select4-13.1 {
000844    sqlite3 db test.db
000845    db eval {
000846      CREATE TABLE t13(a,b);
000847      INSERT INTO t13 VALUES(1,1);
000848      INSERT INTO t13 VALUES(2,1);
000849      INSERT INTO t13 VALUES(3,1);
000850      INSERT INTO t13 VALUES(2,2);
000851      INSERT INTO t13 VALUES(3,2);
000852      INSERT INTO t13 VALUES(4,2);
000853      CREATE INDEX t13ab ON t13(a,b);
000854      SELECT DISTINCT b from t13 WHERE a IN (1,2,3);
000855    }
000856  } {1 2}
000857  
000858  # 2014-02-18: Make sure compound SELECTs work with VALUES clauses
000859  #
000860  do_execsql_test select4-14.1 {
000861    CREATE TABLE t14(a,b,c);
000862    INSERT INTO t14 VALUES(1,2,3),(4,5,6);
000863    SELECT * FROM t14 INTERSECT VALUES(3,2,1),(2,3,1),(1,2,3),(2,1,3);
000864  } {1 2 3}
000865  do_execsql_test select4-14.2 {
000866    SELECT * FROM t14 INTERSECT VALUES(1,2,3);
000867  } {1 2 3}
000868  do_execsql_test select4-14.3 {
000869    SELECT * FROM t14
000870     UNION VALUES(3,2,1),(2,3,1),(1,2,3),(7,8,9),(4,5,6)
000871     UNION SELECT * FROM t14 ORDER BY 1, 2, 3
000872  } {1 2 3 2 3 1 3 2 1 4 5 6 7 8 9}
000873  do_execsql_test select4-14.4 {
000874    SELECT * FROM t14
000875     UNION VALUES(3,2,1)
000876     UNION SELECT * FROM t14 ORDER BY 1, 2, 3
000877  } {1 2 3 3 2 1 4 5 6}
000878  do_execsql_test select4-14.5 {
000879    SELECT * FROM t14 EXCEPT VALUES(3,2,1),(2,3,1),(1,2,3),(2,1,3);
000880  } {4 5 6}
000881  do_execsql_test select4-14.6 {
000882    SELECT * FROM t14 EXCEPT VALUES(1,2,3)
000883  } {4 5 6}
000884  do_execsql_test select4-14.7 {
000885    SELECT * FROM t14 EXCEPT VALUES(1,2,3) EXCEPT VALUES(4,5,6)
000886  } {}
000887  do_execsql_test select4-14.8 {
000888    SELECT * FROM t14 EXCEPT VALUES('a','b','c') EXCEPT VALUES(4,5,6)
000889  } {1 2 3}
000890  do_execsql_test select4-14.9 {
000891    SELECT * FROM t14 UNION ALL VALUES(3,2,1),(2,3,1),(1,2,3),(2,1,3);
000892  } {1 2 3 4 5 6 3 2 1 2 3 1 1 2 3 2 1 3}
000893  do_execsql_test select4-14.10 {
000894    SELECT (VALUES(1),(2),(3),(4))
000895  } {1}
000896  do_execsql_test select4-14.11 {
000897    SELECT (SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4)
000898  } {1}
000899  do_execsql_test select4-14.12 {
000900    VALUES(1) UNION VALUES(2);
000901  } {1 2}
000902  do_execsql_test select4-14.13 {
000903    VALUES(1),(2),(3) EXCEPT VALUES(2);
000904  } {1 3}
000905  do_execsql_test select4-14.14 {
000906    VALUES(1),(2),(3) EXCEPT VALUES(1),(3);
000907  } {2}
000908  do_execsql_test select4-14.15 {
000909    SELECT * FROM (SELECT 123), (SELECT 456) ON likely(0 OR 1) OR 0;
000910  } {123 456}
000911  do_execsql_test select4-14.16 {
000912    VALUES(1),(2),(3),(4) UNION ALL SELECT 5 LIMIT 99;
000913  } {1 2 3 4 5}
000914  do_execsql_test select4-14.17 {
000915    VALUES(1),(2),(3),(4) UNION ALL SELECT 5 LIMIT 3;
000916  } {1 2 3}
000917  
000918  # Ticket https://www.sqlite.org/src/info/d06a25c84454a372
000919  # Incorrect answer due to two co-routines using the same registers and expecting
000920  # those register values to be preserved across a Yield.
000921  #
000922  do_execsql_test select4-15.1 {
000923    DROP TABLE IF EXISTS tx;
000924    CREATE TABLE tx(id INTEGER PRIMARY KEY, a, b);
000925    INSERT INTO tx(a,b) VALUES(33,456);
000926    INSERT INTO tx(a,b) VALUES(33,789);
000927  
000928    SELECT DISTINCT t0.id, t0.a, t0.b
000929      FROM tx AS t0, tx AS t1
000930     WHERE t0.a=t1.a AND t1.a=33 AND t0.b=456
000931    UNION
000932    SELECT DISTINCT t0.id, t0.a, t0.b
000933      FROM tx AS t0, tx AS t1
000934     WHERE t0.a=t1.a AND t1.a=33 AND t0.b=789
000935     ORDER BY 1;
000936  } {1 33 456 2 33 789}
000937  
000938  # Enhancement (2016-03-15):  Use a co-routine for subqueries if the
000939  # subquery is guaranteed to be the outer-most query
000940  #
000941  do_execsql_test select4-16.1 {
000942    DROP TABLE IF EXISTS t1;
000943    CREATE TABLE t1(a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r,s,t,u,v,w,x,y,z,
000944    PRIMARY KEY(a,b DESC)) WITHOUT ROWID;
000945  
000946    WITH RECURSIVE c(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM c WHERE x<100)
000947    INSERT INTO t1(a,b,c,d)
000948      SELECT x%10, x/10, x, printf('xyz%dabc',x) FROM c;
000949  
000950    SELECT t3.c FROM 
000951      (SELECT a,max(b) AS m FROM t1 WHERE a>=5 GROUP BY a) AS t2
000952      JOIN t1 AS t3
000953    WHERE t2.a=t3.a AND t2.m=t3.b
000954    ORDER BY t3.a;
000955  } {95 96 97 98 99}
000956  do_execsql_test select4-16.2 {
000957    SELECT t3.c FROM 
000958      (SELECT a,max(b) AS m FROM t1 WHERE a>=5 GROUP BY a) AS t2
000959      CROSS JOIN t1 AS t3
000960    WHERE t2.a=t3.a AND t2.m=t3.b
000961    ORDER BY t3.a;
000962  } {95 96 97 98 99}
000963  do_execsql_test select4-16.3 {
000964    SELECT t3.c FROM 
000965      (SELECT a,max(b) AS m FROM t1 WHERE a>=5 GROUP BY a) AS t2
000966      LEFT JOIN t1 AS t3
000967    WHERE t2.a=t3.a AND t2.m=t3.b
000968    ORDER BY t3.a;
000969  } {95 96 97 98 99}
000970  
000971  # Ticket https://www.sqlite.org/src/tktview/f7f8c97e975978d45  on 2016-04-25
000972  #
000973  # The where push-down optimization from 2015-06-02 is suppose to disable
000974  # on aggregate subqueries.  But if the subquery is a compound where the
000975  # last SELECT is non-aggregate but some other SELECT is an aggregate, the
000976  # test is incomplete and the optimization is not properly disabled.
000977  # 
000978  # The following test cases verify that the fix works.
000979  #
000980  do_execsql_test select4-17.1 {
000981    DROP TABLE IF EXISTS t1;
000982    CREATE TABLE t1(a int, b int);
000983    INSERT INTO t1 VALUES(1,2),(1,18),(2,19);
000984    SELECT x, y FROM (
000985      SELECT 98 AS x, 99 AS y
000986      UNION
000987      SELECT a AS x, sum(b) AS y FROM t1 GROUP BY a
000988    ) AS w WHERE y>=20
000989    ORDER BY +x;
000990  } {1 20 98 99}
000991  do_execsql_test select4-17.2 {
000992    SELECT x, y FROM (
000993      SELECT a AS x, sum(b) AS y FROM t1 GROUP BY a
000994      UNION
000995      SELECT 98 AS x, 99 AS y
000996    ) AS w WHERE y>=20
000997    ORDER BY +x;
000998  } {1 20 98 99}
000999  do_catchsql_test select4-17.3 {
001000    SELECT x, y FROM (
001001      SELECT a AS x, sum(b) AS y FROM t1 GROUP BY a LIMIT 3
001002      UNION
001003      SELECT 98 AS x, 99 AS y
001004    ) AS w WHERE y>=20
001005    ORDER BY +x;
001006  } {1 {LIMIT clause should come after UNION not before}}
001007  
001008  # 2020-04-03 ticket 51166be0159fd2ce from Yong Heng.
001009  # Adverse interaction between the constant propagation and push-down
001010  # optimizations.
001011  #
001012  reset_db
001013  do_execsql_test select4-18.1 {
001014    CREATE VIEW v0(v0) AS WITH v0 AS(SELECT 0 v0) SELECT(SELECT min(v0) OVER()) FROM v0 GROUP BY v0;
001015    SELECT *FROM v0 v1 JOIN v0 USING(v0) WHERE datetime(v0) = (v0.v0)AND v0 = 10;
001016  } {}
001017  do_execsql_test select4-18.2 {
001018    CREATE VIEW t1(aa) AS
001019       WITH t2(bb) AS (SELECT 123)
001020       SELECT (SELECT min(bb) OVER()) FROM t2 GROUP BY bb;
001021    SELECT * FROM t1;
001022  } {123}
001023  do_execsql_test select4-18.3 {
001024    SELECT * FROM t1 AS z1 JOIN t1 AS z2 USING(aa)
001025     WHERE abs(z1.aa)=z2.aa AND z1.aa=123;
001026  } {123}
001027  
001028  # 2021-03-31 Fix an assert() problem in the logic at the end of sqlite3Select()
001029  # that validates AggInfo.  The checks to ensure that AggInfo.aCol[].pCExpr
001030  # references a valid expression was looking at an expression that had been
001031  # deleted by the truth optimization in sqlite3ExprAnd() which was invoked by
001032  # the push-down optimization.  This is harmless in delivery builds, as that code
001033  # only runs with SQLITE_DEBUG.  But it should still be fixed.  The problem
001034  # was discovered by dbsqlfuzz (crash-dece7b67a3552ed7e571a7bda903afd1f7bd9b21)
001035  #
001036  reset_db
001037  do_execsql_test select4-19.1 {
001038    CREATE TABLE t1(x);
001039    INSERT INTO t1 VALUES(99);
001040    SELECT sum((SELECT 1 FROM (SELECT 2 WHERE x IS NULL) WHERE 0)) FROM t1;
001041  } {{}}
001042  
001043  finish_test