date:20150110

Re: [sqlite] aggregate functions with DISTINCT

2015-01-10 Thread Staffan Tylen

Just to add: my questions were - why this limitation with DISTINCT, and can
it be changed at least for GROUP_CONCAT?

On Sun, Jan 11, 2015 at 1:23 AM, Staffan Tylen 
wrote:

> Well, the SELECT is actually over 400 lines long so 'visualizing' it
> wouldn't be very easy :) But it's along these lines:
>
> SELECT X FROM
> (SELECT 'ABC'||
>  IFNULL(' PARM('||GROUP_CONCAT(COL1,' ')||')'),' ')||
> etc
> FROM T1
> LEFT JOIN T2
> LEFT JOIN T3
> etc etc (lots of joins)
> UNION
> SELECT 'DEF'||
> etc
> UNION
> etc
> )
>
> So in this case COL1 might contain duplicates that need to be filtered. 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.
>
> Staffan
>
>
>
>
>
> On Sun, Jan 11, 2015 at 1:00 AM, John McKown  > wrote:
>
>> On Sat, Jan 10, 2015 at 5:56 PM, Staffan Tylen 
>> wrote:
>>
>> > Thanks Richard but unfortunately I cannot use DISTINCT in that way,
>> since
>> > it affects all the selected columns and they are MANY.
>> >
>> > Staffan
>> >
>>
>> I am having trouble visualizing what your actual SELECT is. Would you
>> mind
>> posting it?
>>
>>
>>
>> >
>> >
>> > On Sun, Jan 11, 2015 at 12:43 AM, Richard Hipp  wrote:
>> >
>> > > On 1/10/15, Staffan Tylen  wrote:
>> > > > I'm in the situation where I need to use GROUP_CONCAT and filter out
>> > > > duplicates at the same time. And the default comma separator in
>> > > > GROUP_CONCAT needs to be replaced by a space. I've tried to use
>> > function
>> > > > REPLACE to get rid of the comma but only to realise that the data
>> being
>> > > > concatenated also might contain one or more commas.
>> > > >
>> > >
>> > > SELECT group_concat(x,'+') FROM (SELECT DISTINCT a+b AS x FROM tab
>> ORDER
>> > > BY 1);
>> > >
>> > >
>> > > --
>> > > 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-users mailing list
>> > sqlite-users@sqlite.org
>> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>> >
>>
>>
>>
>> --
>> 
>> 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
>>
>
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] aggregate functions with DISTINCT

2015-01-10 Thread Staffan Tylen

Well, the SELECT is actually over 400 lines long so 'visualizing' it
wouldn't be very easy :) But it's along these lines:

SELECT X FROM
(SELECT 'ABC'||
 IFNULL(' PARM('||GROUP_CONCAT(COL1,' ')||')'),' ')||
etc
FROM T1
LEFT JOIN T2
LEFT JOIN T3
etc etc (lots of joins)
UNION
SELECT 'DEF'||
etc
UNION
etc
)

So in this case COL1 might contain duplicates that need to be filtered. 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.

Staffan





On Sun, Jan 11, 2015 at 1:00 AM, John McKown 
wrote:

> On Sat, Jan 10, 2015 at 5:56 PM, Staffan Tylen 
> wrote:
>
> > Thanks Richard but unfortunately I cannot use DISTINCT in that way, since
> > it affects all the selected columns and they are MANY.
> >
> > Staffan
> >
>
> I am having trouble visualizing what your actual SELECT is. Would you mind
> posting it?
>
>
>
> >
> >
> > On Sun, Jan 11, 2015 at 12:43 AM, Richard Hipp  wrote:
> >
> > > On 1/10/15, Staffan Tylen  wrote:
> > > > I'm in the situation where I need to use GROUP_CONCAT and filter out
> > > > duplicates at the same time. And the default comma separator in
> > > > GROUP_CONCAT needs to be replaced by a space. I've tried to use
> > function
> > > > REPLACE to get rid of the comma but only to realise that the data
> being
> > > > concatenated also might contain one or more commas.
> > > >
> > >
> > > SELECT group_concat(x,'+') FROM (SELECT DISTINCT a+b AS x FROM tab
> ORDER
> > > BY 1);
> > >
> > >
> > > --
> > > 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-users mailing list
> > sqlite-users@sqlite.org
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >
>
>
>
> --
> 
> 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
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] aggregate functions with DISTINCT

