Richard Hipp wrote:
> 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.
Thanks you for explanation (dynamic/loose type system can cause rather strange
behavior in corner cases :-|; I'd wished sqlite had PRAGMA strict_typing to
reject such ambiguous queries/schemas :-| then again, that would conflict with
"lite" part).
> 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.
So, if someone will implement this optimization :-), it should be applied only
when column types are same.
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
