[firebird-support] Re: Firebird performance vs PostgreSQL

2017-09-28 Thread Dmitry Yemanov dim...@users.sourceforge.net [firebird-support] 
29.09.2017 01:08, Ertan Küçükoğlu wrote:

Answers can be found in the plans:

 > PLAN JOIN (RAPOR_EK ORDER rapor_ek_pkey, SORT (DT M NATURAL))

This join plan is terrible for FB. Are KAYNAGAGORE and SEBEBEGORE 
indexed? They seem not.

> Merge Left Join  (cost=6600.19..6610.98 rows=560 width=340)

FB cannot perform merge- or hash-join for outer joins (yet). When 
important indices are missing, this becomes a problem.


Dmitry

[firebird-support] Re: Firebird performance vs PostgreSQL

2017-09-28 Thread zilez2...@yahoo.com [firebird-support] 
Hi, 

database structures at:

https://paste.ee/p/qjTwz https://paste.ee/p/qjTwz

Aprox size of tables

Artikli = 227.000 rows
faktur = 85.000 rows
izlaz = 292.000 rows
Partneri = 13400 rows
Kurs = 180 rows
Vrste = 560 rows
Porezi = 6 rows
Faktype = 30 rows
Magacin = 60 rows

Query

SELECT
V_TXT1,
sum(I_KOL) AS KOMADA,
sum(I_NCENA*I_KOL/KURS_EURA) AS NABAVNA_VREDNOST ,
Sum(I_CENA*I_KOL*cast((1-(F_KASP/100.00)) AS DOUBLE 
PRECISION)*CAST(((100-I_RAB)/100) AS DOUBLE PRECISION)/KURS_EURA) AS VRED_MP
FROM glavni_upit
WHERE G_TIP='V' AND (F_DAT>=:POC AND F_DAT<=:KRAJ) AND I_CENA>0
GROUP BY V_TXT1

Firebird 2.5.6 execution time = 16 seconds
PostgreSQL latest 9 builds = 2 seconds

on the same machine

RE: [firebird-support] Firebird performance vs PostgreSQL

2017-09-28 Thread Ertan Küçükoğlu ertan.kucuko...@1nar.com.tr [firebird-support] 
> From: mailto:firebird-support@yahoogroups.com 
> [mailto:firebird-support@yahoogroups.com] 
> Sent: Thursday, September 28, 2017 7:35 PM
> To: mailto:firebird-support@yahoogroups.com
> Subject: Re: [firebird-support] Firebird performance vs PostgreSQL
>
> 
> Hi,
>
> without any sample you talking about nothing..
> Show some sample which can be reproduced.
> Without this i can say same Firebird is faster then Oracle and what someone 
> can say about this – nothing 
> 
> regards,
> Karol Bieniaszewski

Hello again,

As forum is not allowing attachments. Necessary files can be found in below 
link:
https://paste.ee/p/1gBQl

RE: [firebird-support] Firebird performance vs PostgreSQL

2017-09-28 Thread Ertan Küçükoğlu ertan.kucuko...@1nar.com.tr [firebird-support] 
> From: firebird-support@yahoogroups.com 
> [mailto:firebird-support@yahoogroups.com] 
> Sent: Thursday, September 28, 2017 7:35 PM
> To: firebird-support@yahoogroups.com
> Subject: Re: [firebird-support] Firebird performance vs PostgreSQL
>
>  
> Hi,
>
> without any sample you talking about nothing..
> Show some sample which can be reproduced.
> Without this i can say same Firebird is faster then Oracle and what someone 
> can say about this – nothing 
> 
> regards,
> Karol Bieniaszewski

Hello,

I am only providing details for above question. Not trying to say this is 
better or worse or anything.

- SQL statements to create necessary database tables can be found in attached 
*.SQL files.
- Followig link includes "insert into" statements which can be used for loading 
necessary data (around 100.000 records) on both FirebirdSQL and PostgreSQL 
databases.
http://s5.dosya.tc/server5/3dard7/insert_sql_statements.rar.html
- FirebirdSQL database should be created using WIN1254 character set.
- PostgreSQL database should be created as follows
For Linux:
CREATE DATABASE pgtest ENCODING 'UTF-8' LC_COLLATE 'tr_TR.UTF-8' TEMPLATE 
template0;
For Windows: 
CREATE DATABASE pgtest ENCODING 'UTF-8' LC_COLLATE 'tr-TR.UTF-8' TEMPLATE 
template0;
or
CREATE DATABASE pgtest ENCODING 'UTF8' LC_COLLATE 'Turkish_Turkey.1254' 
LC_CTYPE = 'Turkish_Turkey.1254' TEMPLATE template0;

- Query used on both database systems is as follows:
SELECT 
  RAPOR_EK.KAYNAK, 
  RAPOR_EK.SEBEP, 
  COALESCE(DT.IAIK_OG, 0) AS IAIK_OG,
  COALESCE(DT.IAIK_AG, 0) AS IAIK_AG,
  COALESCE(DT.IAIK_TOPLAM, 0) AS IAIK_TOPLAM,
  COALESCE(DT.IADK_OG, 0) AS IADK_OG,
  COALESCE(DT.IADK_AG, 0) AS IADK_AG,
  COALESCE(DT.IADK_TOPLAM, 0) AS IADK_TOPLAM,
  COALESCE(DT.GNLTOPLAM, 0) AS GNLTOPLAM
FROM RAPOR_EK
LEFT JOIN (
  SELECT 
M.KAYNAGAGORE AS KAYNAK, 
M.SEBEBEGORE AS SEBEP, 
(sum(M.ES_IIOG)/(select sum(I.IIOG) from sabitler I where kaynak = 
'CİHANBEYLİ')*60) as IAIK_OG,
(sum(M.ES_IIAG)/(select sum(I.IIAG) from sabitler I where kaynak = 
'CİHANBEYLİ')*60) as IAIK_AG,
((sum(M.ES_IIOG) + sum(M.ES_IIAG)) / ((select sum(I.IIOG) from sabitler I 
where kaynak = 'CİHANBEYLİ')  + (select sum(I.IIAG) from sabitler I where 
kaynak = 'CİHANBEYLİ'))*60) AS IAIK_TOPLAM,
(sum(M.ES_IDOG)/(select sum(I.IDOG) from sabitler I where kaynak = 
'CİHANBEYLİ')*60) as IADK_OG,
(sum(M.ES_IDAG)/(select sum(I.IDAG) from sabitler I where kaynak = 
'CİHANBEYLİ')*60) as IADK_AG,
((sum(M.ES_IDOG) + sum(M.ES_IDAG)) / ((select sum(I.IDOG) from sabitler I 
where kaynak = 'CİHANBEYLİ')  + (select sum(I.IDAG) from sabitler I where 
kaynak = 'CİHANBEYLİ'))*60) AS IADK_TOPLAM,
((sum(M.ES_IIOG) + sum(M.ES_IIAG) + (sum(M.ES_IDOG) + sum(M.ES_IDAG))) / 
((select sum(I.toplam) from sabitler I where kaynak = 'CİHANBEYLİ')*60)) AS 
GNLTOPLAM 
  FROM veri M
  WHERE 
M.ILCE = 'CİHANBEYLİ'
AND M.BILDIRIMEGORE = 'Bildirimsiz'
AND M.SUREYEGORE = 'Uzun'
AND (M.baslangic >= '2015-01-01' AND bitis <= '2015-01-31 23:59:59.999')
  GROUP BY 
M.KAYNAGAGORE,
M.SEBEBEGORE
) DT USING (KAYNAK, SEBEP)
ORDER BY KAYNAK, SEBEP;

- Query plan for both databases can be found in attached *.TXT files.

