[sqlite] No such column error

[Aydin Ozgur Yagmur]

I have been developing a c++ program in windows. In this program, in a
loop, I open database, query and update the records then close it.

I am encountering randomly with "No such column error".

Query looks like that (but there are more columns than this STATUS table):
"select a.BIRTH_DATE, a.BIRTH_PLACE, a.BIRTH_XXX, a.BIRTH.YYY from STATUS
a;"

I randomly encounter with this error while executing sqlite3_prepare_v2
method. When encountered this error, I made a second execution of
sqlite3_prepare_v2  with "select * from STATUS a;" statement. Return value
was SQLITE_OK.

Do you have any idea what is going on?

Thank you very much in advance,
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] no such column error

[Bart Smissaert]

Hi Rob,

I can see your point, but couldn't tell you if this should be considered a
bug or not.
Probably only DRH can tell you that.

RBS

On Thu, Oct 27, 2016 at 8:21 AM, Rob Golsteijn 
wrote:

> Hi RBS,
>
> Re-introducing mytable in the sub-select is a workaround (and to get the
> same semantics in the general case I have to use the same row from the
> inner mytable and outer mytable). As indicated in my original message I
> already have a workaround for the issue.
>
> The intention of my post was to report that sqlite incorrectly reports
> that mytable does not exists. Table mytable exists --- it is the target
> table of the UPDATE statement and still in scope. I see no reason why it is
> not allowed to use it in that EXISTS expression.
>
> So my question: is there a valid reason why sqlite does not know
> mytable.myfield2 in my simplified query? Or is this a bug?
>
>
>
> Regards,
>
> Rob Golsteijn
>
>
>
> UPDATE mytable
> SET myfield1 = (SELECT 1 from mytable
>   ORDER BY EXISTS (SELECT 1
> WHERE mytable.myfield2 = 1
>   )
>)
>
>
> RBS
>
> On Tue, Oct 25, 2016 at 4:40 PM, Rob Golsteijn 
> wrote:
>
> > Hi List,
> >
> > I encountered a situation in which sqlite does not understand to which
> > field I try to refer. I simplified the original query to show the
> problem.
> > The simplified query itself is now completely meaningless (and for my
> > specific situation I could rewrite the query to work around the problem).
> >
> > In the example below I expected that mytable.myfield2 in the EXISTS
> > expression would refer to myfield2 of table mytable from the top level
> > UPDATE statement. It looks like the combination of an ORDER BY and an
> > EXISTS that refers to the table of an UPDATE statement causes sqlite to
> > report that myfield2 is unknown. Sqlite does not complain when I use
> > mytable.myfield2 in other places in the query.
> >
> > CREATE TABLE mytable
> > (
> >myfield1 INTEGER,
> >myfield2 INTEGER
> > );
> >
> > UPDATE mytable
> >SET myfield1 = (SELECT 1
> >  ORDER BY EXISTS (SELECT 1
> >WHERE mytable.myfield2 =1
> >  )
> >   );
> >
> > Error: no such column: mytable.myfield2
> >
> > Tested with sqlite versions 3.8.4.3,  3.8.8.2,   3.11.1 and 3.15.0
> > (latest).
> >
> > Met Vriendelijke Groet, Kind Regards,
> >
> > Rob Golsteijn
>
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] no such column error

[Rob Golsteijn]

Hi RBS,

Re-introducing mytable in the sub-select is a workaround (and to get the same 
semantics in the general case I have to use the same row from the inner mytable 
and outer mytable). As indicated in my original message I already have a 
workaround for the issue.

The intention of my post was to report that sqlite incorrectly reports that 
mytable does not exists. Table mytable exists --- it is the target table of the 
UPDATE statement and still in scope. I see no reason why it is not allowed to 
use it in that EXISTS expression.

So my question: is there a valid reason why sqlite does not know 
mytable.myfield2 in my simplified query? Or is this a bug?



Regards,

Rob Golsteijn



UPDATE mytable
SET myfield1 = (SELECT 1 from mytable
  ORDER BY EXISTS (SELECT 1
WHERE mytable.myfield2 = 1
  )
   )


RBS

On Tue, Oct 25, 2016 at 4:40 PM, Rob Golsteijn 
wrote:

> Hi List,
>
> I encountered a situation in which sqlite does not understand to which
> field I try to refer. I simplified the original query to show the problem.
> The simplified query itself is now completely meaningless (and for my
> specific situation I could rewrite the query to work around the problem).
>
> In the example below I expected that mytable.myfield2 in the EXISTS
> expression would refer to myfield2 of table mytable from the top level
> UPDATE statement. It looks like the combination of an ORDER BY and an
> EXISTS that refers to the table of an UPDATE statement causes sqlite to
> report that myfield2 is unknown. Sqlite does not complain when I use
> mytable.myfield2 in other places in the query.
>
> CREATE TABLE mytable
> (
>myfield1 INTEGER,
>myfield2 INTEGER
> );
>
> UPDATE mytable
>SET myfield1 = (SELECT 1
>  ORDER BY EXISTS (SELECT 1
>WHERE mytable.myfield2 =1
>  )
>   );
>
> Error: no such column: mytable.myfield2
>
> Tested with sqlite versions 3.8.4.3,  3.8.8.2,   3.11.1 and 3.15.0
> (latest).
>
> Met Vriendelijke Groet, Kind Regards,
>
> Rob Golsteijn


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] no such column error

[R Smith]

On 2016/10/25 5:44 PM, Bart Smissaert wrote:

Try this:

UPDATE mytable
 SET myfield1 = (SELECT 1 from mytable
   ORDER BY EXISTS (SELECT 1
 WHERE mytable.myfield2 = 1
   )
)


RBS


Thing is, what you propose specifically negates the point the OP is 
trying to make, in that a sub-query of a sub-query can't see the main 
query definitions when specified in an ORDER BY clause.


Your fix puts the definitions within reach, which simply hides the problem.

Myself, I do not actually know whether definitions in a SELECT /can/ in 
fact be seen n-levels of sub-query deep... It doesn't seem to be 
documented in SQLite and I can't find an SQL-Standard reference now that 
specifically demands it, but it seems possible (in SQLite at least) when 
the references happen in the SELECT or WHERE clauses of the 
sub-sub-sub-query. It however doesn't work when referred-to in the ORDER 
BY clause of the same - which is the OP's original point I think.


So to be clear, I do not know which way this should fly, but it does 
seem inconsistent in the current implementation. (Perhaps inconsistent 
is not the right word, it doesn't change from edition to edition, it 
just seem to not act the same way in all clauses, although consistently 
so through the latest editions.)


Cheers,
Ryan


PS to the OP: Kudos for not labeling it a "bug", Rob.

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] no such column error

[Bart Smissaert]

Try this:

UPDATE mytable
SET myfield1 = (SELECT 1 from mytable
  ORDER BY EXISTS (SELECT 1
WHERE mytable.myfield2 = 1
  )
   )


RBS

On Tue, Oct 25, 2016 at 4:40 PM, Rob Golsteijn 
wrote:

> Hi List,
>
> I encountered a situation in which sqlite does not understand to which
> field I try to refer. I simplified the original query to show the problem.
> The simplified query itself is now completely meaningless (and for my
> specific situation I could rewrite the query to work around the problem).
>
> In the example below I expected that mytable.myfield2 in the EXISTS
> expression would refer to myfield2 of table mytable from the top level
> UPDATE statement. It looks like the combination of an ORDER BY and an
> EXISTS that refers to the table of an UPDATE statement causes sqlite to
> report that myfield2 is unknown. Sqlite does not complain when I use
> mytable.myfield2 in other places in the query.
>
> CREATE TABLE mytable
> (
>myfield1 INTEGER,
>myfield2 INTEGER
> );
>
> UPDATE mytable
>SET myfield1 = (SELECT 1
>  ORDER BY EXISTS (SELECT 1
>WHERE mytable.myfield2 =1
>  )
>   );
>
> Error: no such column: mytable.myfield2
>
> Tested with sqlite versions 3.8.4.3,  3.8.8.2,   3.11.1 and 3.15.0
> (latest).
>
> Met Vriendelijke Groet, Kind Regards,
>
> Rob Golsteijn
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] no such column error

[Rob Golsteijn]

Hi List,

I encountered a situation in which sqlite does not understand to which field I 
try to refer. I simplified the original query to show the problem. The 
simplified query itself is now completely meaningless (and for my specific 
situation I could rewrite the query to work around the problem).

In the example below I expected that mytable.myfield2 in the EXISTS expression 
would refer to myfield2 of table mytable from the top level UPDATE statement. 
It looks like the combination of an ORDER BY and an EXISTS that refers to the 
table of an UPDATE statement causes sqlite to report that myfield2 is unknown. 
Sqlite does not complain when I use mytable.myfield2 in other places in the 
query.

CREATE TABLE mytable
(
   myfield1 INTEGER,
   myfield2 INTEGER
);

UPDATE mytable
   SET myfield1 = (SELECT 1
 ORDER BY EXISTS (SELECT 1
   WHERE mytable.myfield2 =1
 )
  );

Error: no such column: mytable.myfield2

Tested with sqlite versions 3.8.4.3,  3.8.8.2,   3.11.1 and 3.15.0 (latest).