2015-01-10 Thread John McKown

On Sat, Jan 10, 2015 at 5:56 PM, Staffan Tylen 
wrote:

> Thanks Richard but unfortunately I cannot use DISTINCT in that way, since
> it affects all the selected columns and they are MANY.
>
> Staffan
>

I am having trouble visualizing what your actual SELECT is. Would you mind
posting it?

>
>
> On Sun, Jan 11, 2015 at 12:43 AM, Richard Hipp  wrote:
>
> > On 1/10/15, Staffan Tylen  wrote:
> > > I'm in the situation where I need to use GROUP_CONCAT and filter out
> > > duplicates at the same time. And the default comma separator in
> > > GROUP_CONCAT needs to be replaced by a space. I've tried to use
> function
> > > REPLACE to get rid of the comma but only to realise that the data being
> > > concatenated also might contain one or more commas.
> > >
> >
> > SELECT group_concat(x,'+') FROM (SELECT DISTINCT a+b AS x FROM tab ORDER
> > BY 1);
> >
> >
> > --
> > 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-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>

-- 

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] aggregate functions with DISTINCT

2015-01-10 Thread Staffan Tylen

Hmm, maybe I'm wrong there, it would remove duplicates of the entire
collection combined, wouldn't it? Yes, it might work.

Staffan


On Sun, Jan 11, 2015 at 12:56 AM, Staffan Tylen 
wrote:

> Thanks Richard but unfortunately I cannot use DISTINCT in that way, since
> it affects all the selected columns and they are MANY.
>
> Staffan
>
>
> On Sun, Jan 11, 2015 at 12:43 AM, Richard Hipp  wrote:
>
>> On 1/10/15, Staffan Tylen  wrote:
>> > I'm in the situation where I need to use GROUP_CONCAT and filter out
>> > duplicates at the same time. And the default comma separator in
>> > GROUP_CONCAT needs to be replaced by a space. I've tried to use function
>> > REPLACE to get rid of the comma but only to realise that the data being
>> > concatenated also might contain one or more commas.
>> >
>>
>> SELECT group_concat(x,'+') FROM (SELECT DISTINCT a+b AS x FROM tab ORDER
>> BY 1);
>>
>>
>> --
>> 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-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] aggregate functions with DISTINCT

2015-01-10 Thread Staffan Tylen

Thanks Richard but unfortunately I cannot use DISTINCT in that way, since
it affects all the selected columns and they are MANY.

Staffan


On Sun, Jan 11, 2015 at 12:43 AM, Richard Hipp  wrote:

> On 1/10/15, Staffan Tylen  wrote:
> > I'm in the situation where I need to use GROUP_CONCAT and filter out
> > duplicates at the same time. And the default comma separator in
> > GROUP_CONCAT needs to be replaced by a space. I've tried to use function
> > REPLACE to get rid of the comma but only to realise that the data being
> > concatenated also might contain one or more commas.
> >
>
> SELECT group_concat(x,'+') FROM (SELECT DISTINCT a+b AS x FROM tab ORDER
> BY 1);
>
>
> --
> 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-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] aggregate functions with DISTINCT

2015-01-10 Thread Richard Hipp

On 1/10/15, Staffan Tylen  wrote:
> I'm in the situation where I need to use GROUP_CONCAT and filter out
> duplicates at the same time. And the default comma separator in
> GROUP_CONCAT needs to be replaced by a space. I've tried to use function
> REPLACE to get rid of the comma but only to realise that the data being
> concatenated also might contain one or more commas.
>

SELECT group_concat(x,'+') FROM (SELECT DISTINCT a+b AS x FROM tab ORDER BY 1);


-- 
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] aggregate functions with DISTINCT

2015-01-10 Thread Staffan Tylen

The documentation for aggregate functions states that "In any aggregate
function that takes a single argument, that argument can be preceded by the
keyword DISTINCT". My questions are: Why is DISTINCT only allowed with a
single argument in a function like GROUP_CONCAT? Can that limitation be
removed?

