Re: [sqlite] xBestIndex/XFilter and virtual tables

[Hick Gunter]

BTW: SQLite will also ask your virtual table about GROUP BY/ORDER BY 
capabilities if either of the clauses is included in the SELECT.

-Ursprüngliche Nachricht-
Von: Hick Gunter [mailto:h...@scigames.at]
Gesendet: Montag, 12. Jänner 2015 08:02
An: 'General Discussion of SQLite Database'
Betreff: Re: [sqlite] xBestIndex/XFilter and virtual tables

SQLite is asking your virtual table questions:

1) what is the cost of a full table scan? (your answer: 1000)
2) what is the cost of a lookup by "id"? (your answer: 1)
3) when performing a key lookup, do you promise to return only rows matching 
the key? (your answer in returned in the "omit" field)

Quesition 1 establishes the base line cost of a full table scan. SQLite knows 
it can answer any query for a constant set of constraints by incurring this cost

Question 2 establishes the cost of retrieving a single row. The answer 1 is 
probably somewhat overoptimistic in terms of real cost which is O(log n).

SQLite now needs to determine if scanning once (1000) is more or less costly 
than performing n key lookups. This cost depends on the answer to Question 3.

If you promise only to return rows matching the given id, the cost is (n * 1) 
=> SQLite will choose to perform n calls to xFilter, once for each element of 
the key set.

If you cannot promise to return only the matching rows, how many rows will you 
return? The conservative estimate is "no more than there are rows in the table" 
(=1000) => SQLite must choose to scan the table once.

-Ursprüngliche Nachricht-
Von: Venkat Murty [mailto:vmu...@fiberlink.com]
Gesendet: Sonntag, 11. Jänner 2015 23:52
An: sqlite-users@sqlite.org
Betreff: Re: [sqlite] xBestIndex/XFilter and virtual tables



>Estimated cost and row for each call to xBestIndex (sqlite3_index_info is 
>filled with the below values for each call)
>
>a) When there is one non-usable constraint
> idxNum = 0 estimatedCost = 1000.00 estimatedRows = 1000
>
>b) when there is one usable constraint
>idxNum = 1 estimatedCost = 1.00 estimatedRows = 1
>
>xFilter is called with idxNum = 0 and argc = 0

If aConstraintUsage[0].omit = 1 then xFilter is called multiple times with 
idxNum = 1. If  aConstraintUsage[0].omit = 0 then xFilter is called with idxNum 
= 0 and argc = 0




Fiberlink Disclaimer: The information transmitted is intended only for the 
person or entity to which it is addressed and may contain confidential and/or 
privileged material.  Any review, retransmission, dissemination or other use 
of, or taking of any action in reliance upon, this information by persons or 
entities other than the intended recipient is prohibited.  If you received this 
in error, please contact the sender and delete the material from any computer.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


___
 Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: +43 1 80100 0
E-Mail: h...@scigames.at

This communication (including any attachments) is intended for the use of the 
intended recipient(s) only and may contain information that is confidential, 
privileged or legally protected. Any unauthorized use or dissemination of this 
communication is strictly prohibited. If you have received this communication 
in error, please immediately notify the sender by return e-mail message and 
delete all copies of the original communication. Thank you for your cooperation.


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


___
 Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: +43 1 80100 0
E-Mail: h...@scigames.at

This communication (including any attachments) is intended for the use of the 
intended recipient(s) only and may contain information that is confidential, 
privileged or legally protected. Any unauthorized use or dissemination of this 
communication is strictly prohibited. If you have received this communication 
in error, please immediately notify the sender by return e-mail message and 
delete all copies of the original communication. Thank you for your cooperation.


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

Re: [sqlite] xBestIndex/XFilter and virtual tables

[Hick Gunter]

SQLite is asking your virtual table questions:

1) what is the cost of a full table scan? (your answer: 1000)
2) what is the cost of a lookup by "id"? (your answer: 1)
3) when performing a key lookup, do you promise to return only rows matching 
the key? (your answer in returned in the "omit" field)