- My configuration i7-4720HQ cpu, 8GB RAM, Windows 10 64bit, 512GB Sandisk 
Extreme Pro SSD 6GB capability on 6GB connection.
- PostgreSQL version 9.6.4 64bit (installed using Enterprise DB binaries)
- FirebirdSQL version 3.0.1.32609 64bit (official installation) 
- Both database servers have default configuration files. Nothing changed, or 
tweaked.
- I read following figures for above SQL statement execution on a freshly 
created database, freshly loaded data
=> FirebirdSQL using flamerobin.exe to execute (I failed to find a way to 
execute an SQL file with stats on using isql.exe): 1.505 seconds after computer 
reboot, 1.379 seconds for 2nd, 1.381 seconds for 3rd.
=> PostgreSQL using psql.exe to execute: 0.252 seconds after computer reboot, 
0.029 seconds on 2nd, 0.024 seconds on 3rd

You may have additional questions, I try my best to answer within my knowledge.

Regards,
Ertan Küçükoğlu


  --

Field #01: RAPOR_EK.KAYNAK Alias:KAYNAK Type:STRING(20)
Field #02: RAPOR_EK.SEBEP Alias:SEBEP Type:STRING(20)
Field #03: .COALESCE Alias:IAIK_OG Type:NUMERIC(18,2)
Field #04: .COALESCE Alias:IAIK_AG Type:NUMERIC(18,2)
Field #05: .COALESCE Alias:IAIK_TOPLAM Type:NUMERIC(18,2)
Field #06: .COALESCE Alias:IADK_OG Type:NUMERIC(18,2)
Field #07: .COALESCE Alias:IADK_AG Type:NUMERIC(18,2)
Field #08: .COALESCE Alias:IADK_TOPLAM Type:NUMERIC(18,2)
Field #09: .COALESCE Alias:GNLTOPLAM Type:NUMERIC(18,2)
PLAN (DT I INDEX (RDB$PRIMARY2))
PLAN (DT I INDEX (RDB$PRIMARY2))
PLAN (DT I INDEX (RDB$PRIMARY2))
PLAN (DT I INDEX (RDB$PRIMARY2))
PLAN (DT I INDEX (RDB$PRIMARY2))
PLAN (DT I INDEX (RDB$PRIMARY2))
PLAN (DT I INDEX (RDB$PRIMARY2))
PLAN (DT I INDEX (RDB$PRIMARY2))
PLAN (DT I INDEX (RDB$PRIMARY2))
PLAN JOIN (RAPOR_EK ORDER rapor_ek_pkey, SORT (DT M NATURAL))
  --

Merge

Re: [firebird-support] Find duplicate multi-row entries

