Re: [sqlite] Possible bug with union and join.

[Hick Gunter]

Incorrect. All data is stored as NULL, integer, float, text or blob. The 
"declared type" is taken as a hint from the designer in respect to the kind of 
values he intends to store there. The result of the hint is called an 
"affinity", i.e. the kind of data the field "likes to" store.  If the type of 
the value presented differs from the affinity, it may be converted, if 
losslessly and reversibly possible (e.g. '1' <=> 1); this is called "applying 
affinity". Or, if not possible ('hugo' cannot be converted to a number), simply 
stored without conversion. This is the sense in which SQLite is "typeless". 
Even if the field has a declared type, any kind of value may be stored there 
without error.

"Affinity" is a volatile property. It may be lost (by using a value in a 
general expression) or gained (by using a cast or in the context of a 
comparison). This is mainly for the benefit of users who for some reason or 
other need to compare (text, e.g. a text constant) '1' with (integer, e.g. a 
value from a field) 1 and have it come out equal. Note that constants have a 
type but no affinity, so SELECT '1' = 1; returns 0.

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Don V Nielsen
Gesendet: Dienstag, 27. Dezember 2016 16:18
An: SQLite mailing list <sqlite-users@mailinglists.sqlite.org>
Betreff: Re: [sqlite] Possible bug with union and join.

Theory related question. I'm being argumentative, I know. But this issue is in 
the same category as one discussed weeks ago.

SQLite is, in a sense, typeless. All data is stored as text (ignore blob). 
Correct? It is when one casts a column to something other than text that 
triggers SQLite to treat the text differently.

Disregarding auto-incremented key values, why have an integer key.
Even if the key value will only be numeric digits like 1, 255, 1024, etc..., 
are they "truly" integers? If the value is not used in a mathematical formula, 
why think of it as an integer? It is still just text...a string of ascii 
digits... but still text. Is there something behind the scenes of how text data 
comprised of numeric digits is stored?

Like the previous issue I suggested keeping the keys between tables the same 
data type. The issue resolves itself. The same would be true, here. One table 
has text which could be '1,10'. But in the other table, it is integer 1 & 10. 
It could be text '1' & '10'. No type conversion problems.

I don't know. I would like to hear what others have to say.
dvn

On Sun, Dec 25, 2016 at 2:43 PM, Adrian Stachlewski 
<adrianstachlew...@gmail.com> wrote:
> Fortunately names of columns are much more transparent and documented
> in our internal specification. 'Id' was created only for example, but
> thanks for advice :)
>
> Adrian
>
> 2016-12-25 13:44 GMT+01:00 Simon Slavin <slav...@bigfraud.org>:
>
>>
>> On 23 Dec 2016, at 4:55pm, Adrian Stachlewski
>> <adrianstachlew...@gmail.com>
>> wrote:
>>
>> > Id field in one table is defined as TEXT, because there are stored
>> > identifiers which can be numeric or text mostly like in the example
>> > ("4", "4,5", "10-1") (to be precise this map is created on the fly
>> > by concatenating some ids and names from another tables).  In
>> > second table there are stored identifiers which are integer only.
>> > This ids means something entirely different, but there is one case,
>> > when table with date keeps ids from both tables. Unfortunately I
>> > cannot change input data - it is taken from some APIs using csv files.
>>
>> Okay.  You’re wedded to a data format created by someone else.  That
>> explains the problem.
>>
>> If you have the opportunity to rename your columns when you import
>> from the CSV files, I might recommend that you do not call the TEXT field 
>> 'id'.
>> The convention for 'id' is for an INTEGER PRIMARY KEY and it might
>> confuse other people who see your database.
>>
>> Good luck with problem you posted about.
>>
>> Simon.
>> ___
>> sqlite-users mailing list
>> sqlite-users@mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___

Re: [sqlite] Possible bug with union and join.

[Don V Nielsen]

Thanks for the correction/information! Merry Christmas and Happy New
Year to you.

