SQLite Forum

Timeline
Login

25 forum posts by user zhangysh1995

2020-08-06
11:56 Reply: Invalid group by usage? (artifact: c27d50ef22 user: zhangysh1995)

I believe I understand the documentation. So I said undefined because it uses this word.

11:53 Reply: Cannot parse `IS TRUE` when connecting with Python library (artifact: bc6d73130b user: zhangysh1995)

Thank you. I use Linux and I found a way to replace the library. See the reply above.

11:52 Reply: Cannot parse `IS TRUE` when connecting with Python library (artifact: 9b958ed9dc user: zhangysh1995)

Thank you.

I followed this blog to build a new version, and it works now.

11:20 Reply: Invalid group by usage? (artifact: 04bd4125ac user: zhangysh1995)

Thank you for the information.

It seems the result row is undefined.

08:51 Reply: Invalid group by usage? (artifact: a8b5ec9500 user: zhangysh1995)

From the rows in the table, I don't know how -11 could select a row.

sqlite> .mode column
sqlite> .headers on
sqlite> select * from table_10_undef_undef;
pk  col_bigint_signed  col_float_signed  col_double_signed  col_char(20)_signed  col_varchar(20)_signed  col_tinyint_signed  col_smallint_signed
--  -----------------  ----------------  -----------------  -------------------  ----------------------  ------------------  -------------------
0   -9.183             12.991            0.0                you                                          a                   c
1   -9553              1.0               -9.183             w                                            back                not
2   1                  -1.0              0.0                                     x                       q
3   -8                 0.0               0.0                                     is                      b
4   18                 0.0               12.991             r                    w                       p
5   117                12.991            1.009              c                                            v                   will
6   18.0266            -1.0              -9.183             can                  okay                                        i
7   12.991             1.0               -121.0             w                                                                j
8   -9.183             100.0             -9.183             right                g                       o                   a
9   100                                  12.991             s                                            r                   want
08:45 Reply: Invalid group by usage? (artifact: fd4c43acc9 user: zhangysh1995)

Could you point me to the specified documentation page? I tried to search for it but I only found this

08:43 Reply: Cannot parse `IS TRUE` when connecting with Python library (artifact: a9046916f9 user: zhangysh1995)

Then what I need to do is to check my Python library version? If I cannot find the version which supports the same grammar, what could I do to make this query runnable except for calling the ./sqlite command explicitly?

07:39 Edit: Cannot parse `IS TRUE` when connecting with Python library (artifact: fe37237521 user: zhangysh1995)

Test case (sqlitetest.py ):

import sqlite3

ddl = '''
create table table_10_undef_undef (
`pk` int primary key,
`col_bigint_signed` bigint ,
`col_float_signed` float ,
`col_double_signed` double ,
`col_char(20)_signed` char(20) ,
`col_varchar(20)_signed` varchar(20) ,
`col_tinyint_signed` tinyint ,
`col_smallint_signed` smallint
) ;
'''

try:
    cu = sqlite3.connect('test.db')
    cu.execute(ddl)
    cu.commit()
    cu.execute("SELECT `col_char(20)_signed` FROM table_10_undef_undef WHERE ( `col_float_signed` / 0 ) IS TRUE ;")
    cu.close()
except sqlite3.OperationalError as e:
    print(e)

Output:

xxx:~/remote/randgen$ rm test.db; python3 sqlitetest.py 
no such column: TRU

However, the query is executable without errors when using the shell:

sqlite> SELECT `col_char(20)_signed` FROM table_10_undef_undef WHERE ( `col_float_signed` / 0 ) IS TRUE ;

07:37 Reply: Invalid group by usage? (artifact: 0bc341b97a user: zhangysh1995)

There is no MIN or MAX in the query, it uses COUNT. Is this also a documented behavior?

07:34 Reply: Cannot parse `IS TRUE` when connecting with Python library (artifact: a88a0fca59 user: zhangysh1995)

And that the query is runnable in the shell, which means it is executable while the connector cannot parse it.

07:32 Reply: Cannot parse `IS TRUE` when connecting with Python library (artifact: 0edb85967e user: zhangysh1995)

Sorry for the typo, TRU should be TRUE in the original query.

The output from the test case is:

xxx:$ rm test.db; python3 sqlitetest.py
no such column: TRUE

I suspect it is a bug in the connector. After changing to the correct quotes ", it still have the same error:

cu.execute('SELECT "col_char(20)_signed" FROM table_10_undef_undef WHERE ( "col_float_signed" / 0 ) IS TRUE ;')

