Siderite's blog

I have been asking this of people at the interviews I am conducting and I thought I should document the correct answer and the expected behavior. And yes, we've filled the position in for this interview question, so you can't cheat :)

The question is quite banal: given two tables (TableA and TableB) both having a column ID, select the rows in TableA that don't have any corresponding row in TableB with the same ID.

Whenever you are answering an interview question, remember that your thinking process is just as important as the answer. So saying nothing, while better than "so I am adding 1 and 1 and getting 2", may not be your best option. Assuming you don't know the answer, a reasonable way of tackling any problem is to take it apart and try to solve every part separately. Let's do this here.

As the question requires the rows in A, select them:

SELECT * FROM TableA

Now, a filter should be applied, but which one? Here are some ideas:

  1. WHERE ID NOT IN (SELECT ID FROM TableB)
  2. WHERE NOT EXISTS (SELECT * FROM TableB WHERE TableA.ID=TableB.ID)
  3. EXCEPT SELECT ID FROM TableB -- this requires to select only ID from TableA, as well (EXCEPT and INTERSECT are new additions to SQL 2019)

Think about it. Any issues with any of them? Any other options?

To test performance, I've used two tables with approximately 35 million rows. Here are the results:

  1. After 17 minutes I had to stop the query. Also, NOT IN has issues with NULL as a value is nether equal or unequal to NULL. SELECT * FROM Table WHERE Value NOT IN (NULL) for example, will always return no rows.
  2. It finished within 4 seconds. There are still issues with NULL, though, as a simple equality would not work with NULL. Assuming we wanted the non-null values of TableA, we're good.
  3. It finished within 5 seconds. This doesn't have any issues with NULL. SELECT NULL EXCEPT SELECT NULL will return no rows, while SELECT 1 EXCEPT SELECT NULL will return a row with the value 1. The syntax is pretty ugly though and works badly if the tables have other columns

What about another solution? We've exhausted simple filtering, how about another avenue? Whenever we want to combine information from two tables we use JOIN, but is that the case here?

SELECT * FROM TableA a
JOIN TableB b
ON a.ID = b.ID -- again, while I would ask people in the interview about null values, we will assume for this post that the values are not nullable

I've used a JOIN keyword, which translates to an INNER JOIN. The query above will select rows from A, but only those that have a correspondence in B. A funny solution to a slightly different question: count the items in A that do not have corresponding items in B:

SELECT (SELECT COUNT(*) FROM TableA) - (SELECT COUNT(*) FROM TableA a JOIN TableB b ON a.ID = b.ID)

However, we want the inverse of the INNER JOIN. What other types of JOINs are there? Bonus interview question! And the answers are:

  • INNER JOIN - returns only rows that have been successfully joined
  • OUTER JOIN (LEFT AND RIGHT) - returns all rows of one table joined to the corresponding values of the other table or NULLs if none
  • CROSS JOIN - returns a 

INNER would not work, as demonstrated, so what about a CROSS JOIN? Clearly not, as it will generate 100 trillion rows before filtering anything. SQL Server would optimize a lot of the query, but it would look really weird anyway.

Is there a solution with OUTER JOIN? RIGHT OUTER JOIN will get the rows in B, not in A, so LEFT OUTER JOIN, by elimination, is the only remaining possible solution.

SELECT a.* FROM TableA a 
LEFT OUTER JOIN TableB b
ON a.ID=b.ID

This returns ALL the rows in table A and for each of them, rows in table B that have the same id. In case of a mismatch, though, for a row in table A with no correspondence in table B, we get a row of NULL values. So all we have to do is filter for those. We know that there are no NULLs in the tables, so here is another working solution, solution 4:

SELECT a.* FROM TableA a 
LEFT OUTER JOIN TableB b
ON a.ID=b.ID
WHERE b.ID IS NULL

This solves the problem, as well, in about 4 seconds. However, the other working solution within the same time (solution 2 above) only works as well because newer versions of SQL server are optimizing the execution. Maybe it's a personal preference from the times solution 4 was clearly the best in terms of performance, but I would chose that as the winner.