I'm in the situation where I need to use GROUP_CONCAT and filter out
duplicates at the same time. And the default comma separator in
GROUP_CONCAT needs to be replaced by a space. I've tried to use function
REPLACE to get rid of the comma but only to realise that the data being
concatenated also might contain one or more commas.

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

Re: [sqlite] Win 64 SQLLite 90 - badimageformatexception

2015-01-10 Thread Joe Mistachkin


Taylor, Jill (RBI-US) wrote:
>
> My application calls a DLL that uses the sqllite dll to open a database.
However,
> when I am debugging I get the BADImageFormatException error "An attempt
was made
> to load a program with an incorrect format error"
> 

These are typically caused by a 32-bit versus 64-bit mismatch between the
process and
the DLL to be loaded.

Please see the following pages for information on how to address these types
of issues:

'Why do I get a BadImageFormatException (for "sqlite3.dll" or
"SQLite.Interop.dll") when trying to run my application?'

https://system.data.sqlite.org/index.html/doc/trunk/www/faq.wiki#q12

'Using Native Library Pre-Loading'


https://system.data.sqlite.org/index.html/doc/trunk/www/downloads.wiki#nativ
eLibraryPreLoading
 
--
Joe Mistachkin

___
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

2015-01-10 Thread Richard Hipp

On 1/9/15, RSmith  wrote:
> The pre-compiled and supplied DLL (sqlite3.dll) seem to be missing an entry
> point for "sqlite3_enable_load_extension" - I do not see
> any mention in the update text about altering or removing this feature so I
> am assuming this might be a compile-time omission?
>

Yes, it was a compile-time omission.  I have uploaded a new DLL that
includes the loadable extension interface.

-- 
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] Win 64 SQLLite 90 - badimageformatexception

2015-01-10 Thread Taylor, Jill (RBI-US)


Hi
I have application compiled under Visual Studio 2012 on a Window 7 64 bit 
machine.  I am trying to use the   sqllite dll .90.

My application calls a DLL that uses the sqllite dll to open a database.   
However, when I am debugging I get the BADImageFormatException error "An 
attempt was made to load a program with an incorrect format error"

This worked fine with version 66 but we are trying to upgrade.

The visual studio exe and dll are both set to target x64.
The error occures when I call "new sqliteHandler(sqlitecnx)" to open a database.

When I first tried, the application complained about missing interop dll.  I 
copied the sqllite.inerop.dll in both bin\debug directories.

Now I get the above error about BADImageFormatException error

Below are some screen shot.

Any help would be appreciated
Bye
Jill