Quesition 1 establishes the base line cost of a full table scan. SQLite knows 
it can answer any query for a constant set of constraints by incurring this cost

Question 2 establishes the cost of retrieving a single row. The answer 1 is 
probably somewhat overoptimistic in terms of real cost which is O(log n).

SQLite now needs to determine if scanning once (1000) is more or less costly 
than performing n key lookups. This cost depends on the answer to Question 3.

If you promise only to return rows matching the given id, the cost is (n * 1) 
=> SQLite will choose to perform n calls to xFilter, once for each element of 
the key set.

If you cannot promise to return only the matching rows, how many rows will you 
return? The conservative estimate is "no more than there are rows in the table" 
(=1000) => SQLite must choose to scan the table once.

-Ursprüngliche Nachricht-
Von: Venkat Murty [mailto:vmu...@fiberlink.com]
Gesendet: Sonntag, 11. Jänner 2015 23:52
An: sqlite-users@sqlite.org
Betreff: Re: [sqlite] xBestIndex/XFilter and virtual tables



>Estimated cost and row for each call to xBestIndex (sqlite3_index_info is 
>filled with the below values for each call)
>
>a) When there is one non-usable constraint
> idxNum = 0 estimatedCost = 1000.00 estimatedRows = 1000
>
>b) when there is one usable constraint
>idxNum = 1 estimatedCost = 1.00 estimatedRows = 1
>
>xFilter is called with idxNum = 0 and argc = 0

If aConstraintUsage[0].omit = 1 then xFilter is called multiple times with 
idxNum = 1. If  aConstraintUsage[0].omit = 0 then xFilter is called with idxNum 
= 0 and argc = 0




Fiberlink Disclaimer: The information transmitted is intended only for the 
person or entity to which it is addressed and may contain confidential and/or 
privileged material.  Any review, retransmission, dissemination or other use 
of, or taking of any action in reliance upon, this information by persons or 
entities other than the intended recipient is prohibited.  If you received this 
in error, please contact the sender and delete the material from any computer.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


___
 Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: +43 1 80100 0
E-Mail: h...@scigames.at

This communication (including any attachments) is intended for the use of the 
intended recipient(s) only and may contain information that is confidential, 
privileged or legally protected. Any unauthorized use or dissemination of this 
communication is strictly prohibited. If you have received this communication 
in error, please immediately notify the sender by return e-mail message and 
delete all copies of the original communication. Thank you for your cooperation.


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

Re: [sqlite] xBestIndex/XFilter and virtual tables

[Venkat Murty]

>Estimated cost and row for each call to xBestIndex (sqlite3_index_info is 
>filled with the below values for each call)
>
>a) When there is one non-usable constraint
> idxNum = 0 estimatedCost = 1000.00 estimatedRows = 1000
>
>b) when there is one usable constraint
>idxNum = 1 estimatedCost = 1.00 estimatedRows = 1
>
>xFilter is called with idxNum = 0 and argc = 0

If aConstraintUsage[0].omit = 1 then xFilter is called multiple times with 
idxNum = 1. If  aConstraintUsage[0].omit = 0 then xFilter is called with idxNum 
= 0 and argc = 0




Fiberlink Disclaimer: The information transmitted is intended only for the 
person or entity to which it is addressed and may contain confidential and/or 
privileged material.  Any review, retransmission, dissemination or other use 
of, or taking of any action in reliance upon, this information by persons or 
entities other than the intended recipient is prohibited.  If you received this 
in error, please contact the sender and delete the material from any computer.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] xBestIndex/XFilter and virtual tables

[Richard Hipp]

On 1/11/15, Venkat Murty  wrote:
>
>
>>> I am expecting two usable entries in sqlite3_index_info with
>>> aConstraint[i].iColumn = 0 and aConstraint[i].op ==
>>> SQLITE_INDEX_CONSTRAINT_EQ for each check
>>
>> What actually happens is that SQLite transforms the query into
>>
>>SELECT stamp, to_text(value) FROM store WHERE id IN (42,24).
>>
>> The xBestIndex method does not distinguish between == and IN, so only
>> a single == constraint will be mentioned in the xBestIndex call.
>>
>
> How to i extract multiple values in xFilter -- int argc, sqlite3_value
> **argv ? If i specify aConstraintUsage[].argvIndex = 1 then i should expect
> a list of values (42 and 24) in argv[0].
>

xFilter will be called multiple times, once for each value in the RHS
of the IN operator.

-- 
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] xBestIndex/XFilter and virtual tables

[Venkat Murty]

>> I am expecting two usable entries in sqlite3_index_info with
>> aConstraint[i].iColumn = 0 and aConstraint[i].op ==
>> SQLITE_INDEX_CONSTRAINT_EQ for each check
>
> What actually happens is that SQLite transforms the query into
>
>SELECT stamp, to_text(value) FROM store WHERE id IN (42,24).
>
> The xBestIndex method does not distinguish between == and IN, so only
> a single == constraint will be mentioned in the xBestIndex call.
>

How to i extract multiple values in xFilter -- int argc, sqlite3_value **argv ? 
If i specify aConstraintUsage[].argvIndex = 1 then i should expect a list of 
values (42 and 24) in argv[0].

>>
>> xBestIndex is called twice,
>>
>>   a) once with only one non-usable entry with  aConstraint[i].iColumn = 0
>> and aConstraint[i].op == SQLITE_INDEX_CONSTRAINT_EQ and
>
> Here it is checking the cost of a full table scan.
>
>>   b) once with only one usable entry with aConstraint[i].iColumn = 0 and
>> aConstraint[i].op == SQLITE_INDEX_CONSTRAINT_EQ.
>
> Here it is checking the codes of using the IN operator.  What
> estimatedCost values are you returning for these two calls.  Because
> when I run it, and the cost of the second version is less, it chooses
> the second version.  What version of SQLite are you using?

Sqlite Version = 3.8.7.1

Estimated cost and row for each call to xBestIndex (sqlite3_index_info is 
filled with the below values for each call)

a) When there is one non-usable constraint
 idxNum = 0 estimatedCost = 1000.00 estimatedRows = 1000

b) when there is one usable constraint
idxNum = 1 estimatedCost = 1.00 estimatedRows = 1

xFilter is called with idxNum = 0 and argc = 0

Fiberlink Disclaimer: The information transmitted is intended only for the 
person or entity to which it is addressed and may contain confidential and/or 
privileged material.  Any review, retransmission, dissemination or other use 
of, or taking of any action in reliance upon, this information by persons or 
entities other than the intended recipient is prohibited.  If you received this 
in error, please contact the sender and delete the material from any computer.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite Android Bindings: how difficult to add LOCALIZED back?

[Christian Werner]

On 01/11/2015 01:55 PM, Philip Warner wrote:

On 9/01/2015 5:00 PM, Dan Kennedy wrote:

On 01/08/2015 07:48 AM, Philip Warner wrote:

How difficult would it be to add LOCALIZED collation support? I'm guessing that 
the fact it's not there means it's non-trivial, but I was hoping otherwise...


The stumbling block is that the Android implementations use ICU. So to use the 
Android versions I think we would have to build ICU as a static library as well 
as SQLite. And ICU is quite large.


Thanks for this; I was naively (again) hoping they might have built the unicode 
stuff as something that was dynamically linkable.

I must admit the benefits of a recent build + ability to build custom functions 
etc is very appealing.

btw, do you have an estimate of how big "quite large" might be?


Please review the stuff recently added to AndroWish's Tcl SQLite interface on

http://www.androwish.org/index.html/info/84d5ed5ae9c24bada8f8b9f9f198306a1e59300a

It tries to use whatever ICU infrastructure is already available on the device 
by
using dynamic linking.

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

Re: [sqlite] xBestIndex/XFilter and virtual tables

[Richard Hipp]

On 1/11/15, Venkat Murty  wrote:
> Hi,
>
> I have a virtual table.
>create table store (id int, stamp int, value blob);
>
> When i execute the following sql
>select stamp, to_text(value) from store where id = 42;
>
> I get as expected sqlite3_index_info to have one usable entry with
> aConstraint[i].iColumn = 0
> aConstraint[i].op == SQLITE_INDEX_CONSTRAINT_EQ
> In xBestIndex I set the aConstraintUsage[i].argvIndex value to 1, to
> indicate that '42' should be the first argument in corresponding xFilter
> call.
>
> And in the corresponding xFilter I get a value of 42 as the first argument
> and  I can initialize the cursor with just one element with id = 42.
>
> When i execute the following sql
>select stamp, to_text(value) from store where id = 42 or id = 24;
>
> I am expecting two usable entries in sqlite3_index_info with
> aConstraint[i].iColumn = 0 and aConstraint[i].op ==
> SQLITE_INDEX_CONSTRAINT_EQ for each check

What actually happens is that SQLite transforms the query into

   SELECT stamp, to_text(value) FROM store WHERE id IN (42,24).

The xBestIndex method does not distinguish between == and IN, so only
a single == constraint will be mentioned in the xBestIndex call.

>
> xBestIndex is called twice,
>
>   a) once with only one non-usable entry with  aConstraint[i].iColumn = 0
> and aConstraint[i].op == SQLITE_INDEX_CONSTRAINT_EQ and

Here it is checking the cost of a full table scan.

>   b) once with only one usable entry with aConstraint[i].iColumn = 0 and
> aConstraint[i].op == SQLITE_INDEX_CONSTRAINT_EQ.

Here it is checking the codes of using the IN operator.  What
estimatedCost values are you returning for these two calls.  Because
when I run it, and the cost of the second version is less, it chooses
the second version.  What version of SQLite are you using?

>
> And the xFilter called corresponds to the  first call to xBestIndex. Thus it
> does a table scan to find entries (42, 24)
>
> Am i missing something here ?
>
> -venkat murty
>
> Fiberlink Disclaimer: The information transmitted is intended only for the
> person or entity to which it is addressed and may contain confidential
> and/or privileged material.  Any review, retransmission, dissemination or
> other use of, or taking of any action in reliance upon, this information by
> persons or entities other than the intended recipient is prohibited.  If you
> received this in error, please contact the sender and delete the material
> from any computer.
> ___
> 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] PK References One of Two Other Tables

[Keith Medcalf]

On Sunday, 11 January, 2015 11:22, Rich Shepard  said:

>   Items to be raffled can be donated or purchased. The Donations and
>Purchases tables each have the item ID as their PK.

>   The Raffles table should have the PK as either the Donations or the
>Purchases item ID. How do I write the DDL creating the Raffles table so
>that the PK is either a donated or purchased item_id number? I've only 
>had a foreign key reference one specific table, not either of two tables 
>and I've not found a solution in the references available to me.

As already said, factor out the common item information into an Item table, the 
Donation information into a Donated table, and the Purchase information into a 
Purchased table.  Set up the schema as suggested by John.

