Two joins change case size

My question is why double joins in SQLite change case size in result. Please look in fiddle: http://sqlfiddle.com/#!7/88671/3

As you can see when I query “select * from user” I have upper size name A123456, but when I do double join I have only lower size name a123456.

Can someone explain why?

Schema below:

CREATE TABLE user (id integer primary key, n_login varchar(64), name varchar(128), CONSTRAINT a UNIQUE (n_login, name) ON CONFLICT IGNORE);

CREATE TABLE role (id integer primary key, name text unique, description text);

CREATE TABLE user_to_role (user_id integer REFERENCES user(id), role_id integer REFERENCES role(id), PRIMARY KEY (user_id, role_id));

CREATE TABLE role_to_database (id integer, role_id integer REFERENCES role(id), database_id integer REFERENCES databases(id), PRIMARY KEY (role_id, database_id));

insert into user (n_login, name) values ("a123456", "A123456");
insert into user (n_login, name) values ("a123456", "a123456");
insert into user (n_login, name) values ("a000000", "A000000");
insert into user_to_role (user_id, role_id) values (2, 1);
insert into user_to_role (user_id, role_id) values (2, 2);
insert into user_to_role (user_id, role_id) values (3, 3);
insert into role_to_database (role_id, database_id) values (1, 3);
insert into role_to_database (role_id, database_id) values (2, 8);
insert into role_to_database (role_id, database_id) values (3, 1);

With this query I have A123456:

select * from user;

With this query I have also A123456:

select * from role_to_database inner join user_to_role ON role_to_database.role_id = user_to_role.role_id inner join user;

But with this query I only have a123456:

select * from role_to_database inner join user_to_role ON role_to_database.role_id = user_to_role.role_id inner join user ON user.id = user_to_role.user_id;

Answer

The query returns exactly the data inserted:

insert into user_to_role (user_id, role_id) values (2, 1);
insert into user_to_role (user_id, role_id) values (2, 2);
insert into user_to_role (user_id, role_id) values (3, 3);

as user_id=1 takes no role.

Did you mean

insert into user_to_role (user_id, role_id) values (1, 1); -- 1
insert into user_to_role (user_id, role_id) values (2, 2);
insert into user_to_role (user_id, role_id) values (3, 3);

? Then you’ll get 'A123456' too.

Advertisement

Answer