DISCLAIMER
This message is intended only for the use of the person(s) ("Intended 
Recipient") to whom it is addressed. It may contain information, which is 
privileged and confidential. Accordingly any dissemination, distribution, 
copying or other use of this message or any of its content by any person other 
than the Intended Recipient may constitute a breach of civil or criminal law 
and is strictly prohibited. If you are not the Intended Recipient, please 
contact the sender as soon as possible.
Reed Business Information Limited. Registered Office: Quadrant House, The 
Quadrant, Sutton, Surrey, SM2 5AS, UK. 
Registered in England under Company No. 151537

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

Re: [sqlite] Segfault with Evolution and patched SQLite 3.8.7.4

2015-01-10 Thread Paul Menzel

Am Freitag, den 09.01.2015, 21:04 -0500 schrieb Richard Hipp:
> On 1/9/15, Paul Menzel wrote:
> > Am Dienstag, den 30.12.2014, 16:15 +0100 schrieb Paul Menzel:
> >
> > With still around 1.3 GB free on the partition mounted to `/var/`,
> > Evolution crashed with the f received the following segmentation fault
> > today.
> 
> Which build of SQLite are you using.  What is SQLITE_SOURCE_ID?

I downloaded the source of Debian package for SQLite 3.8.7.4-1 and
applied the patch from [2] (also attached).

$ /usr/bin/sqlite3 --version
3.8.7.4 2014-12-09 01:34:36
f66f7a17b78ba617acde90fc810107f34f1a1f2e

> Also, we have some new "sqlite3.c" and "sqlite3.h" files for the
> upcoming 3.8.8 release.  Can I encourage you to try them out.

I’ll try to test the 3.8.8 files. Unfortunately, I have not found a way
to reproduce the issue.

> > 0xb3f9af51 in sqlite3Strlen30 (z=0x18  > at address 0x18>) at sqlite3.c:22902
> >
> >
> > Thread 53 (Thread 0xa7e04b40 (LWP 3576)):
> > #0  0xb3f9af51 in sqlite3Strlen30 (z=0x18  > memory at address 0x18>) at sqlite3.c:22902
> 
> sqlite3Strlen30() is called with an invalid string pointer,
> apparently.  The sqlite3Strlen30() function is just a strlen()
> implementation that returns int instead of size_t. Stack frames 0
> through 5 look fine, except for the invalid string pointer, of coruse.
> 
> > #5  0xb3f9ce21 in unixSync (id=0xacbe7898, flags=2) at 
> > sqlite3.c:28396
> > dirfd = 668585276
> > rc = 
> > pFile = 0xacbe7898
> > isDataOnly = 0
> > isFullsync = 0
> 
> The unixSync routine above calls frame 4 from
> (https://www.sqlite.org/src/artifact/949cdedc74dbf3c1?ln=3589).
> Apparently, pFile->zPath is an invalid pointer.
> 
> 
> > #6  0xb7ad33d6 in call_old_file_Sync (flags=, 
> > cFile=) at camel-db.c:66
> 
> The pFile object with the invalid zPath field is a parameter to
> unixSync(), and hence comes from call_old_file_Sync(), which is not a
> part of the SQLite source tree.  I don't have the sources to
> camel-db.c so I cannot trace this any further.

You can view the source at [3].

static gint
call_old_file_Sync (CamelSqlite3File *cFile,
gint flags)
{
g_return_val_if_fail (old_vfs != NULL, SQLITE_ERROR);
g_return_val_if_fail (cFile != NULL, SQLITE_ERROR);

g_return_val_if_fail (cFile->old_vfs_file->pMethods != NULL, 
SQLITE_ERROR);
return cFile->old_vfs_file->pMethods->xSync 
(cFile->old_vfs_file, flags);
}

> My guess (based on the name of the function) is that camel-db.c is
> trying to "sync" an sqlite3_file object that has been previously
> destroyed.

That sounds reasonable. I created a ticket in GNOME’s bug tracker
Bugzilla and it was assigned the ID #742688 [4]. I added you to the CC
list. Hopefully, you do not mind.

> This appears to be completely unrelated to the previous issue.  The
> previous issue was that a file was not being extended correctly
> because of a lack of disk space, so that a memcpy() into a mmap() of
> that file segfaulted.  That does not appear to be what is happening
> here, unless I'm missing something.

[…]

As always thank you very much for the quick and detailed reply!


Thanks,

Paul


> >> [1] https://packages.debian.org/corekeeper
> >> [2] 
> >> https://www.sqlite.org/src/info/776648412c30dce206f1024ff849c2cb025bb006
[3] 
http://sources.debian.net/src/evolution-data-server/3.12.9~git20141128.5242b0-2/camel/camel-db.c/#L66
[4] https://bugzilla.gnome.org/show_bug.cgi?id=742688


signature.asc
Description: This is a digitally signed message part
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] aggregate functions with DISTINCT

Re: [sqlite] aggregate functions with DISTINCT

Re: [sqlite] aggregate functions with DISTINCT

Re: [sqlite] aggregate functions with DISTINCT

Re: [sqlite] aggregate functions with DISTINCT

Re: [sqlite] aggregate functions with DISTINCT

[sqlite] aggregate functions with DISTINCT

Re: [sqlite] Win 64 SQLLite 90 - badimageformatexception

Re: [sqlite] SQLite 3.8.8 expected in January

[sqlite] Win 64 SQLLite 90 - badimageformatexception

Re: [sqlite] Segfault with Evolution and patched SQLite 3.8.7.4

11 matches

Site Navigation

Mail list logo

Footer information