aboutsummaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--ext/rtree/rtreeC.test2
-rw-r--r--manifest65
-rw-r--r--manifest.uuid2
-rw-r--r--src/build.c29
-rw-r--r--src/expr.c4
-rw-r--r--src/resolve.c4
-rw-r--r--src/select.c129
-rw-r--r--src/sqliteInt.h21
-rw-r--r--src/treeview.c9
-rw-r--r--src/vdbe.c10
-rw-r--r--src/where.c130
-rw-r--r--src/whereInt.h18
-rw-r--r--src/wherecode.c146
-rw-r--r--src/whereexpr.c8
-rw-r--r--test/affinity3.test32
-rw-r--r--test/aggnested.test11
-rw-r--r--test/autoindex1.test2
-rw-r--r--test/autoindex4.test25
-rw-r--r--test/btree01.test3
-rw-r--r--test/collate2.test24
-rw-r--r--test/fts3join.test2
-rw-r--r--test/index6.test2
-rw-r--r--test/join.test24
-rw-r--r--test/join2.test31
-rw-r--r--test/join5.test6
-rw-r--r--test/join7.test273
-rw-r--r--test/join8.test24
-rw-r--r--test/vtab6.test14
-rw-r--r--test/where.test9
-rw-r--r--test/where9.test4
30 files changed, 868 insertions, 195 deletions
diff --git a/ext/rtree/rtreeC.test b/ext/rtree/rtreeC.test
index 75afcd7e2..bddc7d303 100644
--- a/ext/rtree/rtreeC.test
+++ b/ext/rtree/rtreeC.test
@@ -342,7 +342,7 @@ do_eqp_execsql_test 7.2 {
QUERY PLAN
|--SCAN xdir
|--SCAN rt VIRTUAL TABLE INDEX 2:B0D1
- `--SCAN ydir
+ `--SCAN ydir LEFT-JOIN
} {
5 1 2 7 12 14 {}
5 2 2 7 8 12 10
diff --git a/manifest b/manifest
index 85b801ffe..a5552c1fb 100644
--- a/manifest
+++ b/manifest
@@ -1,5 +1,5 @@
-C Fix\san\sassert()\sin\sbtree.c\sthat\smight\snot\sbe\strue\sfor\sa\scorrupt\sdatabase\sfile.
-D 2022-04-13T10:49:50.291
+C Add\ssupport\sfor\sRIGHT\sand\sFULL\sOUTER\sJOINs.
+D 2022-04-13T12:34:54.242
F .fossil-settings/empty-dirs dbb81e8fc0401ac46a1491ab34a7f2c7c0452f2f06b54ebb845d024ca8283ef1
F .fossil-settings/ignore-glob 35175cdfcf539b2318cb04a9901442804be81cd677d8b889fcc9149c21f239ea
F LICENSE.md df5091916dbb40e6e9686186587125e1b2ff51f022cc334e886c19a0e9982724
@@ -409,7 +409,7 @@ F ext/rtree/rtree8.test 2d99006a1386663978c9e1df167554671e4f711c419175b39f332719
F ext/rtree/rtree9.test fd3c9384ef8aabbc127b3878764070398f136eebc551cd20484b570f2cc1956a
F ext/rtree/rtreeA.test a7fd235d8194115fa2e14d300337931eb2e960fe8a46cdfb66add2206412ea41
F ext/rtree/rtreeB.test 4cec297f8e5c588654bbf3c6ed0903f10612be8a2878055dd25faf8c71758bc9
-F ext/rtree/rtreeC.test c4bfa9a61c6788c03e4a9ce40ab2cfc6100982559effd9842d1b658e1d47aa5f
+F ext/rtree/rtreeC.test 2978b194d09b13e106bdb0e1c5b408b9d42eb338c1082bf43c87ef43bd626147
F ext/rtree/rtreeD.test fe46aa7f012e137bd58294409b16c0d43976c3bb92c8f710481e577c4a1100dc
F ext/rtree/rtreeE.test e65d3fc625da1800b412fc8785817327d43ccfec5f5973912d8c9e471928caa9
F ext/rtree/rtreeF.test 81ffa7ef51c4e4618d497a57328c265bf576990c7070633b623b23cd450ed331
@@ -495,7 +495,7 @@ F src/btmutex.c 8acc2f464ee76324bf13310df5692a262b801808984c1b79defb2503bbafadb6
F src/btree.c 093c940ddf1d0753542d04e37eb9903cbc126e997815d234dd2b6317e812b192
F src/btree.h 74d64b8f28cfa4a894d14d4ed64fa432cd697b98b61708d4351482ae15913e22
F src/btreeInt.h 8ce1332edd89dfd2461d561ac10a0ab5601c8e06200cb5230596c3caaf54482e
-F src/build.c ff119be98394a65bc8be7afc39d4a791a66f03a778d396de3ec456f5dfaf39e8
+F src/build.c d30ef1cbd19efbaf8596521589f2a56292325e03f0801bf160608da7b03a03ff
F src/callback.c 4c19af69835787bfe790ac560f3071a824eb629f34e41f97b52ce5235c77de1c
F src/complete.c a3634ab1e687055cd002e11b8f43eb75c17da23e
F src/ctime.c 026dbdcdbd8c3cde98a88483ee88310ff43150ab164ad768f12cc700a11495ad
@@ -503,7 +503,7 @@ F src/date.c 15082566229d4b1e5f24fdb490bf9bcc68824b911d70e3573ef075a1b9e2d26f
F src/dbpage.c 90661a87e1db8bfbc8d2ebbdcd3749651ddb287c555c07a28fb17c7c591ffb68
F src/dbstat.c 861e08690fcb0f2ee1165eff0060ea8d4f3e2ea10f80dab7d32ad70443a6ff2d
F src/delete.c a8e844af211a48b13b5b358be77a12c860c6a557c21990ad51a548e2536500ce
-F src/expr.c 5e247a8dfabb92e9fd10b78a675dc5d25430433dfd9e316471b4447b548635ba
+F src/expr.c d5be48b7f3f58026a7e8386ad8cb65c3550bbec79b0fe053a69594a81a5b20e9
F src/fault.c 460f3e55994363812d9d60844b2a6de88826e007
F src/fkey.c 94927f9b46d72a9cb858c208febf04ceb0a3270c5fa5fd0b7f436cf16e09f72a
F src/func.c a3407a6fbb0d4088d8d502e46f0ace63e0aeae7467ae23a9ca9815bbf9239761
@@ -550,14 +550,14 @@ F src/pragma.h e690a356c18e98414d2e870ea791c1be1545a714ba623719deb63f7f226d8bb7
F src/prepare.c fd940149c691684e7c1073c3787a7170e44852b02d1275d2e30a5b58e89cfcaf
F src/printf.c 05d8dfd2018bc4fc3ddb8b37eb97ccef7abf985643fa1caebdcf2916ca90fa32
F src/random.c 097dc8b31b8fba5a9aca1697aeb9fd82078ec91be734c16bffda620ced7ab83c
-F src/resolve.c 18d99e7146852d6064559561769fcca0743eb32b14a97da6dbed373a30ee0e76
+F src/resolve.c 7110fc3b5a4dec5d11559141c1906c4a125349fb602f541b05db3a3d448d4b95
F src/rowset.c ba9515a922af32abe1f7d39406b9d35730ed65efab9443dc5702693b60854c92
-F src/select.c 5ac7c08646ac5a03634da8a954645bdaa13d9bf692fb6b0b5fb3b55b19ab884e
+F src/select.c d48e4ddfd0ff34caca8650a668eb3c9ba75bab21264026f0e9a9c2271334090c
F src/shell.c.in eb7f10d5e2c47bd014d92ec5db1def21fcc1ed56ffaaa4ee715b6c37c370b47f
F src/sqlite.h.in 2a35f62185eb5e7ecc64a2f68442b538ce9be74f80f28a00abc24837edcf1c17
F src/sqlite3.rc 5121c9e10c3964d5755191c80dd1180c122fc3a8
F src/sqlite3ext.h f49e28c25bd941e79794db5415fdf7b202deb3bc072ed6f1ed273d578703684e
-F src/sqliteInt.h 5260c849fd1ac36d763f8efe9438437eb0bbde9a7956287e5e795cc514b30bc3
+F src/sqliteInt.h cfdfe65eeb87d3fa8319481fb541586f49e93d5715e9f487ae83362844a9abc7
F src/sqliteLimit.h d7323ffea5208c6af2734574bae933ca8ed2ab728083caa117c9738581a31657
F src/status.c 4a3da6d77eeb3531cb0dbdf7047772a2a1b99f98c69e90ce009c75fe6328b2c0
F src/table.c 0f141b58a16de7e2fbe81c308379e7279f4c6b50eb08efeec5892794a0ba30d1
@@ -617,14 +617,14 @@ F src/test_window.c cdae419fdcea5bad6dcd9368c685abdad6deb59e9fc8b84b153de513d394
F src/test_wsd.c 41cadfd9d97fe8e3e4e44f61a4a8ccd6f7ca8fe9
F src/threads.c 4ae07fa022a3dc7c5beb373cf744a85d3c5c6c3c
F src/tokenize.c a38f52058b517929e264094abd0b5fd1e8e145a1aa43bc6f6a72ae5218f96c98
-F src/treeview.c 67fc7d8960e525026eb7f8f4afae0d3652cc3f1260f5adeab062079e25bf0879
+F src/treeview.c e143d3def263f304066e87628eebc7c2921a12245541f09b61d0e45009091474
F src/trigger.c 372ada38f667c6823a3db15749eb668338e65c793394e55a37e56a489f2d1b55
F src/update.c 2cfaded82ca80ff56afb8c3ae5e88284e0824bfd86119827cc22481959f96f92
F src/upsert.c 8789047a8f0a601ea42fa0256d1ba3190c13746b6ba940fe2d25643a7e991937
F src/utf.c ee39565f0843775cc2c81135751ddd93eceb91a673ea2c57f61c76f288b041a0
F src/util.c 602fe229f32a96ceccae4f40824129669582096f7c355f53dbac156c9fecef23
F src/vacuum.c 6c38ddc52f0619865c91dae9c441d4d48bf3040d7dc1bc5b22da1e45547ed0b3
-F src/vdbe.c eefc5a96938cc113a95e36a42b626bf594a7f0b8bb56ae299accbbf015b973cd
+F src/vdbe.c 871c55b7eea607980465a3e9d5c1ba1af679789be5e9181af11ed2ff9ffd420f
F src/vdbe.h 89f5edb1422c8783a0b29db836e409876f2b3e847f78e2b21b1fbcc48a93f85f
F src/vdbeInt.h 5f3d0abcf30c2b7a6672ad4386f18be0fca9c9b2cefe18f85a2e3df74f2613bf
F src/vdbeapi.c 354c893f1500cf524cc45c32879b9c68893a28b77e3442c24668d6afe4236217
@@ -639,16 +639,16 @@ F src/vxworks.h d2988f4e5a61a4dfe82c6524dd3d6e4f2ce3cdb9
F src/wal.c b9df133a705093da8977da5eb202eaadb844839f1c7297c08d33471f5491843d
F src/wal.h c3aa7825bfa2fe0d85bef2db94655f99870a285778baa36307c0a16da32b226a
F src/walker.c f890a3298418d7cba3b69b8803594fdc484ea241206a8dfa99db6dd36f8cbb3b
-F src/where.c 3b23b363455f1d12cf36c7baa679b16bf1e48cfdd6cf0166bd52202823212f14
-F src/whereInt.h 41ce0a8c0368372d8422e420e05a1e037624ce52fae139c3c19538ee491fb4c0
-F src/wherecode.c b48476855e4802276e9d9aabb407609059220774b586c8c3a5a61e430aa0eb27
-F src/whereexpr.c 346ca19fa89bcb966feb9ae42324fe7636130757f34890aaa6ef922b96be17a5
+F src/where.c 793d09ef0d824efb953cdaff59102dcdd5390f8c59d1d5119ddfd26dcf5cd957
+F src/whereInt.h ea1e4b6639c4c32246f4c54b733143df76109894adf08bedee4f3999ece62c2d
+F src/wherecode.c e0f7b26a9c2de2cbaa635e5c1ef47b9c22250b0245a14a4b785ff4c61215fa13
+F src/whereexpr.c 174d4ad5be165c610c907abb779ef4a97974d22b84e1ce7898d2d9f6947249e5
F src/window.c 42a71595263dbd8ef8248218e4fc7d4b5ddccece52146ad48e079342d93f6f8f
F test/8_3_names.test ebbb5cd36741350040fd28b432ceadf495be25b2
F test/affinity2.test ce1aafc86e110685b324e9a763eab4f2a73f737842ec3b687bd965867de90627
-F test/affinity3.test eecb0dabee4b7765a8465439d5e99429279ffba23ca74a7eae270a452799f9e7
+F test/affinity3.test b5c19d504dec222c0dc66642673d23dce915d35737b68e74d9f237b80493eb53
F test/aggerror.test a867e273ef9e3d7919f03ef4f0e8c0d2767944f2
-F test/aggnested.test cc47afa5e11e0d6771a85a4993fa6ff721480ddb53ea538ec3fdbafb720bd505
+F test/aggnested.test 7269d07ac879fce161cb26c8fabe65cba5715742fac8a1fccac570dcdaf28f00
F test/alias.test 4529fbc152f190268a15f9384a5651bbbabc9d87
F test/all.test 2ecb8bbd52416642e41c9081182a8df05d42c75637afd4488aace78cc4b69e13
F test/alter.test 313073774ab5c3f2ef1d3f0d03757c9d3a81284ae7e1b4a6ca34db088f886896
@@ -704,10 +704,10 @@ F test/auth2.test 9eb7fce9f34bf1f50d3f366fb3e606be5a2000a1
F test/auth3.test 76d20a7fa136d63bcfcf8bcb65c0b1455ed71078d81f22bcd0550d3eb18594ab
F test/autoanalyze1.test b9cc3f32a990fa56669b668d237c6d53e983554ae80c0604992e18869a0b2dec
F test/autoinc.test 997d6f185f138229dc4251583a1d04816423dddc2fc034871a01aeb1d728cb39
-F test/autoindex1.test fe27af92eaf884bd9c38f94be3e8afa04ec494e5eefb189902026181a6175f5e
+F test/autoindex1.test 523b26034dc5e0c5ff0865055b4593f75863b82f17748dec9ca64bb8b267c502
F test/autoindex2.test 12ef578928102baaa0dc23ad397601a2f4ecb0df
F test/autoindex3.test 2d13958a5617e987624a428d7aed91bf51f322b49b476e3573fadec697ce6da5
-F test/autoindex4.test 75cb1191a552b8201351f5a50d160fcb9387a0fbbfb820c77798bfee7da3f8cf
+F test/autoindex4.test 5df39313526b6f22a26bd119bbd97ca69f28386ab3c671fc10568d921c41eb08
F test/autoindex5.test 2ee94f033b87ca0160e08d81034c507aff8e230df2627f0304fa309b2fee19a3
F test/autovacuum.test 00671369bbf96c6a49989a9425f5b78b94075d6a4b031e5e00000c2c32f365df
F test/autovacuum2.test 76f7eb4fe6a6bf6d33a196a7141dba98886d2fb53a268d7feca285d5da4759d7
@@ -752,7 +752,7 @@ F test/boundary3.tcl 23361e108a125dca9c4080c2feb884fe54d69243
F test/boundary3.test 56ef82096b4329aca2be74fa1e2b0f762ea0eb45
F test/boundary4.tcl 0bb4b1a94f4fc5ae59b79b9a2b7a140c405e2983
F test/boundary4.test 89e02fa66397b8a325d5eb102b5806f961f8ec4b
-F test/btree01.test 8e1ba2f857608ad8fbf9fcc11f33b15b083711162f9566b0a21fb573f2008593
+F test/btree01.test fef17d9e999ac4f04095948e3438fbe674f4e07bb2c63bb1cad41d87baee077f
F test/btree02.test 7555a5440453d900410160a52554fe6478af4faf53098f7235f1f443d5a1d6cc
F test/btreefault.test c2bcb542685eea44621275cfedbd8a13f65201e3
F test/busy.test 510dc6daaad18bcbbc085bcc6217d6dc418def5e73f72ce1475eea0cb7834727
@@ -777,7 +777,7 @@ F test/close.test eccbad8ecd611d974cbf47278c3d4e5874faf02d811338d5d348af42d56d64
F test/closure01.test 9905883f1b171a4638f98fc764879f154e214a306d3d8daf412a15e7f3a9b1e0
F test/coalesce.test cee0dccb9fbd2d494b77234bccf9dc6c6786eb91
F test/collate1.test 71a6f27fdc93a92f14d8ab80c05e1937656a5a03197e1a10157314554d630ce8
-F test/collate2.test 9aaa410a00734e48bcb27f3872617d6f69b2a621
+F test/collate2.test 471c6f74573382b89b0f8b88a05256faa52f7964f9e4799e76708a3b1ece6ba4
F test/collate3.test 89defc49983ddfbf0a0555aca8c0521a676f56a5
F test/collate4.test c953715fb498b87163e3e73dd94356bff1f317bd
F test/collate5.test 65d928034d30d2d263a80f6359f7549ee1598ec6
@@ -1008,7 +1008,7 @@ F test/fts3fault.test 798e45af84be7978ca33d5bdc94246eb44724db24174b5d8e9b1ac46c5
F test/fts3fault2.test 6a17a11d8034b1c4eca9f3091649273d56c49ff049e2173df8060f94341e9da0
F test/fts3first.test dbdedd20914c8d539aa3206c9b34a23775644641
F test/fts3fuzz001.test e3c7b0ce9b04cc02281dcc96812a277f02df03cd7dc082055d87e11eb18aaf56
-F test/fts3join.test b285c919559af5b093c51abb2c07ce7ec0156dbc9573f444b78dabd9f3040db2
+F test/fts3join.test ee25def5e763ea8879c19e74f862d5191410ccc7259338887a3685e97f512662
F test/fts3malloc.test b0e4c133b8d61d4f6d112d8110f8320e9e453ef6
F test/fts3matchinfo.test aa66cc50615578b30f6df9984819ae5b702511cf8a94251ec7c594096a703a4a
F test/fts3matchinfo2.test 00144e841704b8debfcdf6097969cd9f2a1cf759e2203cda42583648f2e6bf58
@@ -1110,7 +1110,7 @@ F test/index2.test f835d5e13ca163bd78c4459ca15fd2e4ed487407
F test/index3.test 51685f39345462b84fcf77eb8537af847fdf438cc96b05c45d6aaca4e473ade0
F test/index4.test ab92e736d5946840236cd61ac3191f91a7856bf6
F test/index5.test 8621491915800ec274609e42e02a97d67e9b13e7
-F test/index6.test 5a7ab531c692ff3b3d139ef8ea6709fab40f9c6862ed418b4976752a5481da3a
+F test/index6.test 6e5b6943f6a97a34898e48c4d0d4990caf55c12c00465a43a9c33d2fd9a3a820
F test/index7.test b238344318e0b4e42126717f6554f0e7dfd0b39cecad4b736039b43e1e3b6eb3
F test/index8.test caa097735c91dbc23d8a402f5e63a2a03c83840ba3928733ed7f9a03f8a912a3
F test/index9.test 0aa3e509dddf81f93380396e40e9bb386904c1054924ba8fa9bcdfe85a8e7721
@@ -1140,12 +1140,14 @@ F test/ioerr4.test f130fe9e71008577b342b8874d52984bd04ede2c
F test/ioerr5.test 2edfa4fb0f896f733071303b42224df8bedd9da4
F test/ioerr6.test a395a6ab144b26a9e3e21059a1ab6a7149cca65b
F test/istrue.test e7f285bb70282625c258e866ce6337d4c762922f5a300e1b50f958aef6e7d9c9
-F test/join.test 85e9c88bf4700b45a48a6362cd47e0c0aefc572629827c31aa58a5978cabdfc5
-F test/join2.test 9bdc615841b91c97a16d68bad9508aea11fa0c6b34e5689847bcc4dac70e4990
+F test/join.test d9c8cb2769b147b223f9dff8f694f56cfd9d0c097f8af9c7c6562b2e4ad256b5
+F test/join2.test 9751dac84a46a960281ca372fe2350252ef40286dde3c542540bccdea9a2d5c6
F test/join3.test 6f0c774ff1ba0489e6c88a3e77b9d3528fb4fda0
F test/join4.test 1a352e4e267114444c29266ce79e941af5885916
-F test/join5.test 0d63c7e43b3160b9d4b93f196ef83b6efc7751b9edd0d18c53a46fbec7a49cfc
+F test/join5.test c4df54e2e204d7f1417bfbdd21ca324b4b07415c647595cc47798eacfddc96d3
F test/join6.test f809c025fa253f9e150c0e9afd4cef8813257bceeb6f46e04041228c9403cc2c
+F test/join7.test e5c9b1b729d7e1d0b4195e99833e0ff0cf2d88e7fdd32b49af1044f4c76f72d9
+F test/join8.test 72248c33b44f8fc8614c739eff176fd8e18ffab04896b9ce6fd7933854dd7d25
F test/journal1.test c7b768041b7f494471531e17abc2f4f5ebf9e5096984f43ed17c4eb80ba34497
F test/journal2.test 9dac6b4ba0ca79c3b21446bbae993a462c2397c4
F test/journal3.test 7c3cf23ffc77db06601c1fcfc9743de8441cb77db9d1aa931863d94f5ffa140e
@@ -1711,7 +1713,7 @@ F test/vtab2.test 14d4ab26cee13ba6cf5c5601b158e4f57552d3b055cdd9406cf7f711e9c840
F test/vtab3.test b45f47d20f225ccc9c28dc915d92740c2dee311e
F test/vtab4.test 8e73ed268f3d596bc3590f45fc948fb40f28e9c3
F test/vtab5.test 889f444970393c73f1e077e2bdc5d845e157a391
-F test/vtab6.test 7167e8e526bc2e719e7818e18b2fd7bb8c455fa018b74e611943a86782e10125
+F test/vtab6.test fa609a4af96da30beceefa3cb624abe9be38c4747ab373d98179b24027d6b798
F test/vtab7.test 70c6f4a1d6177144a8236e4172d5fba92e683440374664ad1f04851fbb335d3c
F test/vtab8.test e19fa4a538fcd1bb66c22825fa8f71618fb13583
F test/vtab9.test ea58d2b95d61955f87226381716b2d0b1d4e4f9b
@@ -1769,7 +1771,7 @@ F test/walthread.test 14b20fcfa6ae152f5d8e12f5dc8a8a724b7ef189f5d8ef1e2ceab79f2a
F test/walvfs.test bccb3e0d235ef85e276f491d34db32c9ada1ea67be8d9f10aabe7b30319ec656
F test/wapp.tcl b440cd8cf57953d3a49e7ee81e6a18f18efdaf113b69f7d8482b0710a64566ec
F test/wapptest.tcl 899594e25684861d5b0c0880fb012364def50ef8097041b8ddf74be5ba7fa270 x
-F test/where.test f114842c1851d257a26770f2ad55119b084001c0e1b8c214f886f45152d37cd8
+F test/where.test 8c6bbd0cae8feae142a7946e3484a802fa566bacf38452b1c3e48cb77321f9a4
F test/where2.test 03c21a11e7b90e2845fc3c8b4002fc44cc2797fa74c86ee47d70bd7ea4f29ed6
F test/where3.test 5b4ffc0ac2ea0fe92f02b1244b7531522fe4d7bccf6fa8741d54e82c10e67753
F test/where4.test 4a371bfcc607f41d233701bdec33ac2972908ba8
@@ -1777,7 +1779,7 @@ F test/where5.test fdf66f96d29a064b63eb543e28da4dfdccd81ad2
F test/where6.test 5da5a98cec820d488e82708301b96cb8c18a258b
F test/where7.test 1c1bf436bf31b913d4764a2b62ac6e98b9681e5c7ae2b562605592a56b7e946b
F test/where8.test 461ca40265ed996a6305da99bb024b0e41602bb586acf544c08f95922358e49f
-F test/where9.test 1ffb75edc50a8faa6e7bd77f8221d783febb00b44b0bdb32fb48cec6e38eca95
+F test/where9.test 2db942671a002621eff4f713e347bb25243295f79d8990297cd160bebcfde3f7
F test/whereA.test 9d1077b117f1b68d5f739d94f36956c36cf995eb87bb19b77b2e81af020edd20
F test/whereB.test 0def95db3bdec220a731c7e4bec5930327c1d8c5
F test/whereC.test cae295158703cb3fc23bf1a108a9ab730efff0f6
@@ -1945,8 +1947,9 @@ F vsixtest/vsixtest.tcl 6a9a6ab600c25a91a7acc6293828957a386a8a93
F vsixtest/vsixtest.vcxproj.data 2ed517e100c66dc455b492e1a33350c1b20fbcdc
F vsixtest/vsixtest.vcxproj.filters 37e51ffedcdb064aad6ff33b6148725226cd608e
F vsixtest/vsixtest_TemporaryKey.pfx e5b1b036facdb453873e7084e1cae9102ccc67a0
-P bff4f083eb1c35544988493a5d73a42e646c4250b841f5aae38c2183f0867a0e
-R 1538c33a9ca8b52845c5bdc4ee31c46c
+P dbd8e2e46cfb2de0ebdbb62cda2fe669df3eda98f5d6112d541f581995b1361d d168f245ecf497368feea4697769930c00420ef47a584904dac85371b61fb78a
+R cd533386f7e40127c2a7f7341f982c4d
+T +closed d168f245ecf497368feea4697769930c00420ef47a584904dac85371b61fb78a
U drh
-Z 98d0ff758b95c26883d10f0f51771962
+Z cd041ad39f6b49ac02cacaab1e4031e1
# Remove this line to create a well-formed Fossil manifest.
diff --git a/manifest.uuid b/manifest.uuid
index 2a7d8f880..438edba24 100644
--- a/manifest.uuid
+++ b/manifest.uuid
@@ -1 +1 @@
-dbd8e2e46cfb2de0ebdbb62cda2fe669df3eda98f5d6112d541f581995b1361d \ No newline at end of file
+fa9d206f904280e3eafc6f4ba6c0c7325948364c62eeeb9f0fdc5825d622ec35 \ No newline at end of file
diff --git a/src/build.c b/src/build.c
index da862fa70..22f7a79e0 100644
--- a/src/build.c
+++ b/src/build.c
@@ -5067,14 +5067,33 @@ void sqlite3SrcListFuncArgs(Parse *pParse, SrcList *p, ExprList *pList){
** The operator is "natural cross join". The A and B operands are stored
** in p->a[0] and p->a[1], respectively. The parser initially stores the
** operator with A. This routine shifts that operator over to B.
+**
+** Additional changes:
+**
+** * All tables to the left of the right-most RIGHT JOIN are tagged with
+** JT_LTORJ (mnemonic: Left Table Of Right Join) so that the
+** code generator can easily tell that the table is part of
+** the left operand of at least one RIGHT JOIN.
*/
void sqlite3SrcListShiftJoinType(SrcList *p){
- if( p ){
- int i;
- for(i=p->nSrc-1; i>0; i--){
- p->a[i].fg.jointype = p->a[i-1].fg.jointype;
- }
+ if( p && p->nSrc>1 ){
+ int i = p->nSrc-1;
+ u8 allFlags = 0;
+ do{
+ allFlags |= p->a[i].fg.jointype = p->a[i-1].fg.jointype;
+ }while( (--i)>0 );
p->a[0].fg.jointype = 0;
+
+ /* All terms to the left of a RIGHT JOIN should be tagged with the
+ ** JT_LTORJ flags */
+ if( allFlags & JT_RIGHT ){
+ for(i=p->nSrc-1; ALWAYS(i>0) && (p->a[i].fg.jointype&JT_RIGHT)==0; i--){}
+ i--;
+ assert( i>=0 );
+ do{
+ p->a[i--].fg.jointype |= JT_LTORJ;
+ }while( i>=0 );
+ }
}
}
diff --git a/src/expr.c b/src/expr.c
index 2c00bb498..fcef002f2 100644
--- a/src/expr.c
+++ b/src/expr.c
@@ -2179,7 +2179,7 @@ Expr *sqlite3ExprSimplifiedAndOr(Expr *pExpr){
static int exprNodeIsConstant(Walker *pWalker, Expr *pExpr){
/* If pWalker->eCode is 2 then any term of the expression that comes from
- ** the ON or USING clauses of a left join disqualifies the expression
+ ** the ON or USING clauses of an outer join disqualifies the expression
** from being considered constant. */
if( pWalker->eCode==2 && ExprHasProperty(pExpr, EP_FromJoin) ){
pWalker->eCode = 0;
@@ -5817,7 +5817,7 @@ static int impliesNotNullRow(Walker *pWalker, Expr *pExpr){
** in an incorrect answer.
**
** Terms of p that are marked with EP_FromJoin (and hence that come from
-** the ON or USING clauses of LEFT JOINS) are excluded from the analysis.
+** the ON or USING clauses of OUTER JOINS) are excluded from the analysis.
**
** This routine is used to check if a LEFT JOIN can be converted into
** an ordinary JOIN. The p argument is the WHERE clause. If the WHERE
diff --git a/src/resolve.c b/src/resolve.c
index 30785ca70..ac00564da 100644
--- a/src/resolve.c
+++ b/src/resolve.c
@@ -367,9 +367,7 @@ static int lookupName(
pExpr->iTable = pMatch->iCursor;
assert( ExprUseYTab(pExpr) );
pExpr->y.pTab = pMatch->pTab;
- /* RIGHT JOIN not (yet) supported */
- assert( (pMatch->fg.jointype & JT_RIGHT)==0 );
- if( (pMatch->fg.jointype & JT_LEFT)!=0 ){
+ if( (pMatch->fg.jointype & (JT_LEFT|JT_LTORJ))!=0 ){
ExprSetProperty(pExpr, EP_CanBeNull);
}
pSchema = pExpr->y.pTab->pSchema;
diff --git a/src/select.c b/src/select.c
index 28934ffd9..2e5c1ac97 100644
--- a/src/select.c
+++ b/src/select.c
@@ -298,13 +298,9 @@ int sqlite3JoinType(Parse *pParse, Token *pA, Token *pB, Token *pC){
const char *zSp2 = " ";
if( pB==0 ){ zSp1++; }
if( pC==0 ){ zSp2++; }
- sqlite3ErrorMsg(pParse, "unknown or unsupported join type: "
+ sqlite3ErrorMsg(pParse, "unknown join type: "
"%T%s%T%s%T", pA, zSp1, pB, zSp2, pC);
jointype = JT_INNER;
- }else if( (jointype & JT_RIGHT)!=0 ){
- sqlite3ErrorMsg(pParse,
- "RIGHT and FULL OUTER JOINs are not currently supported");
- jointype = JT_INNER;
}
return jointype;
}
@@ -377,7 +373,7 @@ static void addWhereTerm(
int iColLeft, /* Index of column in first table */
int iRight, /* Index of second table in pSrc */
int iColRight, /* Index of column in second table */
- int isOuterJoin, /* True if this is an OUTER join */
+ u32 joinType, /* EP_FromJoin or EP_InnerJoin */
Expr **ppWhere /* IN/OUT: The WHERE clause to add to */
){
sqlite3 *db = pParse->db;
@@ -397,8 +393,8 @@ static void addWhereTerm(
assert( pE2!=0 || pEq==0 ); /* Due to db->mallocFailed test
** in sqlite3DbMallocRawNN() called from
** sqlite3PExpr(). */
- if( pEq && isOuterJoin ){
- ExprSetProperty(pEq, EP_FromJoin);
+ if( pEq ){
+ ExprSetProperty(pEq, joinType);
assert( !ExprHasProperty(pEq, EP_TokenOnly|EP_Reduced) );
ExprSetVVAProperty(pEq, EP_NoReduce);
pEq->w.iJoin = pE2->iTable;
@@ -412,7 +408,7 @@ static void addWhereTerm(
** expression.
**
** The EP_FromJoin property is used on terms of an expression to tell
-** the LEFT OUTER JOIN processing logic that this term is part of the
+** the OUTER JOIN processing logic that this term is part of the
** join restriction specified in the ON or USING clause and not a part
** of the more general WHERE clause. These terms are moved over to the
** WHERE clause during join processing but we need to remember that they
@@ -432,9 +428,10 @@ static void addWhereTerm(
** after the t1 loop and rows with t1.x!=5 will never appear in
** the output, which is incorrect.
*/
-void sqlite3SetJoinExpr(Expr *p, int iTable){
+void sqlite3SetJoinExpr(Expr *p, int iTable, u32 joinFlag){
+ assert( joinFlag==EP_FromJoin || joinFlag==EP_InnerJoin );
while( p ){
- ExprSetProperty(p, EP_FromJoin);
+ ExprSetProperty(p, joinFlag);
assert( !ExprHasProperty(p, EP_TokenOnly|EP_Reduced) );
ExprSetVVAProperty(p, EP_NoReduce);
p->w.iJoin = iTable;
@@ -443,11 +440,11 @@ void sqlite3SetJoinExpr(Expr *p, int iTable){
if( p->x.pList ){
int i;
for(i=0; i<p->x.pList->nExpr; i++){
- sqlite3SetJoinExpr(p->x.pList->a[i].pExpr, iTable);
+ sqlite3SetJoinExpr(p->x.pList->a[i].pExpr, iTable, joinFlag);
}
}
}
- sqlite3SetJoinExpr(p->pLeft, iTable);
+ sqlite3SetJoinExpr(p->pLeft, iTable, joinFlag);
p = p->pRight;
}
}
@@ -463,6 +460,7 @@ static void unsetJoinExpr(Expr *p, int iTable){
if( ExprHasProperty(p, EP_FromJoin)
&& (iTable<0 || p->w.iJoin==iTable) ){
ExprClearProperty(p, EP_FromJoin);
+ ExprSetProperty(p, EP_InnerJoin);
}
if( p->op==TK_COLUMN && p->iTable==iTable ){
ExprClearProperty(p, EP_CanBeNull);
@@ -506,10 +504,10 @@ static int sqliteProcessJoin(Parse *pParse, Select *p){
pRight = &pLeft[1];
for(i=0; i<pSrc->nSrc-1; i++, pRight++, pLeft++){
Table *pRightTab = pRight->pTab;
- int isOuter;
+ u32 joinType;
if( NEVER(pLeft->pTab==0 || pRightTab==0) ) continue;
- isOuter = (pRight->fg.jointype & JT_OUTER)!=0;
+ joinType = (pRight->fg.jointype & JT_OUTER)!=0 ? EP_FromJoin : EP_InnerJoin;
/* When the NATURAL keyword is present, add WHERE clause terms for
** every column that the two tables have in common.
@@ -529,7 +527,7 @@ static int sqliteProcessJoin(Parse *pParse, Select *p){
zName = pRightTab->aCol[j].zCnName;
if( tableAndColumnIndex(pSrc, i+1, zName, &iLeft, &iLeftCol, 1) ){
addWhereTerm(pParse, pSrc, iLeft, iLeftCol, i+1, j,
- isOuter, &p->pWhere);
+ joinType, &p->pWhere);
}
}
}
@@ -560,7 +558,7 @@ static int sqliteProcessJoin(Parse *pParse, Select *p){
return 1;
}
addWhereTerm(pParse, pSrc, iLeft, iLeftCol, i+1, iRightCol,
- isOuter, &p->pWhere);
+ joinType, &p->pWhere);
}
}
@@ -568,7 +566,7 @@ static int sqliteProcessJoin(Parse *pParse, Select *p){
** an AND operator.
*/
else if( pRight->u3.pOn ){
- if( isOuter ) sqlite3SetJoinExpr(pRight->u3.pOn, pRight->iCursor);
+ sqlite3SetJoinExpr(pRight->u3.pOn, pRight->iCursor, joinType);
p->pWhere = sqlite3ExprAnd(pParse, p->pWhere, pRight->u3.pOn);
pRight->u3.pOn = 0;
}
@@ -3656,12 +3654,40 @@ static int multiSelectOrderBy(
**
** All references to columns in table iTable are to be replaced by corresponding
** expressions in pEList.
+**
+** ## About "isOuterJoin":
+**
+** The isOuterJoin column indicates that the replacement will occur into a
+** position in the parent that NULL-able due to an OUTER JOIN. Either the
+** target slot in the parent is the right operand of a LEFT JOIN, or one of
+** the left operands of a RIGHT JOIN. In either case, we need to potentially
+** bypass the substituted expression with OP_IfNullRow.
+**
+** Suppose the original expression integer constant. Even though the table
+** has the nullRow flag set, because the expression is an integer constant,
+** it will not be NULLed out. So instead, we insert an OP_IfNullRow opcode
+** that checks to see if the nullRow flag is set on the table. If the nullRow
+** flag is set, then the value in the register is set to NULL and the original
+** expression is bypassed. If the nullRow flag is not set, then the original
+** expression runs to populate the register.
+**
+** Example where this is needed:
+**
+** CREATE TABLE t1(a INTEGER PRIMARY KEY, b INT);
+** CREATE TABLE t2(x INT UNIQUE);
+**
+** SELECT a,b,m,x FROM t1 LEFT JOIN (SELECT 59 AS m,x FROM t2) ON b=x;
+**
+** When the subquery on the right side of the LEFT JOIN is flattened, we
+** have to add OP_IfNullRow in front of the OP_Integer that implements the
+** "m" value of the subquery so that a NULL will be loaded instead of 59
+** when processing a non-matched row of the left.
*/
typedef struct SubstContext {
Parse *pParse; /* The parsing context */
int iTable; /* Replace references to this table */
int iNewTable; /* New table number */
- int isLeftJoin; /* Add TK_IF_NULL_ROW opcodes on each replacement */
+ int isOuterJoin; /* Add TK_IF_NULL_ROW opcodes on each replacement */
ExprList *pEList; /* Replacement expressions */
} SubstContext;
@@ -3711,7 +3737,7 @@ static Expr *substExpr(
sqlite3VectorErrorMsg(pSubst->pParse, pCopy);
}else{
sqlite3 *db = pSubst->pParse->db;
- if( pSubst->isLeftJoin && pCopy->op!=TK_COLUMN ){
+ if( pSubst->isOuterJoin && pCopy->op!=TK_COLUMN ){
memset(&ifNullRow, 0, sizeof(ifNullRow));
ifNullRow.op = TK_IF_NULL_ROW;
ifNullRow.pLeft = pCopy;
@@ -3725,11 +3751,12 @@ static Expr *substExpr(
sqlite3ExprDelete(db, pNew);
return pExpr;
}
- if( pSubst->isLeftJoin ){
+ if( pSubst->isOuterJoin ){
ExprSetProperty(pNew, EP_CanBeNull);
}
- if( ExprHasProperty(pExpr,EP_FromJoin) ){
- sqlite3SetJoinExpr(pNew, pExpr->w.iJoin);
+ if( ExprHasProperty(pExpr,EP_FromJoin|EP_InnerJoin) ){
+ sqlite3SetJoinExpr(pNew, pExpr->w.iJoin,
+ pExpr->flags & (EP_FromJoin|EP_InnerJoin));
}
sqlite3ExprDelete(db, pExpr);
pExpr = pNew;
@@ -3979,6 +4006,7 @@ static void renumberCursors(
** table and
** (3c) the outer query may not be an aggregate.
** (3d) the outer query may not be DISTINCT.
+** See also (26) for restrictions on RIGHT JOIN.
**
** (4) The subquery can not be DISTINCT.
**
@@ -4077,6 +4105,9 @@ static void renumberCursors(
** function in the select list or ORDER BY clause, flattening
** is not attempted.
**
+** (26) The subquery may not be the right operand of a RIGHT JOIN.
+** See also (3) for restrictions on LEFT JOIN.
+**
**
** In this routine, the "p" parameter is a pointer to the outer query.
** The subquery is p->pSrc->a[iFrom]. isAgg is true if the outer query
@@ -4102,7 +4133,7 @@ static int flattenSubquery(
SrcList *pSubSrc; /* The FROM clause of the subquery */
int iParent; /* VDBE cursor number of the pSub result set temp table */
int iNewParent = -1;/* Replacement table for iParent */
- int isLeftJoin = 0; /* True if pSub is the right side of a LEFT JOIN */
+ int isOuterJoin = 0; /* True if pSub is the right side of a LEFT JOIN */
int i; /* Loop counter */
Expr *pWhere; /* The WHERE clause */
SrcItem *pSubitem; /* The subquery */
@@ -4175,23 +4206,24 @@ static int flattenSubquery(
**
** See also tickets #306, #350, and #3300.
*/
- if( (pSubitem->fg.jointype & JT_OUTER)!=0 ){
- isLeftJoin = 1;
- if( pSubSrc->nSrc>1 /* (3a) */
- || isAgg /* (3b) */
- || IsVirtual(pSubSrc->a[0].pTab) /* (3c) */
- || (p->selFlags & SF_Distinct)!=0 /* (3d) */
+ if( (pSubitem->fg.jointype & (JT_OUTER|JT_LTORJ))!=0 ){
+ if( pSubSrc->nSrc>1 /* (3a) */
+ || isAgg /* (3b) */
+ || IsVirtual(pSubSrc->a[0].pTab) /* (3c) */
+ || (p->selFlags & SF_Distinct)!=0 /* (3d) */
+ || (pSubitem->fg.jointype & JT_RIGHT)!=0 /* (26) */
){
return 0;
}
+ isOuterJoin = 1;
}
#ifdef SQLITE_EXTRA_IFNULLROW
else if( iFrom>0 && !isAgg ){
- /* Setting isLeftJoin to -1 causes OP_IfNullRow opcodes to be generated for
+ /* Setting isOuterJoin to -1 causes OP_IfNullRow opcodes to be generated for
** every reference to any result column from subquery in a join, even
** though they are not necessary. This will stress-test the OP_IfNullRow
** opcode. */
- isLeftJoin = -1;
+ isOuterJoin = -1;
}
#endif
@@ -4204,7 +4236,7 @@ static int flattenSubquery(
if( pSub->pOrderBy ){
return 0; /* Restriction (20) */
}
- if( isAgg || (p->selFlags & SF_Distinct)!=0 || isLeftJoin>0 ){
+ if( isAgg || (p->selFlags & SF_Distinct)!=0 || isOuterJoin>0 ){
return 0; /* (17d1), (17d2), or (17f) */
}
for(pSub1=pSub; pSub1; pSub1=pSub1->pPrior){
@@ -4372,6 +4404,7 @@ static int flattenSubquery(
for(pParent=p; pParent; pParent=pParent->pPrior, pSub=pSub->pPrior){
int nSubSrc;
u8 jointype = 0;
+ u8 ltorj = pSrc->a[iFrom].fg.jointype & JT_LTORJ;
assert( pSub!=0 );
pSubSrc = pSub->pSrc; /* FROM clause of subquery */
nSubSrc = pSubSrc->nSrc; /* Number of terms in subquery FROM clause */
@@ -4410,10 +4443,12 @@ static int flattenSubquery(
if( pItem->fg.isUsing ) sqlite3IdListDelete(db, pItem->u3.pUsing);
assert( pItem->fg.isTabFunc==0 );
*pItem = pSubSrc->a[i];
+ pItem->fg.jointype |= ltorj;
iNewParent = pSubSrc->a[i].iCursor;
memset(&pSubSrc->a[i], 0, sizeof(pSubSrc->a[i]));
}
- pSrc->a[iFrom].fg.jointype = jointype;
+ pSrc->a[iFrom].fg.jointype &= JT_LTORJ;
+ pSrc->a[iFrom].fg.jointype |= jointype | ltorj;
/* Now begin substituting subquery result set expressions for
** references to the iParent in the outer query.
@@ -4448,8 +4483,8 @@ static int flattenSubquery(
}
pWhere = pSub->pWhere;
pSub->pWhere = 0;
- if( isLeftJoin>0 ){
- sqlite3SetJoinExpr(pWhere, iNewParent);
+ if( isOuterJoin>0 ){
+ sqlite3SetJoinExpr(pWhere, iNewParent, EP_FromJoin);
}
if( pWhere ){
if( pParent->pWhere ){
@@ -4463,7 +4498,7 @@ static int flattenSubquery(
x.pParse = pParse;
x.iTable = iParent;
x.iNewTable = iNewParent;
- x.isLeftJoin = isLeftJoin;
+ x.isOuterJoin = isOuterJoin;
x.pEList = pSub->pEList;
substSelect(&x, pParent, 0);
}
@@ -4924,7 +4959,7 @@ static int pushDownWhereTerms(
x.pParse = pParse;
x.iTable = iCursor;
x.iNewTable = iCursor;
- x.isLeftJoin = 0;
+ x.isOuterJoin = 0;
x.pEList = pSubq->pEList;
pNew = substExpr(&x, pNew);
#ifndef SQLITE_OMIT_WINDOWFUNC
@@ -6503,7 +6538,7 @@ int sqlite3Select(
/* Convert LEFT JOIN into JOIN if there are terms of the right table
** of the LEFT JOIN used in the WHERE clause.
*/
- if( (pItem->fg.jointype & JT_LEFT)!=0
+ if( (pItem->fg.jointype & (JT_LEFT|JT_RIGHT))==JT_LEFT
&& sqlite3ExprImpliesNonNullRow(p->pWhere, pItem->iCursor)
&& OptimizationEnabled(db, SQLITE_SimplifyJoin)
){
@@ -6589,7 +6624,7 @@ int sqlite3Select(
&& i==0
&& (p->selFlags & SF_ComplexResult)!=0
&& (pTabList->nSrc==1
- || (pTabList->a[1].fg.jointype&(JT_LEFT|JT_CROSS))!=0)
+ || (pTabList->a[1].fg.jointype&(JT_OUTER|JT_CROSS))!=0)
){
continue;
}
@@ -6711,6 +6746,7 @@ int sqlite3Select(
if( OptimizationEnabled(db, SQLITE_PushDown)
&& (pItem->fg.isCte==0
|| (pItem->u2.pCteUse->eM10d!=M10d_Yes && pItem->u2.pCteUse->nUse<2))
+ && (pItem->fg.jointype & JT_RIGHT)==0
&& pushDownWhereTerms(pParse, pSub, p->pWhere, pItem->iCursor,
(pItem->fg.jointype & JT_OUTER)!=0)
){
@@ -6731,18 +6767,19 @@ int sqlite3Select(
/* Generate code to implement the subquery
**
- ** The subquery is implemented as a co-routine if:
+ ** The subquery is implemented as a co-routine all of the following are
+ ** true:
+ **
** (1) the subquery is guaranteed to be the outer loop (so that
** it does not need to be computed more than once), and
** (2) the subquery is not a CTE that should be materialized
- **
- ** TODO: Are there other reasons beside (1) and (2) to use a co-routine
- ** implementation?
+ ** (3) the subquery is not part of a left operand for a RIGHT JOIN
*/
if( i==0
&& (pTabList->nSrc==1
- || (pTabList->a[1].fg.jointype&(JT_LEFT|JT_CROSS))!=0) /* (1) */
- && (pItem->fg.isCte==0 || pItem->u2.pCteUse->eM10d!=M10d_Yes) /* (2) */
+ || (pTabList->a[1].fg.jointype&(JT_OUTER|JT_CROSS))!=0) /* (1) */
+ && (pItem->fg.isCte==0 || pItem->u2.pCteUse->eM10d!=M10d_Yes) /* (2) */
+ && (pTabList->a[0].fg.jointype & JT_LTORJ)==0 /* (3) */
){
/* Implement a co-routine that will return a single row of the result
** set on each invocation.
diff --git a/src/sqliteInt.h b/src/sqliteInt.h
index 42554d263..330d58fa4 100644
--- a/src/sqliteInt.h
+++ b/src/sqliteInt.h
@@ -2887,7 +2887,7 @@ struct Expr {
#define EP_ConstFunc 0x080000 /* A SQLITE_FUNC_CONSTANT or _SLOCHNG function */
#define EP_CanBeNull 0x100000 /* Can be null despite NOT NULL constraint */
#define EP_Subquery 0x200000 /* Tree contains a TK_SELECT operator */
- /* 0x400000 // Available */
+#define EP_InnerJoin 0x400000 /* Originates in ON/USING of an inner join */
#define EP_Leaf 0x800000 /* Expr.pLeft, .pRight, .u.pSelect all NULL */
#define EP_WinFunc 0x1000000 /* TK_FUNCTION with Expr.y.pWin set */
#define EP_Subrtn 0x2000000 /* Uses Expr.y.sub. TK_IN, _SELECT, or _EXISTS */
@@ -3132,13 +3132,14 @@ struct SrcList {
/*
** Permitted values of the SrcList.a.jointype field
*/
-#define JT_INNER 0x0001 /* Any kind of inner or cross join */
-#define JT_CROSS 0x0002 /* Explicit use of the CROSS keyword */
-#define JT_NATURAL 0x0004 /* True for a "natural" join */
-#define JT_LEFT 0x0008 /* Left outer join */
-#define JT_RIGHT 0x0010 /* Right outer join */
-#define JT_OUTER 0x0020 /* The "OUTER" keyword is present */
-#define JT_ERROR 0x0040 /* unknown or unsupported join type */
+#define JT_INNER 0x01 /* Any kind of inner or cross join */
+#define JT_CROSS 0x02 /* Explicit use of the CROSS keyword */
+#define JT_NATURAL 0x04 /* True for a "natural" join */
+#define JT_LEFT 0x08 /* Left outer join */
+#define JT_RIGHT 0x10 /* Right outer join */
+#define JT_OUTER 0x20 /* The "OUTER" keyword is present */
+#define JT_LTORJ 0x40 /* One of the LEFT operands of a RIGHT JOIN */
+#define JT_ERROR 0x80 /* unknown or unsupported join type */
/*
@@ -3162,7 +3163,7 @@ struct SrcList {
#define WHERE_SORTBYGROUP 0x0200 /* Support sqlite3WhereIsSorted() */
#define WHERE_AGG_DISTINCT 0x0400 /* Query is "SELECT agg(DISTINCT ...)" */
#define WHERE_ORDERBY_LIMIT 0x0800 /* ORDERBY+LIMIT on the inner loop */
- /* 0x1000 not currently used */
+#define WHERE_RIGHT_JOIN 0x1000 /* Processing a RIGHT JOIN */
/* 0x2000 not currently used */
#define WHERE_USE_LIMIT 0x4000 /* Use the LIMIT in cost estimates */
/* 0x8000 not currently used */
@@ -4828,7 +4829,7 @@ void sqlite3MaterializeView(Parse*, Table*, Expr*, ExprList*,Expr*,int);
int sqlite3JoinType(Parse*, Token*, Token*, Token*);
int sqlite3ColumnIndex(Table *pTab, const char *zCol);
-void sqlite3SetJoinExpr(Expr*,int);
+void sqlite3SetJoinExpr(Expr*,int,u32);
void sqlite3CreateForeignKey(Parse*, ExprList*, Token*, ExprList*, int);
void sqlite3DeferForeignKey(Parse*, int);
#ifndef SQLITE_OMIT_AUTHORIZATION
diff --git a/src/treeview.c b/src/treeview.c
index c3ecdefef..459316190 100644
--- a/src/treeview.c
+++ b/src/treeview.c
@@ -144,11 +144,18 @@ void sqlite3TreeViewSrcList(TreeView *pView, const SrcList *pSrc){
sqlite3_str_appendf(&x, " tab=%Q nCol=%d ptr=%p used=%llx",
pItem->pTab->zName, pItem->pTab->nCol, pItem->pTab, pItem->colUsed);
}
- if( pItem->fg.jointype & JT_LEFT ){
+ if( (pItem->fg.jointype & (JT_LEFT|JT_RIGHT))==(JT_LEFT|JT_RIGHT) ){
+ sqlite3_str_appendf(&x, " FULL-OUTER-JOIN");
+ }else if( pItem->fg.jointype & JT_LEFT ){
sqlite3_str_appendf(&x, " LEFT-JOIN");
+ }else if( pItem->fg.jointype & JT_RIGHT ){
+ sqlite3_str_appendf(&x, " RIGHT-JOIN");
}else if( pItem->fg.jointype & JT_CROSS ){
sqlite3_str_appendf(&x, " CROSS-JOIN");
}
+ if( pItem->fg.jointype & JT_LTORJ ){
+ sqlite3_str_appendf(&x, " LTORJ");
+ }
if( pItem->fg.fromDDL ){
sqlite3_str_appendf(&x, " DDL");
}
diff --git a/src/vdbe.c b/src/vdbe.c
index 18286c297..45a47756e 100644
--- a/src/vdbe.c
+++ b/src/vdbe.c
@@ -993,8 +993,12 @@ jump_to_p2:
/* Opcode: Return P1 P2 P3 * *
**
-** Jump to the next instruction after the address in register P1. After
-** the jump, register P1 becomes undefined.
+** Jump to the next instruction after the address stored in register P1.
+**
+** It used to be that after the jump, register P1 would become undefined.
+** However, for the subroutine used for the inner loop of a RIGHT JOIN,
+** it is useful for R1 register to be unchanged, so that is what happens
+** now.
**
** P2 is not used by the byte-code engine. However, if P2 is positive
** and also less than the current address, then the "EXPLAIN" output
@@ -1012,7 +1016,7 @@ case OP_Return: { /* in1 */
pIn1 = &aMem[pOp->p1];
assert( pIn1->flags==MEM_Int );
pOp = &aOp[pIn1->u.i];
- pIn1->flags = MEM_Undefined;
+ /* pIn1->flags = MEM_Undefined; */
break;
}
diff --git a/src/where.c b/src/where.c
index 2e97509e0..1ce569d37 100644
--- a/src/where.c
+++ b/src/where.c
@@ -756,13 +756,13 @@ static int termCanDriveIndex(
char aff;
if( pTerm->leftCursor!=pSrc->iCursor ) return 0;
if( (pTerm->eOperator & (WO_EQ|WO_IS))==0 ) return 0;
- if( (pSrc->fg.jointype & JT_LEFT)
+ if( (pSrc->fg.jointype & (JT_LEFT|JT_LTORJ))!=0
&& !ExprHasProperty(pTerm->pExpr, EP_FromJoin)
&& (pTerm->eOperator & WO_IS)
){
/* Cannot use an IS term from the WHERE clause as an index driver for
- ** the RHS of a LEFT JOIN. Such a term can only be used if it is from
- ** the ON clause. */
+ ** the RHS of a LEFT JOIN or for the LHS of a RIGHT JOIN. Such a term
+ ** can only be used if it is from the ON clause. */
return 0;
}
if( (pTerm->prereqRight & notReady)!=0 ) return 0;
@@ -832,7 +832,8 @@ static SQLITE_NOINLINE void constructAutomaticIndex(
** WHERE clause (or the ON clause of a LEFT join) that constrain which
** rows of the target table (pSrc) that can be used. */
if( (pTerm->wtFlags & TERM_VIRTUAL)==0
- && ((pSrc->fg.jointype&JT_LEFT)==0 || ExprHasProperty(pExpr,EP_FromJoin))
+ && ((pSrc->fg.jointype&(JT_LEFT|JT_LTORJ))==0
+ || ExprHasProperty(pExpr,EP_FromJoin))
&& sqlite3ExprIsTableConstant(pExpr, pSrc->iCursor)
){
pPartial = sqlite3ExprAnd(pParse, pPartial,
@@ -1105,7 +1106,7 @@ static SQLITE_NOINLINE void sqlite3ConstructBloomFilter(
const SrcItem *pTabItem;
pLevel = &pWInfo->a[iLevel];
pTabItem = &pWInfo->pTabList->a[pLevel->iFrom];
- if( pTabItem->fg.jointype & JT_LEFT ) continue;
+ if( pTabItem->fg.jointype & (JT_LEFT|JT_LTORJ) ) continue;
pLoop = pLevel->pWLoop;
if( NEVER(pLoop==0) ) continue;
if( pLoop->prereq & notReady ) continue;
@@ -1178,9 +1179,10 @@ static sqlite3_index_info *allocateIndexInfo(
assert( pTerm->u.x.leftColumn<pTab->nCol );
/* tag-20191211-002: WHERE-clause constraints are not useful to the
- ** right-hand table of a LEFT JOIN. See tag-20191211-001 for the
+ ** right-hand table of a LEFT JOIN nor to the left-hand table of a
+ ** RIGHT JOIN. See tag-20191211-001 for the
** equivalent restriction for ordinary tables. */
- if( (pSrc->fg.jointype & JT_LEFT)!=0
+ if( (pSrc->fg.jointype & (JT_LEFT|JT_LTORJ))!=0
&& !ExprHasProperty(pTerm->pExpr, EP_FromJoin)
){
continue;
@@ -2621,10 +2623,11 @@ static void whereLoopOutputAdjust(
**
** 2022-03-24: Self-culling only applies if either the extra terms
** are straight comparison operators that are non-true with NULL
- ** operand, or if the loop is not a LEFT JOIN.
+ ** operand, or if the loop is not an OUTER JOIN.
*/
if( (pTerm->eOperator & 0x3f)!=0
- || (pWC->pWInfo->pTabList->a[pLoop->iTab].fg.jointype & JT_LEFT)==0
+ || (pWC->pWInfo->pTabList->a[pLoop->iTab].fg.jointype
+ & (JT_LEFT|JT_LTORJ))==0
){
pLoop->wsFlags |= WHERE_SELFCULL;
}
@@ -2831,9 +2834,10 @@ static int whereLoopAddBtreeIndex(
if( pTerm->wtFlags & TERM_LIKEOPT && pTerm->eOperator==WO_LT ) continue;
/* tag-20191211-001: Do not allow constraints from the WHERE clause to
- ** be used by the right table of a LEFT JOIN. Only constraints in the
+ ** be used by the right table of a LEFT JOIN nor by the left table of a
+ ** RIGHT JOIN. Only constraints in the
** ON clause are allowed. See tag-20191211-002 for the vtab equivalent. */
- if( (pSrc->fg.jointype & JT_LEFT)!=0
+ if( (pSrc->fg.jointype & (JT_LEFT|JT_LTORJ))!=0
&& !ExprHasProperty(pTerm->pExpr, EP_FromJoin)
){
continue;
@@ -3320,6 +3324,7 @@ static int whereLoopAddBtree(
&& HasRowid(pTab) /* Not WITHOUT ROWID table. (FIXME: Why not?) */
&& !pSrc->fg.isCorrelated /* Not a correlated subquery */
&& !pSrc->fg.isRecursive /* Not a recursive common table expression. */
+ && (pSrc->fg.jointype & JT_RIGHT)==0 /* Not the right tab of a RIGHT JOIN */
){
/* Generate auto-index WhereLoops */
LogEst rLogSize; /* Logarithm of the number of rows in the table */
@@ -4011,6 +4016,9 @@ static int whereLoopAddOr(
pItem = pWInfo->pTabList->a + pNew->iTab;
iCur = pItem->iCursor;
+ /* The multi-index OR optimization does not work for RIGHT and FULL JOIN */
+ if( pItem->fg.jointype & JT_RIGHT ) return SQLITE_OK;
+
for(pTerm=pWC->a; pTerm<pWCEnd && rc==SQLITE_OK; pTerm++){
if( (pTerm->eOperator & WO_OR)!=0
&& (pTerm->u.pOrInfo->indexable & pNew->maskSelf)!=0
@@ -4135,9 +4143,9 @@ static int whereLoopAddAll(WhereLoopBuilder *pBuilder){
pNew->iTab = iTab;
pBuilder->iPlanLimit += SQLITE_QUERY_PLANNER_LIMIT_INCR;
pNew->maskSelf = sqlite3WhereGetMask(&pWInfo->sMaskSet, pItem->iCursor);
- if( (pItem->fg.jointype & (JT_LEFT|JT_CROSS))!=0 ){
+ if( (pItem->fg.jointype & (JT_OUTER|JT_CROSS))!=0 ){
/* This condition is true when pItem is the FROM clause term on the
- ** right-hand-side of a LEFT or CROSS JOIN. */
+ ** right-hand-side of a OUTER or CROSS JOIN. */
mPrereq = mPrior;
}else{
mPrereq = 0;
@@ -4146,7 +4154,7 @@ static int whereLoopAddAll(WhereLoopBuilder *pBuilder){
if( IsVirtual(pItem->pTab) ){
SrcItem *p;
for(p=&pItem[1]; p<pEnd; p++){
- if( mUnusable || (p->fg.jointype & (JT_LEFT|JT_CROSS)) ){
+ if( mUnusable || (p->fg.jointype & (JT_OUTER|JT_CROSS)) ){
mUnusable |= sqlite3WhereGetMask(&pWInfo->sMaskSet, p->iCursor);
}
}
@@ -5762,8 +5770,10 @@ WhereInfo *sqlite3WhereBegin(
/* noop */
}else
#endif
- if( (pLoop->wsFlags & WHERE_IDX_ONLY)==0
- && (wctrlFlags & WHERE_OR_SUBCLAUSE)==0 ){
+ if( ((pLoop->wsFlags & WHERE_IDX_ONLY)==0
+ && (wctrlFlags & WHERE_OR_SUBCLAUSE)==0)
+ || (pTabItem->fg.jointype & (JT_LTORJ|JT_RIGHT))!=0
+ ){
int op = OP_OpenRead;
if( pWInfo->eOnePass!=ONEPASS_OFF ){
op = OP_OpenWrite;
@@ -5865,6 +5875,37 @@ WhereInfo *sqlite3WhereBegin(
}
}
if( iDb>=0 ) sqlite3CodeVerifySchema(pParse, iDb);
+ if( (pTabItem->fg.jointype & JT_RIGHT)!=0
+ && (pLevel->pRJ = sqlite3WhereMalloc(pWInfo, sizeof(WhereRightJoin)))!=0
+ ){
+ WhereRightJoin *pRJ = pLevel->pRJ;
+ pRJ->iMatch = pParse->nTab++;
+ pRJ->regBloom = ++pParse->nMem;
+ sqlite3VdbeAddOp2(v, OP_Blob, 65536, pRJ->regBloom);
+ pRJ->regReturn = ++pParse->nMem;
+ pRJ->addrInit = sqlite3VdbeAddOp2(v, OP_Integer, 0, pRJ->regReturn);
+ assert( pTab==pTabItem->pTab );
+ if( HasRowid(pTab) ){
+ KeyInfo *pInfo;
+ sqlite3VdbeAddOp2(v, OP_OpenEphemeral, pRJ->iMatch, 1);
+ pInfo = sqlite3KeyInfoAlloc(pParse->db, 1, 0);
+ if( pInfo ){
+ pInfo->aColl[0] = 0;
+ pInfo->aSortFlags[0] = 0;
+ sqlite3VdbeAppendP4(v, pInfo, P4_KEYINFO);
+ }
+ }else{
+ Index *pPk = sqlite3PrimaryKeyIndex(pTab);
+ sqlite3VdbeAddOp2(v, OP_OpenEphemeral, pRJ->iMatch, pPk->nKeyCol);
+ sqlite3VdbeSetP4KeyInfo(pParse, pPk);
+ }
+ pLoop->wsFlags &= ~WHERE_IDX_ONLY;
+ /* The nature of RIGHT JOIN processing is such that it messes up
+ ** the output order. So omit any ORDER BY/GROUP BY elimination
+ ** optimizations. We need to do an actual sort for RIGHT JOIN. */
+ pWInfo->nOBSat = 0;
+ pWInfo->eDistinct = WHERE_DISTINCT_UNORDERED;
+ }
}
pWInfo->iTop = sqlite3VdbeCurrentAddr(v);
if( db->mallocFailed ) goto whereBeginError;
@@ -5977,6 +6018,17 @@ void sqlite3WhereEnd(WhereInfo *pWInfo){
for(i=pWInfo->nLevel-1; i>=0; i--){
int addr;
pLevel = &pWInfo->a[i];
+ if( pLevel->pRJ ){
+ /* Terminate the subroutine that forms the interior of the loop of
+ ** the RIGHT JOIN table */
+ WhereRightJoin *pRJ = pLevel->pRJ;
+ int addrHere = sqlite3VdbeCurrentAddr(v);
+ sqlite3VdbeChangeP1(v, pRJ->addrSubrtn-1, addrHere);
+ sqlite3VdbeChangeP1(v, pRJ->addrInit, addrHere);
+ sqlite3VdbeResolveLabel(v, pLevel->addrCont);
+ pLevel->addrCont = 0;
+ sqlite3VdbeAddOp2(v, OP_Return, pRJ->regReturn, pRJ->addrSubrtn);
+ }
pLoop = pLevel->pWLoop;
if( pLevel->op!=OP_Noop ){
#ifndef SQLITE_DISABLE_SKIPAHEAD_DISTINCT
@@ -6004,7 +6056,7 @@ void sqlite3WhereEnd(WhereInfo *pWInfo){
}
#endif /* SQLITE_DISABLE_SKIPAHEAD_DISTINCT */
/* The common case: Advance to the next row */
- sqlite3VdbeResolveLabel(v, pLevel->addrCont);
+ if( pLevel->addrCont ) sqlite3VdbeResolveLabel(v, pLevel->addrCont);
sqlite3VdbeAddOp3(v, pLevel->op, pLevel->p1, pLevel->p2, pLevel->p3);
sqlite3VdbeChangeP5(v, pLevel->p5);
VdbeCoverage(v);
@@ -6019,7 +6071,7 @@ void sqlite3WhereEnd(WhereInfo *pWInfo){
#ifndef SQLITE_DISABLE_SKIPAHEAD_DISTINCT
if( addrSeek ) sqlite3VdbeJumpHere(v, addrSeek);
#endif
- }else{
+ }else if( pLevel->addrCont ){
sqlite3VdbeResolveLabel(v, pLevel->addrCont);
}
if( (pLoop->wsFlags & WHERE_IN_ABLE)!=0 && pLevel->u.in.nIn>0 ){
@@ -6112,11 +6164,6 @@ void sqlite3WhereEnd(WhereInfo *pWInfo){
pWInfo->pTabList->a[pLevel->iFrom].pTab->zName));
}
- /* The "break" point is here, just past the end of the outer loop.
- ** Set it.
- */
- sqlite3VdbeResolveLabel(v, pWInfo->iBreak);
-
assert( pWInfo->nLevel<=pTabList->nSrc );
for(i=0, pLevel=pWInfo->a; i<pWInfo->nLevel; i++, pLevel++){
int k, last;
@@ -6127,6 +6174,15 @@ void sqlite3WhereEnd(WhereInfo *pWInfo){
assert( pTab!=0 );
pLoop = pLevel->pWLoop;
+ /* Do RIGHT JOIN processing. Generate code that will output the
+ ** unmatched rows of the right operand of the RIGHT JOIN with
+ ** all of the columns of the left operand set to NULL.
+ */
+ if( pLevel->pRJ ){
+ sqlite3WhereRightJoinLoop(pWInfo, i, pLevel);
+ continue;
+ }
+
/* For a co-routine, change all OP_Column references to the table of
** the co-routine into OP_Copy of result contained in a register.
** OP_Rowid becomes OP_Null.
@@ -6138,29 +6194,6 @@ void sqlite3WhereEnd(WhereInfo *pWInfo){
continue;
}
-#ifdef SQLITE_ENABLE_EARLY_CURSOR_CLOSE
- /* Close all of the cursors that were opened by sqlite3WhereBegin.
- ** Except, do not close cursors that will be reused by the OR optimization
- ** (WHERE_OR_SUBCLAUSE). And do not close the OP_OpenWrite cursors
- ** created for the ONEPASS optimization.
- */
- if( (pTab->tabFlags & TF_Ephemeral)==0
- && !IsView(pTab)
- && (pWInfo->wctrlFlags & WHERE_OR_SUBCLAUSE)==0
- ){
- int ws = pLoop->wsFlags;
- if( pWInfo->eOnePass==ONEPASS_OFF && (ws & WHERE_IDX_ONLY)==0 ){
- sqlite3VdbeAddOp1(v, OP_Close, pTabItem->iCursor);
- }
- if( (ws & WHERE_INDEXED)!=0
- && (ws & (WHERE_IPK|WHERE_AUTO_INDEX))==0
- && pLevel->iIdxCur!=pWInfo->aiCurOnePass[1]
- ){
- sqlite3VdbeAddOp1(v, OP_Close, pLevel->iIdxCur);
- }
- }
-#endif
-
/* If this scan uses an index, make VDBE code substitutions to read data
** from the index instead of from the table where possible. In some cases
** this optimization prevents the table from ever being read, which can
@@ -6261,6 +6294,11 @@ void sqlite3WhereEnd(WhereInfo *pWInfo){
}
}
+ /* The "break" point is here, just past the end of the outer loop.
+ ** Set it.
+ */
+ sqlite3VdbeResolveLabel(v, pWInfo->iBreak);
+
/* Final cleanup
*/
if( pWInfo->pExprMods ) whereUndoExprMods(pWInfo);
diff --git a/src/whereInt.h b/src/whereInt.h
index e0f44d6ba..c8a188f80 100644
--- a/src/whereInt.h
+++ b/src/whereInt.h
@@ -33,6 +33,7 @@ typedef struct WhereScan WhereScan;
typedef struct WhereOrCost WhereOrCost;
typedef struct WhereOrSet WhereOrSet;
typedef struct WhereMemBlock WhereMemBlock;
+typedef struct WhereRightJoin WhereRightJoin;
/*
** This object is a header on a block of allocated memory that will be
@@ -44,6 +45,17 @@ struct WhereMemBlock {
};
/*
+** Extra information attached to a WhereLevel that is a RIGHT JOIN.
+*/
+struct WhereRightJoin {
+ int iMatch; /* Cursor used to determine prior matched rows */
+ int regBloom; /* Bloom filter for iRJMatch */
+ int regReturn; /* Return register for the interior subroutine */
+ int addrSubrtn; /* Starting address for the interior subroutine */
+ int addrInit; /* OP_Integer used for early init of regReturn */
+};
+
+/*
** This object contains information needed to implement a single nested
** loop in WHERE clause.
**
@@ -75,6 +87,7 @@ struct WhereLevel {
int addrLikeRep; /* LIKE range processing address */
#endif
int regFilter; /* Bloom filter */
+ WhereRightJoin *pRJ; /* Extra information for RIGHT JOIN */
u8 iFrom; /* Which entry in the FROM clause */
u8 op, p3, p5; /* Opcode, P3 & P5 of the opcode that ends the loop */
int p1, p2; /* Operands of the opcode used to end the loop */
@@ -552,6 +565,11 @@ Bitmask sqlite3WhereCodeOneLoopStart(
WhereLevel *pLevel, /* The current level pointer */
Bitmask notReady /* Which tables are currently available */
);
+SQLITE_NOINLINE void sqlite3WhereRightJoinLoop(
+ WhereInfo *pWInfo,
+ int iLevel,
+ WhereLevel *pLevel
+);
/* whereexpr.c: */
void sqlite3WhereClauseInit(WhereClause*,WhereInfo*);
diff --git a/src/wherecode.c b/src/wherecode.c
index 158fb806c..a438db530 100644
--- a/src/wherecode.c
+++ b/src/wherecode.c
@@ -204,6 +204,9 @@ int sqlite3WhereExplainOneScan(
pLoop->u.vtab.idxNum, pLoop->u.vtab.idxStr);
}
#endif
+ if( pItem->fg.jointype & JT_LEFT ){
+ sqlite3_str_appendf(&str, " LEFT-JOIN");
+ }
#ifdef SQLITE_EXPLAIN_ESTIMATED_ROWS
if( pLoop->nOut>=10 ){
sqlite3_str_appendf(&str, " (~%llu rows)",
@@ -1150,7 +1153,7 @@ static void codeDeferredSeek(
pWInfo->bDeferredSeek = 1;
sqlite3VdbeAddOp3(v, OP_DeferredSeek, iIdxCur, 0, iCur);
- if( (pWInfo->wctrlFlags & WHERE_OR_SUBCLAUSE)
+ if( (pWInfo->wctrlFlags & (WHERE_OR_SUBCLAUSE|WHERE_RIGHT_JOIN))
&& DbMaskAllZero(sqlite3ParseToplevel(pParse)->writeMask)
){
int i;
@@ -1502,7 +1505,7 @@ Bitmask sqlite3WhereCodeOneLoopStart(
** initialize a memory cell that records if this table matches any
** row of the left table of the join.
*/
- assert( (pWInfo->wctrlFlags & WHERE_OR_SUBCLAUSE)
+ assert( (pWInfo->wctrlFlags & (WHERE_OR_SUBCLAUSE|WHERE_RIGHT_JOIN))
|| pLevel->iFrom>0 || (pTabItem[0].fg.jointype & JT_LEFT)==0
);
if( pLevel->iFrom>0 && (pTabItem[0].fg.jointype & JT_LEFT)!=0 ){
@@ -2140,7 +2143,7 @@ Bitmask sqlite3WhereCodeOneLoopStart(
/* Seek the table cursor, if required */
omitTable = (pLoop->wsFlags & WHERE_IDX_ONLY)!=0
- && (pWInfo->wctrlFlags & WHERE_OR_SUBCLAUSE)==0;
+ && (pWInfo->wctrlFlags & (WHERE_OR_SUBCLAUSE|WHERE_RIGHT_JOIN))==0;
if( omitTable ){
/* pIdx is a covering index. No need to access the main table. */
}else if( HasRowid(pIdx->pTable) ){
@@ -2174,7 +2177,7 @@ Bitmask sqlite3WhereCodeOneLoopStart(
** move forward to the next index.
** https://sqlite.org/src/info/4e8e4857d32d401f
*/
- if( (pWInfo->wctrlFlags & WHERE_OR_SUBCLAUSE)==0 ){
+ if( (pWInfo->wctrlFlags & (WHERE_OR_SUBCLAUSE|WHERE_RIGHT_JOIN))==0 ){
whereIndexExprTrans(pIdx, iCur, iIdxCur, pWInfo);
}
@@ -2193,7 +2196,7 @@ Bitmask sqlite3WhereCodeOneLoopStart(
/* The following assert() is not a requirement, merely an observation:
** The OR-optimization doesn't work for the right hand table of
** a LEFT JOIN: */
- assert( (pWInfo->wctrlFlags & WHERE_OR_SUBCLAUSE)==0 );
+ assert( (pWInfo->wctrlFlags & (WHERE_OR_SUBCLAUSE|WHERE_RIGHT_JOIN))==0 );
}
/* Record the instruction used to terminate the loop. */
@@ -2605,7 +2608,9 @@ Bitmask sqlite3WhereCodeOneLoopStart(
}
pE = pTerm->pExpr;
assert( pE!=0 );
- if( (pTabItem->fg.jointype&JT_LEFT) && !ExprHasProperty(pE,EP_FromJoin) ){
+ if( (pTabItem->fg.jointype & (JT_LEFT|JT_LTORJ))
+ && !ExprHasProperty(pE,EP_FromJoin)
+ ){
continue;
}
@@ -2667,7 +2672,7 @@ Bitmask sqlite3WhereCodeOneLoopStart(
if( (pTerm->eOperator & (WO_EQ|WO_IS))==0 ) continue;
if( (pTerm->eOperator & WO_EQUIV)==0 ) continue;
if( pTerm->leftCursor!=iCur ) continue;
- if( pTabItem->fg.jointype & JT_LEFT ) continue;
+ if( pTabItem->fg.jointype & (JT_LEFT|JT_LTORJ) ) continue;
pE = pTerm->pExpr;
#ifdef WHERETRACE_ENABLED /* 0x800 */
if( sqlite3WhereTrace & 0x800 ){
@@ -2719,6 +2724,55 @@ Bitmask sqlite3WhereCodeOneLoopStart(
}
}
+ /* For a RIGHT OUTER JOIN, record the fact that the current row has
+ ** been matched at least once.
+ */
+ if( pLevel->pRJ ){
+ Table *pTab;
+ int nPk;
+ int r;
+ int jmp1 = 0;
+ WhereRightJoin *pRJ = pLevel->pRJ;
+
+ /* pTab is the right-hand table of the RIGHT JOIN. Generate code that
+ ** will record that the current row of that table has been matched at
+ ** least once. This is accomplished by storing the PK for the row in
+ ** both the iMatch index and the regBloom Bloom filter.
+ */
+ pTab = pWInfo->pTabList->a[pLevel->iFrom].pTab;
+ if( HasRowid(pTab) ){
+ r = sqlite3GetTempRange(pParse, 2);
+ sqlite3ExprCodeGetColumnOfTable(v, pTab, pLevel->iTabCur, -1, r+1);
+ nPk = 1;
+ }else{
+ int iPk;
+ Index *pPk = sqlite3PrimaryKeyIndex(pTab);
+ nPk = pPk->nKeyCol;
+ r = sqlite3GetTempRange(pParse, nPk+1);
+ for(iPk=0; iPk<nPk; iPk++){
+ int iCol = pPk->aiColumn[iPk];
+ sqlite3ExprCodeGetColumnOfTable(v, pTab, iCur, iCol,r+1+iPk);
+ }
+ }
+ jmp1 = sqlite3VdbeAddOp4Int(v, OP_Found, pRJ->iMatch, 0, r+1, nPk);
+ VdbeCoverage(v);
+ sqlite3VdbeAddOp3(v, OP_MakeRecord, r+1, nPk, r);
+ sqlite3VdbeAddOp4Int(v, OP_IdxInsert, pRJ->iMatch, r, r+1, nPk);
+ sqlite3VdbeAddOp4Int(v, OP_FilterAdd, pRJ->regBloom, 0, r+1, nPk);
+ sqlite3VdbeChangeP5(v, OPFLAG_USESEEKRESULT);
+ sqlite3VdbeJumpHere(v, jmp1);
+ sqlite3ReleaseTempRange(pParse, r, nPk+1);
+
+ /* Create a subroutine used to process all interior loops and code
+ ** of the RIGHT JOIN. During normal operation, the subroutine will
+ ** be in-line with the rest of the code. But at the end, a separate
+ ** loop will run that invokes this subroutine for unmatched rows
+ ** of pTab, with all tables to left begin set to NULL.
+ */
+ sqlite3VdbeAddOp2(v, OP_BeginSubrtn, 0, pRJ->regReturn);
+ pRJ->addrSubrtn = sqlite3VdbeCurrentAddr(v);
+ }
+
#if WHERETRACE_ENABLED /* 0x20800 */
if( sqlite3WhereTrace & 0x20000 ){
sqlite3DebugPrintf("All WHERE-clause terms after coding level %d:\n",
@@ -2732,3 +2786,81 @@ Bitmask sqlite3WhereCodeOneLoopStart(
#endif
return pLevel->notReady;
}
+
+/*
+** Generate the code for the loop that finds all non-matched terms
+** for a RIGHT JOIN.
+*/
+SQLITE_NOINLINE void sqlite3WhereRightJoinLoop(
+ WhereInfo *pWInfo,
+ int iLevel,
+ WhereLevel *pLevel
+){
+ Parse *pParse = pWInfo->pParse;
+ Vdbe *v = pParse->pVdbe;
+ WhereRightJoin *pRJ = pLevel->pRJ;
+ Expr *pSubWhere = 0;
+ WhereClause *pWC = &pWInfo->sWC;
+ WhereInfo *pSubWInfo;
+ WhereLoop *pLoop = pLevel->pWLoop;
+ SrcItem *pTabItem = &pWInfo->pTabList->a[pLevel->iFrom];
+ SrcList sFrom;
+ Bitmask mAll = 0;
+ int k;
+
+ for(k=0; k<iLevel; k++){
+ int iIdxCur;
+ mAll |= pWInfo->a[k].pWLoop->maskSelf;
+ sqlite3VdbeAddOp1(v, OP_NullRow, pWInfo->a[k].iTabCur);
+ iIdxCur = pWInfo->a[k].iIdxCur;
+ if( iIdxCur ){
+ sqlite3VdbeAddOp1(v, OP_NullRow, iIdxCur);
+ }
+ }
+ mAll |= pLoop->maskSelf;
+ for(k=0; k<pWC->nTerm; k++){
+ WhereTerm *pTerm = &pWC->a[k];
+ if( pTerm->wtFlags & TERM_VIRTUAL ) break;
+ if( pTerm->prereqAll & ~mAll ) continue;
+ if( ExprHasProperty(pTerm->pExpr, EP_FromJoin|EP_InnerJoin) ) continue;
+ pSubWhere = sqlite3ExprAnd(pParse, pSubWhere,
+ sqlite3ExprDup(pParse->db, pTerm->pExpr, 0));
+ }
+ sFrom.nSrc = 1;
+ sFrom.nAlloc = 1;
+ memcpy(&sFrom.a[0], pTabItem, sizeof(SrcItem));
+ sFrom.a[0].fg.jointype = 0;
+ ExplainQueryPlan((pParse, 1, "RIGHT-JOIN %s", pTabItem->pTab->zName));
+ pSubWInfo = sqlite3WhereBegin(pParse, &sFrom, pSubWhere, 0, 0, 0,
+ WHERE_RIGHT_JOIN, 0);
+ if( pSubWInfo ){
+ int iCur = pLevel->iTabCur;
+ int r = ++pParse->nMem;
+ int nPk;
+ int jmp;
+ int addrCont = sqlite3WhereContinueLabel(pSubWInfo);
+ Table *pTab = pTabItem->pTab;
+ if( HasRowid(pTab) ){
+ sqlite3ExprCodeGetColumnOfTable(v, pTab, iCur, -1, r);
+ nPk = 1;
+ }else{
+ int iPk;
+ Index *pPk = sqlite3PrimaryKeyIndex(pTab);
+ nPk = pPk->nKeyCol;
+ pParse->nMem += nPk - 1;
+ for(iPk=0; iPk<nPk; iPk++){
+ int iCol = pPk->aiColumn[iPk];
+ sqlite3ExprCodeGetColumnOfTable(v, pTab, iCur, iCol,r+iPk);
+ }
+ }
+ jmp = sqlite3VdbeAddOp4Int(v, OP_Filter, pRJ->regBloom, 0, r, nPk);
+ VdbeCoverage(v);
+ sqlite3VdbeAddOp4Int(v, OP_Found, pRJ->iMatch, addrCont, r, nPk);
+ VdbeCoverage(v);
+ sqlite3VdbeJumpHere(v, jmp);
+ sqlite3VdbeAddOp2(v, OP_Gosub, pRJ->regReturn, pRJ->addrSubrtn);
+ sqlite3WhereEnd(pSubWInfo);
+ }
+ sqlite3ExprDelete(pParse->db, pSubWhere);
+ ExplainQueryPlanPop(pParse);
+}
diff --git a/src/whereexpr.c b/src/whereexpr.c
index adcb90d0b..90c344806 100644
--- a/src/whereexpr.c
+++ b/src/whereexpr.c
@@ -1809,6 +1809,7 @@ void sqlite3WhereTabFuncArgs(
if( pArgs==0 ) return;
for(j=k=0; j<pArgs->nExpr; j++){
Expr *pRhs;
+ u32 joinType;
while( k<pTab->nCol && (pTab->aCol[k].colFlags & COLFLAG_HIDDEN)==0 ){k++;}
if( k>=pTab->nCol ){
sqlite3ErrorMsg(pParse, "too many arguments on %s() - max %d",
@@ -1825,9 +1826,12 @@ void sqlite3WhereTabFuncArgs(
pRhs = sqlite3PExpr(pParse, TK_UPLUS,
sqlite3ExprDup(pParse->db, pArgs->a[j].pExpr, 0), 0);
pTerm = sqlite3PExpr(pParse, TK_EQ, pColRef, pRhs);
- if( pItem->fg.jointype & JT_LEFT ){
- sqlite3SetJoinExpr(pTerm, pItem->iCursor);
+ if( pItem->fg.jointype & (JT_LEFT|JT_LTORJ) ){
+ joinType = EP_FromJoin;
+ }else{
+ joinType = EP_InnerJoin;
}
+ sqlite3SetJoinExpr(pTerm, pItem->iCursor, joinType);
whereClauseInsert(pWC, pTerm, TERM_DYNAMIC);
}
}
diff --git a/test/affinity3.test b/test/affinity3.test
index ef1533a8f..48942de72 100644
--- a/test/affinity3.test
+++ b/test/affinity3.test
@@ -30,11 +30,24 @@ do_execsql_test affinity3-100 {
FROM customer c
LEFT JOIN apr i ON i.id=c.id;
+ CREATE VIEW v1rj AS
+ SELECT c.id, i.apr
+ FROM apr i
+ RIGHT JOIN customer c ON i.id=c.id;
+
CREATE VIEW v2 AS
SELECT c.id, v1.apr
FROM customer c
LEFT JOIN v1 ON v1.id=c.id;
+ CREATE VIEW v2rj AS
+ SELECT c.id, v1.apr
+ FROM v1 RIGHT JOIN customer c ON v1.id=c.id;
+
+ CREATE VIEW v2rjrj AS
+ SELECT c.id, v1rj.apr
+ FROM v1rj RIGHT JOIN customer c ON v1rj.id=c.id;
+
INSERT INTO customer (id) VALUES (1);
INSERT INTO apr (id, apr) VALUES (1, 12);
INSERT INTO customer (id) VALUES (2);
@@ -44,16 +57,35 @@ do_execsql_test affinity3-110 {
PRAGMA automatic_index=ON;
SELECT id, (apr / 100), typeof(apr) apr_type FROM v1;
} {1 0.12 real 2 0.1201 real}
+do_execsql_test affinity3-111 {
+ PRAGMA automatic_index=ON;
+ SELECT id, (apr / 100), typeof(apr) apr_type FROM v1rj;
+} {1 0.12 real 2 0.1201 real}
do_execsql_test affinity3-120 {
SELECT id, (apr / 100), typeof(apr) apr_type FROM v2;
} {1 0.12 real 2 0.1201 real}
+do_execsql_test affinity3-121 {
+ SELECT id, (apr / 100), typeof(apr) apr_type FROM v2rj;
+} {1 0.12 real 2 0.1201 real}
+do_execsql_test affinity3-122 {
+ SELECT id, (apr / 100), typeof(apr) apr_type FROM v2rjrj;
+} {1 0.12 real 2 0.1201 real}
do_execsql_test affinity3-130 {
PRAGMA automatic_index=OFF;
SELECT id, (apr / 100), typeof(apr) apr_type FROM v1;
} {1 0.12 real 2 0.1201 real}
+do_execsql_test affinity3-131 {
+ SELECT id, (apr / 100), typeof(apr) apr_type FROM v1rj;
+} {1 0.12 real 2 0.1201 real}
do_execsql_test affinity3-140 {
SELECT id, (apr / 100), typeof(apr) apr_type FROM v2;
} {1 0.12 real 2 0.1201 real}
+do_execsql_test affinity3-141 {
+ SELECT id, (apr / 100), typeof(apr) apr_type FROM v2rj;
+} {1 0.12 real 2 0.1201 real}
+do_execsql_test affinity3-142 {
+ SELECT id, (apr / 100), typeof(apr) apr_type FROM v2rjrj;
+} {1 0.12 real 2 0.1201 real}
# Ticket https://www.sqlite.org/src/info/7ffd1ca1d2ad4ecf (2017-01-16)
# Incorrect affinity when using automatic indexes
diff --git a/test/aggnested.test b/test/aggnested.test
index 35d5f1e3a..1b8b60880 100644
--- a/test/aggnested.test
+++ b/test/aggnested.test
@@ -137,6 +137,17 @@ do_test aggnested-3.1 {
GROUP BY curr.id1);
}
} {1 1}
+do_test aggnested-3.1-rj {
+ db eval {
+ SELECT
+ (SELECT sum(value2==xyz) FROM t2)
+ FROM
+ (SELECT curr.value1 as xyz
+ FROM t1 AS other RIGHT JOIN t1 AS curr
+ GROUP BY curr.id1);
+ }
+} {1 1}
+
do_test aggnested-3.2 {
db eval {
DROP TABLE IF EXISTS t1;
diff --git a/test/autoindex1.test b/test/autoindex1.test
index 6b437f186..2cd490040 100644
--- a/test/autoindex1.test
+++ b/test/autoindex1.test
@@ -283,7 +283,7 @@ do_eqp_test autoindex1-600a {
| `--CORRELATED SCALAR SUBQUERY xxxxxx
| `--SEARCH later USING COVERING INDEX sqlite_autoindex_flock_owner_1 (flock_no=? AND owner_change_date>? AND owner_change_date<?)
|--SCAN x USING INDEX sheep_reg_flock_index
- `--SEARCH y USING AUTOMATIC COVERING INDEX (sheep_no=?)
+ `--SEARCH y USING AUTOMATIC COVERING INDEX (sheep_no=?) LEFT-JOIN
}
diff --git a/test/autoindex4.test b/test/autoindex4.test
index 24604af58..d9ab783e4 100644
--- a/test/autoindex4.test
+++ b/test/autoindex4.test
@@ -32,12 +32,21 @@ do_execsql_test autoindex4-1.1 {
do_execsql_test autoindex4-1.2 {
SELECT *, '|' FROM t1 LEFT JOIN t2 ON a=234 AND x=555;
} {123 abc {} {} | 234 def {} {} | 234 ghi {} {} | 345 jkl {} {} |}
+do_execsql_test autoindex4-1.2-rj {
+ SELECT t1.*, t2.*, '|' FROM t2 RIGHT JOIN t1 ON a=234 AND x=555;
+} {123 abc {} {} | 234 def {} {} | 234 ghi {} {} | 345 jkl {} {} |}
do_execsql_test autoindex4-1.3 {
SELECT *, '|' FROM t1 LEFT JOIN t2 ON x=555 WHERE a=234;
} {234 def {} {} | 234 ghi {} {} |}
+do_execsql_test autoindex4-1.3-rj {
+ SELECT t1.*, t2.*, '|' FROM t2 RIGHT JOIN t1 ON x=555 WHERE a=234;
+} {234 def {} {} | 234 ghi {} {} |}
do_execsql_test autoindex4-1.4 {
SELECT *, '|' FROM t1 LEFT JOIN t2 WHERE a=234 AND x=555;
} {}
+do_execsql_test autoindex4-1.4-rj {
+ SELECT t1.*, t2.*, '|' FROM t2 RIGHT JOIN t1 WHERE a=234 AND x=555;
+} {}
do_execsql_test autoindex4-2.0 {
@@ -69,6 +78,14 @@ do_execsql_test autoindex4-3.0 {
ORDER BY Items.ItemName;
} {Item1 Item2}
do_execsql_test autoindex4-3.1 {
+ SELECT Items.ItemName
+ FROM A
+ RIGHT JOIN Items ON (A.Name = Items.ItemName and Items.ItemName = 'dummy')
+ LEFT JOIN B ON (B.Name = Items.ItemName)
+ WHERE Items.Name = 'Parent'
+ ORDER BY Items.ItemName;
+} {Item1 Item2}
+do_execsql_test autoindex4-3.10 {
CREATE INDEX Items_x1 ON Items(ItemName,Name) WHERE ItemName = 'dummy';
SELECT Items.ItemName
@@ -78,6 +95,14 @@ do_execsql_test autoindex4-3.1 {
WHERE Items.Name = 'Parent'
ORDER BY Items.ItemName;
} {Item1 Item2}
+do_execsql_test autoindex4-3.11 {
+ SELECT Items.ItemName
+ FROM A
+ RIGHT JOIN Items ON (A.Name = Items.ItemName and Items.ItemName = 'dummy')
+ LEFT JOIN B ON (B.Name = Items.ItemName)
+ WHERE Items.Name = 'Parent'
+ ORDER BY Items.ItemName;
+} {Item1 Item2}
# 2021-11-30 - Enhancement to help the automatic index mechanism to
# create a partial index more often.
diff --git a/test/btree01.test b/test/btree01.test
index 9c309760d..6e4717ae6 100644
--- a/test/btree01.test
+++ b/test/btree01.test
@@ -148,6 +148,9 @@ do_execsql_test btree01-2.1 {
INSERT INTO t2(y) VALUES(198),(187),(100);
SELECT y, c FROM t2 LEFT JOIN t1 ON y=a ORDER BY x;
} {198 99 187 {} 100 50}
+do_execsql_test btree01-2.2 {
+ SELECT y, c FROM t1 RIGHT JOIN t2 ON y=a ORDER BY x;
+} {198 99 187 {} 100 50}
finish_test
diff --git a/test/collate2.test b/test/collate2.test
index d5aadb4eb..281aa3570 100644
--- a/test/collate2.test
+++ b/test/collate2.test
@@ -684,16 +684,34 @@ do_test collate2-5.3 {
SELECT collate2t1.b FROM collate2t2 NATURAL JOIN collate2t1;
}
} {aa}
-do_test collate2-5.4 {
+do_test collate2-5.4.1 {
execsql {
- SELECT collate2t2.b FROM collate2t1 LEFT OUTER JOIN collate2t2 USING (b) order by collate2t1.oid;
+ SELECT collate2t2.b FROM collate2t1 LEFT JOIN collate2t2 USING (b) order by collate2t1.oid;
}
} {{} aa {} {} {} aa {} {} {} aa {} {} {} aa {} {} {}}
-do_test collate2-5.5 {
+do_test collate2-5.4.2 {
+ execsql {
+ SELECT collate2t2.b FROM collate2t2 RIGHT JOIN collate2t1 ON collate2t1.b=collate2t2.b
+ ORDER BY collate2t1.oid;
+ }
+} {{} aa {} {} {} aa {} {} {} aa {} {} {} aa {} {} {}}
+do_test collate2-5.4.3 {
+ execsql {
+ SELECT collate2t2.b FROM collate2t1 LEFT JOIN collate2t2 ON collate2t2.b=collate2t1.b
+ ORDER BY collate2t1.oid;
+ }
+} {{} aa {} {} {} {} {} {} {} {} {} {} {} {} {} {} {}}
+do_test collate2-5.5.1 {
execsql {
SELECT collate2t1.b, collate2t2.b FROM collate2t2 LEFT OUTER JOIN collate2t1 USING (b);
}
} {aa aa}
+do_test collate2-5.5.2 {
+ execsql {
+ SELECT collate2t1.b, collate2t2.b
+ FROM collate2t1 RIGHT JOIN collate2t2 ON collate2t2.b=collate2t1.b
+ }
+} {aa aa}
do_execsql_test 6.1 {
CREATE TABLE t1(x);
diff --git a/test/fts3join.test b/test/fts3join.test
index 9a7994193..3333b1ab4 100644
--- a/test/fts3join.test
+++ b/test/fts3join.test
@@ -100,7 +100,7 @@ do_eqp_test 4.2 {
|--MATERIALIZE rr
| `--SCAN ft4 VIRTUAL TABLE INDEX 3:
|--SCAN t4
- `--SEARCH rr USING AUTOMATIC COVERING INDEX (docid=?)
+ `--SEARCH rr USING AUTOMATIC COVERING INDEX (docid=?) LEFT-JOIN
}
finish_test
diff --git a/test/index6.test b/test/index6.test
index 7eed6a47e..1ae2ee875 100644
--- a/test/index6.test
+++ b/test/index6.test
@@ -320,7 +320,7 @@ do_eqp_test index6-8.1 {
} {
QUERY PLAN
|--SCAN t8a
- `--SEARCH t8b USING INDEX i8c (y=?)
+ `--SEARCH t8b USING INDEX i8c (y=?) LEFT-JOIN
}
do_execsql_test index6-8.2 {
diff --git a/test/join.test b/test/join.test
index f48a1a149..d6e775436 100644
--- a/test/join.test
+++ b/test/join.test
@@ -272,11 +272,13 @@ do_test join-2.2 {
SELECT * FROM t2 NATURAL LEFT OUTER JOIN t1;
}
} {1 2 3 {} 2 3 4 1 3 4 5 2}
-do_test join-2.3 {
- catchsql {
- SELECT * FROM t1 NATURAL RIGHT OUTER JOIN t2;
- }
-} {1 {RIGHT and FULL OUTER JOINs are not currently supported}}
+
+#do_test join-2.3 {
+# catchsql {
+# SELECT * FROM t1 NATURAL RIGHT OUTER JOIN t2;
+# }
+#} {1 {RIGHT and FULL OUTER JOINs are not currently supported}}
+
do_test join-2.4 {
execsql {
SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.d
@@ -330,32 +332,32 @@ do_test join-3.7 {
catchsql {
SELECT * FROM t1 INNER OUTER JOIN t2;
}
-} {1 {unknown or unsupported join type: INNER OUTER}}
+} {1 {unknown join type: INNER OUTER}}
do_test join-3.8 {
catchsql {
SELECT * FROM t1 INNER OUTER CROSS JOIN t2;
}
-} {1 {unknown or unsupported join type: INNER OUTER CROSS}}
+} {1 {unknown join type: INNER OUTER CROSS}}
do_test join-3.9 {
catchsql {
SELECT * FROM t1 OUTER NATURAL INNER JOIN t2;
}
-} {1 {unknown or unsupported join type: OUTER NATURAL INNER}}
+} {1 {unknown join type: OUTER NATURAL INNER}}
do_test join-3.10 {
catchsql {
SELECT * FROM t1 LEFT BOGUS JOIN t2;
}
-} {1 {unknown or unsupported join type: LEFT BOGUS}}
+} {1 {unknown join type: LEFT BOGUS}}
do_test join-3.11 {
catchsql {
SELECT * FROM t1 INNER BOGUS CROSS JOIN t2;
}
-} {1 {unknown or unsupported join type: INNER BOGUS CROSS}}
+} {1 {unknown join type: INNER BOGUS CROSS}}
do_test join-3.12 {
catchsql {
SELECT * FROM t1 NATURAL AWK SED JOIN t2;
}
-} {1 {unknown or unsupported join type: NATURAL AWK SED}}
+} {1 {unknown join type: NATURAL AWK SED}}
do_test join-4.1 {
execsql {
diff --git a/test/join2.test b/test/join2.test
index 170000ca2..4142fd15b 100644
--- a/test/join2.test
+++ b/test/join2.test
@@ -63,6 +63,12 @@ do_test join2-1.6 {
t1 NATURAL LEFT OUTER JOIN t2 NATURAL JOIN t3
}
} {1 11 111 1111}
+do_test join2-1.6-rj {
+ execsql {
+ SELECT * FROM
+ t2 NATURAL RIGHT OUTER JOIN t1 NATURAL JOIN t3
+ }
+} {11 111 1 1111}
ifcapable subquery {
do_test join2-1.7 {
execsql {
@@ -70,6 +76,12 @@ ifcapable subquery {
t1 NATURAL LEFT OUTER JOIN (t2 NATURAL JOIN t3)
}
} {1 11 111 1111 2 22 {} {} 3 33 {} {}}
+ do_test join2-1.7-rj {
+ execsql {
+ SELECT * FROM
+ (t2 NATURAL JOIN t3) NATURAL RIGHT JOIN t1
+ }
+ } {11 111 1111 1 {} {} {} 2 {} {} {} 3}
}
#-------------------------------------------------------------------------
@@ -88,6 +100,9 @@ do_execsql_test 2.0 {
do_catchsql_test 2.1 {
SELECT * FROM aa LEFT JOIN cc ON (a=b) JOIN bb ON (b=coalesce(c,1));
} {1 {ON clause references tables to its right}}
+do_catchsql_test 2.1b {
+ SELECT * FROM aa RIGHT JOIN cc ON (a=b) JOIN bb ON (b=coalesce(c,1));
+} {1 {ON clause references tables to its right}}
do_catchsql_test 2.2 {
SELECT * FROM aa JOIN cc ON (a=b) JOIN bb ON (b=c);
} {0 {one one one}}
@@ -114,7 +129,7 @@ do_eqp_test 3.1 {
} {
QUERY PLAN
|--SCAN t1
- `--SEARCH t2 USING INTEGER PRIMARY KEY (rowid=?)
+ `--SEARCH t2 USING INTEGER PRIMARY KEY (rowid=?) LEFT-JOIN
}
do_eqp_test 3.2 {
@@ -122,7 +137,7 @@ do_eqp_test 3.2 {
} {
QUERY PLAN
|--SCAN t1
- `--SEARCH t2 USING INTEGER PRIMARY KEY (rowid=?)
+ `--SEARCH t2 USING INTEGER PRIMARY KEY (rowid=?) LEFT-JOIN
}
#-------------------------------------------------------------------------
@@ -162,15 +177,15 @@ do_eqp_test 4.1.5 {
} {
QUERY PLAN
|--SCAN c1
- |--SEARCH c2 USING INTEGER PRIMARY KEY (rowid=?)
- `--SEARCH c3 USING INTEGER PRIMARY KEY (rowid=?)
+ |--SEARCH c2 USING INTEGER PRIMARY KEY (rowid=?) LEFT-JOIN
+ `--SEARCH c3 USING INTEGER PRIMARY KEY (rowid=?) LEFT-JOIN
}
do_eqp_test 4.1.6 {
SELECT v1, v3 FROM c1 LEFT JOIN c2 ON (c2.k=v1) LEFT JOIN c3 ON (c3.k=v1+1);
} {
QUERY PLAN
|--SCAN c1
- `--SEARCH c3 USING INTEGER PRIMARY KEY (rowid=?)
+ `--SEARCH c3 USING INTEGER PRIMARY KEY (rowid=?) LEFT-JOIN
}
do_execsql_test 4.2.0 {
@@ -209,15 +224,15 @@ do_eqp_test 4.2.5 {
} {
QUERY PLAN
|--SCAN c1
- |--SEARCH c2 USING INDEX sqlite_autoindex_c2_1 (k=?)
- `--SEARCH c3 USING INDEX sqlite_autoindex_c3_1 (k=?)
+ |--SEARCH c2 USING INDEX sqlite_autoindex_c2_1 (k=?) LEFT-JOIN
+ `--SEARCH c3 USING INDEX sqlite_autoindex_c3_1 (k=?) LEFT-JOIN
}
do_eqp_test 4.2.6 {
SELECT v1, v3 FROM c1 LEFT JOIN c2 ON (c2.k=v1) LEFT JOIN c3 ON (c3.k=v1+1);
} {
QUERY PLAN
|--SCAN c1
- `--SEARCH c3 USING INDEX sqlite_autoindex_c3_1 (k=?)
+ `--SEARCH c3 USING INDEX sqlite_autoindex_c3_1 (k=?) LEFT-JOIN
}
# 2017-11-23 (Thanksgiving day)
diff --git a/test/join5.test b/test/join5.test
index a5cedd8cb..e4fd9cd87 100644
--- a/test/join5.test
+++ b/test/join5.test
@@ -280,9 +280,9 @@ do_eqp_test 7.2 {
|--SCAN t1
`--MULTI-INDEX OR
|--INDEX 1
- | `--SEARCH t2 USING INDEX t2xy (x=? AND y=?)
+ | `--SEARCH t2 USING INDEX t2xy (x=? AND y=?) LEFT-JOIN
`--INDEX 2
- `--SEARCH t2 USING INDEX t2xy (x=? AND y=?)
+ `--SEARCH t2 USING INDEX t2xy (x=? AND y=?) LEFT-JOIN
}
do_execsql_test 7.3 {
@@ -303,7 +303,7 @@ do_eqp_test 7.4 {
} {
QUERY PLAN
|--SCAN t3
- `--SEARCH t4 USING INDEX t4xz (x=?)
+ `--SEARCH t4 USING INDEX t4xz (x=?) LEFT-JOIN
}
do_eqp_test 7.4b {
SELECT * FROM t3 CROSS JOIN t4 ON (t4.x = t3.x) WHERE (+t4.y = ? OR t4.z = ?);
diff --git a/test/join7.test b/test/join7.test
new file mode 100644
index 000000000..a0507be1d
--- /dev/null
+++ b/test/join7.test
@@ -0,0 +1,273 @@
+# 2022-04-09
+#
+# The author disclaims copyright to this source code. In place of
+# a legal notice, here is a blessing:
+#
+# May you do good and not evil.
+# May you find forgiveness for yourself and forgive others.
+# May you share freely, never taking more than you give.
+#
+#***********************************************************************
+# This file implements regression tests for SQLite library.
+#
+# This file implements tests for RIGHT and FULL OUTER JOINs.
+
+set testdir [file dirname $argv0]
+source $testdir/tester.tcl
+
+foreach {id schema} {
+ 1 {
+ CREATE TABLE t1(a INT, b INT);
+ INSERT INTO t1 VALUES(1,2),(1,3),(1,4);
+ CREATE INDEX t1a ON t1(a);
+ CREATE TABLE t2(c INT, d INT);
+ INSERT INTO t2 VALUES(3,33),(4,44),(5,55);
+ CREATE INDEX t2c ON t2(c);
+ CREATE VIEW dual(dummy) AS VALUES('x');
+ }
+ 2 {
+ CREATE TABLE t1(a INT, b INT);
+ INSERT INTO t1 VALUES(1,2),(1,3),(1,4);
+ CREATE INDEX t1ab ON t1(a,b);
+ CREATE TABLE t2(c INT, d INT);
+ INSERT INTO t2 VALUES(3,33),(4,44),(5,55);
+ CREATE INDEX t2cd ON t2(c,d);
+ CREATE VIEW dual(dummy) AS VALUES('x');
+ }
+ 3 {
+ CREATE TABLE t1(a INT, b INT);
+ INSERT INTO t1 VALUES(1,2),(1,3),(1,4);
+ CREATE INDEX t1a ON t1(a);
+ CREATE TABLE t2(c INT, d INT PRIMARY KEY) WITHOUT ROWID;
+ INSERT INTO t2 VALUES(3,33),(4,44),(5,55);
+ CREATE INDEX t2c ON t2(c);
+ CREATE VIEW dual(dummy) AS VALUES('x');
+ }
+ 4 {
+ CREATE TABLE t1(a INT, b INT);
+ INSERT INTO t1 VALUES(1,2),(1,3),(1,4);
+ CREATE TABLE t2(c INTEGER PRIMARY KEY, d INT);
+ INSERT INTO t2 VALUES(3,33),(4,44),(5,55);
+ CREATE VIEW dual(dummy) AS VALUES('x');
+ }
+ 5 {
+ CREATE TABLE t1(a INT, b INT);
+ INSERT INTO t1 VALUES(1,2),(1,3),(1,4);
+ CREATE TABLE t2(c INT PRIMARY KEY, d INT) WITHOUT ROWID;
+ INSERT INTO t2 VALUES(3,33),(4,44),(5,55);
+ CREATE VIEW dual(dummy) AS VALUES('x');
+ }
+ 6 {
+ CREATE TABLE t1(a INT, b INT);
+ INSERT INTO t1 VALUES(1,2),(1,3),(1,4);
+ CREATE VIEW t2(c,d) AS VALUES(3,33),(4,44),(5,55);
+ CREATE VIEW dual(dummy) AS VALUES('x');
+ }
+ 7 {
+ CREATE VIEW t1(a,b) AS VALUES(1,2),(1,3),(1,4);
+ CREATE TABLE t2(c INTEGER PRIMARY KEY, d INT);
+ INSERT INTO t2 VALUES(3,33),(4,44),(5,55);
+ CREATE VIEW dual(dummy) AS VALUES('x');
+ }
+ 8 {
+ CREATE TABLE t1(a INT, b INT);
+ INSERT INTO t1 VALUES(1,2),(1,3),(1,4);
+ CREATE TABLE t2(c INT, d INT);
+ INSERT INTO t2 VALUES(3,33),(4,44),(5,55);
+ CREATE VIEW dual(dummy) AS VALUES('x');
+ }
+ 9 {
+ CREATE TABLE t1(a INT, b INT);
+ INSERT INTO t1 VALUES(1,2),(1,3),(1,4);
+ CREATE TABLE t2a(c INTEGER PRIMARY KEY, i1 INT);
+ CREATE TABLE t2b(i1 INTEGER PRIMARY KEY, d INT);
+ CREATE VIEW t2(c,d) AS SELECT c, d FROM t2a NATURAL JOIN t2b;
+ INSERT INTO t2a VALUES(3,93),(4,94),(5,95),(6,96),(7,97);
+ INSERT INTO t2b VALUES(91,11),(92,22),(93,33),(94,44),(95,55);
+ CREATE TABLE dual(dummy TEXT);
+ INSERT INTO dual(dummy) VALUES('x');
+ }
+ 10 {
+ CREATE TABLE t1(a INT, b INT, PRIMARY KEY(a,b)) WITHOUT ROWID;
+ INSERT INTO t1 VALUES(1,2),(1,3),(1,4);
+ CREATE TABLE t2a(c INTEGER PRIMARY KEY, i1 INT);
+ CREATE TABLE t2b(i1 INTEGER PRIMARY KEY, d INT);
+ CREATE VIEW t2(c,d) AS SELECT c, d FROM t2a NATURAL JOIN t2b;
+ INSERT INTO t2a VALUES(3,93),(4,94),(5,95),(6,96),(7,97);
+ INSERT INTO t2b VALUES(91,11),(92,22),(93,33),(94,44),(95,55);
+ CREATE TABLE dual(dummy TEXT);
+ INSERT INTO dual(dummy) VALUES('x');
+ }
+} {
+ reset_db
+ db nullvalue NULL
+ do_execsql_test join7-$id.setup $schema {}
+ do_execsql_test join7-$id.10 {
+ SELECT b, d FROM t1 FULL OUTER JOIN t2 ON b=c ORDER BY +b;
+ } {
+ NULL 55
+ 2 NULL
+ 3 33
+ 4 44
+ }
+ do_execsql_test join7-$id.20 {
+ SELECT a, c FROM t1 FULL OUTER JOIN t2 ON b=c ORDER BY +b;
+ } {
+ NULL 5
+ 1 NULL
+ 1 3
+ 1 4
+ }
+ do_execsql_test join7-$id.30 {
+ SELECT * FROM t1 FULL OUTER JOIN t2 ON b=c ORDER BY +b;
+ } {
+ NULL NULL 5 55
+ 1 2 NULL NULL
+ 1 3 3 33
+ 1 4 4 44
+ }
+ do_execsql_test join7-$id.31 {
+ SELECT t1.*, t2.* FROM t2 FULL OUTER JOIN t1 ON b=c ORDER BY +b;
+ } {
+ NULL NULL 5 55
+ 1 2 NULL NULL
+ 1 3 3 33
+ 1 4 4 44
+ }
+ do_execsql_test join7-$id.40 {
+ SELECT * FROM t1 RIGHT OUTER JOIN t2 ON b=c ORDER BY +b;
+ } {
+ NULL NULL 5 55
+ 1 3 3 33
+ 1 4 4 44
+ }
+ do_execsql_test join7-$id.50 {
+ SELECT t1.*, t2.* FROM t2 LEFT OUTER JOIN t1 ON b=c ORDER BY +b;
+ } {
+ NULL NULL 5 55
+ 1 3 3 33
+ 1 4 4 44
+ }
+ do_execsql_test join7-$id.60 {
+ SELECT * FROM dual JOIN t1 ON true RIGHT OUTER JOIN t2 ON b=c ORDER BY +b;
+ } {
+ NULL NULL NULL 5 55
+ x 1 3 3 33
+ x 1 4 4 44
+ }
+ do_execsql_test join7-$id.70 {
+ SELECT t1.*, t2.*
+ FROM t2 LEFT JOIN (dual JOIN t1 ON true) ON b=c ORDER BY +b;
+ } {
+ NULL NULL 5 55
+ 1 3 3 33
+ 1 4 4 44
+ }
+ do_execsql_test join7-$id.80 {
+ SELECT * FROM dual CROSS JOIN t1 RIGHT OUTER JOIN t2 ON b=c ORDER BY +b;
+ } {
+ NULL NULL NULL 5 55
+ x 1 3 3 33
+ x 1 4 4 44
+ }
+ do_execsql_test join7-$id.81 {
+ SELECT dual.*, t1.*, t2.*
+ FROM t1 CROSS JOIN dual RIGHT OUTER JOIN t2 ON b=c ORDER BY +b;
+ } {
+ NULL NULL NULL 5 55
+ x 1 3 3 33
+ x 1 4 4 44
+ }
+ do_execsql_test join7-$id.90 {
+ SELECT * FROM t1 LEFT OUTER JOIN t2 ON b=c ORDER BY +b;
+ } {
+ 1 2 NULL NULL
+ 1 3 3 33
+ 1 4 4 44
+ }
+ do_execsql_test join7-$id.100 {
+ SELECT * FROM t1 FULL OUTER JOIN t2 ON b=c AND a=1 ORDER BY +b;
+ } {
+ NULL NULL 5 55
+ 1 2 NULL NULL
+ 1 3 3 33
+ 1 4 4 44
+ }
+ do_execsql_test join7-$id.101 {
+ SELECT t1.*, t2.* FROM t2 FULL OUTER JOIN t1 ON b=c AND a=1 ORDER BY +b;
+ } {
+ NULL NULL 5 55
+ 1 2 NULL NULL
+ 1 3 3 33
+ 1 4 4 44
+ }
+ do_execsql_test join7-$id.110 {
+ SELECT * FROM t1 FULL OUTER JOIN t2 ON b=c WHERE a=1 ORDER BY +b;
+ } {
+ 1 2 NULL NULL
+ 1 3 3 33
+ 1 4 4 44
+ }
+ do_execsql_test join7-$id.111 {
+ SELECT t1.*, t2.* FROM t2 FULL OUTER JOIN t1 ON b=c WHERE a=1 ORDER BY +b;
+ } {
+ 1 2 NULL NULL
+ 1 3 3 33
+ 1 4 4 44
+ }
+ do_execsql_test join7-$id.115 {
+ SELECT * FROM t1 FULL OUTER JOIN t2 ON b=c
+ WHERE a=1 OR a IS NULL ORDER BY +b;
+ } {
+ NULL NULL 5 55
+ 1 2 NULL NULL
+ 1 3 3 33
+ 1 4 4 44
+ }
+ do_execsql_test join7-$id.116 {
+ SELECT t1.*, t2.* FROM t2 FULL OUTER JOIN t1 ON b=c
+ WHERE a=1 OR a IS NULL ORDER BY +b;
+ } {
+ NULL NULL 5 55
+ 1 2 NULL NULL
+ 1 3 3 33
+ 1 4 4 44
+ }
+ do_execsql_test join7-$id.120 {
+ SELECT * FROM t1 FULL OUTER JOIN t2 ON b=c WHERE a IS NULL ORDER BY +d;
+ } {
+ NULL NULL 3 33
+ NULL NULL 4 44
+ NULL NULL 5 55
+ }
+ do_execsql_test join7-$id.130 {
+ SELECT * FROM t1 FULL OUTER JOIN t2 ON b=c AND d<=0 ORDER BY +b, +d;
+ } {
+ NULL NULL 3 33
+ NULL NULL 4 44
+ NULL NULL 5 55
+ 1 2 NULL NULL
+ 1 3 NULL NULL
+ 1 4 NULL NULL
+ }
+ do_execsql_test join7-$id.140 {
+ SELECT a, b, c, d
+ FROM t2 FULL OUTER JOIN t1 ON b=c AND d<=0 ORDER BY +b, +d;
+ } {
+ NULL NULL 3 33
+ NULL NULL 4 44
+ NULL NULL 5 55
+ 1 2 NULL NULL
+ 1 3 NULL NULL
+ 1 4 NULL NULL
+ }
+ do_execsql_test join7-$id.141 {
+ SELECT a, b, c, d
+ FROM t2 FULL OUTER JOIN t1 ON b=c AND d<=0
+ ORDER BY +b, +d LIMIT 2 OFFSET 2
+ } {
+ NULL NULL 5 55
+ 1 2 NULL NULL
+ }
+}
+finish_test
diff --git a/test/join8.test b/test/join8.test
new file mode 100644
index 000000000..0854d9729
--- /dev/null
+++ b/test/join8.test
@@ -0,0 +1,24 @@
+# 2022-04-12
+#
+# The author disclaims copyright to this source code. In place of
+# a legal notice, here is a blessing:
+#
+# May you do good and not evil.
+# May you find forgiveness for yourself and forgive others.
+# May you share freely, never taking more than you give.
+#
+#***********************************************************************
+#
+# This file implements tests for RIGHT and FULL OUTER JOINs.
+
+set testdir [file dirname $argv0]
+source $testdir/tester.tcl
+
+db null NULL
+do_execsql_test join8-10 {
+ CREATE TABLE t1(a,b,c);
+ CREATE TABLE t2(x,y);
+ CREATE INDEX t2x ON t2(x);
+ SELECT avg(DISTINCT b) FROM (SELECT * FROM t2 LEFT RIGHT JOIN t1 ON c);
+} {NULL}
+finish_test
diff --git a/test/vtab6.test b/test/vtab6.test
index ab52c9f15..2ee5e2705 100644
--- a/test/vtab6.test
+++ b/test/vtab6.test
@@ -223,11 +223,11 @@ do_test vtab6-2.2 {
SELECT * FROM t2 NATURAL LEFT OUTER JOIN t1;
}
} {1 2 3 {} 2 3 4 1 3 4 5 2}
-do_test vtab6-2.3 {
- catchsql {
- SELECT * FROM t1 NATURAL RIGHT OUTER JOIN t2;
- }
-} {1 {RIGHT and FULL OUTER JOINs are not currently supported}}
+#do_test vtab6-2.3 {
+# catchsql {
+# SELECT * FROM t1 NATURAL RIGHT OUTER JOIN t2;
+# }
+#} {1 {RIGHT and FULL OUTER JOINs are not currently supported}}
do_test vtab6-2.4 {
execsql {
SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.d
@@ -281,12 +281,12 @@ do_test vtab6-3.7 {
catchsql {
SELECT * FROM t1 INNER OUTER JOIN t2;
}
-} {1 {unknown or unsupported join type: INNER OUTER}}
+} {1 {unknown join type: INNER OUTER}}
do_test vtab6-3.7 {
catchsql {
SELECT * FROM t1 LEFT BOGUS JOIN t2;
}
-} {1 {unknown or unsupported join type: LEFT BOGUS}}
+} {1 {unknown join type: LEFT BOGUS}}
do_test vtab6-4.1 {
execsql {
diff --git a/test/where.test b/test/where.test
index 8ee57b8b6..2f53f2cb4 100644
--- a/test/where.test
+++ b/test/where.test
@@ -1348,16 +1348,25 @@ do_execsql_test where-18.1 {
INSERT INTO t181 VALUES(1);
SELECT DISTINCT a FROM t181 LEFT JOIN t182 ON a=b ORDER BY c IS NULL;
} {1}
+do_execsql_test where-18.1rj {
+ SELECT DISTINCT a FROM t182 RIGHT JOIN t181 ON a=b ORDER BY c IS NULL;
+} {1}
do_execsql_test where-18.2 {
SELECT DISTINCT a FROM t181 LEFT JOIN t182 ON a=b ORDER BY +c;
} {1}
do_execsql_test where-18.3 {
SELECT DISTINCT a FROM t181 LEFT JOIN t182 ON a=b ORDER BY c;
} {1}
+do_execsql_test where-18.3rj {
+ SELECT DISTINCT a FROM t182 RIGHT JOIN t181 ON a=b ORDER BY c;
+} {1}
do_execsql_test where-18.4 {
INSERT INTO t181 VALUES(1),(1),(1),(1);
SELECT DISTINCT a FROM t181 LEFT JOIN t182 ON a=b ORDER BY +c;
} {1}
+do_execsql_test where-18.4rj {
+ SELECT DISTINCT a FROM t182 RIGHT JOIN t181 ON a=b ORDER BY +c;
+} {1}
do_execsql_test where-18.5 {
INSERT INTO t181 VALUES(2);
SELECT DISTINCT a FROM t181 LEFT JOIN t182 ON a=b ORDER BY c IS NULL, +a;
diff --git a/test/where9.test b/test/where9.test
index 429708f1f..475788153 100644
--- a/test/where9.test
+++ b/test/where9.test
@@ -378,9 +378,9 @@ ifcapable explain {
|--SEARCH t1 USING INTEGER PRIMARY KEY (rowid=?)
`--MULTI-INDEX OR
|--INDEX 1
- | `--SEARCH t2 USING INDEX t2d (d=?)
+ | `--SEARCH t2 USING INDEX t2d (d=?) LEFT-JOIN
`--INDEX 2
- `--SEARCH t2 USING COVERING INDEX t2f (f=?)
+ `--SEARCH t2 USING COVERING INDEX t2f (f=?) LEFT-JOIN
}]
}
generated by cgit v1.2.3 (git 2.25.1) at 2025-07-18 11:21:40 +0000