On Tue, Dec 27, 2016 at 9:48 AM, Richard Hipp  wrote:
> On 12/27/16, Don V Nielsen  wrote:
>> Theory related question. I'm being argumentative, I know. But this
>> issue is in the same category as one discussed weeks ago.
>>
>> SQLite is, in a sense, typeless. All data is stored as text (ignore
>> blob). Correct? It is when one casts a column to something other than
>> text that triggers SQLite to treat the text differently.
>
> Incorrect.  SQLite stores content in memory and on disk in multiple
> formats, including 2's-complement integers, IEEE 754 floating point
> numbers, text formatted as UTF8, UTF16be, or UTF16le, and binary
> blobs.  See, for example,
> https://www.sqlite.org/fileformat2.html#serialtype
>
>>
>> Disregarding auto-incremented key values, why have an integer key.
>
> Special optimizations apply to tables with an INTEGER PRIMARY KEY that
> make such tables particularly fast.
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Possible bug with union and join.

[Richard Hipp]

On 12/27/16, Don V Nielsen  wrote:
> Theory related question. I'm being argumentative, I know. But this
> issue is in the same category as one discussed weeks ago.
>
> SQLite is, in a sense, typeless. All data is stored as text (ignore
> blob). Correct? It is when one casts a column to something other than
> text that triggers SQLite to treat the text differently.

Incorrect.  SQLite stores content in memory and on disk in multiple
formats, including 2's-complement integers, IEEE 754 floating point
numbers, text formatted as UTF8, UTF16be, or UTF16le, and binary
blobs.  See, for example,
https://www.sqlite.org/fileformat2.html#serialtype

>
> Disregarding auto-incremented key values, why have an integer key.

Special optimizations apply to tables with an INTEGER PRIMARY KEY that
make such tables particularly fast.
-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Possible bug with union and join.

[Don V Nielsen]

Theory related question. I'm being argumentative, I know. But this
issue is in the same category as one discussed weeks ago.

SQLite is, in a sense, typeless. All data is stored as text (ignore
blob). Correct? It is when one casts a column to something other than
text that triggers SQLite to treat the text differently.

Disregarding auto-incremented key values, why have an integer key.
Even if the key value will only be numeric digits like 1, 255, 1024,
etc..., are they "truly" integers? If the value is not used in a
mathematical formula, why think of it as an integer? It is still just
text...a string of ascii digits... but still text. Is there something
behind the scenes of how text data comprised of numeric digits is
stored?

Like the previous issue I suggested keeping the keys between tables
the same data type. The issue resolves itself. The same would be true,
here. One table has text which could be '1,10'. But in the other
table, it is integer 1 & 10. It could be text '1' & '10'. No type
conversion problems.

I don't know. I would like to hear what others have to say.
dvn

On Sun, Dec 25, 2016 at 2:43 PM, Adrian Stachlewski
 wrote:
> Fortunately names of columns are much more transparent and documented in
> our internal specification. 'Id' was created only for example, but thanks
> for advice :)
>
> Adrian
>
> 2016-12-25 13:44 GMT+01:00 Simon Slavin :
>
>>
>> On 23 Dec 2016, at 4:55pm, Adrian Stachlewski 
>> wrote:
>>
>> > Id field in one table is defined as TEXT, because there are stored
>> > identifiers which can be numeric or text mostly like in the example ("4",
>> > "4,5", "10-1") (to be precise this map is created on the fly by
>> > concatenating some ids and names from another tables).  In second table
>> > there are stored identifiers which are integer only. This ids means
>> > something entirely different, but there is one case, when table with date
>> > keeps ids from both tables. Unfortunately I cannot change input data - it
>> > is taken from some APIs using csv files.
>>
>> Okay.  You’re wedded to a data format created by someone else.  That
>> explains the problem.
>>
>> If you have the opportunity to rename your columns when you import from
>> the CSV files, I might recommend that you do not call the TEXT field 'id'.
>> The convention for 'id' is for an INTEGER PRIMARY KEY and it might confuse
>> other people who see your database.
>>
>> Good luck with problem you posted about.
>>
>> Simon.
>> ___
>> sqlite-users mailing list
>> sqlite-users@mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Possible bug with union and join.

[Adrian Stachlewski]

Fortunately names of columns are much more transparent and documented in
our internal specification. 'Id' was created only for example, but thanks
for advice :)

Adrian

2016-12-25 13:44 GMT+01:00 Simon Slavin :

