reproduction of apparent bug with .label() and not_() in SQLAlchemy

reproduction.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "from sqlalchemy import create_engine\n",
    "from sqlalchemy.schema import MetaData, Table, Index, Column\n",
    "from sqlalchemy.sql import and_, or_, not_, select, text\n",
    "from sqlalchemy.types import Text, Integer, Float, String\n",
    "\n",
    "engine = create_engine('sqlite:///:memory:', echo=False)\n",
    "\n",
    "metadata = MetaData()\n",
    "\n",
    "user = Table('user', metadata,\n",
    "    Column('user_id', Integer, primary_key=True),\n",
    "    Column('user_name', String(16), nullable=False),\n",
    "    Column('email_address', String(60)),\n",
    "    Column('password', String(20), nullable=False)\n",
    ")\n",
    "user.create(engine)\n",
    "\n",
    "with engine.connect() as conn:\n",
    "    for name in ('jack', 'james', 'jenny'):\n",
    "        email = '{name}@gmail.com'.format(name=name)\n",
    "        ins = user.insert().values(user_name=name, email_address=email, password=\"bad\")\n",
    "        result = conn.execute(ins) "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Full results: [(1, 'jack', 'jack@gmail.com', 'bad'), (2, 'james', 'james@gmail.com', 'bad'), (3, 'jenny', 'jenny@gmail.com', 'bad')]\n"
     ]
    }
   ],
   "source": [
    "with engine.connect() as conn:\n",
    "    query = select([user])\n",
    "    found = conn.execute(query).fetchall()\n",
    "\n",
    "print(\"Full results: {results}\".format(results=found))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "SQL: SELECT \"user\".user_id, \"user\".user_name, \"user\".email_address, \"user\".password \n",
      "FROM \"user\" \n",
      "WHERE \"user\".user_name = :user_name_1 OR \"user\".user_name = :user_name_2\n",
      "\n",
      "Returns: [(1, 'jack', 'jack@gmail.com', 'bad'), (2, 'james', 'james@gmail.com', 'bad')].\n",
      "That makes sense.\n",
      "\n"
     ]
    }
   ],
   "source": [
    "query_with_disjunction = query.where(or_(user.c.user_name == \"jack\", user.c.user_name == \"james\"))\n",
    "\n",
    "print(\"SQL: {}\".format(str(query_with_disjunction)))\n",
    "\n",
    "with engine.connect() as conn:\n",
    "    found = conn.execute(query_with_disjunction).fetchall()\n",
    "    \n",
    "print(\"\\nReturns: {results}.\\nThat makes sense.\\n\".format(results=found))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "SQL: SELECT \"user\".user_id, \"user\".user_name, \"user\".email_address, \"user\".password \n",
      "FROM \"user\" \n",
      "WHERE NOT (\"user\".user_name = :user_name_1 OR \"user\".user_name = :user_name_2)\n",
      "\n",
      "Returns: [(3, 'jenny', 'jenny@gmail.com', 'bad')]\n",
      "That makes sense.\n",
      "\n"
     ]
    }
   ],
   "source": [
    "disjunction = or_(user.c.user_name == \"jack\", user.c.user_name == \"james\")\n",
    "query_with_negated_disjunction = query.where(not_(disjunction))\n",
    "\n",
    "print(\"SQL: {}\".format(str(query_with_negated_disjunction)))\n",
    "\n",
    "with engine.connect() as conn:\n",
    "    found = conn.execute(query_with_negated_disjunction).fetchall()\n",
    "    \n",
    "print(\"\\nReturns: {results}\\nThat makes sense.\\n\".format(results=found))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Produces the SQL: SELECT \"user\".user_id, \"user\".user_name, \"user\".email_address, \"user\".password \n",
      "FROM \"user\" \n",
      "WHERE NOT \"user\".user_name = :user_name_1 OR \"user\".user_name = :user_name_2\n",
      ".Notice that the 'not' does NOT put parentheses around the disjunction\n",
      "\n",
      "Returns: [(1, 'jack', 'jack@gmail.com', 'bad'), (3, 'jenny', 'jenny@gmail.com', 'bad')]\n",
      "\n",
      "That doesn't make sense for 2 reasons: 1) we'd expect the same result as the unlabelled disjunction, i.e. just jenny and 2) even if it's correct to apply the 'not' only to the first clause, the result should be james and jenny, not jack and jenny.\n"
     ]
    }
   ],
   "source": [
    "disjunction_with_label = disjunction.label(\"labelled_disjunction\")\n",
    "\n",
    "query_with_negated_disjunction_with_label = query.where(not_(disjunction_with_label))\n",
    "\n",
    "print(\"Produces the SQL: {}\\n.Notice that the 'not' does NOT put parentheses around the disjunction\".format(str(query_with_negated_disjunction_with_label)))\n",
    "\n",
    "with engine.connect() as conn:\n",
    "    found = conn.execute(query_with_negated_disjunction_with_label).fetchall()\n",
    "    \n",
    "print(\"\\nReturns: {results}\\n\\nThat doesn't make sense for 2 reasons: 1) we'd expect the same result as the unlabelled disjunction, i.e. just jenny and 2) even if it's correct to apply the 'not' only to the first clause, the result should be james and jenny, not jack and jenny.\".format(results=found))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "If we pre-bind the variables and run directly, we get james and jenny:\n"
     ]
    }
   ],
   "source": [
    "literal_query = str(query_with_negated_disjunction_with_label.compile(compile_kwargs={\"literal_binds\": True}))\n",
    "\n",
    "print(\"If we pre-bind the variables and run directly, we get james and jenny:\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "\"[(2, 'james', 'james@gmail.com', 'bad'), (3, 'jenny', 'jenny@gmail.com', 'bad')]\""
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "with engine.connect() as conn:\n",
    "    res = conn.execute(literal_query).fetchall()\n",
    "str(res)"
   ]
  }
 ],
 "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.5.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}

