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 d...@sqlite.org 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

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

2011-02-22 Thread Benoit Mortgat 
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
   );

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

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 mort...@gmail.com 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

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 mort...@gmail.com 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