Using aliased table makes literal bindings fail for CTE statement

I've had no problems thus far compiling complex insert statements with literal bind parameters, but the example below fails, binding some parameters into the statement and not binding others when an alias of a table is used. I've put a big fat comment saying LOOK HERE at the point in the code where you can comment in and out a line to see the behavior kicking in.

from sqlalchemy import Table, Column, String, Integer, Float, MetaData, select, cast, literal, and_

_metadata = MetaData()

string_box = Table('string_box', _metadata,
  Column('name', String, primary_key = True),
  Column('str', Integer, primary_key = True),
  Column('x', Integer),
  Column('y', Integer),
  Column('width', Integer),
  Column('height', Integer)
  )

font_name = Table('font_name', _metadata,
  Column('id', Integer),
  Column('val', String))

name = Table('name', _metadata,
  Column('id', Integer),
  Column('val', String))

font_size = Table('font_size', _metadata,
  Column('id', Integer),
  Column('val', Float))

time_signature = Table('time_signature', _metadata,
  Column('id', Integer),
  Column('num', Integer),
  Column('den', Integer))

height = Table('height', _metadata,
  Column('id', Integer),
  Column('val', Float))

width = Table('width', _metadata,
  Column('id', Integer),
  Column('val', Float))

stencil = Table('stencil', _metadata,
  Column('id', Integer, primary_key = True),
  Column('sub_id', Integer, primary_key = True),
  Column('font_name', String),
  Column('font_size', Float),
  Column('str', String),
  Column('x', Float),
  Column('y', Float))

#######################################

time_signatures_to_xy_info = select([
  name.c.id.label('id'),
  font_name.c.val.label('font_name'),
  font_size.c.val.label('font_size'),
  time_signature.c.num.label('num_str'),
  time_signature.c.den.label('den_str'),
  literal(0.0).label('num_x'),
  literal(1.0).label('den_x'),
  literal(1.0).label('num_y'),
  literal(0.0).label('den_y')
]).where(and_(name.c.val == 'time_signature',
              name.c.id == font_name.c.id,
              name.c.id == font_size.c.id,
              name.c.id == time_signature.c.id)).\
cte(name='time_signatures_to_xy_info')

'''
LOOK HERE
'''
time_signatures_to_xy_info_num = time_signatures_to_xy_info.alias('time_signatures_to_xy_info_num')
# the line above causes literal binding to fail
# comment out the line above and uncomment the line below for the binding to succeed
#time_signatures_to_xy_info_num = time_signatures_to_xy_info

time_signatures_to_stencils = select([
   time_signatures_to_xy_info_num.c.id.label('id'),
   literal(0).label('sub_id'),
   time_signatures_to_xy_info_num.c.font_name.label('font_name'),
   time_signatures_to_xy_info_num.c.font_size.label('font_size'),
   time_signatures_to_xy_info_num.c.num_str.label('str'),
   time_signatures_to_xy_info_num.c.num_x.label('x'),
   time_signatures_to_xy_info_num.c.num_y.label('y'),
 ]).cte(name="time_signatures_to_stencils")

insert_stmt = stencil.insert().from_select(['id', 'sub_id', 'font_name', 'font_size', 'str', 'x', 'y'], select([time_signatures_to_stencils]))
print str(insert_stmt)
print "!=+~"*20
print "binding partially fails, partially succeeds"
print "!=+~"*20
print insert_stmt.compile(compile_kwargs={"literal_binds": True})

Comments (4)