Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| Comment: | Back out the previous change on this branch. In its place: Determine the affinity of a subquery by the left-most arm of the subquery that has an affinity other than NONE. In other words, scan from left to right looking for an arm of the compound subquery with an affinity of BLOB, TEXT, INTEGER, or REAL and pick the first one found. Or stay with NONE if no arm has a defined affinity. Test cases added. |
|---|---|
| Downloads: | Tarball | ZIP archive | SQL archive |
| Timelines: | family | ancestors | descendants | both | compound-subquery-affinity |
| Files: | files | file ages | folders |
| SHA3-256: |
b8ec8511b1968bbc1472b3e2e21f0fef |
| User & Date: | drh 2024-04-25 16:55:53 |
Context
|
2024-04-25
| ||
| 17:52 | Further improvements to the computation of affinity for compound subqueries: make sure that the selected affinity is compatible with a literal values in arms to the left of the arm that is used to determine affinity. (Closed-Leaf check-in: bbdf22e3 user: drh tags: compound-subquery-affinity) | |
| 16:55 | Back out the previous change on this branch. In its place: Determine the affinity of a subquery by the left-most arm of the subquery that has an affinity other than NONE. In other words, scan from left to right looking for an arm of the compound subquery with an affinity of BLOB, TEXT, INTEGER, or REAL and pick the first one found. Or stay with NONE if no arm has a defined affinity. Test cases added. (check-in: b8ec8511 user: drh tags: compound-subquery-affinity) | |
|
2024-04-24
| ||
| 19:49 | An experimental minor tweak to the way affinities are computed for compound subqueries, when the actual affinity is ambiguous. (check-in: 779723ad user: drh tags: compound-subquery-affinity) | |
Changes
Changes to src/select.c.
| ︙ | ︙ | |||
2332 2333 2334 2335 2336 2337 2338 2339 2340 2341 2342 2343 2344 2345 |
while( pSelect->pPrior ) pSelect = pSelect->pPrior;
a = pSelect->pEList->a;
memset(&sNC, 0, sizeof(sNC));
sNC.pSrcList = pSelect->pSrc;
for(i=0, pCol=pTab->aCol; i<pTab->nCol; i++, pCol++){
const char *zType;
i64 n;
pTab->tabFlags |= (pCol->colFlags & COLFLAG_NOINSERT);
p = a[i].pExpr;
/* pCol->szEst = ... // Column size est for SELECT tables never used */
pCol->affinity = sqlite3ExprAffinity(p);
if( pCol->affinity<=SQLITE_AFF_NONE ){
pCol->affinity = aff;
}
| > > > > > | < | | 2332 2333 2334 2335 2336 2337 2338 2339 2340 2341 2342 2343 2344 2345 2346 2347 2348 2349 2350 2351 2352 2353 2354 2355 2356 2357 2358 2359 2360 |
while( pSelect->pPrior ) pSelect = pSelect->pPrior;
a = pSelect->pEList->a;
memset(&sNC, 0, sizeof(sNC));
sNC.pSrcList = pSelect->pSrc;
for(i=0, pCol=pTab->aCol; i<pTab->nCol; i++, pCol++){
const char *zType;
i64 n;
Select *pS2 = pSelect;
pTab->tabFlags |= (pCol->colFlags & COLFLAG_NOINSERT);
p = a[i].pExpr;
/* pCol->szEst = ... // Column size est for SELECT tables never used */
pCol->affinity = sqlite3ExprAffinity(p);
while( pCol->affinity<=SQLITE_AFF_NONE && pS2->pNext!=0 ){
pS2 = pS2->pNext;
pCol->affinity = sqlite3ExprAffinity(pS2->pEList->a[i].pExpr);
}
if( pCol->affinity<=SQLITE_AFF_NONE ){
pCol->affinity = aff;
}
if( pCol->affinity>=SQLITE_AFF_TEXT && pS2->pNext ){
int m = 0;
for(m=0, pS2=pS2->pNext; pS2; pS2=pS2->pNext){
m |= sqlite3ExprDataType(pS2->pEList->a[i].pExpr);
}
if( pCol->affinity==SQLITE_AFF_TEXT && (m&0x01)!=0 ){
pCol->affinity = SQLITE_AFF_BLOB;
}else
if( pCol->affinity>=SQLITE_AFF_NUMERIC && (m&0x02)!=0 ){
pCol->affinity = SQLITE_AFF_BLOB;
|
| ︙ | ︙ | |||
6413 6414 6415 6416 6417 6418 6419 |
for(i=0, pFrom=pTabList->a; i<pTabList->nSrc; i++, pFrom++){
Table *pTab = pFrom->pTab;
assert( pTab!=0 );
if( (pTab->tabFlags & TF_Ephemeral)!=0 ){
/* A sub-query in the FROM clause of a SELECT */
Select *pSel = pFrom->pSelect;
if( pSel ){
| | | 6417 6418 6419 6420 6421 6422 6423 6424 6425 6426 6427 6428 6429 6430 6431 |
for(i=0, pFrom=pTabList->a; i<pTabList->nSrc; i++, pFrom++){
Table *pTab = pFrom->pTab;
assert( pTab!=0 );
if( (pTab->tabFlags & TF_Ephemeral)!=0 ){
/* A sub-query in the FROM clause of a SELECT */
Select *pSel = pFrom->pSelect;
if( pSel ){
sqlite3SubqueryColumnTypes(pParse, pTab, pSel, SQLITE_AFF_NONE);
}
}
}
}
#endif
|
| ︙ | ︙ |
Changes to test/view.test.
| ︙ | ︙ | |||
733 734 735 736 737 738 739 |
} {}
do_execsql_test view-27.5 {
SELECT 1 FROM v0 WHERE c0<c1
} {1}
do_execsql_test view-27.6 {
SELECT c0<c1 FROM (SELECT t0.c0 AS c0, AVG(t0.c1) AS c1 FROM t0)
| | | | | 733 734 735 736 737 738 739 740 741 742 743 744 745 746 747 748 749 750 751 752 753 754 755 756 |
} {}
do_execsql_test view-27.5 {
SELECT 1 FROM v0 WHERE c0<c1
} {1}
do_execsql_test view-27.6 {
SELECT c0<c1 FROM (SELECT t0.c0 AS c0, AVG(t0.c1) AS c1 FROM t0)
} 1
do_execsql_test view-27.7 {
SELECT c1<c0 FROM (SELECT t0.c0 AS c0, AVG(t0.c1) AS c1 FROM t0)
} 0
do_execsql_test view-27.8 {
SELECT 1 FROM (SELECT t0.c0 AS c0, AVG(t0.c1) AS c1 FROM t0) WHERE c1<c0
} {}
do_execsql_test view-27.9 {
SELECT 1 FROM (SELECT t0.c0 AS c0, AVG(t0.c1) AS c1 FROM t0) WHERE c0<c1
} {1}
#-------------------------------------------------------------------------
reset_db
do_execsql_test view-28.0 {
CREATE TABLE t0(c0 TEXT);
CREATE VIEW v0(c0) AS SELECT t0.c0 FROM t0;
INSERT INTO t0(c0) VALUES ('0');
|
| ︙ | ︙ | |||
796 797 798 799 800 801 802 803 804 |
do_execsql_test view-30.1 {
PRAGMA table_info = t1;
} { 0 a INT 0 {} 0 1 b BLOB 0 {} 0 }
do_execsql_test view-30.2 {
PRAGMA table_info = t2;
} { 0 a INT 0 {} 0 1 b BLOB 0 {} 0 }
}
finish_test
| > > > > > > > > > > > > > > > > > > > > > > > | 796 797 798 799 800 801 802 803 804 805 806 807 808 809 810 811 812 813 814 815 816 817 818 819 820 821 822 823 824 825 826 827 |
do_execsql_test view-30.1 {
PRAGMA table_info = t1;
} { 0 a INT 0 {} 0 1 b BLOB 0 {} 0 }
do_execsql_test view-30.2 {
PRAGMA table_info = t2;
} { 0 a INT 0 {} 0 1 b BLOB 0 {} 0 }
}
#-----------------------------------------------------------------------
# 2024-04-25 Trying to make type information on compound subqueries
# more predictable and rational.
#
reset_db
do_execsql_test view-31.1 {
CREATE TABLE x2(b TEXT);
CREATE TABLE x1(a TEXT);
INSERT INTO x1 VALUES('123');
-- Two queries get the same result even though the order of terms
-- in the CTE is reversed
WITH c(x) AS ( SELECT b FROM x2 UNION SELECT 123 )
SELECT count(*) FROM x1 WHERE a IN c;
WITH c(x) AS ( SELECT 123 UNION SELECT b FROM x2 )
SELECT count(*) FROM x1 WHERE a IN c;
} {0 0}
do_execsql_test view-31.2 {
CREATE TABLE t3(a INTEGER, b TEXT);
INSERT INTO t3 VALUES(123, 123);
WITH s AS ( VALUES(123), (456) ) SELECT * FROM t3 WHERE b IN s;
} {123 123}
finish_test
|