I faced an issue with SQLite (version 3.7.13 if matters).
I created a new table with two columns foo and bar, data type is undefined. When I try to insert numbers, it works fine. When I insert text, «Error: no such column» happens.
sqlite> CREATE TABLE test (foo, bar);
sqlite> .tables
test
sqlite> insert into test values (0,1);
sqlite> select * from test;
0|1
sqlite> insert into test values (a,b);
Error: no such column: a
What am I doing wrong?
Thanks.
asked Feb 22, 2014 at 18:47
You need to quote strings
insert into test values('a', 'b')
answered Feb 22, 2014 at 19:00
ThayneThayne
6,4392 gold badges41 silver badges63 bronze badges
1
This is a very basic question and I know there are security issues with this code and it should be using parameterized entries among other issues — it is a work in progress. I am attempting to set build a user registration module for a project. I have set up a table with with the first column serving as an ID with a primary key constraint but when I run the code, I get the following error and am not sure why — (if it relates to the p_ID column):
Traceback (most recent call last):
File "user.py", line 72, in <module>
userSignUp()
File "user.py", line 68, in userSignUp
c.execute("INSERT INTO People VALUES(userName, password, confirmPassword, firstName,lastName, companyName, email, phoneNumber,addressLine1, addressLine2, addressLine3, zipCode, province, country, regDate)")
sqlite3.OperationalError: no such column: userName
The code is:
import sqlite3
import datetime
path = "/Users/workhorse/thinkful/"
db = "apartment.db"
def checkAndCreateDB():
#not checking for some reason
#fullPath = os.path.join(path, db)
#if os.path.exists(fullPath):
# print "Database Exists"
#else:
connection = sqlite3.connect(db)
print "Creating database"
createUserRegTable()
def createUserRegTable():
with sqlite3.connect(db) as connection:
c = connection.cursor()
c.execute("""CREATE TABLE People
(p_ID INTEGER PRIMARY KEY AUTOINCREMENT,
userName TEXT NOT NULL UNIQUE,
password TEXT NOT NULL,
confirmPassword TEXT NOT NULL,
firstName TEXT NOT NULL,
lastName TEXT NOT NULL,
companyName TEXT NOT NULL,
email TEXT NOT NULL UNIQUE,
phoneNumber TEXT NOT NULL,
addressLine1 TEXT NOT NULL,
addressLine2 TEXT,
addressLine3 TEXT,
zipCode TEXT NOT NULL,
province TEXT NOT NULL,
country TEXT NOT NULL,
regDate DATE NOT NULL)
""")
print "table made"
def userSignIn():
pass
def userSignUp():
userName = raw_input("Enter a user name: ")
password = raw_input("Enter a password: ")
confirmPassword = raw_input("Confirm Your Password: ")
firstName = raw_input("Enter your first name: ")
lastName = raw_input("Enter your last name: ")
companyName = raw_input("Enter your company name: ")
email = raw_input("Enter your email: ")
phoneNumber = raw_input("Enter your phone number: ")
addressLine1 = raw_input("Enter your address: ")
addressLine2 = raw_input("Enter second line of your address (Not Required): ")
addressLine3 = raw_input("Enter third line of your address (Not Required): ")
zipCode = raw_input("Enter your zip code: ")
province = raw_input("Enter your state or province: ")
country = raw_input("Enter your country: ")
regDate = datetime.date.today()
print regDate
#userInfo = (userName, password, confirmPassword, firstName,lastName, companyName, email, phoneNumber,addressLine1,
#addressLine2, addressLine3, zipCode, province, country, regDate)
with sqlite3.connect(db) as connection:
c = connection.cursor()
c.execute("INSERT INTO People VALUES(userName, password, confirmPassword, firstName,lastName, companyName, email, phoneNumber,addressLine1, addressLine2, addressLine3, zipCode, province, country, regDate)")
checkAndCreateDB()
userSignUp()
Much thanks
I have a table (trackedinfo) inside my database that has the following columns (columns obtained by running PRAGMA table_info(trackedinfo);)
The problem is that even though the column sendok exists, when running a query on the database with that field, it throws an error.
Example queries:
SELECT * FROM trackedinfo WHERE sendok IS NULL;
SELECT sendok FROM trackedinfo;
Error:
SQLITE_ERROR: SQL error or missing database (no such column: sendok)
But, if I run a query selecting all of the fields, it brings me the info regarding sendok:
Here is the CREATE command of the database:
CREATE TABLE trackedinfo
(
id INTEGER PRIMARY KEY,
date_time_start TEXT,
date_time_end TEXT,
tracked_name TEXT,
tracked_origin TEXT,
tracked_maker TEXT,
tracked_version TEXT,
tracked_type TEXT,
sendok TEXT,
tracked_id TEXT
);
It also happens with the column tracked_id
Info that I got by executing .schema trackedinfo
CREATE TABLE IF NOT EXISTS "trackedinfo" ("id" INTEGER PRIMARY KEY, "date_time_start" TEXT, "date_time_end" TEXT, "tracked_name" TEXT, "tracked_origin" TEXT, "tracked_maker" TEXT, "tracked_version" TEXT, "tracked_type" TEXT, "sendok " TEXT, "tracked_id " TEXT);
Содержание
- SQLite Forum
- (1) By Gwendal Roué (groue) on 2020-04-19 12:41:17 [source]
- (2) By Neal (_Neal_) on 2020-04-19 14:25:17 in reply to 1 [link] [source]
- (3) By Ryan Smith (cuz) on 2020-04-19 16:04:35 in reply to 1 [link] [source]
- (4) By Gwendal Roué (groue) on 2020-04-19 16:33:28 in reply to 3 [link] [source]
- (5.1) By Gwendal Roué (groue) on 2020-04-19 17:01:01 edited from 5.0 in reply to 3 [link] [source]
- (6) By Ryan Smith (cuz) on 2020-04-20 01:03:49 in reply to 5.1 [link] [source]
- (7) By Keith Medcalf (kmedcalf) on 2020-04-20 02:24:44 in reply to 5.1 [link] [source]
- (8.1) By Gwendal Roué (groue) on 2020-04-20 07:37:48 edited from 8.0 in reply to 7 [link] [source]
- (9) By Gwendal Roué (groue) on 2020-04-20 16:13:36 in reply to 7 [link] [source]
SQLite Forum
(1) By Gwendal Roué (groue) on 2020-04-19 12:41:17 [source]
I’m working on a SQL generator that wants to «flatten» a to-many relation backed by a foreign key into a to-one relation by focusing on a single row in the associated rows.
Sometimes examples make things more clear. Let’s say we want to fetch «all chats along with their latest post», given the following schema:
In my experiments looking for a SQL pattern that provides correct results, while remaining composable (I’m not looking for the most efficient SQL, and generally speaking I trust the SQLite query planner), I stumbled upon a surprising behavior with SQLite version 3.28.0.
This query works:
This one fails with a «no such column» error:
I have two questions, if someone can help. The first is the most important to me. The second is just a request for clarification:
- Can I rely on the fact that the first query will never give such an error in the future?
- Why can some subqueries refer to outer tables, and some others can not?
(2) By Neal (_Neal_) on 2020-04-19 14:25:17 in reply to 1 [link] [source]
Columns from both of the join operands can be referenced in ON clause — thus your first query works. But your second query is trying to refer to first join operand’s column in second join operand — that is not allowed.
(3) By Ryan Smith (cuz) on 2020-04-19 16:04:35 in reply to 1 [link] [source]
- Yes the first query will always work, but
- That is not the problem with query 2 — The SELECT is very happy, it’s the JOIN that is the problem because you are joining to an as-yet non existing table/view (which the join creates) but then trying to refer to chat.id in that second join-created-view, however «chat» never appears in THAT join’s SELECT .. FROM, and you cannot magically grab it from some other JOIN you did earlier. The JOINS are not the same and won’t be in the same For-Loop at the same row/position at the same moment — that only works for correlated sub-queries, which your JOIN is not.
Perhaps read up on Correlated sub-queries if what I say did not make immediate sense, once you understand the limitations of those, it should become more clear why your JOIN did not behave as expected, and how you could make that sort of thing work.
(4) By Gwendal Roué (groue) on 2020-04-19 16:33:28 in reply to 3 [link] [source]
Perhaps read up on Correlated sub-queries if what I say did not make immediate sense
I’ll surely do! Thank you Ryan for hinting me at a better understanding of what I’m trying to achieve. That’s very well appreciated, and exactly what I was hoping for, even if I was too shy to ask for it 🙂
(5.1) By Gwendal Roué (groue) on 2020-04-19 17:01:01 edited from 5.0 in reply to 3 [link] [source]
Perhaps read up on Correlated sub-queries if what I say did not make immediate sense
So I did. A correlated sub query executes as if it runs once for each row in the outer query.
But «executes as if» and «actually executes» are not always the same. The database is allowed to perform whatever optimization it wants, as long as the observed behavior matches the expected one.
So does the working query below exhibits a quadratic behavior?
I’m not quite used to query planning interpretation yet:
(6) By Ryan Smith (cuz) on 2020-04-20 01:03:49 in reply to 5.1 [link] [source]
Please forgive me, I may not be 100% following your question, and it’s very late, but I’ll give it a try.
Firstly, I don’t know what «exhibits quadratic behavior» means in this case, but I will assume you are asking «will the QP do a Cartesian join where it steps ALL the «FROM rows» (chat) times ALL the JOINed rows (post). [aka O(n^2)]
That answer is no, even though it seems like it would have to.
The Query you propose (for which the Query plan is given) seemingly will start by walking (iterating through) the entire chat table (because you offer no filter in a WHERE clause or such).
Now note that the QP is free to choose to start with the joined table if it pleases, but won’t in this case as reported by the query plan.
It then will simply look up the correlated value using the PK (rowid) of the «post» table, causing only O(log n) look-ups. Note that your ORDER BY and DESC LIMIT 1 statements has no effect and is completely ignored here because the link is to a rowid (or stated otherwise, the unique primary key) so row number N can only ever be row number N, no matter how it is «ordered» or limited, in any lookup.
i.e. — this should have the same query plan:
This may well NOT be the case for other types of correlated sub-query which perhaps do not use the primary key. In that case, the order-by would help to make the QP construct a temporary table with temporary index to do the look-ups. It is hard to guarantee though, and best practice when doing correlated sub-queries is to ensure you have Indexes on the looked-up fields.
I find it best to think of the correlated sub-query as a form of Table/View, and then asking myself if I can see a clear relationship between the tables and how I can phrase the query so the Query-planner cannot miss that same relationship. (And then test it with EXPLAIN of course).
Another way of doing the above would be with a Common Table Expression (CTE — more good reading material) which is like creating a view inside your query which may look something like this:
which is roughly equivalent to:
but with the advantage that multiple CTEs can be added and that they can refer each other in their respective SELECT clauses.
This is all tricky, because the Query Planner can decide, based on many whims, how it wants to traverse query tables. It may be that your Query suddenly switches to another plan after adding some data and doing ANALYZE. The best is to test with some real-World data.
Hope that helps and best of luck!
(7) By Keith Medcalf (kmedcalf) on 2020-04-20 02:24:44 in reply to 5.1 [link] [source]
The performance will be, at best case, O(N*M^2) where N is the number of rows in chat and M is the number of rows in post. It will probably degrade a lot faster than that. We recently discussed that in another thread.
You appear to be trying to find the «earliest post» for each «thread». Note the following comments:
the chat table is useless when querying data as it does nothing except waste time and you can tell this because
(a) there is nothing from that table in the select list; and,
(b) removing all reference to it does not affect the query result in any way.
(chatid, date) must be a candidate key for the post table (that is, it must be unique). If it is not unique then the concept of top and bottom of a chatid cannot use that field and some other tiebreaker must be used. If some other tiebreaker must be used, just use that directly and forget about the added complication of adding something useless.
So, you want the following:
(8.1) By Gwendal Roué (groue) on 2020-04-20 07:37:48 edited from 8.0 in reply to 7 [link] [source]
The performance will be, at best case, O(N*M^2) where N is the number of rows in chat and M is the number of rows in post. It will probably degrade a lot faster than that.
All right. Thank you very much for pointing this out! I can’t use this technique, then, because I won’t ship a library which generates SQL with such a bad complexity.
We recently discussed that in another thread.
I’d be happy to check this conversation.
You appear to be trying to find the «earliest post» for each «thread».
Actually, the example wants to load both thread («chat») and latest post. Imagine you want to feed the main screen of a chat app, with all conversations and a preview of the last message.
the chat table is useless when querying data as it does nothing except waste time and you can tell this because (a) there is nothing from that table in the select list; and, (b) removing all reference to it does not affect the query result in any way.
So, the point is actually to get chat information along: its a SELECT chat.*, post.*
(chatid, date) must be a candidate key for the post table (that is, it must be unique).
I really appreciate your help. This is not an acceptable constraint, in the general case, unfortunately:
In the chat/post example, we can not require unique post dates in a thread. This would exclude a whole set of reasonable and real use cases. If the user wants to disambiguate the «last post», the user would would have to sort per date and another disambiguation column (such as the primary key). If the user does not perform such disambiguation, then any post can be chosen (meaning I can add the primary key as an extra disambiguation ordering clause in the SQL I generate).
The chat/post example is just one example, for illustration purpose. My general case is the following:
- one «parent» table
- one «child» table
- a foreign key from child to parent
- a set of ordering clauses on the child table, which identifies one child per parent. The ordering clauses may not identify a single child per parent. The set of ordering clauses may actually be empty.
- An SQL query
- which selects ( parent.*, child.* ) pairs,
- where the child is the first child of each parent according by the set of ordering clauses (any child is ok if the ordering clauses are ambiguous)
- where the child may actually be missing (if a parent has no child), in a similar fashion to LEFT JOIN
- of acceptable complexity
- Advice for indexes in order to get the desired complexity.
Of course, if this general case is too relaxed, and does not satisfy the requirements for such a desired output, then I’ll have to adapt, and maybe distinguish more cases. For example, unique indexes, if acceptable by the user, can be used. But I have to deal with the fact that users may comes with a «weak» setup. Yet I do not want to punish users who have a «strong» setup with an SQL query which has a bad O(. ) complexity.
It’s a difficult exercice, but I hope I can find out a solution 🙂
Edit: if there is no general solution to the general problem as I describe it, then I may have to provide an advice such as «in such situation, setup an extra foreign key to the desired child, and consider using triggers so that it get automatically updated whenever the child table is modified». That would be an acceptable solution also, even if much less easy for the users to setup. I consider the «all chats with their latest post» and all similar queries to be an important use case to address, and I want to help users who do not want to write their SQL themselves (because they don’t want to, or because they can’t).
(9) By Gwendal Roué (groue) on 2020-04-20 16:13:36 in reply to 7 [link] [source]
Keith, you have provided excellent hints, which drove me to a solution that sounds quite general and satisfying.
It uses window functions, so it requires SQLite 3.28+.
It looks like it has a good complexity, as I see by running the request on datasets of various sizes. For a constant number of posts per chats, it is linear with the number of chats. For a constant number of chats, it is linear with the number of posts per chats. This sounds quite acceptable to me, as it fits well a kind of database that is quite common, in the wild.
It is general enough, because it accepts the input I have:
- one parent table (here, «chat»)
- one child table (here, «post»)
- a foreign key from child to parent
- a set of ordering clauses on the child table, which identifies one child per parent. The ordering clauses may not identify a single child per parent. The set of ordering clauses may actually be empty.
Источник
-
#1
from sqlite3 import *
base = connect(«BASE.db»)
cursor = base.cursor()
LIST10 = list(cursor.execute(«»»SELECT Номер10 FROM Words»»»))
LIST11 = list(cursor.execute(«»»SELECT Номер11 FROM Words»»»))
LIST12 = list(cursor.execute(«»»SELECT Номер12 FROM Words»»»))
LIST15 = list(cursor.execute(«»»SELECT Номер15 FROM Words»»»))
cursor.execute(«»»DELETE FROM Words»»»)
def write_funck(list_num,num):
INP = None
while INP != »:
INP = input(‘Введите слово для номера 10: ‘)
list_num.append(INP.lower())
list_num.remove(»)
word_set = set(list_num)
list_num = list(word_set)
def main():
choose = int(input(‘выберете номер задания(10,11,12,15): ‘))
if choose == 10:
write_funck(LIST10,choose)
elif choose == 11:
write_funck(LIST11,choose)
elif choose == 12:
write_funck(LIST12,choose)
else:
write_funck(LIST15,choose)
main()
cursor.execute(f»»»INSERT INTO Words(Номер10) VALUES({LIST10})»»»)
cursor.execute(f»»»INSERT INTO Words(Номер11) VALUES({LIST11})»»»)
cursor.execute(f»»»INSERT INTO Words(Номер12) VALUES({LIST12})»»»)
cursor.execute(f»»»INSERT INTO Words(Номер15) VALUES({LIST15})»»»)
base.commit()
cursor.close()
-
#3
sqlite3(No such column(Элементы массива)) — это значит что в таблице нет такого столбца.
Вот тут есть пример вставки данных в таблицу:
https://younglinux.info/sqlite/insert
Так проблема в том,что я добавляю в столбец(Номер10) массив состоящий из слов.Но в качестве столбцов программа использует элементы массива, и я не понимаю почему.Вроде по синтаксису Insert сделан правильно.
-
#4
По коду не понятно какие столбцы у вас в таблице (сколько их и какого они типа). Покажите код которым создаете таблицу.
-
#5
По коду не понятно какие столбцы у вас в таблице (сколько их и какого они типа). Покажите код которым создаете таблицу.
-
#6
Список в базу можно добавить в виде строки (т. к. в виде списка не примет):
Python:
from sqlite3 import *
base = connect('BASE.db')
cursor = base.cursor()
l = ['1', '2', '3']
cursor.execute("""INSERT INTO Words ('Номер10') VALUES (?)""", (str(l),))
base.commit()
base.close()
-
#7
Или можно список объединить в строку с помощью join и записать, а при получении разделять через split:
Запись:
Python:
from sqlite3 import *
base = connect('BASE.db')
cursor = base.cursor()
l = ['1', '2', '3']
x = ",".join(l)
cursor.execute("""INSERT INTO Words ('Номер10') VALUES (?) """, (x,))
base.commit()
base.close()Чтение:
Python:
from sqlite3 import *
base = connect('BASE.db')
cursor = base.cursor()
c = cursor.execute("""SELECT Номер10 FROM Words WHERE rowid=1""")
l = c.fetchone()
y = l[0].split(',')
print(y)Запись в определенный ряд таблицы:
Python:
base = connect('BASE.db')
cursor = base.cursor()
l = ['1', '2', '3']
x = ",".join(l)
cursor.execute("""UPDATE Words SET 'Номер10' = (?) WHERE rowid=1""", (x,))
base.commit()
base.close()
-
#8
Список в базу можно добавить в виде строки (т. к. в виде списка не примет):
Python:
from sqlite3 import * base = connect('BASE.db') cursor = base.cursor() l = ['1', '2', '3'] cursor.execute("""INSERT INTO Words ('Номер10') VALUES (?)""", (str(l),)) base.commit() base.close()
Спасибо вам огромное)))))
After upgrading ASP.NET Core 1.1 project to 2.1.4, I am no longer able to execute custom queries I wrote. Please see below stack trace and code. I don’t think so its an issue with the code as it worked just fine when it was running in v1.1. Anyone have any workaround in the mean time? I would hate to downgrade my project back to 1.1.
Exception message:
Microsoft.Data.Sqlite.SqliteException (0x80004005): SQLite Error 1: 'no such column: c.state_id'.
Stack trace:
Microsoft.Data.Sqlite.SqliteException (0x80004005): SQLite Error 1: 'no such column: c.state_id'.
at Microsoft.Data.Sqlite.SqliteException.ThrowExceptionForRC(Int32 rc, sqlite3 db)
at Microsoft.Data.Sqlite.SqliteCommand.ExecuteReader(CommandBehavior behavior)
at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.Execute(IRelationalConnection connection, DbCommandMethod executeMethod, IReadOnlyDictionary`2 parameterValues)
Microsoft.EntityFrameworkCore.Query:Error: An exception occurred in the database while iterating the results of a query for context type 'Sunbelt.Data.ApplicationDbContext'.
Microsoft.Data.Sqlite.SqliteException (0x80004005): SQLite Error 1: 'no such column: c.state_id'.
at Microsoft.Data.Sqlite.SqliteException.ThrowExceptionForRC(Int32 rc, sqlite3 db)
at Microsoft.Data.Sqlite.SqliteCommand.ExecuteReader(CommandBehavior behavior)
at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.Execute(IRelationalConnection connection, DbCommandMethod executeMethod, IReadOnlyDictionary`2 parameterValues)
at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.ExecuteReader(IRelationalConnection connection, IReadOnlyDictionary`2 parameterValues)
at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable`1.Enumerator.BufferlessMoveNext(DbContext _, Boolean buffer)
at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable`1.Enumerator.MoveNext()
at Microsoft.EntityFrameworkCore.Query.Internal.LinqOperatorProvider.ExceptionInterceptor`1.EnumeratorExceptionInterceptor.MoveNext()
Steps to reproduce
Include a complete code listing (or project/solution) that we can run to reproduce the issue.
This query use to work fine in EF Core 1.0 version. After upgrading to 2.1 it is no longer working.
# region
var results = from c in _context.Customers
join BState in _context.States on c.StateId equals BState.Id into lBStates
from lBState in lBStates.Where(r => r.Id == c.StateId).DefaultIfEmpty()
join MState in _context.States on c.MStateId equals MState.Id into LMStates
from LMState in LMStates.Where(r => r.Id == c.MStateId).DefaultIfEmpty()
join status in _context.Statuses on c.StatusId equals status.Id into lstatuses
from lstatus in lstatuses.Where(r => r.Id == c.StatusId).DefaultIfEmpty()
join u in _context.Employees on c.ZoneManager equals u.Id into lus
from lu in lus.Where(r => r.Id == c.ZoneManager).DefaultIfEmpty()
join regions in _context.Regions on c.RegionId equals regions.Id into lregions
from lregion in lregions.Where(r => r.Id == c.Id).DefaultIfEmpty()
join zone in _context.Zones on c.ZoneId equals zone.Id into lzones
from lzone in lzones.Where(r => r.Id == c.ZoneId).DefaultIfEmpty()
join storetype in _context.StoreTypes on c.StoreTypeId equals storetype.Id into lstoretypes
from lstoretype in lstoretypes.Where(r => r.Id == c.StoreTypeId).DefaultIfEmpty()
join gasbrand in _context.GasBrands on c.GasBrandId equals gasbrand.Id into lgasbrands
from lgasbrand in lgasbrands.Where(r => r.Id == c.GasBrandId).DefaultIfEmpty()
join propanebrand in _context.PropaneBrands on c.PropaneBrandId equals propanebrand.Id into lpropandbrands
from lpropanebrand in lpropandbrands.Where(r => r.Id == c.PropaneBrandId).DefaultIfEmpty()
join wasteservice in _context.WasteServices on c.WasteServiceId equals wasteservice.Id into lwasteservices
from lwasteservice in lwasteservices.Where(r => r.Id == c.WasteServiceId).DefaultIfEmpty()
join donationbox in _context.YesNos on c.DonationBoxId equals donationbox.Id into ldonationboxes
from ldonationbox in ldonationboxes.Where(r => r.Id == c.DonationBoxId).DefaultIfEmpty()
join pumptoppersize in _context.PumpTopperSizes on c.PumpTopperSizeId equals pumptoppersize.Id into lpumptoppersizes
from lpumptopper in lpumptoppersizes.Where(r => r.Id == c.PumpTopperSizeId).DefaultIfEmpty()
join fountainbrand in _context.FountainMachines on c.FountainMachineId equals fountainbrand.Id into lfountainbrands
from lfountainbrand in lfountainbrands.Where(r => r.Id == c.FountainMachineId).DefaultIfEmpty()
join coffee in _context.Coffees on c.CoffeeId equals coffee.Id into lcoffees
from lcoffee in lcoffees.Where(r => r.Id == c.CoffeeId).DefaultIfEmpty()
join slushy in _context.Slushies on c.SlushyId equals slushy.Id into lslushies
from lslushy in lslushies.Where(r => r.Id == c.SlushyId).DefaultIfEmpty()
join milk in _context.Milks on c.MilkId equals milk.Id into lmilks
from lmilk in lmilks.Where(r => r.Id == c.MilkId).DefaultIfEmpty()
join storeenrolledinmilkprogram in _context.YesNos on c.StoreEnrolledInMilkProgram equals storeenrolledinmilkprogram.Id into lstoreenrolledinmilkprograms
from lstoreenrolledinmilkprogram in lstoreenrolledinmilkprograms.Where(r => r.Id == c.StoreEnrolledInMilkProgram).DefaultIfEmpty()
join insurancecheck in _context.InsuranceCheck on c.InsuranceCheckId equals insurancecheck.Id into linsurancechecks
from linsurancecheck in linsurancechecks.Where(r => r.Id == c.InsuranceCheckId).DefaultIfEmpty()
join rtcacct in _context.YesNos on c.RtcAcct equals rtcacct.Id into lrtcaccts
from lrtcacct in lrtcaccts.Where(r => r.Id == c.RtcAcct).DefaultIfEmpty()
join atmoption in _context.AtmOptions on c.AtmOptionId equals atmoption.Id into latmoptions
from latmoption in latmoptions.Where(r => r.Id == c.AtmOptionId).DefaultIfEmpty()
select new CustomerViewModel()
{
Id = c.Id,
MemberID = c.MemberCode,
FirstName = c.FirstName,
LastName = c.LastName,
OwnerName = c.OwnerName,
ContactPerson = c.ContactPerson,
CorporateName = c.CorpName,
BusinessName = c.BName,
BusinessAddress = c.BAddress,
BusinessCity = c.BCity,
BusinessState = lBState == null ? string.Empty : lBState.StateCode,
BusinessZipCode = c.BZip,
BusinessPhone = c.BPhone,
BusinessCellphone = c.BCellphone,
MailingAddress = c.MAddress,
MailingCity = c.MCity,
MailingState = LMState == null ? string.Empty : LMState.StateCode,
MailingZip = c.MZip,
Fax = c.Fax,
Email = c.Email,
SalesTaxCode = c.SalesTaxCode,
FederalTaxCode = c.FederalTaxCode,
Zone = lzone == null ? string.Empty : lzone.Zone,
District = c.District,
Region = lregion == null ? string.Empty : lregion.RegionNumber,
ZoneManager = lu == null ? string.Empty : lu.FirstName,
DateInactive = c.DateInactive == null ? c.DateInactive : c.DateInactive.Value.Date,
InactiveReason = c.InactiveReason,
DateJoined = c.DateJoined == null ? c.DateJoined : c.DateJoined.Value.Date,
SignedBy = c.SignedBy,
Comments = c.Comments,
Status = lstatus == null ? string.Empty : lstatus.Status,
StoreType = lstoretype == null ? string.Empty : lstoretype.Type,
GasBrand = lgasbrand == null ? string.Empty : lgasbrand.BrandName,
NoOfMpd = c.NoOfMpd,
PropaneBrand = lpropanebrand == null ? string.Empty : lpropanebrand.BrandName,
WasteService = lwasteservice == null ? string.Empty : lwasteservice.Name,
DonationBox = ldonationbox == null ? string.Empty : ldonationbox.Confirm,
BannerSize = c.BannerSize,
PumpTopperSize = lpumptopper == null ? string.Empty : lpumptopper.Size,
NoOfPumpTopper = c.NoOfPumpTopper,
HotFood = c.HotFood,
FountainMachine = lfountainbrand == null ? string.Empty : lfountainbrand.BrandName,
NoOfNozzels = c.NoOfNozzels,
NoOfCokeNozzels = c.NoOfCokeNozzels,
NoOfDrPepperNozzels = c.NoOfDrPepperNozzels,
NoOfPepsiNozzels = c.NoOfPepsiNozzels,
Coffee = lcoffee == null ? string.Empty : lcoffee.BrandName,
Slushy = lslushy == null ? string.Empty : lslushy.BrandName,
NoOfColdVault = c.NoOfColdVault,
NoOfCarbDoors = c.NoOfCarbDoors,
NoOfNonCarbDoors = c.NoOfNonCarbDoors,
NoOfStoreOptions = c.NoOfStoreOptions,
NoOfBeers = c.NoOfBeers,
NoOfCarbShelves = c.NoOfCarbShelves,
NoOfNonCarbShelves = c.NoOfNonCarbShelves,
NoOfCokeNonCarbs = c.NoOfCokeNonCarbs,
NoOfRedbullShelves = c.NoOfRedbullShelves,
NoOfMonsterShelves = c.NoOfMonsterShelves,
NoOfGatoradeShelves = c.NoOfGatoradeShelves,
NoOfArizonaFacings = c.NoOfArizonaFacings,
NoOfNesquickFacings = c.NoOfNesquickFacings,
NoOfAquafinaFacings = c.NoOfAquafinaFacings,
NoOfWildCatFacings = c.NoOfWildCatFacings,
NoOfJarritosFacings = c.NoOfJarritosFacings,
NoOfBangFacings = c.NoOfBangFacings,
NoOfXyiencFacings = c.NoOfXyiencFacings,
NoOfBodyArmourFacings = c.NoOfBodyArmourFacings,
ShelvesInDoor = c.ShelvesInDoor,
Milk = lmilk == null ? string.Empty : lmilk.BrandName,
NoOfMilkShelves = c.NoOfMilkShelves,
StoreEnrolledInMilkProgram = lstoreenrolledinmilkprogram == null ? string.Empty : lstoreenrolledinmilkprogram.Confirm,
InsuranceCheck = linsurancecheck == null ? string.Empty : linsurancecheck.Option,
InsuranceDate = c.InsuranceDate == null ? c.InsuranceDate : c.InsuranceDate.Value.Date,
RtcAcct = lrtcacct == null ? string.Empty : lrtcacct.Confirm,
Electricity = c.Electricity,
AtmOption = latmoption == null ? string.Empty : latmoption.Option,
AtmProcessor = c.AtmProcessor,
GasJobber = c.GasJobber,
StoreSquareFootage = c.StoreSquareFootage,
IceCream = c.IceCream,
NoOfPepsiNonCarbs = c.NoOfPepsiNonCarbs,
NoOfDrPepperNonCarbs = c.NoOfDrPepperNonCarbs,
NoOfStoreOptionShelves = c.NoOfStoreOptionShelves,
Wholesalers = c.Wholesalers,
NoOfPosters = c.NoOfPosters,
PosterSizes = c.PosterSizes,
Fritolay = c.Fritolay,
OtherSnacks = c.OtherSnacks,
StandingCooler = c.StandingCooler,
CounterTopCooler = c.CounterTopCooler,
MidSizeCooler = c.MidSizeCooler,
Created = c.Created == null ? c.Created : c.Created.Value.Date,
Createdby = c.Createdby,
Modified = c.Modified == null ? c.Modified : c.Modified.Value.Date,
Modifiedby = c.Modifiedby,
CoolerModified = c.CoolerModified == null ? c.CoolerModified : c.CoolerModified.Value.Date,
SpacePaymentEligible = c.SpacePaymentEligible
};
# endregion
return results.AsNoTracking().ToList();
Here is the query it generates in output window.
SELECT "c"."id", "c"."member_code" AS "MemberID", "c"."first_name" AS "FirstName", "c"."last_name" AS "LastName", "c"."owner_name" AS "OwnerName", "c"."contact_person" AS "ContactPerson", "c"."corp_name" AS "CorporateName", "c"."b_name" AS "BusinessName", "c"."b_address" AS "BusinessAddress", "c"."b_city" AS "BusinessCity", CASE
WHEN "t"."id" IS NULL
THEN '' ELSE "t"."state_code"
END AS "BusinessState", "c"."b_zip" AS "BusinessZipCode", "c"."b_phone" AS "BusinessPhone", "c"."b_cellphone" AS "BusinessCellphone", "c"."m_address" AS "MailingAddress", "c"."m_city" AS "MailingCity", CASE
WHEN "t0"."id" IS NULL
THEN '' ELSE "t0"."state_code"
END AS "MailingState", "c"."m_zip" AS "MailingZip", "c"."fax", "c"."email", "c"."sales_tax_code" AS "SalesTaxCode", "c"."federal_tax_code" AS "FederalTaxCode", CASE
WHEN "t4"."id" IS NULL
THEN '' ELSE "t4"."zone"
END AS "Zone", "c"."district", CASE
WHEN "t3"."id" IS NULL
THEN '' ELSE "t3"."region_number"
END AS "Region", CASE
WHEN "t2"."id" IS NULL
THEN '' ELSE "t2"."first_name"
END AS "ZoneManager", CASE
WHEN "c"."date_inactive" IS NULL
THEN CAST(1 AS BIT) ELSE CAST(0 AS BIT)
END, "c"."date_inactive", "c"."date_inactive", "c"."InactiveReason", CASE
WHEN "c"."date_joined" IS NULL
THEN CAST(1 AS BIT) ELSE CAST(0 AS BIT)
END, "c"."date_joined", "c"."date_joined", "c"."SignedBy", "c"."comments", CASE
WHEN "t1"."id" IS NULL
THEN '' ELSE "t1"."status"
END AS "Status", CASE
WHEN "t5"."id" IS NULL
THEN '' ELSE "t5"."type"
END AS "StoreType", CASE
WHEN "t6"."id" IS NULL
THEN '' ELSE "t6"."brand_name"
END AS "GasBrand", "c"."no_of_mpd" AS "NoOfMpd", CASE
WHEN "t7"."id" IS NULL
THEN '' ELSE "t7"."brand_name"
END AS "PropaneBrand", CASE
WHEN "t8"."id" IS NULL
THEN '' ELSE "t8"."name"
END AS "WasteService", CASE
WHEN "t9"."id" IS NULL
THEN '' ELSE "t9"."confirm"
END AS "DonationBox", "c"."banner_size" AS "BannerSize", CASE
WHEN "t10"."id" IS NULL
THEN '' ELSE "t10"."size"
END AS "PumpTopperSize", "c"."no_of_pump_topper" AS "NoOfPumpTopper", "c"."hot_food" AS "HotFood", CASE
WHEN "t11"."id" IS NULL
THEN '' ELSE "t11"."brand_name"
END AS "FountainMachine", "c"."no_of_nozzels" AS "NoOfNozzels", "c"."no_of_coke_nozzels" AS "NoOfCokeNozzels", "c"."no_of_dr_pepper_nozzels" AS "NoOfDrPepperNozzels", "c"."no_of_pepsi_nozzels" AS "NoOfPepsiNozzels", CASE
WHEN "t12"."id" IS NULL
THEN '' ELSE "t12"."brand_name"
END AS "Coffee", CASE
WHEN "t13"."id" IS NULL
THEN '' ELSE "t13"."brand_name"
END AS "Slushy", "c"."no_of_cold_vault" AS "NoOfColdVault", "c"."no_of_carb_doors" AS "NoOfCarbDoors", "c"."no_of_non_carb_doors" AS "NoOfNonCarbDoors", CAST("c"."no_of_store_options" AS TEXT) AS "NoOfStoreOptions", "c"."no_of_beers" AS "NoOfBeers", "c"."no_of_carb_shelves" AS "NoOfCarbShelves", "c"."no_of_non_carb_shelves" AS "NoOfNonCarbShelves", "c"."no_of_coke_non_carbs" AS "NoOfCokeNonCarbs", "c"."no_of_redbull_shelves" AS "NoOfRedbullShelves", "c"."no_of_monster_shelves" AS "NoOfMonsterShelves", "c"."no_of_gatorade_shelves" AS "NoOfGatoradeShelves", "c"."no_of_arizona_facings" AS "NoOfArizonaFacings", "c"."no_of_nesquick_facings" AS "NoOfNesquickFacings", "c"."no_of_aquafina_facings" AS "NoOfAquafinaFacings", "c"."no_of_wild_cat_facings" AS "NoOfWildCatFacings", "c"."no_of_jarritos_facings" AS "NoOfJarritosFacings", "c"."no_of_bang_facings" AS "NoOfBangFacings", "c"."no_of_xyienc_facings" AS "NoOfXyiencFacings", "c"."no_of_body_armour_facings" AS "NoOfBodyArmourFacings", "c"."shelves_in_door" AS "ShelvesInDoor", CASE
WHEN "t14"."id" IS NULL
THEN '' ELSE "t14"."brand_name"
END AS "Milk", "c"."no_of_milk_shelves" AS "NoOfMilkShelves", CASE
WHEN "t15"."id" IS NULL
THEN '' ELSE "t15"."confirm"
END AS "StoreEnrolledInMilkProgram", CASE
WHEN "t16"."id" IS NULL
THEN '' ELSE "t16"."option"
END AS "InsuranceCheck", CASE
WHEN "c"."insurance_date" IS NULL
THEN CAST(1 AS BIT) ELSE CAST(0 AS BIT)
END, "c"."insurance_date", "c"."insurance_date", CASE
WHEN "t17"."id" IS NULL
THEN '' ELSE "t17"."confirm"
END AS "RtcAcct", "c"."electricity", CASE
WHEN "t18"."id" IS NULL
THEN '' ELSE "t18"."option"
END AS "AtmOption", "c"."atm_processor" AS "AtmProcessor", "c"."gas_jobber" AS "GasJobber", "c"."store_square_footage" AS "StoreSquareFootage", "c"."ice_cream" AS "IceCream", "c"."no_of_pepsi_non_carbs" AS "NoOfPepsiNonCarbs", "c"."no_of_dr_pepper_non_carbs" AS "NoOfDrPepperNonCarbs", "c"."no_of_store_option_shelves" AS "NoOfStoreOptionShelves", "c"."wholesalers", "c"."no_of_posters" AS "NoOfPosters", "c"."poster_sizes" AS "PosterSizes", "c"."fritolay", "c"."other_snacks" AS "OtherSnacks", "c"."standing_cooler" AS "StandingCooler", "c"."counter_top_cooler" AS "CounterTopCooler", "c"."mid_size_cooler" AS "MidSizeCooler", CASE
WHEN "c"."created" IS NULL
THEN CAST(1 AS BIT) ELSE CAST(0 AS BIT)
END, "c"."created", "c"."created", "c"."createdby", CASE
WHEN "c"."modified" IS NULL
THEN CAST(1 AS BIT) ELSE CAST(0 AS BIT)
END, "c"."modified", "c"."modified", "c"."modifiedby", CASE
WHEN "c"."cooler_modified" IS NULL
THEN CAST(1 AS BIT) ELSE CAST(0 AS BIT)
END, "c"."cooler_modified", "c"."cooler_modified", "c"."SpacePaymentEligible"
FROM "customers" AS "c"
LEFT JOIN (
SELECT "BState".*
FROM "states" AS "BState"
WHERE "BState"."id" = "c"."state_id"
) AS "t" ON "c"."state_id" = "t"."id"
LEFT JOIN (
SELECT "MState".*
FROM "states" AS "MState"
WHERE "MState"."id" = "c"."m_state_id"
) AS "t0" ON "c"."m_state_id" = "t0"."id"
LEFT JOIN (
SELECT "status".*
FROM "statuses" AS "status"
WHERE "status"."id" = "c"."status_id"
) AS "t1" ON "c"."status_id" = "t1"."id"
LEFT JOIN (
SELECT "u".*
FROM "employees" AS "u"
WHERE "u"."id" = "c"."zone_manager"
) AS "t2" ON "c"."zone_manager" = "t2"."id"
LEFT JOIN (
SELECT "regions".*
FROM "regions" AS "regions"
WHERE "regions"."id" = "c"."id"
) AS "t3" ON "c"."region_id" = "t3"."id"
LEFT JOIN (
SELECT "zone".*
FROM "zones" AS "zone"
WHERE "zone"."id" = "c"."zone_id"
) AS "t4" ON "c"."zone_id" = "t4"."id"
LEFT JOIN (
SELECT "storetype".*
FROM "store_types" AS "storetype"
WHERE "storetype"."id" = "c"."store_type_id"
) AS "t5" ON "c"."store_type_id" = "t5"."id"
LEFT JOIN (
SELECT "gasbrand".*
FROM "gas_brands" AS "gasbrand"
WHERE "gasbrand"."id" = "c"."gas_brand_id"
) AS "t6" ON "c"."gas_brand_id" = "t6"."id"
LEFT JOIN (
SELECT "propanebrand".*
FROM "propane_brands" AS "propanebrand"
WHERE "propanebrand"."id" = "c"."propane_brand_id"
) AS "t7" ON "c"."propane_brand_id" = "t7"."id"
LEFT JOIN (
SELECT "wasteservice".*
FROM "waste_services" AS "wasteservice"
WHERE "wasteservice"."id" = "c"."waste_service_id"
) AS "t8" ON "c"."waste_service_id" = "t8"."id"
LEFT JOIN (
SELECT "donationbox".*
FROM "yes_nos" AS "donationbox"
WHERE "donationbox"."id" = "c"."donation_box_id"
) AS "t9" ON "c"."donation_box_id" = "t9"."id"
LEFT JOIN (
SELECT "pumptoppersize".*
FROM "pump_topper_sizes" AS "pumptoppersize"
WHERE "pumptoppersize"."id" = "c"."pump_topper_size_id"
) AS "t10" ON "c"."pump_topper_size_id" = "t10"."id"
LEFT JOIN (
SELECT "fountainbrand".*
FROM "fountain_machines" AS "fountainbrand"
WHERE "fountainbrand"."id" = "c"."fountain_machine_id"
) AS "t11" ON "c"."fountain_machine_id" = "t11"."id"
LEFT JOIN (
SELECT "coffee".*
FROM "coffees" AS "coffee"
WHERE "coffee"."id" = "c"."coffee_id"
) AS "t12" ON "c"."coffee_id" = "t12"."id"
LEFT JOIN (
SELECT "slushy".*
FROM "slushies" AS "slushy"
WHERE "slushy"."id" = "c"."slushy_id"
) AS "t13" ON "c"."slushy_id" = "t13"."id"
LEFT JOIN (
SELECT "milk".*
FROM "milks" AS "milk"
WHERE "milk"."id" = "c"."milk_id"
) AS "t14" ON "c"."milk_id" = "t14"."id"
LEFT JOIN (
SELECT "storeenrolledinmilkprogram".*
FROM "yes_nos" AS "storeenrolledinmilkprogram"
WHERE "storeenrolledinmilkprogram"."id" = "c"."store_enrolled_in_milk_program"
) AS "t15" ON "c"."store_enrolled_in_milk_program" = "t15"."id"
LEFT JOIN (
SELECT "insurancecheck".*
FROM "insurance_check" AS "insurancecheck"
WHERE "insurancecheck"."id" = "c"."insurance_check_id"
) AS "t16" ON "c"."insurance_check_id" = "t16"."id"
LEFT JOIN (
SELECT "rtcacct".*
FROM "yes_nos" AS "rtcacct"
WHERE "rtcacct"."id" = "c"."rtc_acct"
) AS "t17" ON "c"."rtc_acct" = "t17"."id"
LEFT JOIN (
SELECT "atmoption".*
FROM "atm_options" AS "atmoption"
WHERE "atmoption"."id" = "c"."atm_option_id"
) AS "t18" ON "c"."atm_option_id" = "t18"."id"
Further technical details
EF Core version: 2.1.4
Database Provider: Microsoft.EntityFrameworkCore.Sqlite
Operating system: Windows 10
IDE: Visual Studio 2017 15.4
(1) By Gwendal Roué (groue) on 2020-04-19 12:41:17
[source]
Hello,
I’m working on a SQL generator that wants to «flatten» a to-many relation backed by a foreign key into a to-one relation by focusing on a single row in the associated rows.
Sometimes examples make things more clear. Let’s say we want to fetch «all chats along with their latest post», given the following schema:
CREATE TABLE chat (
id INTEGER PRIMARY KEY
);
CREATE TABLE post (
id INTEGER PRIMARY KEY,
chatId INTEGER REFERENCES chat(id),
date DATETIME
);
In my experiments looking for a SQL pattern that provides correct results, while remaining composable (I’m not looking for the most efficient SQL, and generally speaking I trust the SQLite query planner), I stumbled upon a surprising behavior with SQLite version 3.28.0.
This query works:
-- Success
SELECT chat.*, post.*
FROM chat
JOIN post ON post.id = (SELECT id FROM post WHERE post.id = chat.id ORDER BY date DESC LIMIT 1);
This one fails with a «no such column» error:
-- Error: no such column: chat.id
SELECT chat.*, post.*
FROM chat
JOIN (SELECT * FROM post WHERE post.id = chat.id ORDER BY date DESC LIMIT 1) post;
I have two questions, if someone can help. The first is the most important to me. The second is just a request for clarification:
- Can I rely on the fact that the first query will never give such an error in the future?
- Why can some subqueries refer to outer tables, and some others can not?
(2) By Neal (_Neal_) on 2020-04-19 14:25:17
in reply to 1
[link]
[source]
Columns from both of the join operands can be referenced in ON clause — thus your first query works.
But your second query is trying to refer to first join operand’s column in second join operand — that is not allowed.
(3) By Ryan Smith (cuz) on 2020-04-19 16:04:35
in reply to 1
[link]
[source]
- Yes the first query will always work,
but - That is not the problem with query 2 — The SELECT is very happy, it’s the JOIN that is the problem because you are joining to an as-yet non existing table/view (which the join creates) but then trying to refer to chat.id in that second join-created-view, however «chat» never appears in THAT join’s SELECT .. FROM, and you cannot magically grab it from some other JOIN you did earlier.
The JOINS are not the same and won’t be in the same For-Loop at the same row/position at the same moment — that only works for correlated sub-queries, which your JOIN is not.
Perhaps read up on Correlated sub-queries if what I say did not make immediate sense, once you understand the limitations of those, it should become more clear why your JOIN did not behave as expected, and how you could make that sort of thing work.
Good luck!
(4) By Gwendal Roué (groue) on 2020-04-19 16:33:28
in reply to 3
[link]
[source]
Perhaps read up on Correlated sub-queries if what I say did not make immediate sense
I’ll surely do! Thank you Ryan for hinting me at a better understanding of what I’m trying to achieve. That’s very well appreciated, and exactly what I was hoping for, even if I was too shy to ask for it 🙂
(5.1) By Gwendal Roué (groue) on 2020-04-19 17:01:01 edited from 5.0
in reply to 3
[link]
[source]
Perhaps read up on Correlated sub-queries if what I say did not make immediate sense
So I did. A correlated sub query executes as if it runs once for each row in the outer query.
But «executes as if» and «actually executes» are not always the same. The database is allowed to perform whatever optimization it wants, as long as the observed behavior matches the expected one.
So does the working query below exhibits a quadratic behavior?
CREATE TABLE chat (
id INTEGER PRIMARY KEY
);
CREATE TABLE post (
id INTEGER PRIMARY KEY,
chatId INTEGER REFERENCES chat(id),
date DATETIME
);
CREATE INDEX post_chatId ON post(chatId);
EXPLAIN QUERY PLAN
SELECT chat.*, post.*
FROM chat
JOIN post ON post.id = (SELECT id FROM post WHERE post.id = chat.id ORDER BY date DESC LIMIT 1);
I’m not quite used to query planning interpretation yet:
QUERY PLAN
|--SCAN TABLE chat
|--SEARCH TABLE post USING INTEGER PRIMARY KEY (rowid=?)
`--CORRELATED SCALAR SUBQUERY 1
`--SEARCH TABLE post USING INTEGER PRIMARY KEY (rowid=?)
(6) By Ryan Smith (cuz) on 2020-04-20 01:03:49
in reply to 5.1
[link]
[source]
Please forgive me, I may not be 100% following your question, and it’s very late, but I’ll give it a try.
Firstly, I don’t know what «exhibits quadratic behavior» means in this case, but I will assume you are asking «will the QP do a Cartesian join where it steps ALL the «FROM rows» (chat) times ALL the JOINed rows (post). [aka O(n^2)]
That answer is no, even though it seems like it would have to.
The Query you propose (for which the Query plan is given) seemingly will start by walking (iterating through) the entire chat table (because you offer no filter in a WHERE clause or such).
Now note that the QP is free to choose to start with the joined table if it pleases, but won’t in this case as reported by the query plan.
It then will simply look up the correlated value using the PK (rowid) of the «post» table, causing only O(log n) look-ups. Note that your ORDER BY and DESC LIMIT 1 statements has no effect and is completely ignored here because the link is to a rowid (or stated otherwise, the unique primary key) so row number N can only ever be row number N, no matter how it is «ordered» or limited, in any lookup.
i.e. — this should have the same query plan:
EXPLAIN QUERY PLAN
SELECT chat.*, post.*
FROM chat
JOIN post ON post.id = (SELECT id FROM post WHERE post.id = chat.id);
This may well NOT be the case for other types of correlated sub-query which perhaps do not use the primary key. In that case, the order-by would help to make the QP construct a temporary table with temporary index to do the look-ups. It is hard to guarantee though, and best practice when doing correlated sub-queries is to ensure you have Indexes on the looked-up fields.
I find it best to think of the correlated sub-query as a form of Table/View, and then asking myself if I can see a clear relationship between the tables and how I can phrase the query so the Query-planner cannot miss that same relationship. (And then test it with EXPLAIN of course).
Another way of doing the above would be with a Common Table Expression (CTE — more good reading material) which is like creating a view inside your query which may look something like this:
WITH PD AS (
SELECT id FROM post
)
SELECT chat.*, PD.*
FROM chat
JOIN PD ON PD.id = chat.id;
which is roughly equivalent to:
SELECT chat.*, PD.*
FROM chat
JOIN (SELECT id FROM post) AS PD ON PD.id = chat.id;
but with the advantage that multiple CTEs can be added and that they can refer each other in their respective SELECT clauses.
This is all tricky, because the Query Planner can decide, based on many whims, how it wants to traverse query tables. It may be that your Query suddenly switches to another plan after adding some data and doing ANALYZE.
The best is to test with some real-World data.
Hope that helps and best of luck!
(7) By Keith Medcalf (kmedcalf) on 2020-04-20 02:24:44
in reply to 5.1
[link]
[source]
The performance will be, at best case, O(N*M^2) where N is the number of rows in chat and M is the number of rows in post. It will probably degrade a lot faster than that. We recently discussed that in another thread.
You appear to be trying to find the «earliest post» for each «thread». Note the following comments:
-
the chat table is useless when querying data as it does nothing except waste time and you can tell this because
(a) there is nothing from that table in the select list; and,
(b) removing all reference to it does not affect the query result in any way. -
(chatid, date) must be a candidate key for the post table (that is, it must be unique). If it is not unique then the concept of top and bottom of a chatid cannot use that field and some other tiebreaker must be used. If some other tiebreaker must be used, just use that directly and forget about the added complication of adding something useless.
So, you want the following:
create table chat
(
id integer primary key
);
create table post
(
id integer primary key,
chatid integer not null references chat(id),
date text,
unique (chatid, date)
);
with mins (chatid, mindate)
as (
select chatid,
min(date)
from post
group by chatid
)
select post.*
from mins, post
where mins.chatid == post.chatid
and mins.mindate == post.date;
(8.1) By Gwendal Roué (groue) on 2020-04-20 07:37:48 edited from 8.0
in reply to 7
[link]
[source]
The performance will be, at best case, O(N*M^2) where N is the number of rows in chat and M is the number of rows in post. It will probably degrade a lot faster than that.
All right. Thank you very much for pointing this out! I can’t use this technique, then, because I won’t ship a library which generates SQL with such a bad complexity.
We recently discussed that in another thread.
I’d be happy to check this conversation.
You appear to be trying to find the «earliest post» for each «thread».
Actually, the example wants to load both thread («chat») and latest post. Imagine you want to feed the main screen of a chat app, with all conversations and a preview of the last message.
the chat table is useless when querying data as it does nothing except waste time and you can tell this because
(a) there is nothing from that table in the select list; and,
(b) removing all reference to it does not affect the query result in any way.
So, the point is actually to get chat information along: its a SELECT chat.*, post.*
(chatid, date) must be a candidate key for the post table (that is, it must be unique).
I really appreciate your help. This is not an acceptable constraint, in the general case, unfortunately:
In the chat/post example, we can not require unique post dates in a thread. This would exclude a whole set of reasonable and real use cases. If the user wants to disambiguate the «last post», the user would would have to sort per date and another disambiguation column (such as the primary key). If the user does not perform such disambiguation, then any post can be chosen (meaning I can add the primary key as an extra disambiguation ordering clause in the SQL I generate).
The chat/post example is just one example, for illustration purpose. My general case is the following:
Input:
- one «parent» table
- one «child» table
- a foreign key from child to parent
- a set of ordering clauses on the child table, which identifies one child per parent. The ordering clauses may not identify a single child per parent. The set of ordering clauses may actually be empty.
Ouput:
- An SQL query
- which selects (
parent.*, child.*) pairs, - where the child is the first child of each parent according by the set of ordering clauses (any child is ok if the ordering clauses are ambiguous)
- where the child may actually be missing (if a parent has no child), in a similar fashion to LEFT JOIN
- of acceptable complexity
- which selects (
- Advice for indexes in order to get the desired complexity.
Of course, if this general case is too relaxed, and does not satisfy the requirements for such a desired output, then I’ll have to adapt, and maybe distinguish more cases. For example, unique indexes, if acceptable by the user, can be used. But I have to deal with the fact that users may comes with a «weak» setup. Yet I do not want to punish users who have a «strong» setup with an SQL query which has a bad O(…) complexity.
It’s a difficult exercice, but I hope I can find out a solution 🙂
Edit: if there is no general solution to the general problem as I describe it, then I may have to provide an advice such as «in such situation, setup an extra foreign key to the desired child, and consider using triggers so that it get automatically updated whenever the child table is modified». That would be an acceptable solution also, even if much less easy for the users to setup. I consider the «all chats with their latest post» and all similar queries to be an important use case to address, and I want to help users who do not want to write their SQL themselves (because they don’t want to, or because they can’t).
(9) By Gwendal Roué (groue) on 2020-04-20 16:13:36
in reply to 7
[link]
[source]
Keith, you have provided excellent hints, which drove me to a solution that sounds quite general and satisfying.
It uses window functions, so it requires SQLite 3.28+.
It looks like it has a good complexity, as I see by running the request on datasets of various sizes. For a constant number of posts per chats, it is linear with the number of chats. For a constant number of chats, it is linear with the number of posts per chats. This sounds quite acceptable to me, as it fits well a kind of database that is quite common, in the wild.
It is general enough, because it accepts the input I have:
- one parent table (here, «chat»)
- one child table (here, «post»)
- a foreign key from child to parent
- a set of ordering clauses on the child table, which identifies one child per parent. The ordering clauses may not identify a single child per parent. The set of ordering clauses may actually be empty.
So, the SQL:
WITH latestPost AS (
SELECT chatId, id AS postId FROM (
SELECT chatId, id, RANK() OVER (
PARTITION BY chatId
ORDER BY
-- Any number of user-provided ordering clauses
date DESC,
-- When user-provided ordering clauses are not unique,
-- append a primary key ordering for disambiguation
-- and guarantee that there is a single row with rank 1.
id)
AS _rank
FROM post)
WHERE _rank = 1
)
SELECT chat.*, post.*
FROM chat
LEFT JOIN latestPost ON latestPost.chatId = chat.id
LEFT JOIN post ON post.id = latestPost.postId;
The query plan:
QUERY PLAN
|--MATERIALIZE 1
| |--CO-ROUTINE 4
| | |--SCAN TABLE post USING COVERING INDEX post_index
| | `--USE TEMP B-TREE FOR RIGHT PART OF ORDER BY
| `--SCAN SUBQUERY 4
|--SCAN TABLE chat
|--SEARCH SUBQUERY 1 USING AUTOMATIC COVERING INDEX (_rank=? AND chatId=?)
`--SEARCH TABLE post USING INTEGER PRIMARY KEY (rowid=?)