20190910_SQL_Server_Values_in_Set_Issue_695.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import great_expectations as ge\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "import datetime\n",
    "import sqlalchemy as sa\n",
    "from sqlalchemy.orm import sessionmaker"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "## Put some random data in sufficient for replicating the issue"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "arr = np.random.normal(0, 1, size=(100, 10))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "date = np.full(100, datetime.datetime.now())\n",
    "diffs = np.array([datetime.timedelta(days=int(k)) for k in np.random.choice([-2, -1, 0], size=100)])\n",
    "dates = date + diffs"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "missings = np.random.choice([np.nan, 0,1,2,3,4], size=100)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.DataFrame(arr)\n",
    "df[\"dates\"] = dates\n",
    "df[\"missings\"] = missings"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>0</th>\n",
       "      <th>1</th>\n",
       "      <th>2</th>\n",
       "      <th>3</th>\n",
       "      <th>4</th>\n",
       "      <th>5</th>\n",
       "      <th>6</th>\n",
       "      <th>7</th>\n",
       "      <th>8</th>\n",
       "      <th>9</th>\n",
       "      <th>dates</th>\n",
       "      <th>missings</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>0.129241</td>\n",
       "      <td>1.066469</td>\n",
       "      <td>1.462701</td>\n",
       "      <td>-1.340763</td>\n",
       "      <td>-0.718360</td>\n",
       "      <td>-0.030121</td>\n",
       "      <td>-0.432858</td>\n",
       "      <td>0.160536</td>\n",
       "      <td>0.480938</td>\n",
       "      <td>2.055168</td>\n",
       "      <td>2019-09-11 23:14:04.248406</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>-0.546883</td>\n",
       "      <td>-0.948041</td>\n",
       "      <td>0.857218</td>\n",
       "      <td>0.417211</td>\n",
       "      <td>-2.475520</td>\n",
       "      <td>0.182786</td>\n",
       "      <td>1.145868</td>\n",
       "      <td>1.706848</td>\n",
       "      <td>0.285012</td>\n",
       "      <td>-1.139483</td>\n",
       "      <td>2019-09-10 23:14:04.248406</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>0.732611</td>\n",
       "      <td>1.080726</td>\n",
       "      <td>1.799009</td>\n",
       "      <td>-1.324832</td>\n",
       "      <td>-0.042106</td>\n",
       "      <td>-0.633771</td>\n",
       "      <td>-0.637599</td>\n",
       "      <td>1.497173</td>\n",
       "      <td>0.182029</td>\n",
       "      <td>0.901961</td>\n",
       "      <td>2019-09-11 23:14:04.248406</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>0.040090</td>\n",
       "      <td>-0.280354</td>\n",
       "      <td>0.015777</td>\n",
       "      <td>1.014472</td>\n",
       "      <td>1.557504</td>\n",
       "      <td>2.096489</td>\n",
       "      <td>-0.548691</td>\n",
       "      <td>-0.354212</td>\n",
       "      <td>-0.193882</td>\n",
       "      <td>-1.376235</td>\n",
       "      <td>2019-09-09 23:14:04.248406</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>0.517361</td>\n",
       "      <td>0.007613</td>\n",
       "      <td>0.560541</td>\n",
       "      <td>-0.802848</td>\n",
       "      <td>-0.899714</td>\n",
       "      <td>0.310679</td>\n",
       "      <td>-1.913699</td>\n",
       "      <td>0.024944</td>\n",
       "      <td>0.160441</td>\n",
       "      <td>0.293889</td>\n",
       "      <td>2019-09-11 23:14:04.248406</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          0         1         2         3         4         5         6  \\\n",
       "0  0.129241  1.066469  1.462701 -1.340763 -0.718360 -0.030121 -0.432858   \n",
       "1 -0.546883 -0.948041  0.857218  0.417211 -2.475520  0.182786  1.145868   \n",
       "2  0.732611  1.080726  1.799009 -1.324832 -0.042106 -0.633771 -0.637599   \n",
       "3  0.040090 -0.280354  0.015777  1.014472  1.557504  2.096489 -0.548691   \n",
       "4  0.517361  0.007613  0.560541 -0.802848 -0.899714  0.310679 -1.913699   \n",
       "\n",
       "          7         8         9                      dates  missings  \n",
       "0  0.160536  0.480938  2.055168 2019-09-11 23:14:04.248406       3.0  \n",
       "1  1.706848  0.285012 -1.139483 2019-09-10 23:14:04.248406       0.0  \n",
       "2  1.497173  0.182029  0.901961 2019-09-11 23:14:04.248406       0.0  \n",
       "3 -0.354212 -0.193882 -1.376235 2019-09-09 23:14:04.248406       2.0  \n",
       "4  0.024944  0.160441  0.293889 2019-09-11 23:14:04.248406       2.0  "
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "engine = sa.create_engine(\"mssql+pymssql://USER:PASS@HOST.rds.amazonaws.com:1433/DB\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.to_sql(\"ge_community_replication_20190910_01\", engine, if_exists=\"replace\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[(datetime.datetime(2019, 9, 11, 23, 14, 4, 247000), 3.0),\n",
       " (datetime.datetime(2019, 9, 10, 23, 14, 4, 247000), 0.0),\n",
       " (datetime.datetime(2019, 9, 11, 23, 14, 4, 247000), 0.0),\n",
       " (datetime.datetime(2019, 9, 9, 23, 14, 4, 247000), 2.0),\n",
       " (datetime.datetime(2019, 9, 11, 23, 14, 4, 247000), 2.0),\n",
       " (datetime.datetime(2019, 9, 9, 23, 14, 4, 247000), 1.0),\n",
       " (datetime.datetime(2019, 9, 9, 23, 14, 4, 247000), 3.0),\n",
       " (datetime.datetime(2019, 9, 9, 23, 14, 4, 247000), 4.0),\n",
       " (datetime.datetime(2019, 9, 11, 23, 14, 4, 247000), 1.0),\n",
       " (datetime.datetime(2019, 9, 10, 23, 14, 4, 247000), 4.0)]"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "engine.execute(\n",
    "    sa.select([sa.column(\"dates\"), sa.column(\"missings\")]).select_from(sa.table(\"ge_community_replication_20190910_01\")).limit(10)).fetchall()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "gedf = ge.dataset.SqlAlchemyDataset('ge_community_replication_20190910_01', engine)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'success': True}"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "gedf.expect_column_to_exist(\"dates\")\n",
    "gedf.expect_column_to_exist(\"missings\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "ename": "ProgrammingError",
     "evalue": "(pymssql.ProgrammingError) (102, b\"Incorrect syntax near '='.DB-Lib error message 20018, severity 15:\\nGeneral SQL Server error: Check messages from the SQL Server\\n\")\n[SQL: SELECT TOP 20 missings \nFROM ge_community_replication_20190910_01 \nWHERE NOT (missings >= %(missings_1)s AND missings <= %(missings_2)s) AND (CASE WHEN (missings IS NULL) THEN %(param_1)s ELSE %(param_2)s END = 1 OR (missings IN (NULL)) = 0)]\n[parameters: {'missings_1': -4, 'missings_2': 4, 'param_1': 0, 'param_2': 1}]\n(Background on this error at: http://sqlalche.me/e/f405)",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mMSSQLDatabaseException\u001b[0m                    Traceback (most recent call last)",
      "\u001b[0;32msrc/pymssql.pyx\u001b[0m in \u001b[0;36mpymssql.Cursor.execute\u001b[0;34m()\u001b[0m\n",
      "\u001b[0;32msrc/_mssql.pyx\u001b[0m in \u001b[0;36m_mssql.MSSQLConnection.execute_query\u001b[0;34m()\u001b[0m\n",
      "\u001b[0;32msrc/_mssql.pyx\u001b[0m in \u001b[0;36m_mssql.MSSQLConnection.execute_query\u001b[0;34m()\u001b[0m\n",
      "\u001b[0;32msrc/_mssql.pyx\u001b[0m in \u001b[0;36m_mssql.MSSQLConnection.format_and_run_query\u001b[0;34m()\u001b[0m\n",
      "\u001b[0;32msrc/_mssql.pyx\u001b[0m in \u001b[0;36m_mssql.check_cancel_and_raise\u001b[0;34m()\u001b[0m\n",
      "\u001b[0;32msrc/_mssql.pyx\u001b[0m in \u001b[0;36m_mssql.maybe_raise_MSSQLDatabaseException\u001b[0;34m()\u001b[0m\n",
      "\u001b[0;31mMSSQLDatabaseException\u001b[0m: (102, b\"Incorrect syntax near '='.DB-Lib error message 20018, severity 15:\\nGeneral SQL Server error: Check messages from the SQL Server\\n\")",
      "\nDuring handling of the above exception, another exception occurred:\n",
      "\u001b[0;31mProgrammingError\u001b[0m                          Traceback (most recent call last)",
      "\u001b[0;32m~/anaconda3/envs/dsci/lib/python3.6/site-packages/sqlalchemy/engine/base.py\u001b[0m in \u001b[0;36m_execute_context\u001b[0;34m(self, dialect, constructor, statement, parameters, *args)\u001b[0m\n\u001b[1;32m   1243\u001b[0m                     self.dialect.do_execute(\n\u001b[0;32m-> 1244\u001b[0;31m                         \u001b[0mcursor\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mstatement\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mparameters\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mcontext\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m   1245\u001b[0m                     )\n",
      "\u001b[0;32m~/anaconda3/envs/dsci/lib/python3.6/site-packages/sqlalchemy/engine/default.py\u001b[0m in \u001b[0;36mdo_execute\u001b[0;34m(self, cursor, statement, parameters, context)\u001b[0m\n\u001b[1;32m    551\u001b[0m     \u001b[0;32mdef\u001b[0m \u001b[0mdo_execute\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mcursor\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mstatement\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mparameters\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mcontext\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0;32mNone\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 552\u001b[0;31m         \u001b[0mcursor\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mexecute\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mstatement\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mparameters\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    553\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32msrc/pymssql.pyx\u001b[0m in \u001b[0;36mpymssql.Cursor.execute\u001b[0;34m()\u001b[0m\n",
      "\u001b[0;31mProgrammingError\u001b[0m: (102, b\"Incorrect syntax near '='.DB-Lib error message 20018, severity 15:\\nGeneral SQL Server error: Check messages from the SQL Server\\n\")",
      "\nThe above exception was the direct cause of the following exception:\n",
      "\u001b[0;31mProgrammingError\u001b[0m                          Traceback (most recent call last)",
      "\u001b[0;32m<ipython-input-13-345fbe647870>\u001b[0m in \u001b[0;36m<module>\u001b[0;34m\u001b[0m\n\u001b[1;32m      2\u001b[0m \u001b[0;31m## The extra complication was added in response to an issue on Redshift. See:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m      3\u001b[0m \u001b[0;31m#  https://github.com/great-expectations/great_expectations/issues/392\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m----> 4\u001b[0;31m \u001b[0mgedf\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mexpect_column_values_to_be_between\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m\"missings\"\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m-\u001b[0m\u001b[0;36m4\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;36m4\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m",
      "\u001b[0;32m~/dev/great_expectations/great_expectations/data_asset/util.py\u001b[0m in \u001b[0;36mf\u001b[0;34m(*args, **kwargs)\u001b[0m\n\u001b[1;32m     84\u001b[0m         \u001b[0;34m@\u001b[0m\u001b[0mwraps\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mmthd\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0massigned\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m'__name__'\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m'__module__'\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m     85\u001b[0m         \u001b[0;32mdef\u001b[0m \u001b[0mf\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m*\u001b[0m\u001b[0margs\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m**\u001b[0m\u001b[0mkwargs\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m---> 86\u001b[0;31m             \u001b[0;32mreturn\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mmthd\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mobj\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m*\u001b[0m\u001b[0margs\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m**\u001b[0m\u001b[0mkwargs\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m     87\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m     88\u001b[0m         \u001b[0mf\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m__doc__\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mdoc\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/dev/great_expectations/great_expectations/data_asset/data_asset.py\u001b[0m in \u001b[0;36mwrapper\u001b[0;34m(self, *args, **kwargs)\u001b[0m\n\u001b[1;32m    227\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    228\u001b[0m                         \u001b[0;32melse\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 229\u001b[0;31m                             \u001b[0;32mraise\u001b[0m \u001b[0merr\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    230\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    231\u001b[0m                 \u001b[0;32melse\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/dev/great_expectations/great_expectations/data_asset/data_asset.py\u001b[0m in \u001b[0;36mwrapper\u001b[0;34m(self, *args, **kwargs)\u001b[0m\n\u001b[1;32m    214\u001b[0m                 \u001b[0;32mif\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_config\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mget\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m\"interactive_evaluation\"\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;32mTrue\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    215\u001b[0m                     \u001b[0;32mtry\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 216\u001b[0;31m                         \u001b[0mreturn_obj\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mfunc\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m**\u001b[0m\u001b[0mevaluation_args\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    217\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    218\u001b[0m                     \u001b[0;32mexcept\u001b[0m \u001b[0mException\u001b[0m \u001b[0;32mas\u001b[0m \u001b[0merr\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/dev/great_expectations/great_expectations/dataset/sqlalchemy_dataset.py\u001b[0m in \u001b[0;36minner_wrapper\u001b[0;34m(self, column, mostly, result_format, *args, **kwargs)\u001b[0m\n\u001b[1;32m    137\u001b[0m                                 \u001b[0;31m# Ignore any other values that are in the ignore list\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    138\u001b[0m                                 sa.column(column).in_(ignore_values) == False))\n\u001b[0;32m--> 139\u001b[0;31m                 ).limit(unexpected_count_limit)\n\u001b[0m\u001b[1;32m    140\u001b[0m             )\n\u001b[1;32m    141\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/anaconda3/envs/dsci/lib/python3.6/site-packages/sqlalchemy/engine/base.py\u001b[0m in \u001b[0;36mexecute\u001b[0;34m(self, statement, *multiparams, **params)\u001b[0m\n\u001b[1;32m   2167\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   2168\u001b[0m         \u001b[0mconnection\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_contextual_connect\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mclose_with_result\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0;32mTrue\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 2169\u001b[0;31m         \u001b[0;32mreturn\u001b[0m \u001b[0mconnection\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mexecute\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mstatement\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m*\u001b[0m\u001b[0mmultiparams\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m**\u001b[0m\u001b[0mparams\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m   2170\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   2171\u001b[0m     \u001b[0;32mdef\u001b[0m \u001b[0mscalar\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mstatement\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m*\u001b[0m\u001b[0mmultiparams\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m**\u001b[0m\u001b[0mparams\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/anaconda3/envs/dsci/lib/python3.6/site-packages/sqlalchemy/engine/base.py\u001b[0m in \u001b[0;36mexecute\u001b[0;34m(self, object_, *multiparams, **params)\u001b[0m\n\u001b[1;32m    986\u001b[0m             \u001b[0;32mraise\u001b[0m \u001b[0mexc\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mObjectNotExecutableError\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mobject_\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    987\u001b[0m         \u001b[0;32melse\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 988\u001b[0;31m             \u001b[0;32mreturn\u001b[0m \u001b[0mmeth\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mmultiparams\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mparams\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    989\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    990\u001b[0m     \u001b[0;32mdef\u001b[0m \u001b[0m_execute_function\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mfunc\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mmultiparams\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mparams\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/anaconda3/envs/dsci/lib/python3.6/site-packages/sqlalchemy/sql/elements.py\u001b[0m in \u001b[0;36m_execute_on_connection\u001b[0;34m(self, connection, multiparams, params)\u001b[0m\n\u001b[1;32m    285\u001b[0m     \u001b[0;32mdef\u001b[0m \u001b[0m_execute_on_connection\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mconnection\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mmultiparams\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mparams\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    286\u001b[0m         \u001b[0;32mif\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0msupports_execution\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 287\u001b[0;31m             \u001b[0;32mreturn\u001b[0m \u001b[0mconnection\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_execute_clauseelement\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mmultiparams\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mparams\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    288\u001b[0m         \u001b[0;32melse\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    289\u001b[0m             \u001b[0;32mraise\u001b[0m \u001b[0mexc\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mObjectNotExecutableError\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/anaconda3/envs/dsci/lib/python3.6/site-packages/sqlalchemy/engine/base.py\u001b[0m in \u001b[0;36m_execute_clauseelement\u001b[0;34m(self, elem, multiparams, params)\u001b[0m\n\u001b[1;32m   1105\u001b[0m             \u001b[0mdistilled_params\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1106\u001b[0m             \u001b[0mcompiled_sql\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 1107\u001b[0;31m             \u001b[0mdistilled_params\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m   1108\u001b[0m         )\n\u001b[1;32m   1109\u001b[0m         \u001b[0;32mif\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_has_events\u001b[0m \u001b[0;32mor\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mengine\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_has_events\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/anaconda3/envs/dsci/lib/python3.6/site-packages/sqlalchemy/engine/base.py\u001b[0m in \u001b[0;36m_execute_context\u001b[0;34m(self, dialect, constructor, statement, parameters, *args)\u001b[0m\n\u001b[1;32m   1246\u001b[0m         \u001b[0;32mexcept\u001b[0m \u001b[0mBaseException\u001b[0m \u001b[0;32mas\u001b[0m \u001b[0me\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1247\u001b[0m             self._handle_dbapi_exception(\n\u001b[0;32m-> 1248\u001b[0;31m                 \u001b[0me\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mstatement\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mparameters\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mcursor\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mcontext\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m   1249\u001b[0m             )\n\u001b[1;32m   1250\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/anaconda3/envs/dsci/lib/python3.6/site-packages/sqlalchemy/engine/base.py\u001b[0m in \u001b[0;36m_handle_dbapi_exception\u001b[0;34m(self, e, statement, parameters, cursor, context)\u001b[0m\n\u001b[1;32m   1464\u001b[0m                 \u001b[0mutil\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mraise_from_cause\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mnewraise\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mexc_info\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1465\u001b[0m             \u001b[0;32melif\u001b[0m \u001b[0mshould_wrap\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 1466\u001b[0;31m                 \u001b[0mutil\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mraise_from_cause\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0msqlalchemy_exception\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mexc_info\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m   1467\u001b[0m             \u001b[0;32melse\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1468\u001b[0m                 \u001b[0mutil\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mreraise\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m*\u001b[0m\u001b[0mexc_info\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/anaconda3/envs/dsci/lib/python3.6/site-packages/sqlalchemy/util/compat.py\u001b[0m in \u001b[0;36mraise_from_cause\u001b[0;34m(exception, exc_info)\u001b[0m\n\u001b[1;32m    396\u001b[0m     \u001b[0mexc_type\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mexc_value\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mexc_tb\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mexc_info\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    397\u001b[0m     \u001b[0mcause\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mexc_value\u001b[0m \u001b[0;32mif\u001b[0m \u001b[0mexc_value\u001b[0m \u001b[0;32mis\u001b[0m \u001b[0;32mnot\u001b[0m \u001b[0mexception\u001b[0m \u001b[0;32melse\u001b[0m \u001b[0;32mNone\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 398\u001b[0;31m     \u001b[0mreraise\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mtype\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mexception\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mexception\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mtb\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mexc_tb\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mcause\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mcause\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    399\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    400\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/anaconda3/envs/dsci/lib/python3.6/site-packages/sqlalchemy/util/compat.py\u001b[0m in \u001b[0;36mreraise\u001b[0;34m(tp, value, tb, cause)\u001b[0m\n\u001b[1;32m    150\u001b[0m             \u001b[0mvalue\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m__cause__\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mcause\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    151\u001b[0m         \u001b[0;32mif\u001b[0m \u001b[0mvalue\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m__traceback__\u001b[0m \u001b[0;32mis\u001b[0m \u001b[0;32mnot\u001b[0m \u001b[0mtb\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 152\u001b[0;31m             \u001b[0;32mraise\u001b[0m \u001b[0mvalue\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mwith_traceback\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mtb\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    153\u001b[0m         \u001b[0;32mraise\u001b[0m \u001b[0mvalue\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    154\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/anaconda3/envs/dsci/lib/python3.6/site-packages/sqlalchemy/engine/base.py\u001b[0m in \u001b[0;36m_execute_context\u001b[0;34m(self, dialect, constructor, statement, parameters, *args)\u001b[0m\n\u001b[1;32m   1242\u001b[0m                 \u001b[0;32mif\u001b[0m \u001b[0;32mnot\u001b[0m \u001b[0mevt_handled\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1243\u001b[0m                     self.dialect.do_execute(\n\u001b[0;32m-> 1244\u001b[0;31m                         \u001b[0mcursor\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mstatement\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mparameters\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mcontext\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m   1245\u001b[0m                     )\n\u001b[1;32m   1246\u001b[0m         \u001b[0;32mexcept\u001b[0m \u001b[0mBaseException\u001b[0m \u001b[0;32mas\u001b[0m \u001b[0me\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/anaconda3/envs/dsci/lib/python3.6/site-packages/sqlalchemy/engine/default.py\u001b[0m in \u001b[0;36mdo_execute\u001b[0;34m(self, cursor, statement, parameters, context)\u001b[0m\n\u001b[1;32m    550\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    551\u001b[0m     \u001b[0;32mdef\u001b[0m \u001b[0mdo_execute\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mcursor\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mstatement\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mparameters\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mcontext\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0;32mNone\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 552\u001b[0;31m         \u001b[0mcursor\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mexecute\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mstatement\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mparameters\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    553\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    554\u001b[0m     \u001b[0;32mdef\u001b[0m \u001b[0mdo_execute_no_params\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mcursor\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mstatement\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mcontext\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0;32mNone\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32msrc/pymssql.pyx\u001b[0m in \u001b[0;36mpymssql.Cursor.execute\u001b[0;34m()\u001b[0m\n",
      "\u001b[0;31mProgrammingError\u001b[0m: (pymssql.ProgrammingError) (102, b\"Incorrect syntax near '='.DB-Lib error message 20018, severity 15:\\nGeneral SQL Server error: Check messages from the SQL Server\\n\")\n[SQL: SELECT TOP 20 missings \nFROM ge_community_replication_20190910_01 \nWHERE NOT (missings >= %(missings_1)s AND missings <= %(missings_2)s) AND (CASE WHEN (missings IS NULL) THEN %(param_1)s ELSE %(param_2)s END = 1 OR (missings IN (NULL)) = 0)]\n[parameters: {'missings_1': -4, 'missings_2': 4, 'param_1': 0, 'param_2': 1}]\n(Background on this error at: http://sqlalche.me/e/f405)"
     ]
    }
   ],
   "source": [
    "## Indeed this basic expectation fails because sqlalchemy doesn't translate our extremely convoluted logic correctly.\n",
    "## The extra complication was added in response to an issue on Redshift. See:\n",
    "#  https://github.com/great-expectations/great_expectations/issues/392\n",
    "gedf.expect_column_values_to_be_between(\"missings\", -4, 4)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[(3.0,),\n",
       " (3.0,),\n",
       " (4.0,),\n",
       " (4.0,),\n",
       " (4.0,),\n",
       " (3.0,),\n",
       " (4.0,),\n",
       " (4.0,),\n",
       " (3.0,),\n",
       " (3.0,),\n",
       " (3.0,),\n",
       " (3.0,),\n",
       " (3.0,),\n",
       " (4.0,),\n",
       " (3.0,),\n",
       " (3.0,),\n",
       " (3.0,),\n",
       " (4.0,),\n",
       " (3.0,),\n",
       " (3.0,)]"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Clearly, a cleaner version of this query would work\n",
    "engine.execute(\"SELECT TOP 20 missings FROM ge_community_replication_20190910_01 WHERE ((missings > 2) OR (missings < -4) AND missings IS NOT NULL)\").fetchall()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "ename": "ProgrammingError",
     "evalue": "(pymssql.ProgrammingError) (102, b\"Incorrect syntax near '='.DB-Lib error message 20018, severity 15:\\nGeneral SQL Server error: Check messages from the SQL Server\\n\")\n[SQL: SELECT TOP 20 missings \nFROM ge_community_replication_20190910_01 \nWHERE NOT (NOT (missings > %(missings_1)s OR missings < %(missings_2)s)) AND (CASE WHEN (missings IS NULL) THEN %(param_1)s ELSE %(param_2)s END = 1 OR (missings IN (NULL)) = 0)]\n[parameters: {'missings_1': 4, 'missings_2': -4, 'param_1': 0, 'param_2': 1}]\n(Background on this error at: http://sqlalche.me/e/f405)",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mMSSQLDatabaseException\u001b[0m                    Traceback (most recent call last)",
      "\u001b[0;32msrc/pymssql.pyx\u001b[0m in \u001b[0;36mpymssql.Cursor.execute\u001b[0;34m()\u001b[0m\n",
      "\u001b[0;32msrc/_mssql.pyx\u001b[0m in \u001b[0;36m_mssql.MSSQLConnection.execute_query\u001b[0;34m()\u001b[0m\n",
      "\u001b[0;32msrc/_mssql.pyx\u001b[0m in \u001b[0;36m_mssql.MSSQLConnection.execute_query\u001b[0;34m()\u001b[0m\n",
      "\u001b[0;32msrc/_mssql.pyx\u001b[0m in \u001b[0;36m_mssql.MSSQLConnection.format_and_run_query\u001b[0;34m()\u001b[0m\n",
      "\u001b[0;32msrc/_mssql.pyx\u001b[0m in \u001b[0;36m_mssql.check_cancel_and_raise\u001b[0;34m()\u001b[0m\n",
      "\u001b[0;32msrc/_mssql.pyx\u001b[0m in \u001b[0;36m_mssql.maybe_raise_MSSQLDatabaseException\u001b[0;34m()\u001b[0m\n",
      "\u001b[0;31mMSSQLDatabaseException\u001b[0m: (102, b\"Incorrect syntax near '='.DB-Lib error message 20018, severity 15:\\nGeneral SQL Server error: Check messages from the SQL Server\\n\")",
      "\nDuring handling of the above exception, another exception occurred:\n",
      "\u001b[0;31mProgrammingError\u001b[0m                          Traceback (most recent call last)",
      "\u001b[0;32m~/anaconda3/envs/dsci/lib/python3.6/site-packages/sqlalchemy/engine/base.py\u001b[0m in \u001b[0;36m_execute_context\u001b[0;34m(self, dialect, constructor, statement, parameters, *args)\u001b[0m\n\u001b[1;32m   1243\u001b[0m                     self.dialect.do_execute(\n\u001b[0;32m-> 1244\u001b[0;31m                         \u001b[0mcursor\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mstatement\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mparameters\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mcontext\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m   1245\u001b[0m                     )\n",
      "\u001b[0;32m~/anaconda3/envs/dsci/lib/python3.6/site-packages/sqlalchemy/engine/default.py\u001b[0m in \u001b[0;36mdo_execute\u001b[0;34m(self, cursor, statement, parameters, context)\u001b[0m\n\u001b[1;32m    551\u001b[0m     \u001b[0;32mdef\u001b[0m \u001b[0mdo_execute\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mcursor\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mstatement\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mparameters\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mcontext\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0;32mNone\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 552\u001b[0;31m         \u001b[0mcursor\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mexecute\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mstatement\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mparameters\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    553\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32msrc/pymssql.pyx\u001b[0m in \u001b[0;36mpymssql.Cursor.execute\u001b[0;34m()\u001b[0m\n",
      "\u001b[0;31mProgrammingError\u001b[0m: (102, b\"Incorrect syntax near '='.DB-Lib error message 20018, severity 15:\\nGeneral SQL Server error: Check messages from the SQL Server\\n\")",
      "\nThe above exception was the direct cause of the following exception:\n",
      "\u001b[0;31mProgrammingError\u001b[0m                          Traceback (most recent call last)",
      "\u001b[0;32m<ipython-input-15-6010ef7ef33a>\u001b[0m in \u001b[0;36m<module>\u001b[0;34m\u001b[0m\n\u001b[1;32m     19\u001b[0m                     \u001b[0;31m# Ignore any other values that are in the ignore list\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m     20\u001b[0m                     sa.column(column).in_(ignore_values) == False))\n\u001b[0;32m---> 21\u001b[0;31m     ).limit(unexpected_count_limit)\n\u001b[0m\u001b[1;32m     22\u001b[0m ).fetchall()\n",
      "\u001b[0;32m~/anaconda3/envs/dsci/lib/python3.6/site-packages/sqlalchemy/engine/base.py\u001b[0m in \u001b[0;36mexecute\u001b[0;34m(self, statement, *multiparams, **params)\u001b[0m\n\u001b[1;32m   2167\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   2168\u001b[0m         \u001b[0mconnection\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_contextual_connect\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mclose_with_result\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0;32mTrue\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 2169\u001b[0;31m         \u001b[0;32mreturn\u001b[0m \u001b[0mconnection\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mexecute\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mstatement\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m*\u001b[0m\u001b[0mmultiparams\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m**\u001b[0m\u001b[0mparams\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m   2170\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   2171\u001b[0m     \u001b[0;32mdef\u001b[0m \u001b[0mscalar\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mstatement\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m*\u001b[0m\u001b[0mmultiparams\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m**\u001b[0m\u001b[0mparams\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/anaconda3/envs/dsci/lib/python3.6/site-packages/sqlalchemy/engine/base.py\u001b[0m in \u001b[0;36mexecute\u001b[0;34m(self, object_, *multiparams, **params)\u001b[0m\n\u001b[1;32m    986\u001b[0m             \u001b[0;32mraise\u001b[0m \u001b[0mexc\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mObjectNotExecutableError\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mobject_\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    987\u001b[0m         \u001b[0;32melse\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 988\u001b[0;31m             \u001b[0;32mreturn\u001b[0m \u001b[0mmeth\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mmultiparams\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mparams\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    989\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    990\u001b[0m     \u001b[0;32mdef\u001b[0m \u001b[0m_execute_function\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mfunc\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mmultiparams\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mparams\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/anaconda3/envs/dsci/lib/python3.6/site-packages/sqlalchemy/sql/elements.py\u001b[0m in \u001b[0;36m_execute_on_connection\u001b[0;34m(self, connection, multiparams, params)\u001b[0m\n\u001b[1;32m    285\u001b[0m     \u001b[0;32mdef\u001b[0m \u001b[0m_execute_on_connection\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mconnection\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mmultiparams\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mparams\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    286\u001b[0m         \u001b[0;32mif\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0msupports_execution\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 287\u001b[0;31m             \u001b[0;32mreturn\u001b[0m \u001b[0mconnection\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_execute_clauseelement\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mmultiparams\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mparams\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    288\u001b[0m         \u001b[0;32melse\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    289\u001b[0m             \u001b[0;32mraise\u001b[0m \u001b[0mexc\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mObjectNotExecutableError\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/anaconda3/envs/dsci/lib/python3.6/site-packages/sqlalchemy/engine/base.py\u001b[0m in \u001b[0;36m_execute_clauseelement\u001b[0;34m(self, elem, multiparams, params)\u001b[0m\n\u001b[1;32m   1105\u001b[0m             \u001b[0mdistilled_params\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1106\u001b[0m             \u001b[0mcompiled_sql\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 1107\u001b[0;31m             \u001b[0mdistilled_params\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m   1108\u001b[0m         )\n\u001b[1;32m   1109\u001b[0m         \u001b[0;32mif\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_has_events\u001b[0m \u001b[0;32mor\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mengine\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_has_events\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/anaconda3/envs/dsci/lib/python3.6/site-packages/sqlalchemy/engine/base.py\u001b[0m in \u001b[0;36m_execute_context\u001b[0;34m(self, dialect, constructor, statement, parameters, *args)\u001b[0m\n\u001b[1;32m   1246\u001b[0m         \u001b[0;32mexcept\u001b[0m \u001b[0mBaseException\u001b[0m \u001b[0;32mas\u001b[0m \u001b[0me\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1247\u001b[0m             self._handle_dbapi_exception(\n\u001b[0;32m-> 1248\u001b[0;31m                 \u001b[0me\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mstatement\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mparameters\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mcursor\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mcontext\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m   1249\u001b[0m             )\n\u001b[1;32m   1250\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/anaconda3/envs/dsci/lib/python3.6/site-packages/sqlalchemy/engine/base.py\u001b[0m in \u001b[0;36m_handle_dbapi_exception\u001b[0;34m(self, e, statement, parameters, cursor, context)\u001b[0m\n\u001b[1;32m   1464\u001b[0m                 \u001b[0mutil\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mraise_from_cause\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mnewraise\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mexc_info\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1465\u001b[0m             \u001b[0;32melif\u001b[0m \u001b[0mshould_wrap\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 1466\u001b[0;31m                 \u001b[0mutil\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mraise_from_cause\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0msqlalchemy_exception\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mexc_info\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m   1467\u001b[0m             \u001b[0;32melse\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1468\u001b[0m                 \u001b[0mutil\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mreraise\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m*\u001b[0m\u001b[0mexc_info\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/anaconda3/envs/dsci/lib/python3.6/site-packages/sqlalchemy/util/compat.py\u001b[0m in \u001b[0;36mraise_from_cause\u001b[0;34m(exception, exc_info)\u001b[0m\n\u001b[1;32m    396\u001b[0m     \u001b[0mexc_type\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mexc_value\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mexc_tb\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mexc_info\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    397\u001b[0m     \u001b[0mcause\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mexc_value\u001b[0m \u001b[0;32mif\u001b[0m \u001b[0mexc_value\u001b[0m \u001b[0;32mis\u001b[0m \u001b[0;32mnot\u001b[0m \u001b[0mexception\u001b[0m \u001b[0;32melse\u001b[0m \u001b[0;32mNone\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 398\u001b[0;31m     \u001b[0mreraise\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mtype\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mexception\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mexception\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mtb\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mexc_tb\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mcause\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mcause\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    399\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    400\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/anaconda3/envs/dsci/lib/python3.6/site-packages/sqlalchemy/util/compat.py\u001b[0m in \u001b[0;36mreraise\u001b[0;34m(tp, value, tb, cause)\u001b[0m\n\u001b[1;32m    150\u001b[0m             \u001b[0mvalue\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m__cause__\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mcause\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    151\u001b[0m         \u001b[0;32mif\u001b[0m \u001b[0mvalue\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m__traceback__\u001b[0m \u001b[0;32mis\u001b[0m \u001b[0;32mnot\u001b[0m \u001b[0mtb\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 152\u001b[0;31m             \u001b[0;32mraise\u001b[0m \u001b[0mvalue\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mwith_traceback\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mtb\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    153\u001b[0m         \u001b[0;32mraise\u001b[0m \u001b[0mvalue\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    154\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/anaconda3/envs/dsci/lib/python3.6/site-packages/sqlalchemy/engine/base.py\u001b[0m in \u001b[0;36m_execute_context\u001b[0;34m(self, dialect, constructor, statement, parameters, *args)\u001b[0m\n\u001b[1;32m   1242\u001b[0m                 \u001b[0;32mif\u001b[0m \u001b[0;32mnot\u001b[0m \u001b[0mevt_handled\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1243\u001b[0m                     self.dialect.do_execute(\n\u001b[0;32m-> 1244\u001b[0;31m                         \u001b[0mcursor\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mstatement\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mparameters\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mcontext\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m   1245\u001b[0m                     )\n\u001b[1;32m   1246\u001b[0m         \u001b[0;32mexcept\u001b[0m \u001b[0mBaseException\u001b[0m \u001b[0;32mas\u001b[0m \u001b[0me\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/anaconda3/envs/dsci/lib/python3.6/site-packages/sqlalchemy/engine/default.py\u001b[0m in \u001b[0;36mdo_execute\u001b[0;34m(self, cursor, statement, parameters, context)\u001b[0m\n\u001b[1;32m    550\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    551\u001b[0m     \u001b[0;32mdef\u001b[0m \u001b[0mdo_execute\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mcursor\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mstatement\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mparameters\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mcontext\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0;32mNone\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 552\u001b[0;31m         \u001b[0mcursor\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mexecute\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mstatement\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mparameters\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    553\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    554\u001b[0m     \u001b[0;32mdef\u001b[0m \u001b[0mdo_execute_no_params\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mcursor\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mstatement\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mcontext\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0;32mNone\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32msrc/pymssql.pyx\u001b[0m in \u001b[0;36mpymssql.Cursor.execute\u001b[0;34m()\u001b[0m\n",
      "\u001b[0;31mProgrammingError\u001b[0m: (pymssql.ProgrammingError) (102, b\"Incorrect syntax near '='.DB-Lib error message 20018, severity 15:\\nGeneral SQL Server error: Check messages from the SQL Server\\n\")\n[SQL: SELECT TOP 20 missings \nFROM ge_community_replication_20190910_01 \nWHERE NOT (NOT (missings > %(missings_1)s OR missings < %(missings_2)s)) AND (CASE WHEN (missings IS NULL) THEN %(param_1)s ELSE %(param_2)s END = 1 OR (missings IN (NULL)) = 0)]\n[parameters: {'missings_1': 4, 'missings_2': -4, 'param_1': 0, 'param_2': 1}]\n(Background on this error at: http://sqlalche.me/e/f405)"
     ]
    }
   ],
   "source": [
    "## Grabbing the code we can isolate the confusion...\n",
    "column = \"missings\"\n",
    "table = \"ge_community_replication_20190910_01\"\n",
    "ignore_values = [None]\n",
    "unexpected_count_limit = 20\n",
    "expected_condition = sa.not_(sa.or_(sa.column(column) > 4, sa.column(column) < -4))\n",
    "unexpected_query_results = engine.execute(\n",
    "    sa.select([sa.column(column)]).select_from(sa.table(table)).where(\n",
    "        sa.and_(sa.not_(expected_condition),\n",
    "                sa.or_(\n",
    "                    # SA normally evaluates `== None` as `IS NONE`. However `sa.in_()`\n",
    "                    # replaces `None` as `NULL` in the list and incorrectly uses `== NULL`\n",
    "                    sa.case([\n",
    "                        (\n",
    "                            sa.column(column).is_(None),\n",
    "                            False\n",
    "                        )\n",
    "                    ], else_=True) if None in ignore_values else False,\n",
    "                    # Ignore any other values that are in the ignore list\n",
    "                    sa.column(column).in_(ignore_values) == False))\n",
    "    ).limit(unexpected_count_limit)\n",
    ").fetchall()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[(3.0,), (3.0,), (4.0,), (4.0,), (4.0,), (3.0,), (4.0,), (4.0,), (3.0,), (3.0,), (3.0,), (3.0,), (3.0,), (4.0,), (3.0,), (3.0,), (3.0,), (4.0,), (3.0,), (3.0,)]\n"
     ]
    }
   ],
   "source": [
    "## ...by progressively simplifying\n",
    "\n",
    "column = \"missings\"\n",
    "table = \"ge_community_replication_20190910_01\"\n",
    "ignore_values = [None]\n",
    "unexpected_count_limit = 20\n",
    "expected_condition = sa.not_(sa.or_(sa.column(column) > 2, sa.column(column) < -4))\n",
    "unexpected_query_results = engine.execute(\n",
    "    sa.select([sa.column(column)]).select_from(sa.table(table)).where(\n",
    "        sa.and_(\n",
    "            sa.not_(expected_condition),\n",
    "            sa.not_(sa.column(column) is None)\n",
    "        )\n",
    "    ).limit(unexpected_count_limit)\n",
    ").fetchall()\n",
    "print(unexpected_query_results)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'SELECT missings \\nFROM ge_community_replication_20190910_01 \\nWHERE NOT (NOT (missings > :missings_1 OR missings < :missings_2)) AND missings NOT IN (NULL)\\n LIMIT :param_1'"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "str(sa.select([sa.column(column)]).select_from(sa.table(table)).where(\n",
    "        sa.and_(\n",
    "            sa.not_(expected_condition),\n",
    "            sa.not_(sa.column(column).in_([None]))\n",
    "        )\n",
    "    ).limit(unexpected_count_limit)\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[]\n"
     ]
    }
   ],
   "source": [
    "## Which lets us find specificially what the problem is...\n",
    "column = \"missings\"\n",
    "table = \"ge_community_replication_20190910_01\"\n",
    "ignore_values = [None]\n",
    "unexpected_count_limit = 20\n",
    "expected_condition = sa.not_(sa.or_(sa.column(column) > 2, sa.column(column) < -4))\n",
    "unexpected_query_results = engine.execute(\n",
    "    sa.select([sa.column(column)]).select_from(sa.table(table)).where(\n",
    "        sa.and_(\n",
    "            sa.not_(expected_condition),\n",
    "            sa.not_(sa.column(column).in_([None]))\n",
    "        )\n",
    "    ).limit(unexpected_count_limit)\n",
    ").fetchall()\n",
    "print(unexpected_query_results)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[(4.0,), (4.0,), (4.0,), (4.0,), (4.0,), (4.0,), (4.0,), (4.0,), (4.0,), (4.0,), (4.0,), (4.0,), (4.0,), (4.0,), (4.0,)]\n"
     ]
    }
   ],
   "source": [
    "## Which lets us find specificially what the problem is...\n",
    "column = \"missings\"\n",
    "table = \"ge_community_replication_20190910_01\"\n",
    "ignore_values = [3]\n",
    "unexpected_count_limit = 20\n",
    "expected_condition = sa.not_(sa.or_(sa.column(column) > 2, sa.column(column) < -4))\n",
    "ignore_conditions = [\n",
    "    sa.column(column).isnot(None),\n",
    "    sa.not_(sa.column(column).in_(ignore_values))\n",
    "]\n",
    "\n",
    "unexpected_query_results = engine.execute(\n",
    "    sa.select([sa.column(column)]).select_from(sa.table(table)).where(\n",
    "        sa.and_(\n",
    "            sa.not_(expected_condition),\n",
    "            *ignore_conditions\n",
    "        )\n",
    "    ).limit(unexpected_count_limit)\n",
    ").fetchall()\n",
    "print(unexpected_query_results)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[(3.0,), (3.0,), (4.0,), (4.0,), (4.0,), (3.0,), (4.0,), (4.0,), (3.0,), (3.0,), (3.0,), (3.0,), (3.0,), (4.0,), (3.0,), (3.0,), (3.0,), (4.0,), (3.0,), (3.0,)]\n"
     ]
    }
   ],
   "source": [
    "## Which lets us find specificially what the problem is...\n",
    "column = \"missings\"\n",
    "table = \"ge_community_replication_20190910_01\"\n",
    "ignore_values = [None]\n",
    "unexpected_count_limit = 20\n",
    "expected_condition = sa.not_(sa.or_(sa.column(column) > 2, sa.column(column) < -4))\n",
    "unexpected_query_results = engine.execute(\n",
    "    sa.select([sa.column(column)]).select_from(sa.table(table)).where(\n",
    "        sa.and_(\n",
    "            sa.not_(expected_condition),\n",
    "            sa.not_(sa.column(column).is_(None))\n",
    "        )\n",
    "    ).limit(unexpected_count_limit)\n",
    ").fetchall()\n",
    "print(unexpected_query_results)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[(3.0,), (3.0,), (4.0,), (4.0,), (4.0,), (3.0,), (4.0,), (4.0,), (3.0,), (3.0,), (3.0,), (3.0,), (3.0,), (4.0,), (3.0,), (3.0,), (3.0,), (4.0,), (3.0,), (3.0,)]\n"
     ]
    }
   ],
   "source": [
    "## ... but the new simpler logic compiles clearly for postgres, redshift, and mssql\n",
    "\n",
    "ignore_values = [None]\n",
    "\n",
    "ignore_values_conditions = []\n",
    "if len(ignore_values) > 0 and None not in ignore_values or len(ignore_values) > 1 and None in ignore_values:\n",
    "    ignore_values_conditions += [\n",
    "        sa.column(column).in_([val for val in ignore_values if val is not None])\n",
    "    ]\n",
    "if None in ignore_values:\n",
    "    ignore_values_conditions += [sa.column(column).is_(None)]\n",
    "\n",
    "if len(ignore_values_conditions) > 1:\n",
    "    ignore_values_condition = sa.or_(*ignore_values_conditions)\n",
    "elif len(ignore_values_conditions) == 1:\n",
    "    ignore_values_condition = ignore_values_conditions[0]\n",
    "else:\n",
    "    ignore_values_condition = False\n",
    "    \n",
    "expected_condition = sa.not_(sa.or_(sa.column(column) > 2, sa.column(column) < -4))\n",
    "    \n",
    "unexpected_query_results = engine.execute(\n",
    "    sa.select([sa.column(column)]).select_from(sa.table(table)).where(\n",
    "        sa.and_(sa.not_(expected_condition),\n",
    "                sa.not_(ignore_values_condition)\n",
    "                )\n",
    "    ).limit(unexpected_count_limit)\n",
    ").fetchall()\n",
    "print(unexpected_query_results)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "engine = sa.create_engine(\"postgresql://test:test@localhost:5432/postgres\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.to_sql(\"ge_community_replication_20190910_01\", engine, if_exists=\"replace\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[(3.0,), (3.0,), (4.0,), (4.0,), (4.0,), (3.0,), (4.0,), (4.0,), (3.0,), (3.0,), (3.0,), (3.0,), (3.0,), (4.0,), (3.0,), (3.0,), (3.0,), (4.0,), (3.0,), (3.0,)]\n"
     ]
    }
   ],
   "source": [
    "## ... but the new simpler logic compiles clearly for postgres, redshift, and mssql\n",
    "\n",
    "ignore_values = [None]\n",
    "\n",
    "ignore_values_conditions = []\n",
    "if len(ignore_values) > 0 and None not in ignore_values or len(ignore_values) > 1 and None in ignore_values:\n",
    "    ignore_values_conditions += [\n",
    "        sa.column(column).in_([val for val in ignore_values if val is not None])\n",
    "    ]\n",
    "if None in ignore_values:\n",
    "    ignore_values_conditions += [sa.column(column).is_(None)]\n",
    "\n",
    "if len(ignore_values_conditions) > 1:\n",
    "    ignore_values_condition = sa.or_(*ignore_values_conditions)\n",
    "elif len(ignore_values_conditions) == 1:\n",
    "    ignore_values_condition = ignore_values_conditions[0]\n",
    "else:\n",
    "    ignore_values_condition = sa.literal(False)\n",
    "    \n",
    "expected_condition = sa.not_(sa.or_(sa.column(column) > 2, sa.column(column) < -4))\n",
    "\n",
    "unexpected_query_results = engine.execute(\n",
    "    sa.select([sa.column(column)]).select_from(sa.table(table)).where(\n",
    "        sa.and_(sa.not_(expected_condition),\n",
    "                sa.not_(ignore_values_condition)\n",
    "                )\n",
    "    ).limit(unexpected_count_limit)\n",
    ").fetchall()\n",
    "print(unexpected_query_results)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[(None,), (None,), (None,), (None,), (None,), (None,), (None,), (None,), (None,), (None,), (None,), (None,), (None,), (None,), (None,), (None,), (None,), (None,)]\n"
     ]
    }
   ],
   "source": [
    "print(\n",
    "    engine.execute(\n",
    "        sa.select([\n",
    "            sa.column(column)\n",
    "        ]).select_from(sa.table(table)).where(ignore_values_condition)).fetchall())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [],
   "source": [
    "# ... and now check with redshift, since issue https://github.com/great-expectations/great_expectations/issues/392 \n",
    "# had raised an issue in the past that was causing this situation in the first place"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [],
   "source": [
    "engine = sa.create_engine(\"redshift+psycopg2://USER:PASS@HOST.redshift.amazonaws.com:5439/DB\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.to_sql(\"ge_community_replication_20190910_01\", engine, if_exists=\"replace\", index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[(3.0,), (3.0,), (4.0,), (4.0,), (4.0,), (3.0,), (4.0,), (4.0,), (3.0,), (3.0,), (3.0,), (3.0,), (3.0,), (4.0,), (3.0,), (3.0,), (3.0,), (4.0,), (3.0,), (3.0,)]\n"
     ]
    }
   ],
   "source": [
    "column = \"missings\"\n",
    "table = \"ge_community_replication_20190910_01\"\n",
    "unexpected_count_limit = 20\n",
    "\n",
    "ignore_values = [None]\n",
    "\n",
    "ignore_values_conditions = []\n",
    "if len(ignore_values) > 0 and None not in ignore_values or len(ignore_values) > 1 and None in ignore_values:\n",
    "    ignore_values_conditions += [\n",
    "        sa.column(column).in_([val for val in ignore_values if val is not None])\n",
    "    ]\n",
    "if None in ignore_values:\n",
    "    ignore_values_conditions += [sa.column(column).is_(None)]\n",
    "\n",
    "if len(ignore_values_conditions) > 1:\n",
    "    ignore_values_condition = sa.or_(*ignore_values_conditions)\n",
    "elif len(ignore_values_conditions) == 1:\n",
    "    ignore_values_condition = ignore_values_conditions[0]\n",
    "else:\n",
    "    ignore_values_condition = sa.literal(False)\n",
    "    \n",
    "expected_condition = sa.not_(sa.or_(sa.column(column) > 2, sa.column(column) < -4))\n",
    "\n",
    "unexpected_query_results = engine.execute(\n",
    "    sa.select([sa.column(column)]).select_from(sa.table(table)).where(\n",
    "        sa.and_(sa.not_(expected_condition),\n",
    "                sa.not_(ignore_values_condition)\n",
    "                )\n",
    "    ).limit(unexpected_count_limit)\n",
    ").fetchall()\n",
    "print(unexpected_query_results)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [],
   "source": [
    "# .. finally, because of the original issue, we need to ensure we're working correctly with set\n",
    "gedf = ge.dataset.SqlAlchemyDataset(\"ge_community_replication_20190910_01\", engine)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'success': False,\n",
       " 'result': {'element_count': 100,\n",
       "  'missing_count': 18,\n",
       "  'missing_percent': 0.18,\n",
       "  'unexpected_count': 38,\n",
       "  'unexpected_percent': 0.38,\n",
       "  'unexpected_percent_nonmissing': 0.4634146341463415,\n",
       "  'partial_unexpected_list': [3.0,\n",
       "   3.0,\n",
       "   4.0,\n",
       "   4.0,\n",
       "   4.0,\n",
       "   3.0,\n",
       "   4.0,\n",
       "   4.0,\n",
       "   3.0,\n",
       "   3.0,\n",
       "   3.0,\n",
       "   3.0,\n",
       "   3.0,\n",
       "   4.0,\n",
       "   3.0,\n",
       "   3.0,\n",
       "   3.0,\n",
       "   4.0,\n",
       "   3.0,\n",
       "   3.0]}}"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "gedf.expect_column_values_to_be_in_set(\"missings\", [0, 1, 2])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.8"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}