Re: [sqlite] SQL quine using with

[Scott Robison]

Here is one (sorta with inventive rule interpretation):

sqlite3 /dev/null 'Error: near "Error": syntax error'

Generates the command as output (at least with 3.8.2 from ports on FreeBSD):

Error: near "Error": syntax errors

Thank you! [takes bow]
On Mar 8, 2014 12:16 PM, "Zsbán Ambrus"  wrote:

> And here's a quine which simply concatenates six named strings a lot of
> times.
>
>
> SELECT
> ab||a||a||a||a||aa||b||a||b||a||bb||b||
> a||aa||a||aa||aa||b||a||bb||a||bb||bb||b||
> a||ab||a||aa||bb||b||a||ba||a||bb||aa||ba
> FROM(SELECTa,','b,'a'aa,'b'bb,'SELECT
> ab||a||a||a||a||aa||b||a||b||a||bb||b||
> a||aa||a||aa||aa||b||a||bb||a||bb||bb||b||
> a||ab||a||aa||bb||b||a||ba||a||bb||aa||ba
> FROM(SELECT'ab,');'ba);
> ___
> 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] Suggestion to add "locate" as a broader version of "instr"

[Max Vlasov]

On Sat, Mar 8, 2014 at 2:24 AM, big stone  wrote:
> Ooups !
>
> Thanks to the awesome  posts about "RPAD/LPAD", I understood that I could
> already create a "sqrt()" function for SQLite3  in interpreted python.
>


Yes, that discussion was inspiring :)

Looking at your task I also played with cte version of sqrt.
Based on the "guessing" approach from one of the answers from
  
http://stackoverflow.com/questions/3581528/how-is-the-square-root-function-implemented
the following query finally worked.

/*
  :value=12345
*/
with recursive
  sqrt(depth, val, guess) as
   (
  select 1, :value, Cast(:value as Float)/2
  UNION ALL
  select depth + 1, val as newval, ((guess + val/guess)/2) as
newguess from sqrt where abs(newguess - guess) > 1e-308 and depth <
100
   )
select guess from sqrt order by depth desc limit 1

but I could not overcome some pecularities of float numbers so depth <
100 here is for cases when comparison fails to stop.
Also for CTE queries in general I wonder whether there is another
faster way to get the last row of the query (in natural executing
order), so order by depth can be replaced by something else. I suspect
ordering here triggers temporary storage.

I tested this function as "expression function" implemented based on
that thread and an average speed of this one is about 4000 sqrt
operations / second on a mobile Intel i3. Not so fast, but if one
desperately needs one, then it would be ok.

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

Re: [sqlite] Out of memory error for SELECT char();

[Luuk]

On 8-3-2014 19:48, Simon Slavin wrote:


On 8 Mar 2014, at 6:25pm, Zsbán Ambrus  wrote:


In the sqlite3 console, the following very simple statement gives
"Error: out of memory":

SELECT char();

I think this is a bug.


162:~ simon$ sqlite3 ~/Desktop/test.sqlite
SQLite version 3.7.13 2012-07-17 17:46:21
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> SELECT char();
Error: no such function: char
sqlite>

Could you please post your OS and the version of the SQLite shell tool you're 
using ?




It happens on Windows 7 too:

C:\temp>sqlite3
SQLite version 3.8.3.1 2014-02-11 14:52:19
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> select char();
Error: out of memory
sqlite> select char(65);
A
sqlite>

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

Re: [sqlite] SQL quine using with

[Zsbán Ambrus]

And here's a quine which simply concatenates six named strings a lot of times.


SELECT
ab||a||a||a||a||aa||b||a||b||a||bb||b||
a||aa||a||aa||aa||b||a||bb||a||bb||bb||b||
a||ab||a||aa||bb||b||a||ba||a||bb||aa||ba
FROM(SELECTa,','b,'a'aa,'b'bb,'SELECT
ab||a||a||a||a||aa||b||a||b||a||bb||b||
a||aa||a||aa||aa||b||a||bb||a||bb||bb||b||
a||ab||a||aa||bb||b||a||ba||a||bb||aa||ba
FROM(SELECT'ab,');'ba);
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Out of memory error for SELECT char();

[Richard Hipp]

On Sat, Mar 8, 2014 at 1:25 PM, Zsbán Ambrus  wrote:

> In the sqlite3 console, the following very simple statement gives
> "Error: out of memory":
>
> SELECT char();
>
> I think this is a bug.  This query should need very little memory, so
> it should not give such an error.  I believe it should return a single
> row with a single value of an empty string.
>

It isn't really running out of memory

The implementation of char() allocates 4 bytes of output buffer for each
input character, which is sufficient to hold any valid unicode codepoint.
But with zero input characters, that means it tries to allocate a zero-byte
output buffer.  sqlite3_malloc() returns NULL when asked to allocate zero
bytes, at which point the char() implementation thinks that the malloc()
failed and reports the output-of-memory error.

The fix is to allocate 4*N+1 bytes instead of 4*N bytes.  Dan is checking
in the fix even as I type this reply.


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

Re: [sqlite] Out of memory error for SELECT char();

[Dan Kennedy]

On 03/09/2014 01:25 AM, Zsbán Ambrus wrote:

In the sqlite3 console, the following very simple statement gives
"Error: out of memory":

SELECT char();

I think this is a bug.


It is. Thanks for the report. Now fixed here:

  http://www.sqlite.org/src/info/ba39df9d4f

Dan.




   This query should need very little memory, so
it should not give such an error.  I believe it should return a single
row with a single value of an empty string.

I've reproduced this in both a few days old preview
sqlite-amalgamation-201403051440 and the stable sqlite3 3.8.1.

Ambrus
___
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] Out of memory error for SELECT char();

[Zsbán Ambrus]

On 3/8/14, Simon Slavin  wrote:
> SQLite version 3.7.13 2012-07-17 17:46:21

The char function was added in 3.7.16.

> Could you please post your OS and the version of the SQLite shell tool
> you're using ?

I'm using Linux amd64, compiling with gcc 4.8.1.  I've got the out of
memory result in both sqlite 3.8.1 and a few days old preview
sqlite-amalgamation-201403051440.

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

Re: [sqlite] Out of memory error for SELECT char();

[Simon Slavin]

On 8 Mar 2014, at 6:25pm, Zsbán Ambrus  wrote:

> In the sqlite3 console, the following very simple statement gives
> "Error: out of memory":
> 
> SELECT char();
> 
> I think this is a bug.

162:~ simon$ sqlite3 ~/Desktop/test.sqlite
SQLite version 3.7.13 2012-07-17 17:46:21
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> SELECT char();
Error: no such function: char
sqlite> 

Could you please post your OS and the version of the SQLite shell tool you're 
using ?

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

Re: [sqlite] SQL quine using with

[Zsbán Ambrus]

Anyway, here's a different quine using the replace function.

SELECT replace(s,char(33),)||s||'''s);'FROM(SELECT'SELECT
replace(s,char(33),)||s||!!!s);!FROM(SELECT!'s);
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQL quine using with

[Dan Kennedy]

On 03/08/2014 08:53 PM, Kees Nuyt wrote:

Someone called zzo38 posted a quine (self-replicating program)
on Internet Relay Chat in network: Freenode, channel: #sqlite

[2014-03-08 11:01:59] < zzo38> I made a quine program in SQL.
[2014-03-08 11:02:10] < zzo38>

with q(q) as
(select 'with q(q) as (select ''#'') select
replace(q,x''23'',replace(,)) from q;')
select replace(q,x'23',replace(q,,'')) from q;

[2014-03-08 11:02:52] < zzo38> Do you like quine program in SQL?
[2014-03-08 11:03:06] < zzo38> Maybe do you have a better (shorter) one?

Note:
SQL preferably written as a oneliner
  
References:

http://en.wikipedia.org/wiki/Quine_(computing)
http://sqlite.org/lang_with.html

Enjoy!




SELECT REPLACE(q, 8-8, quote(q)) FROM (SELECT 'SELECT REPLACE(q, 8-8, 
quote(q)) FROM (SELECT 0 AS q);' AS q);




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

[sqlite] Out of memory error for SELECT char();

[Zsbán Ambrus]

In the sqlite3 console, the following very simple statement gives
"Error: out of memory":

SELECT char();

I think this is a bug.  This query should need very little memory, so
it should not give such an error.  I believe it should return a single
row with a single value of an empty string.

I've reproduced this in both a few days old preview
sqlite-amalgamation-201403051440 and the stable sqlite3 3.8.1.

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

Re: [sqlite] SQL quine using with

[Zsbán Ambrus]

I have a favourite general method to write a quine in any programming
language.  This involves a list of strings and a list of numeric
indexes.  The second list is used to subscript into the first list,
and the found strings are then extracted.

This is possible in sqlite3, but comes out particularly ugly.  The
reasons for the ugliness is mostly that it's not easy to concatenate a
list of strings.  The group_concat function doesn't work, because you
can't guarantee the order it concatenates the strings.

Anyway, I show my solution in the bottom of this mail.

Ambrus


CREATE TABLE pt(p);
INSERT INTO pt VALUES (),('),('),('CREATE TABLE pt(p);
INSERT INTO pt VALUES ('),(');
CREATE TABLE nt(n);
INSERT INTO nt VALUES
(3),(1),(1),(1),(1),(2),(1),(2),(1),(2),(1),(3),(1),(2),
(1),(4),(1),(2),(1),(5),(1),(4),(1),(1),(5);
CREATE TABLE rt(r);
INSERT INTO rt VALUES ('),(');
CREATE TABLE mt(m);
CREATE TRIGGER mg AFTER INSERT ON mt BEGIN
UPDATE rt SET r = r || new.m;
END;
INSERT INTO mt SELECT p FROM pt, nt WHERE pt.oid = n;
SELECT r FROM rt;');
CREATE TABLE nt(n);
INSERT INTO nt VALUES
(3),(1),(1),(1),(1),(2),(1),(2),(1),(2),(1),(3),(1),(2),
(1),(4),(1),(2),(1),(5),(1),(4),(1),(1),(5);
CREATE TABLE rt(r);
INSERT INTO rt VALUES ('');
CREATE TABLE mt(m);
CREATE TRIGGER mg AFTER INSERT ON mt BEGIN
UPDATE rt SET r = r || new.m;
END;
INSERT INTO mt SELECT p FROM pt, nt WHERE pt.oid = n;
SELECT r FROM rt;
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] curious idiom of the day...

[Petite Abeille]

with
DataSet
as
(
  select  null as value union all
  select  'YES' as value union all
  select  'NO' as value union all
  select  'PERHAPS' as value
)
select  *
fromDataSet
where   not exists
(
  select  1
  where   value = 'NO'
)
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] SQL quine using with

[Kees Nuyt]

Someone called zzo38 posted a quine (self-replicating program) 
on Internet Relay Chat in network: Freenode, channel: #sqlite

[2014-03-08 11:01:59] < zzo38> I made a quine program in SQL.
[2014-03-08 11:02:10] < zzo38> 

with q(q) as
(select 'with q(q) as (select ''#'') select
replace(q,x''23'',replace(,)) from q;')
select replace(q,x'23',replace(q,,'')) from q;

[2014-03-08 11:02:52] < zzo38> Do you like quine program in SQL?
[2014-03-08 11:03:06] < zzo38> Maybe do you have a better (shorter) one?

Note:
SQL preferably written as a oneliner
 
References:
http://en.wikipedia.org/wiki/Quine_(computing)
http://sqlite.org/lang_with.html

Enjoy!

-- 
Groet, Cordialement, Pozdrawiam, Regards,

Kees Nuyt

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

Re: [sqlite] RPAD/LPAD

[Max Vlasov]

On Sat, Mar 8, 2014 at 10:52 AM, Max Vlasov  wrote:
> On Fri, Mar 7, 2014 at 11:51 PM, Dominique Devienne  
> wrote:
>>
>> basically register_function('rpad', 'x', 'y', 'printf(''%-*s'', y,
>> x)') would register a 2-arg function (register_function's argc-2)
>> named $argv[0], which executes the following statement
>>
>> with args($argv[1],  $argv[2], ... $argv[argc-2]) as (VALUES(?, ?))
>> select $argv[argc-1) from args;
>>
>
> Dominique, your variant is even better than using numbered parameters.
> If you use named ones supported by sqlite (:VVV) then sqlite will do
> the job of the textual replacement with bind api itself  (
> 'printf(''%-*s'', :y,> :x)'  )
>
> The small problem in this case is that there are two ways (times) to
> check whether named parameter exists in the expression.

No longer problem here :) SQLite3_Bind_Parameter_name is available at
the time of registration, so the prepared statement knows everything
about the number and the names of the parameters for full checking.

I have a working prototype, some things are left to do, but this
confirms that sqlite is content with the contexts and everything.
Examples:

SELECT RegisterExpressionFunction('myfunc', '45');
Select Myfunc()
45

SELECT RegisterExpressionFunction('mysum', 'x', ':x + :x');
select mysum(45)
90

SELECT RegisterExpressionFunction('mysumalt', '', '', '?1 + ?2');
select mysumalt(45, 67)
112

SELECT RegisterExpressionFunction('strconcat', 's1', 's2', ':s1||:s2');
Select strconcat('foo ', 'bar')
foo bar


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

Re: [sqlite] SQLite version 3.8.4 release schedule

[Klaas V]

| JN: "... the Cygwin VFS to behave the same as other UNIX'es"

Warning: Off topic: RTF* and Gnu's Not Unix 
Two answers from Cygwin's  FAQ

Q: What?
A The Cygwin tools are ports of the popular GNU development tools for Microsoft 
Windows. 
They run thanks to the Cygwin library which provides the POSIX system calls and 
environment 
these programs expect. 
Q ECCN?
A No. Cygwin source and binary are made publicly available and free of charge 
to download 
so Cygwin is provided under TSU/TSPA exemption. As a result, 
Cygwin does not require an ECCN number.

I interpret this as Cygwin *not* being "other UNIX". 
In an attempt to become on topic: SQLite is another library without ECCN.

Cordiali saluti | Kind regards | Vriendelijke groeten | Freundliche Grüsse,
Klaas `Z4us` V, freelance CIO / ICT-guru / SystemDeveloper-Analyst 


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