Skip to content

Instantly share code, notes, and snippets.

@a2f0
Created January 25, 2014 01:58
Show Gist options
  • Save a2f0/8610635 to your computer and use it in GitHub Desktop.
Save a2f0/8610635 to your computer and use it in GitHub Desktop.
Would somebody be able to tell me why these queries are giving me different results when I am executing the same query? I am executing one in active record and one natively via SQLite.
SQLite:
sqlite> select * from commands LEFT OUTER JOIN tagmapsorters ON tagmapsorters.command_id = commands.id WHERE tagmapsorters.tag_id is NULL;
3|hi|2014-01-24 07:07:20.149017|2014-01-24 07:07:20.149017|hi|||||
sqlite> select * from commands;
2|unix|2014-01-24 06:47:51.701648|2014-01-24 06:47:51.701648|unix
3|hi|2014-01-24 07:07:20.149017|2014-01-24 07:07:20.149017|hi
4|linux|2014-01-24 07:41:29.121709|2014-01-24 07:41:29.121709|whatever
5|hi2|2014-01-24 10:09:58.773432|2014-01-24 10:09:58.773432|hi
sqlite> select * from tagmapsorters;
7|2|2|2014-01-24 07:18:58.882052|2014-01-24 07:18:58.882052
8|4|2|2014-01-24 07:41:29.123377|2014-01-24 07:41:29.123377
9|5|2|2014-01-24 10:09:58.778150|2014-01-24 10:09:58.778150
sqlite> .schema
CREATE TABLE "commands" ("id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, "text" text, "created_at" datetime, "updated_at" datetime, "description" varchar(255));
CREATE TABLE "schema_migrations" ("version" varchar(255) NOT NULL);
CREATE TABLE "tagmapsorters" ("id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, "command_id" integer, "tag_id" integer, "created_at" datetime, "updated_at" datetime);
CREATE TABLE "tags" ("id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, "tag" text, "created_at" datetime, "updated_at" datetime);
CREATE UNIQUE INDEX "unique_schema_migrations" ON "schema_migrations" ("version");
sqlite>
ActiveRecord:
2.0.0p247 :004 > Command.find_by_sql("select * from commands LEFT OUTER JOIN \
2.0.0p247 :005"> tagmapsorters ON tagmapsorters.command_id = commands.id \
2.0.0p247 :006"> WHERE tagmapsorters.tag_id is NULL")
Command Load (3.7ms) select * from commands LEFT OUTER JOIN tagmapsorters ON tagmapsorters.command_id = commands.id WHERE tagmapsorters.tag_id is NULL
=> [#<Command id: nil, text: "hi", created_at: nil, updated_at: nil, description: "hi">]
2.0.0p247 :007 >
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment