1 # 2008 Feb 6

     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 #

    12 # This file is to test that ticket #2927 is fixed.

    13 #

    14 # $Id: tkt2927.test,v 1.4 2008/08/04 03:51:24 danielk1977 Exp $

    15 #

    16 

    17 set testdir [file dirname $argv0]

    18 source $testdir/tester.tcl

    19 

    20 ifcapable !compound {

    21   finish_test

    22   return

    23 }

    24 

    25 # Create a database.

    26 #

    27 do_test tkt2927-1.1 {

    28   db eval {

    29     CREATE TABLE t1(a, b);

    30     INSERT INTO t1 VALUES(1,11);

    31     INSERT INTO t1 VALUES(2,22);

    32     INSERT INTO t1 VALUES(3,33);

    33     INSERT INTO t1 VALUES(4,44);

    34     INSERT INTO t1 VALUES(5,55);

    35     SELECT * FROM t1;

    36   }

    37 } {1 11 2 22 3 33 4 44 5 55}

    38 

    39 

    40 do_test tkt2927-2.1 {

    41   db eval {

    42     SELECT a, b FROM t1

    43     UNION ALL

    44     SELECT a, b FROM t1

    45   }

    46 } {1 11 2 22 3 33 4 44 5 55 1 11 2 22 3 33 4 44 5 55}

    47 do_test tkt2927-2.2 {

    48 #set sqlite_addop_trace 1

    49   db eval {

    50     SELECT a, b FROM t1

    51     UNION ALL

    52     SELECT a, abs(b) FROM t1

    53   }

    54 } {1 11 2 22 3 33 4 44 5 55 1 11 2 22 3 33 4 44 5 55}

    55 do_test tkt2927-2.3 {

    56   db eval {

    57     SELECT a, b FROM t1

    58     UNION ALL

    59     SELECT abs(a), b FROM t1

    60   }

    61 } {1 11 2 22 3 33 4 44 5 55 1 11 2 22 3 33 4 44 5 55}

    62 do_test tkt2927-2.4 {

    63   db eval {

    64     SELECT a, b FROM t1

    65     UNION ALL

    66     SELECT abs(a), abs(b) FROM t1

    67   }

    68 } {1 11 2 22 3 33 4 44 5 55 1 11 2 22 3 33 4 44 5 55}

    69 do_test tkt2927-2.5 {

    70   db eval {

    71     SELECT a, abs(b) FROM t1

    72     UNION ALL

    73     SELECT a, b FROM t1

    74   }

    75 } {1 11 2 22 3 33 4 44 5 55 1 11 2 22 3 33 4 44 5 55}

    76 do_test tkt2927-2.6 {

    77   db eval {

    78     SELECT a, abs(b) FROM t1

    79     UNION ALL

    80     SELECT a, abs(b) FROM t1

    81   }

    82 } {1 11 2 22 3 33 4 44 5 55 1 11 2 22 3 33 4 44 5 55}

    83 do_test tkt2927-2.7 {

    84   db eval {

    85     SELECT a, abs(b) FROM t1

    86     UNION ALL

    87     SELECT abs(a), b FROM t1

    88   }

    89 } {1 11 2 22 3 33 4 44 5 55 1 11 2 22 3 33 4 44 5 55}

    90 do_test tkt2927-2.8 {

    91   db eval {

    92     SELECT a, abs(b) FROM t1

    93     UNION ALL

    94     SELECT abs(a), abs(b) FROM t1

    95   }

    96 } {1 11 2 22 3 33 4 44 5 55 1 11 2 22 3 33 4 44 5 55}

    97 do_test tkt2927-2.9 {

    98   db eval {

    99     SELECT abs(a), b FROM t1

   100     UNION ALL

   101     SELECT a, b FROM t1

   102   }

   103 } {1 11 2 22 3 33 4 44 5 55 1 11 2 22 3 33 4 44 5 55}

   104 do_test tkt2927-2.10 {

   105   db eval {

   106     SELECT abs(a), b FROM t1

   107     UNION ALL

   108     SELECT a, abs(b) FROM t1

   109   }

   110 } {1 11 2 22 3 33 4 44 5 55 1 11 2 22 3 33 4 44 5 55}

   111 do_test tkt2927-2.11 {

   112   db eval {

   113     SELECT abs(a), b FROM t1

   114     UNION ALL

   115     SELECT abs(a), b FROM t1

   116   }

   117 } {1 11 2 22 3 33 4 44 5 55 1 11 2 22 3 33 4 44 5 55}

   118 do_test tkt2927-2.12 {

   119   db eval {

   120     SELECT abs(a), b FROM t1

   121     UNION ALL

   122     SELECT abs(a), abs(b) FROM t1

   123   }

   124 } {1 11 2 22 3 33 4 44 5 55 1 11 2 22 3 33 4 44 5 55}

   125 do_test tkt2927-2.13 {

   126   db eval {

   127     SELECT abs(a), abs(b) FROM t1

   128     UNION ALL

   129     SELECT a, b FROM t1

   130   }

   131 } {1 11 2 22 3 33 4 44 5 55 1 11 2 22 3 33 4 44 5 55}

   132 do_test tkt2927-2.14 {

   133   db eval {

   134     SELECT abs(a), abs(b) FROM t1

   135     UNION ALL

   136     SELECT a, abs(b) FROM t1

   137   }

   138 } {1 11 2 22 3 33 4 44 5 55 1 11 2 22 3 33 4 44 5 55}

   139 do_test tkt2927-2.15 {

   140   db eval {

   141     SELECT abs(a), abs(b) FROM t1

   142     UNION ALL

   143     SELECT abs(a), b FROM t1

   144   }

   145 } {1 11 2 22 3 33 4 44 5 55 1 11 2 22 3 33 4 44 5 55}

   146 do_test tkt2927-2.16 {

   147   db eval {

   148     SELECT abs(a), abs(b) FROM t1

   149     UNION ALL

   150     SELECT abs(a), abs(b) FROM t1

   151   }

   152 } {1 11 2 22 3 33 4 44 5 55 1 11 2 22 3 33 4 44 5 55}

   153 

   154 

   155 do_test tkt2927-3.1 {

   156   db eval {

   157     SELECT a, b FROM t1

   158     UNION 

   159     SELECT a, b FROM t1

   160     ORDER BY 1

   161   }

   162 } {1 11 2 22 3 33 4 44 5 55}

   163 do_test tkt2927-3.2 {

   164   db eval {

   165     SELECT a, b FROM t1

   166     UNION 

   167     SELECT a, abs(b) FROM t1

   168     ORDER BY 1

   169   }

   170 } {1 11 2 22 3 33 4 44 5 55}

   171 do_test tkt2927-3.3 {

   172   db eval {

   173     SELECT a, b FROM t1

   174     UNION 

   175     SELECT abs(a), b FROM t1

   176     ORDER BY 1

   177   }

   178 } {1 11 2 22 3 33 4 44 5 55}

   179 do_test tkt2927-3.4 {

   180   db eval {

   181     SELECT a, b FROM t1

   182     UNION 

   183     SELECT abs(a), abs(b) FROM t1

   184     ORDER BY 1

   185   }

   186 } {1 11 2 22 3 33 4 44 5 55}

   187 do_test tkt2927-3.5 {

   188   db eval {

   189     SELECT a, abs(b) FROM t1

   190     UNION 

   191     SELECT a, b FROM t1

   192     ORDER BY 1

   193   }

   194 } {1 11 2 22 3 33 4 44 5 55}

   195 do_test tkt2927-3.6 {

   196   db eval {

   197     SELECT a, abs(b) FROM t1

   198     UNION 

   199     SELECT a, abs(b) FROM t1

   200     ORDER BY 1

   201   }

   202 } {1 11 2 22 3 33 4 44 5 55}

   203 do_test tkt2927-3.7 {

   204   db eval {

   205     SELECT a, abs(b) FROM t1

   206     UNION 

   207     SELECT abs(a), b FROM t1

   208     ORDER BY 1

   209   }

   210 } {1 11 2 22 3 33 4 44 5 55}

   211 do_test tkt2927-3.8 {

   212   db eval {

   213     SELECT a, abs(b) FROM t1

   214     UNION 

   215     SELECT abs(a), abs(b) FROM t1

   216     ORDER BY 1

   217   }

   218 } {1 11 2 22 3 33 4 44 5 55}

   219 do_test tkt2927-3.9 {

   220   db eval {

   221     SELECT abs(a), b FROM t1

   222     UNION 

   223     SELECT a, b FROM t1

   224     ORDER BY 1

   225   }

   226 } {1 11 2 22 3 33 4 44 5 55}

   227 do_test tkt2927-3.10 {

   228   db eval {

   229     SELECT abs(a), b FROM t1

   230     UNION 

   231     SELECT a, abs(b) FROM t1

   232     ORDER BY 1

   233   }

   234 } {1 11 2 22 3 33 4 44 5 55}

   235 do_test tkt2927-3.11 {

   236   db eval {

   237     SELECT abs(a), b FROM t1

   238     UNION 

   239     SELECT abs(a), b FROM t1

   240     ORDER BY 1

   241   }

   242 } {1 11 2 22 3 33 4 44 5 55}

   243 do_test tkt2927-3.12 {

   244   db eval {

   245     SELECT abs(a), b FROM t1

   246     UNION 

   247     SELECT abs(a), abs(b) FROM t1

   248     ORDER BY 1

   249   }

   250 } {1 11 2 22 3 33 4 44 5 55}

   251 do_test tkt2927-3.13 {

   252   db eval {

   253     SELECT abs(a), abs(b) FROM t1

   254     UNION 

   255     SELECT a, b FROM t1

   256     ORDER BY 1

   257   }

   258 } {1 11 2 22 3 33 4 44 5 55}

   259 do_test tkt2927-3.14 {

   260   db eval {

   261     SELECT abs(a), abs(b) FROM t1

   262     UNION 

   263     SELECT a, abs(b) FROM t1

   264     ORDER BY 1

   265   }

   266 } {1 11 2 22 3 33 4 44 5 55}

   267 do_test tkt2927-3.15 {

   268   db eval {

   269     SELECT abs(a), abs(b) FROM t1

   270     UNION 

   271     SELECT abs(a), b FROM t1

   272     ORDER BY 1

   273   }

   274 } {1 11 2 22 3 33 4 44 5 55}

   275 do_test tkt2927-3.16 {

   276   db eval {

   277     SELECT abs(a), abs(b) FROM t1

   278     UNION 

   279     SELECT abs(a), abs(b) FROM t1

   280     ORDER BY 1

   281   }

   282 } {1 11 2 22 3 33 4 44 5 55}

   283 

   284 

   285 do_test tkt2927-4.1 {

   286   db eval {

   287     SELECT a+b, a-b, a, b FROM t1

   288     UNION ALL

   289     SELECT a+b, a-b, a, b FROM t1

   290   }

   291 } {12 -10 1 11 24 -20 2 22 36 -30 3 33 48 -40 4 44 60 -50 5 55 12 -10 1 11 24 -20 2 22 36 -30 3 33 48 -40 4 44 60 -50 5 55}

   292 do_test tkt2927-4.2 {

   293   db eval {

   294     SELECT a+b, a-b, a, b FROM t1

   295     UNION ALL

   296     SELECT a+b, a-b, a, abs(b) FROM t1

   297   }

   298 } {12 -10 1 11 24 -20 2 22 36 -30 3 33 48 -40 4 44 60 -50 5 55 12 -10 1 11 24 -20 2 22 36 -30 3 33 48 -40 4 44 60 -50 5 55}

   299 do_test tkt2927-4.3 {

   300   db eval {

   301     SELECT a+b, a-b, a, b FROM t1

   302     UNION ALL

   303     SELECT a+b, a-b, abs(a), b FROM t1

   304   }

   305 } {12 -10 1 11 24 -20 2 22 36 -30 3 33 48 -40 4 44 60 -50 5 55 12 -10 1 11 24 -20 2 22 36 -30 3 33 48 -40 4 44 60 -50 5 55}

   306 do_test tkt2927-4.4 {

   307   db eval {

   308     SELECT a+b, a-b, a, b FROM t1

   309     UNION ALL

   310     SELECT a+b, a-b, abs(a), abs(b) FROM t1

   311   }

   312 } {12 -10 1 11 24 -20 2 22 36 -30 3 33 48 -40 4 44 60 -50 5 55 12 -10 1 11 24 -20 2 22 36 -30 3 33 48 -40 4 44 60 -50 5 55}

   313 do_test tkt2927-4.5 {

   314   db eval {

   315     SELECT a+b, a-b, a, abs(b) FROM t1

   316     UNION ALL

   317     SELECT a+b, a-b, a, b FROM t1

   318   }

   319 } {12 -10 1 11 24 -20 2 22 36 -30 3 33 48 -40 4 44 60 -50 5 55 12 -10 1 11 24 -20 2 22 36 -30 3 33 48 -40 4 44 60 -50 5 55}

   320 do_test tkt2927-4.6 {

   321   db eval {

   322     SELECT a+b, a-b, a, abs(b) FROM t1

   323     UNION ALL

   324     SELECT a+b, a-b, a, abs(b) FROM t1

   325   }

   326 } {12 -10 1 11 24 -20 2 22 36 -30 3 33 48 -40 4 44 60 -50 5 55 12 -10 1 11 24 -20 2 22 36 -30 3 33 48 -40 4 44 60 -50 5 55}

   327 do_test tkt2927-4.7 {

   328   db eval {

   329     SELECT a+b, a-b, a, abs(b) FROM t1

   330     UNION ALL

   331     SELECT a+b, a-b, abs(a), b FROM t1

   332   }

   333 } {12 -10 1 11 24 -20 2 22 36 -30 3 33 48 -40 4 44 60 -50 5 55 12 -10 1 11 24 -20 2 22 36 -30 3 33 48 -40 4 44 60 -50 5 55}

   334 do_test tkt2927-4.8 {

   335   db eval {

   336     SELECT a+b, a-b, a, abs(b) FROM t1

   337     UNION ALL

   338     SELECT a+b, a-b, abs(a), abs(b) FROM t1

   339   }

   340 } {12 -10 1 11 24 -20 2 22 36 -30 3 33 48 -40 4 44 60 -50 5 55 12 -10 1 11 24 -20 2 22 36 -30 3 33 48 -40 4 44 60 -50 5 55}

   341 do_test tkt2927-4.9 {

   342   db eval {

   343     SELECT a+b, a-b, abs(a), b FROM t1

   344     UNION ALL

   345     SELECT a+b, a-b, a, b FROM t1

   346   }

   347 } {12 -10 1 11 24 -20 2 22 36 -30 3 33 48 -40 4 44 60 -50 5 55 12 -10 1 11 24 -20 2 22 36 -30 3 33 48 -40 4 44 60 -50 5 55}

   348 do_test tkt2927-4.10 {

   349   db eval {

   350     SELECT a+b, a-b, abs(a), b FROM t1

   351     UNION ALL

   352     SELECT a+b, a-b, a, abs(b) FROM t1

   353   }

   354 } {12 -10 1 11 24 -20 2 22 36 -30 3 33 48 -40 4 44 60 -50 5 55 12 -10 1 11 24 -20 2 22 36 -30 3 33 48 -40 4 44 60 -50 5 55}

   355 do_test tkt2927-4.11 {

   356   db eval {

   357     SELECT a+b, a-b, abs(a), b FROM t1

   358     UNION ALL

   359     SELECT a+b, a-b, abs(a), b FROM t1

   360   }

   361 } {12 -10 1 11 24 -20 2 22 36 -30 3 33 48 -40 4 44 60 -50 5 55 12 -10 1 11 24 -20 2 22 36 -30 3 33 48 -40 4 44 60 -50 5 55}

   362 do_test tkt2927-4.12 {

   363   db eval {

   364     SELECT a+b, a-b, abs(a), b FROM t1

   365     UNION ALL

   366     SELECT a+b, a-b, abs(a), abs(b) FROM t1

   367   }

   368 } {12 -10 1 11 24 -20 2 22 36 -30 3 33 48 -40 4 44 60 -50 5 55 12 -10 1 11 24 -20 2 22 36 -30 3 33 48 -40 4 44 60 -50 5 55}

   369 do_test tkt2927-4.13 {

   370   db eval {

   371     SELECT a+b, a-b, abs(a), abs(b) FROM t1

   372     UNION ALL

   373     SELECT a+b, a-b, a, b FROM t1

   374   }

   375 } {12 -10 1 11 24 -20 2 22 36 -30 3 33 48 -40 4 44 60 -50 5 55 12 -10 1 11 24 -20 2 22 36 -30 3 33 48 -40 4 44 60 -50 5 55}

   376 do_test tkt2927-4.14 {

   377   db eval {

   378     SELECT a+b, a-b, abs(a), abs(b) FROM t1

   379     UNION ALL

   380     SELECT a+b, a-b, a, abs(b) FROM t1

   381   }

   382 } {12 -10 1 11 24 -20 2 22 36 -30 3 33 48 -40 4 44 60 -50 5 55 12 -10 1 11 24 -20 2 22 36 -30 3 33 48 -40 4 44 60 -50 5 55}

   383 do_test tkt2927-4.15 {

   384   db eval {

   385     SELECT a+b, a-b, abs(a), abs(b) FROM t1

   386     UNION ALL

   387     SELECT a+b, a-b, abs(a), b FROM t1

   388   }

   389 } {12 -10 1 11 24 -20 2 22 36 -30 3 33 48 -40 4 44 60 -50 5 55 12 -10 1 11 24 -20 2 22 36 -30 3 33 48 -40 4 44 60 -50 5 55}

   390 do_test tkt2927-4.16 {

   391   db eval {

   392     SELECT a+b, a-b, abs(a), abs(b) FROM t1

   393     UNION ALL

   394     SELECT a+b, a-b, abs(a), abs(b) FROM t1

   395   }

   396 } {12 -10 1 11 24 -20 2 22 36 -30 3 33 48 -40 4 44 60 -50 5 55 12 -10 1 11 24 -20 2 22 36 -30 3 33 48 -40 4 44 60 -50 5 55}

   397 

   398 

   399 do_test tkt2927-5.1 {

   400   db eval {

   401     SELECT a, b FROM t1

   402     EXCEPT

   403     SELECT a, b FROM t1

   404   }

   405 } {}

   406 do_test tkt2927-5.2 {

   407   db eval {

   408     SELECT a, b FROM t1

   409     EXCEPT

   410     SELECT a, abs(b) FROM t1

   411   }

   412 } {}

   413 do_test tkt2927-5.3 {

   414   db eval {

   415     SELECT a, b FROM t1

   416     EXCEPT

   417     SELECT abs(a), b FROM t1

   418   }

   419 } {}

   420 do_test tkt2927-5.4 {

   421   db eval {

   422     SELECT a, b FROM t1

   423     EXCEPT

   424     SELECT abs(a), abs(b) FROM t1

   425   }

   426 } {}

   427 do_test tkt2927-5.5 {

   428   db eval {

   429     SELECT a, abs(b) FROM t1

   430     EXCEPT

   431     SELECT a, b FROM t1

   432   }

   433 } {}

   434 do_test tkt2927-5.6 {

   435   db eval {

   436     SELECT a, abs(b) FROM t1

   437     EXCEPT

   438     SELECT a, abs(b) FROM t1

   439   }

   440 } {}

   441 do_test tkt2927-5.7 {

   442   db eval {

   443     SELECT a, abs(b) FROM t1

   444     EXCEPT

   445     SELECT abs(a), b FROM t1

   446   }

   447 } {}

   448 do_test tkt2927-5.8 {

   449   db eval {

   450     SELECT a, abs(b) FROM t1

   451     EXCEPT

   452     SELECT abs(a), abs(b) FROM t1

   453   }

   454 } {}

   455 do_test tkt2927-5.9 {

   456   db eval {

   457     SELECT abs(a), b FROM t1

   458     EXCEPT

   459     SELECT a, b FROM t1

   460   }

   461 } {}

   462 do_test tkt2927-5.10 {

   463   db eval {

   464     SELECT abs(a), b FROM t1

   465     EXCEPT

   466     SELECT a, abs(b) FROM t1

   467   }

   468 } {}

   469 do_test tkt2927-5.11 {

   470   db eval {

   471     SELECT abs(a), b FROM t1

   472     EXCEPT

   473     SELECT abs(a), b FROM t1

   474   }

   475 } {}

   476 do_test tkt2927-5.12 {

   477   db eval {

   478     SELECT abs(a), b FROM t1

   479     EXCEPT

   480     SELECT abs(a), abs(b) FROM t1

   481   }

   482 } {}

   483 do_test tkt2927-5.13 {

   484   db eval {

   485     SELECT abs(a), abs(b) FROM t1

   486     EXCEPT

   487     SELECT a, b FROM t1

   488   }

   489 } {}

   490 do_test tkt2927-5.14 {

   491   db eval {

   492     SELECT abs(a), abs(b) FROM t1

   493     EXCEPT

   494     SELECT a, abs(b) FROM t1

   495   }

   496 } {}

   497 do_test tkt2927-5.15 {

   498   db eval {

   499     SELECT abs(a), abs(b) FROM t1

   500     EXCEPT

   501     SELECT abs(a), b FROM t1

   502   }

   503 } {}

   504 do_test tkt2927-5.16 {

   505   db eval {

   506     SELECT abs(a), abs(b) FROM t1

   507     EXCEPT

   508     SELECT abs(a), abs(b) FROM t1

   509   }

   510 } {}

   511 

   512 

   513 do_test tkt2927-6.1 {

   514   db eval {

   515     SELECT a, b FROM t1

   516     INTERSECT

   517     SELECT a, b FROM t1

   518     ORDER BY 1

   519   }

   520 } {1 11 2 22 3 33 4 44 5 55}

   521 do_test tkt2927-6.2 {

   522   db eval {

   523     SELECT a, b FROM t1

   524     INTERSECT

   525     SELECT a, abs(b) FROM t1

   526     ORDER BY 1

   527   }

   528 } {1 11 2 22 3 33 4 44 5 55}

   529 do_test tkt2927-6.3 {

   530   db eval {

   531     SELECT a, b FROM t1

   532     INTERSECT

   533     SELECT abs(a), b FROM t1

   534     ORDER BY 1

   535   }

   536 } {1 11 2 22 3 33 4 44 5 55}

   537 do_test tkt2927-6.4 {

   538   db eval {

   539     SELECT a, b FROM t1

   540     INTERSECT

   541     SELECT abs(a), abs(b) FROM t1

   542     ORDER BY 1

   543   }

   544 } {1 11 2 22 3 33 4 44 5 55}

   545 do_test tkt2927-6.5 {

   546   db eval {

   547     SELECT a, abs(b) FROM t1

   548     INTERSECT

   549     SELECT a, b FROM t1

   550     ORDER BY 1

   551   }

   552 } {1 11 2 22 3 33 4 44 5 55}

   553 do_test tkt2927-6.6 {

   554   db eval {

   555     SELECT a, abs(b) FROM t1

   556     INTERSECT

   557     SELECT a, abs(b) FROM t1

   558     ORDER BY 1

   559   }

   560 } {1 11 2 22 3 33 4 44 5 55}

   561 do_test tkt2927-6.7 {

   562   db eval {

   563     SELECT a, abs(b) FROM t1

   564     INTERSECT

   565     SELECT abs(a), b FROM t1

   566     ORDER BY 1

   567   }

   568 } {1 11 2 22 3 33 4 44 5 55}

   569 do_test tkt2927-6.8 {

   570   db eval {

   571     SELECT a, abs(b) FROM t1

   572     INTERSECT

   573     SELECT abs(a), abs(b) FROM t1

   574     ORDER BY 1

   575   }

   576 } {1 11 2 22 3 33 4 44 5 55}

   577 do_test tkt2927-6.9 {

   578   db eval {

   579     SELECT abs(a), b FROM t1

   580     INTERSECT

   581     SELECT a, b FROM t1

   582     ORDER BY 1

   583   }

   584 } {1 11 2 22 3 33 4 44 5 55}

   585 do_test tkt2927-6.10 {

   586   db eval {

   587     SELECT abs(a), b FROM t1

   588     INTERSECT

   589     SELECT a, abs(b) FROM t1

   590     ORDER BY 1

   591   }

   592 } {1 11 2 22 3 33 4 44 5 55}

   593 do_test tkt2927-6.11 {

   594   db eval {

   595     SELECT abs(a), b FROM t1

   596     INTERSECT

   597     SELECT abs(a), b FROM t1

   598     ORDER BY 1

   599   }

   600 } {1 11 2 22 3 33 4 44 5 55}

   601 do_test tkt2927-6.12 {

   602   db eval {

   603     SELECT abs(a), b FROM t1

   604     INTERSECT

   605     SELECT abs(a), abs(b) FROM t1

   606     ORDER BY 1

   607   }

   608 } {1 11 2 22 3 33 4 44 5 55}

   609 do_test tkt2927-6.13 {

   610   db eval {

   611     SELECT abs(a), abs(b) FROM t1

   612     INTERSECT

   613     SELECT a, b FROM t1

   614     ORDER BY 1

   615   }

   616 } {1 11 2 22 3 33 4 44 5 55}

   617 do_test tkt2927-6.14 {

   618   db eval {

   619     SELECT abs(a), abs(b) FROM t1

   620     INTERSECT

   621     SELECT a, abs(b) FROM t1

   622     ORDER BY 1

   623   }

   624 } {1 11 2 22 3 33 4 44 5 55}

   625 do_test tkt2927-6.15 {

   626   db eval {

   627     SELECT abs(a), abs(b) FROM t1

   628     INTERSECT

   629     SELECT abs(a), b FROM t1

   630     ORDER BY 1

   631   }

   632 } {1 11 2 22 3 33 4 44 5 55}

   633 do_test tkt2927-6.16 {

   634   db eval {

   635     SELECT abs(a), abs(b) FROM t1

   636     INTERSECT

   637     SELECT abs(a), abs(b) FROM t1

   638     ORDER BY 1

   639   }

   640 } {1 11 2 22 3 33 4 44 5 55}

   641 

   642 # Ticket #3092 is the same bug.  But another test case never hurts.

   643 #

   644 do_test tkt2927-7.1 {

   645   db eval {

   646     CREATE TABLE host (

   647      hostname text not null primary key,

   648      consoleHost text,

   649      consolePort text

   650     );

   651     INSERT INTO "host" VALUES('aald04','aalp03','4');

   652     INSERT INTO "host" VALUES('aald17','aalp01','1');

   653     CREATE VIEW consolemap1a as

   654       select hostname, consolehost, '/dev/cuaD0.' || (consoleport-1) consoleport

   655         from host where consolehost='aalp01';

   656     CREATE VIEW consolemap1b as

   657       select hostname hostname, consolehost consolehost, '/dev/cuaD' ||

   658              substr('01',1+((consoleport-1)/16),1) ||

   659              substr('0123456789abcdef',1+((consoleport-1)%16),1) consoleport

   660         from host where consolehost='aalp03';

   661     CREATE VIEW consolemap1 as

   662       select * from consolemap1a

   663       union

   664       select * from consolemap1b;

   665     SELECT * from consolemap1b;

   666   }

   667 } {aald04 aalp03 /dev/cuaD03}

   668 do_test tkt2927-7.2 {

   669   db eval {

   670     SELECT * FROM consolemap1

   671   }

   672 } {aald04 aalp03 /dev/cuaD03 aald17 aalp01 /dev/cuaD0.0}

   673 

   674 finish_test