CODEWITHSUNDEEP
androidsqlite
Ne0
Ne0

Reputation: 2788

sqlite TEXT column name same as value bug

I have found what appears to be a strange bug with sqlite.

I have a table

CREATE TABLE controller(id INTEGER PRIMARY KEY ASC, controller_number TEXT, password TEXT);

With data

INSERT INTO controller (controller_number, password) VALUES ("1234", 1234);

No problems there, but check this out.

sqlite> SELECT * FROM controller; 
1|1234|1234
sqlite> UPDATE controller SET controller_number="12345", password="password" WHERE id=1;
sqlite>  SELECT * FROM controller;
1|12345|1234

can anyone explain why this is happening, or this a bug that should be raised?

Upvotes: 0

Views: 869

Answers (3)

te7
te7

Reputation: 609

For any field value that is the same as any field name in a SELECT or UPDATE statement, you must use single quotes (meaning a literal value, not double quotes, which means a field name) for that field value. I fixed this in my app.

Upvotes: 0

Sid
Sid

Reputation: 7631

It may be the double quotes:

'keyword' A keyword in single quotes is a string literal. "keyword" A keyword in double-quotes is an identifier.

from http://www.sqlite.org/lang_keywords.html

Upvotes: 2

Ne0
Ne0

Reputation: 2788

>CREATE TABLE controller(id INTEGER PRIMARY KEY ASC, controller_number TEXT, password TEXT);
>INSERT INTO controller (controller_number, password) VALUES ("1234", 1234);
>SELECT * FROM controller; 
1|1234|1234
>UPDATE controller SET controller_number="12345", password="password" WHERE id=1;
>SELECT * FROM controller;
1|12345|1234

Above causes an issue, but if do this

>UPDATE controller SET controller_number='123456', password='password' WHERE id=1;

I get this

>SELECT * FROM controller;
1|123456|password

It is clear what is going on, the first UPDATE was setting password=password rather then password="password" and second query executes the correct way.

Can anyone elaborate on why this happens, or should it happen in the first place?

Upvotes: 0

Related Questions