>
> On 23 Dec 2016, at 4:55pm, Adrian Stachlewski 
> wrote:
>
> > Id field in one table is defined as TEXT, because there are stored
> > identifiers which can be numeric or text mostly like in the example ("4",
> > "4,5", "10-1") (to be precise this map is created on the fly by
> > concatenating some ids and names from another tables).  In second table
> > there are stored identifiers which are integer only. This ids means
> > something entirely different, but there is one case, when table with date
> > keeps ids from both tables. Unfortunately I cannot change input data - it
> > is taken from some APIs using csv files.
>
> Okay.  You’re wedded to a data format created by someone else.  That
> explains the problem.
>
> If you have the opportunity to rename your columns when you import from
> the CSV files, I might recommend that you do not call the TEXT field 'id'.
> The convention for 'id' is for an INTEGER PRIMARY KEY and it might confuse
> other people who see your database.
>
> Good luck with problem you posted about.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Possible bug with union and join.

[Simon Slavin]

On 23 Dec 2016, at 4:55pm, Adrian Stachlewski  
wrote:

> Id field in one table is defined as TEXT, because there are stored
> identifiers which can be numeric or text mostly like in the example ("4",
> "4,5", "10-1") (to be precise this map is created on the fly by
> concatenating some ids and names from another tables).  In second table
> there are stored identifiers which are integer only. This ids means
> something entirely different, but there is one case, when table with date
> keeps ids from both tables. Unfortunately I cannot change input data - it
> is taken from some APIs using csv files.

Okay.  You’re wedded to a data format created by someone else.  That explains 
the problem.

If you have the opportunity to rename your columns when you import from the CSV 
files, I might recommend that you do not call the TEXT field 'id'.  The 
convention for 'id' is for an INTEGER PRIMARY KEY and it might confuse other 
people who see your database.

Good luck with problem you posted about.

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Possible bug with union and join.

[Adrian Stachlewski]

Simon,
Id field in one table is defined as TEXT, because there are stored
identifiers which can be numeric or text mostly like in the example ("4",
"4,5", "10-1") (to be precise this map is created on the fly by
concatenating some ids and names from another tables).  In second table
there are stored identifiers which are integer only. This ids means
something entirely different, but there is one case, when table with date
keeps ids from both tables. Unfortunately I cannot change input data - it
is taken from some APIs using csv files. Real schema of this database is
mostly determined by input data and hole database is treated like some kind
of cache.

As you wrote, there are many problems with data, that we're storing. Schema
of database is not stable, new features often need new data from other API
and it's hard to predict new types of data. As I wrote in my previous
message, I was quite sure, that comparing text columns and integer columns
should work. Hopefully in other cases fields which are compared are same
types.

Adrian

2016-12-23 13:00 GMT+01:00 Simon Slavin :

>
> On 22 Dec 2016, at 5:55pm, Adrian Stachlewski 
> wrote:
>
> > In this case I think that the best way to do this is cast integer column
> to
> > text.
> > CREATE VIEW id_map(id, name) as
> >  SELECT CAST(id AS TEXT), name
> >  FROM map_integer
> > UNION ALL
> >  SELECT id, name
> >  FROM map_text;
>
> In an earlier post
>
> > CREATE TABLE map_integer (id INTEGER PRIMARY KEY, name TEXT);
> > CREATE TABLE data (id TEXT PRIMARY KEY, flag BOOLEAN);
>
> Your problem is not the form of your enquiry, or the precise way that
> UNION works, it’s the data you’re storing.
>
> Sorry if you’ve already explained this, but can you tell me why you have
> an id field which holds numbers defined as TEXT in one table, but an id
> field which holds numbers defined as INTEGER in another table ?  I know
> that the above schema is an example you made up for discussing the problem,
> and not your real schema, but it still points to an underlying problem with
> the data you’re storing.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Possible bug with union and join.

[Simon Slavin]

On 22 Dec 2016, at 5:55pm, Adrian Stachlewski  
wrote:

> In this case I think that the best way to do this is cast integer column to
> text.
> CREATE VIEW id_map(id, name) as
>  SELECT CAST(id AS TEXT), name
>  FROM map_integer
> UNION ALL
>  SELECT id, name
>  FROM map_text;

In an earlier post

