Skip to content

Instantly share code, notes, and snippets.

@dflemstr

dflemstr/ButGotQuery.sql

Created June 13, 2010 11:42
Show Gist options
  • Save dflemstr/436594 to your computer and use it in GitHub Desktop.
Save dflemstr/436594 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
ButGotQuery.sql
Select
items1.id as items1_id,
localizations3.itemId as localizations3_itemId,
localizations3.languageName as localizations3_languageName,
items1.id as items1_id,
localizations2.string as localizations2_string,
localizations2.itemId as localizations2_itemId,
localizations2.languageName as localizations2_languageName,
localizations3.string as localizations3_string,
localizations3.itemId as localizations3_itemId,
localizations3.languageName as localizations3_languageName
From
items items1,
localizations localizations2
left outer join localizations as localizations3 on ((items1.id = localizations3.itemId) and (localizations3.languageName = "de_DE"))
Where
((items1.id = localizations2.itemId) and (localizations2.languageName = "en_US"));
Raw
ExpectedQuery.sql
Select
items1.id as items1_id,
localizations2.string as localizations2_string,
localizations2.itemId as localizations2_itemId,
localizations2.languageName as localizations2_languageName,
localizations3.string as localizations3_string,
localizations3.itemId as localizations3_itemId,
localizations3.languageName as localizations3_languageName
From
items items1
left outer join localizations as localizations3 on ((items1.id = localizations3.itemId) and (localizations3.languageName = "de_DE")),
localizations localizations2
Where
((items1.id = localizations2.itemId) and (localizations2.languageName = "en_US"));
Raw
LocalizationWithInnerJoin.scala
import org.squeryl._
import org.squeryl.adapters._
import PrimitiveTypeMode._
class Item extends KeyedEntity[Long] {
val id = 0l
override def toString = "Item(" + id + ")"
}
case class Localization(itemId: Long, languageName: String, string: String)
object Database extends Schema {
val items = table[Item]("items")
val localizations = table[Localization]("localizations")
}
object Test {
def test() = {
SessionFactory.concreteFactory = Some(() => {
Class.forName("org.h2.Driver")
Session.create(
java.sql.DriverManager.getConnection("jdbc:h2:~/test", "sa", ""),
new H2Adapter
)
})
transaction {
try {
Database.create
val item1 = Database.items.insert(new Item())
val item2 = Database.items.insert(new Item())
Database.localizations.insert(Localization(item1.id, "en_US", "The randomness of agriculture"))
Database.localizations.insert(Localization(item1.id, "de_DE", "Wieso man niemals mit einem Gummifloß nach Italien fahren sollte"))
Database.localizations.insert(Localization(item2.id, "en_US", "Why did the world turn out to be this way?"))
} catch {case _ => }
println(from(Database.items, Database.localizations, Database.localizations)((i, en, de) =>
where(i.id === en.itemId and en.languageName === "en_US")
select(i, en,
leftOuterJoin(de, i.id === de.itemId and de.languageName === "de_DE"))
).toSeq)
()
}
}
}
Raw
LocalizationWithOuterJoin.scala
import org.squeryl._
import org.squeryl.adapters._
import PrimitiveTypeMode._
class Item extends KeyedEntity[Long] {
val id = 0l
override def toString = "Item(" + id + ")"
}
case class Localization(itemId: Long, languageName: String, string: String)
object Database extends Schema {
val items = table[Item]("items")
val localizations = table[Localization]("localizations")
}
object Test {
def test() = {
SessionFactory.concreteFactory = Some(() => {
Class.forName("org.h2.Driver")
Session.create(
java.sql.DriverManager.getConnection("jdbc:h2:~/test", "sa", ""),
new H2Adapter
)
})
transaction {
try {
Database.create
val item1 = Database.items.insert(new Item())
val item2 = Database.items.insert(new Item())
Database.localizations.insert(Localization(item1.id, "en_US", "The randomness of agriculture"))
Database.localizations.insert(Localization(item1.id, "de_DE", "Wieso man niemals mit einem Gummifloß nach Italien fahren sollte"))
Database.localizations.insert(Localization(item2.id, "en_US", "Why did the world turn out to be this way?"))
} catch {case _ => }
println(from(Database.items, Database.localizations, Database.localizations)((i, en, de) =>
select(i,
leftOuterJoin(en, i.id === en.itemId and en.languageName === "en_US"),
leftOuterJoin(de, i.id === de.itemId and de.languageName === "de_DE"))
).toSeq)
()
}
}
}
Raw
StackTrace.txt
java.lang.RuntimeException: Exception while executing statement, errorCode: 42122, sqlState: 42S22
Select
items1.id as items1_id,
localizations3.itemId as localizations3_itemId,
localizations3.languageName as localizations3_languageName,
items1.id as items1_id,
localizations2.string as localizations2_string,
localizations2.itemId as localizations2_itemId,
localizations2.languageName as localizations2_languageName,
localizations3.string as localizations3_string,
localizations3.itemId as localizations3_itemId,
localizations3.languageName as localizations3_languageName
From
items items1,
localizations localizations2
left outer join localizations as localizations3 on ((items1.id = localizations3.itemId) and (localizations3.languageName = ?))
Where
((items1.id = localizations2.itemId) and (localizations2.languageName = ?))
at org.squeryl.internals.DatabaseAdapter$class._exec(DatabaseAdapter.scala:233)
at org.squeryl.internals.DatabaseAdapter$class.exec(DatabaseAdapter.scala:282)
at org.squeryl.adapters.H2Adapter.exec(H2Adapter.scala:22)
at org.squeryl.internals.DatabaseAdapter$class.executeQuery(DatabaseAdapter.scala:284)
at org.squeryl.adapters.H2Adapter.executeQuery(H2Adapter.scala:22)
at org.squeryl.dsl.AbstractQuery$$anon$1.<init>(AbstractQuery.scala:110)
at org.squeryl.dsl.AbstractQuery.iterator(AbstractQuery.scala:105)
at scala.collection.IterableLike$class.foreach(IterableLike.scala:79)
at org.squeryl.dsl.AbstractQuery.foreach(AbstractQuery.scala:24)
at scala.collection.generic.Growable$class.$plus$plus$eq(Growable.scala:48)
at scala.collection.TraversableOnce$class.toList(TraversableOnce.scala:393)
at org.squeryl.dsl.AbstractQuery.toList(AbstractQuery.scala:24)
at scala.collection.TraversableOnce$class.toSeq(TraversableOnce.scala:405)
at org.squeryl.dsl.AbstractQuery.toSeq(AbstractQuery.scala:24)
at Test$$anonfun$test$1.apply$mcV$sp(Test.scala:40)
at Test$$anonfun$test$1.apply(Test.scala:26)
at Test$$anonfun$test$1.apply(Test.scala:26)
at org.squeryl.dsl.QueryDsl$class._using(QueryDsl.scala:40)
at org.squeryl.dsl.QueryDsl$class._executeTransactionWithin(QueryDsl.scala:86)
at org.squeryl.dsl.QueryDsl$class.transaction(QueryDsl.scala:59)
at org.squeryl.PrimitiveTypeMode$.transaction(PrimitiveTypeMode.scala:39)
at Test$.test(Test.scala:26)
at line3$object$$iw$$iw$.liftedTree1$1(<console>:6)
at line3$object$$iw$$iw$.<init>(<console>:6)
at line3$object$$iw$$iw$.<clinit>(<console>)
at RequestResult$line3$object$.<init>(<console>:9)
at RequestResult$line3$object$.<clinit>(<console>)
at RequestResult$line3$object.scala_repl_result(<console>)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:597)
at scala.tools.nsc.Interpreter$Request$$anonfun$loadAndRun$1$$anonfun$apply$18.apply(Interpreter.scala:981)
at scala.tools.nsc.Interpreter$Request$$anonfun$loadAndRun$1$$anonfun$apply$18.apply(Interpreter.scala:981)
at scala.util.control.Exception$Catch.apply(Exception.scala:79)
at scala.tools.nsc.Interpreter$Request$$anonfun$loadAndRun$1.apply(Interpreter.scala:980)
at scala.tools.nsc.Interpreter$Request$$anonfun$loadAndRun$1.apply(Interpreter.scala:980)
at scala.util.control.Exception$Catch.apply(Exception.scala:79)
at scala.tools.nsc.Interpreter$Request.loadAndRun(Interpreter.scala:979)
at scala.tools.nsc.Interpreter.loadAndRunReq$1(Interpreter.scala:578)
at scala.tools.nsc.Interpreter.interpret(Interpreter.scala:597)
at scala.tools.nsc.Interpreter.interpret(Interpreter.scala:575)
at scala.tools.nsc.InterpreterLoop.reallyInterpret$1(InterpreterLoop.scala:471)
at scala.tools.nsc.InterpreterLoop.interpretStartingWith(InterpreterLoop.scala:514)
at scala.tools.nsc.InterpreterLoop.command(InterpreterLoop.scala:361)
at scala.tools.nsc.InterpreterLoop.processLine$1(InterpreterLoop.scala:242)
at scala.tools.nsc.InterpreterLoop.repl(InterpreterLoop.scala:248)
at scala.tools.nsc.InterpreterLoop.main(InterpreterLoop.scala:558)
at xsbt.ConsoleInterface.run(ConsoleInterface.scala:26)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:597)
at xsbt.AnalyzingCompiler.call(AnalyzingCompiler.scala:40)
at xsbt.AnalyzingCompiler.console(AnalyzingCompiler.scala:32)
at sbt.Console.console0$1(Compile.scala:141)
at sbt.Console$$anonfun$apply$4$$anonfun$apply$5.apply(Compile.scala:142)
at sbt.Console$$anonfun$apply$4$$anonfun$apply$5.apply(Compile.scala:142)
at sbt.TrapExit$.executeMain$1(TrapExit.scala:33)
at sbt.TrapExit$$anon$1.run(TrapExit.scala:42)
Caused by: org.h2.jdbc.JdbcSQLException: Column ITEMS1.ID not found; SQL statement:
Select
items1.id as items1_id,
localizations3.itemId as localizations3_itemId,
localizations3.languageName as localizations3_languageName,
items1.id as items1_id,
localizations2.string as localizations2_string,
localizations2.itemId as localizations2_itemId,
localizations2.languageName as localizations2_languageName,
localizations3.string as localizations3_string,
localizations3.itemId as localizations3_itemId,
localizations3.languageName as localizations3_languageName
From
items items1,
localizations localizations2
left outer join localizations as localizations3 on ((items1.id = localizations3.itemId) and (localizations3.languageName = ?))
Where
((items1.id = localizations2.itemId) and (localizations2.languageName = ?)) [42122-121]
at org.h2.message.Message.getSQLException(Message.java:106)
at org.h2.message.Message.getSQLException(Message.java:117)
at org.h2.message.Message.getSQLException(Message.java:76)
at org.h2.expression.ExpressionColumn.optimize(ExpressionColumn.java:128)
at org.h2.expression.Comparison.optimize(Comparison.java:146)
at org.h2.expression.ConditionAndOr.optimize(ConditionAndOr.java:131)
at org.h2.command.dml.Select.preparePlan(Select.java:816)
at org.h2.command.dml.Select.prepare(Select.java:734)
at org.h2.command.Parser.prepareCommand(Parser.java:236)
at org.h2.engine.Session.prepareLocal(Session.java:415)
at org.h2.engine.Session.prepareCommand(Session.java:376)
at org.h2.jdbc.JdbcConnection.prepareCommand(JdbcConnection.java:1049)
at org.h2.jdbc.JdbcPreparedStatement.<init>(JdbcPreparedStatement.java:73)
at org.h2.jdbc.JdbcConnection.prepareStatement(JdbcConnection.java:233)
at org.squeryl.internals.DatabaseAdapter$class.prepareStatement(DatabaseAdapter.scala:202)
at org.squeryl.adapters.H2Adapter.prepareStatement(H2Adapter.scala:22)
at org.squeryl.internals.DatabaseAdapter$$anonfun$executeQuery$1.apply(DatabaseAdapter.scala:285)
at org.squeryl.internals.DatabaseAdapter$$anonfun$executeQuery$1.apply(DatabaseAdapter.scala:284)
at org.squeryl.internals.DatabaseAdapter$class._exec(DatabaseAdapter.scala:229)
... 59 more
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment