Re: [sqlite] Re versing Text not Numbers

[Igor Tandetnik]

Macgyver7  wrote:
> I tried the case function that was recommended, but that seems to recognise
> everything in the column as text

This means that everything in the column is in fact text. You do realize that 
'123' is not the same thing as 123, right?

> except when it encounters a NULL.  I
> changed the column type to varchar, but that made no difference.

What difference did you expect?

> select (case when typeof (translit) = 'text' then translit else "@" end)
> from otpfinal;
> 
> This was a test to see if it would recognise numbers and replace them with
> an @.  It only replaced NULL fields.

If by "number" you mean "a string that consists only of digits", you could try 
a test like

ltrim(translit, '0123456789') = ''

-- 
Igor Tandetnik

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

Re: [sqlite] Re versing Text not Numbers

[Macgyver7]

Igor Tandetnik wrote:
> 
> 
> That function can also check the type of its parameter and return it
> unchanged if it's not a string. Or, you could do that in SQL:
> 
> select (case when typeof(field) = 'text' then reverse(field) else field
> end)
> from myTable;
> 
> 


I tried the case function that was recommended, but that seems to recognise
everything in the column as text except when it encounters a NULL.  I
changed the column type to varchar, but that made no difference.  What am I
missing here?  

select (case when typeof (translit) = 'text' then translit else "@" end) 
from otpfinal;

This was a test to see if it would recognise numbers and replace them with
an @.  It only replaced NULL fields.


By the way, I was able to (after much searching) find a way that I could
reverse text in SQLITE, this will reverse strings upto 30 characters long as
the column "reversed"

SELECT translit,
SUBSTR(translit,-1,1)||
SUBSTR(translit,-2,1)||
SUBSTR(translit,-3,1)||
SUBSTR(translit,-4,1)||
SUBSTR(translit,-5,1)||
SUBSTR(translit,-6,1)||
SUBSTR(translit,-7,1)||
SUBSTR(translit,-8,1)||
SUBSTR(translit,-9,1)||
SUBSTR(translit,-10,1)||
SUBSTR(translit,-11,1)||
SUBSTR(translit,-12,1)||
SUBSTR(translit,-13,1)||
SUBSTR(translit,-14,1)||
SUBSTR(translit,-15,1)||
SUBSTR(translit,-16,1)||
SUBSTR(translit,-17,1)||
SUBSTR(translit,-18,1)||
SUBSTR(translit,-19,1)||
SUBSTR(translit,-20,1)|| 
SUBSTR(translit,-21,1)||
SUBSTR(translit,-22,1)||
SUBSTR(translit,-23,1)||
SUBSTR(translit,-24,1)||
SUBSTR(translit,-25,1)||
SUBSTR(translit,-26,1)||
SUBSTR(translit,-27,1)||
SUBSTR(translit,-28,1)||
SUBSTR(translit,-29,1)||
SUBSTR(translit,-30,1) 'reversed' 
from otpfinal4;

 

-- 
View this message in context: 
http://old.nabble.com/Reversing-Text-not-Numbers-tp32906645p32945134.html
Sent from the SQLite mailing list archive at Nabble.com.

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

Re: [sqlite] Error: disk I/O error while creating an index

[Dan Kennedy]

On 12/10/2011 06:01 AM, Tal Tabakman wrote:

Hi,

thanks for the reply.

so, I don't have TMPDIR defined in my env. what is the behaviour of
sqlite in such cases ? is there a default ?


By default it will try /var/tmp, then /usr/tmp, then /tmp.
SQLite creates temporary files there that it uses to sort
the data before creating the index. This is different from
the database journal file, which is created in the same
directory as the database file.

You say in another post that the db is only 64MB in size.
So I guess it is not too likely that you are running out
of temp space. Still, try setting TMPDIR to something
sensible anyway.

Another way to go is to run the whole thing under [strace].
Then inspect the output and see if there is an unexpected
system call failure towards the end.



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

Re: [sqlite] Error: disk I/O error while creating an index

[Simon Slavin]

On 10 Dec 2011, at 1:23am, Pavel Ivanov wrote:

>> so, I can see the journal file in my work directory which I own (and
>> in which the db file is placed).
>> 
>> still constantly, I have a disk I/O ERROR which I don't understand.
> 
> If you see journal file along your database file and each attempt to
> open database doesn't delete journal file it means you either don't
> have write permissions on database file, or you don't have write
> permissions on journal file, or you don't have write permissions on
> directory, so that SQLite cannot delete journal.
> Note: don't delete journal yourself - SQLite have to see it. You can
> make a copy of it and try to delete just to check your permissions,
> but then you should restore journal from backup and open database file
> with SQLite.

To neatly delete the journal file simply use the SQLite library to open the 
database, then close it properly with _close().  This should delete the journal 
file.  If the journal file is still there then either some process has it open 
or you lack the permissions needed to delete the file.

Of course, there's always the possibility you have a corrupt hard disk.  Have 
you run CHKDSK/Disk Utility/whatever ?

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

Re: [sqlite] Error: disk I/O error while creating an index

[Pavel Ivanov]

> so, I can see the journal file in my work directory which I own (and
> in which the db file is placed).
>
> still constantly, I have a disk I/O ERROR which I don't understand.

If you see journal file along your database file and each attempt to
open database doesn't delete journal file it means you either don't
have write permissions on database file, or you don't have write
permissions on journal file, or you don't have write permissions on
directory, so that SQLite cannot delete journal.
Note: don't delete journal yourself - SQLite have to see it. You can
make a copy of it and try to delete just to check your permissions,
but then you should restore journal from backup and open database file
with SQLite.


Pavel


On Fri, Dec 9, 2011 at 7:04 PM, Tal Tabakman  wrote:
> Hi,
>
> thanks a lot.
>
> so, I can see the journal file in my work directory which I own (and
> in which the db file is placed).
>
> still constantly, I have a disk I/O ERROR which I don't understand.
>
> the db is only 64M on disk and I have plenty of space.
>
> how can I get to the bottom of this ?
>
> can it be related not to the indexing, but rather to something bad
> that happened during the db creation ?
>
> please advice
>
> Tal
>
>>* so, I don't have TMPDIR defined in my env. what is the behaviour of*>* 
>>sqlite in such cases ? is there a default ?*
> The journal file will be created in the same directory as the database
> file.  For this to work, your application and user must have enough
> privilages to create a new file in that directory.
>
> Simon.
>
>
> On Sat, Dec 10, 2011 at 1:01 AM, Tal Tabakman wrote:
>
>> Hi,
>>
>> thanks for the reply.
>>
>> so, I don't have TMPDIR defined in my env. what is the behaviour of sqlite 
>> in such cases ? is there a default ?
>>
>> cheers
>>
>> Tal
>>
>> On 12/09/2011 04:02 PM, Tal Tabakman wrote:
>> >* Hi Guys,*>* I have an SQLITE database of 1.5 million rows in a single 
>> >table*>* each raw looks like:*>**>*     
>> >149|25|439198507|-1|0|1|44954|24|17|31|9|9|-1|-1|*>**>* now, from the 
>> >sqlite command line interface I am creating an INDEX on a sinGle*>* integer 
>> >column*>**>* CREATE INDEX IND1 ON ENTRIES (snum)*>**>* the result I get 
>> >is:*>**>* Error: disk I/O error*>**>* can you advice how to debug this one 
>> >?*
>> You might be running out of space wherever temporary
>> tables are stored on your system.
>>
>> If you're on unix, try changing environment variable
>> TMPDIR to point to somewhere you have lots of free disk
>> space (say three times the size of the eventual index).
>>
>> Or (I think) environment variable TMP or TEMP on Windows.
>>
>>
>>
> ___
> 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] Error: disk I/O error while creating an index

[Tal Tabakman]

Hi,

thanks a lot.

so, I can see the journal file in my work directory which I own (and
in which the db file is placed).

still constantly, I have a disk I/O ERROR which I don't understand.

the db is only 64M on disk and I have plenty of space.

how can I get to the bottom of this ?

can it be related not to the indexing, but rather to something bad
that happened during the db creation ?

please advice

Tal

>* so, I don't have TMPDIR defined in my env. what is the behaviour of*>* 
>sqlite in such cases ? is there a default ?*
The journal file will be created in the same directory as the database
file.  For this to work, your application and user must have enough
privilages to create a new file in that directory.

Simon.


On Sat, Dec 10, 2011 at 1:01 AM, Tal Tabakman wrote:

