Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| Comment: | Improvement to the way that affinity is determined for columns of a compound subquery. The affinity is the affinity of the left-most arm of the compound subquery that has an affinity other than NONE, adjusted to accommodate the data types coming out of the other arms. |
|---|---|
| Downloads: | Tarball | ZIP archive | SQL archive |
| Timelines: | family | ancestors | descendants | both | trunk |
| Files: | files | file ages | folders |
| SHA3-256: |
e6df846f36209bac3e420dd80ce2bbbd |
| User & Date: | drh 2024-04-25 23:26:11 |
Context
|
2024-04-26
| ||
| 12:01 | Add test demonstrating the problem at forum post c243b8f856. No fix yet. (check-in: 1685495c user: dan tags: vtab-limit-fix) | |
| 11:32 | Fix the TreeView display of a LIMIT clause on a compound query. Debugging code only - not part of production builds. (check-in: 38f1e5ce user: drh tags: trunk) | |
|
2024-04-25
| ||
| 23:26 | Improvement to the way that affinity is determined for columns of a compound subquery. The affinity is the affinity of the left-most arm of the compound subquery that has an affinity other than NONE, adjusted to accommodate the data types coming out of the other arms. (check-in: e6df846f user: drh tags: trunk) | |
| 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) | |
| 06:52 | wasm: add a makefile comment about the static sqlite3.h/c version info injected into the JS files possibly differing from the runtime-emited version info when a user provides their own sqlite3.c. (check-in: d99a01a0 user: stephan tags: trunk) | |
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 2361 |
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;
int m = 0;
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 ){
m |= sqlite3ExprDataType(pS2->pEList->a[i].pExpr);
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 || pS2!=pSelect) ){
for(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;
|
| ︙ | ︙ |
Changes to test/subquery.test.
| ︙ | ︙ | |||
647 648 649 650 651 652 653 654 655 | # |--CO-ROUTINE v2 # | |--SCAN t2 # | `--USE TEMP B-TREE FOR GROUP BY # |--SCAN v2 # `--SEARCH t1 USING INDEX x12 (aa=?) # finish_test | > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 647 648 649 650 651 652 653 654 655 656 657 658 659 660 661 662 663 664 665 666 667 668 669 670 671 672 673 674 675 676 677 678 679 680 681 682 683 684 685 686 687 688 689 690 691 692 693 694 695 696 697 698 699 700 701 702 703 704 705 706 707 708 709 710 711 712 713 714 |
# |--CO-ROUTINE v2
# | |--SCAN t2
# | `--USE TEMP B-TREE FOR GROUP BY
# |--SCAN v2
# `--SEARCH t1 USING INDEX x12 (aa=?)
#
#-----------------------------------------------------------------------------
# 2024-04-25 Column affinities for columns of compound subqueries
#
reset_db
sqlite3_test_control SQLITE_TESTCTRL_INTERNAL_FUNCTIONS db
do_execsql_test subquery-11.1 {
CREATE TABLE t1(ix INT, rx REAL, bx BLOB, tx TEXT, ax);
INSERT INTO t1 VALUES(1,1.0,x'31','x',NULL);
WITH c(a) AS (SELECT 'y' UNION SELECT tx FROM t1) SELECT affinity(a) FROM c;
WITH c(a) AS (SELECT tx FROM t1 UNION SELECT 'y') SELECT affinity(a) FROM c;
} {text text text text}
do_execsql_test subquery-11.2 {
WITH c(a) AS (SELECT 2 UNION SELECT tx FROM t1) SELECT affinity(a) FROM c;
WITH c(a) AS (SELECT tx FROM t1 UNION SELECT 2) SELECT affinity(a) FROM c;
} {blob blob blob blob}
do_execsql_test subquery-11.3 {
WITH c(a) AS (SELECT 2.0 UNION SELECT tx FROM t1) SELECT affinity(a) FROM c;
WITH c(a) AS (SELECT tx FROM t1 UNION SELECT 2.0) SELECT affinity(a) FROM c;
} {blob blob blob blob}
do_execsql_test subquery-11.4 {
WITH c(a) AS (SELECT null UNION SELECT tx FROM t1) SELECT affinity(a) FROM c;
WITH c(a) AS (SELECT tx FROM t1 UNION SELECT null) SELECT affinity(a) FROM c;
} {text text text text}
do_execsql_test subquery-11.5 {
WITH c(a) AS (SELECT x'32' UNION SELECT tx FROM t1) SELECT affinity(a) FROM c;
WITH c(a) AS (SELECT tx FROM t1 UNION SELECT x'32') SELECT affinity(a) FROM c;
} {text text text text}
do_execsql_test subquery-11.6 {
WITH c(a) AS (SELECT 3 UNION SELECT ix FROM t1) SELECT affinity(a) FROM c;
WITH c(a) AS (SELECT ix FROM t1 UNION SELECT 3) SELECT affinity(a) FROM c;
} {integer integer integer integer}
do_execsql_test subquery-11.7 {
WITH c(a) AS (SELECT 3.0 UNION SELECT ix FROM t1) SELECT affinity(a) FROM c;
WITH c(a) AS (SELECT ix FROM t1 UNION SELECT 3.0) SELECT affinity(a) FROM c;
} {integer integer integer integer}
do_execsql_test subquery-11.8 {
WITH c(a) AS (SELECT '3' UNION SELECT ix FROM t1) SELECT affinity(a) FROM c;
WITH c(a) AS (SELECT ix FROM t1 UNION SELECT '3') SELECT affinity(a) FROM c;
} {blob blob blob blob}
do_execsql_test subquery-11.10 {
WITH c(a) AS (SELECT x'32' UNION SELECT ix FROM t1) SELECT affinity(a) FROM c;
WITH c(a) AS (SELECT ix FROM t1 UNION SELECT x'32') SELECT affinity(a) FROM c;
} {integer integer integer integer}
do_execsql_test subquery-11.11 {
WITH c(a) AS (SELECT 4 UNION SELECT rx FROM t1) SELECT affinity(a) FROM c;
WITH c(a) AS (SELECT rx FROM t1 UNION SELECT 4) SELECT affinity(a) FROM c;
} {real real real real}
do_execsql_test subquery-11.12 {
WITH c(a) AS (SELECT '4' UNION SELECT rx FROM t1) SELECT affinity(a) FROM c;
WITH c(a) AS (SELECT rx FROM t1 UNION SELECT '4') SELECT affinity(a) FROM c;
} {blob blob blob blob}
do_execsql_test subquery-11.13 {
WITH c(a) AS (SELECT null UNION SELECT rx FROM t1) SELECT affinity(a) FROM c;
WITH c(a) AS (SELECT rx FROM t1 UNION SELECT null) SELECT affinity(a) FROM c;
} {real real real real}
do_execsql_test subquery-11.14 {
WITH c(a) AS (SELECT x'b4' UNION SELECT rx FROM t1) SELECT affinity(a) FROM c;
WITH c(a) AS (SELECT rx FROM t1 UNION SELECT x'b4') SELECT affinity(a) FROM c;
} {real real real real}
finish_test
|
Changes to test/view.test.
| ︙ | ︙ | |||
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
|