> CREATE TABLE map_integer (id INTEGER PRIMARY KEY, name TEXT);
> CREATE TABLE data (id TEXT PRIMARY KEY, flag BOOLEAN);

Your problem is not the form of your enquiry, or the precise way that UNION 
works, it’s the data you’re storing.

Sorry if you’ve already explained this, but can you tell me why you have an id 
field which holds numbers defined as TEXT in one table, but an id field which 
holds numbers defined as INTEGER in another table ?  I know that the above 
schema is an example you made up for discussing the problem, and not your real 
schema, but it still points to an underlying problem with the data you’re 
storing.

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Possible bug with union and join.

[Adrian Stachlewski]

Richard,

Thank you for your answer. Datatypes in sqlite was always weird for me,
mostly the fact that for example TEXT can be inserted in INTEGER column.

In this case I think that the best way to do this is cast integer column to
text.
CREATE VIEW id_map(id, name) as
  SELECT CAST(id AS TEXT), name
  FROM map_integer
 UNION ALL
  SELECT id, name
  FROM map_text;

Is there any other recipe to do this?
Is there any documentation when '4'==4 (just to satisfy my curiosity)? I've
read https://www.sqlite.org/datatype3.html, but from this doc I was quite
sure that comparing text and integer columns is safe.

--
Adrian Stachlewski

2016-12-22 16:06 GMT+01:00 Richard Hipp :

> Adrian:
>
> String are not equal to numbers.  You should not expect that '4'==4.
> Yes, I know that SQLite will sometimes do this.  The complex type
> coercion rules were added so that SQL scripts that worked on
> PostgreSQL would also work on SQLite.  But really, you should avoid
> depending on automatic type coercion.  If you are having to think
> about the type coercion rules, then you are doing it wrong.
>
> Yes - SQLite should give consistent and predictable answers.  We will
> fix that.  Eventually.  Once I figure out what it ought to be doing.
> But you should not wait on that fix.  You should go ahead redesign
> your application so that it does not depend on the nuances of type
> coercions and so that it does not compare strings against integers and
> expect them to be equal.
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Possible bug with union and join.

[Richard Hipp]

On 12/22/16, David Raymond  wrote:
> Problem appears to be coming from an automatic index.


Thanks for the insight, David.  Automatic indexes do appear to be a
factor, but not the only factor.   The script below shows different
answers depending on whether the VIEW is created with or without
explicit column names:

- begin script ---
CREATE TABLE map_integer (id INT, name);
INSERT INTO map_integer VALUES(1,'a');
CREATE TABLE map_text (id TEXT, name);
INSERT INTO map_text VALUES('4','e');
CREATE TABLE data (id TEXT, name);
INSERT INTO data VALUES(1,'abc');
INSERT INTO data VALUES('4','xyz');

CREATE VIEW id_map1 as
SELECT * FROM map_integer
UNION ALL
SELECT * FROM map_text;

CREATE VIEW id_map2(id,name) as
SELECT * FROM map_integer
UNION ALL
SELECT * FROM map_text;

PRAGMA automatic_index=off;
.print -- id_map1:
SELECT * FROM data LEFT JOIN id_map1 USING(id);
.print -- id_map2:
SELECT * FROM data LEFT JOIN id_map2 USING(id);
 end script --

Adrian:

String are not equal to numbers.  You should not expect that '4'==4.
Yes, I know that SQLite will sometimes do this.  The complex type
coercion rules were added so that SQL scripts that worked on
PostgreSQL would also work on SQLite.  But really, you should avoid
depending on automatic type coercion.  If you are having to think
about the type coercion rules, then you are doing it wrong.

Yes - SQLite should give consistent and predictable answers.  We will
fix that.  Eventually.  Once I figure out what it ought to be doing.
But you should not wait on that fix.  You should go ahead redesign
your application so that it does not depend on the nuances of type
coercions and so that it does not compare strings against integers and
expect them to be equal.
-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Possible bug with union and join.

[David Raymond]

Problem appears to be coming from an automatic index.

