1 # 2001 September 15

     2 #

     3 # The author disclaims copyright to this source code.  In place of

     4 # a legal notice, here is a blessing:

     5 #

     6 #    May you do good and not evil.

     7 #    May you find forgiveness for yourself and forgive others.

     8 #    May you share freely, never taking more than you give.

     9 #

    10 #***********************************************************************

    11 # This file implements regression tests for SQLite library.  The

    12 # focus of this file is testing UNION, INTERSECT and EXCEPT operators

    13 # in SELECT statements.

    14 #

    15 # $Id: select4.test,v 1.29 2008/08/04 03:51:24 danielk1977 Exp $

    16 

    17 set testdir [file dirname $argv0]

    18 source $testdir/tester.tcl

    19 

    20 # Most tests in this file depend on compound-select. But there are a couple

    21 # right at the end that test DISTINCT, so we cannot omit the entire file.

    22 #

    23 ifcapable compound {

    24 

    25 # Build some test data

    26 #

    27 execsql {

    28   CREATE TABLE t1(n int, log int);

    29   BEGIN;

    30 }

    31 for {set i 1} {$i<32} {incr i} {

    32   for {set j 0} {(1<<$j)<$i} {incr j} {}

    33   execsql "INSERT INTO t1 VALUES($i,$j)"

    34 }

    35 execsql {

    36   COMMIT;

    37 }

    38 

    39 do_test select4-1.0 {

    40   execsql {SELECT DISTINCT log FROM t1 ORDER BY log}

    41 } {0 1 2 3 4 5}

    42 

    43 # Union All operator

    44 #

    45 do_test select4-1.1a {

    46   lsort [execsql {SELECT DISTINCT log FROM t1}]

    47 } {0 1 2 3 4 5}

    48 do_test select4-1.1b {

    49   lsort [execsql {SELECT n FROM t1 WHERE log=3}]

    50 } {5 6 7 8}

    51 do_test select4-1.1c {

    52   execsql {

    53     SELECT DISTINCT log FROM t1

    54     UNION ALL

    55     SELECT n FROM t1 WHERE log=3

    56     ORDER BY log;

    57   }

    58 } {0 1 2 3 4 5 5 6 7 8}

    59 do_test select4-1.1d {

    60   execsql {

    61     CREATE TABLE t2 AS

    62       SELECT DISTINCT log FROM t1

    63       UNION ALL

    64       SELECT n FROM t1 WHERE log=3

    65       ORDER BY log;

    66     SELECT * FROM t2;

    67   }

    68 } {0 1 2 3 4 5 5 6 7 8}

    69 execsql {DROP TABLE t2}

    70 do_test select4-1.1e {

    71   execsql {

    72     CREATE TABLE t2 AS

    73       SELECT DISTINCT log FROM t1

    74       UNION ALL

    75       SELECT n FROM t1 WHERE log=3

    76       ORDER BY log DESC;

    77     SELECT * FROM t2;

    78   }

    79 } {8 7 6 5 5 4 3 2 1 0}

    80 execsql {DROP TABLE t2}

    81 do_test select4-1.1f {

    82   execsql {

    83     SELECT DISTINCT log FROM t1

    84     UNION ALL

    85     SELECT n FROM t1 WHERE log=2

    86   }

    87 } {0 1 2 3 4 5 3 4}

    88 do_test select4-1.1g {

    89   execsql {

    90     CREATE TABLE t2 AS 

    91       SELECT DISTINCT log FROM t1

    92       UNION ALL

    93       SELECT n FROM t1 WHERE log=2;

    94     SELECT * FROM t2;

    95   }

    96 } {0 1 2 3 4 5 3 4}

    97 execsql {DROP TABLE t2}

    98 ifcapable subquery {

    99   do_test select4-1.2 {

   100     execsql {

   101       SELECT log FROM t1 WHERE n IN 

   102         (SELECT DISTINCT log FROM t1 UNION ALL

   103          SELECT n FROM t1 WHERE log=3)

   104       ORDER BY log;

   105     }

   106   } {0 1 2 2 3 3 3 3}

   107 }

   108 do_test select4-1.3 {

   109   set v [catch {execsql {

   110     SELECT DISTINCT log FROM t1 ORDER BY log

   111     UNION ALL

   112     SELECT n FROM t1 WHERE log=3

   113     ORDER BY log;

   114   }} msg]

   115   lappend v $msg

   116 } {1 {ORDER BY clause should come after UNION ALL not before}}

   117 

   118 # Union operator

   119 #

   120 do_test select4-2.1 {

   121   execsql {

   122     SELECT DISTINCT log FROM t1

   123     UNION

   124     SELECT n FROM t1 WHERE log=3

   125     ORDER BY log;

   126   }

   127 } {0 1 2 3 4 5 6 7 8}

   128 ifcapable subquery {

   129   do_test select4-2.2 {

   130     execsql {

   131       SELECT log FROM t1 WHERE n IN 

   132         (SELECT DISTINCT log FROM t1 UNION

   133          SELECT n FROM t1 WHERE log=3)

   134       ORDER BY log;

   135     }

   136   } {0 1 2 2 3 3 3 3}

   137 }

   138 do_test select4-2.3 {

   139   set v [catch {execsql {

   140     SELECT DISTINCT log FROM t1 ORDER BY log

   141     UNION

   142     SELECT n FROM t1 WHERE log=3

   143     ORDER BY log;

   144   }} msg]

   145   lappend v $msg

   146 } {1 {ORDER BY clause should come after UNION not before}}

   147 

   148 # Except operator

   149 #

   150 do_test select4-3.1.1 {

   151   execsql {

   152     SELECT DISTINCT log FROM t1

   153     EXCEPT

   154     SELECT n FROM t1 WHERE log=3

   155     ORDER BY log;

   156   }

   157 } {0 1 2 3 4}

   158 do_test select4-3.1.2 {

   159   execsql {

   160     CREATE TABLE t2 AS 

   161       SELECT DISTINCT log FROM t1

   162       EXCEPT

   163       SELECT n FROM t1 WHERE log=3

   164       ORDER BY log;

   165     SELECT * FROM t2;

   166   }

   167 } {0 1 2 3 4}

   168 execsql {DROP TABLE t2}

   169 do_test select4-3.1.3 {

   170   execsql {

   171     CREATE TABLE t2 AS 

   172       SELECT DISTINCT log FROM t1

   173       EXCEPT

   174       SELECT n FROM t1 WHERE log=3

   175       ORDER BY log DESC;

   176     SELECT * FROM t2;

   177   }

   178 } {4 3 2 1 0}

   179 execsql {DROP TABLE t2}

   180 ifcapable subquery {

   181   do_test select4-3.2 {

   182     execsql {

   183       SELECT log FROM t1 WHERE n IN 

   184         (SELECT DISTINCT log FROM t1 EXCEPT

   185          SELECT n FROM t1 WHERE log=3)

   186       ORDER BY log;

   187     }

   188   } {0 1 2 2}

   189 }

   190 do_test select4-3.3 {

   191   set v [catch {execsql {

   192     SELECT DISTINCT log FROM t1 ORDER BY log

   193     EXCEPT

   194     SELECT n FROM t1 WHERE log=3

   195     ORDER BY log;

   196   }} msg]

   197   lappend v $msg

   198 } {1 {ORDER BY clause should come after EXCEPT not before}}

   199 

   200 # Intersect operator

   201 #

   202 do_test select4-4.1.1 {

   203   execsql {

   204     SELECT DISTINCT log FROM t1

   205     INTERSECT

   206     SELECT n FROM t1 WHERE log=3

   207     ORDER BY log;

   208   }

   209 } {5}

   210 

   211 do_test select4-4.1.2 {

   212   execsql {

   213     SELECT DISTINCT log FROM t1

   214     UNION ALL

   215     SELECT 6

   216     INTERSECT

   217     SELECT n FROM t1 WHERE log=3

   218     ORDER BY t1.log;

   219   }

   220 } {5 6}

   221 

   222 do_test select4-4.1.3 {

   223   execsql {

   224     CREATE TABLE t2 AS

   225       SELECT DISTINCT log FROM t1 UNION ALL SELECT 6

   226       INTERSECT

   227       SELECT n FROM t1 WHERE log=3

   228       ORDER BY log;

   229     SELECT * FROM t2;

   230   }

   231 } {5 6}

   232 execsql {DROP TABLE t2}

   233 do_test select4-4.1.4 {

   234   execsql {

   235     CREATE TABLE t2 AS

   236       SELECT DISTINCT log FROM t1 UNION ALL SELECT 6

   237       INTERSECT

   238       SELECT n FROM t1 WHERE log=3

   239       ORDER BY log DESC;

   240     SELECT * FROM t2;

   241   }

   242 } {6 5}

   243 execsql {DROP TABLE t2}

   244 ifcapable subquery {

   245   do_test select4-4.2 {

   246     execsql {

   247       SELECT log FROM t1 WHERE n IN 

   248         (SELECT DISTINCT log FROM t1 INTERSECT

   249          SELECT n FROM t1 WHERE log=3)

   250       ORDER BY log;

   251     }

   252   } {3}

   253 }

   254 do_test select4-4.3 {

   255   set v [catch {execsql {

   256     SELECT DISTINCT log FROM t1 ORDER BY log

   257     INTERSECT

   258     SELECT n FROM t1 WHERE log=3

   259     ORDER BY log;

   260   }} msg]

   261   lappend v $msg

   262 } {1 {ORDER BY clause should come after INTERSECT not before}}

   263 

   264 # Various error messages while processing UNION or INTERSECT

   265 #

   266 do_test select4-5.1 {

   267   set v [catch {execsql {

   268     SELECT DISTINCT log FROM t2

   269     UNION ALL

   270     SELECT n FROM t1 WHERE log=3

   271     ORDER BY log;

   272   }} msg]

   273   lappend v $msg

   274 } {1 {no such table: t2}}

   275 do_test select4-5.2 {

   276   set v [catch {execsql {

   277     SELECT DISTINCT log AS "xyzzy" FROM t1

   278     UNION ALL

   279     SELECT n FROM t1 WHERE log=3

   280     ORDER BY xyzzy;

   281   }} msg]

   282   lappend v $msg

   283 } {0 {0 1 2 3 4 5 5 6 7 8}}

   284 do_test select4-5.2b {

   285   set v [catch {execsql {

   286     SELECT DISTINCT log AS xyzzy FROM t1

   287     UNION ALL

   288     SELECT n FROM t1 WHERE log=3

   289     ORDER BY "xyzzy";

   290   }} msg]

   291   lappend v $msg

   292 } {0 {0 1 2 3 4 5 5 6 7 8}}

   293 do_test select4-5.2c {

   294   set v [catch {execsql {

   295     SELECT DISTINCT log FROM t1

   296     UNION ALL

   297     SELECT n FROM t1 WHERE log=3

   298     ORDER BY "xyzzy";

   299   }} msg]

   300   lappend v $msg

   301 } {1 {1st ORDER BY term does not match any column in the result set}}

   302 do_test select4-5.2d {

   303   set v [catch {execsql {

   304     SELECT DISTINCT log FROM t1

   305     INTERSECT

   306     SELECT n FROM t1 WHERE log=3

   307     ORDER BY "xyzzy";

   308   }} msg]

   309   lappend v $msg

   310 } {1 {1st ORDER BY term does not match any column in the result set}}

   311 do_test select4-5.2e {

   312   set v [catch {execsql {

   313     SELECT DISTINCT log FROM t1

   314     UNION ALL

   315     SELECT n FROM t1 WHERE log=3

   316     ORDER BY n;

   317   }} msg]

   318   lappend v $msg

   319 } {0 {0 1 2 3 4 5 5 6 7 8}}

   320 do_test select4-5.2f {

   321   catchsql {

   322     SELECT DISTINCT log FROM t1

   323     UNION ALL

   324     SELECT n FROM t1 WHERE log=3

   325     ORDER BY log;

   326   }

   327 } {0 {0 1 2 3 4 5 5 6 7 8}}

   328 do_test select4-5.2g {

   329   catchsql {

   330     SELECT DISTINCT log FROM t1

   331     UNION ALL

   332     SELECT n FROM t1 WHERE log=3

   333     ORDER BY 1;

   334   }

   335 } {0 {0 1 2 3 4 5 5 6 7 8}}

   336 do_test select4-5.2h {

   337   catchsql {

   338     SELECT DISTINCT log FROM t1

   339     UNION ALL

   340     SELECT n FROM t1 WHERE log=3

   341     ORDER BY 2;

   342   }

   343 } {1 {1st ORDER BY term out of range - should be between 1 and 1}}

   344 do_test select4-5.2i {

   345   catchsql {

   346     SELECT DISTINCT 1, log FROM t1

   347     UNION ALL

   348     SELECT 2, n FROM t1 WHERE log=3

   349     ORDER BY 2, 1;

   350   }

   351 } {0 {1 0 1 1 1 2 1 3 1 4 1 5 2 5 2 6 2 7 2 8}}

   352 do_test select4-5.2j {

   353   catchsql {

   354     SELECT DISTINCT 1, log FROM t1

   355     UNION ALL

   356     SELECT 2, n FROM t1 WHERE log=3

   357     ORDER BY 1, 2 DESC;

   358   }

   359 } {0 {1 5 1 4 1 3 1 2 1 1 1 0 2 8 2 7 2 6 2 5}}

   360 do_test select4-5.2k {

   361   catchsql {

   362     SELECT DISTINCT 1, log FROM t1

   363     UNION ALL

   364     SELECT 2, n FROM t1 WHERE log=3

   365     ORDER BY n, 1;

   366   }

   367 } {0 {1 0 1 1 1 2 1 3 1 4 1 5 2 5 2 6 2 7 2 8}}

   368 do_test select4-5.3 {

   369   set v [catch {execsql {

   370     SELECT DISTINCT log, n FROM t1

   371     UNION ALL

   372     SELECT n FROM t1 WHERE log=3

   373     ORDER BY log;

   374   }} msg]

   375   lappend v $msg

   376 } {1 {SELECTs to the left and right of UNION ALL do not have the same number of result columns}}

   377 do_test select4-5.4 {

   378   set v [catch {execsql {

   379     SELECT log FROM t1 WHERE n=2

   380     UNION ALL

   381     SELECT log FROM t1 WHERE n=3

   382     UNION ALL

   383     SELECT log FROM t1 WHERE n=4

   384     UNION ALL

   385     SELECT log FROM t1 WHERE n=5

   386     ORDER BY log;

   387   }} msg]

   388   lappend v $msg

   389 } {0 {1 2 2 3}}

   390 

   391 do_test select4-6.1 {

   392   execsql {

   393     SELECT log, count(*) as cnt FROM t1 GROUP BY log

   394     UNION

   395     SELECT log, n FROM t1 WHERE n=7

   396     ORDER BY cnt, log;

   397   }

   398 } {0 1 1 1 2 2 3 4 3 7 4 8 5 15}

   399 do_test select4-6.2 {

   400   execsql {

   401     SELECT log, count(*) FROM t1 GROUP BY log

   402     UNION

   403     SELECT log, n FROM t1 WHERE n=7

   404     ORDER BY count(*), log;

   405   }

   406 } {0 1 1 1 2 2 3 4 3 7 4 8 5 15}

   407 

   408 # NULLs are indistinct for the UNION operator.

   409 # Make sure the UNION operator recognizes this

   410 #

   411 do_test select4-6.3 {

   412   execsql {

   413     SELECT NULL UNION SELECT NULL UNION

   414     SELECT 1 UNION SELECT 2 AS 'x'

   415     ORDER BY x;

   416   }

   417 } {{} 1 2}

   418 do_test select4-6.3.1 {

   419   execsql {

   420     SELECT NULL UNION ALL SELECT NULL UNION ALL

   421     SELECT 1 UNION ALL SELECT 2 AS 'x'

   422     ORDER BY x;

   423   }

   424 } {{} {} 1 2}

   425 

   426 # Make sure the DISTINCT keyword treats NULLs as indistinct.

   427 #

   428 ifcapable subquery {

   429   do_test select4-6.4 {

   430     execsql {

   431       SELECT * FROM (

   432          SELECT NULL, 1 UNION ALL SELECT NULL, 1

   433       );

   434     }

   435   } {{} 1 {} 1}

   436   do_test select4-6.5 {

   437     execsql {

   438       SELECT DISTINCT * FROM (

   439          SELECT NULL, 1 UNION ALL SELECT NULL, 1

   440       );

   441     }

   442   } {{} 1}

   443   do_test select4-6.6 {

   444     execsql {

   445       SELECT DISTINCT * FROM (

   446          SELECT 1,2  UNION ALL SELECT 1,2

   447       );

   448     }

   449   } {1 2}

   450 }

   451 

   452 # Test distinctness of NULL in other ways.

   453 #

   454 do_test select4-6.7 {

   455   execsql {

   456     SELECT NULL EXCEPT SELECT NULL

   457   }

   458 } {}

   459 

   460 

   461 # Make sure column names are correct when a compound select appears as

   462 # an expression in the WHERE clause.

   463 #

   464 do_test select4-7.1 {

   465   execsql {

   466     CREATE TABLE t2 AS SELECT log AS 'x', count(*) AS 'y' FROM t1 GROUP BY log;

   467     SELECT * FROM t2 ORDER BY x;

   468   }

   469 } {0 1 1 1 2 2 3 4 4 8 5 15}  

   470 ifcapable subquery {

   471   do_test select4-7.2 {

   472     execsql2 {

   473       SELECT * FROM t1 WHERE n IN (SELECT n FROM t1 INTERSECT SELECT x FROM t2)

   474       ORDER BY n

   475     }

   476   } {n 1 log 0 n 2 log 1 n 3 log 2 n 4 log 2 n 5 log 3}

   477   do_test select4-7.3 {

   478     execsql2 {

   479       SELECT * FROM t1 WHERE n IN (SELECT n FROM t1 EXCEPT SELECT x FROM t2)

   480       ORDER BY n LIMIT 2

   481     }

   482   } {n 6 log 3 n 7 log 3}

   483   do_test select4-7.4 {

   484     execsql2 {

   485       SELECT * FROM t1 WHERE n IN (SELECT n FROM t1 UNION SELECT x FROM t2)

   486       ORDER BY n LIMIT 2

   487     }

   488   } {n 1 log 0 n 2 log 1}

   489 } ;# ifcapable subquery

   490 

   491 } ;# ifcapable compound

   492 

   493 # Make sure DISTINCT works appropriately on TEXT and NUMERIC columns.

   494 do_test select4-8.1 {

   495   execsql {

   496     BEGIN;

   497     CREATE TABLE t3(a text, b float, c text);

   498     INSERT INTO t3 VALUES(1, 1.1, '1.1');

   499     INSERT INTO t3 VALUES(2, 1.10, '1.10');

   500     INSERT INTO t3 VALUES(3, 1.10, '1.1');

   501     INSERT INTO t3 VALUES(4, 1.1, '1.10');

   502     INSERT INTO t3 VALUES(5, 1.2, '1.2');

   503     INSERT INTO t3 VALUES(6, 1.3, '1.3');

   504     COMMIT;

   505   }

   506   execsql {

   507     SELECT DISTINCT b FROM t3 ORDER BY c;

   508   }

   509 } {1.1 1.2 1.3}

   510 do_test select4-8.2 {

   511   execsql {

   512     SELECT DISTINCT c FROM t3 ORDER BY c;

   513   }

   514 } {1.1 1.10 1.2 1.3}

   515 

   516 # Make sure the names of columns are taken from the right-most subquery

   517 # right in a compound query.  Ticket #1721

   518 #

   519 ifcapable compound {

   520 

   521 do_test select4-9.1 {

   522   execsql2 {

   523     SELECT x, y FROM t2 UNION SELECT a, b FROM t3 ORDER BY x LIMIT 1

   524   }

   525 } {x 0 y 1}

   526 do_test select4-9.2 {

   527   execsql2 {

   528     SELECT x, y FROM t2 UNION ALL SELECT a, b FROM t3 ORDER BY x LIMIT 1

   529   }

   530 } {x 0 y 1}

   531 do_test select4-9.3 {

   532   execsql2 {

   533     SELECT x, y FROM t2 EXCEPT SELECT a, b FROM t3 ORDER BY x LIMIT 1

   534   }

   535 } {x 0 y 1}

   536 do_test select4-9.4 {

   537   execsql2 {

   538     SELECT x, y FROM t2 INTERSECT SELECT 0 AS a, 1 AS b;

   539   }

   540 } {x 0 y 1}

   541 do_test select4-9.5 {

   542   execsql2 {

   543     SELECT 0 AS x, 1 AS y

   544     UNION

   545     SELECT 2 AS p, 3 AS q

   546     UNION

   547     SELECT 4 AS a, 5 AS b

   548     ORDER BY x LIMIT 1

   549   }

   550 } {x 0 y 1}

   551 

   552 ifcapable subquery {

   553 do_test select4-9.6 {

   554   execsql2 {

   555     SELECT * FROM (

   556       SELECT 0 AS x, 1 AS y

   557       UNION

   558       SELECT 2 AS p, 3 AS q

   559       UNION

   560       SELECT 4 AS a, 5 AS b

   561     ) ORDER BY 1 LIMIT 1;

   562   }

   563 } {x 0 y 1}

   564 do_test select4-9.7 {

   565   execsql2 {

   566     SELECT * FROM (

   567       SELECT 0 AS x, 1 AS y

   568       UNION

   569       SELECT 2 AS p, 3 AS q

   570       UNION

   571       SELECT 4 AS a, 5 AS b

   572     ) ORDER BY x LIMIT 1;

   573   }

   574 } {x 0 y 1}

   575 } ;# ifcapable subquery

   576 

   577 do_test select4-9.8 {

   578   execsql {

   579     SELECT 0 AS x, 1 AS y

   580     UNION

   581     SELECT 2 AS y, -3 AS x

   582     ORDER BY x LIMIT 1;

   583   }

   584 } {0 1}

   585 

   586 do_test select4-9.9.1 {

   587   execsql2 {

   588     SELECT 1 AS a, 2 AS b UNION ALL SELECT 3 AS b, 4 AS a

   589   }

   590 } {a 1 b 2 a 3 b 4}

   591 

   592 ifcapable subquery {

   593 do_test select4-9.9.2 {

   594   execsql2 {

   595     SELECT * FROM (SELECT 1 AS a, 2 AS b UNION ALL SELECT 3 AS b, 4 AS a)

   596      WHERE b=3

   597   }

   598 } {}

   599 do_test select4-9.10 {

   600   execsql2 {

   601     SELECT * FROM (SELECT 1 AS a, 2 AS b UNION ALL SELECT 3 AS b, 4 AS a)

   602      WHERE b=2

   603   }

   604 } {a 1 b 2}

   605 do_test select4-9.11 {

   606   execsql2 {

   607     SELECT * FROM (SELECT 1 AS a, 2 AS b UNION ALL SELECT 3 AS e, 4 AS b)

   608      WHERE b=2

   609   }

   610 } {a 1 b 2}

   611 do_test select4-9.12 {

   612   execsql2 {

   613     SELECT * FROM (SELECT 1 AS a, 2 AS b UNION ALL SELECT 3 AS e, 4 AS b)

   614      WHERE b>0

   615   }

   616 } {a 1 b 2 a 3 b 4}

   617 } ;# ifcapable subquery

   618 

   619 # Try combining DISTINCT, LIMIT, and OFFSET.  Make sure they all work

   620 # together.

   621 #

   622 do_test select4-10.1 {

   623   execsql {

   624     SELECT DISTINCT log FROM t1 ORDER BY log

   625   }

   626 } {0 1 2 3 4 5}

   627 do_test select4-10.2 {

   628   execsql {

   629     SELECT DISTINCT log FROM t1 ORDER BY log LIMIT 4

   630   }

   631 } {0 1 2 3}

   632 do_test select4-10.3 {

   633   execsql {

   634     SELECT DISTINCT log FROM t1 ORDER BY log LIMIT 0

   635   }

   636 } {}

   637 do_test select4-10.4 {

   638   execsql {

   639     SELECT DISTINCT log FROM t1 ORDER BY log LIMIT -1

   640   }

   641 } {0 1 2 3 4 5}

   642 do_test select4-10.5 {

   643   execsql {

   644     SELECT DISTINCT log FROM t1 ORDER BY log LIMIT -1 OFFSET 2

   645   }

   646 } {2 3 4 5}

   647 do_test select4-10.6 {

   648   execsql {

   649     SELECT DISTINCT log FROM t1 ORDER BY log LIMIT 3 OFFSET 2

   650   }

   651 } {2 3 4}

   652 do_test select4-10.7 {

   653   execsql {

   654     SELECT DISTINCT log FROM t1 ORDER BY +log LIMIT 3 OFFSET 20

   655   }

   656 } {}

   657 do_test select4-10.8 {

   658   execsql {

   659     SELECT DISTINCT log FROM t1 ORDER BY log LIMIT 0 OFFSET 3

   660   }

   661 } {}

   662 do_test select4-10.9 {

   663   execsql {

   664     SELECT DISTINCT max(n), log FROM t1 ORDER BY +log; -- LIMIT 2 OFFSET 1

   665   }

   666 } {31 5}

   667 

   668 # Make sure compound SELECTs with wildly different numbers of columns

   669 # do not cause assertion faults due to register allocation issues.

   670 #

   671 do_test select4-11.1 {

   672   catchsql {

   673     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

   674     UNION

   675     SELECT x FROM t2

   676   }

   677 } {1 {SELECTs to the left and right of UNION do not have the same number of result columns}}

   678 do_test select4-11.2 {

   679   catchsql {

   680     SELECT x FROM t2

   681     UNION

   682     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

   683   }

   684 } {1 {SELECTs to the left and right of UNION do not have the same number of result columns}}

   685 do_test select4-11.3 {

   686   catchsql {

   687     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

   688     UNION ALL

   689     SELECT x FROM t2

   690   }

   691 } {1 {SELECTs to the left and right of UNION ALL do not have the same number of result columns}}

   692 do_test select4-11.4 {

   693   catchsql {

   694     SELECT x FROM t2

   695     UNION ALL

   696     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

   697   }

   698 } {1 {SELECTs to the left and right of UNION ALL do not have the same number of result columns}}

   699 do_test select4-11.5 {

   700   catchsql {

   701     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

   702     EXCEPT

   703     SELECT x FROM t2

   704   }

   705 } {1 {SELECTs to the left and right of EXCEPT do not have the same number of result columns}}

   706 do_test select4-11.6 {

   707   catchsql {

   708     SELECT x FROM t2

   709     EXCEPT

   710     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

   711   }

   712 } {1 {SELECTs to the left and right of EXCEPT do not have the same number of result columns}}

   713 do_test select4-11.7 {

   714   catchsql {

   715     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

   716     INTERSECT

   717     SELECT x FROM t2

   718   }

   719 } {1 {SELECTs to the left and right of INTERSECT do not have the same number of result columns}}

   720 do_test select4-11.8 {

   721   catchsql {

   722     SELECT x FROM t2

   723     INTERSECT

   724     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

   725   }

   726 } {1 {SELECTs to the left and right of INTERSECT do not have the same number of result columns}}

   727 

   728 do_test select4-11.11 {

   729   catchsql {

   730     SELECT x FROM t2

   731     UNION

   732     SELECT x FROM t2

   733     UNION ALL

   734     SELECT x FROM t2

   735     EXCEPT

   736     SELECT x FROM t2

   737     INTERSECT

   738     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

   739   }

   740 } {1 {SELECTs to the left and right of INTERSECT do not have the same number of result columns}}

   741 do_test select4-11.12 {

   742   catchsql {

   743     SELECT x FROM t2

   744     UNION

   745     SELECT x FROM t2

   746     UNION ALL

   747     SELECT x FROM t2

   748     EXCEPT

   749     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

   750     EXCEPT

   751     SELECT x FROM t2

   752   }

   753 } {1 {SELECTs to the left and right of EXCEPT do not have the same number of result columns}}

   754 do_test select4-11.13 {

   755   catchsql {

   756     SELECT x FROM t2

   757     UNION

   758     SELECT x FROM t2

   759     UNION ALL

   760     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

   761     UNION ALL

   762     SELECT x FROM t2

   763     EXCEPT

   764     SELECT x FROM t2

   765   }

   766 } {1 {SELECTs to the left and right of UNION ALL do not have the same number of result columns}}

   767 do_test select4-11.14 {

   768   catchsql {

   769     SELECT x FROM t2

   770     UNION

   771     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

   772     UNION

   773     SELECT x FROM t2

   774     UNION ALL

   775     SELECT x FROM t2

   776     EXCEPT

   777     SELECT x FROM t2

   778   }

   779 } {1 {SELECTs to the left and right of UNION do not have the same number of result columns}}

   780 do_test select4-11.15 {

   781   catchsql {

   782     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

   783     UNION

   784     SELECT x FROM t2

   785     INTERSECT

   786     SELECT x FROM t2

   787     UNION ALL

   788     SELECT x FROM t2

   789     EXCEPT

   790     SELECT x FROM t2

   791   }

   792 } {1 {SELECTs to the left and right of UNION do not have the same number of result columns}}

   793 

   794 } ;# ifcapable compound

   795 

   796 finish_test