> Hi,
>
> thanks for the reply.
>
> so, I don't have TMPDIR defined in my env. what is the behaviour of sqlite in 
> such cases ? is there a default ?
>
> cheers
>
> Tal
>
> On 12/09/2011 04:02 PM, Tal Tabakman wrote:
> >* Hi Guys,*>* I have an SQLITE database of 1.5 million rows in a single 
> >table*>* each raw looks like:*>**>* 
> >149|25|439198507|-1|0|1|44954|24|17|31|9|9|-1|-1|*>**>* now, from the 
> >sqlite command line interface I am creating an INDEX on a sinGle*>* integer 
> >column*>**>* CREATE INDEX IND1 ON ENTRIES (snum)*>**>* the result I get 
> >is:*>**>* Error: disk I/O error*>**>* can you advice how to debug this one ?*
> You might be running out of space wherever temporary
> tables are stored on your system.
>
> If you're on unix, try changing environment variable
> TMPDIR to point to somewhere you have lots of free disk
> space (say three times the size of the eventual index).
>
> Or (I think) environment variable TMP or TEMP on Windows.
>
>
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Error: disk I/O error while creating an index

[Simon Slavin]

On 9 Dec 2011, at 11:01pm, Tal Tabakman wrote:

> so, I don't have TMPDIR defined in my env. what is the behaviour of
> sqlite in such cases ? is there a default ?

The journal file will be created in the same directory as the database file.  
For this to work, your application and user must have enough privilages to 
create a new file in that directory.

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

[sqlite] Error: disk I/O error while creating an index

[Tal Tabakman]

Hi,

thanks for the reply.

so, I don't have TMPDIR defined in my env. what is the behaviour of
sqlite in such cases ? is there a default ?

cheers

Tal

On 12/09/2011 04:02 PM, Tal Tabakman wrote:
>* Hi Guys,*>* I have an SQLITE database of 1.5 million rows in a single 
>table*>* each raw looks like:*>**>* 
>149|25|439198507|-1|0|1|44954|24|17|31|9|9|-1|-1|*>**>* now, from the 
>sqlite command line interface I am creating an INDEX on a sinGle*>* integer 
>column*>**>* CREATE INDEX IND1 ON ENTRIES (snum)*>**>* the result I get 
>is:*>**>* Error: disk I/O error*>**>* can you advice how to debug this one ?*
You might be running out of space wherever temporary
tables are stored on your system.

If you're on unix, try changing environment variable
TMPDIR to point to somewhere you have lots of free disk
space (say three times the size of the eventual index).

Or (I think) environment variable TMP or TEMP on Windows.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Unable to retrieve columns with table accessor on nested joins.

[Fabrizio Steiner]

> -Ursprüngliche Nachricht-
> Von: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> boun...@sqlite.org] Im Auftrag von Jay A. Kreibich
> Gesendet: Freitag, 9. Dezember 2011 17:42
> An: General Discussion of SQLite Database
> Betreff: Re: [sqlite] Unable to retrieve columns with table accessor on nested
> joins.
> 
> On Fri, Dec 09, 2011 at 01:12:26PM +, Fabrizio Steiner scratched on the
> wall:
> > SELECT t1_title, t2.t2_title, t3.t3_title FROM t1 LEFT JOIN ( t2 INNER
> > JOIN t3 ON t2_id = t3_id) ON t1_id = t2.t2_id;
> >
> > You will receive?SQL Error: no such column: t2.t2_title
> 
>   If you name the result of the sub-join, making it a "top level"
>   object, things work fine:
> 
>   SELECT t1_title, sub.t2_title, sub.t3_title
> FROM t1 LEFT JOIN
>( t2 INNER JOIN t3 ON t2_id = t3_id ) AS sub
>ON t1_id = sub.t2_id;
> 
>   This avoids having to re-order the query, although I suppose it
>   doesn't solve the problem of ambiguous column names in the sub-join.

I'm aware of this solution, but as you say it doesn't help for ambiguos columns 
or it will result in rewriting the sub-join to a subquery with field aliases. 
But e.g. SQL Server doesn't allow to specify an alias for a sub-join they only 
allow it for subqueries. I haven't taken a look onto the SQL92 specification if 
this is valid or not, but at least the SQL syntax page of sqlite states that 
it's not possible to specify an alias for join-source. 
https://sqlite.org/lang_select.html Defining an alias is only possible for a 
single qualified table or if it's a sub-query (select-statement).

If this is really what was intended to be implemented, why should re-ordering 
make any difference, I think it really shouldn't matter if the sub-join is a 
right-hand sub-join or a left-hand sub-join.

> > This is perfectly fine if it's a subquery but if the subquery
> > represents a nested join it has to be possible to access the tables
> > used in the subquery. At least it's possible with all the database
> > systems I'm working with in daily business.
> 
>   Some SQL engines actually require sub-queries to be named.  The
>   columns lose their association with their source tables in a
>   sub-query, so column level access required giving the result a name,
>   not unlike I have done above.

I completely agree with you for sub-quries.

>   As you pointed out, that means that when the sub-join takes on the
>   context of a full sub-query, the naming conventions follow, and that
>   might be considered a bug.  The system needs to distinguish between a
>   sub-join converted to a sub-query and a full sub-query, however, and
>   only allow "deeper" access for sub-joins (I didn't have a chance to
>   review your patch to see if you account for this or not).  

Yes, that is what my patch does. It only allows access to the tables of a 
sub-join which has been internally converted to a sub-query for execution.

> Allowing
>   access to sub-query result set columns via their source table names
>   seems just as much a bug.

Yes that should not be possible and that's also not possible with my patch.

Regards
Fabrizio
 

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

Re: [sqlite] Unable to retrieve columns with table accessor on nested joins.

[Jay A. Kreibich]

On Fri, Dec 09, 2011 at 01:12:26PM +, Fabrizio Steiner scratched on the 
wall:
> Hello
> 
> I'm currently facing a problem with nested right hand joins. I've also
> reported this to the mailing list over one month ago, but haven't
> received any reply. In the meantime I've investigated the problem in
> the SQLite source and sorted some things out.
> 
> Let's first start with an example which reproduces the problem:
> 
> CREATE TABLE t1(t1_id INTEGER PRIMARY KEY, t1_title TEXT);
> CREATE TABLE t2(t2_id INTEGER PRIMARY KEY, t2_title TEXT);
> CREATE TABLE t3(t3_id INTEGER PRIMARY KEY, t3_title TEXT);
> 
> INSERT INTO t1 (t1_id, t1_title) VALUES (888, 'data1');
> INSERT INTO t2 (t2_id, t2_title) VALUES (999, 'data2');
> INSERT INTO t3 (t3_id, t3_title) VALUES (999, 'data3');
> 
> Exeuting the following query works as expected and results?:
> data1 | null | null
> 
> SELECT t1_title, t2_title, t3_title
> FROM t1 LEFT JOIN ( t2 INNER JOIN t3 ON t2_id = t3_id) ON t1_id = t2_id;
> 
> If you now use the tablename t2 or t3 to access the columns like in the
> following query?: 
> 
> SELECT t1_title, t2.t2_title, t3.t3_title
> FROM t1 LEFT JOIN ( t2 INNER JOIN t3 ON t2_id = t3_id) ON t1_id = t2.t2_id;
> 
> You will receive?SQL Error: no such column: t2.t2_title

  If you name the result of the sub-join, making it a "top level"
  object, things work fine:

  SELECT t1_title, sub.t2_title, sub.t3_title
FROM t1 LEFT JOIN
   ( t2 INNER JOIN t3 ON t2_id = t3_id ) AS sub
   ON t1_id = sub.t2_id;

  This avoids having to re-order the query, although I suppose it
  doesn't solve the problem of ambiguous column names in the sub-join.

> This is perfectly fine if it's a subquery but if the subquery
> represents a nested join it has to be possible to access the tables
> used in the subquery. At least it's possible with all the database
> systems I'm working with in daily business.

  Some SQL engines actually require sub-queries to be named.  The
  columns lose their association with their source tables in a
  sub-query, so column level access required giving the result a name,
  not unlike I have done above.

  As you pointed out, that means that when the sub-join takes on the
  context of a full sub-query, the naming conventions follow, and that
  might be considered a bug.  The system needs to distinguish between a
  sub-join converted to a sub-query and a full sub-query, however, and
  only allow "deeper" access for sub-joins (I didn't have a chance to
  review your patch to see if you account for this or not).  Allowing
  access to sub-query result set columns via their source table names
  seems just as much a bug.

   -j


-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it,
 but showing it to the wrong people has the tendency to make them
 feel uncomfortable." -- Angela Johnson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Unable to retrieve columns with table accessor on nested joins.

[Fabrizio Steiner]

Seems like the patch didn't get through, let's try again.

Regards
Fabrizio