sqlite> select * from data left join id_map using (id);
--EQP-- 2,0,0,SCAN TABLE map_integer
--EQP-- 3,0,0,SCAN TABLE map_text
--EQP-- 1,0,0,COMPOUND SUBQUERIES 2 AND 3 (UNION ALL)
--EQP-- 0,0,0,SCAN TABLE data
--EQP-- 0,1,1,SEARCH SUBQUERY 1 USING AUTOMATIC COVERING INDEX (id=?)
id|flag|name
1|0|a
2|1|b
3|1|c
4|0|NULL
3,12|1|d

sqlite> pragma automatic_index = off;

sqlite> select * from data left join id_map using (id);
--EQP-- 2,0,0,SCAN TABLE map_integer
--EQP-- 3,0,0,SCAN TABLE map_text
--EQP-- 1,0,0,COMPOUND SUBQUERIES 2 AND 3 (UNION ALL)
--EQP-- 0,0,0,SCAN TABLE data
--EQP-- 0,1,1,SCAN SUBQUERY 1
id|flag|name
1|0|a
2|1|b
3|1|c
4|0|e
3,12|1|d


-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Adrian Stachlewski
Sent: Wednesday, December 21, 2016 8:14 AM
To: sqlite-users@mailinglists.sqlite.org
Subject: [sqlite] Possible bug with union and join.

Hi everyone.

When I was working with sqlite3 I've found weird behavior of JOIN clause
when I was trying to merge table with union of tables. Let me explain this
using database with reproduced problem.

Database dump:
CREATE TABLE map_integer (id INTEGER PRIMARY KEY, name TEXT);
INSERT INTO "map_integer" VALUES(1,'a');
INSERT INTO "map_integer" VALUES(2,'b');
INSERT INTO "map_integer" VALUES(3,'c');
CREATE TABLE map_text (id TEXT PRIMARY KEY, name TEXT);
INSERT INTO "map_text" VALUES('4','e');
INSERT INTO "map_text" VALUES('3,12','d');
CREATE TABLE data (id TEXT PRIMARY KEY, flag BOOLEAN);
INSERT INTO "data" VALUES('1',0);
INSERT INTO "data" VALUES('2',1);
INSERT INTO "data" VALUES('3',1);
INSERT INTO "data" VALUES('4',0);
INSERT INTO "data" VALUES('3,12',1);

View for map:
CREATE VIEW id_map as
SELECT *
  FROM map_integer
UNION ALL
SELECT *
  FROM map_text;

Select statements:
SELECT * FROM data LEFT JOIN id_map USING(id);
SELECT * FROM data LEFT JOIN id_map ON data.id = id_map.id;

Both of select statements give wrong output:
1|0|a
2|1|b
3|1|c
4|0|
3,12|1|d

Expected result:
1|0|a
2|1|b
3|1|c
4|0|e
3,12|1|d

Another tests I've made:
1. view to CTE - same problem
2. left join to join - same problem, there's no row with id=4
3. change the order of select statements in view - not working, output:
1|0|
2|1|
3|1|
4|0|e
3,12|1|d

My explanation of the problem and workaround:
There is problem when UNION is made on different types in one column. When
I've cast id to text in view or when joining (... ON data.id = CAST(
id_map.id AS TEXT) everything it's working fine. Unfortunately this
workaround makes no sense for me, as long as
SELECT CAST(1 AS TEXT) = CAST(1 AS INTEGER);
gives True.

SQLite versions:
- 3.13.0
- 3.15.2
- pre-release snapshot

OS:
Ubuntu 16.04.1 LTS with 4.4.0-57-generic

I hope that I've described all necessary informations. Of course feel free
to ask me some questions if there's some ambiguous part.

Best regards,
Adrian Stachlewski
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Possible bug with union and join.

[Adrian Stachlewski]

Hi everyone.

When I was working with sqlite3 I've found weird behavior of JOIN clause
when I was trying to merge table with union of tables. Let me explain this
using database with reproduced problem.

Database dump:
CREATE TABLE map_integer (id INTEGER PRIMARY KEY, name TEXT);
INSERT INTO "map_integer" VALUES(1,'a');
INSERT INTO "map_integer" VALUES(2,'b');
INSERT INTO "map_integer" VALUES(3,'c');
CREATE TABLE map_text (id TEXT PRIMARY KEY, name TEXT);
INSERT INTO "map_text" VALUES('4','e');
INSERT INTO "map_text" VALUES('3,12','d');
CREATE TABLE data (id TEXT PRIMARY KEY, flag BOOLEAN);
INSERT INTO "data" VALUES('1',0);
INSERT INTO "data" VALUES('2',1);
INSERT INTO "data" VALUES('3',1);
INSERT INTO "data" VALUES('4',0);
INSERT INTO "data" VALUES('3,12',1);

