Archive for General programming

Where to put condition in SQL?

Let’s suppose I am modeling a business domain with entities A, B and C. These entities have the following properties:

  • An entity A can have an entity B and C
  • An entity A can have only entity B
  • An entity A can exist without B and C
  • An entity B has not null property Active

I am implementing the domain with the following SQL. I omit foreign key constraints for brevity.


	[Active] BIT NOT NULL,



INSERT INTO [B] ([Id], [AId], [Active]) VALUES (10, 1, 1);
INSERT INTO [B] ([Id], [AId], [Active]) VALUES (20, 2, 0);
INSERT INTO [B] ([Id], [AId], [Active]) VALUES (30, 3, 1);
INSERT INTO [B] ([Id], [AId], [Active]) VALUES (40, 4, 1);

INSERT INTO [C] ([Id], [BId]) VALUES (100, 10);
INSERT INTO [C] ([Id], [BId]) VALUES (200, 20);
INSERT INTO [C] ([Id], [BId]) VALUES (300, 30);

Now let’s suppose my task is to perform validity check according to special rules. I am given an Id of an entity A as an input and I have to check:

  1. If the entity exists and
  2. If it is valid

The existence will be checked by simply looking if corresponding row is present in the result set, and for validity check I will write simple CASE statement. These are my rules for my example data:

  • A.1 exists and has active B.10 and has C.100 => exists, correct
  • A.2 exists and has inactive B.20 and has C.200 => exists, incorrect
  • A.3 exists and has active B.30 and has C.300 => exists, correct
  • A.4 exists and has active B.40 and DOES NOT HAVE C => exists, incorrect
  • A.5 exists and DOES NOT HAVE NEITHER B NOR C => exists, incorrect
  • A.6 does not exist, incorrect

I write the following query to do the task:

  a.Id AS AId,
  ( CASE WHEN ((b.Id IS NOT NULL) AND (c.Id IS NOT NULL) AND (b.Active = 1)) THEN 1 ELSE 0 END ) AS Correct
LEFT JOIN B b ON a.Id = b.AId 
LEFT JOIN C c ON b.Id = c.BId
    a.Id IN (1, 2, 3, 4, 5, 6) 
    AND b.Active = 1

My rules include checking if B.Active is true, so I just put this into WHERE. The result is:

AId  Correct 
---- --------
1    1       
3    1       
4    0       

The problem is, I have been given the exact set of Ids of A to check: 1, 2, 3, 4, 5, 6. But my result does not include 2, 5, 6. My application logic fails here, because it considers those A records as missing. For 6 this is fine, because it is absent in table A, but 2 and 5 must be present in the results for my validity check. The fix is extremely easy:

  a.Id AS AId,
  ( CASE WHEN ((b.Id IS NOT NULL) AND (c.Id IS NOT NULL) AND (b.Active = 1)) THEN 1 ELSE 0 END ) AS Correct
LEFT JOIN B b ON a.Id = b.AId AND b.Active = 1
LEFT JOIN C c ON b.Id = c.BId
    a.Id IN (1, 2, 3, 4, 5, 6) 

Now the result is:

AId  Correct 
---- --------
1    1       
2    0       
3    1       
4    0       
5    0       

It is very easy to understand, that WHERE is applied to filter all the results, no matter what my intention for JOIN was. When a record is LEFT JOINed, the condition is not met, because values from B are null. But I still need to have A record in my results. Thus, what I have to do is to include my condition in JOIN.

It is also very easy to fall into this trap of thoughtlessly writing all intended conditions in the WHERE clause.

How to run Tmux in GIT Bash on Windows

Tmux running under Git Bash default terminal with two shell processes

I know everyone uses Cmder, but it didn’t work for me. It hung a few times, it has way too many options, it has issues sending signal to kill a process. I gave up on using it. I work with carefully configured default Windows console and believe it or not, it serves the purpose. I also know you can use Windows Subsystem For Linux under Windows 10, which is truly amazing, but I am just talking about the cases where you need standard Git for Windows installation.

When I worked with Unix I liked GNU Screen, which is terminal multiplexer. It gives you a bunch of keyboard shortcuts to create separate shell processes under the same terminal window. The problem is, it is not available under GIT Bash. But it turns out, its alternative — Tmux is.

I did a little research and have found that GIT Bash uses MINGW compilation of GNU tools. It uses only selected ones. You can install the whole distribution of the tools from and run a command to install Tmux. And then copy some files to installation folder of Git. This is what you do:

  1. Install before-mentioned msys2 package and run bash shell
  2. Install tmux using the following command: pacman -S tmux
  3. Go to msys2 directory, in my case it is C:\msys64\usr\bin
  4. Copy tmux.exe and msys-event-2-1-4.dll to your Git for Windows directory, mine is C:\Program Files\Git\usr\bin. Please note, that in future, you can see this file with the version number higher than 2-1-4

And you are ready to go. Please note, that I do this on 64-bit installations of Git and MSYS . Now when you run Git Bash enter tmux. My most frequently used commands are:

  • CTRL+B, (release and then) C — create new shell within existing terminal window
  • CTRL+B, N — switch between shells
  • CTRL+B, a digit — switch to the chosen shell by the corresponding number
  • CTRL+B, " — split current window horizontally into panels (panels are inside windows)
  • CTRL+B, o — switch between panels in current window
  • CTRL+B, x — close panel

This is everything you need to know to start using it. Simple. There are many other options which you can explore yourself, for example here

Update 1: Users in comments are reporting the method not always works. If you have any experiences with this method please feel free to comment, so that we can figure out what are the circumstances under which it works

Update2: I managed to run this on Windows 7, Windows 2012 R2 and Windows 10. My Git installation is set up to use MinTTy console and tmux works only when run from this console, not from default Windows command line console. Still haven’t figured out what are precise requirements for this trick

UPDATE with JOIN subtle bug

I have been diagnosing very subtle bug in SQL code which led to unexpected results. It happens under rare circumstances, when you do update with join and you want to increase some number by one. You just write value = value + 1. The thing is, you are willing to increase the value by the number of joined rows. The SQL code kind of expresses your intent. However, what actually happens is, the existing value is read only once. It is updated 3 times, indeed. But with the same value, incremented only by one.

declare @UpdateTarget table(Id bigint, Capacity int); 
insert into @UpdateTarget (Id, Capacity) values (10, 0); -- Lets assume this is our counter of available resources
declare @HowManyTimesUpdate table(TargetId bigint);
insert into @HowManyTimesUpdate (TargetId) values (10); -- Lets assume this is a reservation of a resource
insert into @HowManyTimesUpdate (TargetId) values (10); -- Lets assume this is a reservation of a resource
insert into @HowManyTimesUpdate (TargetId) values (10); -- Lets assume this is a reservation of a resource
-- Now, we are releasing resources. Increase the counter for those 3 reservations
update ut
set ut.Capacity = ut.Capacity + 1
from @UpdateTarget ut join @HowManyTimesUpdate hmt on ut.Id = hmt.TargetId;
-- We expect result 3, because 3 resources should be released, but there is only 1
select Capacity from @UpdateTarget;
-- Reset and start again
update @UpdateTarget set Capacity = 0;
-- Do it right
update ut
set ut.Capacity = ut.Capacity + (select count(1) from @HowManyTimesUpdate where TargetId = [Id])
from @UpdateTarget ut join (select distinct TargetId from @HowManyTimesUpdate) hmt on hmt.[TargetId] = [Id];
-- We expect 3, there is 3
select Capacity from @UpdateTarget

Unix tools always work. Even when Windows ones don’t

In the video above, you can see, that we cannot drag and drop a file onto a PowerShell script. Conversely, we can easily do this with bash script having installed Git for Windows.

I needed to perform some trivial conversion within a SQL script, i.e. replace ROLLBACK with COMMIT. I thought I would implement it with PowerShell. I am not going to comment on the implementation itself, even though it turned out to be not that obvious. Then I realized, it would be nice, if I could drag and drop a file in question to apply the conversions on it.

This does not work with default configuration of PowerShell. I did not have time to hack it somewhere in the registry, as I assume it is doable. I switched to old, good bash shell instead.

It’s a pity I couldn’t do that with Windows native scripting technology. It is very interesting, that MinGW port of shell has been so carefully implemented, that even dragging and dropping works in non-native environment.

I recall the book Pragmatic Programmer: From Journeyman To Master. There is a whole subchapter about the power of Unix tools. The conclusion was, that over time we would come across plenty of distinguished file formats and tools to manipulate data stored with them. Some of them may become forgotten and abandoned years later, making it difficult to extract or modify the data. Some may work only on specific platforms.

But standard Unix tools like shell scripting, Perl, AWK will always exist. I should say: not only will they always exist, but also they will thrive and support almost every platform you can imagine. They work on plain text, which is easy to process. I am a strong proponent of before-mentioned technologies and I have successfully used them many times in everyday work. It sounds particularly strange among .NET developers, but this what it looks like. The PowerShell simply did not do the trick for me. Perl did. As it always does.

For the sake of the future reference I am including the actual scripts:

PowerShell script:

if (!$args[0])
  Write-Host "Please provide target file"
  exit 1