CREATE TABLE Item
(
 itemnbr integer primary key,
 Description text collate nocase,
 DonatedOrPurchased text collate nocase NOT NULL CHECK(DonatedOrPurchased 
in ('D','P')
);
CREATE TABLE Donated 
(
 itemnbr integer primary key references Item on delete cascade,
--- other Donated unique columns 
);
CREATE TABLE Purchased 
(
 itemnbr integer primary key references Item ON DELETE CASCADE,
-- other Purchased unique columns
);

CREATE VIEW Items
as
   select * 
 from Items
left join Donated using (Itemnbr)
left join Purchased using (Itemnbr);

CREATE TRIGGER Items_Delete INSTEAD OF DELETE on Items
begin
 delete from Item where Item.Itemnbr = OLD.Itemnbr;
end;

CREATE TRIGGER Items_InsertD INSTEAD OF INSERT on Items
WHEN NEW.DonatedOrPurchased == 'D'
begin
 insert into Item values (NEW.Itemnbr, NEW. ...) -- for all fields that are 
part of the Item table
 insert into Donated values (NEW.Itemnbr, NEW. ...); -- for all fields that are 
part of the Donated table
END;

CREATE TRIGGER Items_InsertP INSTEAD OF INSERT on Items
WHEN NEW.DonatedOrPurchased == 'P'
begin
 insert into Item values (NEW.Itemnbr, NEW. ...) -- for all fields that are 
part of the Item table
 insert into Purchased values (NEW.Itemnbr, NEW. ...); -- for all fields that 
are part of the Purchased table
END;

Of course, you can also just always interd and update manually and use the 
Items view to get all the information for the item.


---
Theory is when you know everything but nothing works.  Practice is when 
everything works but no one knows why.  Sometimes theory and practice are 
combined:  nothing works and no one knows why.




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

[sqlite] xBestIndex/XFilter and virtual tables

[Venkat Murty]

Hi,

I have a virtual table.
   create table store (id int, stamp int, value blob);

When i execute the following sql
   select stamp, to_text(value) from store where id = 42;

I get as expected sqlite3_index_info to have one usable entry with
aConstraint[i].iColumn = 0
aConstraint[i].op == SQLITE_INDEX_CONSTRAINT_EQ
In xBestIndex I set the aConstraintUsage[i].argvIndex value to 1, to indicate 
that '42' should be the first argument in corresponding xFilter call.

And in the corresponding xFilter I get a value of 42 as the first argument and  
I can initialize the cursor with just one element with id = 42.

When i execute the following sql
   select stamp, to_text(value) from store where id = 42 or id = 24;

I am expecting two usable entries in sqlite3_index_info with 
aConstraint[i].iColumn = 0 and aConstraint[i].op == SQLITE_INDEX_CONSTRAINT_EQ 
for each check

xBestIndex is called twice,

  a) once with only one non-usable entry with  aConstraint[i].iColumn = 0 and 
aConstraint[i].op == SQLITE_INDEX_CONSTRAINT_EQ and
  b) once with only one usable entry with aConstraint[i].iColumn = 0 and 
aConstraint[i].op == SQLITE_INDEX_CONSTRAINT_EQ.

And the xFilter called corresponds to the  first call to xBestIndex. Thus it 
does a table scan to find entries (42, 24)

Am i missing something here ?

-venkat murty

Fiberlink Disclaimer: The information transmitted is intended only for the 
person or entity to which it is addressed and may contain confidential and/or 
privileged material.  Any review, retransmission, dissemination or other use 
of, or taking of any action in reliance upon, this information by persons or 
entities other than the intended recipient is prohibited.  If you received this 
in error, please contact the sender and delete the material from any computer.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] PK References One of Two Other Tables

[Rich Shepard]

On Sun, 11 Jan 2015, Simon Slavin wrote:


You can't do the latter.  Foreign keys can reference only one table.  You
could create yet another table, which just supplies primary keys, but it
would seem that this would just duplicate a function of your 'items'
table.


Simon,

  That's what I thought. Guess I need to restructure the tables so as to
avoid the situation.

Thanks,

Rich

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

Re: [sqlite] PK References One of Two Other Tables

[John McKown]

On Sun, Jan 11, 2015 at 12:21 PM, Rich Shepard 
wrote:

>   Items to be raffled can be donated or purchased. The Donations and
> Purchases tables each have the item ID as their PK.
>
>   The Raffles table should have the PK as either the Donations or the
> Purchases item ID. How do I write the DDL creating the Raffles table so
> that
> the PK is either a donated or purchased item_id number? I've only had a
> foreign key reference one specific table, not either of two tables and I've
> not found a solution in the references available to me.
>
> Rich
>

​It might be a tad easier to give an exact answer if you could post the DDL
which creates the tables. But basically, you can't do what you want
directly. Can the "item ID" value in the Purchases and Donations tables be
duplicated? If so, then I would suggest making them unique, perhaps by
prefixing them with a D and a P for "Donation" an "Purchase" respectively.
But, really, what _I_ personally would try to do is to factor out the
common columns in Purchases and Donations into a "items" table which would
then be the primary key in the Donations, Purchases, and Raffles tables.
This column would also be a foreign key in those table. Perhaps something
like:

