Re: [sqlite] SQLite 3.7.5 : same query written using NOT IN and EXCEPT does not give out same results

2011-02-23 Thread Benoit Mortgat 
On Tue, Feb 22, 2011 at 14:17, Richard Hipp  wrote:
>
> The query is really more like this:
>
> SELECT DISTINCT COALESCE(a.xxx, b.value) value
>  FROM tbl1 a
>  LEFT OUTER JOIN tbl2 b
>    ON a.zzz = b.ttt
>  WHERE value NOT IN (
>        SELECT DISTINCT ggg
>          FROM tbl3
>       );
>
> The "value" on the left-hand side of the NOT IN operator is ambiguous:  Does
> it refer to the first column of output or to the "value" column of the "b"
> table?  SQLite chooses the latter.

Thank you for your answer.
Benoit
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite 3.7.5 : same query written using NOT IN and EXCEPT does not give out same results

2011-02-22 Thread Richard Hipp 
On Tue, Feb 22, 2011 at 5:22 AM, Benoit Mortgat  wrote:

> Hello,
>
> I have come across a strange behaviour of SQLite 3.7.5.
>
> The following query:
>
> SELECT DISTINCT COALESCE(a.xxx, b.yyy) value
>  FROM tbl1 a
>  LEFT OUTER JOIN tbl2 b
>ON a.zzz = b.ttt
> EXCEPT
> SELECT DISTINCT ggg value
>  FROM tbl3;
>
> will not return any results (which seems to be correct).
>
> However, when I write it this way:
>
> SELECT DISTINCT COALESCE(a.xxx, b.yyy) value
>  FROM tbl1 a
>  LEFT OUTER JOIN tbl2 b
>ON a.zzz = b.ttt
>  WHERE value NOT IN (
>SELECT DISTINCT ggg
>  FROM tbl3
>   );
>

The query is really more like this:

SELECT DISTINCT COALESCE(a.xxx, b.value) value
 FROM tbl1 a
 LEFT OUTER JOIN tbl2 b
   ON a.zzz = b.ttt
 WHERE value NOT IN (
   SELECT DISTINCT ggg
 FROM tbl3
  );

The "value" on the left-hand side of the NOT IN operator is ambiguous:  Does
it refer to the first column of output or to the "value" column of the "b"
table?  SQLite chooses the latter.  You can fix it in a couple of ways:

SELECT DISTINCT COALESCE(a.xxx, b.value) valueX
 FROM tbl1 a
 LEFT OUTER JOIN tbl2 b
   ON a.zzz = b.ttt
 WHERE valueX NOT IN (
   SELECT DISTINCT ggg
 FROM tbl3
  );

Or

SELECT DISTINCT COALESCE(a.xxx, b.value) value
 FROM tbl1 a
 LEFT OUTER JOIN tbl2 b
   ON a.zzz = b.ttt
 WHERE COALESCE(a.xxx, b.value) NOT IN (
   SELECT DISTINCT ggg
 FROM tbl3
  );





>
> I get results (which are wrong).
>
> I could send a samble database with full query to a developer if
> needed in order to reproduce that.
> Is this known bug?
>
> Thank you,
> Benoit
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite 3.7.5 : same query written using NOT IN and EXCEPT does not give out same results

2011-02-22 Thread Richard Hipp 
On Tue, Feb 22, 2011 at 5:22 AM, Benoit Mortgat  wrote:

> I could send a samble database with full query to a developer if
> needed in order to reproduce that.
>


Please do send the sample database and the full queries.
-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users