Ambiguous column name error - but not in SQLiteStudio

[gordonfroyle]

Consider the following code

CREATE TABLE testing (id INTEGER, value INTEGER);
INSERT INTO testing VALUES (1,1), (1,3), (2,3);
SELECT * FROM testing;

SELECT id, value 
FROM testing T1, testing T2
WHERE T1.value = T2.value AND T1.id != T2.id;

When I run this using sqlite3 on the terminal, there is an error "ambiguous column name: id".

This is what I expect because the SELECT id, value does not have fully qualified column names.

However, when I run the same code on SQLiteStudio, no error is produced, and it just seems to make a choice (presumably T1.id and T1.value).

Is there a reason for this?

Thanks

[pawelsalawa]

Yeah... It's because SQLiteStudio does a bunch of stuff with the query before it is actually executed. Only if this "bunch of stuff" produces invalid SQL, it reverts back to the original query that user entered and retries to execute that original query.

Your query, after "bunch of stuff" looks like this:

SELECT *
  FROM (
           SELECT CASE WHEN typeof(ResCol_2) IN ('real', 'integer', 'numeric', 'null') THEN ResCol_2 ELSE substr(ResCol_2, 1, 100) END AS ResCol_2,
                  CASE WHEN typeof(ResCol_3) IN ('real', 'integer', 'numeric', 'null') THEN ResCol_3 ELSE substr(ResCol_3, 1, 100) END AS ResCol_3,
                  ResCol_0,
                  ResCol_1,
                  typeof(ResCol_2) AS ResCol_4,
                  typeof(ResCol_3) AS ResCol_5
             FROM (
                      SELECT ResCol_2,
                             ResCol_3,
                             ResCol_0,
                             ResCol_1
                        FROM (
                                 SELECT T1.id AS ResCol_2,
                                        T1.value AS ResCol_3,
                                        T1.ROWID AS ResCol_0,
                                        T2.ROWID AS ResCol_1
                                   FROM testing T1,
                                        testing T2
                                  WHERE T1.value = T2.value AND 
                                        T1.id != T2.id
                             )
                  )
       )
 LIMIT 1000 OFFSET 0;

It's a bug, that SQLiteStudio treats id and value columns as the same, no matter if from T1 or from T2 and I will create an issue out of it.

[pawelsalawa]

Created #4228

[gordonfroyle]

Thanks... just what I wanted to know.

[pawelsalawa]

FYI: This has been solved in one of 3.4.x versions (I'm not sure which one, but I just tested it and it works as expected).