/ Check-in [dd8f7f75]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Add test cases for the query flattener fix for ticket [2f7170d73bf9abf8].
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | tkt-2f7170d7
Files: files | file ages | folders
SHA1: dd8f7f7511639a1baa41a6ff2e359dc6f1e66943
User & Date: drh 2015-02-09 16:34:33
Context
2015-02-09
18:28
Propagate COLLATE operators upward through function calls and CASE operations. And do not flatten an aggregate subquery into a query that uses other subqueries. Fixes for tickets [ca0d20b6cdddec5] and [2f7170d73bf9], respectively. check-in: 24e78b8d user: drh tags: trunk
16:34
Add test cases for the query flattener fix for ticket [2f7170d73bf9abf8]. Closed-Leaf check-in: dd8f7f75 user: drh tags: tkt-2f7170d7
16:09
Propagate the COLLATE operator upward through function calls. Initial fix for ticket [ca0d20b6cdddec5e8]. check-in: c053448a user: drh tags: tkt-2f7170d7
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to test/select6.test.

552
553
554
555
556
557
558
559



560





















































561
do_catchsql_test 10.8 {
  SELECT * FROM (
    SELECT * FROM k UNION ALL
    SELECT * FROM t UNION ALL 
    SELECT l,m,l FROM j 
  )
} $err


























































finish_test








>
>
>
|
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>

552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
do_catchsql_test 10.8 {
  SELECT * FROM (
    SELECT * FROM k UNION ALL
    SELECT * FROM t UNION ALL 
    SELECT l,m,l FROM j 
  )
} $err

# 2015-02-09 Ticket [2f7170d73bf9abf80339187aa3677dce3dbcd5ca]
# "misuse of aggregate" error if aggregate column from FROM
# subquery is used in correlated subquery 
#
do_execsql_test 11.1 {
  DROP TABLE IF EXISTS t1;
  CREATE TABLE t1(w INT, x INT);
  INSERT INTO t1(w,x)
   VALUES(1,10),(2,20),(3,30),
         (2,21),(3,31),
         (3,32);
  CREATE INDEX t1wx ON t1(w,x);

  DROP TABLE IF EXISTS t2;
  CREATE TABLE t2(w INT, y VARCHAR(8));
  INSERT INTO t2(w,y) VALUES(1,'one'),(2,'two'),(3,'three'),(4,'four');
  CREATE INDEX t2wy ON t2(w,y);

  SELECT cnt, xyz, (SELECT y FROM t2 WHERE w=cnt), '|'
    FROM (SELECT count(*) AS cnt, w AS xyz FROM t1 GROUP BY 2)
   ORDER BY cnt, xyz;
} {1 1 one | 2 2 two | 3 3 three |}
do_execsql_test 11.2 {
  SELECT cnt, xyz, lower((SELECT y FROM t2 WHERE w=cnt)), '|'
    FROM (SELECT count(*) AS cnt, w AS xyz FROM t1 GROUP BY 2)
   ORDER BY cnt, xyz;
} {1 1 one | 2 2 two | 3 3 three |}
do_execsql_test 11.3 {
  SELECT cnt, xyz, '|'
    FROM (SELECT count(*) AS cnt, w AS xyz FROM t1 GROUP BY 2)
   WHERE (SELECT y FROM t2 WHERE w=cnt)!='two'
   ORDER BY cnt, xyz;
} {1 1 | 3 3 |}
do_execsql_test 11.4 {
  SELECT cnt, xyz, '|'
    FROM (SELECT count(*) AS cnt, w AS xyz FROM t1 GROUP BY 2)
   ORDER BY lower((SELECT y FROM t2 WHERE w=cnt));
} {1 1 | 3 3 | 2 2 |}
do_execsql_test 11.5 {
  SELECT cnt, xyz, 
         CASE WHEN (SELECT y FROM t2 WHERE w=cnt)=='two'
              THEN 'aaa' ELSE 'bbb'
          END, '|'
    FROM (SELECT count(*) AS cnt, w AS xyz FROM t1 GROUP BY 2)
   ORDER BY +cnt;
} {1 1 bbb | 2 2 aaa | 3 3 bbb |}

do_execsql_test 11.100 {
  DROP TABLE t1;
  DROP TABLE t2;
  CREATE TABLE t1(x);
  CREATE TABLE t2(y, z);
  SELECT ( SELECT y FROM t2 WHERE z = cnt )
    FROM ( SELECT count(*) AS cnt FROM t1 );
} {{}}


finish_test