[Firebird-devel] Snapshot buuilds
Hi Snapshots are not avilable http://web.firebirdsql.org/download/snapshot_builds/win/3.0 and whole page http://web.firebirdsql.org/ Regards, Karol Bieniaszewski Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
[Firebird-devel] ODP: ExprNode::FLAG_VALUE
Hi
If i totally miss the point then ignore.
Maybe it is becuse we can sort not only by fields but by any expression.
We can sort by concatenation of strings, numbers and we can even sort by
subquery like
SELECT
*
FROM
RDB$RELATIONS R
ORDER BY (SELECT COUNT(*) FROM RDB$RELATION_FIELDS RF WHERE
RF.RDB$RELATION_NAME=R.RDB$RELATION_NAME)
And same when GROUP BY
Regards,
Karol Bieniaszewski
Od: Adriano dos Santos Fernandes
Wysłano: czwartek, 6 stycznia 2022 02:28
Do: For discussion among Firebird Developers
Temat: [Firebird-devel] ExprNode::FLAG_VALUE
Hi!
There is ExprNode::FLAG_VALUE ("Full value area required in impure
space"), inherited from old (2.5) code base nod_value.
It's set by sort subsystem and used only for parameters and variables.
It makes then allocate impure space for impure_value_ex instead of
traditional dsc.
Most nodes allocate space for impure_value. But not all of them.
Literals directly return the descriptor set in compile time.
I see no usage of the expressions impure_value in sort. And if they were
using, we'd certainly have a problem with literals.
I see no need to have this flag.
Do anyone see something I'm not seeing?
Adriano
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
[Firebird-devel] ODP: ODP: RFC: Fix for issue 6915
For me personally i think that only sorting rule have sense not searching rule. I can imagine that in database i have – consider that Harry Potter is written by „Ch” as „Charry Potter”. And i write: Rád čtu knihy o „Charry Potter”. And now i search for ‘%C%’ and i have empty result because it is „Ch” one letter . regards, Karol Bieniaszewski Od: Omacht András Wysłano: sobota, 6 listopada 2021 13:19 Temat: Re: [Firebird-devel] ODP: RFC: Fix for issue 6915 It is a rule, but hungarian languages have some of this "rules" / "cases". "SÁG"/"SÉG" is a suffix, you can make new words (adjective -> noun) using it. IGAZ -> TRUE IGAZSÁG -> TRUTH ÍNYNEC -> GOURMET ÍNYENCSÉG -> DELICACY In this cases ZS és CS are two different letters and its pronunciation is different than "ZS" and "CS" letters. András -Original Message- From: Adriano dos Santos Fernandes [mailto:adrian...@gmail.com] Sent: Saturday, November 6, 2021 12:50 PM To: For discussion among Firebird Developers Subject: Re: [Firebird-devel] ODP: RFC: Fix for issue 6915 On 06/11/2021 08:35, Omacht András wrote: > For example, in the word of "IGAZSÁG", ZS is not a letter, but here two, a Z > and an S. Is it an exception to the rule or it it another rule? Adriano Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
[Firebird-devel] ODP: RFC: Fix for issue 6915
„The main reason why this "removal of trailing partial contraction" was
done is to achieve behavior "consistent" with search/evaluation in other
software (like text editors etc.), so (for example) STARTING WITH "C" or
LIKE "C%" will return rows starting with "C" or "CH".
This behavior itself is questionable (but more about that later)
„
I try to understand the issue but meybe simpler description is required.
When i use WIN1250 collate PXW_PLK (my Polish language)
Then engine threat all „ch” as single letter in the index?
And i cannot find looking by only „c” without „h” with index lookup?
If yes how engine decide that this is „ch” or „c” and „h” in words?
If i write:
„chmura” it can be ok
But if i write (i write upper letter to show the problem):
„cHandle” which is not „ch” at all?
Is my understanding ok or i misunderstand description of performance
degradation?
regards,
Karol Bieniaszewski
Od: Pavel Cisar
Wysłano: czwartek, 4 listopada 2021 15:14
Do: firebird-devel@lists.sourceforge.net
Temat: Re: [Firebird-devel] RFC: Fix for issue 6915
Mama mia, here we go again.
I have no intention to get sucked even deeper into this endless
discussion. I'm fighting covid right now and really don't feel fit for
it. In fact, I personally don't care HOW this will be fixed, as long as
it WOULD be fixed in some timely manner, which is obviously and sadly
not going to happen.
If it would be on me, the best fix would be fixing physical storage
inefficiency for UTF8 data. The "contraction" problem is in Firebird for
long time, and it wasn't real performance killer until everyone keeps
with NARROW charsets (proven by tests). It's the UTF8 storage
inefficiency that blows this out of proportions (also proven by tests).
Everyone knows that this is the real problem that hurts the performance
in general, and there was a push from users to solve that for many
years. There was even new RLE code from ElectLabs to solve it, and
although it was not accepted - as far as I remember - a solution was
promised by project. Six years later, we still got nothing (I and see no
such thing on v5.0 roadmap so go figure).
Now this failed promise bitten us back as another unhappy Firebird user
(one from big Czech software houses that uses Firebird from day one)
that needs to switch hundreds of databases of its big international
customers from codepages to UTF8 in Q1/2 next year got stuck as
extensive testing revealed serious performance issues after switch to
UTF8 ("contraction" issue is just the biggest performance loss spike, so
they bring it to our attention). They are even willing to pay to get it
solved, but as months pass, it's more and more obvious that they will
eventually end as dead in the water (no solution in due time). Guess
what? As they are under pressure themselves to do the switch, they will
have to start move away from Firebird for important big projects (no
kidding). They will certainly keep it for low end as it makes sense, but
anyway.
Maybe I'm just getting old and worn by mounting waste of time and
effort, but it appears like the project is slowly but surely losing its
drive and spirit over years, as it more and more feels that we're
running to stand still.
regards
Pavel
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
[Firebird-devel] ODP: Cascade replication
Now it have more sense, thx regards, Karol Bieniaszewski Od: Dmitry Yemanov Wysłano: środa, 27 października 2021 09:18 Do: For discussion among Firebird Developers Temat: Re: [Firebird-devel] Cascade replication 27.10.2021 09:06, Karol Bieniaszewski wrote: > > Can you point me > > https://github.com/FirebirdSQL/firebird/commit/e6a33454e871b9f9a368ccf281081e867c2b18cf > > <https://github.com/FirebirdSQL/firebird/commit/e6a33454e871b9f9a368ccf281081e867c2b18cf> > > what is enabled here and on which side? > > If i understand correctly it is configured on primary database side not > on replica? Nope, it's configured on the replica side and allows the received changes to be propagated further (if replica is also configured as a primary, i.e. A->B->C). Dmitry Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
[Firebird-devel] Cascade replication
Hi Can you point me https://github.com/FirebirdSQL/firebird/commit/e6a33454e871b9f9a368ccf281081e867c2b18cf what is enabled here and on which side? If i understand correctly it is configured on primary database side not on replica? If yes, why it is needed? Why not „simple” configure another replication on replica side? And anothere on another replica side... It is more natural. But maybe i am wrong here, please explain more this option. regards, Karol Bieniaszewski Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
[Firebird-devel] ODP: Increasing CHAR/VARCHAR max. length to 64KB
Isn’t such documentation already available by the new API developers? I cannot imagine to have not comparision between every code in old vs new api. Its benefits, comparision with simplicity about use, speed, memory usage, extenibility.. And the final conclusion why new API is better then old one as it was accepted and preffered. It only require some rework for publicity or simply show it as draft and then extend/correct. regards, Karol Bieniaszewski Od: Mark Rotteveel Wysłano: sobota, 22 maja 2021 09:39 Temat: Re: [Firebird-devel] Increasing CHAR/VARCHAR max. length to 64KB On 22-05-2021 09:19, Dmitry Yemanov wrote: > 22.05.2021 10:06, Mark Rotteveel wrote: > >> The majority of our users probably still use the old API, either >> directly or indirectly. Given the undocumented state of the new API, I >> suspect it does not see a lot of usage, nor will it unless that changes. >> >> Introducing features that only benefit users of the new API, which - >> to be a bit hyperbolic - primarily seem to be the Firebird core >> developers themselves, seems a bit off to me. >> >> What is the point of this, and what is the benefit to our users? > > This sounds similar to the Dialect 1 problem. If all the new features > are also available in the old API, what's the point for users to migrate > to the new one? (even if we'd have an API guide published). I acknowledge that it is a bit of chicken-and-egg problem, but I think first more work needs to be done to actually **document** the new API, with basic examples to demonstrate it. If users don't know where to start or how to use it, then even new shiny features probably won't motivate enough to switch. And lets not forget the increased risk that having to invest to switch anyway, people might consider switching to an entirely different DBMS), which I think is bigger than with dialect 1 vs 3. Mark -- Mark Rotteveel Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
[Firebird-devel] ODP: ODP: Replication - generators' value arenotreplicated
Settings written to database only, not stored outside. Then there is not something like the „defaults”. There many other scenarions not only 1/0. It can be date and time stored into generator values and more… I think about replicating generators at all. regards, Karol Bieniaszewski Od: Dimitry Sibiryakov Wysłano: wtorek, 18 maja 2021 16:58 Do: For discussion among Firebird Developers Temat: Re: [Firebird-devel] ODP: Replication - generators' value arenotreplicated 18.05.2021 16:22, liviuslivius wrote: > But such replica cannot became "master" database if master is > broken/unavailable? Why? Values of generators used as flags are usually not important and are reset to defaults by application after promoting as if primary server has been suddenly aborted (which is actually the case). -- WBR, SD. Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
[Firebird-devel] ODP: Replication - generators' value are notreplicated
>> Sorry for the blind noise. It is not noise. Is there a way to configure replication to always replicate generator change? I as as we use genertors also as settings. E.g. we change it from 1 to 0 and back. And in procedures or triggers we check for value in generator if it is 1 (on) or 0 (off). regards, Karol Bieniaszewski Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
[Firebird-devel] ODP: The issue tracker for the Firebird core moveis done ?
Greate work. Good that there are tags in all posts as users accounts are not in github. I can find my cores by searching for e.g.: "submitted by: Livius2" I suppose there is no way to link it to github user as you must have list of users matches provided first by users itself And this will require more time from you. regards, Karol Bieniaszewski Od: Mark Rotteveel Wysłano: wtorek, 27 kwietnia 2021 13:27 Do: firebird-devel@lists.sourceforge.net Temat: Re: [Firebird-devel] The issue tracker for the Firebird core moveis done ? On 27-04-2021 13:15, marius adrian popa wrote: > > The issue tracker for the Firebird documentation has moved to github issues > Also i see the Firebird CORE and the other projects are moved Web/QA... > It's done ? :) Yes, except for configuring the DNS for the redirector from tracker.firebirdsql.org to GitHub issues. Mark -- Mark Rotteveel Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
[Firebird-devel] ODP: Blob filters and UDF deprecation
As Udf’s are depricated, are somwhere all current udfs rewriten in udr form? I mean about fbudf.dll and ib_udf.dll and with source code to look at it as a sample for self written „udr_udf” ? regards, Karol Bieniaszewski Od: Dimitry Sibiryakov Wysłano: niedziela, 31 stycznia 2021 17:57 Do: For discussion among Firebird Developers Temat: Re: [Firebird-devel] Blob filters and UDF deprecation 31.01.2021 17:42, Mark Rotteveel wrote: > On top of that, the current advise is to disable UdfAccess (None), which > means blob > filters can't work either if you have UDFs disabled. IMHO, this is a bug. > So at minimum, I guess work should be done to separate blob filters from UDFs It is easy: just replace Jrd::Module with ModuleLoader in flu.cpp. -- WBR, SD. Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
[Firebird-devel] [FB-Tracker] Created: (CORE-6471) Add MON$ELAPSED_TIME to MON$STATEMENTS
Add MON$ELAPSED_TIME to MON$STATEMENTS -- Key: CORE-6471 URL: http://tracker.firebirdsql.org/browse/CORE-6471 Project: Firebird Core Issue Type: New Feature Components: Engine Reporter: Karol Bieniaszewski Add cumulative time during query execution. Query can be be stalled and active back in long intervals but it can be relative short as a whole. Look at discussion with Vlad Khorsun in CORE-6456 maybe you find more fields/statistics to add. -- This message is automatically generated by JIRA. - If you think it was sent incorrectly contact one of the administrators: http://tracker.firebirdsql.org/secure/Administrators.jspa - For more information on JIRA, see: http://www.atlassian.com/software/jira Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
[Firebird-devel] [FB-Tracker] Created: (CORE-6456) Add trace info about read query in packets
Add trace info about read query in packets -- Key: CORE-6456 URL: http://tracker.firebirdsql.org/browse/CORE-6456 Project: Firebird Core Issue Type: New Feature Components: Engine, TRACEMGR Reporter: Karol Bieniaszewski Provide the way to see info about packet read in trace. I mean e.g.: Table have 1 records. I do SELECT * FROM MY_TABLE But i do not read all records at start (do not fetch all) but i read data in packets e.g. 300 records at one time. Such statement is shown in MON$STATEMENTS as „Stalled". Now i do not see any info in trace about this fetches. I see only PREPARE_STATEMENT, EXECUTE_STATEMENT_START, CLOSE_CURSOR, EXECUTE_STATEMENT_FINISH, FREE_STATEMENT But i need to calculate how long query was executed. And between next 300 record fetches can be e.g. 5 minute break. And i report then such query in my tool as long long running query which consume resources continously. But really this query run only few ms. All fetches alltogether take e.g. 100ms but i report it as they run e.g. 1 hour. -- This message is automatically generated by JIRA. - If you think it was sent incorrectly contact one of the administrators: http://tracker.firebirdsql.org/secure/Administrators.jspa - For more information on JIRA, see: http://www.atlassian.com/software/jira Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
[Firebird-devel] Firebird tracker "Service Temporarily Unavailable"
As in title – Firebird tracker „Service Temporarily Unavailable”. And i do not suppose it is good practice to provide server details: „Apache/2.2.3 (CentOS) Server at tracker.firebirdsql.org Port 80” regards, Karol Bieniaszewski Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
[Firebird-devel] ODP: SQL profiler
Really great feature but i am worried about the title „package (FB 5.0)”. Is it for FB4 or for the future version FB5? regards, Karol Bieniaszewski Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
[Firebird-devel] Firebird ppa 3.0.6 for ubuntu 20.04
In my humble opinion it should be released ASAP or 3.0.6 should be removed from download. As this is about several server hangs regressions. regards, Karol Bieniaszewski Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
[Firebird-devel] ODP: RFC: RDB$BLOB_UTIL system package
>>PSQL compiler a little >>bit more intelligent and do such optimizations automatically. It should be more inteligent as this is simple task. Blob should have flag like returned outside. If set then new blob should be created if false it is simple appended. E.g. in this loop there is no need to create blob as it is not returned > while (i < 150) > do > begin > b = b || s; > i = i + 1; > end But e.g. here (e.g. blob b is in returning values) > while (i < 150) > do > begin > b = b || s; > if i mod 10=0 then > susspend; > i = i + 1; > end Every 10 loop there is need to new one blob to create – as susspend return blob outside. Only should be identified situations when „blob is returned” occure. If it sussped or more situations like >> insert into t (b) values (:b); Only need to set flag for blob in that situations and in next assignment create new one with flag set to false Regards, Karol Bieniaszewski Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
[Firebird-devel] Firebird 4 RC1
Hi I know that you wait for some fidback about testing. And you wait for some bugs to be reported especially about new features. But i suppose it is also valuable to you to have info that old functionality work ok and server do not carash. I have run 20 hours stress test on FB4 RC1 and it handle it without any crash I have run also speed comparison and query plan difference comparision. And all looks really good. FB4 is comparable to FB3. 3 intensive queries was faster on FB4. E.g. on FB3 it took 11383 ms on FB4 10428 ms. Others queries was comparable in times. All comparision was using same machine, big cache and second run of the queries. regards, Karol Bieniaszewski Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
[Firebird-devel] [FB-Tracker] Created: (CORE-6354) improve error message "key size too big for index"
improve error message "key size too big for index" -- Key: CORE-6354 URL: http://tracker.firebirdsql.org/browse/CORE-6354 Project: Firebird Core Issue Type: Improvement Components: Engine Reporter: Karol Bieniaszewski Priority: Minor Now when we try to create index on big e.g. VARCHAR(1000) field we got an error "key size too big for index XXX". But this is not definitly true as it depend on page size. Will be good if the message will be variable. It should depend of database page size. As you know max index size depend on page size. This info should be included into error message. If database page size is lower then FB max page size it should contain info like: "key size too big for index XXX. Increase database page size to support bigger key sizes." Now many users of Firebird use some tools which have default 4K page size. And they think that FB is so limited and they choose different database for use becaouse of such simple thing. I know presonally 2 persons which was affected by this and i see also sample in FB tracker CORE-2201 - "Vrinda Nayak 15/Nov/17 04:29 PM" -- This message is automatically generated by JIRA. - If you think it was sent incorrectly contact one of the administrators: http://tracker.firebirdsql.org/secure/Administrators.jspa - For more information on JIRA, see: http://www.atlassian.com/software/jira Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
[Firebird-devel] ODP: ODP: Modern C++: constexpr
My English is bad so sorry if it have wrong maining. In my humble opinion this discussion is usless. E.g. rasism is against people. It have no corelation with software/hardware. And there must be violence against people physical or mental. But this have not relations to maschines/software. Master/slave is technical thing. It is in any electronic with magistral. And think about. If i write white people i am rasist or only when i write black people? I suppose in non of this. There must be some violence against people. In software there is not. regards, Karol Bieniaszewski Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
[Firebird-devel] ODP: ODP: Modern C++: constexpr
>> If feature belongs to both lists - is it allowed or denied? Please explain how? >> If it does not belong to both them? Its usege should be considered as use at own risk. regards, Karol Bieniaszewski Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
[Firebird-devel] ODP: Modern C++: constexpr
>> we may have black list instead white list like now. As always better is to have both black + white. Regards, Karol Bieniaszewski Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
[Firebird-devel] [FB-Tracker] Created: (CORE-6324) Alter domain operation for domain with collation specified revert its collation to database default
Alter domain operation for domain with collation specified revert its collation to database default --- Key: CORE-6324 URL: http://tracker.firebirdsql.org/browse/CORE-6324 Project: Firebird Core Issue Type: Bug Components: Engine Affects Versions: 3.0.5 Reporter: Karol Bieniaszewski After comment in CORE-1202 Mark Rotteveel ask me for creating new bug ticket. CREATE DOMAIN XXX Varchar(1000) CHARACTER SET WIN1250 COLLATE PXW_PLK; now when you alter its e.g. size ALTER DOMAIN XXX TYPE VARCHAR(2000); it will be after ALTER equivalent to CREATE DOMAIN XXX Varchar(2000) CHARACTER SET WIN1250 COLLATE WIN1250; as you can see it change PXW_PLK to WIN1250; -- This message is automatically generated by JIRA. - If you think it was sent incorrectly contact one of the administrators: http://tracker.firebirdsql.org/secure/Administrators.jspa - For more information on JIRA, see: http://www.atlassian.com/software/jira Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
[Firebird-devel] [FB-Tracker] Created: (CORE-6300) Next attachment id, next statement id
Next attachment id, next statement id - Key: CORE-6300 URL: http://tracker.firebirdsql.org/browse/CORE-6300 Project: Firebird Core Issue Type: New Feature Components: Engine Reporter: Karol Bieniaszewski Please provide easy access to the info: - next attachment id - next statement id Currently we can read in easy way only next transaction id as MON$NEXT_TRANSACTION in MON$DATABASE. Next statement id we can read from joining current attachment with mon$statements. But next attachment id can be read only by statistics service (reading header page) and it does not show real time values only some "updated one" and not correlated with current snapshot of database state taken by MON$ tables. Of course we can made new connection - but this is to costly and not natural way. This info can be used to monitor database e.g. how many new attachements are done during period. In a period there can be many short leaving attachment and reading MON$ATTACHEMNT can simply miss it. -- This message is automatically generated by JIRA. - If you think it was sent incorrectly contact one of the administrators: http://tracker.firebirdsql.org/secure/Administrators.jspa - For more information on JIRA, see: http://www.atlassian.com/software/jira Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
[Firebird-devel] ODP: New API and scrollable cursors
May i ask if this is big developmenet cost to support it on the client side api? Maybe it is shorter time than this disscussion I try make this discussion lighter Regards, Karol Bieniaszewski Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
[Firebird-devel] [FB-Tracker] Created: (CORE-6267) Not avaiable alias is used inside CTE without an error
Not avaiable alias is used inside CTE without an error -- Key: CORE-6267 URL: http://tracker.firebirdsql.org/browse/CORE-6267 Project: Firebird Core Issue Type: Bug Components: Engine Affects Versions: 3.0.5, 3.0.6 Reporter: Karol Bieniaszewski Look at this not trivial query but migrated to use only system tables to show the problem. Alias "U" is not available in the CTE "ZAWIESZENIA". If you remove whole WHERE from the main query then an error is raised. -- SQL error code = -206 Column unknown U.UMOWA_DATA_DO At line 8, column 190. --- but if you add whole where no error present. It can be releated somehow with window functions. - WITH DATY_ZAWIESZEN AS ( SELECT date '2020-01-01' AS DZ_OD, date '2020-01-31' AS DZ_DO FROM RDB$DATABASE ) , ZAWIESZENIA AS ( SELECT W_UZ.RDB$RELATION_ID, W_UZ.RDB$CHARACTER_SET_NAME, W_UZ.UMOWA_ID, MAXVALUE(W_UZ.ZAWIESZ_DATA_OD, W_U.UMOWA_DATA_OD) AS ZAWIESZ_DATA_OD, MINVALUE(COALESCE(W_UZ.ZAWIESZ_DATA_DO, W_DZ.DZ_DO), U.UMOWA_DATA_DO /* ALIAS "U" IS NOT AVAILABLE HERE */) AS ZAWIESZ_DATA_DO FROM (SELECT CURRENT_DATE AS ZAWIESZ_DATA_OD, CURRENT_DATE AS ZAWIESZ_DATA_DO, RDB$RELATION_ID, RDB$CHARACTER_SET_NAME, 1 AS UMOWA_ID FROM RDB$DATABASE ) W_UZ INNER JOIN (SELECT CURRENT_DATE AS UMOWA_DATA_OD FROM RDB$DATABASE ) W_U ON 1=1 INNER JOIN DATY_ZAWIESZEN W_DZ ON 1=1 ) SELECT U.UMOWA_ID FROM (SELECT CURRENT_DATE AS UMOWA_DATA_DO, 2 AS UMOWA_ID FROM RDB$DATABASE) U INNER JOIN DATY_ZAWIESZEN DZ ON 1=1 /* without this WHERE an error is raised about unknown column UMOWA_DATA_DO */ WHERE EXISTS ( SELECT * FROM ( SELECT ISLANDS.UMOWA_ID , ISLANDS.ISLAND_NR , MIN(ISLANDS.START_DATE) AS ISLAND_START_DATE , MAX(ISLANDS.END_DATE) AS ISLAND_END_DATE FROM (SELECT GROUPS.* , CASE WHEN GROUPS.PREV_END_DATE >= START_DATE THEN 0 ELSE 1 END AS IS_ISLAND_START , SUM(CASE WHEN GROUPS.PREV_END_DATE >= START_DATE THEN 0 ELSE 1 END) OVER (PARTITION BY GROUPS.UMOWA_ID ORDER BY GROUPS.RN) AS ISLAND_NR FROM (SELECT ROW_NUMBER() OVER(PARTITION BY UZ.UMOWA_ID ORDER BY UZ.ZAWIESZ_DATA_OD, COALESCE(UZ.ZAWIESZ_DATA_DO, DZ.DZ_DO)) AS RN , UZ.UMOWA_ID , UZ.ZAWIESZ_DATA_OD AS START_DATE , COALESCE(UZ.ZAWIESZ_DATA_DO, DZ.DZ_DO) AS END_DATE , LAG(COALESCE(UZ.ZAWIESZ_DATA_DO, DZ.DZ_DO), 1) OVER(PARTITION BY UZ.UMOWA_ID ORDER BY UZ.ZAWIESZ_DATA_OD, COALESCE(UZ.ZAWIESZ_DATA_DO, DZ.DZ_DO)) AS PREV_END_DATE FROM ZAWIESZENIA UZ WHERE UZ.UMOWA_ID=U.UMOWA_ID ) GROUPS ) ISLANDS GROUP BY ISLANDS.UMOWA_ID , ISLANDS.ISLAND_NR ORDER BY ISLAND_START_DATE ) X WHERE DZ.DZ_OD>=X.ISLAND_START_DATE AND DZ.DZ_DO<=X.ISLAND_END_DATE ) -- This message is automatically generated by JIRA. - If you think it was sent incorrectly contact one of the administrators: http://tracker.firebirdsql.org/secure/Administrators.jspa - For more information on JIRA, see: http://www.atlassian.com/software/jira Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
[Firebird-devel] ODP: CORE-6199 - few question about Firebird 4
>> IMO, it will be much better if you describe the task you need to solve Task is in CORE-6199 but i need only part of it. My current solution is with OAT + RDB$RECORD_VERSION and now i am looking into FB4 changes if it can help more. It is better now because of existence of RDB$GET_TRANSACTION_CN. But it still do not use and index to query. And my question was about optimize this part to use an expression index somehow. Regards, Karol Bieniaszewski Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
[Firebird-devel] ODP: ODP: CORE-6199 - few question about Firebird 4
Ach now i understand. It is point view for current transaction, which record versions it see. Very usefull, thank you. Regards, Karol Bieniaszewski Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
[Firebird-devel] ODP: CORE-6199 - few question about Firebird 4
>> Last already commited transaction CN. OK >> SNAPSHOT_NUMBER What it mean, is it from current transaction point of view first snapshot, last snapshot transaction what it is? Can you explain value based on below points? 1. Start transaction 1 snapshot 2. Commit transaction 1 3. Start transaction 2 snapshot 4. Start transaction 3 snapshot 5. Start transaction 4 read commited >> See RDB$GET_TRANSACTION_CN, described at >> doc/sql.extensions/README.builtin_functions.txt Super Regards, Karol Bieniaszewski Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
[Firebird-devel] [FB-Tracker] Created: (CORE-6176) Name for the field "CURRENT_USER" is "USER" after select
Name for the field "CURRENT_USER" is "USER" after select
Key: CORE-6176
URL: http://tracker.firebirdsql.org/browse/CORE-6176
Project: Firebird Core
Issue Type: Bug
Components: Engine
Affects Versions: 3.0.5
Reporter: Karol Bieniaszewski
Priority: Trivial
SELECT CURRENT_USER, CURRENT_TRANSACTION, CURRENT_TIME, CURRENT_DATE,
CURRENT_TIMESTAMP, LOCALTIME, LOCALTIMESTAMP FROM RDB$DATABASE
result:
USERCURRENT_TRANSACTION CURRENT_TIMECURRENT_DATE
CURRENT_TIMESTAMP LOCALTIME LOCALTIMESTAMP
SYSDBA 268705 09:45:442019-10-31 2019-10-31 09:45:44
09:45:442019-10-31 09:45:44
as you can see CURRENT_USER is named as "USER" all other variable names are ok
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://tracker.firebirdsql.org/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
[Firebird-devel] [FB-Tracker] Created: (CORE-6167) Incorrect message "Cannot use an aggregate or window function in a GROUP BY clause." when field if from outside context
Incorrect message "Cannot use an aggregate or window function in a GROUP BY clause." when field if from outside context --- Key: CORE-6167 URL: http://tracker.firebirdsql.org/browse/CORE-6167 Project: Firebird Core Issue Type: Bug Components: Engine Affects Versions: 3.0.4, 3.0.5 Reporter: Karol Bieniaszewski Priority: Minor do nota analyse logic here, this is only a sample on system tables --- SELECT R.RDB$RELATION_NAME, ( SELECT SUM(RF.RDB$FIELD_ID) FROM RDB$RELATION_FIELDS RF WHERE RF.RDB$RELATION_NAME=R.RDB$RELATION_NAME GROUP BY R.RDB$RELATION_NAME /* here should be RF not R */ ) FROM RDB$RELATIONS R --- SQL error code = -104 Cannot use an aggregate or window function in a GROUP BY clause. --- Message should be maybe "cannot 'group by' by outside context" or something else but current one is not appropriate. -- This message is automatically generated by JIRA. - If you think it was sent incorrectly contact one of the administrators: http://tracker.firebirdsql.org/secure/Administrators.jspa - For more information on JIRA, see: http://www.atlassian.com/software/jira Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
[Firebird-devel] [FB-Tracker] Created: (CORE-6153) Provide snapshot builds for Android port
Provide snapshot builds for Android port Key: CORE-6153 URL: http://tracker.firebirdsql.org/browse/CORE-6153 Project: Firebird Core Issue Type: Task Reporter: Karol Bieniaszewski Please provide snapshot builds for Android port. It deserve to have it. Now we must wait for fixed version avaiable to official download, many months. E.g. look at CORE-5888, Alexander Peshkov provide link for fixed version at 23/May/19 04:49 PM. And it is still not avaiable for download. -- This message is automatically generated by JIRA. - If you think it was sent incorrectly contact one of the administrators: http://tracker.firebirdsql.org/secure/Administrators.jspa - For more information on JIRA, see: http://www.atlassian.com/software/jira Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
[Firebird-devel] ODP: How to fb 3.0.4 embedde in android with delphi 10.2
Hi First you must download fixed version of Firebird (current one on the official page is not working properly). Look into http://tracker.firebirdsql.org/browse/CORE-5888. There is a link for fixed version provided by Alexander Peshkov. Look if it is working for you. Regards, Karol Bieniaszewski Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
[Firebird-devel] ODP: ODP: ODP: ODP: Inserts and FKs
You have right, there is a bug and the big one! I suppose that index of Foreign Key is not validated by existence of value in the record itself and its (the record version) transaction numer is not compared to snapshot number. Please report a bug to the tracker. regards, Karol Bieniaszewski Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
[Firebird-devel] ODP: ODP: ODP: Inserts and FKs
Better show your commands in isql then we can reproduce problem. Regards, Karol Bieniaszewski Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
[Firebird-devel] ODP: ODP: Inserts and FKs
>>tx1 starts (read commited)
>>tx1 INSERT INTO A (ID) VALUES (1);
>>tx2 starts (snapshot isolation) - How did you started tx2? If you are in
>>isql, you need at last to run some statement, like a select, to actually
>>start a transaction. For example, run this on the >>second isql section:
>>commit; -- making sure that if there is a transaction started, it will be
>>closed now
>>select * from a; -- Doing this you are actually starting a transaction (tx2)
>>tx1 commits
>>tx2 INSERT INTO B (ID, ID_A) VALUES (1, 1);
>>tx2 commits – i have an error here! violation of FOREIGN KEY constraint
>>"FK_B__A" on table "B" Foreign key reference target does not exist
>>Problematic key value is ("ID_A" = 1)
>>or transaction wait if i specifi wait for locks.
>>I tested your steps (following my comments above) and get no error (FB 2.5).
I have tested this by 2 instances of Flamerobin.
But i have tested this under FB3 (WI-V3.0.4.32989 Firebird 3.0) not 2.5.
I have not 2.5 installed as i have migrated all to FB3.
Regards,
Karol Bieniaszewski
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
[Firebird-devel] ODP: Inserts and FKs
I have tested this and i got an error all the time or i get waiting for lock
depending on transaction settings.
I do the following:
CREATE TABLE A
(
ID BIGINT NOT null PRIMARY KEY
);
CREATE TABLE B
(
ID BIGINT NOT null PRIMARY KEY,
ID_A BIGINT NOT NULL
);
ALTER TABLE B ADD constraint FK_B__A FOREIGN KEY(ID_A) REFERENCES A(ID) ON
UPDATE CASCADE ON DELETE CASCADE;
tx1 starts (read commited)
tx1 INSERT INTO A (ID) VALUES (1);
tx2 starts (snapshot isolation)
tx1 commits
tx2 INSERT INTO B (ID, ID_A) VALUES (1, 1);
tx2 commits – i have an error here! violation of FOREIGN KEY constraint
"FK_B__A" on table "B" Foreign key reference target does not exist Problematic
key value is ("ID_A" = 1)
or transaction wait if i specifi wait for locks.
I have also create empty triggers but no change
I suppose you do someting different. Please describe exact steeps to reproduce
this.
Regards,
Karol Bieniaszewski
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
[Firebird-devel] [FB-Tracker] Created: (CORE-6124) "no current record for fetch operation" when joining with stored procedure inside derived table
"no current record for fetch operation" when joining with stored procedure inside derived table --- Key: CORE-6124 URL: http://tracker.firebirdsql.org/browse/CORE-6124 Project: Firebird Core Issue Type: Bug Components: Engine Affects Versions: 3.0.5 Reporter: Karol Bieniaszewski SET TERM ^ ; CREATE PROCEDURE PSTRID ( IN_ID1 VARCHAR(100), IN_ID2 VARCHAR(100)) RETURNS ( ID1 VARCHAR(100), ID2 VARCHAR(100)) AS BEGIN ID1 = IN_ID1; ID2 = IN_ID2; SUSPEND; END^ SET TERM ; ^ SELECT * FROM RDB$RELATIONS R INNER JOIN (SELECT * FROM RDB$RELATION_FIELDS F INNER JOIN PSTRID(F.RDB$RELATION_NAME, F.RDB$FIELD_NAME) P ON P.ID1 = F.RDB$RELATION_NAME AND P.ID2 = F.RDB$FIELD_NAME ROWS 2) F2 ON R.RDB$RELATION_NAME = F2.RDB$RELATION_NAME -- This message is automatically generated by JIRA. - If you think it was sent incorrectly contact one of the administrators: http://tracker.firebirdsql.org/secure/Administrators.jspa - For more information on JIRA, see: http://www.atlassian.com/software/jira Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
[Firebird-devel] [FB-Tracker] Created: (CORE-6121) Add switch to gbak to restore database with specified transaction number
Add switch to gbak to restore database with specified transaction number Key: CORE-6121 URL: http://tracker.firebirdsql.org/browse/CORE-6121 Project: Firebird Core Issue Type: New Feature Components: GBAK Reporter: Karol Bieniaszewski Please add switch to gbak to restore database with specified transaction number. E.g. Interbase have this as "-START(ING_TRANS)" the name can be different. I suppose it is very simple to implement as already during restore Firebird start this numbering from 1. Now it should take into account if start it from 1 or specified value from the parameter. gbak -c -v -START 123456789 and database after restore looks like Database header page information: Flags 0 Checksum12345 Write timestamp Aug 9, 2019 15:17:08 Page size 4096 ODS version 16.0 Oldest transaction 123456790 <- THE SPECIFIED NUMBER +1 Oldest active 123456791 Oldest snapshot 123456791 Next transaction123456850 Sequence number 0 Next attachment ID 0 Implementation ID 16 Shadow count0 Page buffers2048 Next header page0 Database dialect3 Creation date Aug 9, 2019 15:17:07 Backup timestampAug 9, 2019 15:17:08 Attributes force write Variable header data: Sweep interval: 2 *END* -- This message is automatically generated by JIRA. - If you think it was sent incorrectly contact one of the administrators: http://tracker.firebirdsql.org/secure/Administrators.jspa - For more information on JIRA, see: http://www.atlassian.com/software/jira Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
[Firebird-devel] [FB-Tracker] Created: (CORE-6106) no current record for fetch operation
no current record for fetch operation - Key: CORE-6106 URL: http://tracker.firebirdsql.org/browse/CORE-6106 Project: Firebird Core Issue Type: Bug Components: Engine Affects Versions: 3.0.5 Reporter: Karol Bieniaszewski Attachments: COREXXX.FDB WITH KWARTALY AS (SELECT * FROM KWARTAL KW WHERE KW.CKWARTAL BETWEEN '2019.I' AND '2019.I'), KWARTALY_DATY AS (SELECT MIN(KW.ZALICZAJ_OD) AS MIN_ZALICZAJ_OD , MAX(KW.ZALICZAJ_DO) AS MAX_ZALICZAJ_DO , MIN(KW.D_OD) AS MIN_D_OD , MAX(KW.D_DO) AS MAX_D_DO , MIN(KW.CKWARTAL) AS MIN_KWARTAL , MAX(KW.CKWARTAL) AS MAX_KWARTAL FROM KWARTALY KW) SELECT K.ID , K.KONT_NR FROM KONTRAHENT K WHERE NOT ( EXISTS ( SELECT 1 FROM KWARTALY_DATY KW WHERE /* jest jakakolwiek deklaracja w zadanym przedziale to znaczy, ze jest platnikiem*/ EXISTS (SELECT * FROM SPR S INNER JOIN KWARTAL SKW ON S.ID_KWARTAL = SKW.ID WHERE S.ID_KONTRAHENT = K.ID AND SKW.CKWARTAL BETWEEN KW.MIN_KWARTAL AND KW.MAX_KWARTAL ) OR /* jesli ma wpis o statusie dzialnosci za zadany okres (z data pozyaskania nie z przyszlosci) to tez jest platnikiem */ EXISTS (SELECT * FROM STATUS_PLATNIKA SP INNER JOIN STATUS_PLATNIKA_TYP SPT ON SP.ID_STATUS_PLATNIKA_TYP = SPT.ID WHERE SP.ID_KONTRAHENT = K.ID AND SP.DATA_POZYSKANIA <= KW.MAX_ZALICZAJ_DO AND (SPT.FLAGA_SYS IN(1) AND ((SP.DATA_OD <= KW.MAX_D_DO) AND (SP.DATA_DO IS NULL OR (SP.DATA_DO >= KW.MIN_D_OD ) UNION ALL SELECT 1 FROM KWARTALY_DATY KD INNER JOIN URZADZENIE U ON 1 = 1 /* robimy liste kwartalow w ktorych obowiazuje status dzialalnosci kontrahenta i szukamy brakow deklaracji */ INNER JOIN KWARTALY KW ON EXISTS (SELECT * FROM STATUS_PLATNIKA SP INNER JOIN STATUS_PLATNIKA_TYP SPT ON SP.ID_STATUS_PLATNIKA_TYP = SPT.ID WHERE SP.ID_KONTRAHENT = K.ID AND SP.DATA_POZYSKANIA <= KD.MAX_ZALICZAJ_DO AND (SPT.FLAGA_SYS IN(1) AND ((SP.DATA_OD <= KW.D_DO) AND (SP.DATA_DO IS NULL OR (SP.DATA_DO >= KW.D_OD ) WHERE NOT EXISTS (SELECT * FROM SPR S WHERE S.ID_KONTRAHENT = K.ID AND S.ID_URZADZENIE = U.ID AND ((EXTRACT(YEAR FROM S.DATA_OD)*12+EXTRACT(MONTH FROM S.DATA_OD)) BETWEEN(KW.ROK*12+KW.MIESIAC_OD) AND (KW.ROK*12+KW.MIESIAC_DO) OR (EXTRACT(YEAR FROM S.DATA_DO)*12+EXTRACT(MONTH FROM S.DATA_DO)) BETWEEN(KW.ROK*12+KW.MIESIAC_OD) AND (KW.ROK*12+KW.MIESIAC_DO)) ) ) AND K.KONT_NR > 0) SQL Message : -508 The cursor identified in the UPDATE or DELETE statement is not positioned on a row. Engine Code: 335544348 Engine Message : no current record for fetch operation if you change last AND K.KONT_NR > 0) to AND K.KONT_NR+0 >
[Firebird-devel] [FB-Tracker] Created: (CORE-6100) Incorrect check for nested CTE
Incorrect check for nested CTE -- Key: CORE-6100 URL: http://tracker.firebirdsql.org/browse/CORE-6100 Project: Firebird Core Issue Type: Bug Components: Engine Affects Versions: 3.0.4, 3.0.5 Reporter: Karol Bieniaszewski Firebird incorrectly check for nested CTE. Below query have not nested CTE WITH X AS ( SELECT 6 AS NUMBER FROM RDB$DATABASE ) SELECT * FROM RDB$RELATIONS R WHERE NOT EXISTS( WITH Y AS ( SELECT 6 AS NUMBER FROM RDB$DATABASE ) SELECT * FROM Y INNER JOIN RDB$RELATION_FIELDS RF ON 1=1 WHERE RF.RDB$RELATION_NAME=R.RDB$RELATION_NAME AND RF.RDB$FIELD_POSITION>Y.NUMBER ) -- This message is automatically generated by JIRA. - If you think it was sent incorrectly contact one of the administrators: http://tracker.firebirdsql.org/secure/Administrators.jspa - For more information on JIRA, see: http://www.atlassian.com/software/jira Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
[Firebird-devel] [FB-Tracker] Created: (CORE-6092) isql -i should stop after first error
isql -i should stop after first error - Key: CORE-6092 URL: http://tracker.firebirdsql.org/browse/CORE-6092 Project: Firebird Core Issue Type: Bug Components: ISQL Reporter: Karol Bieniaszewski currently when we run isql -i and we provide script file name then if inside script is an error isql only report it and ignore and it go to the next script this can be catastrophic in case when next script is dependend on previous one which is near to always true -- This message is automatically generated by JIRA. - If you think it was sent incorrectly contact one of the administrators: http://tracker.firebirdsql.org/secure/Administrators.jspa - For more information on JIRA, see: http://www.atlassian.com/software/jira Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
[Firebird-devel] [FB-Tracker] Created: (CORE-6082) From time to time handle to restore of security3.fdb backup is not released by firebird.exe (using service_mgr)
>From time to time handle to restore of security3.fdb backup is not released by >firebird.exe (using service_mgr) --- Key: CORE-6082 URL: http://tracker.firebirdsql.org/browse/CORE-6082 Project: Firebird Core Issue Type: Bug Components: Engine, GBAK, SVCMGR Affects Versions: 3.0.5 Environment: Windows 2008R2 WI-V3.0.5.33100 Firebird 3.0 Reporter: Karol Bieniaszewski Attachments: FirebirdHandle.png We have automatic backup and restore process of many databases including security3.fdb But from time to time - and only for "security3.fdb.restored", file handle is not released by firebird.exe and cannot be deleted. gbak: time delta gbak:0.000 0.000 opened file D:\backup\security3.bak gbak: ERROR:database D:\restore\security3.fdb.restored already exists. To replace it, use the -REP switch gbak: ERROR:Exiting before completion due to errors gbak:Exiting before completion due to errors the process looks like this: 1. We try to delete previous files (we do not check if this process is succesfull or not) 2. every night we run backup to "security3.bak" we validate the succesfull by "looking" into log file for text "closing file, committing, and finishing" if not present, we write log and also send email to admins 3. next we run restore process and this is also validated by log like in point 2. all was ok and next day process failed on steep 3 as in step 1 security3.fdb.restored was not deleted. We try to delete it but it is looked by the firebird.exe and never released - we have waited few days. restore command looks like "%FB_DIR%\gbak" -service 127.0.0.1:service_mgr -c -v -BUFFERS 131072 -STATISTICS TD -user SYSDBA -password %FB_BACKUP%\security3.bak %FB_RESTORE%\security3.fdb.restored This situation have occured 3 times - and now we report this here. -- This message is automatically generated by JIRA. - If you think it was sent incorrectly contact one of the administrators: http://tracker.firebirdsql.org/secure/Administrators.jspa - For more information on JIRA, see: http://www.atlassian.com/software/jira Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
[Firebird-devel] ODP: Firebird 4: Could not find acceptable ICUlibrary
>> as the field stores the UTC time I know naive but.. If conversion table is not available then if it is stored as utc why not showing it as „15:09:49 UTC”? PS. one more dll is not the problem for me, but maybe for others.. regards, Karol Bieniaszewski Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
[Firebird-devel] [FB-Tracker] Created: (CORE-6079) Like should use expression index if pattern is besed on system function e.g. UPPER
Like should use expression index if pattern is besed on system function e.g.
UPPER
--
Key: CORE-6079
URL: http://tracker.firebirdsql.org/browse/CORE-6079
Project: Firebird Core
Issue Type: Improvement
Components: Engine
Reporter: Karol Bieniaszewski
CREATE TABLE NAMES
(
NAME VARCHAR(100)
);
commit;
INSERT INTO names
SELECT RF.RDB$FIELD_NAME FROM RDB$RELATION_FIELDS RF;
commit;
CREATE ASCENDING INDEX IXAE_NAMES__NAME ON NAMES COMPUTED BY(UPPER(NAME COLLATE
PXW_PLK));
commit;
SELECT * FROM NAMES N WHERE UPPER(N.NAME COLLATE PXW_PLK) LIKE
'RDB%SYSTEM_FLAG%'
PLAN (N INDEX (IXAE_NAMES__NAME))
this works as expected
SELECT * FROM NAMES N WHERE UPPER(N.NAME COLLATE PXW_PLK) LIKE
UPPER('RDB%SYSTEM_FLAG%')
PLAN (N NATURAL)
but this one not - because of function used in LIKE pattern.
I understand current logic, because someone can use custom function and change
order of characters.
But system function UPPER does not change anything in like pattern, and index
still can be used.
I do not know which other (only system) functions can be used this way but
UPPER and LOWER are obvious one.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://tracker.firebirdsql.org/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
[Firebird-devel] [FB-Tracker] Created: (CORE-6062) Install incomplete - add steps to the info
Install incomplete - add steps to the info -- Key: CORE-6062 URL: http://tracker.firebirdsql.org/browse/CORE-6062 Project: Firebird Core Issue Type: Improvement Components: Engine Affects Versions: 4.0 Beta 1, 3.0.4 Reporter: Karol Bieniaszewski Now when you have fresh Firebird installation - you got an error Engine Code: 335545029 Engine Message : Install incomplete, please read the Compatibility chapter in the release notes for this version Can this be improved by adding steps here: "Install incomplete, please read the Compatibility chapter in the release notes for this version. You can use this steps also: stop firebird service if it is running and run isql from command line isql -user sysdba employe SQL> create user SYSDBA password 'SomethingCryptic'; SQL> commit; SQL> quit; " I know that this is error message and to more info is not ok - but this is some special one. This is important especially for new users, but i like to see it also - as i never remember steps. And many times google search show me the sample with gsec instead of isql and it not work anymore. -- This message is automatically generated by JIRA. - If you think it was sent incorrectly contact one of the administrators: http://tracker.firebirdsql.org/secure/Administrators.jspa - For more information on JIRA, see: http://www.atlassian.com/software/jira Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
[Firebird-devel] ODP: Open-sourcing F14 for memory-efficient hash tables- Facebook Code
Thank you for pointing this out. Really good description in the article Regards, Karol Bieniaszewski Od: Adriano dos Santos Fernandes Wysłano: piątek, 26 kwietnia 2019 18:23 Do: For discussion among Firebird Developers Temat: [Firebird-devel] Open-sourcing F14 for memory-efficient hash tables- Facebook Code https://code.fb.com/developer-tools/f14/ Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
[Firebird-devel] [FB-Tracker] Created: (CORE-6030) Provide running counters in the explained plan
Provide running counters in the explained plan -- Key: CORE-6030 URL: http://tracker.firebirdsql.org/browse/CORE-6030 Project: Firebird Core Issue Type: New Feature Reporter: Karol Bieniaszewski It will be good if we can obtain running plan with counters. Now we have statistics in monitoring tables, but in complicated case it is hard to say where this values was consumed in the query. Especially it can be hard if the table is involved many times in same query. I know that query can be retrived in partial fetches, and this feature should support this. When we fetch next record packets, statistics should be increased and plan can be retrived with more recent values and so on. This feature is releated to CORE-6029 -- This message is automatically generated by JIRA. - If you think it was sent incorrectly contact one of the administrators: http://tracker.firebirdsql.org/secure/Administrators.jspa - For more information on JIRA, see: http://www.atlassian.com/software/jira Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
[Firebird-devel] [FB-Tracker] Created: (CORE-6024) FB3.0.4.33063 vs FB3.0.5.33100 manual plan cause "index cannot be used in the specified plan"
FB3.0.4.33063 vs FB3.0.5.33100 manual plan cause "index cannot be used in the specified plan" - Key: CORE-6024 URL: http://tracker.firebirdsql.org/browse/CORE-6024 Project: Firebird Core Issue Type: Bug Environment: Windows 2008R2 FB3.0.5.33100 Reporter: Karol Bieniaszewski Something was broken between versions FB3.0.4.33063 vs FB3.0.5.33100 Under FB3.0.4.33063 there was possibility to specify ordered plan for this query now in FB3.0.5.33100 it is not possible. Also look why this ordered plan is not default generated by the engine. CREATE TABLE WPLATA ( DYR_ID Smallint NOT NULL, OKRES_NUMER Char(7) NOT NULL, INSP_ID Smallint NOT NULL, KONTO_ID Smallint NOT NULL, WPLATA_DATA_WYCIAGU Date NOT NULL, WPLATA_NR_WYCIAGU Varchar(10) NOT NULL, WPLATA_NR_POZYCJI Smallint NOT NULL, CONSTRAINT PK_WPLATA PRIMARY KEY (DYR_ID,INSP_ID,KONTO_ID,WPLATA_DATA_WYCIAGU,WPLATA_NR_WYCIAGU,WPLATA_NR_POZYCJI) ); CREATE INDEX IXA_WPLATA__KONTRAHENT__PK ON WPLATA (WPLATA_KONTRAHENT_ID,DYR_ID); - SELECT W.DYR_ID , W.INSP_ID , W.KONTO_ID , W.WPLATA_DATA_WYCIAGU , W.WPLATA_DATA_WPLATY , W.WPLATA_NR_WYCIAGU , W.WPLATA_NR_POZYCJI FROM WPLATA W WHERE W.WPLATA_KONTRAHENT_ID IN (1452) AND W.DYR_ID = 6 PLAN(W ORDER PK_WPLATA INDEX(IXA_WPLATA__KONTRAHENT__PK)) ORDER BY W.DYR_ID , W.INSP_ID , W.KONTO_ID , W.WPLATA_DATA_WYCIAGU , W.WPLATA_NR_WYCIAGU , W.WPLATA_NR_POZYCJI - index PK_WPLATA cannot be used in the specified plan. engine generate PLAN SORT (W INDEX (IXA_WPLATA__KONTRAHENT__PK)) which is not efficient -- This message is automatically generated by JIRA. - If you think it was sent incorrectly contact one of the administrators: http://tracker.firebirdsql.org/secure/Administrators.jspa - For more information on JIRA, see: http://www.atlassian.com/software/jira Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
[Firebird-devel] Interesting OLTP results on RAM Disk and question about
Hi. I resend and shortened this email because previous emails are gone on the list ☹ I set first only 2048 buffers as i supposed buffers does not matter on RAM DISK because read from file is really read from RAM. But to my surprise (maybe not so big surprise) the results are >3 times slower (vs cache 262144 buffers). Is this overhead because of using Windows file read API compared to direct read from cache or something else involved? This can be also because of cache processing. You know, if something is not in the cache, it must be read by Windows file API (here from memory). Then old data must be removed from cache and new one stored in the cache. Can someone profile this and catch where time is spend? On Win API or on cache storing/removing or somewhere else? PS> FB4 looks faster than FB3 ~7% - i test only one time FB4 then i must retest it. 48 isql sessions Server version: WI-T4.0.0.1435 Firebird 4.0 Beta 1 SUCCESSFUL TIMES DONE: 282094 vs 901743 Regards, Karol Bieniaszewski Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
[Firebird-devel] Interesting OLTP results on RAM Disk - question about results
I set first only 2048 buffers as i supposed buffers does not matter on RAM DISK because read from file is really read from RAM. But to my surprise (maybe not so big surprise) the results are as >3 times slower. Is this overhead becouse of using Windows file read api compared to direct read from cache or something else involved? PS> FB4 looks faster than FB3 ~7% - i test only one time FB4 then i must retest it. 48 isql sessions Server version: WI-T4.0.0.1435 Firebird 4.0 Beta 1 FB_ARCHITECTURE DB_NAME FORCED_WRITES SWEEP_INT PAGE_BUFFERS PAGE_SIZE SuperServer 4.0.0 P:\DB\OLTP4.FDB ON 2 2048 8192 ACTION AVG_TIMES_PER_MINUTE AVG_ELAPSED_MS SUCCESSFUL_TIMES_DONE JOB_BEG JOB_END *** OVERALL *** for 60 minutes: 4701.57 9891 282094 2019-02-16 18:35 2019-02-16 19:35 FB_ARCHITECTURE DB_NAME FORCED_WRITES SWEEP_INT PAGE_BUFFERS PAGE_SIZE SuperServer 4.0.0 P:\DB\OLTP4.FDB ON 2 262144 819 ACTION AVG_TIMES_PER_MINUTE AVG_ELAPSED_MS SUCCESSFUL_TIMES_DONE JOB_BEG JOB_END *** OVERALL *** for 60 minutes: 15029.05 5049 901743 2019-02-16 21:45 2019-02-16 22:45 For comparision 60 minutes tests for FB3 and FB2.5 FB_ARCHITECTURE DB_NAME FORCED_WRITES SWEEP_INT PAGE_BUFFERS PAGE_SIZE SuperServer 3.0.5 P:\DB\OLTP3.FDB ON 2 262144 8192 ACTION AVG_TIMES_PER_MINUTE AVG_ELAPSED_MS SUCCESSFUL_TIMES_DONE JOB_BEG JOB_END *** OVERALL *** for 60 minutes: 14005.30 5365 840318 2019-02-11 21:21 2019-02-11 22:21 FB_ARCHITECTURE DB_NAME FORCED_WRITES SWEEP_INT PAGE_BUFFERS PAGE_SIZE Classic 2.5.9 P:\DB\OLTP2.FDB ON 2 12288 8192 ACTION AVG_TIMES_PER_MINUTE AVG_ELAPSED_MS SUCCESSFUL_TIMES_DONE JOB_BEG JOB_END *** OVERALL *** for 60 minutes: 1164.40 48244 69864 2019-02-12 18:41 2019-02-12 19:41 FB_ARCHITECTURE DB_NAME FORCED_WRITES SWEEP_INT PAGE_BUFFERS PAGE_SIZE SuperClassic 2.5.9 P:\DB\OLTP2.FDB ON 2 12288 8192 ACTION AVG_TIMES_PER_MINUTE AVG_ELAPSED_MS SUCCESSFUL_TIMES_DONE JOB_BEG JOB_END *** OVERALL *** for 60 minutes: 6176.02 16168 370561 2019-02-12 21:35 2019-02-12 22:35 FB_ARCHITECTURE DB_NAME FORCED_WRITES SWEEP_INT PAGE_BUFFERS PAGE_SIZE SuperServer 2.5.9 P:\DB\OLTP2.FDB ON 2 262144 8192 ACTION AVG_TIMES_PER_MINUTE AVG_ELAPSED_MS SUCCESSFUL_TIMES_DONE JOB_BEG JOB_END *** OVERALL *** for 60 minutes: 1263.35 46980 75801 2019-02-13 00:07 2019-02-13 01:07 Regards, Karol Bieniaszewski Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
[Firebird-devel] ODP: Latest OLTP results DB size resolved
>> I thought that OLTP tests operated on a fixed dataset size. Starting point is the same record count, but during test, new documents are created. And if server is faster (and better SMP) then more new documents are created. Regards, Karol Bieniaszewski Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
[Firebird-devel] Latest OLTP results DB size resolved
>> Can you execute same SS (2.5 vs 3.0) tests with 5 ISQL sessions? I do not know what purpose for only 5 ISQL but here you are. As suppsed, more succesfull requests – smaller numer of conflicts as only 5 ISQL. FB3 wins unquestionably. Server version: WI-V3.0.5.33100 Firebird 3.0 resulted database size: 21,8 GB (23 501 996 032) Records count: 151 178 165 FB_ARCHITECTURE DB_NAME FORCED_WRITES SWEEP_INT PAGE_BUFFERS PAGE_SIZE SuperServer 3.0.5 P:\DB\OLTP3.FDB ON 2 262144 8192 ACTION AVG_TIMES_PER_MINUTE AVG_ELAPSED_MS SUCCESSFUL_TIMES_DONE JOB_BEG JOB_END *** OVERALL *** for 60 minutes: 16158.17 653 969490 2019-02-14 17:54 2019-02-14 18:54 Server version: WI-V2.5.9.27127 Firebird 2.5 Resulted database size: 5,15 GB (5 531 705 344) Records count: 36 316 350 FB_ARCHITECTURE DB_NAME FORCED_WRITES SWEEP_INT PAGE_BUFFERS PAGE_SIZE SuperServer 2.5.9 P:\DB\OLTP2.FDB ON 2 262144 8192 ACTION AVG_TIMES_PER_MINUTE AVG_ELAPSED_MS SUCCESSFUL_TIMES_DONE JOB_BEG JOB_END *** OVERALL *** for 60 minutes: 6414.48 1786 384869 2019-02-14 19:56 2019-02-14 20:56 Regards, Karol Bieniaszewski Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
[Firebird-devel] Latest OLTP results
If someone is interested. Newest results on recent snapshots. 60 minutes test. FB3 SuperServer is the best always. I have repeated test 3 times and near same results. Because of that i use lastest results. Now i know why DB size have so big difference. E.g. FB25 SuperClassic 370561 times vs FB3 SS 840318. Two Times bigger result. But overall records in resulted databases are 34 705 188 vs 129 473 418. This is near 4 times bigger count! FB3 for me is a leader in all tests. FB_ARCHITECTURE DB_NAME FORCED_WRITES SWEEP_INT PAGE_BUFFERS PAGE_SIZE SuperServer 3.0.5 P:\DB\OLTP3.FDB ON 2 262144 8192 ACTION AVG_TIMES_PER_MINUTE AVG_ELAPSED_MS SUCCESSFUL_TIMES_DONE JOB_BEG JOB_END *** OVERALL *** for 60 minutes: 14005.30 5365 840318 2019-02-11 21:21 2019-02-11 22:21 FB_ARCHITECTURE DB_NAME FORCED_WRITES SWEEP_INT PAGE_BUFFERS PAGE_SIZE Classic 2.5.9 P:\DB\OLTP2.FDB ON 2 12288 8192 ACTION AVG_TIMES_PER_MINUTE AVG_ELAPSED_MS SUCCESSFUL_TIMES_DONE JOB_BEG JOB_END *** OVERALL *** for 60 minutes: 1164.40 48244 69864 2019-02-12 18:41 2019-02-12 19:41 FB_ARCHITECTURE DB_NAME FORCED_WRITES SWEEP_INT PAGE_BUFFERS PAGE_SIZE SuperClassic 2.5.9 P:\DB\OLTP2.FDB ON 2 12288 8192 ACTION AVG_TIMES_PER_MINUTE AVG_ELAPSED_MS SUCCESSFUL_TIMES_DONE JOB_BEG JOB_END *** OVERALL *** for 60 minutes: 6176.02 16168 370561 2019-02-12 21:35 2019-02-12 22:35 FB_ARCHITECTURE DB_NAME FORCED_WRITES SWEEP_INT PAGE_BUFFERS PAGE_SIZE SuperServer 2.5.9 P:\DB\OLTP2.FDB ON 2 262144 8192 ACTION AVG_TIMES_PER_MINUTE AVG_ELAPSED_MS SUCCESSFUL_TIMES_DONE JOB_BEG JOB_END *** OVERALL *** for 60 minutes: 1263.35 46980 75801 2019-02-13 00:07 2019-02-13 01:07 Regards, Karol Bieniaszewski Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
[Firebird-devel] ODP: ODP: ODP: CORE-5997
>>I prefer if PSQL switches from begin/end to braces, it's much simple, >>readable and compact, but it's not going to be an acceptable change. I see no problem with Pascal style Begin and End. >>Brackets are already an array index operator in Firebird. >>Adriano This is not problem in Delphi, then i suppose no problem in Firebird too. User should know what he do and on what data. e.g. a[5][2] can be one dimension array (index 5) of Varchar (second char) – no problem for me. Regards, Karol Bieniaszewski Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
[Firebird-devel] ODP: ODP: CORE-5997
>>You ask for wrong thing. Instead of ask for a non standard feature that >>no DBMS made, and that completely changes SQL, you'd better ask for >>optimized PSQL execution. >>PSQL engine has none optimization currently. >>Adriano If this change something in string allocation on every change, than maybe it is the way. But this is only one point of this request. Other point is simple string manipulation. Compare: -- VAR_S = VAR_A[5] || VAR_A[3] || VAR_A[1]; Vs VAR_S = SUBSTRING(VAR_A FROM 5 FOR 1) || SUBSTRING(VAR_A FROM 3 FOR 1) || SUBSTRING(VAR_A FROM 1 FOR 1); -- Or better sample VAR_S[5] = VAR_A[3]; VAR_S[3] = VAR_A[5]; Vs VAR_S = SUBSTRING(VAR_S FROM 1 FOR 2) || SUBSTRING(VAR_A FROM 5 FOR 1) || SUBSTRING(VAR_S FROM 4 FOR 1) || SUBSTRING(VAR_A FROM 3 FOR 1) || SUBSTRING(VAR_S FROM 6); -- And i only show some simple sample. How many times you can put wrong calculation in above sample? Especially in the second one? Regards, Karol Bieniaszewski Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
[Firebird-devel] ODP: CORE-5997
>>I.e. I assume that this problem is raised due to UDR deprecation? >>Why not use UDR (written using any language you like) for complex >>strings manipulatons? Not only. Previously there was no possibility to have PSQL functions and udf was the only option. The main reason to change udf is that, psql function is defined in the database itself and go with it to other servers. Udf/udr must be copied to destination server which most of the time require server stop/start. It also require in some situation to have some function which provide version of udf lib. And this version must be checked if it is compatibile with current version of the database (structure). >>I do not want to say that I hate your idea in general. But IMO in >>curretn state it's not ready for implementation. I understand that this require implementation time and team resources. And as any feature it require future maitenance. But without this, speed of string manipulations is not efficient and database require to have optimized speed in every possible aspect. Regards, Karol Bieniaszewski Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
[Firebird-devel] [FB-Tracker] Created: (CORE-5997) Add simple indexed(positioned) operations on strings (varchar, char) in Triggers, Procedures, functions
Add simple indexed(positioned) operations on strings (varchar, char) in
Triggers, Procedures, functions
---
Key: CORE-5997
URL: http://tracker.firebirdsql.org/browse/CORE-5997
Project: Firebird Core
Issue Type: New Feature
Components: Engine
Reporter: Karol Bieniaszewski
Please implement simple operations on strings in stored procedures, triggers,
functions..
i mean:
s[5] = 'A';
s[5] = s[6];
xxx = s[5] + s[3] + s[1];
instead of "[]" there can be different bracket e.g. "{}" if it collide somehow
with array fields
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://tracker.firebirdsql.org/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
[Firebird-devel] ODP: Performance - 2.5 vs 3.0 vs 4.0
Do you have permanent (dummy) connection to Firebird3 SS database? If not connect to database and then run test. Permanent connection cause to do not clear SuperServer cache. Regards, Karol Bieniaszewski Od: Gabor Boros Wysłano: czwartek, 24 stycznia 2019 10:30 Do: firebird-devel@lists.sourceforge.net Temat: Re: [Firebird-devel] Performance - 2.5 vs 3.0 vs 4.0 2019. 01. 24. 8:27 keltezéssel, Dmitry Yemanov írta: > i.e. v3 finally becomes faster than v2.5, but v4 is slower than v3 > (while faster than v2.5) > > Correct? Yes. But 2.5 SC beats them all. I made a graph from the results. The numbers are seconds and the smaller is the better. Gabor Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
[Firebird-devel] ODP: 32-bit builds check
>> Do not know why but 25% of users continue downloading i86 firebird binaries Because they need fbclient.dll in 32 bit version for 32bit projects Regards, Karol Bieniaszewski Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
[Firebird-devel] ODP: CORE-5888
>> Afraid only after fb4-beta release. Hard to wait but thank you for your honest answer I believe that FB4 beta will be available soon regards, Karol Bieniaszewski Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
[Firebird-devel] CORE-5888
Hi Alex and others. Can you spend some time on fixing CORE-5888 about Android? This is really important to us to have suport of Android (i know, your priorities can be different). As a small incentive, I would say that it would also be very good from a marketing point of view I would be very grateful for that. Regards, Karol Bieniaszewski Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
[Firebird-devel] ODP: ODP: ODP: Some OLTP numbers
Windows 10 64bit. Regards, Karol Bieniaszewski What platform? Windows server and Windows client? Gabor Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
[Firebird-devel] ODP: ODP: Some OLTP numbers
Hi. If someone is interested, now i have compared 3 version of Firebird. The results are promissing as FB4 is the fastest, next FB3. The slowest is FB2.5 – no surprise for me in SS. My numbers for settings: MEDIUM_03 5 docs start FW synch 48 isql SuperServer For FB2.5 i use CpuAffinityMask = 63 WI-V2.5.9.27119 Firebird 2.5 ACTION AVG_TIMES_PER_MINUTE AVG_ELAPSED_MS SUCCESSFUL_TIMES_DONE JOB_BEG JOB_END *** OVERALL *** for 180 minutes: 2106.71 40679 379208 2018-10-29 00:13 2018-10-29 03:13 WI-V3.0.5.33075 Firebird 3.0 ACTION AVG_TIMES_PER_MINUTE AVG_ELAPSED_MS SUCCESSFUL_TIMES_DONE JOB_BEG JOB_END *** OVERALL *** for 180 minutes: 3968.06 16426 714251 2018-10-28 20:00 2018-10-28 23:00 WI-T4.0.0.1249 Firebird 4.0 Alpha 1 ACTION AVG_TIMES_PER_MINUTE AVG_ELAPSED_MS SUCCESSFUL_TIMES_DONE JOB_BEG JOB_END *** OVERALL *** for 180 minutes: 4547,94 15714 823177 2018-10-29 23:59 2018-10-30 03:00 Regards, Karol Bieniaszewski Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
[Firebird-devel] ODP: Firebird4 sysdba install different then in FB3?
Forgot it CREATE !!! not alter first Regards, Karol Bieniaszewski Od: Karol Bieniaszewski Wysłano: poniedziałek, 29 października 2018 18:16 Do: For discussion among Firebird Developers Temat: [Firebird-devel] Firebird4 sysdba install different then in FB3? I can not add sysdba to Firebird4 I do the folowing: P:\>cd Firebird4 P:\Firebird4>isql Use CONNECT or CREATE DATABASE to specify a database SQL> connect employee user SYSDBA password masterkey; Database: employee, User: SYSDBA SQL> alter user sysdba set password 'masterkey'; Statement failed, SQLSTATE = 28000 modify record error -Install incomplete, please read the Compatibility chapter in the release notes for this version SQL> Is this procedure somechow different under FB4 now? regards, Karol Bieniaszewski Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
[Firebird-devel] Firebird4 sysdba install different then in FB3?
I can not add sysdba to Firebird4 I do the folowing: P:\>cd Firebird4 P:\Firebird4>isql Use CONNECT or CREATE DATABASE to specify a database SQL> connect employee user SYSDBA password masterkey; Database: employee, User: SYSDBA SQL> alter user sysdba set password 'masterkey'; Statement failed, SQLSTATE = 28000 modify record error -Install incomplete, please read the Compatibility chapter in the release notes for this version SQL> Is this procedure somechow different under FB4 now? regards, Karol Bieniaszewski Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
[Firebird-devel] ODP: Some OLTP numbers
Hi, My numbers for settings: 5 docs start FW synch 48 isql WI-V2.5.9.27119 Firebird 2.5 ACTION AVG_TIMES_PER_MINUTE AVG_ELAPSED_MS SUCCESSFUL_TIMES_DONE JOB_BEG JOB_END *** OVERALL *** for 180 minutes: 2106.71 40679 379208 2018-10-29 00:13 2018-10-29 03:13 WI-V3.0.5.33075 Firebird 3.0 ACTION AVG_TIMES_PER_MINUTE AVG_ELAPSED_MS SUCCESSFUL_TIMES_DONE JOB_BEG JOB_END *** OVERALL *** for 180 minutes: 3968.06 16426 714251 2018-10-28 20:00 2018-10-28 23:00 Regards, Karol Bieniaszewski Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
[Firebird-devel] ODP: ODP: Internal Firebird consistency after 10minutesof OLTP test
Still no luck but.. I have analysed previous log and i found interesting entry DESKTOP-6BKOVL9 Sat Oct 27 13:29:19 2018 I/O error during "ReadFile" operation for file "C:\WINDOWS\TEMP\FB_TABLE_5FBVM1" Error while trying to read from file Nieprawidłowe dojście. but in Firebird.conf i have this configuration TempDirectories = O:\temp Why Firebird have used C:\WINDOWS\TEMP which is my old SSD, still working but it is not so good. Only when i create FIREBIRD_TMP it did not create any file in C:\WINDOWS\TEMP directory. I can’t figure out why it prefere Windows temp instead.. Maybe in some point FB eat whole space (20GB) of O:\temp and go there. DB size was 7GB. But in all current tests FB never go to C:\WINDOWS\TEMP. Maybe the reason of bugcheck was releated to my SSD use… Regards, Karol Bieniaszewski Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
[Firebird-devel] ODP: Internal Firebird consistency after 10 minutesof OLTP test
This time no luck. All work ok for 3 hours. Whole test without any problems. I will investigate this more. If i found something i will back here. regards, Karol Bieniaszewski Od: Dimitry Sibiryakov Wysłano: sobota, 27 października 2018 15:35 Do: firebird-devel@lists.sourceforge.net Temat: Re: [Firebird-devel] Internal Firebird consistency after 10 minutesof OLTP test 27.10.2018 14:28, Karol Bieniaszewski wrote: > I do not have core dump because engine did not crashed. > What next? Make it crashing by setting of BugcheckAbort in firebird.conf. -- WBR, SD. Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
[Firebird-devel] Internal Firebird consistency after 10 minutes of OLTP test
Hi, I got „internal Firebird consistency” after 10 minutes of test of todays Firebird 3 snapshot. I use: MEDIUM_03 48 of isql 100k documents Database file size after ~7GB DESKTOP-6BKOVL9 Sat Oct 27 13:29:19 2018 Database: O:\DB\OLTP3.FDB internal Firebird consistency check (wrong record length (183), file: vio.cpp line: 1430) DESKTOP-6BKOVL9 Sat Oct 27 13:29:19 2018 Database: O:\DB\OLTP3.FDB internal Firebird consistency check (wrong record length (183), file: vio.cpp line: 1430) internal Firebird consistency check (can't continue after bugcheck) I have tried gfix -validate -full but it run 1 hour and do not return and do not write more info into log. In proces manager i se that it read only 1 921 600 bytes but it still increassing really slow. I do not have core dump because engine did not crashed. What next? regards, Karol Bieniaszewski Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
[Firebird-devel] ODP: OLTP run problem
Great tip! Thats it. Thank you Regards, Karol Bieniaszewski Od: Dimitry Sibiryakov Wysłano: sobota, 27 października 2018 11:31 Do: firebird-devel@lists.sourceforge.net Temat: Re: [Firebird-devel] OLTP run problem 27.10.2018 10:49, Karol Bieniaszewski wrote: > What can i do to fix this? Make sure that you checkout-ed the tree with platform-specific line ending. -- WBR, SD. Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
[Firebird-devel] OLTP run problem
Hi, I try to run OLTP on Windows 10 64 bit and Firebird 3 current snapshot When i try to run it i got an error -- Database has been created SUCCESSFULLY and is ready for initial documents filling. ## Change config setting 'wait_after_create' to 0 in order to remove this pause. Press any key to go on or Ctrl-C to exit. . . Config parameter 'use_external_to_stop' is UNDEFINED (this is DEFAULT). SKIP checking for non-empty external file. 10:30:18,46 Internal routine: count_existing_docs. The system cannot find the batch label specified - repl_with_bound_quotes The system cannot find the batch label specified - repl_with_bound_quotes The system cannot find the batch label specified - repl_with_bound_quotes The system cannot find the batch label specified - repl_with_bound_quotes The system cannot find the batch label specified - repl_with_bound_quotes ### ATTENTION ### FAILED to run script which tries to add 'signal' record into log that will be used to evaluate planning finish time. Command: O:\Firebird3\isql 127.0.0.1/3305:O:\DB\oltp3.fdb -user SYSDBA -password masterkey -n -i O:\logs\tmp_init_data_chk.sql SQL script: O:\logs\tmp_init_data_chk.sql Content of error log (O:\logs\tmp_init_data_chk.err): === Statement failed, SQLSTATE = 42S02 Dynamic SQL Error -SQL error code = -204 -Table unknown -UNKNOWN_TABLE -At line 18, column 21 At line 22 in file O:\logs\tmp_init_data_chk.sql === See details in file O:\logs\oltp30.prepare.log Press any key to FINISH this batch. . . -- What can i do to fix this? I see that this table realy does not exists win resulted database PS> i have addded many wait commands and echo to your script, because without it, script failed many times (some timings problem or Windows 10 releated problem). Too fast computer… Regards, Karol Bieniaszewski Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
[Firebird-devel] ODP: Some OLTP numbers
Where can i find this OLTP stress test software I like to see my numbers Regards, Karol Bieniaszewski Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
[Firebird-devel] [FB-Tracker] Created: (CORE-5941) Altering non existing trigger with OLD. variable cause "Column unknown" instead "Trigger not found"
Altering non existing trigger with OLD. variable cause "Column unknown" instead
"Trigger not found"
---
Key: CORE-5941
URL: http://tracker.firebirdsql.org/browse/CORE-5941
Project: Firebird Core
Issue Type: Bug
Components: Engine
Affects Versions: 3.0.4, 3.0.5
Reporter: Karol Bieniaszewski
try altering not existing trigger
SET TERM ^ ;
ALTER TRIGGER XYZXX
ACTIVE AFTER DELETE POSITION 1000
AS
DECLARE VARIABLE VAR_OLD_UMOWA_ID CHAR(7);
begin
END^
SET TERM ; ^
as expected:
Engine Code: 335544351
Engine Message :
unsuccessful metadata update
ALTER TRIGGER XYZXX failed
Trigger XYZXX not found
add reference to OLD variable in the not existing trigger
SET TERM ^ ;
ALTER TRIGGER XYZXX
ACTIVE AFTER DELETE POSITION 1000
AS
DECLARE VARIABLE VAR_OLD_UMOWA_ID CHAR(7);
begin
VAR_OLD_UMOWA_ID = OLD.UMOWA_ID;
END^
SET TERM ; ^
Engine Code: 335544351
Engine Message :
unsuccessful metadata update
ALTER TRIGGER XYZXX failed
Dynamic SQL Error
SQL error code = -206
Column unknown
OLD.UMOWA_ID
but the main reason is that this trigger does not exists at all
the body does not matter here
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://tracker.firebirdsql.org/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
[Firebird-devel] ODP: User-defined aggregate functions
Hi, I know that i am not Firebird core developer and I have less influence. But, why do you still say about WHILE, SUSSPEND, YIELD, FETCH if there is simple alternative to not have it at all? Can someone tell me what is wrong with my proposition? -- Instead of simple create aggregate function custom_sum (i integer) returns (o integer) as begin if (i is not null) then begin if (o is null) then o = 0; o = o + i; end end you propose something like this create aggregate function custom_sum (i integer) returns (o integer) as begin while (not agg_finished) do begin if (i is not null) then begin if (o is null) then o = 0; o = o + i; end suspend; end end -- instead of simple: create aggregate function custom_avg (i double precision) returns (o double precision) as declare accumulated double precision = 0; begin if (i is not null) then accumulated = accumulated + i; if (agg_result_needed and (accumulated is not null) then o = accumulated / agg_i; end you propose: create aggregate function custom_avg (i double precision) returns (o double precision) as declare count integer = 0; declare accumulated double precision = 0; begin while (not agg_finished) do begin if (i is not null) then begin count = count + 1; accumulated = accumulated + i; o = accumulated / count; end suspend; end end -- Can you tell me also how you prevent: - Infinite while do (i know user should use it with care but why increase risk?) - that user do not write SUSPEND/YIELD/FETCH or user call it to many times? - minimize code execution like in avg? Instead of make one division you propose design where you must divide it always row by row. And e.g. if you have 1000 rows with only one grouping key, you have 1000 of additions and 1000 of divisions. -- How work internal SUM, AVG already? They call yead/fetch/suspend or something like this? Or engine send them required state of parameters/variables? regards, Karol Bieniaszewski Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
[Firebird-devel] ODP: ODP: User-defined aggregate functions
>>- A clause to ignore null input values. If used, engine will filter out null >>input and not pass to the routine. >> This need to declare what.parameters shluld be considered I have thinked more about this and i can not imagine how this can work with function which have more then one parameter? e.g first param will have ignore null and second not. What walue will have then first parameter when we write ignore null for it? Pozdrawiam, Karol Bieniaszewski Od: Karol Bieniaszewski Wysłano: niedziela, 30 września 2018 09:01 Do: For discussion among Firebird Developers Temat: [Firebird-devel] ODP: User-defined aggregate functions >>I think four adjustments makes the initial syntax better: >>- Allow usage of more than one input parameter. Maybe even zero would be >>allowed. ok >> Instead of have special semantics for empty data source, declare the >> constant to be returned for it, say: >> create aggregate function my_count returning 0 for empty data source If >> this clause is omitted, null is returned for empty data source. This will limit functionality. What if you need to return something based on input parameters values? Not real sample but SELECT (SELECT CUSTOM_AGG(T2.A, T1.B, T1.C) FROM T2 WHERE T2.X=T1.Y) FROM T1 And you need to return e.g. T1.B*T1.C if there is empty resultset. With declarative value this will be not possible. >>- A clause to ignore null input values. If used, engine will filter out null >>input and not pass to the routine. This need to declare what.parameters >>shluld be considered This will be over complicated. And have same problem as above. >>- Instead of agg_finished, adjust SUSPEND (probably with another keyword) to >>somehing like: >>SUSPEND WHEN FETCHED DO WHEN FINISHED DO This is only my humble opinion, but i do not like suspend concept here. Engine know when provide values and when it need result from agg. See my previous email (with „Agg_result_needed”) and criticize it if you see any problems there. regards, Karol Bieniaszewski Od: Adriano dos Santos Fernandes Wysłano: niedziela, 30 września 2018 04:52 Do: For discussion among Firebird Developers Temat: Re: [Firebird-devel] User-defined aggregate functions I think four adjustments makes the initial syntax better: - Allow usage of more than one input parameter. Maybe even zero would be allowed. - Instead of have special semantics for empty data source, declare the constant to be returned for it, say: create aggregate function my_count returning 0 for empty data source If this clause is omitted, null is returned for empty data source. - A clause to ignore null input values. If used, engine will filter out null input and not pass to the routine. This need to declare what.parameters shluld be considered - Instead of agg_finished, adjust SUSPEND (probably with another keyword) to somehing like: SUSPEND WHEN FETCHED DO WHEN FINISHED DO Adriano (Sorry to not be more detailed, I'm writing from smartphone) Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
[Firebird-devel] ODP: User-defined aggregate functions
>>I think four adjustments makes the initial syntax better: >>- Allow usage of more than one input parameter. Maybe even zero would be >>allowed. ok >> Instead of have special semantics for empty data source, declare the >> constant to be returned for it, say: >> create aggregate function my_count returning 0 for empty data source If >> this clause is omitted, null is returned for empty data source. This will limit functionality. What if you need to return something based on input parameters values? Not real sample but SELECT (SELECT CUSTOM_AGG(T2.A, T1.B, T1.C) FROM T2 WHERE T2.X=T1.Y) FROM T1 And you need to return e.g. T1.B*T1.C if there is empty resultset. With declarative value this will be not possible. >>- A clause to ignore null input values. If used, engine will filter out null >>input and not pass to the routine. This need to declare what.parameters >>shluld be considered This will be over complicated. And have same problem as above. >>- Instead of agg_finished, adjust SUSPEND (probably with another keyword) to >>somehing like: >>SUSPEND WHEN FETCHED DO WHEN FINISHED DO This is only my humble opinion, but i do not like suspend concept here. Engine know when provide values and when it need result from agg. See my previous email (with „Agg_result_needed”) and criticize it if you see any problems there. regards, Karol Bieniaszewski Od: Adriano dos Santos Fernandes Wysłano: niedziela, 30 września 2018 04:52 Do: For discussion among Firebird Developers Temat: Re: [Firebird-devel] User-defined aggregate functions I think four adjustments makes the initial syntax better: - Allow usage of more than one input parameter. Maybe even zero would be allowed. - Instead of have special semantics for empty data source, declare the constant to be returned for it, say: create aggregate function my_count returning 0 for empty data source If this clause is omitted, null is returned for empty data source. - A clause to ignore null input values. If used, engine will filter out null input and not pass to the routine. This need to declare what.parameters shluld be considered - Instead of agg_finished, adjust SUSPEND (probably with another keyword) to somehing like: SUSPEND WHEN FETCHED DO WHEN FINISHED DO Adriano (Sorry to not be more detailed, I'm writing from smartphone) Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
[Firebird-devel] ODP: ODP: ODP: User-defined aggregate functions
Hi, >From optimization POV one more system var should be provided Agg_result_needed true when engine need result of calculation false if not. sample with avg: create aggregate function custom_avg (i double precision) returns double precision as declare accumulated double precision = 0; begin if (i is not null) then accumulated = accumulated + i; if (Agg_result_needed and (agg_i>0)) then RETURN accumulated / agg_i; end Why it is needed. e.g. table with 1000 records query SELECT AVG(FIELD) FROM TABLE need result only after all 1000 records are parsed. One thousand additions and only one division. Without it we will have 1000 additions and 1000 divisions. For query like SELECT AVG(FIELD) FROM TABLE GROUP BY FLDX We will have same amount of divisions as group keys. For window function also not problem. „Yeld” is really not needed because the database engine controls calls not the function itself. Engine know when agg_finished is true/false and when it need result of agg (e.g. intermediate result in window function). „While” is also not needed here. Do you see any problem with this implementation? Speed, implementation in Firebird, udr, user POV? Regards, Karol Bieniaszewski Od: Karol Bieniaszewski Wysłano: sobota, 29 września 2018 19:29 Temat: [Firebird-devel] ODP: ODP: User-defined aggregate functions >>Your syntax looks too much like a normal function, which I think is >>confusing. For me this is positive aspect. Why do you need something really different. >>I also don't see how your syntax discerns between accumulation and >>finishing, for example how would the value of `accumulated` in >>`custom_avg` be retained? Accumulated is declared as variable and is initialized by engine at start of every grouping level (key) Consider: Engine call Declare variable section and store variables in memory It then call function for every row and by agg_i inform function if this is first row, next row, empty resultset or it is agg_finished. >>How will your proposal work when the aggregate function is used in for >>example a window function with an order by? In that case intermediate >>results are needed. >>Consider for example the difference between `count(*) over()` and >> `count(*) over(order by something)`, or say something like (Firebird 4) >>`avg(something) over(order by something rows between 5 preceding and 5 >>following)`. Good point. But i know how to suport this simple – really one change needed. Below Avg which can work with window also create aggregate function custom_avg (i double precision) returns double precision as declare accumulated double precision = 0; begin if (i is not null) then accumulated = accumulated + i; if (agg_i>0) then RETURN accumulated / agg_i; end engine will decide if it need consume RESULT or not. E.g. in normal SELECT AVG(FIELD) FROM TABLE engine will use it only on agg_finished In window, if engine need to have intermediate value it use value assigned in RETURN. Still really simple. Do you see more problems with it? Udr will also work simple with this, it only must be informed about agg_i and agg_finished by param to udr function. Pozdrawiam, Karol Bieniaszewski Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
[Firebird-devel] ODP: ODP: User-defined aggregate functions
>>Your syntax looks too much like a normal function, which I think is >>confusing. For me this is positive aspect. Why do you need something really different. >>I also don't see how your syntax discerns between accumulation and >>finishing, for example how would the value of `accumulated` in >>`custom_avg` be retained? Accumulated is declared as variable and is initialized by engine at start of every grouping level (key) Consider: Engine call Declare variable section and store variables in memory It then call function for every row and by agg_i inform function if this is first row, next row, empty resultset or it is agg_finished. >>How will your proposal work when the aggregate function is used in for >>example a window function with an order by? In that case intermediate >>results are needed. >>Consider for example the difference between `count(*) over()` and >> `count(*) over(order by something)`, or say something like (Firebird 4) >>`avg(something) over(order by something rows between 5 preceding and 5 >>following)`. Good point. But i know how to suport this simple – really one change needed. Below Avg which can work with window also create aggregate function custom_avg (i double precision) returns double precision as declare accumulated double precision = 0; begin if (i is not null) then accumulated = accumulated + i; if (agg_i>0) then RETURN accumulated / agg_i; end engine will decide if it need consume RESULT or not. E.g. in normal SELECT AVG(FIELD) FROM TABLE engine will use it only on agg_finished In window, if engine need to have intermediate value it use value assigned in RETURN. Still really simple. Do you see more problems with it? Udr will also work simple with this, it only must be informed about agg_i and agg_finished by param to udr function. Pozdrawiam, Karol Bieniaszewski Od: Mark Rotteveel Wysłano: sobota, 29 września 2018 17:46 Do: firebird-devel@lists.sourceforge.net Temat: Re: [Firebird-devel] ODP: User-defined aggregate functions On 29-9-2018 16:46, Karol Bieniaszewski wrote: > Hi, > > Maybe i show my concept not so clearly. > > Look how simple it is with my proposition and also how simple to > understand by users. Your syntax looks too much like a normal function, which I think is confusing. How will your proposal work when the aggregate function is used in for example a window function with an order by? In that case intermediate results are needed. Consider for example the difference between `count(*) over()` and `count(*) over(order by something)`, or say something like (Firebird 4) `avg(something) over(order by something rows between 5 preceding and 5 following)`. I also don't see how your syntax discerns between accumulation and finishing, for example how would the value of `accumulated` in `custom_avg` be retained? Mark -- Mark Rotteveel Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
[Firebird-devel] ODP: User-defined aggregate functions
Hi, Maybe i show my concept not so clearly. Look how simple it is with my proposition and also how simple to understand by users. Adriano, below you can see your samples addapted to my concept. Function is called also for empty resultset once then agg_i=0. Agg_i will control the whole execution choices – for every row it is increased by 1. Vars declaration section is called onece at start of groupung level. this can be simple for use also for udr you can have there udr init for every groupin level – there you declare variables and udr function (body) will be called for every row in grouping level -- Works as standard SUM. create aggregate function custom_sum (i integer) returns (o integer) as begin if agg_i=1 then o = 0; if agg_i>0 then o = o + i; end -- Works as standard AVG. create aggregate function custom_avg (i double precision) returns (o double precision) as declare accumulated double precision = 0; begin if (i is not null) then accumulated = accumulated + i; if (agg_finished and (agg_i>0)) then o = accumulated / agg_i; end -- Works as standard COUNT. create aggregate function custom_count (i integer) returns (o integer) as begin if (agg_finished) then o = agg_i; end -- This function shows the difference of returning value when data set is not empty and returning in function termination when data set is empty. -- select custom_count_plus_1000(1) from rdb$database -- returns 1 -- select custom_count_plus_1000(1) from rdb$database where 1 = 0 -- returns 1000 create aggregate function custom_count_plus_1000 (i integer) returns (o integer) as begin if agg_finished then Begin if agg_i>0 then o = agg_i; else o = 1000; end; end regards, Karol Bieniaszewski Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
[Firebird-devel] ODP: User-defined aggregate functions
Hi, First – good concept But, how this will work? In every WHILE loop, engine will lock loop until next row is „fetched” and susspend is called? I do not know if you understand me? What if function will do not call susspend at all or do not run while loop properly? Maybe this can be done simpler without while do? Assuming that function is in WHILE .. DO .. already and every „call to function” do susspend. Also why limiting to only one parameter? Lets introduce: agg_finished – is true in last row in agg grouping level agg_i – row numer in the current grouping level Lets assume: Variables are „declared” only once per group level only body is called. /* while (not agg_finished) do */ create aggregate function custom_count_plus_1000 (i integer) returns (o integer) as Begin If (agg_i<=1) then Begin --init vars and more o = 0; end; -- here body of this function -- do whatever you want o= o + 1; if (agg_finished) then o = o + 1000; end Maybe you can mix this with your proposition Regards, Karol Bieniaszewski Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
[Firebird-devel] [FB-Tracker] Created: (CORE-5924) Automatic name of the field for RDB$GET_CONTEXT is not generated in the union
Automatic name of the field for RDB$GET_CONTEXT is not generated in the union
-
Key: CORE-5924
URL: http://tracker.firebirdsql.org/browse/CORE-5924
Project: Firebird Core
Issue Type: Bug
Affects Versions: 3.0.3, 4.0 Beta 1, 3.0.4
Reporter: Karol Bieniaszewski
SELECT RDB$GET_CONTEXT('SYSTEM', 'CURRENT_USER'), RDB$GET_CONTEXT('SYSTEM',
'CLIENT_ADDRESS'), R.RDB$CHARACTER_SET_NAME FROM RDB$DATABASE R
UNION
SELECT RDB$GET_CONTEXT('SYSTEM', 'CURRENT_USER'), RDB$GET_CONTEXT('SYSTEM',
'CLIENT_ADDRESS'), R.RDB$CHARACTER_SET_NAME FROM RDB$DATABASE R
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://tracker.firebirdsql.org/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
[Firebird-devel] ODP: CORE-5921
What is then SNAPSHOT_CN? Is this last snapshot commited before my transaction start? Consider sample – what i try to accomplish (get deltas) CREATE TABLE NAMES ( ID integer NOT NULL, NAME varchar(100), TR_ID bigint, CONSTRAINT INTEG_2 PRIMARY KEY (ID) ); CREATE TABLE NAMES__DEL ( ID integer NOT NULL, CONSTRAINT INTEG_4 PRIMARY KEY (ID) ); SET TERM ^ ; CREATE TRIGGER TRIGGAD_NAMES FOR NAMES ACTIVE AFTER delete POSITION 0 AS BEGIN INSERT INTO NAMES__DEL(ID) VALUES(OLD.ID); END^ SET TERM ; ^ SET TERM ^ ; CREATE TRIGGER TRIGGAIU_NAMES FOR NAMES ACTIVE BEFORE insert OR update POSITION 0 AS BEGIN NEW.TR_ID = RDB$RECORD_VERSION; END^ SET TERM ; ^ 1. Start application 2. Start readonly transaction read commited transaction 3. Retrive TransactinStartCN and remember it as last_retrived_data_CN SELECT * FROM NAMES N INNER JOIN RDB$CN RCN ON RCN.TN=N.TR_ID WHERE RCN.TR_CN>=last_retrived_data_CN 4. Repeat point 3 in some interval to get deltas TR_ID field is not needed as we can instead RCN.TN=N.TR_ID use RCN.TN=N.RDB$RECORD_VERSION. But this is for optimization only. We can add index there. And we can add to the WHERE clause „AND RCN.TR_ID>=last_retrived_OAT” This can add suport for real deltas. With only OAT this is not possible as OAT is not so progressive. And in the worst case we can have same value throught whole day (when some long running transaction exists). Then asking for delta will got same resultset as previous delta which is not so usefull. With only SNAPSHOT_CN we have same problem as with OAT. Regards, Karol Bieniaszewski Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
[Firebird-devel] [FB-Tracker] Created: (CORE-5914) Allow NULLS LAST in CREATE INDEX
Allow NULLS LAST in CREATE INDEX Key: CORE-5914 URL: http://tracker.firebirdsql.org/browse/CORE-5914 Project: Firebird Core Issue Type: Improvement Components: Engine Reporter: Karol Bieniaszewski Add posiibility to specify NULLS LAST/NULLS FIRS directive in index creation CREATE ASCENDING INDEX IXA_TABLE_FIELD ON TABLE(FIELD) NULLS LAST; this will help in queries like this SELECT * FROM TABLE ORDER BY FIELD NULLS LAST or SELECT * FROM TABLE WHERE FIELD IS NOT NULL ORDER BY FIELD now plan looks like PLAN (TABLE ORDER IXA_TABLE_FIELD) which is not efficient because first in index are nulls But if table have most of nulls but for e.g. 1% contain data, this plan is inefficient -- This message is automatically generated by JIRA. - If you think it was sent incorrectly contact one of the administrators: http://tracker.firebirdsql.org/secure/Administrators.jspa - For more information on JIRA, see: http://www.atlassian.com/software/jira Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
[Firebird-devel] [FB-Tracker] Created: (CORE-5909) Index only operations
Index only operations - Key: CORE-5909 URL: http://tracker.firebirdsql.org/browse/CORE-5909 Project: Firebird Core Issue Type: New Feature Components: Engine Reporter: Karol Bieniaszewski Will be good to see index only operations in situation like this: table with index on SOURCE_ID and the query SELECT GS.SOURCE_ID, COUNT(*) FROM GAIA_SOURCE GS GROUP BY GS.SOURCE_ID HAVING COUNT(*)>1 should go throught index only without visiting table data pages it is important when working with big databases e.g. index size can be relatively small compared to table itself. In my real database index have only 18GB but table have 720GB. As you can see accessing only 18GB is not the problem - but accessing 720GB is. same in other places join between tables (using indexed fields) without returning any other field values from some joined tables. I suppose this is big change and require REAL TRANSACTIONAL INDEXES. Current indexes benefit only for intensive updating application. -- This message is automatically generated by JIRA. - If you think it was sent incorrectly contact one of the administrators: http://tracker.firebirdsql.org/secure/Administrators.jspa - For more information on JIRA, see: http://www.atlassian.com/software/jira Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
[Firebird-devel] [FB-Tracker] Created: (CORE-5894) Numeric restrictions (datatype declarations) are not checked by engine
Numeric restrictions (datatype declarations) are not checked by engine -- Key: CORE-5894 URL: http://tracker.firebirdsql.org/browse/CORE-5894 Project: Firebird Core Issue Type: Bug Components: Engine Affects Versions: 3.0.4 Environment: WI-V3.0.4.32954 Firebird 3.0 Reporter: Karol Bieniaszewski CREATE TABLE TEST ( F_NUMERIC_2_1 NUMERIC(2,1) , F_NUMERIC_10_2 NUMERIC(10, 2) ); commit; INSERT INTO TEST2 (F_NUMERIC_2_1, F_NUMERIC_10_2) VALUES (1234.5, 123456789012.12); commit; <- no error here select * from TEST2 1234.5 123456789012.12 -- This message is automatically generated by JIRA. - If you think it was sent incorrectly contact one of the administrators: http://tracker.firebirdsql.org/secure/Administrators.jspa - For more information on JIRA, see: http://www.atlassian.com/software/jira -- Check out the vibrant tech community on one of the world's most engaging tech sites, Slashdot.org! http://sdm.link/slashdot Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
[Firebird-devel] [FB-Tracker] Created: (CORE-5888) Firebird server on Android have problem with numerics
Firebird server on Android have problem with numerics - Key: CORE-5888 URL: http://tracker.firebirdsql.org/browse/CORE-5888 Project: Firebird Core Issue Type: Bug Components: Engine Affects Versions: 3.0.3 Environment: Android 8.0 Firebird version: LI-V3.0.3.32900 Reporter: Karol Bieniaszewski Any select from Firebird containing numeric data type cause "Fixed length column [F_NUMERIC] data length mismatch. Value length - [2], column fixed length - [8]" when run from Delphi Firedac. Same select throught firebird client to remote server works ok. It looks like (from Firedac error message) that this is metadata/buffer retrurn problem to the client. -- This message is automatically generated by JIRA. - If you think it was sent incorrectly contact one of the administrators: http://tracker.firebirdsql.org/secure/Administrators.jspa - For more information on JIRA, see: http://www.atlassian.com/software/jira -- Check out the vibrant tech community on one of the world's most engaging tech sites, Slashdot.org! http://sdm.link/slashdot Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
[Firebird-devel] [FB-Tracker] Created: (CORE-5876) Provide name of udf function for "arithmetic exception, numeric overflow, or string truncation"
Provide name of udf function for "arithmetic exception, numeric overflow, or
string truncation"
---
Key: CORE-5876
URL: http://tracker.firebirdsql.org/browse/CORE-5876
Project: Firebird Core
Issue Type: New Feature
Components: Engine
Affects Versions: 3.0.4
Reporter: Karol Bieniaszewski
This is important to know the reason of the error - especially if it is caused
by udf function
DECLARE EXTERNAL FUNCTION SUBSTR
CSTRING(80), Smallint, Smallint
RETURNS CSTRING(80)
ENTRY_POINT 'IB_UDF_substr'
MODULE_NAME 'ib_udf.dll';
-
SELECT SUBSTR(CAST('abc' AS CHAR(1500)) || '123', 1, 1000) FROM RDB$DATABASE
-
SQL Message : -802
Arithmetic overflow or division by zero has occurred.
Engine Code: 335544321
Engine Message :
arithmetic exception, numeric overflow, or string truncation
string right truncation
expected length 80, actual 1503
-
for not udf functions we have name of problematic function "At function 'BBB'
line: 6, col: 1"
SQL Message : -802
Arithmetic overflow or division by zero has occurred.
Engine Code: 335544321
Engine Message :
arithmetic exception, numeric overflow, or string truncation
string right truncation
expected length 100, actual 103
At function 'BBB' line: 6, col: 1
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://tracker.firebirdsql.org/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
--
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
[Firebird-devel] [FB-Tracker] Created: (CORE-5849) Alter function result type and run query can cause server to crash
Alter function result type and run query can cause server to crash
--
Key: CORE-5849
URL: http://tracker.firebirdsql.org/browse/CORE-5849
Project: Firebird Core
Issue Type: Bug
Components: Engine
Affects Versions: 3.0.4
Environment: WI-V3.0.4.32954 Firebird 3.0
Reporter: Karol Bieniaszewski
Priority: Critical
SET TERM ^ ;
CREATE FUNCTION FUN2(A CHAR(8), B CHAR(4), C CHAR(12)) RETURNS CHAR(2)
AS
DECLARE VARIABLE VAR_RESULT VARCHAR(50);
BEGIN
VAR_RESULT = A || B || C;
IF (char_length(VAR_RESULT)=20) then
VAR_RESULT = '21312321';
RETURN VAR_RESULT;
END^
SET TERM ; ^
SET TERM ^ ;
CREATE FUNCTION FUN1(A CHAR(8), B CHAR(4), C CHAR(12)) RETURNS CHAR(26)
AS
BEGIN
RETURN FUN2(A, B, C) || A || B || C;
END^
SET TERM ; ^
--
run the query:
SELECT FUN1('1020', '2080', '000625180347'), FUN2('1020', '2080',
'000625180347') FROM RDB$DATABASE
commit;
--
alter result type
SET TERM ^ ;
ALTER FUNCTION FUN2(A CHAR(8), B CHAR(4), C CHAR(12)) RETURNS CHAR(50)
AS
DECLARE VARIABLE VAR_RESULT VARCHAR(50);
BEGIN
VAR_RESULT = A || B || C;
IF (char_length(VAR_RESULT)=20) then
VAR_RESULT = '21312321';
RETURN VAR_RESULT;
END^
SET TERM ; ^
--
run the query:
SELECT FUN1('1020', '2080', '000625180347'), FUN2('1020', '2080',
'000625180347') FROM RDB$DATABASE
--
error reading data from the connection...
FIREBIRDDEV Wed Jun 20 09:52:34 2018
Access violation.
The code attempted to access a virtual
address without privilege to do so.
This exception will cause the Firebird server
to terminate abnormally.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://tracker.firebirdsql.org/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
--
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
[Firebird-devel] [FB-Tracker] Created: (CORE-5845) ORDER BY on index can cause suboptimal index choices
ORDER BY on index can cause suboptimal index choices Key: CORE-5845 URL: http://tracker.firebirdsql.org/browse/CORE-5845 Project: Firebird Core Issue Type: Bug Components: Engine Affects Versions: 3.0.3, 3.0.4 Reporter: Karol Bieniaszewski CREATE TABLE TEST ( ID1 INTEGER, ID2 INTEGER, ID3 INTEGER, X NUMERIC(18,2), CONSTRAINT PK_TEST PRIMARY KEY(ID1, ID2, ID3) ); CREATE INDEX IXA_TEST__X ON TEST(X); CREATE INDEX IXA_TEST__ID1_X ON TEST(ID1, X); SELECT * FROM TEST T WHERE T.ID1=1 AND T.X>0 PLAN (T INDEX (IXA_TEST__ID1_X)) index IXA_TEST__ID1_X is used SELECT * FROM TEST T WHERE T.ID1=1 AND T.X>0 ORDER BY T.ID1, T.ID2, T.ID3 PLAN (T ORDER PK_TEST INDEX (IXA_TEST__X)) index IXA_TEST__X - suboptimal as you can see adding ORDER BY which consume some index (PK_TEST) cause suboptimal choice of index (IXA_TEST__X) if query is changed to order by not by index SELECT * FROM TEST T WHERE T.ID1=1 AND T.X>0 ORDER BY T.ID1+0, T.ID2, T.ID3 PLAN SORT (T INDEX (IXA_TEST__ID1_X)) It can be releated to CORE-5795 but this can be different case -- This message is automatically generated by JIRA. - If you think it was sent incorrectly contact one of the administrators: http://tracker.firebirdsql.org/secure/Administrators.jspa - For more information on JIRA, see: http://www.atlassian.com/software/jira -- Check out the vibrant tech community on one of the world's most engaging tech sites, Slashdot.org! http://sdm.link/slashdot Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
[Firebird-devel] [FB-Tracker] Created: (CORE-5841) no permission for SELECT access to TABLE PLG$SRP in newer snapshot
no permission for SELECT access to TABLE PLG$SRP in newer snapshot -- Key: CORE-5841 URL: http://tracker.firebirdsql.org/browse/CORE-5841 Project: Firebird Core Issue Type: Bug Components: Engine Affects Versions: 3.0.4 Environment: WI-V3.0.4.32985 Firebird 3.0 Reporter: Karol Bieniaszewski Something was broken recently. Below sql is working under WI-V3.0.4.32954 Firebird 3.0 but cause an error under WI-V3.0.4.32972 Firebird 3.0 and current snapshot WI-V3.0.4.32985 Firebird 3.0 -- find/display record error no permission for SELECT access to TABLE PLG$SRP. -- SELECT U.SEC$USER_NAME , (SELECT UA.SEC$VALUE FROM SEC$USER_ATTRIBUTES UA WHERE UA.SEC$USER_NAME=U.SEC$USER_NAME AND UA.SEC$KEY='DYR_ID') AS GID , (SELECT UA.SEC$VALUE FROM SEC$USER_ATTRIBUTES UA WHERE UA.SEC$USER_NAME=U.SEC$USER_NAME AND UA.SEC$KEY='PRAC_ID') AS UID FROM SEC$USERS U WHERE U.SEC$USER_NAME=CURRENT_USER - this core can be releated to CORE-5827 For me, this is a blocking issue. -- This message is automatically generated by JIRA. - If you think it was sent incorrectly contact one of the administrators: http://tracker.firebirdsql.org/secure/Administrators.jspa - For more information on JIRA, see: http://www.atlassian.com/software/jira -- Check out the vibrant tech community on one of the world's most engaging tech sites, Slashdot.org! http://sdm.link/slashdot Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
[Firebird-devel] [FB-Tracker] Created: (CORE-5835) Provide place where "Invalid usage of boolean expression"
Provide place where "Invalid usage of boolean expression" - Key: CORE-5835 URL: http://tracker.firebirdsql.org/browse/CORE-5835 Project: Firebird Core Issue Type: Improvement Components: Engine Reporter: Karol Bieniaszewski Please provide place where "Invalid usage of boolean expression" occur eg. line number and col now finding this in big query is really hard -- This message is automatically generated by JIRA. - If you think it was sent incorrectly contact one of the administrators: http://tracker.firebirdsql.org/secure/Administrators.jspa - For more information on JIRA, see: http://www.atlassian.com/software/jira -- Check out the vibrant tech community on one of the world's most engaging tech sites, Slashdot.org! http://sdm.link/slashdot Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
[Firebird-devel] [FB-Tracker] Created: (CORE-5834) Provide DDL to change external table file name
Provide DDL to change external table file name -- Key: CORE-5834 URL: http://tracker.firebirdsql.org/browse/CORE-5834 Project: Firebird Core Issue Type: New Feature Components: Engine Reporter: Karol Bieniaszewski As proposed by Mark Rotteveel on support list please provide DDL to change file name of external table ALTER TABLE ALTER EXTERNAL [FILE] -- This message is automatically generated by JIRA. - If you think it was sent incorrectly contact one of the administrators: http://tracker.firebirdsql.org/secure/Administrators.jspa - For more information on JIRA, see: http://www.atlassian.com/software/jira -- Check out the vibrant tech community on one of the world's most engaging tech sites, Slashdot.org! http://sdm.link/slashdot Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
[Firebird-devel] [FB-Tracker] Created: (CORE-5828) udr pascal returning char(x) cannot be converted to bigint
udr pascal returning char(x) cannot be converted to bigint -- Key: CORE-5828 URL: http://tracker.firebirdsql.org/browse/CORE-5828 Project: Firebird Core Issue Type: Bug Components: Engine Affects Versions: 3.0.4 Reporter: Karol Bieniaszewski modify udr pascal sample gen_rows and compile with Delphi (maybe this is unreleated to Delphi) --- change result to: result: Array[0.199] of AnsiChar; --- and result declaration of procedure under Firebird result CHAR(200); --- in fetch procedure put '1635721458409799680' as result --- now try select: SELECT CAST(CAST(P.RESULT AS VARCHAR(200)) AS BIGINT) FROM gen_rows_pascal( 1 , 100 ) p; arithmetic exception, numeric overflow, or string truncation string right truncation expected length 52, actual 200. --- interesting that CAST(CAST(P.RESULT AS VARCHAR(100)) AS BIGINT) cause expected length 100, actual 100. --- maybe this is releated to CORE-5802 but with bigger problem because here cast fail and this prevent to work with data -- This message is automatically generated by JIRA. - If you think it was sent incorrectly contact one of the administrators: http://tracker.firebirdsql.org/secure/Administrators.jspa - For more information on JIRA, see: http://www.atlassian.com/software/jira -- Check out the vibrant tech community on one of the world's most engaging tech sites, Slashdot.org! http://sdm.link/slashdot Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
[Firebird-devel] [FB-Tracker] Created: (CORE-5818) Extend INSERT INTO to support csv
Extend INSERT INTO to support csv
-
Key: CORE-5818
URL: http://tracker.firebirdsql.org/browse/CORE-5818
Project: Firebird Core
Issue Type: New Feature
Components: Engine
Affects Versions: 3.0.4
Reporter: Karol Bieniaszewski
I know that someone can think that this is tool feature like isql or something
like this.
But it can not have same performance like engine itself have.
It should be as fast as it is for external tables now - but as we know external
tables can work only with fixed width data.
Extending external tables to csv data i suppose is not simple task at all.
now we can e.g.:
---
INSERT INTO TEST_TABLE(ID, NAME)
SELECT ID, NAME FROM EXT_TABLE
---
please consider extended syntax for INSERT INTO to:
by index:
INSERT INTO TEST_TABLE(ID, NAME) FROM FILE 'XXX.scv' DELIMITED BY '\t'
HEADER=True COLUMNS(1,3);
by header column names:
INSERT INTO TEST_TABLE(ID, NAME) FROM FILE 'XXX.scv' DELIMITED BY '\t'
COLUMNS('SOL_ID', 'CONT_SUFFIX'); <- here header can not be false because we
use names
It will be really usefull for importing data into Firebird database
>From security reason location of csv should be restricted to e.g. external
>table locations specified already in Firebird.conf
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://tracker.firebirdsql.org/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
--
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
[Firebird-devel] [FB-Tracker] Created: (CORE-5806) Allow specify of null constraint name in SET NOT NULL
Allow specify of null constraint name in SET NOT NULL - Key: CORE-5806 URL: http://tracker.firebirdsql.org/browse/CORE-5806 Project: Firebird Core Issue Type: New Feature Components: Documentation, Engine Affects Versions: 3.0.4 Reporter: Karol Bieniaszewski Few thinks about not null constraint and its name - i do not know if this should be as one ticket or some releated tickets 1. Specification of NOT NULL constraint is undocumented feature avaiable for years. e.g we can do: Create table A ( FIELD1 INTEGER CONSTRAINT NK_A_FIELD1 NOT NULL ) 2. In previous Firebird versions (prior FB3.0) there was possibility to do: ALTER TABLE XXX ADD FIELDX INTEGER CONSTRAINT NK_XXX__FIELDX NOT NULL; UPDATE TABLE XXX SET FIELDX=some calculations; and after that we have named null constraint but now in FB3 we can not do same. We must do: ALTER TABLE XXX ADD FIELDX INTEGER; UPDATE TABLE XXX SET FIELDX=some calculations; ALTER TABLE XXX ALTER FIELDX SET NOT NULL; and in SET NOT NULL we can not specify NOT NULL constraint name. And this is good if user can name all self created constraint in the database. 3. Will be good to see not null constraint name in error message like it is for all other constraints like PK, FK, CK, UK. this was discussed on the support mailing list and Mark Rotteveel say there: "Given named not null constraints are an undocumented feature, you can't expect too much from it. I suggest you create tickets to get this documented, and maybe to extend support to allow naming the constraint when using alter table xxx alter yyy set not null. I'd suggest something like expanding ALTER TABLE ADD by adding the option to tconstraint: [CONSTRAINT ] NOT NULL () Or maybe ALTER TABLE ALTER SET [CONSTRAINT ] NOT NULL Although that might conflict with the oddity of also supporting ALTER TABLE ALTER SET NULL, which is not defined in the SQL standard and is not a real constraint, and shouldn't get named. Interestingly, the SQL standard also supports named not null constraints, but there also naming it using ALTER COLUMN ... SET NOT NULL is not supported. Mark -- Mark Rotteveel " -- This message is automatically generated by JIRA. - If you think it was sent incorrectly contact one of the administrators: http://tracker.firebirdsql.org/secure/Administrators.jspa - For more information on JIRA, see: http://www.atlassian.com/software/jira -- Check out the vibrant tech community on one of the world's most engaging tech sites, Slashdot.org! http://sdm.link/slashdot Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
[Firebird-devel] [FB-Tracker] Created: (CORE-5802) Field name length check wrongly on max bound if national characters specified
Field name length check wrongly on max bound if national characters specified - Key: CORE-5802 URL: http://tracker.firebirdsql.org/browse/CORE-5802 Project: Firebird Core Issue Type: Bug Components: Engine Affects Versions: 3.0.4 Reporter: Karol Bieniaszewski Database WIN1250 (if matter) -OK-- SELECT 1 AS "Suma wszystkich INSP_ID rosnącoo" FROM RDB$DATABASE --- SQL error code = -104 Name longer than database column size. => ---WRONG SELECT 1 AS "Suma wszystkich INSP_ID rosnąco" FROM RDB$DATABASE --- arithmetic exception, numeric overflow, or string truncation string right truncation expected length 31, actual 31. --- -- This message is automatically generated by JIRA. - If you think it was sent incorrectly contact one of the administrators: http://tracker.firebirdsql.org/secure/Administrators.jspa - For more information on JIRA, see: http://www.atlassian.com/software/jira -- Check out the vibrant tech community on one of the world's most engaging tech sites, Slashdot.org! http://sdm.link/slashdot Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
[Firebird-devel] [FB-Tracker] Created: (CORE-5795) ORDER BY clause on compound index may disable usage of other indices
ORDER BY clause on compound index may disable usage of other indices Key: CORE-5795 URL: http://tracker.firebirdsql.org/browse/CORE-5795 Project: Firebird Core Issue Type: Bug Components: Engine Affects Versions: 3.0.3, 3.0.4 Reporter: Karol Bieniaszewski Attachments: TESTPLAN.7z This is releated to old CORE-5020 but now i have reproductible test case with sample database restore attached database backup run the query SELECT W.DYR_ID , W.INSP_ID , W.KONTO_ID , W.WPLATA_DATA_WYCIAGU , W.WPLATA_KONTRAHENT_ID FROM WPLATA W WHERE W.WPLATA_KONTRAHENT_ID IN (136804) AND W.DYR_ID = 9 ORDER BY W.DYR_ID , W.INSP_ID , W.KONTO_ID , W.WPLATA_DATA_WYCIAGU , W.WPLATA_NR_WYCIAGU , W.WPLATA_NR_POZYCJI - PLAN (W ORDER WPLATA_PK) Executing statement... Statement executed (elapsed time: 0.000s). 679228 fetches, 0 marks, 0 reads, 0 writes. 0 inserts, 0 updates, 0 deletes, 334971 index, 0 seq. Delta memory: 248896 bytes. Total execution time: 0.500s Script execution finished. plan should be PLAN (W INDEX (IXA_WPLATA__KONTRAHENT__PK)) Executing statement... Statement executed (elapsed time: 0.000s). 19 fetches, 0 marks, 0 reads, 0 writes. 0 inserts, 0 updates, 0 deletes, 6 index, 0 seq. Delta memory: 58112 bytes. Total execution time: 0.063s Script execution finished. or --- PLAN (W ORDER WPLATA_PK INDEX (IXA_WPLATA__KONTRAHENT__PK)) Executing statement... Statement executed (elapsed time: 0.000s). 185 fetches, 0 marks, 0 reads, 0 writes. 0 inserts, 0 updates, 0 deletes, 9 index, 0 seq. Delta memory: 59264 bytes. Total execution time: 0.047s Script execution finished. -- This message is automatically generated by JIRA. - If you think it was sent incorrectly contact one of the administrators: http://tracker.firebirdsql.org/secure/Administrators.jspa - For more information on JIRA, see: http://www.atlassian.com/software/jira -- Check out the vibrant tech community on one of the world's most engaging tech sites, Slashdot.org! http://sdm.link/slashdot Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
[Firebird-devel] [FB-Tracker] Created: (CORE-5782) Execution of multiple stored procedures in some case is slow
Execution of multiple stored procedures in some case is slow Key: CORE-5782 URL: http://tracker.firebirdsql.org/browse/CORE-5782 Project: Firebird Core Issue Type: Bug Components: Engine Affects Versions: 3.0.3, 3.0.4 Reporter: Karol Bieniaszewski We are in point of migration of database from Interbase to Firebird and encountered slownes. Below query run slow ~1s. With same shema this query run instant on Interbase. We have analysed all procedures execution one by one and all looks fast with good plans. But as whole it is slow compared to Interbase. SELECT D.DYR_ID , D.DYR_NAZWA , D2.DYR_ID AS MA_DYR_UPR FROM DYREKCJA D LEFT JOIN Pracownik_dyr_akcja(49, 0, 'ARozrachunkiKsiegowane', '') D2 ON D2.DYR_ID = D.DYR_ID WHERE D.OBSZAR_ID = 1 AND D.DYR_ID <> 0 ORDER BY D.DYR_NAZWA COLLATE PXW_PLK ASC one point in comparision is that Firebird for above query use PLAN SORT (JOIN (D INDEX (FK_DYREKCJA__OBSZAR), D2 NATURAL)) but Interbase PLAN SORT (MERGE (SORT (D INDEX (RDB$FOREIGN323)),SORT ())) on Firebird there are 561598 fetches when on Interbase there is only 30199 fetches I have attached difference with stats and sample database to test -- This message is automatically generated by JIRA. - If you think it was sent incorrectly contact one of the administrators: http://tracker.firebirdsql.org/secure/Administrators.jspa - For more information on JIRA, see: http://www.atlassian.com/software/jira -- Check out the vibrant tech community on one of the world's most engaging tech sites, Slashdot.org! http://sdm.link/slashdot Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
[Firebird-devel] [FB-Tracker] Created: (CORE-5767) GEN_ID in select can not take aliased field name - Token unknown - line 4, column 13
GEN_ID in select can not take aliased field name - Token unknown - line 4, column 13 Key: CORE-5767 URL: http://tracker.firebirdsql.org/browse/CORE-5767 Project: Firebird Core Issue Type: Bug Components: Engine Affects Versions: 3.0.3, 3.0.4 Reporter: Karol Bieniaszewski SELECT r.RDB$GENERATOR_NAME, r.RDB$GENERATOR_ID, r.RDB$SYSTEM_FLAG, r.RDB$DESCRIPTION, r.RDB$SECURITY_CLASS, r.RDB$OWNER_NAME, r.RDB$INITIAL_VALUE, r.RDB$GENERATOR_INCREMENT, GEN_ID(r.RDB$GENERATOR_NAME, 0) AS V FROM RDB$GENERATORS r cause "Token unknown - line 4, column 13" but without "r." alias GEN_ID(RDB$GENERATOR_NAME, 0) AS V it is working -- This message is automatically generated by JIRA. - If you think it was sent incorrectly contact one of the administrators: http://tracker.firebirdsql.org/secure/Administrators.jspa - For more information on JIRA, see: http://www.atlassian.com/software/jira -- Check out the vibrant tech community on one of the world's most engaging tech sites, Slashdot.org! http://sdm.link/slashdot Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