Summary

  • You can either use NOT EXISTS (and not NOT IN!) or a LEFT OUTER JOIN with a filter on NULL b values.
  • It's important to know if you have NULL values in the joining columns and it's extra points for asking that from your interviewer
  • If not asking, I would penalize solutions that do not take NULL values in consideration. Extra complexity of code, as one cannot simply check for NULL for solution 4. Also a decision has to be made on the expected behavior when working with NULL values
  • When trying to find the solution to a problem in an interview:
    • think of concrete examples of the problem so you can test your solutions
    • break the problems into manageable bits if possible
    • think aloud, but to the point
  • Also, there is nothing more annoying than doing that thing pupils in school do: looking puppy eyed at the teacher while listing solutions to elicit a response. You're not in school anymore. Examples are dirty, time is important, no one cares about your grades.
  • Good luck out there!

TheHatching.jpg The Hatching felt like a cross between The Troop and Infected, but not as cool. The premise, the style and the characters felt artificial, like someone writing by numbers. Common phobias as main subject: check. Characters acting all human and relationshippy: check. Women in positions of power and important characters: check. OK, spiders don't work that way, biology doesn't work that way. If large arthropodes would be capable of coordinating in swarms, eating people, invading a human body and hatching in a matter of hours, they would do it already. There are numerous reasons why they don't, so in fact it was a simple choice: write a less alarming story that is even remotely possible or write something quick, algorithmically and that hopefully sells. Ezekiel Boone chose the latter.

It's not that it's a bad book. Far from it: the familiar writing style and pace made it really easy to read and get into the mood of it. Unfortunately the details were all wrong: the biology, the way everything happens at the same time without any reason to, the politically correct setup that was still sexist because from three lead women characters all of them were sleeping with an underling or thinking about it, plus the extra characters including some gay ones that had no role in the story at all. Now, I understand this is a trilogy or something and those characters will probably play a role later on, but as it stands, The Hatching is simply a bland average book that doesn't even provide closure. If you were caught by the story, you will need to wait until the next book in the series comes out. And for what? To hear about even more people who can't kill spiders or study them in any scientific way until providence saves them because they love their children. Oh, loving ones children as a reason to survive: check.

Bottom line: utterly average and strangely not scary for such a horrific subject.

Siderite's blog

I got this exception that doesn't appear anywhere on Google while I was debugging a .NET Core web app. You just have to enable Windows Authentication in the project properties (Debug tab). Duh!

System.InvalidOperationException: The Negotiate Authentication handler cannot be used on a server that directly supports Windows Authentication. Enable Windows Authentication for the server and the Negotiate Authentication handler will defer to it.
   at Microsoft.AspNetCore.Authentication.Negotiate.PostConfigureNegotiateOptions.PostConfigure(String name, NegotiateOptions options)
   at Microsoft.Extensions.Options.OptionsFactory`1.Create(String name)
   at Microsoft.Extensions.Options.OptionsMonitor`1.<>c__DisplayClass11_0.<Get>b__0()
   at System.Lazy`1.ViaFactory(LazyThreadSafetyMode mode)
   at System.Lazy`1.ExecutionAndPublication(LazyHelper executionAndPublication, Boolean useDefaultConstructor)
   at System.Lazy`1.CreateValue()
   at System.Lazy`1.get_Value()
   at Microsoft.Extensions.Options.OptionsCache`1.GetOrAdd(String name, Func`1 createOptions)
   at Microsoft.Extensions.Options.OptionsMonitor`1.Get(String name)
   at Microsoft.AspNetCore.Authentication.AuthenticationHandler`1.InitializeAsync(AuthenticationScheme scheme, HttpContext context)
   at Microsoft.AspNetCore.Authentication.AuthenticationHandlerProvider.GetHandlerAsync(HttpContext context, String authenticationScheme)
   at Microsoft.AspNetCore.Authentication.AuthenticationService.ChallengeAsync(HttpContext context, String scheme, AuthenticationProperties properties)
   at Microsoft.AspNetCore.Authorization.AuthorizationMiddleware.Invoke(HttpContext context)
   at Microsoft.AspNetCore.Diagnostics.DeveloperExceptionPageMiddleware.Invoke(HttpContext context)

