Predicates in SQL

/10 Comments/in /by

One of the most confusing topics for developers coming from traditional languages like C, C++, Java, C#, and VB to SQL, is the evaluation of predicates. In most languages predicate logic is evaluated left to right. SQL is a different story. It follows the concept of all-at-once operations. This means all logical predicates will be evaluated at the same time. In practice, the SQL engine is free to process the logical expressions in any order it finds appropriate and cost effective to retrieve the data.

Below is example to illustrate this behavior. Given a table with account policies, where a policy may be represented only as numeric values for certain accounts, and a mix of alpha-numeric characters for other accounts. Knowing that account 2 has policies that can be represented only as numeric values, the goal is to retrieve policies that are greater than 50000.

An attempt to add a first predicate to filter on account 2 and then a second one to select policies greater than 50000 will fail. Even if the query is formed with a derived table to extract first all policies for account 2 and then the outer query filters on the policy, it will not work. The derived table is expanded in the main query plan and a single query plan is produced. Then the query engine is free to push up and down the predicates in the plan as it finds efficient.

CREATE TABLE AccountPolicies (
 account_nbr INT,
 policy VARCHAR(20),
 PRIMARY KEY (account_nbr, policy));
 
INSERT INTO AccountPolicies VALUES(1, 'P1000234'); 
INSERT INTO AccountPolicies VALUES(1, 'P1020256');
INSERT INTO AccountPolicies VALUES(2, '1001');
INSERT INTO AccountPolicies VALUES(2, '5002');
INSERT INTO AccountPolicies VALUES(2, '50001');
INSERT INTO AccountPolicies VALUES(2, '50005');
INSERT INTO AccountPolicies VALUES(2, 'P50005');
 
-- Second predicate may be evaluated first
-- resulting in conversion error
SELECT account_nbr, policy
FROM AccountPolicies
WHERE account_nbr = 2
  AND CAST(policy AS INT) > 50000;
 
-- The derived table is expended in the
-- query plan and predicates can be pushed
-- up or down in the plan  
SELECT account_nbr, policy
FROM (SELECT account_nbr, policy
      FROM AccountPolicies
      WHERE account_nbr = 2) AS P
WHERE CAST(policy AS INT) > 50000;

One way to solve this is to use a CASE expression to force the desired evaluation because WHEN clauses in CASE are evaluated in order. This solution not only demonstrates how to handle the evaluation process, but adds verification that only policies with numeric values are casted (as usual business rules change and later account 2 may be allowed to create policies with alpha-numeric characters).

-- Enforce sequence of evaluating conditions
-- and cast only valid values  
SELECT account_nbr, policy
FROM AccountPolicies
WHERE CASE 
        WHEN account_nbr = 2
        THEN CASE 
               WHEN policy NOT LIKE '%[^0-9]%'
               THEN CASE 
                      WHEN CAST(policy AS INT) > 50000
                      THEN 'True'
                      ELSE 'False'
                    END
               ELSE 'False'
             END             
        ELSE 'False'
      END = 'True';

Binding IsChecked property of RadioButton in WPF

/33 Comments/in , , , /by

If you have tried to bind the RadioButton’s IsChecked property in WPF to an object, you have most likely experienced the following problem: In OneWay bindings it works great. But if you have more than one RadioButtons binded TwoWay and you click on an unchecked one, you were expecting that the object to which the previously checked RadioButton was binded to receive the value of False. But you were wrong in your expectations. That’s because for some reasons Microsoft does not obey bindings and does not pass the False value to the DependencyProperty and instead of that they just assign the value False directly to the property, which ruins the binding.

There are many proposed solutions to this around the internet, problem with all those is that they do not work with dynamically generated controls. So since I had to find a way to make this working with dynamic controls, decided to make a wrapper of the real RadioButton which will correctly Bind in two ways. Here is the code for the wrapper:

using System;
using System.IO;
using System.Printing;
using System.Text;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Controls.Primitives;
using System.Windows.Documents;
using System.Windows.Input;
using System.Windows.Interop;
using System.Windows.Media;
using Microsoft.Win32;

namespace Controls
{
    public class RadioButtonExtended : RadioButton
    {
        static bool m_bIsChanging = false;

        public RadioButtonExtended()
        {
            this.Checked += new RoutedEventHandler(RadioButtonExtended_Checked);
            this.Unchecked += new RoutedEventHandler(RadioButtonExtended_Unchecked);
        }

        void RadioButtonExtended_Unchecked(object sender, RoutedEventArgs e)
        {
            if (!m_bIsChanging)
                this.IsCheckedReal = false;
        }

        void RadioButtonExtended_Checked(object sender, RoutedEventArgs e)
        {
            if (!m_bIsChanging)
                this.IsCheckedReal = true;
        }

        public bool? IsCheckedReal
        {
            get { return (bool?)GetValue(IsCheckedRealProperty); }
            set
            {
                SetValue(IsCheckedRealProperty, value);
            }
        }

        // Using a DependencyProperty as the backing store for IsCheckedReal. This enables animation, styling, binding, etc...
        public static readonly DependencyProperty IsCheckedRealProperty =
        DependencyProperty.Register("IsCheckedReal", typeof(bool?), typeof(RadioButtonExtended),
        new FrameworkPropertyMetadata(false, FrameworkPropertyMetadataOptions.Journal |
        FrameworkPropertyMetadataOptions.BindsTwoWayByDefault,
        IsCheckedRealChanged));

        public static void IsCheckedRealChanged(DependencyObject d, DependencyPropertyChangedEventArgs e)
        {
            m_bIsChanging = true;
            ((RadioButtonExtended)d).IsChecked = (bool)e.NewValue;
            m_bIsChanging = false;
        }
    }
}

So now all you have to do is to use the ExtendedRadioButton instead of the built-in one and bind to the IsCheckedReal property instead of the IsChecked one.
Enjoy 🙂