CREATE TABLE All_Items (itemnbr integer primary key, Description text,
DonatedOrPurchased char(1) NOT NULL CHECK(DonatedOrPurchased in ('D','P'));
CREATE TABLE Donations (itemnbr integer primary key references All_Items
(itemnbr) ON DELETE CASCADE,
--- other Donations unique columns
);
CREATE TABLE Purchases (itemnbr integer primary key​

​references All_Items (itemnbr) ON DELETE CASCADE,
-- other Purchases unique columns
);​
​CREATE TABLE Raffles (itemnbr integer primary key reference All_Items
(itemnbr) ON DELETE CASCADE,
-- other Raffles columns
)​;




-- 
​
While a transcendent vocabulary is laudable, one must be eternally careful
so that the calculated objective of communication does not become ensconced
in obscurity.  In other words, eschew obfuscation.

111,111,111 x 111,111,111 = 12,345,678,987,654,321

Maranatha! <><
John McKown
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] PK References One of Two Other Tables

[Simon Slavin]

On 11 Jan 2015, at 6:21pm, Rich Shepard  wrote:

> I've only had a
> foreign key reference one specific table, not either of two tables

You can't do the latter.  Foreign keys can reference only one table.  You could 
create yet another table, which just supplies primary keys, but it would seem 
that this would just duplicate a function of your 'items' table.

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

[sqlite] PK References One of Two Other Tables

[Rich Shepard]

  Items to be raffled can be donated or purchased. The Donations and
Purchases tables each have the item ID as their PK.

  The Raffles table should have the PK as either the Donations or the
Purchases item ID. How do I write the DDL creating the Raffles table so that
the PK is either a donated or purchased item_id number? I've only had a
foreign key reference one specific table, not either of two tables and I've
not found a solution in the references available to me.

Rich

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

Re: [sqlite] aggregate functions with DISTINCT

[Clemens Ladisch]

Staffan Tylen wrote:
> Well, the SELECT is actually over 400 lines long [...]  I
> can't use SELECT DISTINCT X as that wouldn't give the result I want, and I
> can't use SELECT DISTINCT 'ABC' either. So my "Yes, it might work" comment
> doesn't actually hold. I see no other way than to use DISTINCT with the
> GROUP_CONCAT function, which in this case is invalid.

You could move the aggregate function into a correlated subquery:

  SELECT x,
 GROUP_CONCAT(y)
  FROM T1
  GROUP BY x

  =>

  SELECT x,
 (SELECT GROUP_CONCAT(y)
  FROM T1 as T1_inner
  WHERE T1_inner.x = T1.x)
  FROM T1
  GROUP BY x

This requires duplicating all the joins and WHEREs from the outer query,
but allows you to handle the inner query differently:

  SELECT x,
 (SELECT GROUP_CONCAT(y, ' ')
  FROM (SELECT DISTINCT y
FROM T1 as T1_inner
WHERE T1_inner.x = T1.x))
  FROM T1
  GROUP BY x


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

Re: [sqlite] SQLite Android Bindings: how difficult to add LOCALIZED back?

[Philip Warner]

On 9/01/2015 5:00 PM, Dan Kennedy wrote:

On 01/08/2015 07:48 AM, Philip Warner wrote:
How difficult would it be to add LOCALIZED collation support? I'm guessing 
that the fact it's not there means it's non-trivial, but I was hoping 
otherwise...


The stumbling block is that the Android implementations use ICU. So to use the 
Android versions I think we would have to build ICU as a static library as 
well as SQLite. And ICU is quite large.


Thanks for this; I was naively (again) hoping they might have built the unicode 
stuff as something that was dynamically linkable.


I must admit the benefits of a recent build + ability to build custom functions 
etc is very appealing.


btw, do you have an estimate of how big "quite large" might be?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite 3.8.8 expected in January

[RSmith]

On 2015/01/10 15:50, Richard Hipp wrote:
Yes, it was a compile-time omission. I have uploaded a new DLL that includes the loadable extension interface. 


Thank you - it works perfectly for all entries.

All other tests worked well too, so no new problems to report from this side.

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