anse1 / sqlsmith Goto Github PK

I get the following issue when running configure on Ubuntu 18.04. Any assistance appreciated :)

$ autoreconf -i
configure.ac:4: installing './install-sh'
configure.ac:4: installing './missing'
Makefile.am: installing './depcomp'
$ ./configure
checking for a BSD-compatible install... /usr/bin/install -c
checking whether build environment is sane... yes
checking for a thread-safe mkdir -p... /bin/mkdir -p
checking for gawk... gawk
checking whether make sets $(MAKE)... yes
checking whether make supports nested variables... yes
checking for g++... g++
checking whether the C++ compiler works... yes
checking for C++ compiler default output file name... a.out
checking for suffix of executables...
checking whether we are cross compiling... no
checking for suffix of object files... o
checking whether we are using the GNU C++ compiler... yes
checking whether g++ accepts -g... yes
checking for style of include used by make... GNU
checking dependency style of g++... gcc3
./configure: line 3192: syntax error near unexpected token `noext,mandatory'
./configure: line 3192: `AX_CXX_COMPILE_STDCXX_11(noext,mandatory)'

When testing SQLSmith against PostgreSQL 9.5, I found the sqlsmith process to be hanging (not proceeding for quite a while, each time I had to kill it). Also, SQLSmith sets 'statement_timeout' to 1s, so this should not have happened. At this point tested again, without giving any target database, i.e. dry-run and found even that to hang.

e.g.:

./sqlsmith --verbose --dry-run --max-queries=20

^C
queries: 15
AST stats (avg): height = 5 nodes = 26
error rate: 0

From Readme I can see that by default the errors are not logged. There is only an option to log to postgres db.
--log-to=connstr: postgres db for logging errors into (default: don’t log)

How can I log errors to disk instead of postgres db?

Thanks

/usr/local/Cellar/libpqxx/7.2.1/include/pqxx/util.hxx:120:22: error: no template named
'remove_cv_t' in namespace 'std'; did you mean 'remove_cv'?
using strip_t = std::remove_cv_t<std::remove_reference_t>;
~~~~~^~~~~~~~~~~
remove_cv
/Library/Developer/CommandLineTools/usr/include/c++/v1/type_traits:660:50: note: 'remove_cv'
declared here
template struct _LIBCPP_TEMPLATE_VIS remove_cv
^
fatal error: too many errors emitted, stopping now [-ferror-limit=]

Hello

There's been some work on adding SQL/JSON features to Postgres, and it would be great to have sqlsmith run through them and see how many problems can be found. However, those features have a bespoke syntax, so I'm afraid the current sqlsmith is not covering all of the new code.

Would it be possible to add support for those features so that we can harden the code more?

Some examples (from the src/test/regress/sql/sqljson.sql file)

SELECT JSON_OBJECT(
    'a': '123',
    1.23 value 123,
    'c': json '[ 1,true,{ } ]',
    'd': jsonb '{ "x" : 123.45 }'
    RETURNING jsonb
);
SELECT JSON_OBJECT('a': JSON_OBJECT('b': 1 RETURNING bytea) FORMAT JSON);
SELECT JSON_OBJECT(1: 1, '2': NULL, '3': 1, 4: NULL, '5': 'a' ABSENT ON NULL WITH UNIQUE RETURNING jsonb);
SELECT JSON_ARRAYAGG(foo ORDER BY bar RETURNING jsonb) FILTER (WHERE bar > 2) FROM
    (VALUES (NULL), (3), (1), (NULL), (NULL), (5), (2), (4), (NULL)) foo(bar);
SELECT JSON_OBJECTAGG(i: ('111' || i)::bytea FORMAT JSON WITH UNIQUE RETURNING text) OVER (PARTITION BY i % 2)
FROM generate_series(1,5) i;

Upcoming patches have extra stuff, such as the IS JSON VALUE expression and more.