xxx:~/remote/randgen$ rm test.db; python3 sqlitetest.py 
no such column: TRUE
05:32 Post: Cannot parse `IS TRUE` when connecting with Python library (artifact: e7ed2309cb user: zhangysh1995)

Test case:

import sqlite3

ddl = '''
create table table_10_undef_undef (
`pk` int primary key,
`col_bigint_signed` bigint ,
`col_float_signed` float ,
`col_double_signed` double ,
`col_char(20)_signed` char(20) ,
`col_varchar(20)_signed` varchar(20) ,
`col_tinyint_signed` tinyint ,
`col_smallint_signed` smallint
) ;
'''

try:
    cu = sqlite3.connect('test.db')
    cu.execute(ddl)
    cu.commit()
    cu.execute("SELECT `col_char(20)_signed` FROM table_10_undef_undef WHERE ( `col_float_signed` / 0 ) IS TRUE ;")
    cu.close()
except sqlite3.OperationalError as e:
    print(e)

Output:

xxx:~/remote/randgen$ rm test.db; python3 sqlitetest.py 
no such column: TRU

However, the query is executable without errors when using the shell:

sqlite> SELECT `col_char(20)_signed` FROM table_10_undef_undef WHERE ( `col_float_signed` / 0 ) IS TRUE ;

04:17 Post: Invalid group by usage? (artifact: ad75935186 user: zhangysh1995)

In short, the following query shouldn't be executable and it produces not meaningful result:

sqlite> SELECT COUNT() AS field1, `col_char(20)_signed` FROM table_10_undef_undef ;
10|you

How to reproduce:

create table table_10_undef_undef (
`pk` int primary key,
`col_bigint_signed` bigint ,
`col_float_signed` float ,
`col_double_signed` double ,
`col_char(20)_signed` char(20) ,
`col_varchar(20)_signed` varchar(20) ,
`col_tinyint_signed` tinyint ,
`col_smallint_signed` smallint 
);

insert into table_10_undef_undef values (0,-9.183,12.991,-0,"you",null,'a','c'),(1,-9553,1,-9.183,'w',null,"back","not"),(2,1,-1,0,null,'x','q',null),(3,-8,-0,-0,null,"is",'b',null),(4,18,0,12.991,'r','w','p',null),(5,117,12.991,1.009,'c',null,'v',"will"),(6,18.0266,-1,-9.183,"can","okay",null,"i"),(7,12.991,1,-121,'w',null,null,'j'),(8,-9.183,100,-9.183,"right",'g','o','a'),(9,100,null,12.991,'s',null,'r',"want");

SELECT ~ ( COUNT(  ) ) AS field1, `col_char(20)_signed` FROM table_10_undef_undef ;
2020-06-08
13:32 Edit: Closed, answered myself Cannot select from newly created tables with Python 3 connector (artifact: 8f431ab094 user: zhangysh1995)

Hi,

My script reads from the sql files, and executes the query one by one. But I found that it cannot select the correct number of rows from the table.

For the script (Python 3.6.9):

SELECT col3, col1 from t0;

Error >>>>>> SELECT col3, col1 from t0; // this means no row returned

However, if I run it in the binary:

SQLite version 3.30.1 2019-10-10 20:19:45
Enter ".help" for usage hints.
sqlite> .tables
t0  t1  t2  t3  t4  t5  t6  t7  t8  t9
sqlite> select col3, col1 from t0;
df6320a9-6c4b-4fe3-972a-382fea3ca440|1970-01-02 00:00:01
sqlite>

This gives the correct result.

I provide the script together with the queries (all included) here.

13:31 Reply: Closed, answered myself Cannot select from newly created tables with Python 3 connector (artifact: d851579e31 user: zhangysh1995)

I found that I misunderstood the behavior of the rowcount.

Related links:

https://docs.python.org/3/library/sqlite3.html#sqlite3.Cursor.rowcount

https://stackoverflow.com/questions/839069/cursor-rowcount-always-1-in-sqlite3-in-python3k

https://stackoverflow.com/questions/21829266/how-to-get-the-numbers-of-data-rows-from-sqlite-table-in-python

13:14 Reply: Min/max and index usage? (artifact: 5ff06cbcab user: zhangysh1995)

Curious, why this one is faster than the original one?

It seems the only difference is that it splits the aggregation functions into two queries.

12:02 Post: Closed, answered myself Cannot select from newly created tables with Python 3 connector (artifact: c55c1dbf94 user: zhangysh1995)

Hi,

My script reads from the sql files, and executes the query one by one. But I found that it cannot select the correct number of rows from the table.

For the script (Python 3.6.9):

SELECT col3, col1 from t0;

