On Wed, May 30, 2012 at 2:17 PM, Yuriy Kaminskiy <yum...@gmail.com> wrote:
> 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.
>
Not a bug because 1!='1' for untyped fields such as Uniform.employeename
and so the values are unique.
But Employee.name has type "INT" and so when comparing Employee.name on the
left to Uniform.employeename on the right, the left field is used to
determine the comparison type, which means the string '1' is converted to
1, and so both 1 and '1' on the right both compare equal to the 1 on the
left.
--
D. Richard Hipp
d...@sqlite.org
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