View for map:
CREATE VIEW id_map as
SELECT *
  FROM map_integer
UNION ALL
SELECT *
  FROM map_text;

Select statements:
SELECT * FROM data LEFT JOIN id_map USING(id);
SELECT * FROM data LEFT JOIN id_map ON data.id = id_map.id;

Both of select statements give wrong output:
1|0|a
2|1|b
3|1|c
4|0|
3,12|1|d

Expected result:
1|0|a
2|1|b
3|1|c
4|0|e
3,12|1|d

Another tests I've made:
1. view to CTE - same problem
2. left join to join - same problem, there's no row with id=4
3. change the order of select statements in view - not working, output:
1|0|
2|1|
3|1|
4|0|e
3,12|1|d

My explanation of the problem and workaround:
There is problem when UNION is made on different types in one column. When
I've cast id to text in view or when joining (... ON data.id = CAST(
id_map.id AS TEXT) everything it's working fine. Unfortunately this
workaround makes no sense for me, as long as
SELECT CAST(1 AS TEXT) = CAST(1 AS INTEGER);
gives True.

SQLite versions:
- 3.13.0
- 3.15.2
- pre-release snapshot

OS:
Ubuntu 16.04.1 LTS with 4.4.0-57-generic

I hope that I've described all necessary informations. Of course feel free
to ask me some questions if there's some ambiguous part.

Best regards,
Adrian Stachlewski
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Possible bug with union and join.

[Adrian Stachlewski]

Hi everyone.

When I was working with sqlite3 I've found weird behavior of JOIN clause
when I was trying to merge table with union of tables. Let me explain this
using database with reproduced problem.

Database dump:
CREATE TABLE map_integer (id INTEGER PRIMARY KEY, name TEXT);
INSERT INTO "map_integer" VALUES(1,'a');
INSERT INTO "map_integer" VALUES(2,'b');
INSERT INTO "map_integer" VALUES(3,'c');
CREATE TABLE map_text (id TEXT PRIMARY KEY, name TEXT);
INSERT INTO "map_text" VALUES('4','e');
INSERT INTO "map_text" VALUES('3,12','d');
CREATE TABLE data (id TEXT PRIMARY KEY, flag BOOLEAN);
INSERT INTO "data" VALUES('1',0);
INSERT INTO "data" VALUES('2',1);
INSERT INTO "data" VALUES('3',1);
INSERT INTO "data" VALUES('4',0);
INSERT INTO "data" VALUES('3,12',1);

View for map:
CREATE VIEW id_map as
SELECT *
  FROM map_integer
UNION ALL
SELECT *
  FROM map_text;

Select statements:
SELECT * FROM data LEFT JOIN id_map USING(id);
SELECT * FROM data LEFT JOIN id_map ON data.id = id_map.id;

Both of select statements give wrong output:
1|0|a
2|1|b
3|1|c
4|0|
3,12|1|d

Expected result:
1|0|a
2|1|b
3|1|c
4|0|e
3,12|1|d

Another tests I've made:
1. view to CTE - same problem
2. left join to join - same problem, there's no row with id=4
3. change the order of select statements in view - not working, output:
1|0|
2|1|
3|1|
4|0|e
3,12|1|d

My explanation of the problem and workaround:
There is problem when UNION is made on different types in one column. When
I've cast id to text in view or when joining (... ON data.id = CAST(
id_map.id AS TEXT) everything it's working fine. Unfortunately this
workaround makes no sense for me, as long as
SELECT CAST(1 AS TEXT) = CAST(1 AS INTEGER);
gives True.

SQLite versions:
- 3.13.0
- 3.15.2
- pre-release snapshot

OS:
Ubuntu 16.04.1 LTS with 4.4.0-57-generic

I hope that I've described all necessary informations. Of course feel free
to ask me some questions if there's some ambiguous part.

Best regards,
Adrian Stachlewski
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users