<p dir="auto"><strong>Migrated issue, originally created by Raphaël Slinckx</strong></p>
<p dir="auto">Here is a test case yielding different results between sqlalchemy 0.9.9 and 1.0+</p>
<div class="highlight highlight-source-python notranslate position-relative overflow-auto" dir="auto" data-snippet-clipboard-copy-content="import json

from sqlalchemy import create_engine, Column
from sqlalchemy.types import TypeDecorator, UnicodeText, Integer
from sqlalchemy.ext.mutable import MutableDict
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import Session

class JSONDictType(TypeDecorator):
	impl = UnicodeText

	def process_bind_param(self, value, dialect):
		if value is not None:
			value = unicode(json.dumps(value))
		return value

	def process_result_value(self, value, dialect):
		if value is not None:
			value = json.loads(value)
		return value

def JSON():
	return MutableDict.as_mutable(JSONDictType)

Base = declarative_base()

class MyDataClass(Base):
	__tablename__ = 'my_data'

	id   = Column(Integer, primary_key=True)
	data = Column(JSON(), default={}, nullable=False)

session = Session(create_engine(&quot;sqlite:///:memory:&quot;, echo=True))
Base.metadata.create_all(session.bind)

m1 = MyDataClass()
session.add(m1)
session.flush() # Note: without this, the m1.data is None on sqlalchemy 1.0
m1.data[&quot;test&quot;] = 1
session.commit()

m2 = MyDataClass()
session.add(m2)
session.commit()

assert m1.data == {'test': 1}
assert m2.data == {}
# With sqlalchemy 1.0 this results in
# m1.data == {}
# m2.data == {'test': 1}"><pre class="notranslate"><span class="pl-k">import</span> <span class="pl-s1">json</span>

<span class="pl-k">from</span> <span class="pl-s1">sqlalchemy</span> <span class="pl-k">import</span> <span class="pl-s1">create_engine</span>, <span class="pl-v">Column</span>
<span class="pl-k">from</span> <span class="pl-s1">sqlalchemy</span>.<span class="pl-s1">types</span> <span class="pl-k">import</span> <span class="pl-v">TypeDecorator</span>, <span class="pl-v">UnicodeText</span>, <span class="pl-v">Integer</span>
<span class="pl-k">from</span> <span class="pl-s1">sqlalchemy</span>.<span class="pl-s1">ext</span>.<span class="pl-s1">mutable</span> <span class="pl-k">import</span> <span class="pl-v">MutableDict</span>
<span class="pl-k">from</span> <span class="pl-s1">sqlalchemy</span>.<span class="pl-s1">ext</span>.<span class="pl-s1">declarative</span> <span class="pl-k">import</span> <span class="pl-s1">declarative_base</span>
<span class="pl-k">from</span> <span class="pl-s1">sqlalchemy</span>.<span class="pl-s1">orm</span> <span class="pl-k">import</span> <span class="pl-v">Session</span>

<span class="pl-k">class</span> <span class="pl-v">JSONDictType</span>(<span class="pl-v">TypeDecorator</span>):
	<span class="pl-s1">impl</span> <span class="pl-c1">=</span> <span class="pl-v">UnicodeText</span>

	<span class="pl-k">def</span> <span class="pl-en">process_bind_param</span>(<span class="pl-s1">self</span>, <span class="pl-s1">value</span>, <span class="pl-s1">dialect</span>):
		<span class="pl-k">if</span> <span class="pl-s1">value</span> <span class="pl-c1">is</span> <span class="pl-c1">not</span> <span class="pl-c1">None</span>:
			<span class="pl-s1">value</span> <span class="pl-c1">=</span> <span class="pl-en">unicode</span>(<span class="pl-s1">json</span>.<span class="pl-en">dumps</span>(<span class="pl-s1">value</span>))
		<span class="pl-k">return</span> <span class="pl-s1">value</span>

	<span class="pl-k">def</span> <span class="pl-en">process_result_value</span>(<span class="pl-s1">self</span>, <span class="pl-s1">value</span>, <span class="pl-s1">dialect</span>):
		<span class="pl-k">if</span> <span class="pl-s1">value</span> <span class="pl-c1">is</span> <span class="pl-c1">not</span> <span class="pl-c1">None</span>:
			<span class="pl-s1">value</span> <span class="pl-c1">=</span> <span class="pl-s1">json</span>.<span class="pl-en">loads</span>(<span class="pl-s1">value</span>)
		<span class="pl-k">return</span> <span class="pl-s1">value</span>

<span class="pl-k">def</span> <span class="pl-v">JSON</span>():
	<span class="pl-k">return</span> <span class="pl-v">MutableDict</span>.<span class="pl-en">as_mutable</span>(<span class="pl-v">JSONDictType</span>)

<span class="pl-v">Base</span> <span class="pl-c1">=</span> <span class="pl-en">declarative_base</span>()

<span class="pl-k">class</span> <span class="pl-v">MyDataClass</span>(<span class="pl-v">Base</span>):
	<span class="pl-s1">__tablename__</span> <span class="pl-c1">=</span> <span class="pl-s">'my_data'</span>

	<span class="pl-s1">id</span>   <span class="pl-c1">=</span> <span class="pl-v">Column</span>(<span class="pl-v">Integer</span>, <span class="pl-s1">primary_key</span><span class="pl-c1">=</span><span class="pl-c1">True</span>)
	<span class="pl-s1">data</span> <span class="pl-c1">=</span> <span class="pl-v">Column</span>(<span class="pl-v">JSON</span>(), <span class="pl-s1">default</span><span class="pl-c1">=</span>{}, <span class="pl-s1">nullable</span><span class="pl-c1">=</span><span class="pl-c1">False</span>)

<span class="pl-s1">session</span> <span class="pl-c1">=</span> <span class="pl-v">Session</span>(<span class="pl-en">create_engine</span>(<span class="pl-s">"sqlite:///:memory:"</span>, <span class="pl-s1">echo</span><span class="pl-c1">=</span><span class="pl-c1">True</span>))
<span class="pl-v">Base</span>.<span class="pl-s1">metadata</span>.<span class="pl-en">create_all</span>(<span class="pl-s1">session</span>.<span class="pl-s1">bind</span>)

<span class="pl-s1">m1</span> <span class="pl-c1">=</span> <span class="pl-v">MyDataClass</span>()
<span class="pl-s1">session</span>.<span class="pl-en">add</span>(<span class="pl-s1">m1</span>)
<span class="pl-s1">session</span>.<span class="pl-en">flush</span>() <span class="pl-c"># Note: without this, the m1.data is None on sqlalchemy 1.0</span>
<span class="pl-s1">m1</span>.<span class="pl-s1">data</span>[<span class="pl-s">"test"</span>] <span class="pl-c1">=</span> <span class="pl-c1">1</span>
<span class="pl-s1">session</span>.<span class="pl-en">commit</span>()

<span class="pl-s1">m2</span> <span class="pl-c1">=</span> <span class="pl-v">MyDataClass</span>()
<span class="pl-s1">session</span>.<span class="pl-en">add</span>(<span class="pl-s1">m2</span>)
<span class="pl-s1">session</span>.<span class="pl-en">commit</span>()

<span class="pl-k">assert</span> <span class="pl-s1">m1</span>.<span class="pl-s1">data</span> <span class="pl-c1">==</span> {<span class="pl-s">'test'</span>: <span class="pl-c1">1</span>}
<span class="pl-k">assert</span> <span class="pl-s1">m2</span>.<span class="pl-s1">data</span> <span class="pl-c1">==</span> {}
<span class="pl-c"># With sqlalchemy 1.0 this results in</span>
<span class="pl-c"># m1.data == {}</span>
<span class="pl-c"># m2.data == {'test': 1}</span></pre></div>
<p dir="auto">As you can see the default={} seemed to work previously (also the m1.data['test'] = 1 worked without the session.flush() just above)</p>
<p dir="auto">With the latest version, the dict provided as default seems to be used and re-used resulting in a process-wide globally shared default dict which can lead to catastrophic leaks in a webapp for example. Also the object needs to be flushed before m1.data is populated.</p>
<p dir="auto">Maybe this usage was not expected or even discouraged, but I didn't find anything in the docs about this. The closest thing I identified is the change discussed here: <a href="http://docs.sqlalchemy.org/en/latest/changelog/migration_10.html#changes-to-attribute-events-and-other-operations-regarding-attributes-that-have-no-pre-existing-value" rel="nofollow">http://docs.sqlalchemy.org/en/latest/changelog/migration_10.html#changes-to-attribute-events-and-other-operations-regarding-attributes-that-have-no-pre-existing-value</a> and possibly the new default handling.</p>
<p dir="auto">What is the expected behavior as of 1.0 ? And how do I get the old behavior using the latest version ?</p>
<p dir="auto"><strong>Migrated issue, originally created by Anonymous</strong></p>
<p dir="auto">If I create a join object and then use it as a parameter for relation's <strong>primaryjoin</strong>, the mapper will compile without complaining, but accessing the property that maps to such a relation generates illegal SQL and throws ProgrammingError.</p>
<p dir="auto">Ie.:</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="class_mapper(Class).add_property('property', relation(OtherClass, primaryjoin=Class.other_id==OtherClass.id)))"><pre class="notranslate"><code class="notranslate">class_mapper(Class).add_property('property', relation(OtherClass, primaryjoin=Class.other_id==OtherClass.id)))
</code></pre></div>
<p dir="auto">works fine but:</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="myjoin=join(Class, OtherCalss, Class.other_id==OtherClass.id)
class_mapper(Class).add_property('property', relation(OtherClass, primaryjoin=myjoin))"><pre class="notranslate"><code class="notranslate">myjoin=join(Class, OtherCalss, Class.other_id==OtherClass.id)
class_mapper(Class).add_property('property', relation(OtherClass, primaryjoin=myjoin))
</code></pre></div>
<p dir="auto">Compiles but throws ProgrammingError on accessing Class.property.</p>
<p dir="auto">If a join cannot be used to specify <strong>primaryjoin</strong> then the mapper should complain right away; otherwise, SQLA should somehow extract the join clause from the join when it's used in this way.</p>
<p dir="auto">This issue may be related to changeset <a href="5325">5325</a>.</p>
<p dir="auto">I've attached a test case that's maybe a little more complicated than necessary but demonstrates the issue clearly.</p>
<hr>
<p dir="auto">Attachments: <a href="../wiki/imported_issue_attachments/1622/join_as_primaryjoin.py">join_as_primaryjoin.py</a></p>