Re: [Firebird-devel] Odd difference for IN (..) and = ANY (..) in SELECT vs WHERE
Hi! As I stated previously in this thread, IN/ANY/ALL and its negated form works only in simple (existent before the boolean data type was introduced) expressions. That happens because Firebird returns FALSE for some queries that should return NULL. There is special handling for NOT (IN/ANY/ALL expression), so this NOT still result in FALSE, making all simple expression work, as a embedded in AND, OR and NOT expressions in a WHERE clause will have the same treatment as NULL. To fix this case (have correct NULL result in value expressions) and some others with indexes following the old code appeared to be not simple, and also, the code used some trick layering (AnyBoolean) which I tought was not good to invest, so I did various changes in the way it works. It needs a careful review. Pull request: https://github.com/FirebirdSQL/firebird/pull/269 I ran Mark's test for CORE-6322 (and variant with index) and TCS. Would like to have the full test suite ran on it too. In the first commit there is only the necessary changes (and would be backported to v3 if approved). The second commit refactors req_null as a nice to have and is not for v3. I suppose it now give correct results and still use indexes when possible (thanks ConditionalStream). Now the main engine path for IN/ANY/ALL is ANY. ALL is early converted to NOT ANY. Internally it converts "val = any(select col from table where condition)" so the record source is "from table where condition and (col = val or col is null or val is null)". FilteredStream does not have special logic for them anymore. The ANY/ALL logic is now in RseBoolNode. Adriano Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
[Firebird-devel] [FB-Tracker] Created: (CORE-6323) File-system ID may be duplicated among databases located on different volumes
File-system ID may be duplicated among databases located on different volumes - Key: CORE-6323 URL: http://tracker.firebirdsql.org/browse/CORE-6323 Project: Firebird Core Issue Type: Bug Components: Engine Affects Versions: 4.0 Beta 2, 3.0.5, 2.5.9, 4.0 Beta 1, 3.0.4, 3.0.3, 2.5.8, 4.0 Alpha 1, 3.0.2, 2.5.7, 3.0.1, 2.5.6, 3.0.0, 4.0 Initial, 2.5.5, 2.5.4, 2.5.3 Update 1, 2.1.7, 2.5.3, 2.5.2 Update 1, 2.5.2, 2.5.1, 2.5.0 Environment: Windows only Reporter: Dmitry Yemanov Firebird uses file-system ID of the open database file to uniquely name its dependent shared memory files - lock table, event table, etc. File-system ID is retrieved using GetFileInformationByHandle() API and composed from the three fields (dwVolumeSerialNumber, nFileIndexHigh, nFileIndexLow) that are documented in MSDN as providing a unique combination: https://docs.microsoft.com/ru-ru/windows/win32/api/fileapi/nf-fileapi-getfileinformationbyhandle "You can compare the VolumeSerialNumber and FileIndex members returned in the BY_HANDLE_FILE_INFORMATION structure to determine if two paths map to the same target; for example, you can compare two file paths and determine if they map to the same directory." The problem, however, is that Volume Serial Number (VSN) is not guaranteed to be unique. It's generated when then the volume is formatted and it's stored inside the volume's master boot record. But if the volume is cloned at the physical block level, or if a virtual (preformatted) drive is used, or if a volume snapshot (created by storage system like Dell EMC) is attached as a different logical drive, then VSN may duplicate an existing VSN. This may cause two different databases (located on different volumes) to share the same file-system ID thus sharing the same lock table, causing unexpected freezes and other undesired side-effects. -- 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
Re: [Firebird-devel] Value of CURRENT_TIME is not current time
On 03/06/2020 12:58, Mark Rotteveel wrote: That is what you can expect when linking to documentation on a branch called 'work' (which, afaik, was deleted a very long time ago), which implies it's transient. It had been used in relation to a previous thread and was the only link I could see earlier ... and I have corrected that ... -- Lester Caine - G8HFL - Contact - https://lsces.uk/wiki/Contact L.S.Caine Electronic Services - https://lsces.uk Model Engineers Digital Workshop - https://medw.uk Rainbow Digital Media - https://rainbowdigitalmedia.uk Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Value of CURRENT_TIME is not current time
On 2020-06-03 12:51, Lester Caine wrote: On 03/06/2020 09:24, Alex Peshkoff via Firebird-devel wrote: Has https://github.com/FirebirdSQL/firebird/blob/work/time-zone-support/doc/sql.extensions/README.time_zone.md Been replaced with something more up to date? https://github.com/FirebirdSQL/firebird/blob/master/doc/sql.extensions/README.time_zone.md should be a stable URL ? It's stable but it will always reference that file in a state reflecting current development state. Really stable URL will arrive after creation of B4_0_Release branch. Problem was that the first link no longer exists and github then gets a bit more difficult to navigate ... Linking to github in discussions does not always produce the same document even ignoring the fact that these documents will be evolving, so it is important to have a set of non-github documentation sooner rather than later. That is what you can expect when linking to documentation on a branch called 'work' (which, afaik, was deleted a very long time ago), which implies it's transient. Mark Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Value of CURRENT_TIME is not current time
On 2020-06-02 23:32, Adriano dos Santos Fernandes wrote: On 02/06/2020 16:45, Mark Rotteveel wrote: Documentation should be improved for this case too. This essentially means that select * from atable where sometimecolumn > current_time; will yield different results when the time zone is 'Europe/Amsterdam' than it does when the time zone is UTC. I think that is extremely confusing, and not acceptable. Test case showing the problem (with time/tz constant), please. For constants we can explain this away with the fact that 'it is the time on 2020-01-01', that logic doesn't really work well with something that is purported to be the current time, and is defined as based on UTC. I'm specifically talking about the fact that given TIME WITH TIME ZONE is defined as time in UTC with additional information (either offset or a named zone) to derive a local time, that CURRENT_TIME in fact does not conform to that definition, because it is in fact not UTC. Right now in Europe/Amsterdam it is 13:37 (11:37 UTC). If I use CURRENT_TIME right now, I will get a time of 12:37 UTC (which is intended to render as 13:37), which is not correct. Especially not if you consider the fact that the mere changing of the time zone using SET TIME ZONE 'UTC' would result in a time of 11:37 UTC. In essence, I think this makes named zones largely meaningless for TIME WITH TIME ZONE. If we can't fix this somehow, then I at least want an option (DPB-item/SET xxx) where even with a named session time zone, CURRENT_TIME and CURRENT_TIMESTAMP and casts of literals to TIME WITH TIME ZONE will yield offset-based times (derived from the named zone rules for 'right now'). Otherwise I see no other option than to make Jaybird default to use UTC or the offset at connect time as the session time zone for sake of correctness. Mark Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Value of CURRENT_TIME is not current time
On 03/06/2020 09:24, Alex Peshkoff via Firebird-devel wrote: Has https://github.com/FirebirdSQL/firebird/blob/work/time-zone-support/doc/sql.extensions/README.time_zone.md Been replaced with something more up to date? https://github.com/FirebirdSQL/firebird/blob/master/doc/sql.extensions/README.time_zone.md should be a stable URL ? It's stable but it will always reference that file in a state reflecting current development state. Really stable URL will arrive after creation of B4_0_Release branch. Problem was that the first link no longer exists and github then gets a bit more difficult to navigate ... Linking to github in discussions does not always produce the same document even ignoring the fact that these documents will be evolving, so it is important to have a set of non-github documentation sooner rather than later. -- Lester Caine - G8HFL - Contact - https://lsces.uk/wiki/Contact L.S.Caine Electronic Services - https://lsces.uk Model Engineers Digital Workshop - https://medw.uk Rainbow Digital Media - https://rainbowdigitalmedia.uk Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Value of CURRENT_TIME is not current time
On 03/06/2020 04:10, Alex Peshkoff via Firebird-devel wrote: > On 2020-06-03 00:17, Dimitry Sibiryakov wrote: >> 02.06.2020 21:45, Mark Rotteveel wrote: >>> I think that is extremely confusing, and not acceptable. >> >> That's why everyone was given several years to replace CURRENT_TIME >> with LOCALTIME and don't use TIME WITH TIMEZONE at all. >> > > Yes. What we definitely need in RN: "Think twice before using TIME > WITH TIMEZONE and better don't use it at all". > I'd say the problem is with conversions from/to timestamps, from/to different zones and comparisons of values using different zones - only when using named zones. Adriano Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Value of CURRENT_TIME is not current time
On 2020-06-03 10:56, Lester Caine wrote: On 03/06/2020 08:23, Lester Caine wrote: On 02/06/2020 22:17, Dimitry Sibiryakov wrote: 02.06.2020 21:45, Mark Rotteveel wrote: I think that is extremely confusing, and not acceptable. That's why everyone was given several years to replace CURRENT_TIME with LOCALTIME and don't use TIME WITH TIMEZONE at all. Has https://github.com/FirebirdSQL/firebird/blob/work/time-zone-support/doc/sql.extensions/README.time_zone.md Been replaced with something more up to date? https://github.com/FirebirdSQL/firebird/blob/master/doc/sql.extensions/README.time_zone.md should be a stable URL ? It's stable but it will always reference that file in a state reflecting current development state. Really stable URL will arrive after creation of B4_0_Release branch. Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Value of CURRENT_TIME is not current time
On 03/06/2020 08:23, Lester Caine wrote: On 02/06/2020 22:17, Dimitry Sibiryakov wrote: 02.06.2020 21:45, Mark Rotteveel wrote: I think that is extremely confusing, and not acceptable. That's why everyone was given several years to replace CURRENT_TIME with LOCALTIME and don't use TIME WITH TIMEZONE at all. Has https://github.com/FirebirdSQL/firebird/blob/work/time-zone-support/doc/sql.extensions/README.time_zone.md Been replaced with something more up to date? https://github.com/FirebirdSQL/firebird/blob/master/doc/sql.extensions/README.time_zone.md should be a stable URL ? Certainly I have no intention of doing anything other than retaining ALL timestamps at UTC AND running the server local time as UTC. Displaying and handling CLIENT local times is an interface problem and not something which should be inconsistent in the core data ... especially when one does not know what future time offsets will actually be ... It would be nice to see the final paragraph much closer to the top so that the fundamental problems of using the TZ database is highlighted earlier. THAT is an explanation of where the FB4 approach to them may or may not be appropriate. Also still missing is the lack of second accuracy with timezone offsets in the FB4 version and that dates prior to 1970 may produce different results. It should be clear exactly which version of TZ data is bundled so we know if pre-19790 data is included or not ... -- Lester Caine - G8HFL - Contact - https://lsces.uk/wiki/Contact L.S.Caine Electronic Services - https://lsces.uk Model Engineers Digital Workshop - https://medw.uk Rainbow Digital Media - https://rainbowdigitalmedia.uk Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Value of CURRENT_TIME is not current time
On 02/06/2020 22:17, Dimitry Sibiryakov wrote: 02.06.2020 21:45, Mark Rotteveel wrote: I think that is extremely confusing, and not acceptable. That's why everyone was given several years to replace CURRENT_TIME with LOCALTIME and don't use TIME WITH TIMEZONE at all. Has https://github.com/FirebirdSQL/firebird/blob/work/time-zone-support/doc/sql.extensions/README.time_zone.md Been replaced with something more up to date? Certainly I have no intention of doing anything other than retaining ALL timestamps at UTC AND running the server local time as UTC. Displaying and handling CLIENT local times is an interface problem and not something which should be inconsistent in the core data ... especially when one does not know what future time offsets will actually be ... -- Lester Caine - G8HFL - Contact - https://lsces.uk/wiki/Contact L.S.Caine Electronic Services - https://lsces.uk Model Engineers Digital Workshop - https://medw.uk Rainbow Digital Media - https://rainbowdigitalmedia.uk Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Value of CURRENT_TIME is not current time
On 2020-06-03 00:17, Dimitry Sibiryakov wrote: 02.06.2020 21:45, Mark Rotteveel wrote: I think that is extremely confusing, and not acceptable. That's why everyone was given several years to replace CURRENT_TIME with LOCALTIME and don't use TIME WITH TIMEZONE at all. Yes. What we definitely need in RN: "Think twice before using TIME WITH TIMEZONE and better don't use it at all". Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
