SQLite Error – Resolving ‘No Such Column’ When Column Exists

errorsselectsqlite

I have a table (trackedinfo) inside my database that has the following columns (columns obtained by running PRAGMA table_info(trackedinfo);)

The problem is that even though the column sendok exists, when running a query on the database with that field, it throws an error.

Example queries:

SELECT * FROM trackedinfo WHERE sendok IS NULL;
SELECT sendok FROM trackedinfo;

Error:

SQLITE_ERROR: SQL error or missing database (no such column: sendok)

But, if I run a query selecting all of the fields, it brings me the info regarding sendok:

Here is the CREATE command of the database:

CREATE TABLE trackedinfo
(
    id INTEGER PRIMARY KEY,
    date_time_start TEXT,
    date_time_end TEXT,
    tracked_name TEXT,
    tracked_origin TEXT,
    tracked_maker TEXT,
    tracked_version TEXT,
    tracked_type TEXT,
    sendok TEXT,
    tracked_id TEXT
);

EDIT

It also happens with the column tracked_id

EDIT 2

Info that I got by executing .schema trackedinfo

CREATE TABLE IF NOT EXISTS "trackedinfo" ("id" INTEGER PRIMARY KEY, "date_time_start" TEXT, "date_time_end" TEXT, "tracked_name" TEXT, "tracked_origin" TEXT, "tracked_maker" TEXT, "tracked_version" TEXT, "tracked_type" TEXT, "sendok " TEXT, "tracked_id " TEXT);

Best Answer

The problem was that I had an space at the end of the name of the columns, solved the problem by deleting such spaces.

Before:

CREATE TABLE IF NOT EXISTS "trackedinfo" ("id" INTEGER PRIMARY KEY, "date_time_start" TEXT, "date_time_end" TEXT, "tracked_name" TEXT, "tracked_origin" TEXT, "tracked_maker" TEXT, "tracked_version" TEXT, "tracked_type" TEXT, "sendok " TEXT, "tracked_id " TEXT);

After:

CREATE TABLE IF NOT EXISTS "trackedinfo" ("id" INTEGER PRIMARY KEY, "date_time_start" TEXT, "date_time_end" TEXT, "tracked_name" TEXT, "tracked_origin" TEXT, "tracked_maker" TEXT, "tracked_version" TEXT, "tracked_type" TEXT, "sendok" TEXT, "tracked_id" TEXT);

In the above examples white spaces whithin "sendok" and "tracked_id" columns has been removed.

Related Solutions

Mysql – Autoincrement primary key in CREATE TABLE … AS SELECT

CREATE TABLE `user_mv` (id INT AUTO_INCREMENT PRIMARY KEY) SELECT `user`.`firstname` as 
   `firstname`,
   `user`.`lastname` as `lastname`,
   `user`.`lang` as `lang`,
   `user`.`name` as `user_name`,
   `group`.`name` as `group_name`
from `user`
  inner join `user_groups` on (`user`.`user_id`=`user_groups`.`user_id`)
  left  join `group` on (`group`.`group_id`=`user_groups`.`group_id`)
where `user`.`lang`=`group`.`lang`;

As long as you don't have an 'id' column in your SELECT, this appears to do exactly what you want. The columns in the select will be appended to the right of the columns you declare.

http://dev.mysql.com/doc/refman/5.6/en/create-table-select.html (also in previous versions).

PostgreSQL Group By – Why Select All Fields When Grouping by Primary Key but Not Another Column

id is a primary key.
As far as I remember, this is actually a legal query according to ANSI/ISO SQL.
Grouping by primary key results in a single record in each group which is logically the same as not grouping at all / grouping by all columns, therefore we can select all other columns.

create table t (id int primary key,c1 int,c2 int)
insert into t (id,c1,c2) values (1,2,3),(4,5,6);
select * from t group by id;

+----+----+----+
| id | c1 | c2 |
+----+----+----+
| 1  | 2  | 3  |
+----+----+----+
| 4  | 5  | 6  |
+----+----+----+

Reference given by @a_horse_with_no_name

https://www.postgresql.org/docs/current/static/sql-select.html#SQL-GROUPBY

When GROUP BY is present, or any aggregate functions are present, it is not valid for the SELECT list expressions to refer to ungrouped columns except within aggregate functions or when the ungrouped column is functionally dependent on the grouped columns, since there would otherwise be more than one possible value to return for an ungrouped column. A functional dependency exists if the grouped columns (or a subset thereof) are the primary key of the table containing the ungrouped column.

While logically we would expect UNIQUE NOT NULL to follow the same behaviour, it applies only for PK (as described in the documentation)

create table t (id int unique not null,c1 int,c2 int);
insert into t (id,c1,c2) values (1,2,3),(4,5,6);
select * from t group by id;

[Code: 0, SQL State: 42803] ERROR: column "t.c1" must appear in the GROUP BY clause or be used in an aggregate function

Best Answer

Related Solutions

Mysql – Autoincrement primary key in CREATE TABLE … AS SELECT

PostgreSQL Group By – Why Select All Fields When Grouping by Primary Key but Not Another Column

Related Question