This translates to a change in Properties/launchSettings.json like this:

{
  "iisSettings": {
    "windowsAuthentication": true,
    "anonymousAuthentication": true,
    //...
  },
  //...
}

BurningWhite.jpg  What is burning so white? Love, of course. Brent Weeks ends his Lightbringer saga with a huge book that completes all started threads, brings closure to the grieving, love to the survivors, second and third chances to just about everyone. I liked it, as I did the entire series, but for me Burning White was the weakest book in the series.

  And it wasn't that there was anything wrong with the writing, there was just too much of everything. A lot of new information came along, as it did in many of the other books, but in this, everything was being upended every other chapter. People have lost their memories, then they remembered, then the memories were actually wrong, but they were right, and everybody was being connected, but they didn't actually exist, but they did and everything has a glorious design, but you never find out what the actual design was and what the hell white and black luxin actually do and why people don't use them on a daily basis, etc. There was so much to do in the book that the last ten chapters (three of them called Epilogue) were ALL epilogues and then a post credits scene and even a post book scene. And we still don't know who Kip's other grandfather is.

  I am the first to complain about straight lines in books, but in Burning White, lines go all over the place, loop back on themselves, different colors, shape of kittens, the whole shebang. So in the end, when everything has to come to a close, it all feels really really unnatural and even random. Why did that guy die? Why did this guy live? Why is anyone doing anything?

  I know I am filling this space for nothing. If you are going to read the book you probably read the others in the series and no one is going to stop you now. I am not even suggesting it; this is a great book. However, with great epic stories comes great responsibility to end them right. I don't know exactly why I feel so unsatisfied with the ending, but maybe because the author build up all of these grand heroes, only to kind of make them fail until someone changed their view of the world and helped them out. It invalidates a lot of the previous books. Also, less cool magical mechanisms in this one and a lot more talking and feeling.

Book coverFull title "Factfulness: Ten Reasons We're Wrong About the World – and Why Things Are Better Than You Think, by Hans Rosling, Ola Rosling, Anna Rosling Rönnlund", this is a book that everyone should read. It's not a masterpiece, in fact it feels banal, but it's this banality that makes it so powerful. Hans Rosling is capable of showing with actual proof that your world view is really really wrong. And not only you, but also the politicians that are supposed to fix the world, the specialists that are supposed to find solutions to all of our problems, the reporters who are supposed to report on the news and the large majority of people are just as confused as you are.

And it is proof of the power of the book that someone like me, who knew who Rosling was and watched his very inspiring TED Talks, was still surprised by its content and teachings.

The book claims that the instincts that helped our ancestors survive are clouding our judgement of the much less dangerous, but more abstract modern world we live in. Rosling enumerates ten, with anecdotes that feel like scenes from TV dramas only to make you doubt who is the villain and who are the heroes. It is the main idea of book that only by working with fact based information we can make the best decisions and take the best possible action to move us forward.

This is not a book that will amaze you with its writing style, it will not make you laugh out loud, nor is it intended to make you feel good, although it probably will. It is facts about things that you felt you knew, but in fact were ignorant of. It is for example heartbreaking to read the part about immigrants in Europe dying and being taken advantage of because of really dumb European laws, not because of their destination countries regimes or from poverty. It is uplifting to know how much the world has changed for the better in just a few decades and all over the place. As a Romanian, I've always assumed that the rest of the world was rich and plentiful outside the Iron Curtain and that the recent improvements in life style were caused mostly by the fall of Communism in the area. I had no idea how similar life was for so many around the time I was born and that life has improved dramatically all over, not just here. It is empowering to know that women make more or less children in consistent correlation to their income and not their culture, religion or regime. When people don't need to have children, they don't make them, regardless of what Mao, the Pope or anyone else says.

Bottom line: you have to read this just as much as you have to get a general education. It simply should be taught in schools.

Siderite's blog

I had this annoying warning that appeared whenever I would build my Wix setup project. I know, Wix sucks, but what can you do? The warning was warning MSB3270: There was a mismatch between the processor architecture of the project being built "MSIL" and the processor architecture of the reference "SomeProject\Some.dll", "AMD64". This mismatch may cause runtime failures. Please consider changing the targeted processor architecture of your project through the Configuration Manager so as to align the processor architectures between your project and references, or take a dependency on references with a processor architecture that matches the targeted processor architecture of your project.

What is worse, the warning would come in the VS Errors window with no warning code, so I couldn't even supress it. And yes, the text above does show a warning code, but it just wouldn't be intrepreted by Visual Studio.

In order to fix it, apparently I had to set ResolveAssemblyWarnOrErrorOnTargetArchitectureMismatch to None, as according to this StackOverflow answer. But where should I add this wonderful property? It didn't work in the .wixproj file, it wouldn't make a difference in the projects mentioned in the warning.

In the end, I decided to customize my build! Final solution:

  • add a file called Directory.Build.props to your solution folder
  • paste in it
<Project>
 <PropertyGroup>
   <ResolveAssemblyWarnOrErrorOnTargetArchitectureMismatch>None</ResolveAssemblyWarnOrErrorOnTargetArchitectureMismatch>
 </PropertyGroup>
</Project>

Siderite's blog

I will show you some code, like in an interview question. Try to figure out what happened, then read on. The question is: what does the following code write to the console:

class Program
{
    static void Main(string[] args)
    {
        var c = new MyClass();
        c.DoWork();
        Console.ReadKey();
    }
}
 
class BaseClass
{
    public BaseClass()
    {
        DoWork();
    }
 
    public virtual void DoWork()
    {
        Console.WriteLine("Doing work in the base class");
    }
}
 
class MyClass:BaseClass
{
    private readonly string _myString = "I've set the string directly in the field";
 
    public MyClass()
    {
        _myString = "I've set the string in the constructor of MyClass";
    }
 
    public override void DoWork()
    {
        Console.WriteLine($"I am doing work in MyClass with my string: {_myString}");
    }
}

Click to expand

PermanentRecord.jpgPermanent Record is a very well written book. Ed Snowden has the rare capacity of deep introspection and when he explains what happened and why, he makes connections to his upbringing, events in his life and the world and has a very clear view of his internal processes when making any decision or taking action. He also has a way with words. He's a guy easy to either hate or love, obviously, depending on how prone to envy one is.

There are several important takes from this book.

One is how extraordinary Snowden's position was to make him able to do what he did. Not only is he this brilliant guy who lives in his head and plans everything in advance, not only did he have specialized training on how to do what he did and get away with it, but he was given free access to the underlying technology of the CIA and the NSA. As he himself admits, he was on a list that probably contained under a dozen people, and that only from incompetence and lack of oversight.

Another is that every large organization is the same. Be it the NSA, the state run companies or private corporations in any country, they all impose hiring limits that they break by using contractors that don't affect the count (or their declared principles). It's amazing how similar my own experiences were, but in (supposedly) very different contexts. The contractor system is a global disease that too few benefit from and too many are affected by.

A more salient point is that technology is what happens when there is something we can do. We will ultimately do it, just because we can. As much sympathy I have for Snowden, his actions will probably have short lived consequences. The machine to get all the data from everybody is already there. No one will put that genie back into the bottle. If anything this book helps the very people who the author hopes to thwart by giving them valuable intelligence in the mindset of the whistleblower and on the limitations and vulnerabilities of their technical, administrative and indoctrination systems. As I was saying before, the chances that Snowden happened were astronomically small. Now they are orders of magnitude smaller.

It was devastatingly depressing to read how few places in the world are even marginally free of the American hegemony. Ironically, he had to go to Hong Kong to maximize his chances of remaining free. That says something terrible about the state of the world. I felt like the most hard hitting bits were those that explained what the government would do to discredit him (or anybody in their way), using deeply personal histories, fabricating evidence if none found or amplifying the one that does exist. This makes Snowden even more outstanding, for being a guy with very little dirt in his life to be turned into a weapon.

