Django odd behaviour with Q objects and `and_` (vs `and`)

Question

I have been trying to dynamically construct list of filters based on some function arguments. I build a list of predicates using Q objects and finally I construct one predicate with:

filters = []
... some code appending Q objects to filters ...
combined_filter = reduce(and_, filters)

Then I query my database objects with:

MyModel.objects.filter(combined_filter)

I have noticed a bit odd behaviour when I try to combine Q objects with operator.and_.

For example comparing output of:

items = Item.objects.filter(and_(is_metal, ~is_wood))
print([i.name for i in items])

items = Item.objects.filter(is_metal and ~is_wood)
print([i.name for i in items])

items = Item.objects.filter(is_metal, ~is_wood)
print([i.name for i in items])

I get:

['Table', 'Container']
['Container']
['Table', 'Container']

What is the reason for different behaviour between and_ and and?

My expected output is to get just ['Container'] (see below for full example, "Container" is the only thing with only "metal" as material, "Table" should be excluded because it also has "wood").

Followup question would be: how do I get behaviour of and when using reduce?

My django version is 2.0.7 I have reproduced that exact problem on https://repl.it/repls/AgonizingBossyEfficiency

In case above link dies all code I've modified is below:

models.py:

from django.db import models

class Material(models.Model):
  name = models.CharField(max_length=50)

class Item(models.Model):
  name = models.CharField(max_length=50)
  materials = models.ManyToManyField(Material)

views.py:

from django.shortcuts import render
from django.db import transaction
from django.db.models import Q
from operator import and_
from .models import Material, Item

# Create your views here.
def home(request):
    with transaction.atomic():
      metal = Material(name="metal")
      metal.save()
      wood = Material(name="wood")
      wood.save()

      table = Item(name="Table")
      table.save()
      table.materials.add(metal, wood)
      table.save()

      chair = Item(name="Chair")
      chair.save()
      chair.materials.add(wood)
      chair.save()

      container = Item(name="Container")
      container.save()
      container.materials.add(metal)
      container.save()

      is_metal = Q(materials__name__in = ('metal',))
      is_wood = Q(materials__name__in = ('wood',))

      items = Item.objects.filter(and_(is_metal, ~is_wood))
      print([i.name for i in items])

      items = Item.objects.filter(is_metal and ~is_wood)
      print([i.name for i in items])

      items = Item.objects.filter(is_metal, ~is_wood)
      print([i.name for i in items])

      raise Exception('nope')
    return render(request, 'main/index.html')

Answer 1

As pointed out by Ilja my question is effectively a duplicate.

My main problem was thinking that operator.and_ applies logical and. However that is not the case, operator.and_ is a bitwise operator.

If I compare behaviour of operator.and_(is_metal, is_wood) to is_metal & is_wood (as opposed to is_metal && is_wood == is_metal and is_wood) I do get the same results.

So my problem was that I was using bitwise and where I wanted logical and.