reproduction.py

# coding: utf-8

from sqlalchemy import create_engine
from sqlalchemy.schema import MetaData, Table, Index, Column
from sqlalchemy.sql import and_, or_, not_, select, text
from sqlalchemy.types import Text, Integer, Float, String

engine = create_engine('sqlite:///:memory:', echo=False)
metadata = MetaData()

user = Table('user', metadata,
    Column('user_id', Integer, primary_key=True),
    Column('user_name', String(16), nullable=False),
    Column('email_address', String(60)),
    Column('password', String(20), nullable=False)
)
user.create(engine)

with engine.connect() as conn:
    for name in ('jack', 'james', 'jenny'):
        email = '{name}@gmail.com'.format(name=name)
        ins = user.insert().values(user_name=name, email_address=email, password="bad_pass")
        result = conn.execute(ins)

with engine.connect() as conn:
    query = select([user])
    found = conn.execute(query).fetchall()
    print("Full results: {results}".format(results=found))

    query_with_disjunction = query.where(or_(user.c.user_name == "jack", user.c.user_name == "james"))
    print("SQL: {}".format(str(query_with_disjunction)))
    found = conn.execute(query_with_disjunction).fetchall()
    print("\nReturns: {results}.\nThat makes sense.\n".format(results=found))

    disjunction = or_(user.c.user_name == "jack", user.c.user_name == "james")
    query_with_negated_disjunction = query.where(not_(disjunction))
    print("SQL: {}".format(str(query_with_negated_disjunction)))
    found = conn.execute(query_with_negated_disjunction).fetchall()
    print("\nReturns: {results}\nThat makes sense.\n".format(results=found))

    disjunction_with_label = disjunction.label("labelled_disjunction")
    query_with_negated_disjunction_with_label = query.where(not_(disjunction_with_label))
    print(("Produces the SQL: {}\n\nNotice that the 'not' does "
           "NOT put parentheses around the disjunction".format(str(query_with_negated_disjunction_with_label))))
    found = conn.execute(query_with_negated_disjunction_with_label).fetchall()
    print(("\nReturns: {results}\n\nThat doesn't make sense for 2 reasons: "
           "1) we'd expect the same result as the unlabelled disjunction, i.e. just jenny and "
           "2) even if it's correct to apply the 'not' only to the first clause, "
           "the result should be james and jenny, not jack and jenny.\n".format(results=found)))

    literal_query = str(query_with_negated_disjunction_with_label.compile(
        compile_kwargs={"literal_binds": True}))
    print("If we pre-bind the variables and run directly, we get james and jenny:")
    res = conn.execute(literal_query).fetchall()
    print(str(res))