Bug in Sqlite module

Expo SDK
1

I am getting very weird results when using the Expo’s SQLite module.

Please consider these two tables, i.e., users and messages:

CREATE TABLE IF NOT EXISTS users (
	id integer primary key, 
	email text
);

CREATE TABLE IF NOT EXISTS messages (
	id integer primary key, 
	sender_id integer, 
	thread_id integer, 
	body text, 
	foreign key (sender_id) references users (id), 
	foreign key (thread_id) references threads (id)
);

Let’s insert some rows into both users and messages:

insert or replace into users (id, email) values (1, 'first@mail.com');
insert or replace into users (id, email) values (2, 'second@mail.com');
insert or replace into users (id, email) values (3, 'third@mail.com');

INSERT INTO messages (id, sender_id, thread_id, body) values (1, 3, 1, 'hello world');
INSERT INTO messages (id, sender_id, thread_id, body) values (2, 1, 1, 'test');
INSERT INTO messages (id, sender_id, thread_id, body) values (3, 2, 1, 'check again');
INSERT INTO messages (id, sender_id, thread_id, body) values (4, 1, 1, 'payload');

Now to get all the messages, including its related user data I need to select from both the tables:

select * from messages inner join users on messages.sender_id=users.id where thread_id =1;

Here the instead of getting results like this (note the id and sender_id:

id          sender_id   thread_id   body         id          email         
----------  ----------  ----------  -----------  ----------  --------------
1           3           1           hello world  3           third@mail.com
2           1           1           test         1           first@mail.com
3           2           1           check again  2           second@mail.co
4           1           1           payload      1           first@mail.com

I am getting results like this:

id          sender_id   thread_id   body         id          email         
----------  ----------  ----------  -----------  ----------  --------------
3           3           1           hello world  3           third@mail.com
1           1           1           test         1           first@mail.com
2           2           1           check again  2           second@mail.co
1           1           1           payload      1           first@mail.com

As you can see from the above results, instead of the message id to have its own value, message’s id is replaced by the sender’s id.

I have made an expo snack demo link here.

What’s going on here?

2

Hey @kakarn! I’ll check this out soon. Thanks for the snack link too, that’s super helpful. What happens btw when you try to run this on a local sqlite instance or some other database for example?

3

I have also tried it by creating a local Sqlite3 instance in my mac. I am getting results like it should be:

id          sender_id   thread_id   body         id          email         
----------  ----------  ----------  -----------  ----------  --------------
1           3           1           hello world  3           third@mail.com
2           1           1           test         1           first@mail.com
3           2           1           check again  2           second@mail.co
4           1           1           payload      1           first@mail.com

Meaning the message’s id has its own value instead of the sender_id.

4

Woah, weird. Ok I’ll look into this soon. :thinking: What happens if you explicitly select messages.id and something else only out of the table?

5

When only selecting one table, I am getting the expected results:

select * from messages;

Returns back with messages own id, instead of the sender_id.

[
	{
		"id":1,"sender_id":3,"thread_id":1,"body":"hello world"
	},
	{
		"id":2,"sender_id":1,"thread_id":1,"body":"test"
	},
	{
		"id":3,"sender_id":2,"thread_id":1,"body":"check again"
	},
	{
		"id":4,"sender_id":1,"thread_id":1,"body":"payload"
	}
]

As you can see, I think it only happens when JOIN tables.

Snack link.

6

Now, this is weirder! When selecting users table and joining the messages table

select * from users, messages where thread_id = 1 and messages.sender_id = users.id;

Then I get the messages rows including the user’s data, and the message’s have their own id instead of the sender_id’s value.

[
	{
		"id":1,"email":"third@mail.com","sender_id":3,"thread_id":1,"body":"hello world"
	},
	{
		"id":2,"email":"first@mail.com","sender_id":1,"thread_id":1,"body":"test"
	},
	{
		"id":3,"email":"second@mail.com","sender_id":2,"thread_id":1,"body":"check again"
	},
	{
		"id":4,"email":"first@mail.com","sender_id":1,"thread_id":1,"body":"payload"
	}
]

Snack demo