Artifact
e495839eab6f12cbbea810fd8b03d51439c5b594:
Wiki page
[Shadowed ROWID issues] by
sandro
2013-09-12 17:56:24.
0000: 44 20 32 30 31 33 2d 30 39 2d 31 32 54 31 37 3a D 2013-09-12T17:
0010: 35 36 3a 32 34 2e 33 38 34 0a 4c 20 53 68 61 64 56:24.384.L Shad
0020: 6f 77 65 64 5c 73 52 4f 57 49 44 5c 73 69 73 73 owed\sROWID\siss
0030: 75 65 73 0a 55 20 73 61 6e 64 72 6f 0a 57 20 36 ues.U sandro.W 6
0040: 30 35 36 0a 42 61 63 6b 20 74 6f 20 3c 61 20 68 056.Back to <a h
0050: 72 65 66 3d 22 68 74 74 70 73 3a 2f 2f 77 77 77 ref="https://www
0060: 2e 67 61 69 61 2d 67 69 73 2e 69 74 2f 66 6f 73 .gaia-gis.it/fos
0070: 73 69 6c 2f 6c 69 62 73 70 61 74 69 61 6c 69 74 sil/libspatialit
0080: 65 2f 77 69 6b 69 3f 6e 61 6d 65 3d 53 70 61 74 e/wiki?name=Spat
0090: 69 61 4c 69 74 65 22 3e 6d 61 69 6e 20 53 70 61 iaLite">main Spa
00a0: 74 69 61 4c 69 74 65 20 57 69 6b 69 20 70 61 67 tiaLite Wiki pag
00b0: 65 3c 2f 61 3e 3c 68 72 3e 3c 62 72 3e 0d 0a 3c e</a><hr><br>..<
00c0: 68 32 3e 42 72 6f 6b 65 6e 20 52 2a 54 72 65 65 h2>Broken R*Tree
00d0: 20 63 61 75 73 65 64 20 62 79 20 73 68 61 64 6f caused by shado
00e0: 77 65 64 20 52 4f 57 49 44 20 69 73 73 75 65 73 wed ROWID issues
00f0: 3c 2f 68 32 3e 0d 0a 41 20 6e 65 77 20 69 73 73 </h2>..A new iss
0100: 75 65 20 61 66 66 65 63 74 69 6e 67 20 74 68 65 ue affecting the
0110: 20 54 2a 54 72 65 65 20 53 70 61 74 69 61 6c 20 T*Tree Spatial
0120: 49 6e 64 65 78 20 68 61 73 20 62 65 65 6e 20 72 Index has been r
0130: 65 63 65 6e 74 6c 79 20 72 65 70 6f 72 74 65 64 ecently reported
0140: 2e 3c 62 72 3e 0d 0a 54 68 65 20 73 70 65 63 69 .<br>..The speci
0150: 66 69 63 20 70 72 6f 62 6c 65 6d 20 63 61 75 73 fic problem caus
0160: 69 6e 67 20 74 68 69 73 20 69 73 73 75 65 20 73 ing this issue s
0170: 65 65 6d 73 20 74 6f 20 68 61 76 65 20 61 20 76 eems to have a v
0180: 65 72 79 20 6c 6f 77 20 70 72 6f 62 61 62 69 6c ery low probabil
0190: 69 74 79 20 74 6f 20 68 61 70 70 65 6e 20 75 6e ity to happen un
01a0: 64 65 72 20 6e 6f 72 6d 61 6c 20 63 6f 6e 64 69 der normal condi
01b0: 74 69 6f 6e 73 3b 20 61 6e 79 77 61 79 2c 20 6f tions; anyway, o
01c0: 6e 63 65 20 74 68 69 73 20 70 72 6f 62 6c 65 6d nce this problem
01d0: 20 77 69 6c 6c 20 61 63 74 75 61 6c 6c 79 20 61 will actually a
01e0: 72 69 73 65 20 74 68 65 20 63 6f 6e 73 65 71 75 rise the consequ
01f0: 65 6e 63 65 73 20 63 6f 75 6c 64 20 65 61 73 69 ences could easi
0200: 6c 79 20 68 65 61 76 65 20 61 20 72 65 61 6c 6c ly heave a reall
0210: 79 20 73 74 72 6f 6e 67 20 6e 65 67 61 74 69 76 y strong negativ
0220: 65 20 69 6d 70 61 63 74 20 6f 6e 20 74 68 65 20 e impact on the
0230: 6f 76 65 72 61 6c 6c 20 70 65 72 66 6f 72 6d 61 overall performa
0240: 6e 63 65 20 6f 66 20 53 70 61 74 69 61 4c 69 74 nce of SpatiaLit
0250: 65 2e 3c 62 72 3e 0d 0a 53 6f 20 79 6f 75 20 61 e.<br>..So you a
0260: 72 65 20 6b 69 6e 64 6c 79 20 69 6e 76 69 74 65 re kindly invite
0270: 64 20 74 6f 20 63 61 72 65 66 75 6c 6c 79 20 74 d to carefully t
0280: 68 69 73 20 74 65 63 68 6e 69 63 61 6c 20 6e 6f his technical no
0290: 74 65 2e 0d 0a 3c 68 33 3e 55 6e 64 65 72 73 74 te...<h3>Underst
02a0: 61 6e 64 69 6e 67 20 77 68 61 74 20 72 65 61 6c anding what real
02b0: 6c 79 20 69 73 20 61 20 52 4f 57 49 44 3c 2f 68 ly is a ROWID</h
02c0: 33 3e 0d 0a 46 72 6f 6d 20 74 68 65 20 53 51 4c 3>..From the SQL
02d0: 69 74 65 27 73 20 6f 77 6e 20 64 6f 63 75 6d 65 ite's own docume
02e0: 6e 74 61 74 69 6f 6e 3a 0d 0a 3c 76 65 72 62 61 ntation:..<verba
02f0: 74 69 6d 3e 0d 0a 45 61 63 68 20 65 6e 74 72 79 tim>..Each entry
0300: 20 69 6e 20 61 6e 20 53 51 4c 69 74 65 20 74 61 in an SQLite ta
0310: 62 6c 65 20 68 61 73 20 61 20 75 6e 69 71 75 65 ble has a unique
0320: 20 36 34 2d 62 69 74 20 73 69 67 6e 65 64 20 69 64-bit signed i
0330: 6e 74 65 67 65 72 20 6b 65 79 20 63 61 6c 6c 65 nteger key calle
0340: 64 20 74 68 65 20 22 72 6f 77 69 64 22 2e 20 0d d the "rowid". .
0350: 0a 54 68 65 20 72 6f 77 69 64 20 69 73 20 61 6c .The rowid is al
0360: 77 61 79 73 20 61 76 61 69 6c 61 62 6c 65 20 61 ways available a
0370: 73 20 61 6e 20 75 6e 64 65 63 6c 61 72 65 64 20 s an undeclared
0380: 63 6f 6c 75 6d 6e 20 6e 61 6d 65 64 20 52 4f 57 column named ROW
0390: 49 44 2c 20 4f 49 44 2c 20 6f 72 20 5f 52 4f 57 ID, OID, or _ROW
03a0: 49 44 5f 20 61 73 20 0d 0a 6c 6f 6e 67 20 61 73 ID_ as ..long as
03b0: 20 74 68 6f 73 65 20 6e 61 6d 65 73 20 61 72 65 those names are
03c0: 20 6e 6f 74 20 61 6c 73 6f 20 75 73 65 64 20 62 not also used b
03d0: 79 20 65 78 70 6c 69 63 69 74 6c 79 20 64 65 63 y explicitly dec
03e0: 6c 61 72 65 64 20 63 6f 6c 75 6d 6e 73 2e 20 0d lared columns. .
03f0: 0a 49 66 20 74 68 65 20 74 61 62 6c 65 20 68 61 .If the table ha
0400: 73 20 61 20 63 6f 6c 75 6d 6e 20 6f 66 20 74 79 s a column of ty
0410: 70 65 20 49 4e 54 45 47 45 52 20 50 52 49 4d 41 pe INTEGER PRIMA
0420: 52 59 20 4b 45 59 20 74 68 65 6e 20 74 68 61 74 RY KEY then that
0430: 20 63 6f 6c 75 6d 6e 20 69 73 20 61 6e 6f 74 68 column is anoth
0440: 65 72 20 0d 0a 61 6c 69 61 73 20 66 6f 72 20 74 er ..alias for t
0450: 68 65 20 72 6f 77 69 64 2e 0d 0a 3c 2f 76 65 72 he rowid...</ver
0460: 62 61 74 69 6d 3e 20 0d 0a 49 6e 20 6f 74 68 65 batim> ..In othe
0470: 72 20 77 6f 72 64 73 3a 20 61 20 52 4f 57 49 44 r words: a ROWID
0480: 20 72 65 70 72 65 73 65 6e 74 73 20 61 20 55 6e represents a Un
0490: 69 71 75 65 20 49 64 65 6e 74 69 66 69 65 72 20 ique Identifier
04a0: 61 6c 6c 6f 77 69 6e 67 20 74 6f 20 72 65 74 72 allowing to retr
04b0: 69 65 76 65 20 65 76 65 72 79 20 69 6e 64 69 76 ieve every indiv
04c0: 69 64 75 61 6c 20 72 6f 77 20 66 72 6f 6d 20 61 idual row from a
04d0: 20 74 61 62 6c 65 20 69 6e 20 76 65 72 79 20 71 table in very q
04e0: 75 69 63 6b 20 74 69 6d 65 2e 3c 62 72 3e 0d 0a uick time.<br>..
04f0: 55 73 69 6e 67 20 52 4f 57 49 44 20 76 61 6c 75 Using ROWID valu
0500: 65 73 20 61 73 20 72 65 6c 61 74 69 6f 6e 61 6c es as relational
0510: 20 6b 65 79 73 20 77 68 65 6e 65 76 65 72 20 69 keys whenever i
0520: 73 20 70 6f 73 73 69 62 6c 65 20 69 73 20 72 65 s possible is re
0530: 61 6c 6c 79 20 75 73 65 66 75 6c 20 69 6e 20 6d ally useful in m
0540: 61 6e 79 20 53 51 4c 20 71 75 65 72 69 65 73 2c any SQL queries,
0550: 20 62 65 63 61 75 73 65 20 72 65 6c 61 74 69 6f because relatio
0560: 6e 61 6c 20 4a 4f 49 4e 20 6f 70 73 20 62 61 73 nal JOIN ops bas
0570: 65 64 20 6f 6e 20 52 4f 57 49 44 73 20 61 6c 77 ed on ROWIDs alw
0580: 61 79 73 20 61 72 65 20 76 65 72 79 20 65 66 66 ays are very eff
0590: 69 63 69 65 6e 74 20 61 6e 64 20 66 61 73 74 20 icient and fast
05a0: 74 6f 20 62 65 20 63 6f 6d 70 75 74 65 64 2e 3c to be computed.<
05b0: 62 72 3e 3c 62 72 3e 0d 0a 54 68 65 20 53 70 61 br><br>..The Spa
05c0: 74 69 61 6c 4c 69 74 65 27 73 20 6f 77 6e 20 69 tialLite's own i
05d0: 6d 70 6c 65 6d 65 6e 74 61 74 69 6f 6e 20 6f 66 mplementation of
05e0: 20 74 68 65 20 53 70 61 74 69 61 6c 20 49 6e 64 the Spatial Ind
05f0: 65 78 20 77 69 64 65 6c 79 20 64 65 70 65 6e 64 ex widely depend
0600: 73 20 6f 6e 20 52 4f 57 49 44 73 20 69 6e 20 6f s on ROWIDs in o
0610: 72 64 65 72 20 74 6f 20 4a 4f 49 4e 20 62 6f 74 rder to JOIN bot
0620: 68 20 74 68 65 20 6d 61 69 6e 20 74 61 62 6c 65 h the main table
0630: 20 63 6f 6e 74 61 69 6e 69 6e 67 20 47 65 6f 6d containing Geom
0640: 65 74 72 69 65 73 20 61 6e 64 20 74 68 65 20 63 etries and the c
0650: 6f 72 72 65 73 70 6f 6e 64 69 6e 67 20 52 2a 54 orresponding R*T
0660: 72 65 65 20 74 61 62 6c 65 2e 3c 62 72 3e 0d 0a ree table.<br>..
0670: 41 6e 79 77 61 79 20 74 68 65 72 65 20 69 73 20 Anyway there is
0680: 61 20 64 61 6e 67 65 72 6f 75 73 20 68 69 64 64 a dangerous hidd
0690: 65 6e 20 74 72 61 70 20 69 6e 20 61 6c 6c 20 74 en trap in all t
06a0: 68 69 73 3a 20 69 74 20 77 6f 72 6b 73 20 77 65 his: it works we
06b0: 6c 6c 20 6f 6e 6c 79 20 73 69 6e 63 65 20 20 52 ll only since R
06c0: 4f 57 49 44 20 76 61 6c 75 65 73 20 72 65 74 75 OWID values retu
06d0: 72 6e 65 64 20 62 79 20 74 68 65 20 6d 61 69 6e rned by the main
06e0: 20 74 61 62 6c 65 20 64 6f 20 65 78 61 63 74 6c table do exactl
06f0: 79 20 63 6f 72 72 65 73 70 6f 6e 64 20 74 6f 20 y correspond to
0700: 52 4f 57 49 44 20 76 61 6c 75 65 73 20 73 74 6f ROWID values sto
0710: 72 65 64 20 69 6e 74 6f 20 74 68 65 20 52 2a 54 red into the R*T
0720: 72 65 65 2e 20 49 66 20 28 66 6f 72 20 61 6e 79 ree. If (for any
0730: 20 72 65 61 73 6f 6e 29 20 74 68 65 20 61 62 6f reason) the abo
0740: 76 65 20 61 73 73 75 6d 70 74 69 6f 6e 20 69 73 ve assumption is
0750: 20 6e 6f 74 20 73 61 74 69 73 66 69 65 64 20 74 not satisfied t
0760: 68 65 20 53 70 61 74 69 61 6c 20 49 6e 64 65 78 he Spatial Index
0770: 20 77 69 6c 6c 20 73 69 6d 70 6c 79 20 72 65 74 will simply ret
0780: 75 72 6e 20 62 6f 67 75 73 20 61 6e 64 20 75 73 urn bogus and us
0790: 65 6c 65 73 73 20 72 65 73 75 6c 74 73 2e 3c 62 eless results.<b
07a0: 72 3e 3c 62 72 3e 0d 0a 59 6f 75 27 6c 6c 20 70 r><br>..You'll p
07b0: 72 6f 62 61 62 6c 79 20 62 65 20 77 65 6c 6c 20 robably be well
07c0: 61 77 61 72 65 20 74 68 61 74 20 6e 6f 74 20 64 aware that not d
07d0: 65 63 6c 61 72 69 6e 67 20 61 6e 20 49 4e 54 45 eclaring an INTE
07e0: 47 45 52 20 50 52 49 4d 41 52 59 20 4b 45 59 20 GER PRIMARY KEY
07f0: 73 75 70 70 6f 72 74 69 6e 67 20 61 6e 79 20 47 supporting any G
0800: 65 6f 6d 65 74 72 79 20 74 61 62 6c 65 20 63 6f eometry table co
0810: 75 6c 64 20 64 69 72 65 63 74 6c 79 20 63 61 75 uld directly cau
0820: 73 65 20 6d 61 6e 79 20 73 65 76 65 72 65 20 53 se many severe S
0830: 70 61 74 69 61 6c 20 49 6e 64 65 78 20 68 65 61 patial Index hea
0840: 64 61 63 68 65 73 20 69 6d 6d 65 64 69 61 74 65 daches immediate
0850: 6c 79 20 61 66 74 65 72 20 70 65 72 66 6f 72 6d ly after perform
0860: 69 6e 67 20 61 20 56 41 43 55 55 4d 2e 0d 0a 49 ing a VACUUM...I
0870: 66 20 79 6f 75 20 6d 69 73 73 65 64 20 74 68 69 f you missed thi
0880: 73 20 70 6f 69 6e 74 2c 20 72 65 61 64 69 6e 67 s point, reading
0890: 20 74 68 69 73 20 3c 61 20 68 72 65 66 3d 22 68 this <a href="h
08a0: 74 74 70 3a 2f 2f 77 77 77 2e 67 61 69 61 2d 67 ttp://www.gaia-g
08b0: 69 73 2e 69 74 2f 73 70 61 74 69 61 6c 69 74 65 is.it/spatialite
08c0: 2d 33 2e 30 2e 30 2d 42 45 54 41 2f 53 70 61 74 -3.0.0-BETA/Spat
08d0: 69 61 6c 49 6e 64 65 78 2d 55 70 64 61 74 65 2e ialIndex-Update.
08e0: 70 64 66 22 3e 6f 6c 64 20 74 65 63 68 6e 69 63 pdf">old technic
08f0: 61 6c 20 6e 6f 74 65 3c 2f 61 3e 20 69 73 20 77 al note</a> is w
0900: 61 72 6d 6c 79 20 73 75 67 67 65 73 74 65 64 2e armly suggested.
0910: 3c 62 72 3e 0d 0a 54 68 65 20 72 65 63 65 6e 74 <br>..The recent
0920: 6c 79 20 64 69 73 63 6f 76 65 72 65 64 20 69 73 ly discovered is
0930: 73 75 65 20 69 73 20 65 76 65 6e 20 77 6f 72 73 sue is even wors
0940: 74 20 28 69 66 20 70 6f 73 73 69 62 6c 65 29 20 t (if possible)
0950: 74 68 61 6e 20 74 68 65 20 70 72 65 76 69 6f 75 than the previou
0960: 73 20 6f 6e 65 3a 20 69 74 20 77 69 6c 6c 20 6e s one: it will n
0970: 6f 74 20 73 69 6d 70 6c 79 20 63 61 75 73 65 20 ot simply cause
0980: 61 20 73 65 76 65 72 65 20 53 70 61 74 69 61 6c a severe Spatial
0990: 20 49 6e 64 65 78 20 63 6f 72 72 75 70 74 69 6f Index corruptio
09a0: 6e 2c 20 69 74 20 63 6f 75 6c 64 20 65 76 65 6e n, it could even
09b0: 74 75 61 6c 6c 79 20 74 72 61 6e 73 66 6f 72 6d tually transform
09c0: 20 61 6e 79 20 53 51 4c 20 71 75 65 72 79 20 61 any SQL query a
09d0: 74 74 65 6d 70 74 69 6e 67 20 74 6f 20 61 63 63 ttempting to acc
09e0: 65 73 73 20 74 68 65 20 53 70 61 74 69 61 6c 20 ess the Spatial
09f0: 49 6e 64 65 78 20 69 6e 74 6f 20 61 20 64 65 61 Index into a dea
0a00: 64 6c 79 20 73 6c 6f 77 20 71 75 65 72 79 2e 3c dly slow query.<
0a10: 62 72 3e 3c 62 72 3e 0d 0a 4e 6f 77 20 77 65 27 br><br>..Now we'
0a20: 6c 6c 20 72 65 61 64 20 79 65 74 20 61 67 61 69 ll read yet agai
0a30: 6e 20 74 68 65 20 61 62 6f 76 65 20 70 61 72 61 n the above para
0a40: 67 72 61 70 68 20 74 61 6b 65 6e 20 66 72 6f 6d graph taken from
0a50: 20 53 51 4c 69 74 65 27 73 20 6f 77 6e 20 64 6f SQLite's own do
0a60: 63 75 6d 65 6e 74 61 74 69 6f 6e 2c 20 74 68 69 cumentation, thi
0a70: 73 20 74 69 6d 65 20 70 61 79 69 6e 67 20 74 68 s time paying th
0a80: 65 20 64 75 65 20 61 74 74 65 6e 74 69 6f 6e 20 e due attention
0a90: 74 6f 20 61 6e 79 20 73 69 6e 67 6c 65 20 77 6f to any single wo
0aa0: 72 6c 64 3a 3c 62 72 3e 0d 0a 3c 74 61 62 6c 65 rld:<br>..<table
0ab0: 20 62 67 63 6f 6c 6f 72 3d 22 23 66 66 66 66 64 bgcolor="#ffffd
0ac0: 64 22 20 63 65 6c 6c 70 61 64 64 69 6e 67 3d 22 d" cellpadding="
0ad0: 38 22 20 63 65 6c 6c 73 70 61 63 69 6e 67 3d 22 8" cellspacing="
0ae0: 38 22 3e 3c 74 72 3e 3c 74 64 3e 0d 0a 3c 62 3e 8"><tr><td>..<b>
0af0: 54 68 65 20 72 6f 77 69 64 20 69 73 20 61 6c 77 The rowid is alw
0b00: 61 79 73 20 61 76 61 69 6c 61 62 6c 65 20 61 73 ays available as
0b10: 20 61 6e 20 75 6e 64 65 63 6c 61 72 65 64 20 63 an undeclared c
0b20: 6f 6c 75 6d 6e 20 6e 61 6d 65 64 20 52 4f 57 49 olumn named ROWI
0b30: 44 2c 20 4f 49 44 2c 20 6f 72 20 5f 52 4f 57 49 D, OID, or _ROWI
0b40: 44 5f 3c 62 72 3e 0d 0a 3c 66 6f 6e 74 20 63 6f D_<br>..<font co
0b50: 6c 6f 72 3d 22 23 66 66 30 30 30 30 22 3e 3c 75 lor="#ff0000"><u
0b60: 3e 61 73 20 6c 6f 6e 67 20 61 73 20 74 68 6f 73 >as long as thos
0b70: 65 20 6e 61 6d 65 73 20 61 72 65 20 6e 6f 74 20 e names are not
0b80: 61 6c 73 6f 20 75 73 65 64 20 62 79 20 65 78 70 also used by exp
0b90: 6c 69 63 69 74 6c 79 20 64 65 63 6c 61 72 65 64 licitly declared
0ba0: 20 63 6f 6c 75 6d 6e 73 3c 2f 75 3e 3c 2f 66 6f columns</u></fo
0bb0: 6e 74 3e 3c 2f 62 3e 2e 0d 0a 3c 2f 74 64 3e 3c nt></b>...</td><
0bc0: 2f 74 72 3e 3c 2f 74 61 62 6c 65 3e 3c 62 72 3e /tr></table><br>
0bd0: 0d 0a 48 65 72 65 20 69 73 20 74 68 65 20 64 61 ..Here is the da
0be0: 6e 67 65 72 6f 75 73 20 70 69 74 66 61 6c 6c 3a ngerous pitfall:
0bf0: 20 69 66 20 74 68 65 20 73 61 6d 65 20 74 61 62 if the same tab
0c00: 6c 65 20 63 6f 6e 74 61 69 6e 69 6e 67 20 79 6f le containing yo
0c10: 75 72 20 47 65 6f 6d 65 74 72 69 65 73 20 61 63 ur Geometries ac
0c20: 74 75 61 6c 6c 79 20 64 65 63 6c 61 72 65 73 20 tually declares
0c30: 28 66 6f 72 20 61 6e 79 20 70 6f 73 73 69 62 6c (for any possibl
0c40: 65 20 72 65 61 73 6f 6e 29 20 61 6e 20 6f 72 64 e reason) an ord
0c50: 69 6e 61 72 79 20 63 6f 6c 75 6d 6e 20 6e 61 6d inary column nam
0c60: 65 64 20 22 3c 62 3e 72 6f 77 69 64 3c 2f 62 3e ed "<b>rowid</b>
0c70: 22 2c 20 74 68 65 6e 20 61 6c 6c 20 76 61 6c 75 ", then all valu
0c80: 65 73 20 63 6f 6e 74 61 69 6e 65 64 20 69 6e 74 es contained int
0c90: 6f 20 74 68 69 73 20 63 6f 6c 75 6d 6e 20 77 69 o this column wi
0ca0: 6c 6c 20 63 6f 6d 70 6c 65 74 65 6c 79 20 73 68 ll completely sh
0cb0: 61 64 6f 77 20 74 68 65 20 65 78 70 65 63 74 65 adow the expecte
0cc0: 64 20 55 6e 69 71 75 65 20 49 64 65 6e 74 69 66 d Unique Identif
0cd0: 69 65 72 73 20 61 6c 6c 6f 77 69 6e 67 20 74 6f iers allowing to
0ce0: 20 69 6d 6d 65 64 69 61 74 65 6c 79 20 61 63 63 immediately acc
0cf0: 65 73 73 20 65 61 63 68 20 73 69 6e 67 6c 65 20 ess each single
0d00: 72 6f 77 20 28 61 6b 61 20 3c 62 3e 52 4f 57 49 row (aka <b>ROWI
0d10: 44 3c 2f 62 3e 20 76 61 6c 75 65 73 2c 20 61 73 D</b> values, as
0d20: 20 75 73 75 61 6c 6c 79 20 69 6e 74 65 6e 64 65 usually intende
0d30: 64 29 2e 3c 62 72 3e 0d 0a 41 6e 64 20 61 73 20 d).<br>..And as
0d40: 61 20 72 61 74 68 65 72 20 6f 62 76 69 6f 75 73 a rather obvious
0d50: 20 63 6f 6e 73 65 71 75 65 6e 63 65 20 61 20 73 consequence a s
0d60: 74 72 6f 6e 67 20 72 65 6c 61 74 69 6f 6e 73 68 trong relationsh
0d70: 69 70 20 4a 4f 49 4e 69 6e 67 20 65 61 63 68 20 ip JOINing each
0d80: 47 65 6f 6d 65 74 72 79 20 74 6f 20 69 74 73 20 Geometry to its
0d90: 53 70 61 74 69 61 6c 20 49 6e 64 65 78 20 65 6e Spatial Index en
0da0: 74 72 79 20 77 69 6c 6c 20 69 6d 6d 65 64 69 61 try will immedia
0db0: 74 65 6c 79 20 76 61 6e 69 73 68 2e 3c 62 72 3e tely vanish.<br>
0dc0: 3c 62 72 3e 0d 0a 54 68 61 74 27 73 20 6e 6f 74 <br>..That's not
0dd0: 20 61 6c 6c 3a 20 74 68 65 20 77 6f 72 73 74 20 all: the worst
0de0: 63 6f 6e 73 65 71 75 65 6e 63 65 20 77 69 6c 6c consequence will
0df0: 20 6e 6f 77 20 62 65 20 74 68 61 74 20 69 6e 73 now be that ins
0e00: 74 65 61 64 20 75 73 69 6e 67 20 61 20 72 65 61 tead using a rea
0e10: 6c 6c 79 20 66 61 73 74 20 6b 65 79 20 61 6c 6c lly fast key all
0e20: 6f 77 69 6e 67 20 74 6f 20 71 75 69 63 6b 6c 79 owing to quickly
0e30: 20 72 65 74 72 69 65 76 65 20 65 61 63 68 20 72 retrieve each r
0e40: 6f 77 2c 20 79 6f 75 72 20 71 75 65 72 79 20 75 ow, your query u
0e50: 6e 64 65 72 20 73 75 63 68 20 63 6f 6e 64 69 74 nder such condit
0e60: 69 6f 6e 73 20 77 69 6c 6c 20 72 65 71 75 69 72 ions will requir
0e70: 65 20 61 20 6c 65 6e 67 74 68 79 20 3c 75 3e 66 e a lengthy <u>f
0e80: 75 6c 6c 20 74 61 62 6c 65 20 73 63 61 6e 3c 2f ull table scan</
0e90: 75 3e 20 66 6f 72 20 65 61 63 68 20 73 69 6e 67 u> for each sing
0ea0: 6c 65 20 72 6f 77 20 74 6f 20 62 65 20 72 65 73 le row to be res
0eb0: 6f 6c 76 65 64 3b 20 61 6e 64 20 63 6f 6e 73 65 olved; and conse
0ec0: 71 75 65 6e 74 6c 79 20 61 20 53 70 61 74 69 61 quently a Spatia
0ed0: 6c 20 49 6e 64 65 78 20 71 75 65 72 79 20 65 78 l Index query ex
0ee0: 70 65 63 74 65 64 20 74 6f 20 61 64 64 20 61 20 pected to add a
0ef0: 6e 6f 74 69 63 65 61 62 6c 65 20 70 65 72 66 6f noticeable perfo
0f00: 72 6d 61 6e 63 65 20 62 6f 6f 73 74 20 77 69 6c rmance boost wil
0f10: 6c 20 63 61 75 73 65 20 69 6e 73 74 65 61 64 20 l cause instead
0f20: 61 20 63 61 74 61 73 74 72 6f 70 68 69 63 20 70 a catastrophic p
0f30: 65 72 66 6f 72 6d 61 6e 63 65 20 6c 6f 73 73 2e erformance loss.
0f40: 3c 62 72 3e 3c 62 72 3e 0d 0a 48 61 70 70 69 6c <br><br>..Happil
0f50: 79 20 65 6e 6f 75 67 68 20 74 68 69 73 20 69 73 y enough this is
0f60: 73 75 65 20 64 6f 65 73 6e 27 74 20 73 65 65 6d sue doesn't seem
0f70: 73 20 74 6f 20 6d 61 74 65 72 69 61 6c 69 7a 65 s to materialize
0f80: 20 74 6f 6f 20 6f 66 74 65 6e 20 69 6e 20 72 65 too often in re
0f90: 61 6c 20 77 6f 72 6c 64 20 63 61 73 65 73 3b 20 al world cases;
0fa0: 61 6e 79 77 61 79 20 6f 6e 63 65 20 69 74 20 6f anyway once it o
0fb0: 63 63 75 72 73 20 69 74 20 63 6f 75 6c 64 20 72 ccurs it could r
0fc0: 65 61 6c 6c 79 20 68 61 76 65 20 61 20 64 72 61 eally have a dra
0fd0: 6d 61 74 69 63 20 6e 65 67 61 74 69 76 65 20 69 matic negative i
0fe0: 6d 70 61 63 74 2e 3c 62 72 3e 0d 0a 42 65 20 61 mpact.<br>..Be a
0ff0: 77 61 72 65 20 61 6e 64 20 77 65 6c 6c 20 63 6f ware and well co
1000: 6e 73 63 69 6f 75 73 20 6f 66 20 74 68 65 20 70 nscious of the p
1010: 72 6f 62 6c 65 6d 2e 0d 0a 3c 68 33 3e 52 65 6d roblem...<h3>Rem
1020: 65 64 69 65 73 20 61 6e 64 20 63 75 72 65 73 3c edies and cures<
1030: 2f 68 33 3e 0d 0a 54 68 65 20 63 75 72 72 65 6e /h3>..The curren
1040: 74 20 63 6f 64 65 20 62 61 73 65 20 28 61 6c 72 t code base (alr
1050: 65 61 64 79 20 61 76 61 69 6c 61 62 6c 65 20 66 eady available f
1060: 72 6f 6d 20 74 68 65 20 46 6f 73 73 69 6c 20 72 rom the Fossil r
1070: 65 70 6f 73 69 74 6f 72 79 29 20 61 64 6f 70 74 epository) adopt
1080: 73 20 74 68 65 20 66 6f 6c 6c 6f 77 69 6e 67 20 s the following
1090: 70 72 65 63 61 75 74 69 6f 6e 73 20 69 6e 20 6f precautions in o
10a0: 72 64 65 72 20 74 6f 20 6d 69 74 69 67 61 74 65 rder to mitigate
10b0: 20 74 68 69 73 20 72 65 63 65 6e 74 6c 79 20 64 this recently d
10c0: 69 73 63 6f 76 65 72 65 64 20 69 73 73 75 65 3a iscovered issue:
10d0: 0d 0a 3c 75 6c 3e 0d 0a 3c 6c 69 3e 74 68 65 20 ..<ul>..<li>the
10e0: 3c 62 3e 43 72 65 61 74 65 53 70 61 74 69 61 6c <b>CreateSpatial
10f0: 49 6e 64 65 78 3c 2f 62 3e 20 53 51 4c 20 66 75 Index</b> SQL fu
1100: 6e 63 74 69 6f 6e 20 77 69 6c 6c 20 6e 6f 77 20 nction will now
1110: 72 65 66 75 73 65 20 74 6f 20 62 75 69 6c 64 20 refuse to build
1120: 61 20 53 70 61 74 69 61 6c 20 49 6e 64 65 78 20 a Spatial Index
1130: 69 66 20 74 68 65 20 63 6f 72 72 65 73 70 6f 6e if the correspon
1140: 64 69 6e 67 20 74 61 62 6c 65 20 64 65 63 6c 61 ding table decla
1150: 72 65 73 20 61 6e 79 20 63 6f 6c 75 6d 6e 20 6e res any column n
1160: 61 6d 65 64 20 3c 62 3e 72 6f 77 69 64 3c 2f 62 amed <b>rowid</b
1170: 3e 2c 0d 0a 72 65 74 75 72 6e 69 6e 67 20 69 6e >,..returning in
1180: 20 74 68 69 73 20 63 61 73 65 20 61 20 3c 62 3e this case a <b>
1190: 2d 31 3c 2f 62 3e 20 76 61 6c 75 65 20 28 73 70 -1</b> value (sp
11a0: 65 63 69 66 20 65 72 72 6f 72 20 63 6f 6e 64 69 ecif error condi
11b0: 74 69 6f 6e 29 2e 0d 0a 3c 75 3e 50 6c 65 61 73 tion)...<u>Pleas
11c0: 65 20 6e 6f 74 65 3c 2f 75 3e 3a 20 74 68 69 73 e note</u>: this
11d0: 20 69 73 6e 27 74 20 61 20 63 6f 6d 70 6c 65 74 isn't a complet
11e0: 65 20 72 65 6d 65 64 79 2c 20 62 65 63 61 75 73 e remedy, becaus
11f0: 65 20 61 20 63 6f 6c 75 6d 6e 20 6e 61 6d 65 64 e a column named
1200: 20 3c 62 3e 72 6f 77 69 64 3c 2f 62 3e 20 63 6f <b>rowid</b> co
1210: 75 6c 64 20 65 76 65 6e 74 75 61 6c 6c 79 20 62 uld eventually b
1220: 65 20 61 64 64 65 64 20 74 6f 20 74 68 65 20 74 e added to the t
1230: 61 62 6c 65 20 69 6e 20 61 20 73 65 63 6f 6e 64 able in a second
1240: 20 74 69 6d 65 20 62 79 20 69 6e 76 6f 6b 69 6e time by invokin
1250: 67 20 61 6e 20 3c 62 3e 41 4c 54 45 52 20 54 41 g an <b>ALTER TA
1260: 42 4c 45 20 2e 2e 2e 20 41 44 44 20 43 4f 4c 55 BLE ... ADD COLU
1270: 4d 4e 20 72 6f 77 69 64 20 2e 2e 2e 3c 2f 62 3e MN rowid ...</b>
1280: 20 73 74 61 74 65 6d 65 6e 74 2e 3c 2f 6c 69 3e statement.</li>
1290: 0d 0a 3c 6c 69 3e 74 68 65 20 3c 62 3e 43 68 65 ..<li>the <b>Che
12a0: 63 6b 53 70 61 74 69 61 6c 49 6e 64 65 78 3c 2f ckSpatialIndex</
12b0: 62 3e 20 53 51 4c 20 66 75 6e 63 74 69 6f 6e 20 b> SQL function
12c0: 77 69 6c 6c 20 61 6c 77 61 79 73 20 72 65 66 75 will always refu
12d0: 73 65 20 74 6f 20 63 68 65 63 6b 20 61 6e 79 20 se to check any
12e0: 53 70 61 74 69 61 6c 20 49 6e 64 65 78 20 63 6f Spatial Index co
12f0: 72 72 65 73 70 6f 6e 64 69 6e 67 20 74 6f 20 61 rresponding to a
1300: 20 73 68 61 64 6f 77 65 64 20 3c 62 3e 72 6f 77 shadowed <b>row
1310: 69 64 3c 2f 62 3e 20 69 6d 6d 65 64 69 61 74 65 id</b> immediate
1320: 6c 79 20 72 65 74 75 72 6e 69 6e 67 20 61 20 3c ly returning a <
1330: 62 3e 2d 31 3c 2f 62 3e 20 76 61 6c 75 65 2e 3c b>-1</b> value.<
1340: 2f 6c 69 3e 0d 0a 3c 6c 69 3e 74 68 65 20 3c 62 /li>..<li>the <b
1350: 3e 52 65 63 6f 76 65 72 53 70 61 74 69 61 6c 49 >RecoverSpatialI
1360: 6e 64 65 78 3c 2f 62 3e 20 53 51 4c 20 66 75 6e ndex</b> SQL fun
1370: 63 74 69 6f 6e 20 61 73 20 77 65 6c 6c 20 77 69 ction as well wi
1380: 6c 6c 20 72 65 66 75 73 65 20 74 6f 20 63 68 65 ll refuse to che
1390: 63 6b 20 61 6e 79 20 53 70 61 74 69 61 6c 20 49 ck any Spatial I
13a0: 6e 64 65 78 20 63 6f 72 72 65 73 70 6f 6e 64 69 ndex correspondi
13b0: 6e 67 20 74 6f 20 61 20 73 68 61 64 6f 77 65 64 ng to a shadowed
13c0: 20 3c 62 3e 72 6f 77 69 64 3c 2f 62 3e 20 69 6d <b>rowid</b> im
13d0: 6d 65 64 69 61 74 65 6c 79 20 72 65 74 75 72 6e mediately return
13e0: 69 6e 67 20 61 20 3c 62 3e 2d 31 3c 2f 62 3e 20 ing a <b>-1</b>
13f0: 76 61 6c 75 65 2e 3c 2f 6c 69 3e 0d 0a 3c 6c 69 value.</li>..<li
1400: 3e 74 68 65 20 3c 62 3e 56 69 72 74 75 61 6c 53 >the <b>VirtualS
1410: 70 61 74 69 61 6c 49 6e 64 65 78 3c 2f 62 3e 20 patialIndex</b>
1420: 69 6e 74 65 72 66 61 63 65 20 77 69 6c 6c 20 72 interface will r
1430: 65 66 75 73 65 20 74 6f 20 61 63 63 65 73 73 20 efuse to access
1440: 61 6e 79 20 53 70 61 74 69 61 6c 20 49 6e 64 65 any Spatial Inde
1450: 78 20 63 6f 72 72 65 73 70 6f 6e 64 69 6e 67 20 x corresponding
1460: 74 6f 20 61 20 73 68 61 64 6f 77 65 64 20 3c 62 to a shadowed <b
1470: 3e 72 6f 77 69 64 3c 2f 62 3e 2e 3c 2f 6c 69 3e >rowid</b>.</li>
1480: 0d 0a 3c 6c 69 3e 6c 61 73 74 20 62 75 74 20 6e ..<li>last but n
1490: 6f 74 20 6c 65 61 73 74 2c 20 61 20 62 72 61 6e ot least, a bran
14a0: 64 20 6e 65 77 20 3c 62 3e 43 68 65 63 6b 53 68 d new <b>CheckSh
14b0: 61 64 6f 77 65 64 52 6f 77 69 64 3c 2f 62 3e 20 adowedRowid</b>
14c0: 53 51 4c 20 66 75 6e 63 74 69 6f 6e 20 68 61 73 SQL function has
14d0: 20 62 65 65 6e 20 69 6d 70 6c 65 6d 65 6e 74 65 been implemente
14e0: 64 2e 0d 0a 3c 2f 75 6c 3e 0d 0a 3c 68 33 3e 48 d...</ul>..<h3>H
14f0: 6f 77 20 74 6f 20 75 73 65 20 43 68 65 63 6b 53 ow to use CheckS
1500: 68 61 64 6f 77 65 64 52 6f 77 69 64 3c 2f 68 33 hadowedRowid</h3
1510: 3e 0d 0a 3c 76 65 72 62 61 74 69 6d 3e 0d 0a 53 >..<verbatim>..S
1520: 45 4c 45 43 54 20 66 5f 74 61 62 6c 65 5f 6e 61 ELECT f_table_na
1530: 6d 65 2c 20 66 5f 67 65 6f 6d 65 74 72 79 5f 63 me, f_geometry_c
1540: 6f 6c 75 6d 6e 0d 0a 46 52 4f 4d 20 67 65 6f 6d olumn..FROM geom
1550: 65 74 72 79 5f 63 6f 6c 75 6d 6e 73 0d 0a 57 48 etry_columns..WH
1560: 45 52 45 20 43 68 65 63 6b 53 68 61 64 6f 77 65 ERE CheckShadowe
1570: 64 52 6f 77 69 64 28 66 5f 74 61 62 6c 65 5f 6e dRowid(f_table_n
1580: 61 6d 65 29 20 3d 20 31 3b 0d 0a 2d 2d 2d 2d 2d ame) = 1;..-----
1590: 2d 0d 0a 62 72 6f 6b 65 6e 5f 74 61 62 6c 65 09 -..broken_table.
15a0: 67 65 6f 6d 65 74 72 79 0d 0a 69 6e 73 61 6e 65 geometry..insane
15b0: 5f 74 61 62 6c 65 09 67 65 6f 6d 65 74 72 79 0d _table.geometry.
15c0: 0a 63 72 61 7a 79 5f 74 61 62 6c 65 09 67 65 6f .crazy_table.geo
15d0: 6d 65 74 72 79 0d 0a 3c 2f 76 65 72 62 61 74 69 metry..</verbati
15e0: 6d 3e 0d 0a 45 78 65 63 75 74 69 6e 67 20 61 6e m>..Executing an
15f0: 20 65 6c 65 6d 65 6e 74 61 72 79 20 73 69 6d 70 elementary simp
1600: 6c 65 20 71 75 65 72 79 20 61 73 20 74 68 65 20 le query as the
1610: 61 62 6f 76 65 20 6f 6e 65 20 6a 75 73 74 20 72 above one just r
1620: 65 71 75 69 72 65 73 20 66 65 77 20 73 65 63 6f equires few seco
1630: 6e 64 73 3b 20 61 6e 64 20 63 6f 75 6c 64 20 70 nds; and could p
1640: 72 6f 62 61 62 6c 79 20 61 76 6f 69 64 20 74 6f robably avoid to
1650: 20 73 70 65 6e 64 20 6d 61 6e 79 20 6c 6f 6e 67 spend many long
1660: 20 68 6f 75 72 73 20 69 6e 20 66 72 75 69 74 6c hours in fruitl
1670: 65 73 73 20 64 65 62 75 67 67 69 6e 67 20 73 65 ess debugging se
1680: 73 73 69 6f 6e 73 2e 3c 62 72 3e 0d 0a 44 6f 6e ssions.<br>..Don
1690: 27 74 20 68 65 73 69 74 61 74 65 20 74 6f 20 69 't hesitate to i
16a0: 6d 6d 65 64 69 61 74 65 6c 79 20 70 65 72 66 6f mmediately perfo
16b0: 72 6d 20 61 20 63 68 65 63 6b 20 6c 69 6b 65 20 rm a check like
16c0: 74 68 69 73 20 69 66 20 79 6f 75 20 73 69 6d 70 this if you simp
16d0: 6c 79 20 73 75 73 70 65 63 74 20 74 68 61 74 20 ly suspect that
16e0: 73 6f 6d 65 74 68 69 6e 67 20 69 73 20 67 6f 69 something is goi
16f0: 6e 67 20 62 61 64 20 69 6e 20 79 6f 75 72 20 53 ng bad in your S
1700: 70 61 74 69 61 6c 20 49 6e 64 69 63 65 73 2c 20 patial Indices,
1710: 6f 72 20 77 68 65 6e 20 79 6f 75 20 6e 6f 74 69 or when you noti
1720: 63 65 20 73 6f 6d 65 20 70 75 7a 7a 6c 69 6e 67 ce some puzzling
1730: 20 6f 72 20 75 6e 65 78 70 65 63 74 65 64 20 62 or unexpected b
1740: 65 68 61 76 69 6f 75 72 20 70 6f 73 73 69 62 6c ehaviour possibl
1750: 79 20 72 65 6c 61 74 65 64 20 74 6f 20 53 70 61 y related to Spa
1760: 74 69 61 6c 20 49 6e 64 69 63 65 73 2e 3c 62 72 tial Indices.<br
1770: 3e 3c 62 72 3e 0d 0a 3c 68 72 3e 42 61 63 6b 20 ><br>..<hr>Back
1780: 74 6f 20 3c 61 20 68 72 65 66 3d 22 68 74 74 70 to <a href="http
1790: 73 3a 2f 2f 77 77 77 2e 67 61 69 61 2d 67 69 73 s://www.gaia-gis
17a0: 2e 69 74 2f 66 6f 73 73 69 6c 2f 6c 69 62 73 70 .it/fossil/libsp
17b0: 61 74 69 61 6c 69 74 65 2f 77 69 6b 69 3f 6e 61 atialite/wiki?na
17c0: 6d 65 3d 53 70 61 74 69 61 4c 69 74 65 22 3e 6d me=SpatiaLite">m
17d0: 61 69 6e 20 53 70 61 74 69 61 4c 69 74 65 20 57 ain SpatiaLite W
17e0: 69 6b 69 20 70 61 67 65 3c 2f 61 3e 0a 5a 20 38 iki page</a>.Z 8
17f0: 61 34 34 36 62 39 62 39 35 34 30 37 33 61 63 63 a446b9b954073acc
1800: 61 64 33 33 33 61 33 63 35 36 63 38 62 31 37 0a ad333a3c56c8b17.