Re: [sqlite] How to enforce a specific order of group_concat?
On Sunday, 1 March, 2020 14:58, mailing lists wrote:
>Assume I create the following table:
>CREATE TABLE Test (ID INTEGER PRIMARY KEY, Value TEXT);
>INSERT INTO Test (Value) VALUES('Alpha');
>INSERT INTO Test (Value) VALUES('Beta');
>INSERT INTO Test (Value) VALUES('Beta');
>INSERT INTO Test (Value) VALUES('Alpha');
>According to the documentation of group_concat the order is undefined,
>indeed:
>SELECT group_concat(DISTINCT Value) FROM Test ORDER BY Value ASC;
>SELECT group_concat(DISTINCT Value) FROM Test ORDER BY Value DESC;
>Both queries result in Alpha,Beta.
>Changing the queries to
>WITH Result(x) AS (SELECT DISTINCT Value FROM Test ORDER BY Value ASC)
>SELECT group_concat(x) FROM Result;
>WITH Result(x) AS (SELECT DISTINCT Value FROM Test ORDER BY Value DESC)
>SELECT group_concat(x) FROM Result;
>leads to the results Alpha,Beta, respectively Beta,Alpha.
>Is this a coincidence or is this guaranteed to work?
>Are there any other solutions / possibilities?
group_concat builds a "group" by concatenating the values sent to it in the
order they are sent. If you do not know this order then for all intents and
purposes the order is "undefined" because it is defined as the order in which
the query planner decides to visit the rows forming the group. SQLite3
believes that all aggregate functions are commutative and that the order in
which rows are fed into them is immaterial to the result and there (presently)
is no way to specify that this is not the case.
So in the rare case where the aggregate is not commutative and you depend on
the presentation order, then you must specify it. The only built-in aggregate
that is not commutative is the group_concat function. If you were to write
another non-commutative aggregate function, lets say SHA1(...), that computed
the SHA1 hash of the values fed into it, you would also have to control the
presentation order or the result would be "undefined".
select group_concat(value) from (select distinct value from test order by value
desc); will do that. (rephrasing as a CTE makes no difference)
This works because the query as phrased cannot be flattened since the outer
query contains an aggregate and the inner query contains an order by.
Moving the distinct does not alter the fact that the query cannot be flattened.
select group_concat(distinct value) from (select value from test order by value
desc);
Whether the query planner will always not flatten a query where the outer query
contains an aggregate and the inner query contains an order by is something on
which I cannot comment other than to say that is does not flatten such a query
up to now.
--
The fact that there's a Highway to Hell but only a Stairway to Heaven says a
lot about anticipated traffic volume.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Write complete pages to journal in VFS?
I've recently changed the underlaying file system driver such that it
allocates one of 2 sizes of blocks (4096 or 256), based on how much is
written to the file at a time; so a write < 2048 will allocate 256 byte
blocks instead; it's not a HUGE performance hit, but I am trying to keep it
more on the efficient side (I could maybe provide an IOCTL sort of op that
forces files to only use large blocks)
I was wondering if there was a way I could make SQLite always write full
pages to the VFS (actually I found the atomic4k option after this). It has
(xSectorSize)
int xSectorSize(sqlite3_file*file)
{
return 4096;
}
And I found SQLITE_IOCAP_ATOMIC4K just now,and updated to use that...
int xDeviceCharacteristics(sqlite3_file*file)
{
SQLITE_IOCAP_ATOMIC4K|SQLITE_IOCAP_SAFE_APPEND|SQLITE_IOCAP_UNDELETABLE_WHEN_OPEN|SQLITE_IOCAP_POWERSAFE_OVERWRITE;
}
But, the -journal file doesn't respect that setting...
Write keyMaster.db-journal 512 at 0
Write keyMaster.db 4096 at 0
Write keyMaster.db 4096 at 4096
Write keyMaster.db-journal 512 at 0
Write keyMaster.db-journal 4 at 512
Write keyMaster.db-journal 4096 at 516
Write keyMaster.db-journal 4 at 4612
Write keyMaster.db 4096 at 0
Write keyMaster.db 4096 at 8192
Write keyMaster.db-journal 512 at 0
Write keyMaster.db-journal 4 at 512
Write keyMaster.db-journal 4096 at 516
Write keyMaster.db-journal 4 at 4612
Write keyMaster.db 4096 at 0
Write keyMaster.db 4096 at 12288
Write keyMaster.db-journal 512 at 0
Write keyMaster.db-journal 4 at 512
Write keyMaster.db-journal 4096 at 516
Write keyMaster.db-journal 4 at 4612
Write keyMaster.db 4096 at 0
Write keyMaster.db 4096 at 16384
Write keyMaster.db 4096 at 20480
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] How to enforce a specific order of group_concat?
Assume I create the following table:
CREATE TABLE Test (ID INTEGER PRIMARY KEY, Value TEXT);
INSERT INTO Test (Value) VALUES('Alpha');
INSERT INTO Test (Value) VALUES('Beta');
INSERT INTO Test (Value) VALUES('Beta');
INSERT INTO Test (Value) VALUES('Alpha');
According to the documentation of group_concat the order is undefined, indeed:
SELECT group_concat(DISTINCT Value) FROM Test ORDER BY Value ASC;
SELECT group_concat(DISTINCT Value) FROM Test ORDER BY Value DESC;
Both queries result in Alpha,Beta.
Changing the queries to
WITH Result(x) AS (SELECT DISTINCT Value FROM Test ORDER BY Value ASC) SELECT
group_concat(x) FROM Result;
WITH Result(x) AS (SELECT DISTINCT Value FROM Test ORDER BY Value DESC) SELECT
group_concat(x) FROM Result;
leads to the results Alpha,Beta, respectively Beta,Alpha.
Is this a coincidence or is this guaranteed to work?
Are there any other solutions / possibilities?
Regards,
Hardy
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
