Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | 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. |
---|---|
Downloads: | Tarball | ZIP archive | SQL archive |
Timelines: | family | ancestors | descendants | both | compound-subquery-affinity |
Files: | files | file ages | folders |
SHA3-256: |
bbdf22e3d989f42b963f1f2f219dfeac |
User & Date: | drh 2024-04-25 17:52:10 |
Context
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) | |
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) | |
Changes
Changes to src/select.c.
︙ | ︙ | |||
2332 2333 2334 2335 2336 2337 2338 2339 2340 2341 2342 2343 2344 2345 2346 2347 2348 2349 2350 | 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; } | > > | < | | 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 |