2017-09-28 Thread setysvar setys...@gmail.com [firebird-support] 
Sorry again, Daniel, I had to get to a place where I could test things 
before replying. My problem was that what I'd done before was to try to 
find identical sets, not trying to find unique sets, and that made me 
mess up the logic. I hope this query will get you what you want (and 
this time I've tested before replying):


with tmp(NODE, ROUTE_UPDATED) as
(select distinct r.NODE, rh.ROUTE_UPDATED
 from routes r
 left join route_history rh on r.NODE = rh.NODE),
new_routes(NODE) as
(select distinct t.NODE
 from tmp t
 where exists(select *
  from routes r
  left join route_history rh on r.NODE = rh.NODE
and r.ROUTE_INDEX = rh.ROUTE_INDEX
and r.LINK_NODE = rh.LINK_NODE
and r.QUALITY is not distinct 
from rh.QUALITY
and t.ROUTE_UPDATED = 
rh.ROUTE_UPDATED

  where t.NODE = r.NODE
and rh.NODE is null)
or exists(select *
  from route_history rh
  left join routes r on r.NODE = rh.NODE
and r.ROUTE_INDEX = rh.ROUTE_INDEX
and r.LINK_NODE = rh.LINK_NODE
and r.QUALITY is not distinct from 
rh.QUALITY

  where t.NODE = rh.NODE
and t.ROUTE_UPDATED = rh.ROUTE_UPDATED
and r.NODE is null))
select r.NODE, r.ROUTE_INDEX, r.LINK_NODE, r.QUALITY
from new_routes nr
join routes r on nr.NODE = r.NODE

HTH,
Set

Den 28.09.2017 11:00, skrev 'Daniel Miller' dmil...@amfes.com 
[firebird-support]:



This appears no different than a simple:

select * from routes

So...not there yet.  I think what I'm fighting is the lack of pivot or 
crosstab functionality - and I may have no choice (though it offends 
me deeply) but to setup my archive table for this comparison.  Either 
via the hated structure of column names ROUTE1, ROUTE2, ROUTE8...or 
the only slightly less offensive VARCHAR concatenation.


A concatenated LIST seems like it would be a elegant solution - and 
while operating over the whole table is time-consuming a single node 
isn't too bad.  And if I can get the history filtered and old 
duplicate records deleted it would be fine.  But the inconsistency is 
killing me.


There MUST be a Firebird-friendly, normalized, compact storage 
structure that will work.  I'm just not seeing it.

--
Daniel

On 9/28/2017 1:19:18 AM, "Svein Erling Tysvær setys...@gmail.com 
[firebird-support]" > wrote:


Sorry again, Daniel, fourth query required (I forgot that things may 
vary depending on time, this is something I've never done before):


with tmp(NODE, ROUTE_UPDATED) as
(select distinct rh.NODE, rh.ROUTE_UPDATED
 from route_history rh
 join routes r on rh.NODE = r.NODE), //*This join is only for 
performance reasons in case ROUTE_HISTORY contains a lot more records 
than ROUTES and may be removed*/

new_routes(NODE) as
(select distinct t.NODE
 from tmp t
 where not exists(select *
  from routes r
  left join route_history rh on r.NODE = rh.NODE
  and r.ROUTE_INDEX = rh.ROUTE_INDEX
  and r.LINK_NODE = rh.LINK_NODE
  and r.QUALITY is not distinct from rh.QUALITY
  and t.ROUTE_UPDATED = rh.ROUTE_UPDATED
  where t.NODE = r.NODE
and rh.NODE is null)
  and not exists(select *
  from route_history rh
  left join routes r on r.NODE = rh.NODE
  and r.ROUTE_INDEX = rh.ROUTE_INDEX
  and r.LINK_NODE = rh.LINK_NODE
  and r.QUALITY is not distinct from rh.QUALITY
  where t.NODE = rh.NODE
and t.ROUTE_UPDATED = rh.ROUTE_UPDATED
and r.NODE is null))
select r.NODE, r.ROUTE_INDEX, r.LINK_NODE, r.QUALITY
from new_routes nr
join routes r on nr.NODE = r.NODE

Hope I finally got it right,
Set

2017-09-28 10:01 GMT+02:00 Svein Erling Tysvær >:


Sorry, I forgot that should probably use 'IS NOT DISTINCT' and
not '=' for QUALITY.

Note that this checks only checks if there has ever been anything
identical in ROUTE_HISTORY, it doesn't restrain itself to the
latest entry only (i.e. the query I've written is slightly
similar to the second query I wrote, it has to be extended if you
want something slightly similar to the first query). Also, my
third query doesn't consider duplicate rows (since the fields
seem to be the primary key in ROUTE, it isn't theoretically
possible in your example).

Set

2017-09-28 9:52 GMT+02:00 Svein Erling Tysvær >:

OK, that's completely different, but I've done something
similar once

RE: [firebird-support] Re: Collation with Numeric-Sort and index

2017-09-28 Thread Magnus Johansson mag...@nimajo.se [firebird-support] 
From: firebird-support@yahoogroups.com 
[mailto:firebird-support@yahoogroups.com]
Sent: Friday, September 22, 2017 9:06 AM

Did you have a setup (version of FB and ICU) that passes these tests?

No, I have not been able to find a config that works.
Tested again for both FB 3.0 and FB 4.0 using the latest snapshots as of today 
against the full version of ICU 52, results are the same.
Firebird-3.0.3.32805-0_x64.
Firebird-4.0.0.749-0_x64.
icu4c-52_1-Win64-msvc10.

I do not use CI so i have not suffered from the potential disasters you point 
put above.
IMHO looks like bugs.

I have posted in FB-Devel about this, although the last response was a while 
ago now.

Regards,
Magnus Johansson

Re: [firebird-support] Firebird performance vs PostgreSQL

2017-09-28 Thread 'livius' liviusliv...@poczta.onet.pl [firebird-support] 
Hi,

without any sample you talking about nothing..
Show some sample which can be reproduced.
Without this i can say same Firebird is faster then Oracle and what someone can 
say about this – nothing 

regards,
Karol Bieniaszewski

From: zilez2...@yahoo.com [firebird-support] 
Sent: Thursday, September 28, 2017 11:17 AM
To: firebird-support@yahoogroups.com 
Subject: [firebird-support] Firebird performance vs PostgreSQL

  
Hello community, 

I am Firebird user for many years, I think I started using Interbase in 2001, 
and after that I use Firebird from version 1.0.
I have a Firebird 2.5 Server in the company where I work, which stores some 
data for the purpose of business analysis.
Since now it is September, many queries works relatively slow, due to a lot of 
data in the database.
>From the curiosity, I installed PostgreSQL yesterday on my laptop, migrated 
>data from actual Firebird database to Postgresql, and compared time of 
>execution on both system. On my great surprise, PostgreSQL was much faster 
>tqan Firebird. I don't know nothing about tweak of PostgreSQL database, so I 
>only migrated data, nothing else. 
I know that some of my views / queries are not optimized 100% on Firebird, but 
same situation is with PostgreSQL database which was just converted version of 
firebird.
Query which gives me sales by product lines from begining of the year till 
yesterday was working on firebird server 22 seconds, and on PostgreSQL 2 
seconds.

My laptop has SSD disc, but even when i put Firebird database on my laptop, 
query takes 16 seconds in Firebird.

How is it possible that PostgreSQL is so much faster than Firebird?

Regards,

Zoran 








[Non-text portions of this message have been removed]

Re: [firebird-support] LIST gives inconsistent results

2017-09-28 Thread Mark Rotteveel m...@lawinegevaar.nl [firebird-support] 
On 2017-09-28 16:40, 'Daniel Miller' dmil...@amfes.com 
[firebird-support] wrote:
> But that doesn't make sense. Right from the docs you quoted - "If
> ordering is important,

It is no guarantee as documented: "The ordering of the list values is 
**undefined**—the order in which the strings are concatenated is 
determined by **read order from the source set** which, in tables, is 
**not generally defined**.". The final sentence, "If ordering is 
important, the source data can be pre-sorted using a derived table or 
similar. ", is just a trick that works by arranging the desired 'read 
order from the source set' with the implementation as it currently is, 
it is not actually guaranteed and it can be tricky to get right.

> the source data can be pre-sorted using a derived table or similar." So
> if I create the derived table using ORDER BY - why is the LIST not
> sorted?

In the mail I replied to, you applied the ORDER BY in the subquery that 
was then subject to a join, this can apply a different order because it 
might apply an index to perform the joining. In my reply, I hoisted the 
order by up out of the join, and before the group by.

> But...if somehow the join is messing up the LIST processing I can work
> around that - generate the LIST first and then join after.  Here's the
> next issue:
> 
> Having generated a table with LIST results - why can I not sort or
> compare on the LISTS?  I first create a view:
> create view TEMP_ROUTE_HISTORY_LISTED (NODE, ROUTE_UPDATED,
> LISTED_ROUTE)
> as
> select H.NODE, H.ROUTE_UPDATED,
> list(H.ROUTE_INDEX||H.LINK_NODE||H.QUALITY) LISTED_ROUTE
>  from ROUTE_HISTORY H
>  group by H.NODE, H.ROUTE_UPDATED;
> 
> This (seems) to work - I get exactly what I think I want now.
> Except...the sort order of the rows.  So...
> select TRHL.LISTED_ROUTE
>  from TEMP_ROUTE_HISTORY_LISTED TRHL
>  where NODE=5003
>  order by TRHL.LISTED_ROUTE
> 
> This does...exactly the same as the view. LISTED_ROUTE is obviously not
> sorted. What am I doing wrong now?

The execution plan of a view is not fixed, it is influenced by how it is 
used. And this execution plan dictates the read order, and thus the 
order of values in LIST. The use of "where NODE=5003" can cause a 
different execution plan, for example - assuming an index on 
ROUTE_HISTORY.NODE - it could access the rows in index order instead of 
natural (storage) order. Or it might apply a different optimization 
based on "order by TRHL.LISTED_ROUTE" that leads to a different access 
order.

In any case, you need to apply an explicit order by **before** the LIST, 
and not rely on some accidental sort order. Try to create the view as:

select NODE, ROUTE_UPDATED,
  list(ROUTE_INDEX||LINK_NODE||QUALITY) LISTED_ROUTE
from (
 H.NODE, H.ROUTE_UPDATED, H.ROUTE_INDEX, H.LINK_NODE, H.QUALITY
 from ROUTE_HISTORY H
 order by H.NODE, H.ROUTE_UPDATED, H.ROUTE_INDEX
) a
group by NODE, ROUTE_UPDATED;

but again, this might still fail, as this trick relies on implementation 
artefacts of list and execution plans: this behavior is not 
guaranteed(!). Even worse, selecting from the view with a where might 
still screw this up, a single query with the where pushed down as far as 
possible into the subquery would probably have a greater chance of 
success.

But the only real solution would be for Firebird to add something like 
LIST(... ORDER BY ...), which it currently doesn't have.

Mark

Re[2]: [firebird-support] LIST gives inconsistent results

2017-09-28 Thread 'Daniel Miller' dmil...@amfes.com [firebird-support] 
But that doesn't make sense. Right from the docs you quoted - "If 
ordering is important,
the source data can be pre-sorted using a derived table or similar." So 
if I create the derived table using ORDER BY - why is the LIST not 
sorted?

But...if somehow the join is messing up the LIST processing I can work 
around that - generate the LIST first and then join after.  Here's the 
next issue:

Having generated a table with LIST results - why can I not sort or 
compare on the LISTS?  I first create a view:
create view TEMP_ROUTE_HISTORY_LISTED (NODE, ROUTE_UPDATED, 
LISTED_ROUTE)
as
select H.NODE, H.ROUTE_UPDATED, 
list(H.ROUTE_INDEX||H.LINK_NODE||H.QUALITY) LISTED_ROUTE
 from ROUTE_HISTORY H
 group by H.NODE, H.ROUTE_UPDATED;

This (seems) to work - I get exactly what I think I want now.  
Except...the sort order of the rows.  So...
select TRHL.LISTED_ROUTE
 from TEMP_ROUTE_HISTORY_LISTED TRHL
 where NODE=5003
 order by TRHL.LISTED_ROUTE

This does...exactly the same as the view. LISTED_ROUTE is obviously not 
sorted. What am I doing wrong now?
--
Daniel

On 9/28/2017 5:40:51 AM, "Mark Rotteveel m...@lawinegevaar.nl 
[firebird-support]"  wrote:

>On 2017-09-28 10:49, 'Daniel Miller' dmil...@amfes.com
>[firebird-support] wrote:
>>It's certainly possible my database is corrupted - I don't think it
>>is.  But I'm willing to test if someone tells me how.  However...
>>
>>At the moment, after several painful hours, I think I've determined
>>the following:
>>
>>CREATE TABLE NODES
>>(
>>   NODE smallint NOT NULL,
>>   ROUTE_UPDATED timestamp,
>>   CONSTRAINT PK_NODES PRIMARY KEY (NODE)
>>);
>>
>>CREATE TABLE ROUTES
>>(
>>   NODE smallint NOT NULL,
>>   ROUTE_INDEX smallint NOT NULL,
>>   LINK_NODE smallint NOT NULL,
>>   QUALITY smallint,
>>   CONSTRAINT PK_ROUTES PRIMARY KEY (NODE,ROUTE_INDEX)
>>);
>>
>>Entries in table ROUTES are inserted in primary key order - as well as
>>sorted by primary key. So via both "raw & natural" order and an active
>>ORDER BY it shouldn't be that difficult to have a sorted list of
>>routes!
>
>If you assume that Firebird will somehow guarantee that rows are
>returned in insertion order (or that they are even stored in insertion
>order on disk), than your are mistaken. There is no such guarantee, the
>only guarantee is using an ORDER BY.
>
>>if I do:
>>select R.NODE, list(R.ROUTE_INDEX||R.LINK_NODE||R.QUALITY)
>>LISTED_ROUTE
>> from ROUTES R
>> group by R.NODE
>>
>>I get a computed column that is properly sorted. I don't even need to
>>specify an ORDER BY.  But...
>>
>>select N.NODE, list(ROUTE_INDEX||LINK_NODE||QUALITY) LISTED_ROUTE
>> from NODES N
>> join (select R.NODE, R.ROUTE_INDEX, R.LINK_NODE, R.QUALITY from
>>ROUTES R order by R.NODE,R.ROUTE_INDEX) using (NODE)
>> group by N.NODE
>>
>>Even though I'm explicitly sorting the source derived table for the
>>LIST - I get a set of results that seems almost random. Absolutely
>>maddening.
>
>As documented in :
>https://www.firebirdsql.org/file/documentation/reference_manuals/fblangref25-en/html/fblangref25-functions-aggfuncs.html#fblangref25-functions-aggfuncs-list
>
>"**The ordering of the list values is undefined**—the order in which 
>the
>strings are concatenated is determined by read order from the source 
>set
>which, in tables, is not generally defined. If ordering is important,
>the source data can be pre-sorted using a derived table or similar."
>
>(emphasis mine)
>
>In your query your ORDER BY in the subquery of the join gets lost (or 
>is
>not guaranteed to be maintained) in the join, so you need to push that
>ORDER BY up out of the join:
>
>  select NODE, list(ROUTE_INDEX||LINK_NODE||QUALITY) LISTED_ROUTE
>  from (
> select N.NODE, R.ROUTE, R.LINK_NODE, R.QUALITY
> from NODES N
> join ROUTES R using (NODE)
> order by N.NODE, R.ROUTE_INDEX
>  ) a
>  group by NODE
>
>However, this still depends on an implementation artefact and is not
>guaranteed to work, nor guaranteed to work in future versions.
>
>Mark
>
>
>
>Posted by: Mark Rotteveel 
>
>
>++
>
>Visit http://www.firebirdsql.org and click the Documentation item
>on the main (top) menu.  Try FAQ and other links from the left-side 
>menu there.
>
>Also search the knowledgebases at 
>http://www.ibphoenix.com/resources/documents/
>
>++
>
>
>Yahoo Groups Links
>
>
>

RE: [firebird-support] Re: FB 3.0 Replacing ICU libraries

2017-09-28 Thread Magnus Johansson mag...@nimajo.se [firebird-support] 
Hi Dany,

Do you mind sharing which character set you use and how your collation is 
defined?

Mvh
Magnus Johansson

Re: [firebird-support] LIST gives inconsistent results

2017-09-28 Thread Mark Rotteveel m...@lawinegevaar.nl [firebird-support] 
On 2017-09-28 10:49, 'Daniel Miller' dmil...@amfes.com 
[firebird-support] wrote:
> It's certainly possible my database is corrupted - I don't think it
> is.  But I'm willing to test if someone tells me how.  However...
> 
> At the moment, after several painful hours, I think I've determined
> the following:
> 
> CREATE TABLE NODES
> (
>   NODE smallint NOT NULL,
>   ROUTE_UPDATED timestamp,
>   CONSTRAINT PK_NODES PRIMARY KEY (NODE)
> );
> 
> CREATE TABLE ROUTES
> (
>   NODE smallint NOT NULL,
>   ROUTE_INDEX smallint NOT NULL,
>   LINK_NODE smallint NOT NULL,
>   QUALITY smallint,
>   CONSTRAINT PK_ROUTES PRIMARY KEY (NODE,ROUTE_INDEX)
> );
> 
> Entries in table ROUTES are inserted in primary key order - as well as
> sorted by primary key. So via both "raw & natural" order and an active
> ORDER BY it shouldn't be that difficult to have a sorted list of
> routes!

If you assume that Firebird will somehow guarantee that rows are 
returned in insertion order (or that they are even stored in insertion 
order on disk), than your are mistaken. There is no such guarantee, the 
only guarantee is using an ORDER BY.

> if I do:
> select R.NODE, list(R.ROUTE_INDEX||R.LINK_NODE||R.QUALITY)
> LISTED_ROUTE
> from ROUTES R
> group by R.NODE
> 
> I get a computed column that is properly sorted. I don't even need to
> specify an ORDER BY.  But...
> 
> select N.NODE, list(ROUTE_INDEX||LINK_NODE||QUALITY) LISTED_ROUTE
> from NODES N
> join (select R.NODE, R.ROUTE_INDEX, R.LINK_NODE, R.QUALITY from
> ROUTES R order by R.NODE,R.ROUTE_INDEX) using (NODE)
> group by N.NODE
> 
> Even though I'm explicitly sorting the source derived table for the
> LIST - I get a set of results that seems almost random. Absolutely
> maddening.

As documented in : 
https://www.firebirdsql.org/file/documentation/reference_manuals/fblangref25-en/html/fblangref25-functions-aggfuncs.html#fblangref25-functions-aggfuncs-list

"**The ordering of the list values is undefined**—the order in which the 
strings are concatenated is determined by read order from the source set 
which, in tables, is not generally defined. If ordering is important, 
the source data can be pre-sorted using a derived table or similar."

(emphasis mine)

In your query your ORDER BY in the subquery of the join gets lost (or is 
not guaranteed to be maintained) in the join, so you need to push that 
ORDER BY up out of the join:

  select NODE, list(ROUTE_INDEX||LINK_NODE||QUALITY) LISTED_ROUTE
  from (
 select N.NODE, R.ROUTE, R.LINK_NODE, R.QUALITY
 from NODES N
 join ROUTES R using (NODE)
 order by N.NODE, R.ROUTE_INDEX
  ) a
  group by NODE

However, this still depends on an implementation artefact and is not 
guaranteed to work, nor guaranteed to work in future versions.

Mark

Re: [firebird-support] Embedded FB3 was killed?

2017-09-28 Thread Dimitry Sibiryakov s...@ibphoenix.com [firebird-support] 
28.09.2017 12:25, Matthias Hanft m...@hanft.de [firebird-support] wrote:
> As far as I understand that, I just call "fbclient.dll" as usual,
> and if the database path is a local file, "fbclient.dll" automagically
> calls "engine12.dll" (or whatever it needs) for the use as a local/
> embedded server. Correct?

   Yes.

> Which leads my to the question: Which of the 274 files in the 3.0.2
> ZIP are necessary to be copied into the client's application directory
> in order to run the embedded server? (Of course, as few as possible...)

   You can skip documentation and utilities.

-- 
   WBR, SD.






++

Visit http://www.firebirdsql.org and click the Documentation item
on the main (top) menu.  Try FAQ and other links from the left-side menu there.

Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/ 

++


Yahoo Groups Links

<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/firebird-support/

<*> Your email settings:
Individual Email | Traditional

<*> To change settings online go to:
http://groups.yahoo.com/group/firebird-support/join
(Yahoo! ID required)

<*> To change settings via email:
firebird-support-dig...@yahoogroups.com 
firebird-support-fullfeatu...@yahoogroups.com

<*> To unsubscribe from this group, send an email to:
firebird-support-unsubscr...@yahoogroups.com

<*> Your use of Yahoo Groups is subject to:
https://info.yahoo.com/legal/us/yahoo/utos/terms/

Re: [firebird-support] Embedded FB3 was killed?

2017-09-28 Thread Matthias Hanft m...@hanft.de [firebird-support] 
Dimitry Sibiryakov s...@ibphoenix.com [firebird-support] wrote:
> 
>Read carefully "Remodelled Architecture" part.

As far as I understand that, I just call "fbclient.dll" as usual,
and if the database path is a local file, "fbclient.dll" automagically
calls "engine12.dll" (or whatever it needs) for the use as a local/
embedded server. Correct?

Which leads my to the question: Which of the 274 files in the 3.0.2
ZIP are necessary to be copied into the client's application directory
in order to run the embedded server? (Of course, as few as possible...)

Thanks,

-Matt







++

Visit http://www.firebirdsql.org and click the Documentation item
on the main (top) menu.  Try FAQ and other links from the left-side menu there.

Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/ 

++


Yahoo Groups Links

<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/firebird-support/

<*> Your email settings:
Individual Email | Traditional

<*> To change settings online go to:
http://groups.yahoo.com/group/firebird-support/join
(Yahoo! ID required)

<*> To change settings via email:
firebird-support-dig...@yahoogroups.com 
firebird-support-fullfeatu...@yahoogroups.com

<*> To unsubscribe from this group, send an email to:
firebird-support-unsubscr...@yahoogroups.com

<*> Your use of Yahoo Groups is subject to:
https://info.yahoo.com/legal/us/yahoo/utos/terms/

Re: Re[4]: [firebird-support] Find duplicate multi-row entries

2017-09-28 Thread Svein Erling Tysvær setys...@gmail.com [firebird-support] 
OK, that's completely different, but I've done something similar once
before. It is not possible to directly compare sets for equality, but it is
possible to check that set A doesn't contain anything that isn't in set B
and that set B doesn't contain anything that isn't in set A. I assume NODE
is the one common denominator:

with tmp (NODE) as
(select distinct NODE from routes),
new_routes(NODE) as
(select t.NODE
 from tmp t
 where not exists(select *
  from routes r
  left join route_history rh on r.NODE = rh.NODE
and r.ROUTE_INDEX =
rh.ROUTE_INDEX
and r.LINK_NODE = rh.LINK_NODE
and r.QUALITY = rh.QUALITY
  where t.NODE = r.NODE
and rh.NODE is null)
  and not exists(select *
  from route_history rh
  left join routes r on r.NODE = rh.NODE
and r.ROUTE_INDEX =
rh.ROUTE_INDEX
and r.LINK_NODE = rh.LINK_NODE
and r.QUALITY = rh.QUALITY
  where t.NODE = rh.NODE
and r.NODE is null))
select r.NODE, r.ROUTE_INDEX, r.LINK_NODE, r.QUALITY
from new_routes nr
join routes r on nr.NODE = r.NODE

Does this get you the result you want?
Set

2017-09-28 9:19 GMT+02:00 'Daniel Miller' dmil...@amfes.com
[firebird-support] :

>
>
> Thanks - but that still doesn't work.  I get a huge return set with a ton
> of duplicates.  I think something's being lost in translation.  A simple
> match of one row isn't sufficient - I need to match the "set".
>
> With the following six records:
> 5557111160
> 5557211150
> 5557351042
> 5557450732
> 5557552222
> 5557651102
>
> That is the list of routes a given node has at a specific point in time.
> So it's perfectly possible at another time to have fewer, or more, or
> different routes.  And I need to consider ALL the routes in play for a
> given timestamp to be a single "set".  So if today I have the above list of
> routes, and yesterday route index 4 was looking at a different node - that
> would constitute a different and unique set even though the other 5 records
> match.
>
> 5557111160
> 5557211150
> 5557351042
> 555742
> 5557552222
> 5557651102
>
> So it's quite possible I will have lots of duplicates for an individual
> row in ROUTES when compared in ROUTE_HISTORY - what I'm trying to filter is
> the pattern of rows.  Only if ALL the rows of a current entries in ROUTES
> exist in ROUTE_HISTORY should it be considered a duplicate condition.
>
> --
> Daniel
>
> On 9/27/2017 11:53:48 PM, "Svein Erling Tysvær setys...@gmail.com
> [firebird-support]"  wrote:
>
> Sure it is possible to write such a query:
>
> select r.NODE, r.ROUTE_INDEX, r.LINK_NODE, r.QUALITY
> from routes r
> left join route_history rh1 on r.node = rh1.node
>   and r.route_index = rh1.route_index
>   and r.link_node = rh1.link_node
>   and r.quality is not distinct from rh1.quality
> left join route_history rh2 on rh1.node = rh2.node
>   and rh1.route_index = rh2.route_index
>   and rh1.route_updated < rh2.route_updated
> where rh1.node is null
>   and rh2.node is null
>
> I use two LEFT JOINS because I assume you want things to be returned if
> things are changed back, e.g. if you have
>
> 5557111160
> 5557111160
>
> then you only want one row in route_history, but if you have
>
> 5557111160
> 5557111161
> 5557111160
>
> then you don't want the last record to be considered a duplicate, but know
> when it changed back. If ROUTE_INDEX is just a running number, you consider
>
> 5557111160
> 5557211160
>
> to be duplicates, and there are no gaps between the ROUTE_INDEX for each
> NODE, then the query can be simplified:
>
> select r.NODE, r.ROUTE_INDEX, r.LINK_NODE, r.QUALITY
> from routes r
> left join route_history rh1 on r.node = rh1.node
>   and r.route_index+1 = rh1.route_index
>   and r.link_node = rh1.link_node
>   and r.quality is not distinct from rh1.quality
> where rh1.node is null
>
> Note that I use IS NOT DISTINCT so that NULLs are considered equal. If you
> prefer, the left joins can be replaced by (nested) NOT EXISTS.
>
> HTH,
> Set
>
> 2017-09-28 7:22 GMT+02:00 'Daniel Miller' dmil...@amfes.com
> [firebird-support] :
>
>>
>>
>> On 9/27/2017 9:20:54 PM, "liviuslivius liviusliv...@poczta.onet.pl
>>

[firebird-support] Firebird performance vs PostgreSQL

2017-09-28 Thread zilez2...@yahoo.com [firebird-support] 
Hello community, 

I am Firebird user for many years, I think I started using Interbase in 2001, 
and after that I use Firebird from version 1.0.
I have a Firebird 2.5 Server in the company where I work, which stores some 
data for the purpose of business analysis.
Since now it is September, many queries works relatively slow, due to a lot of 
data in the database.
From the curiosity, I installed PostgreSQL yesterday on my laptop, migrated 
data from actual Firebird database to Postgresql, and compared time of 
execution on both system. On my great surprise, PostgreSQL was much faster tqan 
Firebird. I don't know nothing about tweak of PostgreSQL database, so I only 
migrated data, nothing else. 
I know that some of my views / queries are not optimized 100% on Firebird, but 
same situation is with PostgreSQL database which was just converted version of 
firebird.
Query which gives me sales by product lines from begining of the year till 
yesterday was working on firebird server 22 seconds, and on PostgreSQL 2 
seconds.

My laptop has SSD disc, but even when i put Firebird database on my laptop, 
query takes 16 seconds in Firebird.

How is it possible that PostgreSQL is so much faster than Firebird?

Regards,

Zoran

Re[6]: [firebird-support] Find duplicate multi-row entries

2017-09-28 Thread 'Daniel Miller' dmil...@amfes.com [firebird-support] 

This appears no different than a simple:

select * from routes

So...not there yet.  I think what I'm fighting is the lack of pivot or 
crosstab functionality - and I may have no choice (though it offends me 
deeply) but to setup my archive table for this comparison.  Either via 
the hated structure of column names ROUTE1, ROUTE2, ROUTE8...or the only 
slightly less offensive VARCHAR concatenation.


A concatenated LIST seems like it would be a elegant solution - and 
while operating over the whole table is time-consuming a single node 
isn't too bad.  And if I can get the history filtered and old duplicate 
records deleted it would be fine.  But the inconsistency is killing me.


There MUST be a Firebird-friendly, normalized, compact storage structure 
that will work.  I'm just not seeing it.

--
Daniel

On 9/28/2017 1:19:18 AM, "Svein Erling Tysvær setys...@gmail.com 
[firebird-support]"  wrote:





Sorry again, Daniel, fourth query required (I forgot that things may 
vary depending on time, this is something I've never done before):


with tmp(NODE, ROUTE_UPDATED) as
(select distinct rh.NODE, rh.ROUTE_UPDATED
 from route_history rh
 join routes r on rh.NODE = r.NODE), //*This join is only for 
performance reasons in case ROUTE_HISTORY contains a lot more records 
than ROUTES and may be removed*/

new_routes(NODE) as
(select distinct t.NODE
 from tmp t
 where not exists(select *
  from routes r
  left join route_history rh on r.NODE = rh.NODE
and r.ROUTE_INDEX = 
rh.ROUTE_INDEX
and r.LINK_NODE = 
rh.LINK_NODE
and r.QUALITY is not 
distinct from rh.QUALITY
and t.ROUTE_UPDATED = 
rh.ROUTE_UPDATED

  where t.NODE = r.NODE
and rh.NODE is null)
  and not exists(select *
  from route_history rh
  left join routes r on r.NODE = rh.NODE
and r.ROUTE_INDEX = 
rh.ROUTE_INDEX
and r.LINK_NODE = 
rh.LINK_NODE
and r.QUALITY is not 
distinct from rh.QUALITY

  where t.NODE = rh.NODE
and t.ROUTE_UPDATED = rh.ROUTE_UPDATED
and r.NODE is null))
select r.NODE, r.ROUTE_INDEX, r.LINK_NODE, r.QUALITY
from new_routes nr
join routes r on nr.NODE = r.NODE

Hope I finally got it right,
Set

2017-09-28 10:01 GMT+02:00 Svein Erling Tysvær :
Sorry, I forgot that should probably use 'IS NOT DISTINCT' and not '=' 
for QUALITY.


Note that this checks only checks if there has ever been anything 
identical in ROUTE_HISTORY, it doesn't restrain itself to the latest 
entry only (i.e. the query I've written is slightly similar to the 
second query I wrote, it has to be extended if you want something 
slightly similar to the first query). Also, my third query doesn't 
consider duplicate rows (since the fields seem to be the primary key 
in ROUTE, it isn't theoretically possible in your example).


Set

2017-09-28 9:52 GMT+02:00 Svein Erling Tysvær :
OK, that's completely different, but I've done something similar once 
before. It is not possible to directly compare sets for equality, but 
it is possible to check that set A doesn't contain anything that 
isn't in set B and that set B doesn't contain anything that isn't in 
set A. I assume NODE is the one common denominator:


with tmp (NODE) as
(select distinct NODE from routes),
new_routes(NODE) as
(select t.NODE
 from tmp t
 where not exists(select *
  from routes r
  left join route_history rh on r.NODE = rh.NODE
and r.ROUTE_INDEX = 
rh.ROUTE_INDEX
and r.LINK_NODE = 
rh.LINK_NODE
and r.QUALITY = 
rh.QUALITY

  where t.NODE = r.NODE
and rh.NODE is null)
  and not exists(select *
  from route_history rh
  left join routes r on r.NODE = rh.NODE
and r.ROUTE_INDEX = 
rh.ROUTE_INDEX
and r.LINK_NODE = 
rh.LINK_NODE
and r.QUALITY = 
rh.QUALITY

  where t.NODE = rh.NODE
and r.NODE is null))
select r.NODE, r.ROUTE_INDEX, r.LINK_NODE, r.QUALITY
from new_routes nr
join routes r on nr.NODE = r.NODE

Does this get you the result you want?
Set

2017-09-28 9:19 GMT+02:00 'Daniel Miller' dmil...@amfes.com 
[firebird-support] >:



Thanks - but that still doesn't work.  I get a huge return set with 
a ton

Re: Re[4]: [firebird-support] Find duplicate multi-row entries

2017-09-28 Thread Svein Erling Tysvær setys...@gmail.com [firebird-support] 
Sorry again, Daniel, fourth query required (I forgot that things may vary
depending on time, this is something I've never done before):

with tmp(NODE, ROUTE_UPDATED) as
(select distinct rh.NODE, rh.ROUTE_UPDATED
 from route_history rh
 join routes r on rh.NODE = r.NODE), //*This join is only for performance
reasons in case ROUTE_HISTORY contains a lot more records than ROUTES and
may be removed*/
new_routes(NODE) as
(select distinct t.NODE
 from tmp t
 where not exists(select *
  from routes r
  left join route_history rh on r.NODE = rh.NODE
and r.ROUTE_INDEX =
rh.ROUTE_INDEX
and r.LINK_NODE = rh.LINK_NODE
and r.QUALITY is not distinct
from rh.QUALITY
and t.ROUTE_UPDATED =
rh.ROUTE_UPDATED
  where t.NODE = r.NODE
and rh.NODE is null)
  and not exists(select *
  from route_history rh
  left join routes r on r.NODE = rh.NODE
and r.ROUTE_INDEX =
rh.ROUTE_INDEX
and r.LINK_NODE = rh.LINK_NODE
and r.QUALITY is not distinct
from rh.QUALITY
  where t.NODE = rh.NODE
and t.ROUTE_UPDATED = rh.ROUTE_UPDATED
and r.NODE is null))
select r.NODE, r.ROUTE_INDEX, r.LINK_NODE, r.QUALITY
from new_routes nr
join routes r on nr.NODE = r.NODE

Hope I finally got it right,
Set

2017-09-28 10:01 GMT+02:00 Svein Erling Tysvær :

> Sorry, I forgot that should probably use 'IS NOT DISTINCT' and not '=' for
> QUALITY.
>
> Note that this checks only checks if there has ever been anything
> identical in ROUTE_HISTORY, it doesn't restrain itself to the latest entry
> only (i.e. the query I've written is slightly similar to the second query I
> wrote, it has to be extended if you want something slightly similar to the
> first query). Also, my third query doesn't consider duplicate rows (since
> the fields seem to be the primary key in ROUTE, it isn't theoretically
> possible in your example).
>
> Set
>
> 2017-09-28 9:52 GMT+02:00 Svein Erling Tysvær :
>
>> OK, that's completely different, but I've done something similar once
>> before. It is not possible to directly compare sets for equality, but it is
>> possible to check that set A doesn't contain anything that isn't in set B
>> and that set B doesn't contain anything that isn't in set A. I assume NODE
>> is the one common denominator:
>>
>> with tmp (NODE) as
>> (select distinct NODE from routes),
>> new_routes(NODE) as
>> (select t.NODE
>>  from tmp t
>>  where not exists(select *
>>   from routes r
>>   left join route_history rh on r.NODE = rh.NODE
>> and r.ROUTE_INDEX =
>> rh.ROUTE_INDEX
>> and r.LINK_NODE =
>> rh.LINK_NODE
>> and r.QUALITY = rh.QUALITY
>>   where t.NODE = r.NODE
>> and rh.NODE is null)
>>   and not exists(select *
>>   from route_history rh
>>   left join routes r on r.NODE = rh.NODE
>> and r.ROUTE_INDEX =
>> rh.ROUTE_INDEX
>> and r.LINK_NODE =
>> rh.LINK_NODE
>> and r.QUALITY = rh.QUALITY
>>   where t.NODE = rh.NODE
>> and r.NODE is null))
>> select r.NODE, r.ROUTE_INDEX, r.LINK_NODE, r.QUALITY
>> from new_routes nr
>> join routes r on nr.NODE = r.NODE
>>
>> Does this get you the result you want?
>> Set
>>
>> 2017-09-28 9:19 GMT+02:00 'Daniel Miller' dmil...@amfes.com
>> [firebird-support] :
>>
>>>
>>>
>>> Thanks - but that still doesn't work.  I get a huge return set with a
>>> ton of duplicates.  I think something's being lost in translation.  A
>>> simple match of one row isn't sufficient - I need to match the "set".
>>>
>>> With the following six records:
>>> 5557111160
>>> 5557211150
>>> 5557351042
>>> 5557450732
>>> 5557552222
>>> 5557651102
>>>
>>> That is the list of routes a given node has at a specific point in
>>> time.  So it's perfectly possible at another time to have fewer, or more,
>>> or different routes.  And I need to consider ALL the routes in play for a
>>> given timestamp to be a single "set".  So if today I have the above list of
>>> routes, and yesterday route index 4 was looking at a different node - that
>>> would constitute a different and unique set even though the other 5 records
>>> match.
>>>
>>> 5557111160
>>> 555721115

[firebird-support] LIST gives inconsistent results

2017-09-28 Thread 'Daniel Miller' dmil...@amfes.com [firebird-support] 
It's certainly possible my database is corrupted - I don't think it is.  
But I'm willing to test if someone tells me how.  However...


At the moment, after several painful hours, I think I've determined the 
following:


CREATE TABLE NODES
(
  NODE smallint NOT NULL,
  ROUTE_UPDATED timestamp,
  CONSTRAINT PK_NODES PRIMARY KEY (NODE)
);

CREATE TABLE ROUTES
(
  NODE smallint NOT NULL,
  ROUTE_INDEX smallint NOT NULL,
  LINK_NODE smallint NOT NULL,
  QUALITY smallint,
  CONSTRAINT PK_ROUTES PRIMARY KEY (NODE,ROUTE_INDEX)
);

Entries in table ROUTES are inserted in primary key order - as well as 
sorted by primary key. So via both "raw & natural" order and an active 
ORDER BY it shouldn't be that difficult to have a sorted list of routes!


if I do:
select R.NODE, list(R.ROUTE_INDEX||R.LINK_NODE||R.QUALITY) LISTED_ROUTE
from ROUTES R
group by R.NODE

I get a computed column that is properly sorted. I don't even need to 
specify an ORDER BY.  But...


select N.NODE, list(ROUTE_INDEX||LINK_NODE||QUALITY) LISTED_ROUTE
from NODES N
join (select R.NODE, R.ROUTE_INDEX, R.LINK_NODE, R.QUALITY from 
ROUTES R order by R.NODE,R.ROUTE_INDEX) using (NODE)

group by N.NODE

Even though I'm explicitly sorting the source derived table for the LIST 
- I get a set of results that seems almost random. Absolutely maddening.

--
Daniel

Re: [firebird-support] Multiple LIST columns

2017-09-28 Thread 'Daniel Miller' dmil...@amfes.com [firebird-support] 
I found a far more elegant? way to accomplish this - concatenate the 
fields with a single LIST, e.g.


select n.NODE, h.ROUTE_UPDATED,
list( h.ROUTE_INDEX || h.LINK_NODE || h.QUALITY )
from NODES n
join ROUTE_HISTORY h on n.NODE=h.NODE
where n.NODE=5104
group by n.NODE, h.ROUTE_UPDATED
order by h.ROUTE_UPDATED desc

This ensures the "lists" are matching - but brings up another problem 
which I'll start another thread on.

--
Daniel

On 9/27/2017 10:26:31 PM, "'Daniel Miller' dmil...@amfes.com 
[firebird-support]"  wrote:





With a statement such as:

select n.NODE, h.ROUTE_UPDATED,
list( h.ROUTE_INDEX ) LI, list( h.LINK_NODE ) LN, list( h.QUALITY ) 
LQ

from NODES n
join ROUTE_HISTORY h on n.NODE=h.NODE
where n.NODE=5104
group by n.NODE, h.ROUTE_UPDATED
order by h.ROUTE_UPDATED desc

Are the LISTS guaranteed to be in matching order? So each entry of each 
comma-delimited list is pulled from the same row as the corresponding 
entry in the other lists?


--
Daniel

Re: Re[4]: [firebird-support] Find duplicate multi-row entries

2017-09-28 Thread Svein Erling Tysvær setys...@gmail.com [firebird-support] 
Sorry, I forgot that should probably use 'IS NOT DISTINCT' and not '=' for
QUALITY.

Note that this checks only checks if there has ever been anything identical
in ROUTE_HISTORY, it doesn't restrain itself to the latest entry only (i.e.
the query I've written is slightly similar to the second query I wrote, it
has to be extended if you want something slightly similar to the first
query). Also, my third query doesn't consider duplicate rows (since the
fields seem to be the primary key in ROUTE, it isn't theoretically possible
in your example).

Set

2017-09-28 9:52 GMT+02:00 Svein Erling Tysvær :

> OK, that's completely different, but I've done something similar once
> before. It is not possible to directly compare sets for equality, but it is
> possible to check that set A doesn't contain anything that isn't in set B
> and that set B doesn't contain anything that isn't in set A. I assume NODE
> is the one common denominator:
>
> with tmp (NODE) as
> (select distinct NODE from routes),
> new_routes(NODE) as
> (select t.NODE
>  from tmp t
>  where not exists(select *
>   from routes r
>   left join route_history rh on r.NODE = rh.NODE
> and r.ROUTE_INDEX =
> rh.ROUTE_INDEX
> and r.LINK_NODE = rh.LINK_NODE
> and r.QUALITY = rh.QUALITY
>   where t.NODE = r.NODE
> and rh.NODE is null)
>   and not exists(select *
>   from route_history rh
>   left join routes r on r.NODE = rh.NODE
> and r.ROUTE_INDEX =
> rh.ROUTE_INDEX
> and r.LINK_NODE = rh.LINK_NODE
> and r.QUALITY = rh.QUALITY
>   where t.NODE = rh.NODE
> and r.NODE is null))
> select r.NODE, r.ROUTE_INDEX, r.LINK_NODE, r.QUALITY
> from new_routes nr
> join routes r on nr.NODE = r.NODE
>
> Does this get you the result you want?
> Set
>
> 2017-09-28 9:19 GMT+02:00 'Daniel Miller' dmil...@amfes.com
> [firebird-support] :
>
>>
>>
>> Thanks - but that still doesn't work.  I get a huge return set with a ton
>> of duplicates.  I think something's being lost in translation.  A simple
>> match of one row isn't sufficient - I need to match the "set".
>>
>> With the following six records:
>> 5557111160
>> 5557211150
>> 5557351042
>> 5557450732
>> 5557552222
>> 5557651102
>>
>> That is the list of routes a given node has at a specific point in time.
>> So it's perfectly possible at another time to have fewer, or more, or
>> different routes.  And I need to consider ALL the routes in play for a
>> given timestamp to be a single "set".  So if today I have the above list of
>> routes, and yesterday route index 4 was looking at a different node - that
>> would constitute a different and unique set even though the other 5 records
>> match.
>>
>> 5557111160
>> 5557211150
>> 5557351042
>> 555742
>> 5557552222
>> 5557651102
>>
>> So it's quite possible I will have lots of duplicates for an individual
>> row in ROUTES when compared in ROUTE_HISTORY - what I'm trying to filter is
>> the pattern of rows.  Only if ALL the rows of a current entries in ROUTES
>> exist in ROUTE_HISTORY should it be considered a duplicate condition.
>>
>> --
>> Daniel
>>
>> On 9/27/2017 11:53:48 PM, "Svein Erling Tysvær setys...@gmail.com
>> [firebird-support]"  wrote:
>>
>> Sure it is possible to write such a query:
>>
>> select r.NODE, r.ROUTE_INDEX, r.LINK_NODE, r.QUALITY
>> from routes r
>> left join route_history rh1 on r.node = rh1.node
>>   and r.route_index = rh1.route_index
>>   and r.link_node = rh1.link_node
>>   and r.quality is not distinct from rh1.quality
>> left join route_history rh2 on rh1.node = rh2.node
>>   and rh1.route_index = rh2.route_index
>>   and rh1.route_updated < rh2.route_updated
>> where rh1.node is null
>>   and rh2.node is null
>>
>> I use two LEFT JOINS because I assume you want things to be returned if
>> things are changed back, e.g. if you have
>>
>> 5557111160
>> 5557111160
>>
>> then you only want one row in route_history, but if you have
>>
>> 5557111160
>> 5557111161
>> 5557111160
>>
>> then you don't want the last record to be considered a duplicate, but
>> know when it changed back. If ROUTE_INDEX is just a running number, you
>> consider
>>
>> 5557111160
>> 5557211160
>>
>> to be duplicates, and there are no gaps between the

Re[4]: [firebird-support] Find duplicate multi-row entries

2017-09-28 Thread 'Daniel Miller' dmil...@amfes.com [firebird-support] 
Thanks - but that still doesn't work.  I get a huge return set with a 
ton of duplicates.  I think something's being lost in translation.  A 
simple match of one row isn't sufficient - I need to match the "set".


With the following six records:
5557111160
5557211150
5557351042
5557450732
5557552222
5557651102

That is the list of routes a given node has at a specific point in time. 
 So it's perfectly possible at another time to have fewer, or more, or 
different routes.  And I need to consider ALL the routes in play for a 
given timestamp to be a single "set".  So if today I have the above list 
of routes, and yesterday route index 4 was looking at a different node - 
that would constitute a different and unique set even though the other 5 
records match.


5557111160
5557211150
5557351042
555742
5557552222
5557651102

So it's quite possible I will have lots of duplicates for an individual 
row in ROUTES when compared in ROUTE_HISTORY - what I'm trying to filter 
is the pattern of rows.  Only if ALL the rows of a current entries in 
ROUTES exist in ROUTE_HISTORY should it be considered a duplicate 
condition.


--
Daniel

On 9/27/2017 11:53:48 PM, "Svein Erling Tysvær setys...@gmail.com 
[firebird-support]"  wrote:





Sure it is possible to write such a query:

select r.NODE, r.ROUTE_INDEX, r.LINK_NODE, r.QUALITY
from routes r
left join route_history rh1 on r.node = rh1.node
  and r.route_index = rh1.route_index
  and r.link_node = rh1.link_node
  and r.quality is not distinct from 
rh1.quality

left join route_history rh2 on rh1.node = rh2.node
  and rh1.route_index = rh2.route_index
  and rh1.route_updated < rh2.route_updated
where rh1.node is null
  and rh2.node is null

I use two LEFT JOINS because I assume you want things to be returned if 
things are changed back, e.g. if you have


5557111160
5557111160

then you only want one row in route_history, but if you have

5557111160
5557111161
5557111160

then you don't want the last record to be considered a duplicate, but 
know when it changed back. If ROUTE_INDEX is just a running number, you 
consider


5557111160
5557211160

to be duplicates, and there are no gaps between the ROUTE_INDEX for 
each NODE, then the query can be simplified:


select r.NODE, r.ROUTE_INDEX, r.LINK_NODE, r.QUALITY
from routes r
left join route_history rh1 on r.node = rh1.node
  and r.route_index+1 = rh1.route_index
  and r.link_node = rh1.link_node
  and r.quality is not distinct from 
rh1.quality

where rh1.node is null

Note that I use IS NOT DISTINCT so that NULLs are considered equal. If 
you prefer, the left joins can be replaced by (nested) NOT EXISTS.


HTH,
Set

2017-09-28 7:22 GMT+02:00 'Daniel Miller' dmil...@amfes.com 
[firebird-support] :



On 9/27/2017 9:20:54 PM, "liviuslivius liviusliv...@poczta.onet.pl 
[firebird-support]" > wrote:


group by + having
is your friend.
E.g.
Select field1, field2, count(*)
From tablex
Group by field1, field2
Having count(*)>1

But in your scenario i do not know if this is the solution. Question 
is if record was changed and in next update "restored" and once again 
changed is this duplicate or no?

If yes above query is ok if no then procedure is your friend.

And for the future change your audit trigger and check before if 
there was any change


Thank you, but I this isn't quite what I need.  I need to match 
against all the records of a set (up to 8 rows per set) - not just 
individual rows.


--
Daniel

Re: Re[2]: [firebird-support] Find duplicate multi-row entries

2017-09-28 Thread Svein Erling Tysvær setys...@gmail.com [firebird-support] 
Sure it is possible to write such a query:

select r.NODE, r.ROUTE_INDEX, r.LINK_NODE, r.QUALITY
from routes r
left join route_history rh1 on r.node = rh1.node
  and r.route_index = rh1.route_index
  and r.link_node = rh1.link_node
  and r.quality is not distinct from rh1.quality
left join route_history rh2 on rh1.node = rh2.node
  and rh1.route_index = rh2.route_index
  and rh1.route_updated < rh2.route_updated
where rh1.node is null
  and rh2.node is null

I use two LEFT JOINS because I assume you want things to be returned if
things are changed back, e.g. if you have

5557111160
5557111160

then you only want one row in route_history, but if you have

5557111160
5557111161
5557111160

then you don't want the last record to be considered a duplicate, but know
when it changed back. If ROUTE_INDEX is just a running number, you consider

5557111160
5557211160

to be duplicates, and there are no gaps between the ROUTE_INDEX for each
NODE, then the query can be simplified:

select r.NODE, r.ROUTE_INDEX, r.LINK_NODE, r.QUALITY
from routes r
left join route_history rh1 on r.node = rh1.node
  and r.route_index+1 = rh1.route_index
  and r.link_node = rh1.link_node
  and r.quality is not distinct from rh1.quality
where rh1.node is null

Note that I use IS NOT DISTINCT so that NULLs are considered equal. If you
prefer, the left joins can be replaced by (nested) NOT EXISTS.

HTH,
Set

2017-09-28 7:22 GMT+02:00 'Daniel Miller' dmil...@amfes.com
[firebird-support] :

>
>
> On 9/27/2017 9:20:54 PM, "liviuslivius liviusliv...@poczta.onet.pl
> [firebird-support]"  wrote:
>
>
> group by + having
> is your friend.
> E.g.
> Select field1, field2, count(*)
> From tablex
> Group by field1, field2
> Having count(*)>1
>
> But in your scenario i do not know if this is the solution. Question is if
> record was changed and in next update "restored" and once again changed is
> this duplicate or no?
> If yes above query is ok if no then procedure is your friend.
>
> And for the future change your audit trigger and check before if there was
> any change
>
> Thank you, but I this isn't quite what I need.  I need to match against
> all the records of a set (up to 8 rows per set) - not just individual rows.
>
> --
> Daniel
>
>
> 
>

Re: Re[2]: [firebird-support] Find duplicate multi-row entries

2017-09-28 Thread liviuslivius liviusliv...@poczta.onet.pl [firebird-support] 
Please define "set"


Regards,Karol Bieniaszewski
null