Pavel Ivanov wrote:
> Here is an example when left outer join makes the difference. Example
> could seem very artificial but SQLite should count on any possible
> usage.
>
> sqlite> create table Employee (name int);
> sqlite> create table Uniform (employeename, inseam, constraint ue
> unique (employeename));
> sqlite> insert into employee values ("Joe");
> sqlite> insert into employee values ("Steve");
> sqlite> insert into Uniform values ("Joe", 77);
> sqlite> insert into employee values (1);
> sqlite> insert into Uniform values (1, 77);
> sqlite> insert into Uniform values ("1", 77);
> sqlite> select Name from Employee left outer join Uniform on
> Employee.name=Uniform.employeename;
> Joe
> Steve
> 1
> 1
> sqlite> select Name from Employee;
> Joe
> Steve
> 1
> sqlite>
I'd rather call this sqlite bug.
Either "=" should return false, or "UNIQUE" constraint must reject duplicate
rows.
> On Wed, May 30, 2012 at 1:15 PM, Charles Samuels <char...@cariden.com> wrote:
>> On Wednesday, May 30, 2012 10:14:22 f.h. Charles Samuels wrote:
>>> sqlite> select Name from Employee join Uniform on
>>> Employee.name=Uniform.employeename;
>>> Joe
>>> Dave
>>> sqlite> explain query plan select Name from Employee join Uniform on
>>> Employee.name=Uniform.employeename;
>>> 0|0|0|SCAN TABLE Employee (~1000000 rows)
>>> 0|1|1|SEARCH TABLE Uniform USING COVERING INDEX sqlite_autoindex_Uniform_1
>>> (employeename=?) (~1 rows)
>> I mean "left outer join" here!
>>
>> sqlite> select Name from Employee left outer join Uniform on
>> Employee.name=Uniform.employeename;
>> Joe
>> Steve
>> Eric
>> Dave
>> sqlite> explain query plan select Name from Employee left outer join Uniform
>> on Employee.name=Uniform.employeename;
>> 0|0|0|SCAN TABLE Employee (~1000000 rows)
>> 0|1|1|SEARCH TABLE Uniform USING COVERING INDEX sqlite_autoindex_Uniform_1
>> (employeename=?) (~1 rows)
>>
>> Charles
>> (can never proofread enough.)
>> _______________________________________________
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
