System.Data.SQLite
View Ticket
Not logged in
Ticket UUID: 3f050701ce38dd5c28d7466fd1e9d1434d957e06
Title: invalid column names in SqLitedataReader.
Status: Closed Type: Incident
Severity: Important Priority: Blocker
Subsystem: Data_Reader Resolution: Not_A_Bug
Last Modified: 2015-02-19 17:09:36
Version Found In: 1.0.94.1
User Comments:
anonymous added on 2015-02-18 12:01:36:
invalid column names in SqLitedataReader.
using (var sqlCmd = connection.CreateCommand())
{
   sqlCmd.CommandText = "SELECT [TableName].[C1] FROM [TableName];";
     using (var reader = sqlCmd.ExecuteReader())
     {
        reader.Read();
        var name = reader.GetName(i);
     }
}
//name = "TableName";

mistachkin added on 2015-02-18 22:34:05:
I'm unable to reproduce the results you are seeing.  Are you able to share
the schema of the table involved?

mistachkin (claiming to be anonymous) added on 2015-02-19 17:00:44:
invalid column names in SqLitedataReader.
 class Program
    {
        static void Main(string[] args)
        {
            using (var connection = new SQLiteConnection("Data Source=:memory:"))
            {
                connection.Open();
                using (var cmd = connection.CreateCommand())
                {
                    cmd.CommandText = "PRAGMA foreign_keys = ON;";
                    cmd.ExecuteNonQuery();
                    cmd.CommandText = "CREATE TABLE [TABLE1] ([Id] INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL UNIQUE, [Test1] NVARCHAR(150) NOT NULL);";
                    cmd.ExecuteNonQuery();
                    cmd.CommandText = "CREATE TABLE [TABLE2] ([Id] INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL UNIQUE, [TABLE1Id] INTEGER NOT NULL, [Test2] NVARCHAR(150) NOT NULL, FOREIGN KEY (TABLE1Id) REFERENCES TABLE1(id) ON DELETE CASCADE);";
                    cmd.ExecuteNonQuery();
                    cmd.CommandText = "CREATE VIEW [TEST] AS SELECT TABLE1.Test1, TABLE2.Test2 FROM TABLE2 INNER JOIN TABLE1 ON TABLE2.Id == TABLE1.Id;";
                    cmd.ExecuteNonQuery();
                    cmd.CommandText = "INSERT INTO TABLE1 ([Test1]) VALUES ('adads');";
                    cmd.ExecuteNonQuery();
                    cmd.CommandText = "INSERT INTO TABLE2 ([TABLE1Id], [Test2]) VALUES (1, 'adads');";
                    cmd.ExecuteNonQuery();
                    cmd.CommandText = "SELECT [TEST].[Test1], [TEST].[Test2] FROM [TEST]";
                    using (var reader =  cmd.ExecuteReader())
                    {
                        reader.Read();
                        var columnName = reader.GetName(0);
                        Console.WriteLine("columnName = {0}", columnName);
                    }
                }
            }
            Console.ReadKey();
        }
    }

mistachkin added on 2015-02-19 17:07:19:
Simplified test script for the command line SQLite utility (sqlite3.exe):

.mode columns
.headers on
PRAGMA foreign_keys = ON;
CREATE TABLE [TABLE1] ([Id] INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL UNIQUE, [Test1] NVARCHAR(150) NOT NULL);
CREATE TABLE [TABLE2] ([Id] INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL UNIQUE, [TABLE1Id] INTEGER NOT NULL, [Test2] NVARCHAR(150) NOT NULL, FOREIGN KEY (TABLE1Id) REFERENCES TABLE1(id) ON DELETE CASCADE);
CREATE VIEW [TEST] AS SELECT TABLE1.Test1, TABLE2.Test2 FROM TABLE2 INNER JOIN TABLE1 ON TABLE2.Id == TABLE1.Id;
INSERT INTO TABLE1 ([Test1]) VALUES ('adads');
INSERT INTO TABLE2 ([TABLE1Id], [Test2]) VALUES (1, 'adads');
SELECT [TEST].[Test1], [TEST].[Test2] FROM [TEST];

mistachkin added on 2015-02-19 17:09:36:
This is not a bug in System.Data.SQLite nor SQLite.  The SQL standard requires
that an 'AS' clause be present to set the column names.  Without that, the SQL
engine is free to choose whatever column names it sees fit.  In this case, the
VIEW and JOIN is causing it to choose 'undesirable' names.  In order to get the
desired column names, please use 'AS' clauses in the SQL.