Error >>>>>> SELECT col3, col1 from t0; // this means no row returned

However, if I run it in the binary:

SQLite version 3.30.1 2019-10-10 20:19:45
Enter ".help" for usage hints.
sqlite> .tables
t0  t1  t2  t3  t4  t5  t6  t7  t8  t9
sqlite> select col3, col1 from t0;
df6320a9-6c4b-4fe3-972a-382fea3ca440|1970-01-02 00:00:01
sqlite>

This gives the correct result.

I provide the script together with the queries (all included) here.

2020-05-25
14:21 Reply: Bug when converting string to boolean? (artifact: c283b4712b user: zhangysh1995)

Fully understand the context now! Thank all of you!

11:11 Reply: Bug when converting string to boolean? (artifact: 93a8a47551 user: zhangysh1995)

Nod nod.

Then what is compared when using =? Because I cannot tell in the case of '1xxx'=True. Is it comparing for the value?

08:16 Reply: Bug when converting string to boolean? (artifact: 5bb5044247 user: zhangysh1995)

You pointed out the thing I don't know how to describe.

If in the sense of = true meaning being true, I think '1xxx' is also 'being' true.

08:13 Reply: Bug when converting string to boolean? (artifact: 406071de34 user: zhangysh1995)

Think I'm not arguing about the compare results or the hack to do the compare. I'm confused with the operator =. What is the meaning of = in SQLite?

2020-05-24
14:37 Reply: Bug when converting string to boolean? (artifact: 919be5fffd user: zhangysh1995)

Thanks for the explanation. I'm more confused with the example queries when comparing a string to a Boolean.

14:36 Edit reply: Bug when converting string to boolean? (artifact: 6f6922e58a user: zhangysh1995)

First, for the documentation, it doesn't explicitly state the string should start with a number. It only gives the example which starts with a number. I think it could be adjusted to state the string must start with a number to be evaluated to true.

Secondly, it doesn't explain why '1english' is neither a FASLE or a TRUE. It is neither a NULL. I expected it should implicitly convert the string to a number/Boolean when I compare it to a number/Boolean.

Intuitively, when comparing with =, the engine should do the conversion itself.

If I do select '1xxx'=TRUE;, it is intuitively expected to convert the left hand side to a bool. Vice versa, it could also convert the right hand side to a string and compare. In the case of my examples, if it is meaningless to compare a string with a Boolean, it should raise a warning or forbid the user from using the comparison, which leads to never true result. Similarly, what is the meaning of = in '1xxx'=TRUE is not explained.

If it is not comparing the value, is it comparing the type? But because the result is always false, I couldn't tell whether I get a correct result.

14:28 Reply: Bug when converting string to boolean? (artifact: 0c85ff888f user: zhangysh1995)

First, for the documentation, it doesn't explicitly state the string should start with a number. It only gives the example which starts with a number. I think it could be adjusted to state the string must start with a number to be evaluated to true.

Secondly, it doesn't explain why '1english' is neither a FASLE or a TRUE. It is neither a NULL. I expected it should implicitly convert the string to a number/Boolean when I compare it to a number/Boolean.

Intuitively, when comparing with =, the engine should do the conversion itself.

If I do select '1xxx'=TRUE;, it is intuitively expected to convert the left hand side to a bool. Vice versa, it could also convert the right hand side to a string and compare. In the case of my examples, if it is meaningless to compare a string with a Boolean, it should raise a warning or forbid the user from using the comparison, which leads to never true result.

13:02 Post: Bug when converting string to boolean? (artifact: 34b4ec581b user: zhangysh1995)

Hi all,

Consider the following query:

sqlite> select null or (null or 'xxx1');

According to the doc:

For example, the values NULL, 0.0, 0, 'english' and '0' are all considered to > be false. Values 1, 1.0, 0.1, -0.1 and '1english' are considered to be true.

For this case, 'xxx1' should be evaluated to 1 or True, and the whole query should return 1.

However, it returned the following:

sqlite> select null or (null or 'xxx1'); <NULL>

Similarly, we have:

sqlite> select 'xxx1' is 1; 0

Furthermore, I got:

sqlite> select '1english' is 1;
0

-- implicit conversion?
sqlite> select '1english' or null;
1

sqlite> select '1english' != 1;
1

sqlite> select '1english' = FALSE;
0

sqlite> select '1english' = TRUE;
0

sqlite>  select '1english' != TRUE;
1

-- ? what is it except for true and false?
sqlite>  select '1english' != FALSE;
1

-- correct
sqlite> select '1english' is TRUE;
1

-- correct
sqlite> select '1english' is FALSE;
0

This is inconsistent with the documentation.