Subscribe to this thread
Home - General / All posts - Update query M8 versus M9
yves61
169 post(s)
#13-Sep-20 00:14

this query is working for me in M8:

-- Manifold8

UPDATE (

SELECT A.id, A.name, B.id, B.name

FROM [sometable_1] as A, [sometable_2] as B

WHERE

CONTAINS(A.id,B.id)

)

SET B.name = A.name

;

****

I am trying to find a similar query to use in M9.

I thought the following would work but does not, even so a Btree index is set to [mfd_id] and a Rtree index is set to [Geom]

-- Manifold9

Update (

select A.[mfd_id], A.[name],A.[Geom], B.[mfd_id] , B.[name],B.[Geom]

FROM

[sometable_1] as A, [sometable_2] as B

WHERE

GeomContains(B.[Geom], A.[Geom], 0)

)

Set B.[name] = A.[name]

;

********

Any help is appreciated.

tjhb

9,409 post(s)
#13-Sep-20 02:12

Nice example. Your approach is basically correct, but misses one important thing arising because of the join.

(1) First take the case of Manifold 8.

The join is

FROM A, B

WHERE Contains(A.ID, B.ID)

or more explicitly (same logic)

FROM A INNER JOIN B

ON Contains(A.ID, B.ID)

What happens if, for some given record in A, there are more than records in B whose geometry meets the join condition, that is, if the geom in A contains more than one geoms in B?

Then that record from A will be listed more than once in the joined table, each time against a different record from B.

(It will be the same the other way around, i.e. if for a given record in B, its geom is contained by the geom of more than one record in A. But that does not make the difference here.)

What will happen to the UPDATE in this case? In 8, any target record in A which is listed multiple times will likewise be updated multiple times, with the last update winning. That "works" but is normally an unpredictable result.

(2) Now look at 9. A similar join, with slightly different syntax (and different effective tolerance).

FROM A INNER JOIN B

On GeomContains(A.[Geom], B.[Geom], 0)

Now what happens? At first the same thing.

Some record(s) from A might be matched against more than one record from B, and if that happens, that or those record(s) will again be listed more than once.

The first important point is that whether this occurs depends on the data. Until the actual data is examined, it remains a possibility. Manifold compiles a query before source data is examined, so it must allow for this possibility: it must assume that some record(s) may be multiply matched in the join.

This means that it can't assume that the BTREE from the left table will remain valid after the join. Some records might be matched multiple times, some might not be matched at all, meaning a BTREE structure would fail.

It therefore pre-emptively converts the BTREE from the left table (and from the right table, but this is not the present point) to a BTREEDUPNULL index, to allow for both possibilities.

That is why you can't UPDATE this joined table in 9. The index on the joined table is no longer a BTREE, and moreover is no longer directly inherited from the (left) source table. There is no longer a one-to-one match between records. (You can compare this to the result of an aggregate, which also breaks the one-to-one link.)

Why can Manifold 8 make this "just work", while Manifold 9 cannot? Well, remember that 8 is doing something logically unpredictable (in advance of knowing the exact data), while 9 insists on predictability (in all cases). 9 cares, 8 does not.

Next step? How to make sure that each record from the left table can only be listed once, so that its BTREE index can be (and will be) strictly preserved in the joined table?

...

tjhb

9,409 post(s)
#13-Sep-20 02:30

...

You probably know what I am going to say.

To force uniqueness in the left table, you must use its records for grouping. In other words, you must use an aggregate expression to enforce the choice of only one record from the right table to match each record from the left, making this explicit in the query logic.

The Manifold 9 engine will see this logic and respect it, preserving any existing BTREE index on the left table, so that its source records can be matched one-to-one and updated without ambiguity. (On the other hand, any BTREE from the right table will no longer be converted to BTREEDUPNULL, but simply dropped. That is not an issue here, since we only care about the index from the left.)

The easiest way to achieve this is to use FIRST or LAST, assuming that any match is valid, wherever several are possible. In some cases you may know (even though the engine does not) that several matches are not possible, so FIRST or LAST is also determinate in practice.

Other times, you might want to adjust your aggregate to allow explicitly for multiple matches, and where they arise, choose a particular record based on a defined ranking.

An intermediate approach is to add a second aggregate to COUNT matches, and a sanity check using a WHERE filter, even if you "know" that multiple matches "cannot" arise. Data can be surprising!

tjhb

9,409 post(s)
#13-Sep-20 02:42

[I made one mistake above (I hope only one).

Because this is an INNER join, the engine in 9 would AFAIK convert BTREE indexes on either side to BTREEDUP not BTREEDUPNULL--that is, without without grouping to guarantee uniqueness on one side. For an INNER join, multiple matches can occur, but non-matches are filtered out. NULL values could only arise if it were using an OUTER join.

This error does not change anything here.]

tjhb

9,409 post(s)
#13-Sep-20 07:47

For a more succinct statement, see adamw at http://www.georeference.org/forum/t148468#148984.

Manifold User Community Use Agreement Copyright (C) 2007-2019 Manifold Software Limited. All rights reserved.