Met Vriendelijke Groet, Kind Regards,

Rob Golsteijn

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] 'no such column' error returned in a CASE statement

[Ioannis Epaminonda]

Pavel Ivanov-2 wrote:
> 
> 
> Yes, it's expected. Column aliases are visible only in GROUP BY/ORDER
> BY/HAVING clauses and outer selects. All other places should use exact
> column expression instead.
> 
> Pavel
> 
> 

Ah, thanks Pavel for the clarification, now it makes sense.
This is a bit inconvenient but i guess i can use a VIEW for the second
select.

I noticed that the same applies for aggregate functions in the select
statement.

I was searching for some documentation in the sqlite site but didn't find
anything so i guess this is defined in the SQL standard.


-- 
View this message in context: 
http://old.nabble.com/%27no-such-column%27-error-returned-in-a-CASE-statement-tp30113686p30123189.html
Sent from the SQLite mailing list archive at Nabble.com.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] 'no such column' error returned in a CASE statement

[Pavel Ivanov]

> This works:
>
> sqlite> select a,case when a='test' then 'true' else 'false' end from (sele
> ct 'test' as a) as errval;

I guess OP meant it like this:
select a,case when a='test' then 'true' else 'false' end as errval
from (select 'test' as a);

And to answer the question:
> Is this the expected result or should the generated column be available to
> the case statement.

Yes, it's expected. Column aliases are visible only in GROUP BY/ORDER
BY/HAVING clauses and outer selects. All other places should use exact
column expression instead.


Pavel

On Tue, Nov 2, 2010 at 10:39 AM, Black, Michael (IS)
<michael.bla...@ngc.com> wrote:
> This works:
>
> sqlite> select a,case when a='test' then 'true' else 'false' end from (sele
> ct 'test' as a) as errval;
> test|true
> sqlite> select a,case when a='test' then 'true' else 'false' end from (sele
> ct 'test2' as a) as errval;
> test2|false
>
> I suppose there's another solution too...
>
> Michael D. Black
> Senior Scientist
> Advanced Analytics Directorate
> Northrop Grumman Information Systems
>
>
> 
>
> From: sqlite-users-boun...@sqlite.org on behalf of Ioannis Epaminonda
> Sent: Tue 11/2/2010 8:19 AM
> To: sqlite-users@sqlite.org
> Subject: EXTERNAL:[sqlite] 'no such column' error returned in a CASE statement
>
>
>
>
> The following error 'no such column: A' is returned when i execute the
> following statement.
>
> SELECT 'test' as A,CASE WHEN A = 'test' THEN 'true' ELSE 'false' END as
> ERRVAL
>
> Is this the expected result or should the generated column be available to
> the case statement.
> Thanks.
>
> --
> View this message in context: 
> http://old.nabble.com/%27no-such-column%27-error-returned-in-a-CASE-statement-tp30113686p30113686.html
> Sent from the SQLite mailing list archive at Nabble.com.
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] 'no such column' error returned in a CASE statement

[Black, Michael (IS)]

This works:
 
sqlite> select a,case when a='test' then 'true' else 'false' end from (sele
ct 'test' as a) as errval;
test|true
sqlite> select a,case when a='test' then 'true' else 'false' end from (sele
ct 'test2' as a) as errval;
test2|false
 
I suppose there's another solution too...
 
Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Northrop Grumman Information Systems
 



From: sqlite-users-boun...@sqlite.org on behalf of Ioannis Epaminonda
Sent: Tue 11/2/2010 8:19 AM
To: sqlite-users@sqlite.org
Subject: EXTERNAL:[sqlite] 'no such column' error returned in a CASE statement




The following error 'no such column: A' is returned when i execute the
following statement.

SELECT 'test' as A,CASE WHEN A = 'test' THEN 'true' ELSE 'false' END as
ERRVAL

Is this the expected result or should the generated column be available to
the case statement.
Thanks.

--
View this message in context: 
http://old.nabble.com/%27no-such-column%27-error-returned-in-a-CASE-statement-tp30113686p30113686.html
Sent from the SQLite mailing list archive at Nabble.com.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] 'no such column' error returned in a CASE statement

[Ioannis Epaminonda]

The following error 'no such column: A' is returned when i execute the
following statement.

SELECT 'test' as A,CASE WHEN A = 'test' THEN 'true' ELSE 'false' END as
ERRVAL

Is this the expected result or should the generated column be available to
the case statement.
Thanks.

-- 
View this message in context: 
http://old.nabble.com/%27no-such-column%27-error-returned-in-a-CASE-statement-tp30113686p30113686.html
Sent from the SQLite mailing list archive at Nabble.com.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users