On a more personal note, it's amazing that reading Permanent Record, I was reminded most of Gary Sinise's autobiography than anything else. Thinking of what Snowden did, a family tradition of serving the country military and strong patriotic spirit is not what would first come to mind, yet that applies to him and he did what he did because he strongly believed in his country and in service to it. Even now, hunted by the US government in the most blunt, unimaginative, bureaucratic and cruel way possible, like big bullies do, Ed Snowden still loves his country and believes in it.

At a glance, Permanent Record is a book on hacking. As a child, Ed starts understanding how the world works and manipulates it to his advantage, only to grow up working in an organization that has hacked the Constitution and society as a whole in order to manipulate it to their advantage. People of our generation grew up with at least the illusion of a fair society, people who would take you at your value and judge you on it, whatever the outcome. The new hacked society cannot see you as you are, to the point that makes you doubt yourself of who you are. There cannot be a fair process of evaluation anymore because everyone comes out distorted.

This is a must read book and I recommend it highly to all adult readers of any level or education. It's easy to read, captivating and relatively small. Fair warning: it's not a happy story.

Siderite's blog

Let's say you have a Type and you want to find it by the simple name, not the entire namespace. So for string, for example, you want to use Boolean, not System.Boolean. And if you try in your code typeof(bool).Name you get "Boolean" and for typeof(bool).FullName you get "System.Boolean".

However, for generic types, that is not the case. Try typeof(int?). For FullName you get "System.Nullable`1[[System.Int32, System.Private.CoreLib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e]]", but for Name you get "Nullable`1".

So the "name" of the type is just that, the name. In case of generics, the name of the type is the same as the name of its generic definition. I find this a bit disingenuous, because in the name you get encoded the fact that is a generic type or not and how many generic type attributes it has, but you don't get the attributes themselves.

I admit that if I had to make a choice, I couldn't have come up with one to satisfy all demands, either. Just a heads up that Type.Name should probably not be used anywhere.

Siderite's blog

Welcome to my new blog!

Please let me know what you think.

Siderite's blog

I've had some issues with Blogger and that pushed me towards the step that I have been too lazy to make for so long a time: my own domain and hosting. So, from today on, my personal blog is at https://siderite.dev. I am working on it, still, so forgive the occasional hiccups.

Let me know if anything is wrong or, even better, that everything is right!

Siderite

book cover I haven't been as frustrated with a book as with Lightless for a long time. Everyone in it is incompetent to the level of cretinism. The only people remotely good at what they do are psychos, all of them.

I understand why books like these need to be written: when you write a character based play-like story you become a better writer, but it doesn't mean what you write is any good. In this case, a character based story has cardboard shapes moving around randomly instead of characters. It failed. The main character is a computer programmer. As one, I felt insulted that C.A. Higgins believes we are that moronic.

As for the plot, that's the most redeeming factor, but it's not great either. In a future where mobile devices don't exist and locks are still based on physical keys and the Solar System is under the iron boot control of ... err... The System, an organization straight out of Blake's 7, with the same cartoonish efficiency, a mysterious ship is boarded by two people who turn out to be criminals the System wants. 90% of the book is people being dumb and talking to each other, all while assuming they are the bread of the universe. The rest is people doing really dumb things.

Conclusion: all the "smart" bits of the book were obvious from the start, making the journey of the buffoons in the story to figure them out really boring. The idea of the Ananke was added only to make this, of course, a trilogy. It reads like a theater play with people being stuck in a room.

Siderite's blog FizzBuzz is a programming task that is often used for job interviews, because it shows the thinking of the candidate in an isolated and concrete case. The requirements may vary slightly, but is goes like this: A loop is going through numbers from 1 to 100. The candidate must write the code in the loop that will display, for each number, a string. This string is: "Fizz" for numbers divisible by 3, "Buzz" divisible by 5 and "FizzBuzz" for numbers that are both divisible by 3 and 5. For all other numbers, just display that number.

There have been many implementations of this, for example my own in JavaScript from a while ago and FizzBuzz Enterprise Edition, which is always a good read about how not to complicate your code. However, since I've last written about it, JavaScript has changed, so I felt compelled to write an updated version. And here it is:
(d=n=>('0369'.includes((f=n=>n>9&&f([...''+n].reduce((s,v)=>+v+s,0))||n)&&f(n))&&'Fizz'||'')+(/[05]$/.test(n)&&'Buzz'||'')||n)*(i=n=>n>100||console.log(n+': '+d(n))+i(n+1))*i(1)

Among the abominations there, some of them inspired by C++ because why not, there are some of the new JavaScript constructs, like arrow functions and the spread operator. I know it seems pointless, but it's not: try to understand what the code does. If you want to see it in action, open Dev Tools in any browser and copy paste it in the console.

Siderite's blog A funny feature that I've encountered recently. It's not something most people would find useful, but it helps tremendously with tracing and debugging what is going on. It's easy, just add .TagWith(someString) to your LINQ query and it will generate comments in SQL. More details here: Query tags.

Siderite's blog One of the best things you could do in software is unit testing. There are tons of articles, including mine, explaining why people should take the time to write code in a way that makes it easily split into independent parts that then can automatically tested. The part that is painful comes afterwards, when you've written your software, put it in production and you are furiously working for the second iteration. Traditionally, unit tests are great for refactorings, but when you are changing the existing code, you need not only to "fix" the tests, but also cover the new scenarios, allow for changes and expansions of existing ones.

Long story short, you will not be able to be confident your test suite covers the code as it changes until you can compute something called Code Coverage, or the amount of your code that is traversed during unit tests. Mind you, this is not a measure of how much of your functionality is covered, only the lines of code. In Visual Studio, they did a dirty deed and restricted the functionality to the Enterprise edition. But I am here to tell you that in .NET Core (and possibly for Framework, too, but I haven't tested it) it's very easy to have all the functionality and more even for the free version of Visual Studio.

These are the steps you have to take:

  • Add coverlet.msbuild NuGet to your unit tests project
  • Add ReportGenerator NuGet to your unit tests project
  • Write a batch file that looks like

    @ECHO OFF
    REM You need to add references to the nuget packages ReportGenerator and coverlet.msbuild
    IF NOT EXIST "..\..\packages\reportgenerator" (
      ECHO You need to install the ReportGenerator by Daniel Palme nuget
      EXIT 1
    )
    IF NOT EXIST "..\..\packages\coverlet.msbuild" (
      ECHO You need to install the coverlet.msbuild by tonerdo nuget
      EXIT 1
    )
    IF EXIST "bin/CoverageReport" RMDIR /Q /S "bin/CoverageReport"
    IF EXIST "bin/coverage.opencover.xml" DEL /F "bin/coverage.opencover.xml"
    dotnet test "Primus.Core.UnitTests.csproj"  --collect:"code coverage" /p:CollectCoverage=true /p:CoverletOutputFormat=\"opencover\" /p:CoverletOutput=\"bin/coverage\"
    for /f "tokens=*" %%a in ('dir ..\..\packages\reportgenerator /b /od') do set newest=%%a
    "..\..\packages\reportgenerator\%newest%\tools\netcoreapp3.0\ReportGenerator.exe" "-reports:bin\coverage.opencover.xml" "-targetdir:bin/CoverageReport" "-assemblyfilters:-*.DAL*" "-filefilters:-*ServiceCollectionExtensions.cs"
    start "Primus Plumbing Code Coverage Report" "bin/CoverageReport/index.htm"​


    and save it in your unit test project folder
  • Optional: follow this answer on StackOverflow to be able to see the coverage directly in Visual Studio


Notes about the batch file:

  • newest is the current version of ReportGenerator, if that doesn't work, change it with whatever version you have (ex: 4.3.0)
  • the DAL filter tells the report to ignore projects with DAL in their name. You shouldn't have to unit test your data access layer.
  • the ServiceCollectionExtensions.cs filter is for files that should be ignored, like extension methods rarely need to be unit tested


Running the batch should start dotnet test and save the result in both coverage.opencover.xml and also some files in the TestResults folder. Then ReportGenerator will generate an html file with the coverage report that will get open at the end. However, if you followed the optional answer, now you are able to open the files in TestResults and see what parts of your code are covered when opened in the Visual Studio editor! I found this less useful than the HTML report, but some of my colleagues liked the option.

I hope this helps you.