-Ursprüngliche Nachricht-
Von: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
Im Auftrag von Fabrizio Steiner
Gesendet: Freitag, 9. Dezember 2011 14:12
An: 'sqlite-users@sqlite.org'
Betreff: [sqlite] Unable to retrieve columns with table accessor on nested 
joins.

Hello

I'm currently facing a problem with nested right hand joins. I've also reported 
this to the mailing list over one month ago, but haven't received any reply. In 
the meantime I've investigated the problem in the SQLite source and sorted some 
things out.

Let's first start with an example which reproduces the problem:

CREATE TABLE t1(t1_id INTEGER PRIMARY KEY, t1_title TEXT);
CREATE TABLE t2(t2_id INTEGER PRIMARY KEY, t2_title TEXT);
CREATE TABLE t3(t3_id INTEGER PRIMARY KEY, t3_title TEXT);

INSERT INTO t1 (t1_id, t1_title) VALUES (888, 'data1');
INSERT INTO t2 (t2_id, t2_title) VALUES (999, 'data2');
INSERT INTO t3 (t3_id, t3_title) VALUES (999, 'data3');

Exeuting the following query works as expected and results : data1 | null | null

SELECT t1_title, t2_title, t3_title
FROM t1 LEFT JOIN ( t2 INNER JOIN t3 ON t2_id = t3_id) ON t1_id = t2_id;

If you now use the tablename t2 or t3 to access the columns like in the 
following query : 

SELECT t1_title, t2.t2_title, t3.t3_title
FROM t1 LEFT JOIN ( t2 INNER JOIN t3 ON t2_id = t3_id) ON t1_id = t2.t2_id;

You will receive SQL Error: no such column: t2.t2_title

I've also investigated the mailing list archive and I've seen one or two 
messages regarding this problem. There the advice was given to rewrite the join 
so you dont have a nested right handed join. I know this could be the solution 
in some cases but it's not always possible to do that. And it's not always 
possible to do the join without a table accessor on the columns, especially if 
you always use the same column name for the primary key on all tables.

As I've stated, I've investigated the SQLite source and found the problem why 
this doesn 't work. Right handed nested joins are currently implemented by 
subqueries, because the joining is implemented as a list, where every table is 
joined with the next one. The "lookupName" function which tries to search the 
column in the used tables searches only through the source list. The subquery, 
which represents the nested join, has an internal table name and there will  
never be tried to take a look on the tables used in the subquery to resolve the 
column. This is perfectly fine if it's a subquery but if the subquery 
represents a nested join it has to be possible to access the tables used in the 
subquery. At least it's possible with all the database systems I'm working with 
in daily business.

Attached you will find a tcl test which shows the problem as well. In addition 
you will find a patch which I've implemented to solve the problem. I know the 
patch is not a perfect solution, but for the patch my main goal was to fix the 
problem with at least changes to the original SQLite source as possible. The 
patch currently breaks two of the authorizer tests, because for the patch to 
work the resolving order of subqueries is changed in "resolveSelectStep" . 
First subqueries in the FROM will be resolved and afterwards the result set. 
The patch basically marks nested join subqueries during parsing and during 
lookup it also searches in these subquries for the table.column. After a match, 
this column will be remapped to correct column on the subqury result set. It 
also works for TABLENAME.rowid as long as the rowid is designed in the table as 
INTEGER PRIMARY KEY.
One problem still exists, it's not possible to execute a query with a selection 
of all columns of a table, where  table is defined in a nested join subquery, 
e.g.

SELECT t2.*
FROM t1 LEFT JOIN ( t2 INNER JOIN t3 ON t2.t2_id = t3.t3_id) ON t1_id = 
t2.t2_id;

I would really appreciate it if someone of the developers would reply to this 
mail what they're opinion is regarding this problem, because I really think 
this is a bug.

Kind Regards
Fabrizio

Index: src/parse.y
===
--- src/parse.y
+++ src/parse.y
@@ -505,10 +505,11 @@
   A = F;
 }else{
   Select *pSubquery;
   sqlite3SrcListShiftJoinType(F);
   pSubquery = sqlite3SelectNew(pParse,0,F,0,0,0,0,0,0,0);
+  pSubquery->selFlags |= SF_NestedJoin;
   A = sqlite3SrcListAppendFromTerm(pParse,X,0,0,,pSubquery,N,U);
 }
   }

   // A seltablist_paren nonterminal represents anything in a FROM that

