Re: [sqlite] what could be the reason that natural join stops working ?

[Stef Mientki]

 On 15-09-2010 20:12, Nicolas Williams wrote:
> On Wed, Sep 15, 2010 at 08:05:26PM +0200, Stef Mientki wrote:
>>  On 15-09-2010 11:36, Benoit Mortgat wrote:
>>> Are you sure that after altering your tables adding columns, natural
>>> join still only joins on vlid?
>>>
>> no, very stupid of me !!
>> I added a column to each of the tables, with the same name, they
>> should have the same value, but due to another programming error, the
>> values were different.  again, thank all very much !!
>>
>> That brings another issue to my mind, which I still don't understand:
>> "why can we so easily manipulate complex pages of text, by adding /
>> deleting / copying / pasting etc, and is manipulating of a few tables
>> in a database so difficult?"
> You shouldn't select * and you shouldn't natural joins precisely to
> avoid these sorts of surprises.  Select * is handy for manual queries
> from a shell; ditto natural joins.
>
> Of course, you can always change your schema in such a way as to break
> existing statements.  But the point here is to write statements such
> that they will be stable in the face of a schema that evolves in a
> backwards compatible way.  When you have to radically change your schema
> you know you have to update your code; when you're merely adding columns
> that don't contribute to primary keys then you shouldn't have to go
> change any statements other than the ones where you specifically need
> the new columns.  SQL shortcuts like '*' and natural join are just not
> compatible with such a schema evolution model.
>
thanks Nico,
I think these are very valuable tips.

cheers,
Stef
> Nico

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

Re: [sqlite] what could be the reason that natural join stops working ?

[Nicolas Williams]

On Wed, Sep 15, 2010 at 08:05:26PM +0200, Stef Mientki wrote:
>  On 15-09-2010 11:36, Benoit Mortgat wrote:
> > Are you sure that after altering your tables adding columns, natural
> > join still only joins on vlid?
> >
> no, very stupid of me !!
> I added a column to each of the tables, with the same name, they
> should have the same value, but due to another programming error, the
> values were different.  again, thank all very much !!
> 
> That brings another issue to my mind, which I still don't understand:
> "why can we so easily manipulate complex pages of text, by adding /
> deleting / copying / pasting etc, and is manipulating of a few tables
> in a database so difficult?"

You shouldn't select * and you shouldn't natural joins precisely to
avoid these sorts of surprises.  Select * is handy for manual queries
from a shell; ditto natural joins.

Of course, you can always change your schema in such a way as to break
existing statements.  But the point here is to write statements such
that they will be stable in the face of a schema that evolves in a
backwards compatible way.  When you have to radically change your schema
you know you have to update your code; when you're merely adding columns
that don't contribute to primary keys then you shouldn't have to go
change any statements other than the ones where you specifically need
the new columns.  SQL shortcuts like '*' and natural join are just not
compatible with such a schema evolution model.

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

Re: [sqlite] what could be the reason that natural join stops working ?

[Stef Mientki]

 On 15-09-2010 11:36, Benoit Mortgat wrote:
> On Tue, Sep 14, 2010 at 23:41, Stef Mientki  wrote:
>
>> until a few moments ago, this worked perfectly
>>
>> select Header from vraag
>>  natural join vraaglist
>>  where Nr = 0 and vraaglist.Name = 'eortc_br23'
>>
>> but now it returns an empty string (while the string shouldn't be empty).
>>
>> I test that by doing an explicit join
>>
>> select Header from vraag
>>  join vraaglist on vraag.vlid = vraaglist.vlid
>>  where Nr = 0 and vraaglist.Name = 'eortc_br23'
>>
> Are you sure that after altering your tables adding columns, natural join
> still only joins on vlid?
>
no, very stupid of me !!
I added a column to each of the tables, with the same name,
they should have the same value, but due to another programming error, the 
values were different.
again, thank all very much !!

That brings another issue to my mind, which I still don't understand:
"why can we so easily manipulate complex pages of text, by adding / deleting / 
copying / pasting etc,
and is manipulating of a few tables in a database so difficult?"

cheers,
Stef
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] what could be the reason that natural join stops working ?

[Benoit Mortgat]

On Tue, Sep 14, 2010 at 23:41, Stef Mientki  wrote:

>
> until a few moments ago, this worked perfectly
>
> select Header from vraag
>  natural join vraaglist
>  where Nr = 0 and vraaglist.Name = 'eortc_br23'
>
> but now it returns an empty string (while the string shouldn't be empty).
>
> I test that by doing an explicit join
>
> select Header from vraag
>  join vraaglist on vraag.vlid = vraaglist.vlid
>  where Nr = 0 and vraaglist.Name = 'eortc_br23'
>

Are you sure that after altering your tables adding columns, natural join
still only joins on vlid?

-- 
Benoit Mortgat
20, avenue Marcel Paul
69200 Vénissieux, France
+33 6 17 15 41 58
+33 4 27 11 61 23
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] what could be the reason that natural join stops working ?

[Stef Mientki]

 On 15-09-2010 03:42, Pavel Ivanov wrote:
> Did you by any chance introduced some unique constraint or unique
> index on a set of columns one of which is primary key? AFAIK, there
> was a problem in SQLite until some recent versions in processing of
> redundant unique constraints in conjunction with natural joins.
thanks, that's a good idea,
I indeed, removed a unique constraint of 1 of the columns (and also renamed 
that column).
So I'll try to replicate the transformations and let you know.

cheers,
Stef
> But the best idea would be to post the full definition of those two
> tables along with their indexes and constraints.
>
> Pavel
>
> On Tue, Sep 14, 2010 at 6:09 PM, Stef Mientki  wrote:
>>  On 14-09-2010 23:50, Oliver Peters wrote:
>>> your version of sqlite?
>> I used several programs
>> SQLiteSpy,
>> SQLcc,
>> Python-programs,
>> so I don't know which versions of sqlite I used,
>> might that be the problem ?
>>> your OS?
>> windows XP
>>> your backend?
>> several, see above
>>> What exactly have you done before the Natural Join stopped working?
>> converted tables from string to unicode,
>> column rename + columns added + changed column constraints ( through copy 
>> table / drop table /
>> rename table )
>>
>> thanks,
>> Stef
>>> Oliver
>>>
>>>
>>> Am Dienstag, den 14.09.2010, 23:41 +0200 schrieb Stef Mientki:
 hello,

 after modifying some tables, natural join stopped working  ???

 I've 2 tables, each with a field named "vlid", which is the primary key in 
 one of the tables

 until a few moments ago, this worked perfectly

 select Header from vraag
   natural join vraaglist
   where Nr = 0 and vraaglist.Name = 'eortc_br23'

 but now it returns an empty string (while the string shouldn't be empty).

 I test that by doing an explicit join

 select Header from vraag
   join vraaglist on vraag.vlid = vraaglist.vlid
   where Nr = 0 and vraaglist.Name = 'eortc_br23'

 which gives me the correct string of the field Header.

 exchanging the 2 tables in the above statements, gives exactly the same 
 results.

 Is my database corrupt ?

 thanks,
 Stef Mientki
 ___
 sqlite-users mailing list
 sqlite-users@sqlite.org
 http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>> ___
>>> sqlite-users mailing list
>>> sqlite-users@sqlite.org
>>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

Re: [sqlite] what could be the reason that natural join stops working ?

[Pavel Ivanov]

Did you by any chance introduced some unique constraint or unique
index on a set of columns one of which is primary key? AFAIK, there
was a problem in SQLite until some recent versions in processing of
redundant unique constraints in conjunction with natural joins.

But the best idea would be to post the full definition of those two
tables along with their indexes and constraints.

Pavel

On Tue, Sep 14, 2010 at 6:09 PM, Stef Mientki  wrote:
>  On 14-09-2010 23:50, Oliver Peters wrote:
>> your version of sqlite?
> I used several programs
> SQLiteSpy,
> SQLcc,
> Python-programs,
> so I don't know which versions of sqlite I used,
> might that be the problem ?
>> your OS?
> windows XP
>> your backend?
> several, see above
>> What exactly have you done before the Natural Join stopped working?
> converted tables from string to unicode,
> column rename + columns added + changed column constraints ( through copy 
> table / drop table /
> rename table )
>
> thanks,
> Stef
>> Oliver
>>
>>
>> Am Dienstag, den 14.09.2010, 23:41 +0200 schrieb Stef Mientki:
>>> hello,
>>>
>>> after modifying some tables, natural join stopped working  ???
>>>
>>> I've 2 tables, each with a field named "vlid", which is the primary key in 
>>> one of the tables
>>>
>>> until a few moments ago, this worked perfectly
>>>
>>> select Header from vraag
>>>   natural join vraaglist
>>>   where Nr = 0 and vraaglist.Name = 'eortc_br23'
>>>
>>> but now it returns an empty string (while the string shouldn't be empty).
>>>
>>> I test that by doing an explicit join
>>>
>>> select Header from vraag
>>>   join vraaglist on vraag.vlid = vraaglist.vlid
>>>   where Nr = 0 and vraaglist.Name = 'eortc_br23'
>>>
>>> which gives me the correct string of the field Header.
>>>
>>> exchanging the 2 tables in the above statements, gives exactly the same 
>>> results.
>>>
>>> Is my database corrupt ?
>>>
>>> thanks,
>>> Stef Mientki
>>> ___
>>> sqlite-users mailing list
>>> sqlite-users@sqlite.org
>>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] what could be the reason that natural join stops working ?

[Stef Mientki]

 On 14-09-2010 23:50, Oliver Peters wrote:
> your version of sqlite?
I used several programs
SQLiteSpy,
SQLcc,
Python-programs,
so I don't know which versions of sqlite I used,
might that be the problem ?
> your OS?
windows XP
> your backend?
several, see above
> What exactly have you done before the Natural Join stopped working?
converted tables from string to unicode,
column rename + columns added + changed column constraints ( through copy table 
/ drop table /
rename table )

thanks,
Stef
> Oliver
>
>
> Am Dienstag, den 14.09.2010, 23:41 +0200 schrieb Stef Mientki:
>> hello,
>>
>> after modifying some tables, natural join stopped working  ???
>>
>> I've 2 tables, each with a field named "vlid", which is the primary key in 
>> one of the tables
>>
>> until a few moments ago, this worked perfectly
>>
>> select Header from vraag
>>   natural join vraaglist
>>   where Nr = 0 and vraaglist.Name = 'eortc_br23'
>>
>> but now it returns an empty string (while the string shouldn't be empty).
>>
>> I test that by doing an explicit join
>>
>> select Header from vraag
>>   join vraaglist on vraag.vlid = vraaglist.vlid
>>   where Nr = 0 and vraaglist.Name = 'eortc_br23'
>>
>> which gives me the correct string of the field Header.
>>
>> exchanging the 2 tables in the above statements, gives exactly the same 
>> results.
>>
>> Is my database corrupt ?
>>
>> thanks,
>> Stef Mientki
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

Re: [sqlite] what could be the reason that natural join stops working ?

[Oliver Peters]

your version of sqlite?
your OS?
your backend?

What exactly have you done before the Natural Join stopped working?

Oliver


Am Dienstag, den 14.09.2010, 23:41 +0200 schrieb Stef Mientki:
> hello,
> 
> after modifying some tables, natural join stopped working  ???
> 
> I've 2 tables, each with a field named "vlid", which is the primary key in 
> one of the tables
> 
> until a few moments ago, this worked perfectly
> 
> select Header from vraag
>   natural join vraaglist
>   where Nr = 0 and vraaglist.Name = 'eortc_br23'
> 
> but now it returns an empty string (while the string shouldn't be empty).
> 
> I test that by doing an explicit join
> 
> select Header from vraag
>   join vraaglist on vraag.vlid = vraaglist.vlid
>   where Nr = 0 and vraaglist.Name = 'eortc_br23'
> 
> which gives me the correct string of the field Header.
> 
> exchanging the 2 tables in the above statements, gives exactly the same 
> results.
> 
> Is my database corrupt ?
> 
> thanks,
> Stef Mientki
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


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

[sqlite] what could be the reason that natural join stops working ?

[Stef Mientki]

 hello,

after modifying some tables, natural join stopped working  ???

I've 2 tables, each with a field named "vlid", which is the primary key in one 
of the tables

until a few moments ago, this worked perfectly

select Header from vraag
  natural join vraaglist
  where Nr = 0 and vraaglist.Name = 'eortc_br23'

but now it returns an empty string (while the string shouldn't be empty).

I test that by doing an explicit join

select Header from vraag
  join vraaglist on vraag.vlid = vraaglist.vlid
  where Nr = 0 and vraaglist.Name = 'eortc_br23'

which gives me the correct string of the field Header.

exchanging the 2 tables in the above statements, gives exactly the same results.

Is my database corrupt ?

thanks,
Stef Mientki
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users