Hi, I use sqlsmith to test a SQLite database ([chinook.db](https://github.com/lerocha/chinook-database)), and I use the following command:

 ./sqlsmith --verbose --sqlite="file:$HOME/database/chinook.db?mode=ro" --max-queries=200000

However, the program terminated within seconds:

24      near "default": syntax error
22      near "select": syntax error
9       attempt to write a readonly database
4       parser stack overflow
error rate: 0.782364
impedance report:
  window_function: 0/0 (bad/ok)
  coalesce: 325/70 (bad/ok)
  exists_predicate: 1087/269 (bad/ok)
  case_expr: 701/176 (bad/ok)
  atomic_subselect: 1097/281 (bad/ok)
  column_reference: 1221/347 (bad/ok)
  const_expr: 1099/270 (bad/ok)
  nullif: 299/88 (bad/ok)
  truth_value: 1169/322 (bad/ok)
  bool_term: 1137/288 (bad/ok)
  null_predicate: 1206/335 (bad/ok)
  table_subquery: 962/285 (bad/ok)
  table_or_query_name: 1192/347 (bad/ok)
  target_table: 38/0 (bad/ok)
  table_sample: 100/0 (bad/ok) -> BLACKLISTED
  joined_table: 1011/156 (bad/ok)
  from_clause: 1198/347 (bad/ok)
  select_list: 1221/347 (bad/ok)
  query_spec: 1172/333 (bad/ok)
  insert_stmt: 34/0 (bad/ok)
  set_list: 57/0 (bad/ok)
  when_clause: 15/0 (bad/ok)
  when_clause_update: 15/0 (bad/ok)
  when_clause_insert: 15/0 (bad/ok)
  merge_stmt: 38/0 (bad/ok)
  simple_join_cond: 515/30 (bad/ok)
  expr_join_cond: 1000/149 (bad/ok)
  select_for_update: 355/128 (bad/ok)
  common_table_expression: 137/18 (bad/ok)
  lateral_subquery: 100/0 (bad/ok) -> BLACKLISTED
  delete_returning: 33/0 (bad/ok)
  update_returning: 42/0 (bad/ok)
  upsert_stmt: 0/0 (bad/ok)

See.Se..SeSeSee.Se.eeeSeS.SeeeeSeSeSeSee.eeSe.SeSee.eSeeee.ee..eeee.eeeeSSe.SeeS
e.eee.ee.eeS...Seeeee.SSS..S.S..eeSS.SeSe.eee..eeS.Seeee.ee.eee.eAlarm clock

My environment:

zhangys@xx:~/database$ ./sqlsmith --verison
SQLsmith v1.2.1
zhangys@xx:~/database$ uname -r
4.15.0-70-generic

sqlsmith is built with ./configure && make -j4.

when I excute "make", I get this error
schema.cc:6:20: fatal error: gitrev.h: No such file or directory

where can I find this head file ?

Hi SQLSmith Team! Roel here (principal test engineer at MariaDB). Great program you have here. Would you be so kind as to a connector to MariaDB? Thank you & God bless

Ref https://mariadb.com/kb/en/mysql_query/

Starting multiple sqlsmith processes targeting the same database is inefficient because they each generate a redundant read-only schema object, wasting memory and clobbering caches.

The grammar generation would also profit from running in its own thread to avoid blocking while the DUT is busy.

Types should to be schema-qualified w/ postgres

@df7cb just noticed they are not because of errors with the
information_schema types.

Shouldn't PQXX_CFLAGS be replaced with LIBPQXX_CFLAGS in Makefile.am?

Hi,
Testing sqmlsmith over a postgresql database, the script seem to run continuously and never stop. Maybe it's the default behaviour, but as we think about inlcude it in our CI is it a solution to just run outsite of an infinite loop ?

When testing SQLSmith against PostgreSQL 12.9 (./sqlsmith --dry-run --max-queries=1 --exclude-catalog), I found the process stucking at line 159 of sqlsmith.cc (shared_ptr gen = statement_factory(&scope);). Removing "--exclude-catalog" can fix it, but the queries generated were all about the catalog, not the database (not empty) I specified.
Any assistance appreciated!

Though this issues shows up with code 'e', from PostgreSQL code and documentation, pg_event_trigger_table_rewrite_reason() can only be called in a table_rewrite event trigger function.

In SQLSmith with PostgreSQL 9.5, looks like query being generated does not have event trigger or table rewrite, however it calls this function and shows error (as expected).

Attaching query over here.
postgresql_event_trigger_table_rewrite_reason.txt

hi @anse1

When trying to use sqlsmith for PostgreSQL 11, I was facing this compilation issue:

g++ -DHAVE_CONFIG_H -I.  -I/usr/include    -Wall -Wextra   -g -O2 -std=c++11 -MT postgres.o -MD -MP -MF .deps/postgres.Tpo -c -o postgres.o postgres.cc
In file included from postgres.cc:1:0:
postgres.hh:14:22: fatal error: libpq-fe.h: No such file or directory
 #include <libpq-fe.h>
                      ^
compilation terminated.
make[1]: *** [postgres.o] Error 1
make[1]: Leaving directory `/var/lib/postgresql/sources/sqlsmith'
make: *** [all] Error 2

This was due to error in https://github.com/anse1/sqlsmith/blob/master/Makefile.am at line 26.
Here POSTGRESQL_CFLAGS is erroneously mentioned as POSTGRESQL_CPPFLAGS .
Replacing it fixes this issue

there is a test code for pg in configure
`
/* end confdefs.h. */

#include <libpq-fe.h>

int main ()

{

	   char conninfo="dbname = postgres";
	    PGconn     *conn;
	    conn = PQconnectdb(conninfo);
                return 0;

}
`

this compile failed, but reported as "configure: error: could not find libpq-fe.h header"
need this fix:
char conninfo="dbname = postgres"; >>> const char* conninfo="dbname = postgres";

https://postgr.es/m/CAPV2KRjd=ErgVGbvO2Ty20tKTEZZr6cYsYLxgN_W3eAo9pf5sw@mail.gmail.com revealed a bug in a WITH HOLD cursor:

BEGIN;
DECLARE "test-cursor-crash" CURSOR WITH HOLD FOR
            SELECT di.itemId FROM "debug".downloaded_images di
            LEFT JOIN (SELECT itemId, MIN(last_update) as last_update FROM "debug"."foo" GROUP BY itemId) computed ON di.itemId=computed.itemId
            WHERE COALESCE(last_update, '1970-01-01') < download_time;
FETCH 10000 IN "test-cursor-crash";
COMMIT;

I realize this requires sending a whole transaction instead of an isolated query, but I think it's worthwhile to explore some of the less-frequently used SQL features as well.

@zesoup reported sqlsmith turns into a CPU hog after some time when run against an empty database. Need to reproduce/investigate.

Hi,

I am trying to exercise Sqlsmith version 1.4 against postgresql version 14.6. I have a simple table created in my schema create table foo(id integer, name varchar(10));

On executing for 100 queries I am seeing a error rate of 0.52 [./sqlsmith --verbose --target="host=/tmp port=5432 dbname=mydb" --max-queries=100 --dump-all-queries]

16	ERROR:  argument declared anymultirange is not a multirange type but type anymul
8	ERROR:  argument declared anyrange is not a range type but type anyrange
6	ERROR:  cannot cast type unknown to anyenum
5	ERROR:  operator does not exist: point = point
2	ERROR:  operator does not exist: cstring = cstring
1	ERROR:  COALESCE types anymultirange and tstzmultirange cannot be matched
1	ERROR:  COALESCE types int4multirange and anymultirange cannot be matched
1	ERROR:  function pg_catalog.array_position(name, integer) does not exist
1	ERROR:  function pg_catalog.range_contains(int8range, anyrange) does not exist
1	ERROR:  function pg_catalog.range_gt(tsrange, daterange) does not exist
1	ERROR:  operator does not exist: anymultirange = int4multirange
1	ERROR:  operator does not exist: anymultirange @> oid
1	ERROR:  operator does not exist: anyrange @> oid
1	ERROR:  operator does not exist: int4multirange -|- anymultirange
1	ERROR:  operator does not exist: name && name
1	ERROR:  operator does not exist: nummultirange = anymultirange
1	ERROR:  operator does not exist: pg_node_tree <@ anymultirange
1	ERROR:  operator does not exist: polygon = polygon
1	ERROR:  operator does not exist: tsmultirange >> anyrange
1	ERROR:  operator does not exist: tsrange = anyrange

When looked into queries generated I find cast statements defined as below.

where cast(null as anymultirange) <> cast(null as anymultirange)) then cast(null as macaddr) else cast(null as macaddr) end

Could some one help on what I could be missing. I believe the operator issues flagged above are also related to the above anymultirange datatype

Thanks
Praveen

From Readme: --log-to=connstr: postgres db for logging errors into (default: don’t log)
Do I need to create a table in DB or sqlsmith will create one for it?
What is the table schema if i need to create the table beforehand?

Thanks

Hi,

Because of the previous issue #29, I couldn't fetch all queries I want with "--log-to".
I also tried with "--dump-all-queries" and pipe to save the output, but they all terminate with the same issue in #29 . Is there any chance I could save all the queries?

Thank you ;)

After
git clone
autoreconf -i
During
./configure
the following error occurred:

checking for LIBPQXX... ./configure: line 4035: syntax error near unexpected token noext,mandatory' ./configure: line 4035: AX_CXX_COMPILE_STDCXX_11(noext,mandatory)'

It may be because I have not installed libpqxx-dev (couldn't find it in the Arch Linux repos).

1.3 currently has two tags on the same commit v1.3 and debian/1.3-1.

When compiling from source with git, the version output shows the wrong tag.

$ sqlsmith --version
SQLsmith debian/1.3-1

I suggest stop using the weird debian tags and let debian use normal tags like everyone else.

Issue showed up with 's' code when running SQLSmith against PostgreSQL 9.5

e.g. valid:
postgres=# select cast (null as integer ARRAY);

int4

(1 row)

invalid:
postgres=# select cast (null as ARRAY);
ERROR: syntax error at or near "ARRAY"
LINE 1: select cast (null as ARRAY);

SQLSmith generated this query, when testing on PostgreSQL 9.5:

Query generated by SQLSmith is:

sqlsmith_cast_null_as_array_query.txt

Hi,

While trying to build sqlsmith on MacOS Sierra, the make command fails with this error -

GNU Make 3.81
Copyright (C) 2006 Free Software Foundation, Inc.
This is free software; see the source for copying conditions.
There is NO warranty; not even for MERCHANTABILITY or FITNESS FOR A
PARTICULAR PURPOSE.
This program built for i386-apple-darwin11.3.0

Any suggestions please ?

Thanks,
Kapil.

SQLsmith currently dies when a memory allocation fails. This
won't happen when run on a dedicated host due to SQLsmith's low
memory consumption, but it might happen when run on the same
machine as the DUT.

It should be easy to catch these OOM exceptions and report them
with an "M" flag in verbose output.

When compiling from source, make fails around here:

mv -f .deps/prod.Tpo .deps/prod.Po
postgres.cc: In member function ‘virtual void dut_pqxx::test(const string&)’:
postgres.cc:74:10: error: ‘class pqxx::connection’ has no member named ‘activate’
   74 |        c.activate();
      |          ^~~~~~~~
postgres.cc: In constructor ‘schema_pqxx::schema_pqxx(std::string&, bool)’:
postgres.cc:284:5: error: ‘class pqxx::connection’ has no member named ‘disconnect’
  284 |   c.disconnect();
      |     ^~~~~~~~~~
mv -f .deps/dump.Tpo .deps/dump.Po
log.cc: In constructor ‘pqxx_logger::pqxx_logger(std::string, std::string, schema&)’:
log.cc:149:16: error: ‘using work = class pqxx::transaction<>’ {aka ‘class pqxx::transaction<>’} has no member named ‘prepared’; did you mean ‘exec_prepared’?
  149 |   result r = w.prepared("instance")(GITREV)(target)(hostname)(s.version)(seed.str()).exec();
      |                ^~~~~~~~
      |                exec_prepared
log.cc: In member function ‘virtual void pqxx_logger::error(prod&, const dut::failure&)’:
log.cc:172:5: error: ‘using work = class pqxx::transaction<>’ {aka ‘class pqxx::transaction<>’} has no member named ‘prepared’; did you mean ‘exec_prepared’?
  172 |   w.prepared("error")(e.what())(s.str())(e.sqlstate).exec();
      |     ^~~~~~~~
      |     exec_prepared
log.cc: In member function ‘virtual void pqxx_logger::generated(prod&)’:
log.cc:183:7: error: ‘using work = class pqxx::transaction<>’ {aka ‘class pqxx::transaction<>’} has no member named ‘prepared’; did you mean ‘exec_prepared’?
  183 |     w.prepared("stat")(queries)(sum_height/queries)(sum_nodes/queries)(sum_retries/queries)(s.str()).exec();
      |       ^~~~~~~~
      |       exec_prepared
mv -f .deps/impedance.Tpo .deps/impedance.Po
mv -f .deps/schema.Tpo .deps/schema.Po
make[1]: *** [Makefile:453: log.o] Error 1
make[1]: *** Waiting for unfinished jobs....
make[1]: *** [Makefile:453: postgres.o] Error 1

According to libpqxx documentation, some of these function methods no longer exist.

https://www.postgresql.org/message-id/flat/PH0PR21MB132823A46AA36F0685B7A29AD8BD9%40PH0PR21MB1328.namprd21.prod.outlook.com
shows a crash in member assignment in PG14:

UPDATE domain_indirection_test SET domain_array[0].if2 = 5;
server closed the connection unexpectedly

Could be an interesting syntax feature to support.

Hi，I am build sqlsmith in Debian 9, when I run ./configure, the following errors are encountered:

./configure: line 4445: syntax error near unexpected token noext,mandatory' ./configure: line 4445: AX_CXX_COMPILE_STDCXX_17(noext,mandatory)'

Can you please advise what the problem is?

Thanks
Peng

Hi,

I compiled sqlsmith with postgresql 12beta2 and got no problems, but when running it i got this error:

"""
./sqlsmith --target="port=5432 host=/tmp dbname=regression user=jcasanov" --log-to="port=5436 host=127.0.0.1 dbname=sqlsmith user=jcasanov" --exclude-catalog
SQLsmith v1.2.1-dirty
Loading types...done.
Loading tables...done.
Loading columns and constraints...done.
Loading operators...done.
Loading routines...done.
Loading routine parameters...done.
Loading aggregates...done.
Loading aggregate parameters...done.
Generating indexes...done.
Violación de segmento (`core' generado)
"""

sqlsmith_backtrace.txt
Attached is a file with the stacktrace of the generated core

Hello,

I was using SQLSmith to generate queries for a sqlite3 database I was working on, and I was wondering if there is any procedure, to manipulate the query generator to generate certain kinds of queries that we want it to , for our database?
Any help would be highly appreciated.

Hi, I read the talk slides and want to see the implementation of AST mutation. Could you give me any hints to start with?

This requires either patching libpqxx or swapping it for libpq.

There's a 5 year old feature request in libpqxx' tracker:
http://pqxx.org/development/libpqxx/ticket/219

/home/pryzbyj/git/sqlsmith/postgres.cc: In member function 'void dut_libpq::connect(std::string&)':
/home/pryzbyj/git/sqlsmith/postgres.cc:318:19: error: 'strlen' was not declared in this scope
if (strlen(errmsg))

[pid 6169] execve("/usr/bin/c++", ["/usr/bin/c++", "-std=gnu++1y", "-I/home/pryzbyj/git/sqlsmith", "-isystem", "/usr/pgsql-11/include", "-isystem", "/usr/pgsql-11/include/server", "-c", "-o", "CMakeFiles/sqlsmith.dir/postgres.cc.o", "/home/pryzbyj/git/sqlsmith/postgres.cc"], [/* 31 vars /]) = 0
Process 6170 attached
[pid 6170] execve("/usr/libexec/gcc/x86_64-redhat-linux/4.8.5/cc1plus", ["/usr/libexec/gcc/x86_64-redhat-linux/4.8.5/cc1plus", "-quiet", "-I", "/home/pryzbyj/git/sqlsmith", "-D_GNU_SOURCE", "-isystem", "/usr/pgsql-11/include", "-isystem", "/usr/pgsql-11/include/server", "/home/pryzbyj/git/sqlsmith/postgres.cc", "-quiet", "-dumpbase", "postgres.cc", "-mtune=generic", "-march=x86-64", "-auxbase-strip", "CMakeFiles/sqlsmith.dir/postgres.cc.o", "-std=gnu++1y", "-o", "/tmp/ccfqm4Y6.s"], [/ 33 vars */]) = 0

After many hours of testing, it can happen that the Postgres DUT issues
statements into a failed transaction (commands ignored until commit) and
doesn't get out of this state.