Does row(:hash) behave sensibly on SELECT from table JOINs with identical column names

[zaucker]

What does my %values = $sth.row(:hash); do if the SELECT is on a JOIN of two tables with (some) columns/attributes having the same name, e.g.

  SELECT t1.name, t2.name FROM t1 JOIN t2 ON t1.id=t2.id

gives

sqlite> SELECT t1.name, t2.name FROM t1 JOIN t2 ON t1.id=t2.id;
name|name
Test11|Test21
Test12|Test22

Perl5 DBI silent drops one of the columns because they result in identical hash keys (it is documented, but hard to miss) and so does DBIish.

[zaucker]

This shows the problem:

use DBIish;
my $dbh = DBIish.connect('SQLite', database => './test-hash.sqlite3');
my $sth;

$dbh.do('CREATE TABLE IF NOT EXISTS t1(id INTEGER, name TEXT)');
$dbh.do('DELETE FROM t1');
$sth = $dbh.prepare('INSERT INTO t1 VALUES ( ?, ?)');
$sth.execute(1,'Test11');
$sth.execute(2,'Test12');

$dbh.do('CREATE TABLE IF NOT EXISTS t2(id INTEGER, name TEXT)');
$dbh.do('DELETE FROM t2');
$sth = $dbh.prepare('INSERT INTO t2 VALUES ( ?, ?)');
$sth.execute(1,'Test21');
$sth.execute(2,'Test22');

$sth = $dbh.prepare('SELECT t1.name, t2.name FROM t1 JOIN t2 ON t1.id=t2.id');

$sth.execute;
my @row = $sth.row;
dd @row;

$sth.execute;
my %row = $sth.row(:hash);
dd %row;
$sth.finish;
$dbh.dispose;

gives

Array @row = ["Test11", "Test21"]
Hash %row = {:name("Test21")}

thus one of the columns (probably always the first) is "lost" (overwritten because of the identical hash key).
I am not sure what the right behaviour is, probably an exception should be thrown.

[zaucker]

For completeness:

my %rows;

$sth.execute;
@rows = $sth.allrows;
dd @rows;

$sth.execute;
@rows = $sth.allrows(:array-of-hash);
dd @rows;

$sth.execute;
%rows = $sth.allrows(:hash-of-array);
dd %rows;
$sth.finish;
$dbh.dispose;

gives

Array @rows = [["Test11", "Test21"], ["Test12", "Test22"]]
Array @rows = [{:name("Test21")}, {:name("Test22")}]
Hash %rows = {:name($["Test11", "Test21", "Test12", "Test22"])}

I think this is sensible.