fix(ddl): use column names, not position, for insertion order

[gforsyth]

If we have an ir.Table (or something that will be converted to a memtable before insertion), we now generate the column list for the sge.Insert using the column ordering of the to-be-inserted-table, not the parent table. This is technically still positional ordering, but the ordering matches up with the target table.

So if we have t

┏━━━━━━━┳━━━━━━━┓
┃ x     ┃ y     ┃
┡━━━━━━━╇━━━━━━━┩
│ int64 │ int64 │
├───────┼───────┤
│     6 │     0 │
│     5 │     1 │
└───────┴───────┘

and t2

┏━━━━━━━┳━━━━━━━┓
┃ y     ┃ x     ┃
┡━━━━━━━╇━━━━━━━┩
│ int64 │ int64 │
├───────┼───────┤
│     1 │     3 │
│     2 │     4 │
└───────┴───────┘

then we will generate something like INSERT INTO t (y, x) (SELECT * FROM t2) which will yield

>>> con.insert("t", t2)

>>> con.tables.t
┏━━━━━━━┳━━━━━━━┓
┃ x     ┃ y     ┃
┡━━━━━━━╇━━━━━━━┩
│ int64 │ int64 │
├───────┼───────┤
│     6 │     0 │
│     5 │     1 │
│     3 │     1 │
│     4 │     2 │
└───────┴───────┘

The other case is where a user passes in records directly, which we will convert into an intermediate memtable, something like:

>>> ibis.memtable([(1, 2), (3, 4)])
┏━━━━━━━┳━━━━━━━┓
┃ col0  ┃ col1  ┃
┡━━━━━━━╇━━━━━━━┩
│ int64 │ int64 │
├───────┼───────┤
│     1 │     2 │
│     3 │     4 │
└───────┴───────┘

We definitely cannot use those column names to handle insertion, so in this case, we revert back to using the parent tables columns, which results in using positional ordering for the insert, which seems like the only reasonable thing to do in this scenario.

That SQL will look something like INSERT INTO t (x,y) (SELECT * FROM some_memtable)

>>> con.insert("t", [(1, 2), (3, 4)])  # implicit positional insert

>>> con.tables.t
┏━━━━━━━┳━━━━━━━┓
┃ x     ┃ y     ┃
┡━━━━━━━╇━━━━━━━┩
│ int64 │ int64 │
├───────┼───────┤
│     6 │     0 │
│     5 │     1 │
│     3 │     1 │
│     4 │     2 │
│     1 │     2 │
│     3 │     4 │
└───────┴───────┘

Resolves #9249

• fix(impala): use column names for insertion order, not position
• fix(sqlite): use column names, not position, for insertion order
• fix(SQLBackend): use column names, not position, for insertion order
• fix(clickhouse): use column names, not position, for insertion order
• test(all): add test case for insert-by-name

[gforsyth]

Hmm, I'm going to have to rethink this -- moving this to draft for the moment

[gforsyth]

Ok, this is ready for a look, but I'll be OOO for the next while, so feel free to fix it up or nuke it as necessary.

[cpcloud]

This seems good!

We should probably start to do some consolidation of backend DDL code, even if we don't get to the point of having something like a set of internal DDL op classes.

[cpcloud]

Consolidate the implementation under a new private backend on SQLBackend (_build_insert_query)