tag:blogger.com,1999:blog-35688928897047605082024-03-13T12:50:46.122+02:00Firebird: Under the HoodVarious aspects of the database development and misc thoughts related to the Firebird SQL engine.Dmitry Yemanovhttp://www.blogger.com/profile/09711564106003431793noreply@blogger.comBlogger8125tag:blogger.com,1999:blog-3568892889704760508.post-24410528758088722552012-05-30T12:48:00.000+03:002012-05-30T14:41:47.604+03:00A few words about the shared memory and files<div dir="ltr" style="text-align: left;" trbidi="on">
Even if running in the Classic (isolated process) mode, Firebird needs some data to be available to all the running server processes. There are four kinds of information that must be shared:<br />
<ul style="text-align: left;">
<li>Lock table. It's the vital part of the lock manager and its primary goal is to synchronize access to various resources that can be read or modified simultaneously.</li>
<li>Event table. Every time a posted event is committed, the server needs to find all the subscribers and redirect the event delivery to processes handling the appropriate user connections.</li>
<li>Monitoring snapshot. It keeps the latest known state of all the running worker processes and it gets updated once some user connection attempts to access the monitoring tables in a new transaction.</li>
<li>Trace configuration. It contains the information required for the worker processes to react on the currently active tracing events and log the appropriate notifications.</li>
</ul>
The shared memory regions are backed by the files on disk, i.e. those files are mapped to the address space of the worker processes. The backing files can grow if necessary, causing the shared memory regions to be remapped. The backing files and shared memory regions are created when the first worker process initializes the corresponding subsystem and then used by other worker processes. Once there are no active users of those backing files, they are either removed by the last worker process (Firebird 2.5 and above) or could be left on disk to be reused later (prior to Firebird 2.5). They're also left on disk in the case of the server crash. The initial size of the shared memory regions can be user configurable, see LockMemSize and EventMemSize settings in firebird.conf.<br />
<br />
<a name='more'></a>Historically, the files backing the shared memory were located in the root directory if the Firebird installation and thus they were isolated from different Firebird instances. However, this could cause serious troubles if two or more separate Classic instances would occasionally try to access the same database in the read-write mode. Without a shared lock table, they would not synchronize their activity and hence they could easily overwrite each other's changes (in the best case) or just leave the database totally corrupted and unusable (in the worst case).<br />
<br />
Also, it should be noted that the shared memory regions were global for the particular server instance. In other words, they handled the shared data for all the databases processed by this server instance. Given the fact that the whole shared memory region is protected by a single mutex, it could cause unexpectedly high mutex wait ratios (and thus degraded performance) if multiple databases were heavily loaded by user connections.<br />
<br />
Firebird 2.5 has attempted to improve this situation in two ways:<br />
<ul style="text-align: left;">
<li>From one side, the shared memory regions (and their backing files) have been made database specific. A separate file and mutex are used for the every database being accessed. So databases are completely isolated now and don't interfere with each other even via the shared resources.</li>
<li>From another side, they have been made system-wide, i.e. they are visible and can be accessed by different server instances trying to access the same database. So any related database corruption is not possible anymore.</li>
</ul>
While implementing these changes, one question was raised for discussion - where should the backing files reside now, provided their system-wide nature. Their location must be well-known to all the programs running in the system but it must be persistent and not depending on any runtime overrides. So global configuration options or environment variables don't satisfy the requirement, as any change at runtime could lead to a half of worker processes using one lock table and another half using another one. One suitable solution has been attempted in Vulcan which had the lock table residing along with the database file itself, in the same directory. But, unfortunately, this cannot work for databases mounted on read-only partitions. So another solution has been chosen - a predefined directory in the host OS that is guaranteed to exist and that allows read-write permissions. On Linux and other unixes, the original suggestion was something like /var/firebird, but the /var directory itself is usually protected, so some kind of a privileged initial setup would be required in order to create a sub-directory, and this conflicts with "zero-install" requirements for the embedded server. So we have ended with /tmp/firebird, as the system without /tmp can be hardly found. Also, it corresponds to the temporary (even if sometimes long-living) nature of those files. On Windows, there's no predefined temporary directory and the path returned by GetTempPath() can be easily affected by TMP/TEMP environment variables. So we ended with %PROGRAMDATA%/firebird there.<br />
<br />
That said, you can still override the default location with the FIREBIRD_LOCK environment variable. But please don't say you haven't been warned. Make yourself absolutely confident what you are doing and what issues you can expect if the setting has been misused, e.g. not made the same value available for all users/programs or altered at runtime.</div>Dmitry Yemanovhttp://www.blogger.com/profile/09711564106003431793noreply@blogger.com0tag:blogger.com,1999:blog-3568892889704760508.post-39996572820355549042012-03-27T12:52:00.015+03:002012-03-27T14:43:26.828+03:00Firebird vs Windows: file-system caching issue<div dir="ltr" style="text-align: left;" trbidi="on">This article is going to shed some light on the facts related to the issue registered as <a href="http://tracker.firebirdsql.org/browse/CORE-3791">CORE-3791</a> in the project bug tracker. Let's start with some links.<br />
<br />
The origin of the problem is described here:<br />
<a href="http://support.microsoft.com/kb/2549369">http://support.microsoft.com/kb/2549369</a><br />
<br />
A lot of additional useful information can be found here (especially in the comments):<br />
<a href="http://blogs.msdn.com/b/ntdebugging/archive/2007/11/27/too-much-cache.aspx">http://blogs.msdn.com/b/ntdebugging/archive/2007/11/27/too-much-cache.aspx</a><br />
<br />
In fact, this issue consists of two slightly different but interrelated parts.<br />
<br />
The first one is that the file-system cache size is unlimited by default in Windows 64-bit, so its working set can consume all the available RAM, thus forcing almost everything else to swap and finally starting to swap itself. It's visually detected by a slow system response and lots of the HDD activity. The suggested solution is to limit the file-system cache size with the <a href="http://msdn.microsoft.com/en-us/library/aa965240.aspx">SetSystemFileCacheSize</a> API function. And Firebird 2.5 supports this solution with the <i>FileSystemCacheSize</i> setting in firebird.conf.<br />
<br />
Unfortunately, we have reports that the problem may still take place for some customers. I have performed a research and the findings are the following:<br />
<br />
<a name='more'></a><ol style="text-align: left;"><li>In order to change the file-system cache size, the user running the application must have the priviledge named <i>SeIncreaseQuotaPrivilege</i>. This fact is documented in firebird.conf. By default, only LocalSystem and the Administrators group have this priviledge assigned. The Firebird service installation utility <i>instsvc</i> grants this priviledge to the custom user account chosen during the installation. So far so good.<br />
</li>
<li>However, this may not work if you run Firebird as an application or use it in the embedded mode. In this case and with the default <a href="http://en.wikipedia.org/wiki/User_Account_Control">UAC</a> settings, Firebird will not have the sufficient permissions even if the currently logged user is an administrator or has <i>SeIncreaseQuotaPrivilege</i> explicitly assigned to the current user in the Local Security Policies. In order to make it working, you need to start Firebird (or the host application using fbembed.dll) using the UAC's feature "Run as Administrator".<br />
</li>
<li>The worst thing here is that it could be problematic to figure out whether the file-system cache size has been actually limited or not. Contrary to what the comment inside firebird.conf says, the lack-of-permission error is unlikely to be printed to firebird.log and it makes the diagnostics hardly possible.<br />
</li>
<li>And finally, 32-bit builds of Firebird may completely ignore the <i>FileSystemCacheSize</i> configuration setting if they run on a 64-bit Windows box. This is because the default cache size limit is too high to be represented with the 32-bit Windows API.</li>
</ol><div>The second part of the issue is that even if your setup handles the file-system cache size limit properly and thus avoids any possible out-of-memory conditions, you may still notice the active swapping and the related performance issues. The explanation is simple - the aforementioned approach doesn't limit the cache per se, it just limits its working set, i.e. pages kept resident in RAM.</div><div><br />
</div><div>Now we get closer to the root of the whole issue. If the application requests random file access, and this is what Firebird does, it forces the Windows cache manager to "pin" all the visited pages in a hope to have them accessed again. In other words, those pages are never removed from the cache. With the working set size being limited, the cache manager has no other choice but to store the non-resident part (pages that don't fit the working set) in the pagefile. I know it sounds completely crazy and I could never imagine that caching of a disk access might be implemented via yet another disk access. But it appears to be true. You can see the similar conclusions published for another affected application (IBM Lotus Domino):</div><div><a href="http://blog.nashcom.de/nashcomblog.nsf/dx/new-performance-problem-with-domino-on-windows-2008-64bit.htm">http://blog.nashcom.de/nashcomblog.nsf/dx/new-performance-problem-with-domino-on-windows-2008-64bit.htm</a><br />
<br />
</div><div>So the only effective solution seems to disable the random access request (i.e. remove the FILE_FLAG_RANDOM_ACCESS flag) from the Windows API calls used to create/open the files. Moreover, in this case the file-system cache size limit should not be actual anymore, as Windows won't be expanding the cache out of the reasonable boundaries. The quick tests prove this solution being workable. Below are the results observed for a 8GB database on a box with 4GB RAM (both Firebird and Windows are 64-bit):</div><div><ul style="text-align: left;"><li>More or less sequential access (natural table scan): ~ 2m00s vs 1m10s</li>
<li>Mixed sequential and random access (natural table scan + external sorting): ~ 3m20s vs 1m30s</li>
<li>Random access (table scan via non-PK index based ordering): ~ 15m vs 12m</li>
</ul><div><div>So we can expect up to 2x performance improvement but mostly for cases when the whole database or its major part has to be accessed (e.g. during backup or after its completion).</div><div><br />
</div><div>I've also attempted to find out how other open source databases work with files on Windows and here are the results:</div><div><ul><li>MySQL / InnoDB - random access is not requested</li>
<li>PostgreSQL - random access is not requested</li>
<li>SQLite - random access was requested, but disabled in 2008</li>
</ul></div></div></div><div>Here is the quote of the commit log for SQLite:</div><div><br />
</div><div><pre style="background-color: #eeeeee; font-size: 0.93em; line-height: 1.3em; overflow-x: auto; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; white-space: pre-wrap; width: 550px; word-wrap: break-word;">Iterating through all the features of a large SDF file causes a significant loss of the system available memory. This is due to the system disk cache which tend to use all the memory it can get. The fix is to remove the random access option when opening the SDF file. That reduced the memory used by the disk cache to a reasonable amount and did not impact the performance of the SDF provider (it actually helped a bit).</pre></div><div><br />
</div><div>As we have seen above, it really helped for Firebird as well. But so far we were speaking about large databases with sizes exceeding the available memory amounts. So the only remaining question is how this change would affect small databases that can fit into the file-system cache completely. I've repeated the aforementioned test with a 1GB database and results are the following:</div><div><ul style="text-align: left;"><li>More or less sequential access (natural table scan): ~ 4.2s vs 4.5s</li>
<li>Mixed sequential and random access (natural table scan + external sorting): ~ 12.4s vs 12.7s</li>
<li>Random access (table scan via non-PK index based ordering): ~ 1m10s vs 1m30s</li>
</ul><div>So we see a very small penalty for sequential or mixed access but a noticeable penalty (about 30%) for highly random access. This means that even if there's enough RAM to cache the whole database, the Windows cache manager prefers to work with a small working set unless the random access is explicitly requested. As for me, this is also quite unexpected from a decent cache manager, but perhaps it depends on the various Windows settings (<i>LargeSystemCache</i> etc).</div><div><br />
</div><div>Please beware that the worst possible result is achieved with a test pattern rarely used in production. Also, it can be compensated by a larger Firebird page cache (remember that databases in question are small enough and hence you have plenty of the unused RAM). Taking this into account, as well as the experience of other databases, this solution has been committed into Firebird 2.1.5, Firebird 2.5.2 and Firebird 3.0 branches.</div></div><div><br />
</div><div><i>P.S. One might think that Firebird could be smart enough and decide about the random access mode dynamically depending on the database size vs the RAM size or even better vs the current file-system cache limit. But it doesn't sound as a good idea. There may be many databases accessed on the same server, databases may grow while being connected and so on. It's practically impossible to handle all these cases properly.</i></div></div>Dmitry Yemanovhttp://www.blogger.com/profile/09711564106003431793noreply@blogger.com10tag:blogger.com,1999:blog-3568892889704760508.post-38016090353301101272012-03-15T14:55:00.006+03:002012-03-17T17:27:27.572+03:00Records batching<div dir="ltr" style="text-align: left;" trbidi="on">As explained priorly, the fetch response includes multiple resulting records batched together. But how many records should be batched? Sending too few records is going to increase a number of round-trips. Sending too many records will cause the client to stall waiting until it can acknowledge the delivery. And it could also be possible that the client does not need so many records and it was going to close the cursor after receiving the first one. So it turns out that some compromise is required here. The minimal practical batch size depends on the used protocol buffer size, i.e. how many records could be cached before sending and then transmitted as a single packet. The buffer size for the TCP protocol is defined by the TcpRemoteBufferSize setting in firebird.conf. However, it often makes sense to send more records (i.e. a few protocol buffers) without waiting for an ACK, because the CPU power could allow to process more records while waiting for the network to transmit the next batch.<br />
<br />
Firebird has its batching logic optimized to transfer between 8 and 16 packets at once. The client library sends this number to the server, waits for transmission of the requested number of records, starts caching the received records and returning them to the client application. The tricky thing here is that the batch size is expressed in records and this value is calculated using the batch size in packets and the expanded (unpacked) record length. A soon as the record gets packed or compressed in some way, the calculation becomes wrong and it results in sending less packets than expected. Also, the last packet could be sent incomplete. Currently, the only "compression" that's available is trimming of VARCHAR values. So the batching could be either effective or somewhat sub-optimal depending on how many VARCHAR values are fetched and how long the actual strings are as compared to their declared lengths.<br />
<br />
<a name='more'></a>The important thing to remember here is that it's the client library that calculates the batch size. It means that if you need to change the number of records transmitted as a single fetch response, you need to alter the TcpRemoteBufferSize setting on the client side. The server side setting does not matter here.<br />
<br />
If the server waits for the next fetch request after sending the batch, or if the client asks for the next batch after processing all the cached records, this is known as a synchronous batching. But obviously, it wastes a lot of time in the case of slow networks. So Firebird uses the asynchronous batching, also known as pipelining. As soon as all records of the batch are sent to the client, the server starts to fetch new records from the engine and cache them for the next transmission. As soon as the client library has processed some part of the current batch, it asks the server for the next batch and continues processing the remaining records. This allows to distribute the load more evenly and provide a better overall throughput. The current (hardcoded) pipelining threshold is 1/2 of the batch size.<br />
<br />
Now let's review what could be enhanced in this area:<br />
<ul style="text-align: left;"><li>Denser encoding of records is possible. The XDR encoding used currently is very sparse: all numerics are sent as at least four-byte values, all values are aligned at the four-byte boundary, NULL flags are transmitted as integers, etc. One solution could be to replace XDR with some other encoding that's aware of the data specifics and that could provide a denser representation while still respecting the cross-platform interoperability (network byte order). Also, some computationally cheap compression like RLE could also be applied. It would result in more records per protocol buffer, hence less round-trips to transmit the same number of records.</li>
</ul><ul style="text-align: left;"><li>The batch size calculation should take the record compression into account. Or, even better, the server should just send as many records as it can fit into the desired number of packets and forget about any tricky computations at all. It would allow a more honest transmission with packets filled properly.</li>
</ul><ul style="text-align: left;"><li>The pipelining threshold could be adjusted dynamically depending on the network specifics. For high-latency networks, it makes a lot of sense to ask for the next batch immediately after receiving the current one. The quick tests show that more than 50% of a performance gain is possible here.</li>
</ul><div>I'm working on a prototype that could demonstrate the ideas expressed here, so please expect a follow-up post with some performance figures in the not-so-distant future.</div></div>Dmitry Yemanovhttp://www.blogger.com/profile/09711564106003431793noreply@blogger.com0tag:blogger.com,1999:blog-3568892889704760508.post-8125776757818324462012-03-14T17:11:00.008+03:002012-03-17T17:25:48.887+03:00Firebird roadmap has been updated<div dir="ltr" style="text-align: left;" trbidi="on">The project <a href="http://www.firebirdsql.org/en/roadmap/">roadmap</a> has been updated a bit. The change is to boost the v2.1.5 and v2.5.2 releases at the cost of slightly delaying the v3.0 Alpha release.<br />
<br />
Firebird 2.1.4 was released exactly one year ago, so now it's a promised time for v2.1.5. It has 53 bugs fixed and no critical issues remaining unresolved. Firebird 2.5.1 was released more than 5 months ago and the expected release date for v2.5.2 is approaching the next month. It has 45 issues resolved up-to-date and a few more are in the pipeline. So it makes a lot of sense to release them sooner rather than later.<br />
<br />
The v3.0 Alpha release will be going through the preparation stage while all three release candidates (v2.0.7, v2.1.5, v2.5.2) are being field tested, so it's likely to appear shortly after the aforementioned releases, in the second quarter.<br />
<br />
Thanks for your understanding.</div>Dmitry Yemanovhttp://www.blogger.com/profile/09711564106003431793noreply@blogger.com0tag:blogger.com,1999:blog-3568892889704760508.post-40195511770069920312012-02-07T16:10:00.002+03:002012-03-17T15:26:28.315+03:00Protocol packets and buffers<div dir="ltr" style="text-align: left;" trbidi="on">Before going into the rows batching details, let's speak about some protocol details, in particular packets and buffers. As virtually any other kind of networking-enabled software, Firebird is packet oriented, i.e. it sends and receives logically bound chunks of bytes. So it caches the bytes to be sent in the buffer until the packet is completed or until the buffer is full, and only then it transmits the data.<br />
<br />
Usually, logical packets are quite small and thus the protocol buffer is always large enough to fit it entirely. The rule of thumb here is that it must be not less than the MSS (<a href="http://en.wikipedia.org/wiki/Maximum_segment_size">maximum segment size</a>) value for the given transport layer. For example, Ethernet has MTU (<a href="http://en.wikipedia.org/wiki/Maximum_transmission_unit">maximum transmission unit</a>) equal to 1500 bytes, so IPv4 MSS = MTU - IPv4 overhead = 1460 bytes, and TCP MSS = IPv4 MSS - TCP overhead = 1448 bytes. If the protocol buffer size is smaller than this value, the network bandwidth will not be utilized completely.<br />
<br />
Just for the record, Firebird supports TCP buffer sizes in the range between 1448 (min) and 32768 (max) bytes. The default setting is 8KB. For NetBEUI (named pipes), the buffer size is hardcoded as 2KB.<br />
<br />
<a name='more'></a>From another side, some packets can be large enough and the fetch response is a good example. Also, multiple logical packets can be sent together within a single transport layer transmission. So a larger protocol buffer sounds as a good idea at the first glance. And, provided that we don't need a reply immediately, it also becomes possible to replace a few application layer round-trips with a single one. But we already know that any packet longer than MSS will be splitted into multiple smaller packets. Actually, this is not as bad as it sounds because they can be transmitted more or less asynchronously and recombined on the other side, i.e. the performance should be limited mostly by the bandwidth (which is commonly quite good nowadays) and not the latency (which can be bad for the global networks). However, TCP requires regular acknowledgements from the other side to make sure that everything is delivered properly. This is necessary to avoid resending the whole [possibly longish] message in the case of networking issues (lost frames, etc). So every few outcoming TCP packets get ACKed with a small reply packet. This introduces another level of round-trips that depends on the network only and cannot be tuned at the application layer (*). This leads us to the conclusion that the larger protocol buffer on the sender's side allows to reduce the number of application layer round-trips, but it's unlikely to significantly affect the number of transport layer round-trips, so the performance impact would be minimal.<br />
<br />
So far we were discussing the sender side, now what's about the receiver side? The protocol buffer size does not matter much there, because the received bytes are already cached at the transport layer (TCP/IP stack driver) anyway, so a bigger protocol buffer just means less OS calls which is not that important if compared to delays in the network. However, there's a situation where the client side protocol buffer size matters a lot. It will be mentioned in the next blog post, so stay tuned.<br />
<br />
<i>(*) A good reading on the subject is here: <a href="http://smallvoid.com/article/tcpip-rwin-size.html">http://smallvoid.com/article/tcpip-rwin-size.html</a>.</i><br />
<i>This article, as well as many others, suggest that the intensity of ACKs (i.e. how many received bytes get ACKed) depends exclusively on the RWIN (TCP window size) setting on the receiving side and it could be altered both at the system level (in the registry) and at the application level. My box has the registry limit set to 256KB. For a remote Internet connection, the network dynamically selects RWIN equal to 8KB (at least getsockopt(SO_RCVBUF) reports so). But ACKs are sent for the every two TCP segments received from the remote host. Trying to alter RWIN to 32KB with setsockopt(SO_RCVBUF) succeeds and reports the new value as 32KB but ACKs are still sent for the every two TCP segments. I suppose it's related to the Delayed ACK algorithm as described here:</i><br />
<a href="http://www.stuartcheshire.org/papers/NagleDelayedAck/">http://www.stuartcheshire.org/papers/NagleDelayedAck/</a>.<i><br />
</i></div>Dmitry Yemanovhttp://www.blogger.com/profile/09711564106003431793noreply@blogger.com2tag:blogger.com,1999:blog-3568892889704760508.post-14303297139280106552012-01-14T17:39:00.001+03:002012-03-17T15:25:43.436+03:00Generic thoughts about the network performance<div dir="ltr" style="text-align: left;" trbidi="on">Everybody knows that the performance of the data transfer in the Internet world mostly depends not on the network bandwidth but on its latency (response times). In other words, the chattier is the protocol the worse is the overall performance. Transferring the same data as a large single packet will be faster than splitting it across multiple smaller packets.<br />
<br />
At the software level, there may be different solutions attempted to minimize the number of network round-trips and thus improve the transmission times. Here you can see what has been done in Firebird up to date.<br />
<br />
<a name='more'></a><ul style="text-align: left;"><li>While replying to the fetch request, the server batches as many records as it can fit into a single protocol packet. They are then cached on the client side and returned to the caller one by one. Once the local cache is exhausted, a new fetch request is sent to the server and the batching continues until the end-of-stream marker has been processed for the cursor. In order to optimize the process a little, the server side maintains its own cache of records which gets filled in the meantime, while waiting for the next fetch request from the client. <i>[Done many years ago during the InterBase era.]</i><br />
</li>
<li>Optimize the binary layout of the transmitted data, i.e. pack/compress them. In memory, all packets are represented in the expanded (structured) form but they are serialized accordingly to the <a href="http://en.wikipedia.org/wiki/External_Data_Representation">XDR</a> rules before transmission. However, XDR is not a compression, its primary goal is cross-platform interoperability. So some packet types could benefit from the additional packing of their internals. For example, the server truncates the VARCHAR values up to their real length instead of sending them with the declared length. As a result, more records can fit a single protocol packet, thus reducing the number of round-trips (see above). <i>[Performed unconditionally starting with Firebird 1.5, more advanced packing techniques are possible.]</i><br />
</li>
<li>Batch multiple different packets into the single one. This can be done if the order of packets is deterministic and can be foreseen. Currently the processing of some packet types is deferred in order to be batched together with the next packet. For example, statement allocation requests are batched together with the first usage of those statement handles, releasing of statement and blob handles is delayed until the next packet is sent, execution of SELECT statements is deferred until the first fetch is attempted, etc. <i>[Done in Firebird 2.1, a few other optimizations of that kind are possible.]</i></li>
</ul><div>I'm going to address these items in more details in the subsequent blog posts.</div></div>Dmitry Yemanovhttp://www.blogger.com/profile/09711564106003431793noreply@blogger.com0tag:blogger.com,1999:blog-3568892889704760508.post-54216877958112633932011-07-12T15:00:00.001+03:002012-03-17T15:25:57.445+03:00Data type coercion vs comparison rules<div dir="ltr" style="text-align: left;" trbidi="on">A somewhat unexpected behavior has been observed while investigating different aspects of the data type coercion. By coercion I mean an implicit casting to the "common" data type that happens when a few operands have different data types but the expression requires some predefined data type to be chosen for the resulting value. In Firebird, common data type determination is used in two cases: (a) describing the select list expressions of the union and (b) describing the resulting data type of the CASE/COALESCE functions.<br />
<br />
In old versions, different data types couldn't be used as arguments in these cases, throwing the famous error "data type unknown", see this simple test case: <br />
<blockquote><div style="font-family: "Courier New",Courier,monospace;"><span style="font-size: small;">select 1 from rdb$database</span></div><div style="font-family: "Courier New",Courier,monospace;"><span style="font-size: small;">union</span></div><span style="font-size: small;"><span style="font-family: 'Courier New', Courier, monospace;">select '1' from rdb$database</span></span></blockquote>Starting with Firebird 2.0, the common data type determination logic has been added, so that heterogeneous expressions are now allowed and the aforementioned test case works as expected. The implementation follows the SQL specification, subclause 9.3 - "Result of data type combinations". In particular, case [3a] declares:<br />
<blockquote>If any of the data types in DTS is character string, then:<br />
<br />
i) All data types in DTS shall be character string, and all of them shall have the same character<br />
repertoire. The character set of the result is the character set of the data type in DTS that has the character encoding form with the highest precedence.</blockquote>This means that in the above heterogeneous example, the resulting value will be a character string and the non-conforming expressions will be implicitly casted to become strings. And it looks sensible, as otherwise (if a numeric would be chosen as the common data type) the following example would cause the data type conversion error:<br />
<blockquote><div style="font-family: "Courier New",Courier,monospace;"><span style="font-size: small;">select 1 from rdb$database</span></div><div style="font-family: "Courier New",Courier,monospace;"><span style="font-size: small;">union</span></div><span style="font-size: small;"><span style="font-family: 'Courier New', Courier, monospace;">select 'a' from rdb$database</span></span></blockquote>However, there's a hidden issue which remained unnoticed for a few recent years.<br />
<br />
<a name='more'></a>A union without the ALL clause implies DISTINCT which in turn involves the comparison operation to decide whether values are equal or distinct. The SQL specification doesn't allow heterogeneous comparisons, so the data type coercion perfectly fits the declared behavior by initially casting the values to the common data type and then comparing them being of the same data type. But Firebird does allow heterogeneous comparisons and it brings a few interesting questions.<br />
<br />
Look at these examples:<br />
<blockquote style="font-family: "Courier New",Courier,monospace;"><span style="font-size: small;">select 1 from rdb$database where 1 = '1'</span> <br />
<span style="font-size: small;">select 1 from rdb$database where 1 = '+1'</span><br />
<span style="font-size: small;">select 1 from rdb$database where 1 = '01'</span><span style="font-size: small;"><br />
select 1 from rdb$database where 1 = '1.0'</span></blockquote>All of them return one row as the result. In order to do so, Firebird involves special comparison priorities, in particular character strings are implicitly casted to numerics and only then compared as numerics. In other words, the common data type becomes numeric. On the negative side, it causes the following example to fail with a conversion error:<br />
<blockquote><span style="font-family: 'Courier New', Courier, monospace; font-size: small;">select 1 from rdb$database where 1 = 'a'</span></blockquote>On the positive side, non-trivial comparisons like the ones mentioned above return the practically correct results. And let's note this is the originally intended behavior which works this way for the past few decades.<br />
<br />
Now let's modify the example with unions a little:<br />
<blockquote style="font-family: "Courier New",Courier,monospace;"><span style="font-size: small;">select 1 from rdb$database<br />
union<br />
</span><span style="font-size: small;">select '1.0' </span><span style="font-size: small;">from rdb$database</span></blockquote>This query returns two resulting rows, thus reporting the values as distinct. But accordingly to the comparison rules, these values are equal! A somewhat tricky situation, isn't it?<br />
<br />
Okay, let's look at other database engines:<br />
<ul><li>PostgreSQL 9.0<br />
<br />
Mixed-type comparisons are not allowed, mixed-type unions are not allowed. The safest but at the same time the most limited solution.<br />
</li>
<li>Oracle 10g<br />
<br />
Mixed-type comparisons are allowed and work like in Firebird, mixed-type unions are not allowed. This matches the situation existed in Firebird prior to version 2.0.<br />
</li>
<li>MSSQL 2008<br />
<br />
Both mixed-type comparisons and unions are allowed and the coercion rules are the same: a character string gets casted to a numeric, like in Firebird. IMHO, this is the most flexible and at the same time absolutely consistent solution, something not really expected from Microsoft ;-) However, beware that (1 union 'a') would throw a data type conversion error.</li>
</ul>Now the question is what to do with Firebird. The current behavior is IMHO wrong. Changing the data type comparison rules is not an option. Reverting back to the pre-v2.0 limitations is not desirable and it's likely to affect backward compatibility. I tend to think that the MSSQL solution is the best one, even knowing that it can potentially break some existing queries. That said, this isn't going to happen in point releases, so only Firebird 3.0 is likely to have the rules changed.</div>Dmitry Yemanovhttp://www.blogger.com/profile/09711564106003431793noreply@blogger.com0tag:blogger.com,1999:blog-3568892889704760508.post-72524887114924189712011-07-10T15:17:00.000+03:002012-01-14T16:13:58.444+03:00Reincarnation of the developer journal<div dir="ltr" style="text-align: left;" trbidi="on">The former Firebird Development Journal that was hosted at the <a href="http://www.firebirdsql.org/">official Firebird site</a> and occasionally maintained by myself now strikes back in a new personal incarnation on the <a href="http://www.blogger.com/">Blogger</a> service with a hope to share more Firebird related information along with other personal blogs maintained by the project members (see the sidebar for the links). Please don't expect me posting frequently but I'll do my best to write about the things I consider worth mentioning.</div>Dmitry Yemanovhttp://www.blogger.com/profile/09711564106003431793noreply@blogger.com0