开发者

quine (self-producing) SQL query

This is a very interesting wiki article about programs that print their own source code without any access to physical source file (in the filesystem). Examples in the articles include C and Scheme quine programs (yeah, it appears they are called like that). I remember someone asking me long ago (about 5 years) whether I coul开发者_如何学God write an SQL query that "returns itself". I had given it little thought back then (to be perfectly honest I hadn't given it any thought at all). But After reading this article I recalled that thing. Now I want to state that the person who asked that problem is not 100% credible in that it is pretty much possible he had no idea what he was talking about. But maybe it's possible? So, does anyone know if there exist quine SQL queries, whatever "return itself" may mean in that context. Thanks.


SQL Server version, from here:

SELECT Replace(Replace(
'SELECT REPLACE(REPLACE("$",CHAR(34),CHAR(39)),CHAR(36),"$") AS Quine',
Char(34), Char(39)), Char(36),
'SELECT REPLACE(REPLACE("$",CHAR(34),CHAR(39)),CHAR(36),"$") AS Quine')
AS Quine 


Oracle version:

SELECT REPLACE(REPLACE('SELECT REPLACE(REPLACE("$",CHR(34),CHR(39)),CHR(36),"$") AS Quine FROM dual',CHR(34),CHR(39)),CHR(36),'SELECT REPLACE(REPLACE("$",CHR(34),CHR(39)),CHR(36),"$") AS Quine FROM dual') AS Quine FROM dual


A couple solutions from the book Oracle SQL Revealed.

123 bytes

select
replace('@''[@'||chr(93)||''')from dual;','@',q'[select
replace('@''[@'||chr(93)||''')from dual;','@',q]')from dual;

100 bytes

select
substr(rpad(1,125,'||chr(39)),26)from dual;select
substr(rpad(1,125,'||chr(39)),26)from dual;


Sorry for digging this out. My shortest version for MS SQL Server is this 131 characters long quine:

DECLARE @ CHAR(65)='DECLARE @ CHAR(65)=#PRINT REPLACE(@,CHAR(35),CHAR(39)+@+CHAR(39))'PRINT REPLACE(@,CHAR(35),CHAR(39)+@+CHAR(39))


Using Snowflake and CURRENT_STATEMENT():

SELECT CURRENT_STATEMENT();

Output:

SELECT CURRENT_STATEMENT();


A PostgreSQL quine (from here):

SELECT left(A.v, 81) || chr(39) || A.v || chr(39) || right(A.v, 12) FROM (SELECT 'SELECT left(A.v, 81) || chr(39) || A.v || chr(39) || right(A.v, 12) FROM (SELECT AS v) AS A;' AS v) AS A;


Here is an alternative SQL Server solution that I think is a bit more explanatory. This shows how you can include arbitrary information such as comments. It makes apparent the difference between "code" and "data" in a quine. That is, you can see how there is an "escaped" representation (source code representation) of the content, and where the "unescaped" representation of the content comes from.

The "content" comes from the encoded formatting of the print statements. New line characters are included with CHAR(10). The VARBINARY is created by coping the "content" and escaping (with newlines added):

SELECT CONVERT(VARBINARY(MAX), 'print ''-- prefix'';'+CHAR(10)+'print ''DECLARE @vs VARBINARY(MAX) = '' + master.dbo.fn_varbintohexstr(@vs) + '';'';'+CHAR(10)+'print CAST(@vs AS VARCHAR(MAX))')

(Note: the output of the above is upper case, fn_varbintohexstr is lowercase, so one of those will have to change)

This is then used to build the quine:

-- prefix
DECLARE @vs VARBINARY(MAX) = 0x7072696e7420272d2d20707265666978273b0a7072696e7420274445434c415245204076732056415242494e415259284d415829203d2027202b206d61737465722e64626f2e666e5f76617262696e746f6865787374722840767329202b20273b273b0a7072696e742043415354284076732041532056415243484152284d41582929;
print '-- prefix';
print 'DECLARE @vs VARBINARY(MAX) = ' + master.dbo.fn_varbintohexstr(@vs) + ';';
print CAST(@vs AS VARCHAR(MAX))
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