tdbc::sqlite3

select a.name, b.name from t1 a, t2 b where a.id = b.id;

the tdbc::sqlite driver will only return one 'name' key in a result dict. A result list will contain both values, but the columnvariable will contain the same column name twice.

kbk added on 2009-10-27 01:09:56:
This is being recorded as a 'known bug'. It's essentially unfixable until we convert the SQLite3 driver over to using C, because that's where it's tripping over the duplicate names.

kbk added on 2009-10-27 01:10:40:
Oh, yeah. It can be fixed by using AS clauses to disambiguate the column names.

I'm not sure that disambiguating works, or I don't know how to make it work.

I'm using tdbc::sqlite3 1.1.0 on Linux Mint.

I hope to attach a test program that seeks to set up tables of Premier League teams and fixtures, populate them with this weekend's fixtures. The attached log
shows that a query to display a fixture list works ok when executed directly through the sqlite3 utility but fails when executed through tdbc, displaying the home team's name twice.

You give as example the query:

    SELECT
        fix_date,
        home.tea_name,
        away.tea_name
    FROM
    fixtures
    JOIN
    teams AS home ON home.tea_id = fix_home
    JOIN
    teams AS away ON away.tea_id = fix_away

The way to work around it is to make sure that all the column names returned are distinct.

  SELECT fix_date, home.tea_name AS hometeam, away.tea_name AS awayteam
  FROM fixtures
  JOIN teams AS home ON home.tea_id = fix_home
  JOIN teams AS away ON away.tea_id = fix_away

Now you don't have a name collision and you won't have a problem with one 'tea_name' overwriting another.