Sql logic error row value misused

SQLite Forum

(1) By beetlejuice (coleifer) on 2021-07-05 18:08:59 [link] [source]

I was hoping someone could help me understand the rules around using row values and the VALUES keyword. I am having a little trouble understanding what is and isn’t allowed.

Starting with a simple table containing a key, value:

The following works with Sqlite:

The following seems to work in Postgres, but Sqlite is reporting «row value misused»:

The following works in both Postgres and Sqlite:

Another thing I have seen done is to join on a list of values, which works well in Postgres, but Sqlite reports a syntax error near «(«:

The following, unsurprising, also works in Postgres but not Sqlite:

Moving it into a CTE works in both Postgres and Sqlite:

My questions are:

• When should one use plain row-values versus VALUES?
• Why does (key, val) = (‘k1’, 1) work but (key, val) IN ((‘k1’, 1). ) not?
• Are there ways of constructing the above type of query which would be better or more idiomatic with Sqlite?

Thank you in advance for the help.

(2) By Harald Hanche-Olsen (hanche) on 2021-07-06 13:31:03 in reply to 1 [source]

I am not an expert or authority, but here is my understanding of the issue, as far as it goes. If I am wildly off target, someone will hopefully correct me.

• A comma-separated list of expressions in parenthesis usually signifies a row value, or tuple.
• A VALUES expression is a special select statement, and as such produces a sequence of rows. Even if that sequence contains precisely one row, it is something different from a row value.
• A row value can contain any of the datatypes allowed by SQLite. Note that a row value is not among these; hence ((1,2,),(3,4)) is illegal.

The following seems to work in Postgres, but Sqlite is reporting «row value misused»:

That is not so clear-cut. What’s on the right side of WHERE … IN (…) is not an expression but a parenthesised list of expressions, so arguably, a list of row values could have been legitimate according to these rules. Perhaps it would be useful; but OTOH, the alternative to a list of expressions is a select statement, and VALUES is of course a select statement, so that will always work.

Another thing I have seen done is to join on a list of values, which works well in Postgres, but Sqlite reports a syntax error near «(«:

I am not sure why that does not work in SQLite. The syntax diagrams seem to allow it.

(3) By mzm2021 on 2021-07-06 14:13:41 in reply to 2 [link] [source]

SQLite does not accept temporary column names in a table/view alias.

That’s why it is reporting a syntax error near the left parenthesis of:

PostgresSQL docs describe aliases with column names in:

But the SQLite syntax diagram of the FROM clause does not allow such a syntax:

(4) By Harald Hanche-Olsen (hanche) on 2021-07-06 15:27:55 in reply to 3 [link] [source]

Ah, that explains it.

I do wish for more context in SQLite error messages sometimes. I mean, just adding a few characters, say “ near «(«k»,» ” would be a lot more helpful than “ near «(» ”.

Источник

Sql logic error row value misused

Asked by:

Question

This should be straight forward but apparently its not. I am simply trying to get a list from within an id list in a database table. _connection is already instantiated

public Task
> GetTextInfosAsync(List idList) < return _connection.Table

The error message I get is «row value misused». What gives?

Is TextInfo.Id a list or a single int?

You maybe use variable type incorrectly.

Is the Id one field of model TextInfo ? And is it of type int ?

If yes, then you should pass a single int as the parameter of function GetTextInfosAsync ,just as follows:

public Task
> GetTextInfosAsync(int id) < return _connection.Table

Xamarin forums are migrating to a new home on Microsoft Q&A! We invite you to post new questions in the Xamarin forums’ new home on Microsoft Q&A! For more information, please refer to this sticky post.

@JoeManke said: Is TextInfo.Id a list or a single int?

Its a single int. Basically I am trying to get all the records that match the list of ids’ that i’m passing into the function. Maybe I should be using a join or something else.

@jezh said: You maybe use variable type incorrectly.

Is the Id one field of model TextInfo ? And is it of type int ?

If yes, then you should pass a single int as the parameter of function GetTextInfosAsync ,just as follows:

public Task
> GetTextInfosAsync(int id) < return _connection.Table

Xamarin forums are migrating to a new home on Microsoft Q&A! We invite you to post new questions in the Xamarin forums’ new home on Microsoft Q&A! For more information, please refer to this sticky post.

Is there no way to match with a list of associated ids?

A list will never be equal to an int. You want something like this:

@JoeManke said: A list will never be equal to an int. You want something like this:

I previously tried that, I still get the exact same error. This logic works with Entity Framework Core on my functions server app. With SQLite I cannot get .Equals or .Contains to work for some reason.

Have you tried to clean and rebuilt your app?

@jezh said: Have you tried to clean and rebuilt your app?

Yes. Here is an update to the situation — I have made a modification the code as per below. Here is whats mindboggling — the code below WORKS when performed against a list of type TextInfo, however it does NOT work when performed against SQL Lite. Now i’m getting a «One or more errors occurred. (Cannot compile: Lambda)» exception. SQL Lite can’t seem to handle this query but standard C# linq can.

// This works: public List

currentTexts) < var updatedList = currentTexts.Where(x =>!currentTexts.Select(s => s.TextId).Contains(x.TextId)).ToList(); return updatedList; >

//This does not work: public Task
> GetTextInfosAsync(List

Источник

SQLError: row value misused after upgrade #1921

I recently updated to 3.9.5 and I’m now experiencing the following errors with a query that was previously working:

The text was updated successfully, but these errors were encountered:

Try rather than using distinct as a function, to append a call to .distinct() at the end of the query.

If I go back to 3.6.4 and rerun the exact same sequence, it looks like 3.9.5 is putting parentheses around the column list that weren’t there before:

I’m using this with sqlite — apsw 3.24.0.post1

There’s some logic outside this block that’s preparing a query for self.vulnerabilties, but I am calling distinct() at the end of the query rather than as a function.

Can you share what self.vulnerabilities is?

I’m wondering if you’re re-selecting or somehow selecting a tuple instead of a list of columns?

In your example code you have hosts = . but that doesn’t appear to be in any way relevant to the error, right? I’m just not understanding why you included it.

For example, I’m trying to replicate this and cannot:

This came up during regression testing and may possibly be relevant, 2692e78 — but that being said, I cannot replicate the invalid SQL you are reporting.

Please provide more information or at least an example of how I can replicate this issue.

Sorry, I was trying to trim out some extra bits of non-peewee code and omitted the wrong bit. The full block is:

This is actually in a class method with a bunch of business logic in it that subclasses one of my Model classes, so self.vulnerabilities is a query against another table via foreign key backreference.

I’ll try with that commit, and if that does not fix it I’ll see if I can easily bisect and/or replicate.

Источник

SQLite Forum

(1) By beetlejuice (coleifer) on 2021-07-05 18:08:59 [link] [source]

I was hoping someone could help me understand the rules around using row values and the VALUES keyword. I am having a little trouble understanding what is and isn’t allowed.

Starting with a simple table containing a key, value:

The following works with Sqlite:

The following seems to work in Postgres, but Sqlite is reporting «row value misused»:

The following works in both Postgres and Sqlite:

Another thing I have seen done is to join on a list of values, which works well in Postgres, but Sqlite reports a syntax error near «(«:

The following, unsurprising, also works in Postgres but not Sqlite:

Moving it into a CTE works in both Postgres and Sqlite:

My questions are:

• When should one use plain row-values versus VALUES?
• Why does (key, val) = (‘k1’, 1) work but (key, val) IN ((‘k1’, 1). ) not?
• Are there ways of constructing the above type of query which would be better or more idiomatic with Sqlite?

Thank you in advance for the help.

(2) By Harald Hanche-Olsen (hanche) on 2021-07-06 13:31:03 in reply to 1 [link] [source]

I am not an expert or authority, but here is my understanding of the issue, as far as it goes. If I am wildly off target, someone will hopefully correct me.

• A comma-separated list of expressions in parenthesis usually signifies a row value, or tuple.
• A VALUES expression is a special select statement, and as such produces a sequence of rows. Even if that sequence contains precisely one row, it is something different from a row value.
• A row value can contain any of the datatypes allowed by SQLite. Note that a row value is not among these; hence ((1,2,),(3,4)) is illegal.

The following seems to work in Postgres, but Sqlite is reporting «row value misused»:

That is not so clear-cut. What’s on the right side of WHERE … IN (…) is not an expression but a parenthesised list of expressions, so arguably, a list of row values could have been legitimate according to these rules. Perhaps it would be useful; but OTOH, the alternative to a list of expressions is a select statement, and VALUES is of course a select statement, so that will always work.

Another thing I have seen done is to join on a list of values, which works well in Postgres, but Sqlite reports a syntax error near «(«:

I am not sure why that does not work in SQLite. The syntax diagrams seem to allow it.

(3) By mzm2021 on 2021-07-06 14:13:41 in reply to 2 [link] [source]

SQLite does not accept temporary column names in a table/view alias.

That’s why it is reporting a syntax error near the left parenthesis of:

PostgresSQL docs describe aliases with column names in:

But the SQLite syntax diagram of the FROM clause does not allow such a syntax:

(4) By Harald Hanche-Olsen (hanche) on 2021-07-06 15:27:55 in reply to 3 [source]

Ah, that explains it.

I do wish for more context in SQLite error messages sometimes. I mean, just adding a few characters, say “ near «(«k»,» ” would be a lot more helpful than “ near «(» ”.

Источник

SQLite Forum

(1) By beetlejuice (coleifer) on 2021-07-05 18:08:59 [link] [source]

I was hoping someone could help me understand the rules around using row values and the VALUES keyword. I am having a little trouble understanding what is and isn’t allowed.

Starting with a simple table containing a key, value:

The following works with Sqlite:

The following seems to work in Postgres, but Sqlite is reporting «row value misused»:

The following works in both Postgres and Sqlite:

Another thing I have seen done is to join on a list of values, which works well in Postgres, but Sqlite reports a syntax error near «(«:

The following, unsurprising, also works in Postgres but not Sqlite:

Moving it into a CTE works in both Postgres and Sqlite:

My questions are:

• When should one use plain row-values versus VALUES?
• Why does (key, val) = (‘k1’, 1) work but (key, val) IN ((‘k1’, 1). ) not?
• Are there ways of constructing the above type of query which would be better or more idiomatic with Sqlite?

Thank you in advance for the help.

(2) By Harald Hanche-Olsen (hanche) on 2021-07-06 13:31:03 in reply to 1 [link] [source]

I am not an expert or authority, but here is my understanding of the issue, as far as it goes. If I am wildly off target, someone will hopefully correct me.

• A comma-separated list of expressions in parenthesis usually signifies a row value, or tuple.
• A VALUES expression is a special select statement, and as such produces a sequence of rows. Even if that sequence contains precisely one row, it is something different from a row value.
• A row value can contain any of the datatypes allowed by SQLite. Note that a row value is not among these; hence ((1,2,),(3,4)) is illegal.

The following seems to work in Postgres, but Sqlite is reporting «row value misused»:

That is not so clear-cut. What’s on the right side of WHERE … IN (…) is not an expression but a parenthesised list of expressions, so arguably, a list of row values could have been legitimate according to these rules. Perhaps it would be useful; but OTOH, the alternative to a list of expressions is a select statement, and VALUES is of course a select statement, so that will always work.

Another thing I have seen done is to join on a list of values, which works well in Postgres, but Sqlite reports a syntax error near «(«:

I am not sure why that does not work in SQLite. The syntax diagrams seem to allow it.

(3) By mzm2021 on 2021-07-06 14:13:41 in reply to 2 [link] [source]

SQLite does not accept temporary column names in a table/view alias.

That’s why it is reporting a syntax error near the left parenthesis of:

PostgresSQL docs describe aliases with column names in:

But the SQLite syntax diagram of the FROM clause does not allow such a syntax:

(4) By Harald Hanche-Olsen (hanche) on 2021-07-06 15:27:55 in reply to 3 [source]

Ah, that explains it.

I do wish for more context in SQLite error messages sometimes. I mean, just adding a few characters, say “ near «(«k»,» ” would be a lot more helpful than “ near «(» ”.

Источник

Hi, I’m getting this messages when I try to get_or_create a record on db. This is the model on sqlite

class BaseModel(Model):
    class Meta:
        database = db

class Compania(BaseModel):
    name = TextField(null=False)
    url = TextField()
    country = TextField()
    state = TextField()
    city = TextField()
    procesado = BooleanField(default=False)

class Visitados(BaseModel):
    url = TextField()

class Facebook(BaseModel):
    compania = ForeignKeyField(Compania,related_name='facebooks')
    link = ForeignKeyField(Visitados, related_name='enlaces_a_facebooks')
    url = TextField()

From previous querys I have link and compania, and pass the objects, when I try to issue this command:
resp = Facebook.get_or_create(url=url_a_chequear, link=de_donde_viene, compania=que_cia)

got:

Traceback (most recent call last):
  File "/usr/lib/python3.6/site-packages/peewee.py", line 3830, in execute_sql
    cursor.execute(sql, params or ())
sqlite3.OperationalError: row value misused

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/home/nomar/Documents/Trabajos/Connexa/hubspot/005.py", line 254, in <module>
    main()
  File "/home/nomar/Documents/Trabajos/Connexa/hubspot/005.py", line 237, in main
    if procesa_url(url_pagina, link, compania) is not None:
  File "/home/nomar/Documents/Trabajos/Connexa/hubspot/005.py", line 158, in procesa_url
    resp = Facebook.get_or_create(url=url_a_chequear, link=de_donde_viene, compania=que_cia)
  File "/usr/lib/python3.6/site-packages/peewee.py", line 5001, in get_or_create
    return query.get(), False
  File "/usr/lib/python3.6/site-packages/peewee.py", line 3220, in get
    return next(clone.execute())
  File "/usr/lib/python3.6/site-packages/peewee.py", line 3274, in execute
    self._qr = ResultWrapper(model_class, self._execute(), query_meta)
  File "/usr/lib/python3.6/site-packages/peewee.py", line 2939, in _execute
    return self.database.execute_sql(sql, params, self.require_commit)
  File "/usr/lib/python3.6/site-packages/peewee.py", line 3837, in execute_sql
    self.commit()
  File "/usr/lib/python3.6/site-packages/peewee.py", line 3656, in __exit__
    reraise(new_type, new_type(*exc_args), traceback)
  File "/usr/lib/python3.6/site-packages/peewee.py", line 135, in reraise
    raise value.with_traceback(tb)
  File "/usr/lib/python3.6/site-packages/peewee.py", line 3830, in execute_sql
    cursor.execute(sql, params or ())
peewee.OperationalError: row value misused

Looking into the code notice this is the raw query:
<class 'modelo.Facebook'> SELECT "t1"."id", "t1"."compania_id", "t1"."link_id", "t1"."url" FROM "facebook" AS t1 WHERE ((("t1"."url" = ?) AND ("t1"."link_id" = (?, ?))) AND ("t1"."compania_id" = ?)) ['https://www.facebook.com/XXXXXXXX', 1, 0, 1]

Why ("t1"."link_id" = (?, ?)) hast two values?!?!?!

I used peewee to create tables.

Any advice?

Thanks in advance
Nomar

SQLite Forum

(1) By beetlejuice (coleifer) on 2021-07-05 18:08:59 [link] [source]

I was hoping someone could help me understand the rules around using row values and the VALUES keyword. I am having a little trouble understanding what is and isn’t allowed.

Starting with a simple table containing a key, value:

The following works with Sqlite:

The following seems to work in Postgres, but Sqlite is reporting «row value misused»:

The following works in both Postgres and Sqlite:

Another thing I have seen done is to join on a list of values, which works well in Postgres, but Sqlite reports a syntax error near «(«:

The following, unsurprising, also works in Postgres but not Sqlite:

Moving it into a CTE works in both Postgres and Sqlite:

My questions are:

• When should one use plain row-values versus VALUES?
• Why does (key, val) = (‘k1’, 1) work but (key, val) IN ((‘k1’, 1). ) not?
• Are there ways of constructing the above type of query which would be better or more idiomatic with Sqlite?

Thank you in advance for the help.

(2) By Harald Hanche-Olsen (hanche) on 2021-07-06 13:31:03 in reply to 1 [source]

I am not an expert or authority, but here is my understanding of the issue, as far as it goes. If I am wildly off target, someone will hopefully correct me.

• A comma-separated list of expressions in parenthesis usually signifies a row value, or tuple.
• A VALUES expression is a special select statement, and as such produces a sequence of rows. Even if that sequence contains precisely one row, it is something different from a row value.
• A row value can contain any of the datatypes allowed by SQLite. Note that a row value is not among these; hence ((1,2,),(3,4)) is illegal.

The following seems to work in Postgres, but Sqlite is reporting «row value misused»:

That is not so clear-cut. What’s on the right side of WHERE … IN (…) is not an expression but a parenthesised list of expressions, so arguably, a list of row values could have been legitimate according to these rules. Perhaps it would be useful; but OTOH, the alternative to a list of expressions is a select statement, and VALUES is of course a select statement, so that will always work.

Another thing I have seen done is to join on a list of values, which works well in Postgres, but Sqlite reports a syntax error near «(«:

I am not sure why that does not work in SQLite. The syntax diagrams seem to allow it.

(3) By mzm2021 on 2021-07-06 14:13:41 in reply to 2 [link] [source]

SQLite does not accept temporary column names in a table/view alias.

That’s why it is reporting a syntax error near the left parenthesis of:

PostgresSQL docs describe aliases with column names in:

But the SQLite syntax diagram of the FROM clause does not allow such a syntax:

(4) By Harald Hanche-Olsen (hanche) on 2021-07-06 15:27:55 in reply to 3 [link] [source]

Ah, that explains it.

I do wish for more context in SQLite error messages sometimes. I mean, just adding a few characters, say “ near «(«k»,» ” would be a lot more helpful than “ near «(» ”.

Источник

SQLError: row value misused after upgrade #1921

Comments

brandond commented May 3, 2019

I recently updated to 3.9.5 and I’m now experiencing the following errors with a query that was previously working:

The text was updated successfully, but these errors were encountered:

coleifer commented May 3, 2019

Try rather than using distinct as a function, to append a call to .distinct() at the end of the query.

brandond commented May 4, 2019 •

If I go back to 3.6.4 and rerun the exact same sequence, it looks like 3.9.5 is putting parentheses around the column list that weren’t there before:

I’m using this with sqlite — apsw 3.24.0.post1

brandond commented May 4, 2019 •

There’s some logic outside this block that’s preparing a query for self.vulnerabilties, but I am calling distinct() at the end of the query rather than as a function.

coleifer commented May 4, 2019 •

Can you share what self.vulnerabilities is?

I’m wondering if you’re re-selecting or somehow selecting a tuple instead of a list of columns?

In your example code you have hosts = . but that doesn’t appear to be in any way relevant to the error, right? I’m just not understanding why you included it.

coleifer commented May 4, 2019

For example, I’m trying to replicate this and cannot:

coleifer commented May 4, 2019

This came up during regression testing and may possibly be relevant, 2692e78 — but that being said, I cannot replicate the invalid SQL you are reporting.

Please provide more information or at least an example of how I can replicate this issue.

brandond commented May 5, 2019 •

Sorry, I was trying to trim out some extra bits of non-peewee code and omitted the wrong bit. The full block is:

This is actually in a class method with a bunch of business logic in it that subclasses one of my Model classes, so self.vulnerabilities is a query against another table via foreign key backreference.

I’ll try with that commit, and if that does not fix it I’ll see if I can easily bisect and/or replicate.

Источник

SQL(query) error or missing database error

SQL(query) error or missing database. (no such table: TABLE_NAME (code 1): while compiling: SELECT * FROM TABLE_NAME WHERE IDNO = idNo )

When I call insertData() , the app crashes by showing following error:

please see what is the error in it. so that that it can work properly.

2 Answers 2

In this line: String query = » SELECT * FROM TABLE_NAME WHERE IDNO = idNo»;

You’re querying for a table called TABLE_NAME, it looks like you actually wanted to query the table whose name is contained in the variable TABLE_NAME so it should be something like:

String query = «SELECT * FROM » + TABLE_NAME + » WHERE IDNO = » + idNo;

Also read up on SQL injection vulnerabilities and use parameterised queries to avoid them.

You issue is that the table named TABLE_NAME doesn’t exist, that is because the TABLE_NAME has been enclosed in «» and is taken literally as TABLE_NAME and has not been resolved to screenGestures.

To resolve this you could code String query = » SELECT * FROM » + TABLE_NAME + » WHERE IDNO = idNo»;

However, unless required, it is recommended to not use the rawQuery method but to use the convenience method query

As such you may wish to to use :-

The conveniece methods had the following advantages :-

• The underlying SQL is built on your behalf, thus reducing the chance for errors
• The values (as passed via the 4th parameter (whereargs in the above)) are properly escaped(enclosed in quotes) and thus protected against SQL injection.

Note the 3rd and 4th parameters are closely related, that is for every ? (a placeholder for a passed value), the should be an element in the 4th parameter (a String array).

The 2nd parameter, if null, equates to all columns ( SELECT * FROM . )

The code above is in-principle code, it hasn’t been tested or run. It may therefore contain simple errors.

You may wish to refer to SQLiteDatabase — query. Note that there are 4 different query methods.

Источник

Ошибка SQL (запроса) или отсутствующая ошибка базы данных

Ошибка SQL (запроса) или отсутствует база данных. (такой таблицы нет: TABLE_NAME (код 1): во время компиляции: SELECT * FROM TABLE_NAME WHERE IDNO = idNo )

Когда я звоню insertData() , приложение вылетает, показывая следующую ошибку:

Пожалуйста, посмотрите, что в этом ошибка. так, чтобы это могло работать должным образом.

2 ответа

Вы сталкиваетесь с тем, что таблица с именем TABLE_NAME не существует, потому что TABLE_NAME заключен в «» и воспринимается буквально как TABLE_NAME и не был преобразован в screenGestures .

Чтобы решить эту проблему, вы можете написать код String query = » SELECT * FROM » + TABLE_NAME + » WHERE IDNO = idNo»;

Однако, если это не требуется, рекомендуется не использовать метод rawQuery , а использовать удобный метод запрос .

В качестве такового вы можете использовать: —

Удобные методы имели следующие преимущества:

• Базовый SQL создан от вашего имени, что снижает вероятность ошибок
• Значения (передаваемые через 4-й параметр (где указано выше) правильно экранируются (заключаются в кавычки) и, таким образом, защищены от внедрения SQL.

Обратите внимание, что 3-й и 4-й параметры тесно связаны, то есть для каждого ? (заполнителя для переданного значения) должен быть элемент в 4-м параметре (массив String).