(gc -raw $args[0]) -replace "`nROLLBACK;", "`nCOMMIT;" | sc $args[0]

Bash script running Perl:

perl -pi -w -e 's/^ROLLBACK;/COMMIT;/g;' "$1"

Basic example of SQL Server transaction deadlock with serializable isolation level

Today I am demonstrating a deadlock condition which I came across after I had accidentally increased isolation level to serializable. We can replay this condition with the simplest table possible:

  Id INT,
  Val varchar(8)
INSERT INTO dbo.test VALUES (1, 'aa');

Now let’s open SSMS (BTW, since version 2016 there is an installer decoupled from SQL Server available here) with two separate tabs. Please consider, that each tab has its own connection. Then execute the following statement to increase isolation level in both tabs:


The following code tries to resemble application level function which does a bunch of possibly time consuming things. These are emulated with WAITFOR instruction. But the point is that the transaction does both SELECT and UPDATE on the same table having those time consuming things in between.

  SELECT * FROM dbo.test;
  WAITFOR DELAY '00:00:10';
  raiserror ('after wait', 10,1) with nowait;
  UPDATE [dbo].[test] SET [Val] = 'cccccc' WHERE [Id] = 1;

Let’s put the code in both tabs and then execute one tab and the second tab. After waiting more than 10 seconds, which is the delay in code, we will observe an error message on the first tab:

Msg 1205, Level 13, State 56, Line 8
Transaction (Process ID 54) was deadlocked on lock resources with another 
process and has been chosen as the deadlock victim. Rerun the transaction.

This situation occurred in a web application where concurrent execution of methods is pretty common. For application developer it is very easy to be tricked into thinking that having set SERIALIZABLE isolation level we magically make sequential execution of our SQL code. But this is wrong. By setting SERIALIZABLE level we do not automatically switch the behavior of the code wrapped with transaction to the behavior of lock statement known from C# (technically lock is a monitor).

I would advise having a closer look at the instructions wrapped in transaction. In real application the execution flow is much more `polluted` with an ORM calls, but my simplified code from above just tries to model common scenario of reads followed by writes. What happens here is that SQL Server takes a reader lock on the table after executing the SELECT. When we execute the code again in another session we have one more reader lock taken on the table. Now when the first session passes waitfor and comes to UPDATE it needs to take a writer lock and waits (I am purposely using generic vocabulary instead of SQL Server specific one — these locks inside database engine all have their names). We observe the first tab waits more than 10 seconds. This is because when the first tab reaches its UPDATE it needs to take writer lock, but it is locked by the SELECT in the second tab. Conversely, the second’s tab UPDATE waits for the lock taken by the SELECT in the first tab. This is deadlock which fortunately is detected by the engine.

The problem is caused by the lock taken witch SELECT instruction having SERIALIZABLE isolation level set. The lock is not taken in this place with READ COMMITED which is the default level.

I am writing about this for the following reasons:

  • This is very simple scenario from the application point of view: to read some data, update the data, do some things and have all of this wrapped with a transaction.
  • It is very easy to make wrong assumption, that SERIALIZABLE level guarantees that our SQL code will be executed sequentially. But it only guarantees, that if the transactions execute, their observable effects will be as if they both executed sequentially i.e. one after another. But it is your job to make them actually execute not run into a deadlock.

‘Differs only in casing’ class of problems

Error message from TypeScript compiler

This one attacked me yesterday by the end of the day. This is a TypeScript project with some instrumentation done with gulp. The command shown attempts to launch TypeScript compiler. The console is standard git bash for Windows. I didn’t realize what was going on there at the first sight. The messages were confusing and mysterious. I made sure I didn’t have any changes in the repository comparing to the HEAD. I opened the folder in Visual Studio Code and everything was fine. It was only the compiler which saw the problems.

The next day I figured out there is a small detail in my path. Just take a look at directory names casing:

TypeScript compiler ran successfully

After changing the directory using names with proper casing everything was fine. What actually happened was that, I somehow opened the git bash providing the current directory name using lower case letters. It is not that difficult to do that. This is a flaw in the MINGW implementation of bash, I think. It just allows you to change the directory using its name written with whatever case. That is not the problem itself, because cmd.exe allows you to do so as well. The problem is that it then stores the path (probably in PWD environment variable). Some cross-platform tools, which are case sensitive on other platforms, when executed from git bash with mismatched working directory casing may begin to treat such path as separate one, different from the original one. Especially tools which process files using their paths relative to the current directory, like TypeScript compiler for instance.

This can possibly be a wider class of problems and I guess there are other development tools which behave like that when launched from git bash under before mentioned conditions.