Index: src/resolve.c
===
--- src/resolve.c
+++ src/resolve.c
@@ -113,10 +113,168 @@
 }
   }
   return 0;
 }

+/*
+** Given the name of a column of the form X.Y.Z or Y.Z or just Z, look up
+** that name in the set of source tables in pSrcList and make the pExpr
+** expression node refer back to that 

[sqlite] Unable to retrieve columns with table accessor on nested joins.

[Fabrizio Steiner]

Hello

I'm currently facing a problem with nested right hand joins. I've also reported 
this to the mailing list over one month ago, but haven't received any reply. In 
the meantime I've investigated the problem in the SQLite source and sorted some 
things out.

Let's first start with an example which reproduces the problem:

CREATE TABLE t1(t1_id INTEGER PRIMARY KEY, t1_title TEXT);
CREATE TABLE t2(t2_id INTEGER PRIMARY KEY, t2_title TEXT);
CREATE TABLE t3(t3_id INTEGER PRIMARY KEY, t3_title TEXT);

INSERT INTO t1 (t1_id, t1_title) VALUES (888, 'data1');
INSERT INTO t2 (t2_id, t2_title) VALUES (999, 'data2');
INSERT INTO t3 (t3_id, t3_title) VALUES (999, 'data3');

Exeuting the following query works as expected and results : data1 | null | null

SELECT t1_title, t2_title, t3_title
FROM t1 LEFT JOIN ( t2 INNER JOIN t3 ON t2_id = t3_id) ON t1_id = t2_id;

If you now use the tablename t2 or t3 to access the columns like in the 
following query : 

SELECT t1_title, t2.t2_title, t3.t3_title
FROM t1 LEFT JOIN ( t2 INNER JOIN t3 ON t2_id = t3_id) ON t1_id = t2.t2_id;

You will receive SQL Error: no such column: t2.t2_title

I've also investigated the mailing list archive and I've seen one or two 
messages regarding this problem. There the advice was given to rewrite the join 
so you dont have a nested right handed join. I know this could be the solution 
in some cases but it's not always possible to do that. And it's not always 
possible to do the join without a table accessor on the columns, especially if 
you always use the same column name for the primary key on all tables.

As I've stated, I've investigated the SQLite source and found the problem why 
this doesn 't work. Right handed nested joins are currently implemented by 
subqueries, because the joining is implemented as a list, where every table is 
joined with the next one. The "lookupName" function which tries to search the 
column in the used tables searches only through the source list. The subquery, 
which represents the nested join, has an internal table name and there will  
never be tried to take a look on the tables used in the subquery to resolve the 
column. This is perfectly fine if it's a subquery but if the subquery 
represents a nested join it has to be possible to access the tables used in the 
subquery. At least it's possible with all the database systems I'm working with 
in daily business.

Attached you will find a tcl test which shows the problem as well. In addition 
you will find a patch which I've implemented to solve the problem. I know the 
patch is not a perfect solution, but for the patch my main goal was to fix the 
problem with at least changes to the original SQLite source as possible. The 
patch currently breaks two of the authorizer tests, because for the patch to 
work the resolving order of subqueries is changed in "resolveSelectStep" . 
First subqueries in the FROM will be resolved and afterwards the result set. 
The patch basically marks nested join subqueries during parsing and during 
lookup it also searches in these subquries for the table.column. After a match, 
this column will be remapped to correct column on the subqury result set. It 
also works for TABLENAME.rowid as long as the rowid is designed in the table as 
INTEGER PRIMARY KEY.
One problem still exists, it's not possible to execute a query with a selection 
of all columns of a table, where  table is defined in a nested join subquery, 
e.g.

SELECT t2.*
FROM t1 LEFT JOIN ( t2 INNER JOIN t3 ON t2.t2_id = t3.t3_id) ON t1_id = 
t2.t2_id;

I would really appreciate it if someone of the developers would reply to this 
mail what they're opinion is regarding this problem, because I really think 
this is a bug.

Kind Regards
Fabrizio

# 2011 December 09
#
# The author disclaims copyright to this source code.  In place of
# a legal notice, here is a blessing:
#
#May you do good and not evil.
#May you find forgiveness for yourself and forgive others.
#May you share freely, never taking more than you give.
#
#***
# This file implements regression tests for SQLite library.
#
# This file implements tests to check if nested right hand joins work correctly.

set testdir [file dirname $argv0]
source $testdir/tester.tcl

set testprefix "tkt-nested-joins"

do_execsql_test 1.1 {
  CREATE TABLE t1(t1_id INTEGER PRIMARY KEY, t1_title TEXT);
  CREATE TABLE t2(t2_id INTEGER PRIMARY KEY, t2_title TEXT);
  CREATE TABLE t3(t3_id INTEGER PRIMARY KEY, t3_title TEXT);

  INSERT INTO t1 (t1_id, t1_title) VALUES (888, 'data1');
  INSERT INTO t2 (t2_id, t2_title) VALUES (999, 'data2');
  INSERT INTO t3 (t3_id, t3_title) VALUES (999, 'data3');
}

do_execsql_test 1.2 {
  SELECT t1_title, ifnull(t2_title, 'nil')
  FROM t1 LEFT JOIN (t2) ON t1_id = t2_id;
} {data1 nil}

do_execsql_test 1.3 {
  SELECT t1.t1_title, ifnull(t2.t2_title, 'nil')
  FROM t1 LEFT JOIN (t2) ON t1.t1_id = t2.t2_id;
} {data1 nil}

Re: [sqlite] FTS Tokenizer (separator)

[Richard Hipp]

On Fri, Dec 9, 2011 at 6:48 AM, Ephraim Stevens
wrote:

> Greetings All,
>
> From section seven of the FTS3/FTS4 documentation:
>
> A term is a contiguous sequence of eligible characters, where eligible
> characters are all alphanumeric characters, the "_" character, and all
> characters with UTF codepoints greater than or equal to 128. All other
> characters are discarded when splitting a document into terms. Their only
> contribution is to separate adjacent terms.
>
>
> Is there a way to modify/control this behavior?  I would like the equal
> sign ('=') to be treated with the same designation as an alpha numeric
> character. Currently, the equal sign acts as a separator.
>
> Thanks in advance for any suggestions/help you provide.
>

Create your own tokenizer.  http://www.sqlite.org/fts3.html#section_7_1


> ___
> 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

[sqlite] FTS Tokenizer (separator)

[Ephraim Stevens]

Greetings All,

>From section seven of the FTS3/FTS4 documentation:

A term is a contiguous sequence of eligible characters, where eligible
characters are all alphanumeric characters, the "_" character, and all
characters with UTF codepoints greater than or equal to 128. All other
characters are discarded when splitting a document into terms. Their only
contribution is to separate adjacent terms.


Is there a way to modify/control this behavior?  I would like the equal
sign ('=') to be treated with the same designation as an alpha numeric
character. Currently, the equal sign acts as a separator.

Thanks in advance for any suggestions/help you provide.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Error: disk I/O error while creating an index

[Simon Slavin]

On 9 Dec 2011, at 9:02am, Tal Tabakman wrote:

> CREATE INDEX IND1 ON ENTRIES (snum)
> 
> the result I get is:
> 
> Error: disk I/O error
> 
> can you advice how to debug this one ?

Before your 'CREATE INDEX' command try typeing

.stats ON

It may or may not do something, depending on which version you're using.

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

Re: [sqlite] Error: disk I/O error while creating an index

[Dan Kennedy]

On 12/09/2011 04:02 PM, Tal Tabakman wrote:

Hi Guys,
I have an SQLITE database of 1.5 million rows in a single table
each raw looks like:

149|25|439198507|-1|0|1|44954|24|17|31|9|9|-1|-1|

now, from the sqlite command line interface I am creating an on a sincle
integer column

CREATE INDEX IND1 ON ENTRIES (snum)

the result I get is:

Error: disk I/O error

can you advice how to debug this one ?


You might be running out of space wherever temporary
tables are stored on your system.

If you're on unix, try changing environment variable
TMPDIR to point to somewhere you have lots of free disk
space (say three times the size of the eventual index).

Or (I think) environment variable TMP or TEMP on Windows.

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

[sqlite] Error: disk I/O error while creating an index

[Tal Tabakman]

Hi Guys,
I have an SQLITE database of 1.5 million rows in a single table
each raw looks like:

   149|25|439198507|-1|0|1|44954|24|17|31|9|9|-1|-1|

now, from the sqlite command line interface I am creating an on a sincle
integer column

CREATE INDEX IND1 ON ENTRIES (snum)

the result I get is:

Error: disk I/O error

can you advice how to debug this one ?
cheers
Tal
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users