test/subquery.test

   1 # 2005 January 19
   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 script is testing correlated subqueries
  13 #
  14
  15 set testdir [file dirname $argv0]
  16 source $testdir/tester.tcl
  17
  18 ifcapable !subquery {
  19   finish_test
  20   return
  21 }
  22
  23 do_test subquery-1.1 {
  24   execsql {
  25     BEGIN;
  26     CREATE TABLE t1(a,b);
  27     INSERT INTO t1 VALUES(1,2);
  28     INSERT INTO t1 VALUES(3,4);
  29     INSERT INTO t1 VALUES(5,6);
  30     INSERT INTO t1 VALUES(7,8);
  31     CREATE TABLE t2(x,y);
  32     INSERT INTO t2 VALUES(1,1);
  33     INSERT INTO t2 VALUES(3,9);
  34     INSERT INTO t2 VALUES(5,25);
  35     INSERT INTO t2 VALUES(7,49);
  36     COMMIT;
  37   }
  38   execsql {
  39     SELECT a, (SELECT y FROM t2 WHERE x=a) FROM t1 WHERE b<8
  40   }
  41 } {1 1 3 9 5 25}
  42 do_test subquery-1.2 {
  43   execsql {
  44     UPDATE t1 SET b=b+(SELECT y FROM t2 WHERE x=a);
  45     SELECT * FROM t1;
  46   }
  47 } {1 3 3 13 5 31 7 57}
  48
  49 do_test subquery-1.3 {
  50   execsql {
  51     SELECT b FROM t1 WHERE EXISTS(SELECT * FROM t2 WHERE y=a)
  52   }
  53 } {3}
  54 do_test subquery-1.4 {
  55   execsql {
  56     SELECT b FROM t1 WHERE NOT EXISTS(SELECT * FROM t2 WHERE y=a)
  57   }
  58 } {13 31 57}
  59
  60 # Simple tests to make sure correlated subqueries in WHERE clauses
  61 # are used by the query optimizer correctly.
  62 do_test subquery-1.5 {
  63   execsql {
  64     SELECT a, x FROM t1, t2 WHERE t1.a = (SELECT x);
  65   }
  66 } {1 1 3 3 5 5 7 7}
  67 do_test subquery-1.6 {
  68   execsql {
  69     CREATE INDEX i1 ON t1(a);
  70     SELECT a, x FROM t1, t2 WHERE t1.a = (SELECT x);
  71   }
  72 } {1 1 3 3 5 5 7 7}
  73 do_test subquery-1.7 {
  74   execsql {
  75     SELECT a, x FROM t2, t1 WHERE t1.a = (SELECT x);
  76   }
  77 } {1 1 3 3 5 5 7 7}
  78
  79 # Try an aggregate in both the subquery and the parent query.
  80 do_test subquery-1.8 {
  81   execsql {
  82     SELECT count(*) FROM t1 WHERE a > (SELECT count(*) FROM t2);
  83   }
  84 } {2}
  85
  86 # Test a correlated subquery disables the "only open the index" optimization.
  87 do_test subquery-1.9.1 {
  88   execsql {
  89     SELECT (y*2)>b FROM t1, t2 WHERE a=x;
  90   }
  91 } {0 1 1 1}
  92 do_test subquery-1.9.2 {
  93   execsql {
  94     SELECT a FROM t1 WHERE (SELECT (y*2)>b FROM t2 WHERE a=x);
  95   }
  96 } {3 5 7}
  97
  98 # Test that the flattening optimization works with subquery expressions.
  99 do_test subquery-1.10.1 {
 100   execsql {
 101     SELECT (SELECT a), b FROM t1;
 102   }
 103 } {1 3 3 13 5 31 7 57}
 104 do_test subquery-1.10.2 {
 105   execsql {
 106     SELECT * FROM (SELECT (SELECT a), b FROM t1);
 107   }
 108 } {1 3 3 13 5 31 7 57}
 109 do_test subquery-1.10.3 {
 110   execsql {
 111     SELECT * FROM (SELECT (SELECT sum(a) FROM t1));
 112   }
 113 } {16}
 114 do_test subquery-1.10.4 {
 115   execsql {
 116     CREATE TABLE t5 (val int, period text PRIMARY KEY);
 117     INSERT INTO t5 VALUES(5, '2001-3');
 118     INSERT INTO t5 VALUES(10, '2001-4');
 119     INSERT INTO t5 VALUES(15, '2002-1');
 120     INSERT INTO t5 VALUES(5, '2002-2');
 121     INSERT INTO t5 VALUES(10, '2002-3');
 122     INSERT INTO t5 VALUES(15, '2002-4');
 123     INSERT INTO t5 VALUES(10, '2003-1');
 124     INSERT INTO t5 VALUES(5, '2003-2');
 125     INSERT INTO t5 VALUES(25, '2003-3');
 126     INSERT INTO t5 VALUES(5, '2003-4');
 127
 128     SELECT period, vsum
 129     FROM (SELECT
 130       a.period,
 131       (select sum(val) from t5 where period between a.period and '2002-4') vsum
 132       FROM t5 a where a.period between '2002-1' and '2002-4')
 133     WHERE vsum < 45 ;
 134   }
 135 } {2002-2 30 2002-3 25 2002-4 15}
 136 do_test subquery-1.10.5 {
 137   execsql {
 138     SELECT period, vsum from
 139       (select a.period,
 140       (select sum(val) from t5 where period between a.period and '2002-4') vsum
 141     FROM t5 a where a.period between '2002-1' and '2002-4')
 142     WHERE vsum < 45 ;
 143   }
 144 } {2002-2 30 2002-3 25 2002-4 15}
 145 do_test subquery-1.10.6 {
 146   execsql {
 147     DROP TABLE t5;
 148   }
 149 } {}
 150
 151
 152
 153 #------------------------------------------------------------------
 154 # The following test cases - subquery-2.* - are not logically
 155 # organized. They're here largely because they were failing during
 156 # one stage of development of sub-queries.
 157 #
 158 do_test subquery-2.1 {
 159   execsql {
 160     SELECT (SELECT 10);
 161   }
 162 } {10}
 163 do_test subquery-2.2.1 {
 164   execsql {
 165     CREATE TABLE t3(a PRIMARY KEY, b);
 166     INSERT INTO t3 VALUES(1, 2);
 167     INSERT INTO t3 VALUES(3, 1);
 168   }
 169 } {}
 170 do_test subquery-2.2.2 {
 171   execsql {
 172     SELECT * FROM t3 WHERE a IN (SELECT b FROM t3);
 173   }
 174 } {1 2}
 175 do_test subquery-2.2.3 {
 176   execsql {
 177     DROP TABLE t3;
 178   }
 179 } {}
 180 do_test subquery-2.3.1 {
 181   execsql {
 182     CREATE TABLE t3(a TEXT);
 183     INSERT INTO t3 VALUES('10');
 184   }
 185 } {}
 186 do_test subquery-2.3.2 {
 187   execsql {
 188     SELECT a IN (10.0, 20) FROM t3;
 189   }
 190 } {0}
 191 do_test subquery-2.3.3 {
 192   execsql {
 193     DROP TABLE t3;
 194   }
 195 } {}
 196 do_test subquery-2.4.1 {
 197   execsql {
 198     CREATE TABLE t3(a TEXT);
 199     INSERT INTO t3 VALUES('XX');
 200   }
 201 } {}
 202 do_test subquery-2.4.2 {
 203   execsql {
 204     SELECT count(*) FROM t3 WHERE a IN (SELECT 'XX')
 205   }
 206 } {1}
 207 do_test subquery-2.4.3 {
 208   execsql {
 209     DROP TABLE t3;
 210   }
 211 } {}
 212 do_test subquery-2.5.1 {
 213   execsql {
 214     CREATE TABLE t3(a INTEGER);
 215     INSERT INTO t3 VALUES(10);
 216
 217     CREATE TABLE t4(x TEXT);
 218     INSERT INTO t4 VALUES('10.0');
 219   }
 220 } {}
 221 do_test subquery-2.5.2 {
 222   # In the expr "x IN (SELECT a FROM t3)" the RHS of the IN operator
 223   # has text affinity and the LHS has integer affinity.  The rule is
 224   # that we try to convert both sides to an integer before doing the
 225   # comparision.  Hence, the integer value 10 in t3 will compare equal
 226   # to the string value '10.0' in t4 because the t4 value will be
 227   # converted into an integer.
 228   execsql {
 229     SELECT * FROM t4 WHERE x IN (SELECT a FROM t3);
 230   }
 231 } {10.0}
 232 do_test subquery-2.5.3.1 {
 233   # The t4i index cannot be used to resolve the "x IN (...)" constraint
 234   # because the constraint has integer affinity but t4i has text affinity.
 235   execsql {
 236     CREATE INDEX t4i ON t4(x);
 237     SELECT * FROM t4 WHERE x IN (SELECT a FROM t3);
 238   }
 239 } {10.0}
 240 do_test subquery-2.5.3.2 {
 241   # Verify that the t4i index was not used in the previous query
 242   execsql {
 243     EXPLAIN QUERY PLAN
 244     SELECT * FROM t4 WHERE x IN (SELECT a FROM t3);
 245   }
 246 } {~/t4i/}
 247 do_test subquery-2.5.4 {
 248   execsql {
 249     DROP TABLE t3;
 250     DROP TABLE t4;
 251   }
 252 } {}
 253
 254 #------------------------------------------------------------------
 255 # The following test cases - subquery-3.* - test tickets that
 256 # were raised during development of correlated subqueries.
 257 #
 258
 259 # Ticket 1083
 260 ifcapable view {
 261   do_test subquery-3.1 {
 262     catchsql { DROP TABLE t1; }
 263     catchsql { DROP TABLE t2; }
 264     execsql {
 265       CREATE TABLE t1(a,b);
 266       INSERT INTO t1 VALUES(1,2);
 267       CREATE VIEW v1 AS SELECT b FROM t1 WHERE a>0;
 268       CREATE TABLE t2(p,q);
 269       INSERT INTO t2 VALUES(2,9);
 270       SELECT * FROM v1 WHERE EXISTS(SELECT * FROM t2 WHERE p=v1.b);
 271     }
 272   } {2}
 273   do_test subquery-3.1.1 {
 274     execsql {
 275       SELECT * FROM v1 WHERE EXISTS(SELECT 1);
 276     }
 277   } {2}
 278 } else {
 279   catchsql { DROP TABLE t1; }
 280   catchsql { DROP TABLE t2; }
 281   execsql {
 282     CREATE TABLE t1(a,b);
 283     INSERT INTO t1 VALUES(1,2);
 284     CREATE TABLE t2(p,q);
 285     INSERT INTO t2 VALUES(2,9);
 286   }
 287 }
 288
 289 # Ticket 1084
 290 do_test subquery-3.2 {
 291   catchsql {
 292     CREATE TABLE t1(a,b);
 293     INSERT INTO t1 VALUES(1,2);
 294   }
 295   execsql {
 296     SELECT (SELECT t1.a) FROM t1;
 297   }
 298 } {1}
 299
 300 # Test Cases subquery-3.3.* test correlated subqueries where the
 301 # parent query is an aggregate query. Ticket #1105 is an example
 302 # of such a query.
 303 #
 304 do_test subquery-3.3.1 {
 305   execsql {
 306     SELECT a, (SELECT b) FROM t1 GROUP BY a;
 307   }
 308 } {1 2}
 309 do_test subquery-3.3.2 {
 310   catchsql {DROP TABLE t2}
 311   execsql {
 312     CREATE TABLE t2(c, d);
 313     INSERT INTO t2 VALUES(1, 'one');
 314     INSERT INTO t2 VALUES(2, 'two');
 315     SELECT a, (SELECT d FROM t2 WHERE a=c) FROM t1 GROUP BY a;
 316   }
 317 } {1 one}
 318 do_test subquery-3.3.3 {
 319   execsql {
 320     INSERT INTO t1 VALUES(2, 4);
 321     SELECT max(a), (SELECT d FROM t2 WHERE a=c) FROM t1;
 322   }
 323 } {2 two}
 324 do_test subquery-3.3.4 {
 325   execsql {
 326     SELECT a, (SELECT (SELECT d FROM t2 WHERE a=c)) FROM t1 GROUP BY a;
 327   }
 328 } {1 one 2 two}
 329 do_test subquery-3.3.5 {
 330   execsql {
 331     SELECT a, (SELECT count(*) FROM t2 WHERE a=c) FROM t1;
 332   }
 333 } {1 1 2 1}
 334
 335 # The following tests check for aggregate subqueries in an aggregate
 336 # query.
 337 #
 338 do_test subquery-3.4.1 {
 339   execsql {
 340     CREATE TABLE t34(x,y);
 341     INSERT INTO t34 VALUES(106,4), (107,3), (106,5), (107,5);
 342     SELECT a.x, avg(a.y)
 343       FROM t34 AS a
 344      GROUP BY a.x
 345      HAVING NOT EXISTS( SELECT b.x, avg(b.y)
 346                           FROM t34 AS b
 347                          GROUP BY b.x
 348                          HAVING avg(a.y) > avg(b.y));
 349   }
 350 } {107 4.0}
 351 do_test subquery-3.4.2 {
 352   execsql {
 353     SELECT a.x, avg(a.y) AS avg1
 354       FROM t34 AS a
 355      GROUP BY a.x
 356      HAVING NOT EXISTS( SELECT b.x, avg(b.y) AS avg2
 357                           FROM t34 AS b
 358                          GROUP BY b.x
 359                          HAVING avg1 > avg2);
 360   }
 361 } {107 4.0}
 362 do_test subquery-3.4.3 {
 363   execsql {
 364     SELECT
 365        a.x,
 366        avg(a.y),
 367        NOT EXISTS ( SELECT b.x, avg(b.y)
 368                       FROM t34 AS b
 369                       GROUP BY b.x
 370                      HAVING avg(a.y) > avg(b.y)),
 371        EXISTS ( SELECT c.x, avg(c.y)
 372                   FROM t34 AS c
 373                   GROUP BY c.x
 374                  HAVING avg(a.y) > avg(c.y))
 375       FROM t34 AS a
 376      GROUP BY a.x
 377      ORDER BY a.x;
 378   }
 379 } {106 4.5 0 1 107 4.0 1 0}
 380
 381 do_test subquery-3.5.1 {
 382   execsql {
 383     CREATE TABLE t35a(x); INSERT INTO t35a VALUES(1),(2),(3);
 384     CREATE TABLE t35b(y); INSERT INTO t35b VALUES(98), (99);
 385     SELECT max((SELECT avg(y) FROM t35b)) FROM t35a;
 386   }
 387 } {98.5}
 388 do_test subquery-3.5.2 {
 389   execsql {
 390     SELECT max((SELECT count(y) FROM t35b)) FROM t35a;
 391   }
 392 } {2}
 393 do_test subquery-3.5.3 {
 394   execsql {
 395     SELECT max((SELECT count() FROM t35b)) FROM t35a;
 396   }
 397 } {2}
 398 do_test subquery-3.5.4 {
 399   catchsql {
 400     SELECT max((SELECT count(x) FROM t35b)) FROM t35a;
 401   }
 402 } {1 {misuse of aggregate: count()}}
 403 do_test subquery-3.5.5 {
 404   catchsql {
 405     SELECT max((SELECT count(x) FROM t35b)) FROM t35a;
 406   }
 407 } {1 {misuse of aggregate: count()}}
 408 do_test subquery-3.5.6 {
 409   catchsql {
 410     SELECT max((SELECT a FROM (SELECT count(x) AS a FROM t35b))) FROM t35a;
 411   }
 412 } {1 {misuse of aggregate: count()}}
 413 do_test subquery-3.5.7 {
 414   execsql {
 415     SELECT max((SELECT a FROM (SELECT count(y) AS a FROM t35b))) FROM t35a;
 416   }
 417 } {2}
 418
 419
 420 #------------------------------------------------------------------
 421 # These tests - subquery-4.* - use the TCL statement cache to try
 422 # and expose bugs to do with re-using statements that have been
 423 # passed to sqlite3_reset().
 424 #
 425 # One problem was that VDBE memory cells were not being initialized
 426 # to NULL on the second and subsequent executions.
 427 #
 428 do_test subquery-4.1.1 {
 429   execsql {
 430     SELECT (SELECT a FROM t1);
 431   }
 432 } {1}
 433 do_test subquery-4.2 {
 434   execsql {
 435     DELETE FROM t1;
 436     SELECT (SELECT a FROM t1);
 437   }
 438 } {{}}
 439 do_test subquery-4.2.1 {
 440   execsql {
 441     CREATE TABLE t3(a PRIMARY KEY);
 442     INSERT INTO t3 VALUES(10);
 443   }
 444   execsql {INSERT INTO t3 VALUES((SELECT max(a) FROM t3)+1)}
 445 } {}
 446 do_test subquery-4.2.2 {
 447   execsql {INSERT INTO t3 VALUES((SELECT max(a) FROM t3)+1)}
 448 } {}
 449
 450 #------------------------------------------------------------------
 451 # The subquery-5.* tests make sure string literals in double-quotes
 452 # are handled efficiently.  Double-quote literals are first checked
 453 # to see if they match any column names.  If there is not column name
 454 # match then those literals are used a string constants.  When a
 455 # double-quoted string appears, we want to make sure that the search
 456 # for a matching column name did not cause an otherwise static subquery
 457 # to become a dynamic (correlated) subquery.
 458 #
 459 do_test subquery-5.1 {
 460   proc callcntproc {n} {
 461     incr ::callcnt
 462     return $n
 463   }
 464   set callcnt 0
 465   db function callcnt callcntproc
 466   execsql {
 467     CREATE TABLE t4(x,y);
 468     INSERT INTO t4 VALUES('one',1);
 469     INSERT INTO t4 VALUES('two',2);
 470     INSERT INTO t4 VALUES('three',3);
 471     INSERT INTO t4 VALUES('four',4);
 472     CREATE TABLE t5(a,b);
 473     INSERT INTO t5 VALUES(1,11);
 474     INSERT INTO t5 VALUES(2,22);
 475     INSERT INTO t5 VALUES(3,33);
 476     INSERT INTO t5 VALUES(4,44);
 477     SELECT b FROM t5 WHERE a IN
 478        (SELECT callcnt(y)+0 FROM t4 WHERE x='two')
 479   }
 480 } {22}
 481 do_test subquery-5.2 {
 482   # This is the key test.  The subquery should have only run once.  If
 483   # The double-quoted identifier "two" were causing the subquery to be
 484   # processed as a correlated subquery, then it would have run 4 times.
 485   set callcnt
 486 } {1}
 487
 488
 489 # Ticket #1380.  Make sure correlated subqueries on an IN clause work
 490 # correctly when the left-hand side of the IN operator is constant.
 491 #
 492 do_test subquery-6.1 {
 493   set callcnt 0
 494   execsql {
 495     SELECT x FROM t4 WHERE 1 IN (SELECT callcnt(count(*)) FROM t5 WHERE a=y)
 496   }
 497 } {one two three four}
 498 do_test subquery-6.2 {
 499   set callcnt
 500 } {4}
 501 do_test subquery-6.3 {
 502   set callcnt 0
 503   execsql {
 504     SELECT x FROM t4 WHERE 1 IN (SELECT callcnt(count(*)) FROM t5 WHERE a=1)
 505   }
 506 } {one two three four}
 507 do_test subquery-6.4 {
 508   set callcnt
 509 } {1}
 510
 511 if 0 {   #############  disable until we get #2652 fixed
 512 # Ticket #2652.  Allow aggregate functions of outer queries inside
 513 # a non-aggregate subquery.
 514 #
 515 do_test subquery-7.1 {
 516   execsql {
 517     CREATE TABLE t7(c7);
 518     INSERT INTO t7 VALUES(1);
 519     INSERT INTO t7 VALUES(2);
 520     INSERT INTO t7 VALUES(3);
 521     CREATE TABLE t8(c8);
 522     INSERT INTO t8 VALUES(100);
 523     INSERT INTO t8 VALUES(200);
 524     INSERT INTO t8 VALUES(300);
 525     CREATE TABLE t9(c9);
 526     INSERT INTO t9 VALUES(10000);
 527     INSERT INTO t9 VALUES(20000);
 528     INSERT INTO t9 VALUES(30000);
 529
 530     SELECT (SELECT c7+c8 FROM t7) FROM t8;
 531   }
 532 } {101 201 301}
 533 do_test subquery-7.2 {
 534   execsql {
 535     SELECT (SELECT max(c7)+c8 FROM t7) FROM t8;
 536   }
 537 } {103 203 303}
 538 do_test subquery-7.3 {
 539   execsql {
 540     SELECT (SELECT c7+max(c8) FROM t8) FROM t7
 541   }
 542 } {301}
 543 do_test subquery-7.4 {
 544   execsql {
 545     SELECT (SELECT max(c7)+max(c8) FROM t8) FROM t7
 546   }
 547 } {303}
 548 do_test subquery-7.5 {
 549   execsql {
 550     SELECT (SELECT c8 FROM t8 WHERE rowid=max(c7)) FROM t7
 551   }
 552 } {300}
 553 do_test subquery-7.6 {
 554   execsql {
 555     SELECT (SELECT (SELECT max(c7+c8+c9) FROM t9) FROM t8) FROM t7
 556   }
 557 } {30101 30102 30103}
 558 do_test subquery-7.7 {
 559   execsql {
 560     SELECT (SELECT (SELECT c7+max(c8+c9) FROM t9) FROM t8) FROM t7
 561   }
 562 } {30101 30102 30103}
 563 do_test subquery-7.8 {
 564   execsql {
 565     SELECT (SELECT (SELECT max(c7)+c8+c9 FROM t9) FROM t8) FROM t7
 566   }
 567 } {10103}
 568 do_test subquery-7.9 {
 569   execsql {
 570     SELECT (SELECT (SELECT c7+max(c8)+c9 FROM t9) FROM t8) FROM t7
 571   }
 572 } {10301 10302 10303}
 573 do_test subquery-7.10 {
 574   execsql {
 575     SELECT (SELECT (SELECT c7+c8+max(c9) FROM t9) FROM t8) FROM t7
 576   }
 577 } {30101 30102 30103}
 578 do_test subquery-7.11 {
 579   execsql {
 580     SELECT (SELECT (SELECT max(c7)+max(c8)+max(c9) FROM t9) FROM t8) FROM t7
 581   }
 582 } {30303}
 583 }  ;############# Disabled
 584
 585 # 2015-04-21.
 586 # Verify that a memory leak in the table column type and collation analysis
 587 # is plugged.
 588 #
 589 do_execsql_test subquery-8.1 {
 590   CREATE TABLE t8(a TEXT, b INT);
 591   SELECT (SELECT 0 FROM (SELECT * FROM t1)) AS x WHERE x;
 592   SELECT (SELECT 0 FROM (SELECT * FROM (SELECT 0))) AS x WHERE x;
 593 } {}
 594
 595 # 2022-01-12 https://sqlite.org/forum/forumpost/0ec80f12d02acb3f
 596 #
 597 reset_db
 598 do_execsql_test subquery-9.1 {
 599   CREATE TABLE t1(x);
 600   INSERT INTO t1 VALUES(1),(1),(1);
 601   SELECT (SELECT DISTINCT x FROM t1 ORDER BY +x LIMIT 1 OFFSET 100) FROM t1;
 602 } {{} {} {}}
 603 do_execsql_test subquery-9.2 {
 604   SELECT (SELECT DISTINCT x FROM t1 ORDER BY +x LIMIT 1 OFFSET 0) FROM t1;
 605 } {1 1 1}
 606 do_execsql_test subquery-9.3 {
 607   INSERT INTO t1 VALUES(2);
 608   SELECT (SELECT DISTINCT x FROM t1 ORDER BY +x LIMIT 1 OFFSET 1) FROM t1;
 609 } {2 2 2 2}
 610 do_execsql_test subquery-9.4 {
 611   SELECT (SELECT DISTINCT x FROM t1 ORDER BY +x LIMIT 1 OFFSET 2) FROM t1;
 612 } {{} {} {} {}}
 613
 614 # 2023-09-15
 615 # Query planner performance regression reported by private email
 616 # on 2023-09-14, caused by VIEWSCAN optimization of check-in 609fbb94b8f01d67
 617 # from 2022-09-01.
 618 #
 619 reset_db
 620 do_execsql_test subquery-10.1 {
 621   CREATE TABLE t1(aa TEXT, bb INT, cc TEXT);
 622   CREATE INDEX x11 on t1(bb);
 623   CREATE INDEX x12 on t1(aa);
 624   CREATE TABLE t2(aa TEXT, xx INT);
 625   ANALYZE sqlite_master;
 626   INSERT INTO sqlite_stat1(tbl, idx, stat) VALUES('t1', 'x11', '156789 28');
 627   INSERT INTO sqlite_stat1(tbl, idx, stat) VALUES('t1', 'x12', '156789 1');
 628   ANALYZE sqlite_master;
 629 }
 630 do_eqp_test subquery-10.2 {
 631   WITH v1(aa,cc,bb) AS (SELECT aa, cc, bb FROM t1 WHERE bb=12345),
 632        v2(aa,mx)    AS (SELECT aa, max(xx) FROM t2 GROUP BY aa)
 633   SELECT * FROM v1 JOIN v2 ON v1.aa=v2.aa;
 634 } {
 635   QUERY PLAN
 636   |--CO-ROUTINE v2
 637   |  |--SCAN t2
 638   |  `--USE TEMP B-TREE FOR GROUP BY
 639   |--SEARCH t1 USING INDEX x11 (bb=?)
 640   `--SEARCH v2 USING AUTOMATIC COVERING INDEX (aa=?)
 641 }
 642 # ^^^^^^^^^^^^^
 643 # Prior to the fix the incorrect (slow) plan caused by the
 644 # VIEWSCAN optimization was:
 645 #
 646 # QUERY PLAN
 647 # |--CO-ROUTINE v2
 648 # |  |--SCAN t2
 649 # |  `--USE TEMP B-TREE FOR GROUP BY
 650 # |--SCAN v2
 651 # `--SEARCH t1 USING INDEX x12 (aa=?)
 652 #
 653
 654 #-----------------------------------------------------------------------------
 655 # 2024-04-25 Column affinities for columns of compound subqueries
 656 #
 657 reset_db
 658 sqlite3_test_control SQLITE_TESTCTRL_INTERNAL_FUNCTIONS db
 659 do_execsql_test subquery-11.1 {
 660   CREATE TABLE t1(ix INT, rx REAL, bx BLOB, tx TEXT, ax);
 661   INSERT INTO t1 VALUES(1,1.0,x'31','x',NULL);
 662   WITH c(a) AS (SELECT 'y' UNION SELECT tx FROM t1) SELECT affinity(a) FROM c;
 663   WITH c(a) AS (SELECT tx FROM t1 UNION SELECT 'y') SELECT affinity(a) FROM c;
 664 } {text text text text}
 665 do_execsql_test subquery-11.2 {
 666   WITH c(a) AS (SELECT 2 UNION SELECT tx FROM t1) SELECT affinity(a) FROM c;
 667   WITH c(a) AS (SELECT tx FROM t1 UNION SELECT 2) SELECT affinity(a) FROM c;
 668 } {blob blob blob blob}
 669 do_execsql_test subquery-11.3 {
 670   WITH c(a) AS (SELECT 2.0 UNION SELECT tx FROM t1) SELECT affinity(a) FROM c;
 671   WITH c(a) AS (SELECT tx FROM t1 UNION SELECT 2.0) SELECT affinity(a) FROM c;
 672 } {blob blob blob blob}
 673 do_execsql_test subquery-11.4 {
 674   WITH c(a) AS (SELECT null UNION SELECT tx FROM t1) SELECT affinity(a) FROM c;
 675   WITH c(a) AS (SELECT tx FROM t1 UNION SELECT null) SELECT affinity(a) FROM c;
 676 } {text text text text}
 677 do_execsql_test subquery-11.5 {
 678   WITH c(a) AS (SELECT x'32' UNION SELECT tx FROM t1) SELECT affinity(a) FROM c;
 679   WITH c(a) AS (SELECT tx FROM t1 UNION SELECT x'32') SELECT affinity(a) FROM c;
 680 } {text text text text}
 681 do_execsql_test subquery-11.6 {
 682   WITH c(a) AS (SELECT 3 UNION SELECT ix FROM t1) SELECT affinity(a) FROM c;
 683   WITH c(a) AS (SELECT ix FROM t1 UNION SELECT 3) SELECT affinity(a) FROM c;
 684 } {integer integer integer integer}
 685 do_execsql_test subquery-11.7 {
 686   WITH c(a) AS (SELECT 3.0 UNION SELECT ix FROM t1) SELECT affinity(a) FROM c;
 687   WITH c(a) AS (SELECT ix FROM t1 UNION SELECT 3.0) SELECT affinity(a) FROM c;
 688 } {integer integer integer integer}
 689 do_execsql_test subquery-11.8 {
 690   WITH c(a) AS (SELECT '3' UNION SELECT ix FROM t1) SELECT affinity(a) FROM c;
 691   WITH c(a) AS (SELECT ix FROM t1 UNION SELECT '3') SELECT affinity(a) FROM c;
 692 } {blob blob blob blob}
 693 do_execsql_test subquery-11.10 {
 694   WITH c(a) AS (SELECT x'32' UNION SELECT ix FROM t1) SELECT affinity(a) FROM c;
 695   WITH c(a) AS (SELECT ix FROM t1 UNION SELECT x'32') SELECT affinity(a) FROM c;
 696 } {integer integer integer integer}
 697 do_execsql_test subquery-11.11 {
 698   WITH c(a) AS (SELECT 4 UNION SELECT rx FROM t1) SELECT affinity(a) FROM c;
 699   WITH c(a) AS (SELECT rx FROM t1 UNION SELECT 4) SELECT affinity(a) FROM c;
 700 } {real real real real}
 701 do_execsql_test subquery-11.12 {
 702   WITH c(a) AS (SELECT '4' UNION SELECT rx FROM t1) SELECT affinity(a) FROM c;
 703   WITH c(a) AS (SELECT rx FROM t1 UNION SELECT '4') SELECT affinity(a) FROM c;
 704 } {blob blob blob blob}
 705 do_execsql_test subquery-11.13 {
 706   WITH c(a) AS (SELECT null UNION SELECT rx FROM t1) SELECT affinity(a) FROM c;
 707   WITH c(a) AS (SELECT rx FROM t1 UNION SELECT null) SELECT affinity(a) FROM c;
 708 } {real real real real}
 709 do_execsql_test subquery-11.14 {
 710   WITH c(a) AS (SELECT x'b4' UNION SELECT rx FROM t1) SELECT affinity(a) FROM c;
 711   WITH c(a) AS (SELECT rx FROM t1 UNION SELECT x'b4') SELECT affinity(a) FROM c;
 712 } {real real real real}
 713
 714 finish_test