Re: [firebird-support] SELECT with CTE and LIST: Duplicates with DISTINCT and incorrect ordering in the main select

[Michal Kurczabinski michk...@gmail.com [firebird-support]]

In future beware of something like this:

select cast('text' as blob)a from rdb$database
union
select cast('text' as blob)a from rdb$database


list() returns blob...

http://tracker.firebirdsql.org/browse/CORE-1345



-- 
regards,
Michał Kurczabiński

Re: [firebird-support] SELECT with CTE and LIST: Duplicates with DISTINCT and incorrect ordering in the main select

[Svein Erling Tysvær setys...@gmail.com [firebird-support]]

Thanks Michał, I was certain I tried that yesterday, but now I notice that
this actually works, so I must have CASTed the wrong place(s).

Thanks again,
Set

Re: [firebird-support] How to get the the tables and the columns of a Foreign Key?

['Walter R. Ojeda Valiente' sistemas2000profesio...@gmail.com [firebird-support]]

Thank you very much SET.

Yes, it will be useful. No exactly what I was looking for but good enough
for know the names of the tables and the columns involved.

Greetings.

Walter.


On Tue, May 30, 2017 at 3:38 AM, Svein Erling Tysvær setys...@gmail.com
[firebird-support]  wrote:

>
>
> Hopefully this can get you started (though since your key consists of two
> fields, I would expect it to return four rows, not one row with two tables
> and four fields):
>
> select iChild.rdb$Relation_name, isChild.rdb$field_name,
> iMain.rdb$relation_name, isMain.rdb$field_name
> from rdb$indices iChild
> join rdb$index_segments isChild on iChild.rdb$index_name =
> isChild.rdb$index_name
> join rdb$indices iMain on iChild.rdb$Foreign_key = iMain.rdb$index_name
> join rdb$index_segments isMain on iMain.rdb$index_name =
> isMain.rdb$index_name
> where iChild.rdb$index_name = 'FK_ADHERENTES'
>
> HTH,
> Set
>
> 2017-05-30 7:10 GMT+02:00 'Walter R. Ojeda Valiente'
> sistemas2000profesio...@gmail.com [firebird-support] <
> firebird-support@yahoogroups.com>:
>
>>
>>
>> Hello everybody
>>
>> I had defined a Foreign Key as:
>>
>> ALTER TABLE ADHERENTES
>> ADD CONSTRAINT FK_ADHERENTES
>> FOREIGN KEY (ADH_SERVID, ADH_IDECAB)
>> REFERENCES CLIENTES(CLI_SERVID, CLI_IDENTI)
>> ON DELETE CASCADE
>> ON UPDATE CASCADE;
>>
>> Of course, that work very well.
>>
>> However, after watching the system tables I can not find a simple way to
>> build a SELECT what can give me the name of the tables and the columns
>> involved. Something as:
>>
>> SELECT ... something
>>
>> Result:
>> ADHERENTES, ADH_SERVID, ADH_IDECAB, CLIENTES, CLI_SERVID, CLI_IDENTI
>>
>> Can somebody tell me how to get that result?
>>
>> Thank you very much in advance.
>>
>> Greetings.
>>
>> Walter.
>>
>>
>>
> 
>

Re: [firebird-support] SELECT with CTE and LIST: Duplicates with DISTINCT and incorrect ordering in the main select

[Michal Kurczabinski michk...@gmail.com [firebird-support]]

First thought:
you should cast as varchar list expr as
cast (list(distinct 'I shouldn''t duplicate') as varchar(100)),
cast(list(distinct substring('Hello' from 1 for 5)) as varchar(100))
in each occurence of list



-- 
regards,
Michał Kurczabiński

[firebird-support] SELECT with CTE and LIST: Duplicates with DISTINCT and incorrect ordering in the main select

[Svein Erling Tysvær setys...@gmail.com [firebird-support]]

Yesterday I tried to select using a CTE containing LIST(DISTINCT), and then
the main (outer) select tried to group by this LIST. The result ended up
with incorrect ordering as well as duplicates. Trying to make a
reproducible test case, I didn’t get the duplicates, but rather the list
contained duplicate values. I did a search to see if others have observed
the same thing, but didn’t find anything (though I’m far better with
Firebirds SELECT statement than internet searching, so this could well be
just me).



Here’s the query (“reproducible” test case):



with tmp(id, greeting) as

(select list(distinct 'I should go last'), list(distinct substring('Hi'
from 1 for 5))

from rdb$database

left join rdb$database r2 on (1=1)

union

select list(distinct 'I shouldn''t duplicate'), list(distinct
substring('Hello' from 1 for 5))

from rdb$database

left join rdb$database r2 on (1=1)

union

select list(distinct 'I shouldn''t duplicate'), list(distinct
substring('Hello' from 1 for 5))

from rdb$database

left join rdb$database r2 on (1=1))

select greeting, list(distinct id), count(distinct id)

from tmp

group by 1

order by Greeting



Expected result:

Hello I shouldn’t duplicate  1

HiI should go last 1



Actual result (on Firebird 2.5.4, probably with ISO8859_1 as the character
set and may actual query may have had NO_NO as the collation):

HiI should go last 1

Hello I shouldn’t duplicate,I shouldn’t duplicate  2



How the wrong result would have been if I had been able to completely make
a copy of my original error (random sorting and lots of duplicates):

HiI should go last 1

Hello I shouldn’t duplicate  1

Hello I shouldn’t duplicate  1



I wrote the query to answer one particular question, so I could get a
similar result to what I wanted by first running only the content of the
CTE and then using PivotTable in Excel, but I was very surprised by bumping
into what I consider being two separate errors inside one query (duplicates
and ordering), or maybe even 2,5 errors since my original query returned
duplicate rows rather than duplicates within LIST(DISTINCT…).



Does anyone have similar experiences or an explanation?
Set