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: |
dd8f7f7511639a1baa41a6ff2e359dc6 |
| 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
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
|